I will explain Expdp exclude Schemas & Expdp exclude tables in Oracle in this post.
If you want to learn more details about Datapump ( Expdp ), read the following post.
Oracle Data Pump – Export Import ( Expdp Impdp ) Tutorial with Examples-4
Expdp exclude in Oracle
When You perform Full export of database or Schema, you may need excluding some schemas or tables. Especially your database or Schemas are too big, probably you need to exclude some schemas or tables during full export of database or Schema export.
You can add the EXCLUDE option to expdp command, EXCLUDE syntax is as follows.
EXCLUDE=object_type[:name_clause] [, ...]
expdp ... SCHEMAS=scott EXCLUDE=SEQUENCE, TABLE:\"IN ('EMP', 'DEPT')\"
Exclude Table in Oracle
For Example;
I want to export MSDB Schema , If I need full schema export , following command can perform this.
expdp \"/ as sysdba\" directory=DATA_PUMP_DIR dumpfile=SchemaBackup%U.dmp schemas=MSDB logfile=SchemaBackup.log parallel=64 cluster=n
BUT I want to Exclude Some tables from MSDB Schema, All tables except this excluded tables will be exported by using EXCLUDE option as follows.
If you don’t use parfile ( parameter file ), you can get the quote marks errors or other special characters, So you need to be escaped with backslash (\) character in the exclude clause as follows. OR You need to use parfile ( parameter file )
expdp \"/ as sysdba\" directory=DATA_PUMP_DIR dumpfile=SchemaBackup%U.dmp schemas=MSDB logfile=SchemaBackup.log parallel=64 cluster=n EXCLUDE=TABLE:\= \'TABLE_1\'\ EXCLUDE=TABLE:\= \'TABLE_2\'\ EXCLUDE=TABLE:\= \'TABLE_3\'\
expdp \"/ as sysdba\" directory=DATA_PUMP_DIR dumpfile=SchemaBackup%U.dmp schemas=MSDB logfile=SchemaBackup.log parallel=64 cluster=n EXCLUDE=TABLE:\"IN ('TABLE_1', 'TABLE_2','TABLE_3')\"
Exclude Schema in Oracle
If I need full database export , following command can perform this.
expdp \"/ as sysdba\" directory=DATA_PUMP_DIR dumpfile=FullDatabase%U.dmp full=y logfile=FullDatabase.log parallel=64 cluster=n
BUT I want to Exclude Some schemas from Full database export, All Schemas except this excluded schemas will be exported by using EXCLUDE option as follows.
expdp \"/ as sysdba\" directory=DATA_PUMP_DIR dumpfile=FullDatabase%U.dmp full=y logfile=FullDatabase.log parallel=64 cluster=n exclude=schema: "IN ('SCHEMA_1', 'SCHEMA_2','SCHEMA_3')"
If you don’t use parfile ( parameter file ), you can get the quote marks errors or other special characters, So you need to be escaped with backslash (\) character in the exclude clause as follows. OR You need to use parfile ( parameter file )
expdp \"/ as sysdba\" directory=DATA_PUMP_DIR dumpfile=FullDatabase%U.dmp full=y logfile=FullDatabase.log parallel=64 cluster=n EXCLUDE=SCHEMA:\"IN \(\'ORDSYS\',\'ORDPLUGINS\'\, \'SI_INFORMTN_SCHEMA\'\, \'ORDDATA\'\, \'MDSYS\'\)\"
Exclude Statistics & Index & Constraints in Oracle
You can exclude both Schemas, tables and other database objects like Statistics, Index, Constraints,grant and etc.
You can exclude these objects as follows.
exclude=constraint,GRANT,REF_CONSTRAINT,TRIGGER,STATISTICS
For Example;
I will export full schema, but I don’t want to export of schema statistics, I can exclude statistics as follows.
expdp \"/ as sysdba\" directory=DATA_PUMP_DIR dumpfile=SchemaBackup%U.dmp schemas=MSDB logfile=SchemaBackup.log parallel=64 cluster=n exclude=statistics
OR
If you want to exlude all database objects except Schema and table, use the following exclude command.
expdp \"/ as sysdba\" directory=DATA_PUMP_DIR dumpfile=SchemaBackup%U.dmp schemas=MSDB logfile=SchemaBackup.log parallel=64 cluster=n exclude=constraint,GRANT,REF_CONSTRAINT,TRIGGER,STATISTICS,PACKAGE,VIEW
You can read the following post to learn more details about Datapump Export.
Do you want to learn Oracle Database for Beginners, then read the following articles.
Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )