Schema/User Objects Cleanup-Oracle

1. Below query to list out objects under DBUSER schema before cleanup

set linesize 400 pages 1000

col owner for a20

col object_name for a40

col object_type for a20

col status for a10

select owner,object_name,object_type,status from dba_objects where owner='DBUSER' and status='INVALID';

2. Below Query is to get the size of DBUSER schema before cleanup 

select owner,tablespace_name,sum(bytes/1024/1024/1024) from dba_segments where owner='DBUSER' group by owner,tablespace_name;

OWNER      TABLESPACE_NAME                SUM(BYTES/1024/1024/1024)

------     ---------------                -------------------------

DBUSER     DBUSER_DATA                                   322.838684

DBUSER     DBUSER_INDX                                    115.40509

3. Generate the script to drop the schema objects using below queries.

spool object_drop_DBUSER_11APR2022.sql

select 'drop '||object_type|| ' '||owner||'."'||object_name||'" cascade constraints;' from dba_objects where owner in ('DBUSER') and object_type!='LOB' and object_type = 'TABLE' order by 1 desc;

select 'drop '||object_type|| ' '||owner||'."'||object_name||'";' from dba_objects where owner in ('DBUSER') and object_type!='LOB' and object_type != 'TABLE' order by 1 desc;

select 'drop '||object_type|| ' '||owner||'."'||object_name||'";' from dba_objects where owner in ('DBUSER') and object_type='TYPE';

spool off;

4. Execute the spool file which is generated in step-3(object_drop_DBUSER_11APR2022.sql) to drop the objects

SQL> @object_drop_DBUSER_11APR2022.sql

5. Please validate the objects after schema cleanup.

set linesize 400 pages 1000

col owner for a20

col object_name for a40

col object_type for a20

col status for a10

select owner,object_name,object_type,status from dba_objects where owner='DBUSER' and status='INVALID';

6. If we want to reclaim the space from tablespaces, then purge the recycle bin and check the segments size.

purge dba_recyclebin;

PURGE RECYCLEBIN;

select owner,tablespace_name,sum(bytes/1024/1024/1024) from dba_segments where owner='DBUSER' group by owner,tablespace_name;

################################################Thank You####################################################