Export the Schemas

 Export:

-----------

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:


EXCLUDE=object_type[:name_clause] [, ...]

expdp ... SCHEMAS=scott EXCLUDE=SEQUENCE, TABLE:\"IN ('EMP', 'DEPT')\"


Exclude Table in Oracle

For Example;


Scenario 1:

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


Scenario 2:

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')\"


Scenario 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


Scenario 4:

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\'\)\"


Comments

Post a Comment

Popular posts from this blog

Troubleshooting EBS login page issue in R12.2:

Troubleshooting EBS login page issue in R12.2: ------------------------------------------------------------------------------------------------------------------- 1. If the login page doesn't comes up then we have to focus on the varios logs present under $LOG_HOME directory. In case of EBS 12.2(as it is the latest release), 1) OHS (apache) failure If OHS is not running or not responding, one would see a message as below. If OHS is not running then there will not be any messages in any EBS log file for this request. Firefox: “The connection was reset” Steps to take Check OHS has started OK adapcctl.sh status adapcctl.sh stop adapcctl.sh start 2 OACore JVM process not available If the OACore JVM is not running or not reachable, then one will likely see the following message in the browser: Failure of server APACHE bridge: No backend server available for connection: timed out after 10 seconds or idempotent set to OFF or method not idempotent. There could be two reas...
Read more

Oracle apps dba Interview Q&A's

Oracle apps dba Interview Q&A's: =========================== What is the significance of utl_file_dir parameter in init.ora file? ===================================================================== Answer The value of this parameter is the group of directories to which u r database can write,  means u r database packages have permission to write to flat files in these directories. What is APPLPTMP environment variable? ======================================= Answer This is the temporary file location for the pl/sql temp files.  If this variable was not set then the concurrent programs may error-ed out. DIAG_PARAM ="Y" in adcmctl.sh. for CM issues oracle apps concurrent managers interview question and common information's Question:Error: ================ After submitting the concurrent program, i am getting the status as inactive and no manager and when i clicked on diagnostics button its showing a message like This request will not be pro...
Read more

Goldengate basics and Bounce

Goldengate basics, Components, and Bounce: --------------------------------------------------------- GoldenGate enables us to extract and replicate data across a variety of topologies as shown  the diagram below as well as the  exchange and manipulation of data at the transactional level between a variety of database  platforms like Oracle, DB2, SQL Server, Ingres, MySQL etc. It can support a number of different business requirements like: Business Continuity and High Availablity Data migrations and upgrades Decision Support Systems and Data Warehousing Data integration and consolidation  -- Let us know look at the differents components and processes that make up a typical GoldenGate  configuration on Oracle. Manager The Manager process must be running on both the source as well as target systems before the  Extract or Replicat process can be started and performs a number of functions including  monitoring and starting other Gol...
Read more