Schema EXPDP/IMPDP Parfiles

 There are couple of parameter which need to pass while doing export and import in NON Container database and Pluggable database.

Please find below sample parfiles.

1. Export parfile for non container database.

cat expdp_DBUSER_ORADB_10MAY2022.par

dumpfile=expdp_DBUSER_ORADB_10MAY2022_%U.dmp

logfile=expdp_DBUSER_ORADB_10MAY2022.log

job_name=expdp_DBUSER_ORADB_10MAY2022

schemas=DBUSER

directory=EXPDP

exclude=statistics

userid = '/ as sysdba'

parallel=8

Please follow below syntax to initiate the export backup in nohup mode.

nohup expdp PARFILE=expdp_DBUSER_ORADB_10MAY2022.par &

2. Import parfile for non container database.

cat impdp_DBUSER_ORADB_10MAY2022.par

dumpfile=expdp_DBUSER_ORADB_10MAY2022_%U.dmp

logfile=impdp_DBUSER_ORADB_10MAY2022.log

job_name=impdp_DBUSER_ORADB_10MAY2022

schemas=DBUSER

directory=EXPDP

exclude=statistics

userid = '/ as sysdba'

parallel=8

Please follow below syntax to initiate the import backup in nohup mode.

nohup impdp PARFILE=expdp_DBUSER_ORADB_10MAY2022.par &

3. Export parfile for pluggable database database.

cat expdp_DBUSER_PDB_10MAY2022.par

dumpfile=expdp_DBUSER_PDB_10MAY2022_%U.dmp

logfile=expdp_DBUSER_PDB_10MAY2022.log

job_name=expdp_DBUSER_PDB_10MAY2022

schemas=DBUSER

directory=EXPDP

exclude=statistics

parallel=8

Please follow below syntax to initiate the export backup in nohup mode.

nohup expdp \'system/password@PDB_NAME\' parfile=expdp_DBUSER_PDB_10MAY2022.par &

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####################################################