PL/SQL Code For Dropping All Objects in Schema

DECLARE
v_itemcount INTEGER;
BEGIN
SELECT Count(*)
INTO   v_itemcount
FROM   all_objects AO
WHERE  AO.owner = USER
AND AO.object_type NOT IN ( ‘INDEX’ )
AND AO.object_name NOT LIKE ‘BIN$%’;

WHILE ( v_itemcount > 0 ) LOOP
FOR v_cmd IN (SELECT ‘drop ‘
|| AO.object_type
|| ‘ ‘
|| AO.object_name
|| Decode(AO.object_type, ‘TABLE’,
‘ CASCADE CONSTRAINTS’,
”) AS DROPCMD
FROM   all_objects AO
WHERE  AO.owner = USER
AND AO.object_type NOT IN ( ‘INDEX’ )
AND AO.object_name NOT LIKE ‘BIN$%’) LOOP
BEGIN
EXECUTE IMMEDIATE v_cmd.dropcmd;
EXCEPTION
WHEN OTHERS THEN
NULL; — ignore errors
END;
END LOOP;

SELECT Count(*)
INTO   v_itemcount
FROM   all_objects AO
WHERE  AO.owner = USER
AND AO.object_type NOT IN ( ‘INDEX’ )
AND AO.object_name NOT LIKE ‘BIN$%’;
END LOOP;

EXECUTE IMMEDIATE ‘purge recyclebin’;
END;

[edsanimate_start entry_animation_type= “fadeIn” entry_delay= “0” entry_duration= “2” entry_timing= “linear” exit_animation_type= “” exit_delay= “” exit_duration= “” exit_timing= “” animation_repeat= “infinite” keep= “yes” animate_on= “load” scroll_offset= “” custom_css_class= “”]

NOTE: Be Careful Before Running it On Production Environment [edsanimate_end]

PL/SQL Code for Counting Rows In Schema Tables

DECLARE
i INTEGER;
BEGIN
dbms_output.Put_line(‘Table Name,Row Count,’
|| To_char(SYSDATE, ‘DD-MON-YYYY HH24:MI:SS’));

FOR v_table IN (SELECT ut.table_name
FROM   user_tables ut) LOOP
EXECUTE IMMEDIATE ‘select count(*) from ‘|| v_table.table_name INTO i;

dbms_output.Put_line(v_table.table_name
|| ‘,’
|| To_char(i));
END LOOP;
END;