Use the below sql query to export and import multiple schema:
expdp schemas=schema1,schema2,schema3 directory=DATA_PUMP_DIR dumpfile=schemas120514bkp.dmp exclude=statistics logfile=expdpschemas120514.log
impdp schemas=schema1,schema2,schema3 directory=DATA_PUMP_DIR dumpfile=schemas120514bkp.dmp logfile=impdpschemas120514.log
sql query to export and import a schema:
expdp schemas=schema directory=DATA_PUMP_DIR dumpfile=schema120514bkp.dmp exclude=statistics logfile=expdpschema120514.log
impdp schemas=schema directory=DATA_PUMP_DIR dumpfile=schema120514bkp.dmp logfile=expdpschema120514.log
Parameter STATISTICS=NONE can either be used in export or import. No need to use the parameter in both.
To export meta data only to get ddl of the schemas:
expdp schemas=schema1,schema2,schema3 directory=TEST_DIR dumpfile=content.dat content=METADATA_ONLY exclude=statistics
To get the DDL in a text file:
impdp directory=TEST_DIR sqlfile=sql.dat logfile=sql.log dumpfile=content120514.dat
To check the DATA_PUMP_DIR path:
To see the DATA_PUMP_DIR location on server, use below sql query
set lines 300
col DIRECTORY_PATH for a50
select * from dba_directories where directory_name='DATA_PUMP_DIR';
To change the directory path:
connect database as sysdba and give following query
CREATE OR REPLACE DIRECTORY test_dir AS '/backup_DB/';
For example:
expdp schemas=schema directory=TEST_DIR dumpfile=schema120514bkp.dmp exclude=statistics logfile=expdpschema120514.log
impdp schemas=schema directory=TEST_DIR dumpfile=schema120514bkp.dmp logfile=expdpschema120514.log
expdp schemas=schema1,schema2,schema3 directory=DATA_PUMP_DIR dumpfile=schemas120514bkp.dmp exclude=statistics logfile=expdpschemas120514.log
impdp schemas=schema1,schema2,schema3 directory=DATA_PUMP_DIR dumpfile=schemas120514bkp.dmp logfile=impdpschemas120514.log
sql query to export and import a schema:
expdp schemas=schema directory=DATA_PUMP_DIR dumpfile=schema120514bkp.dmp exclude=statistics logfile=expdpschema120514.log
impdp schemas=schema directory=DATA_PUMP_DIR dumpfile=schema120514bkp.dmp logfile=expdpschema120514.log
Parameter STATISTICS=NONE can either be used in export or import. No need to use the parameter in both.
To export meta data only to get ddl of the schemas:
expdp schemas=schema1,schema2,schema3 directory=TEST_DIR dumpfile=content.dat content=METADATA_ONLY exclude=statistics
To get the DDL in a text file:
impdp directory=TEST_DIR sqlfile=sql.dat logfile=sql.log dumpfile=content120514.dat
You can open the sql.dat file in a text format to see the entire DDL of the schema exported.
To check the DATA_PUMP_DIR path:
To see the DATA_PUMP_DIR location on server, use below sql query
set lines 300
col DIRECTORY_PATH for a50
select * from dba_directories where directory_name='DATA_PUMP_DIR';
To change the directory path:
connect database as sysdba and give following query
CREATE OR REPLACE DIRECTORY test_dir AS '/backup_DB/';
For example:
expdp schemas=schema directory=TEST_DIR dumpfile=schema120514bkp.dmp exclude=statistics logfile=expdpschema120514.log
impdp schemas=schema directory=TEST_DIR dumpfile=schema120514bkp.dmp logfile=expdpschema120514.log
First, Thanks a lot.
ReplyDeleteNice Article. Was really helpful. Keep it up.
super thnx
ReplyDeleteHi my friend.
ReplyDeleteI need to export schemmas in Oracle, using the expdp.
My question:
I can use the select,exemple.
select owner, version , database
from versionsdb
where database='Model' ** in here model is a schemma model, without data, just estruture tables )
Answer the select
owner
db01 | 12.1.1.0 | model
db01 | 12.1.2.0 | model
What I need. I need to generate the backup without me worrying that every time I have another version, I have to go in the script and add a line that informs the new model schema.