Learn Database Concepts

oracle@Linux01:[/u01/oracle/DPUMP] $ exp atoorpu file=abcd.dmp logfile=test.log table=sys.aud$ About to export specified tables via Conventional Path ... . . exporting table                           AUD$     494321 rows exported Export terminated successfully without warnings. oracle@qpdbuat211:[/d01/oracle/DPUMP] $ echo $? 0 oracle@Linux01:[/u01/oracle/DPUMP] $ imp atoorpu file=abcd.dmp logifle=test.log LRM-00101: unknown parameter name 'logifle' IMP-00022: failed to process parameters, type 'IMP HELP=Y' for help IMP-00000: Import terminated unsuccessfully oracle@Linux01:[/u01/oracle/DPUMP] $ echo $? 1 Can be used in export shell scripts for status verification: if test $status -eq 0 then echo "export was successfull ." else echo " export was not successfull . " fi Also check below page fore reference : https://docs.oracle.com/database/121/SUTIL/GUID-...

expdp export exclude syntax: Here is a simple example on how to use exclude in your export cmds. ---------------------------------------------- Exclude Tables ---------------------------------------------- Using parfile: First create a parfile with details. $ vi impdp_full.par --add the details to parfile directory=DPUMP logfile=SCOTT_EXP.log dumpfile=SCOTT_%U.dmp parallel=4  EXCLUDE=TABLE:"IN ('TABLE1','TABLE2','TABLE3') Then execute using parfile: expdp SCOTT/XXXXXX parfile=impdp_full.par on cmdline: expdp SCOTT/XXXXX directory=DPUMP dumpfile=SCOTT_%U.dmp logfile=SCOTT.log schemas=SCOTT  parallel=6 EXCLUDE=TABLE:\"IN (\'TABLE1\', \'TABLE2\')\"   ---------------------------------------------- Exclude Schemas ---------------------------------------------- First create a parfile with details. $ vi expdp_full.par --add the details to parfile directory=DPUMP FULL=Y dumpfile=FULLDB_%U.dmp logfile=FULL.lo...

[oracle@oracle1 dpump]$ expdp atest/password directory=dpump dumpfile=test_tab1.dmp content=data_only tables=test_tab1 logfile=test_tab1.log Export: Release 11.2.0.1.0 - Production on Wed Feb 11 10:58:23 2015 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "ATEST"."SYS_EXPORT_TABLE_01":  atest/******** directory=dpump dumpfile=test_tab1.dmp content=data_only tables=test_tab1 logfile=test_tab1.log Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB . . exported "ATEST"."TEST_TAB1"                         5.937 KB      11 rows Master table "ATES...

Import schema from full db expdp backup: In some situations you might want to restore a single schema from entire EXPDP backup. In this example I want to explain how to import a single schema from full DB expdp backup. Lets backup the full database using the EXPDP: F:\>expdp atoorpu directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb.log full=y compression=all parallel=8 Export: Release 11.2.0.4.0 - Production on Tue May 17 14:55:21 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. Password: UDI-28002: operation generated ORACLE error 28002 ORA-28002: the password will expire within 7 days Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "ATOORPU"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded Starting "ATOORPU"."SYS_EXPORT_SCHEMA_01":  atoorpu/******** directory=dpump dump...

Lets take a full Backup: F:\>expdp atoorpu directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb.log full=y compression=all parallel=8 Export: Release 11.2.0.4.0 - Production on Tue May 17 14:55:21 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. Password: UDI-28002: operation generated ORACLE error 28002 ORA-28002: the password will expire within 7 days Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "ATOORPU"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded Starting "ATOORPU"."SYS_EXPORT_SCHEMA_01":  atoorpu/******** directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb.log full=y compression=all parallel=8 . . . . . Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX . . exported "SCOTT1"."DEPT"                ...

Most of the legacy mode options will work in the data pump with its corresponding options. In legacy mode (imp utility) we have show=y option to spool the content of the dump file into sql scripts without doing the actual import. The same command can be used in data pump impdp also but it will get replaced with sqlfile option. oracle@Linux01:[/u01/app/oracle/dpump] $ expdp atoorpu directory=DPUMP dumpfile=test1.dmp logfile=test1.log tables=TEST Export: Release 11.2.0.4.0 - Production on Wed Feb 3 15:22:00 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "ATOORPU"."SYS_EXPORT_TABLE_01":  atoorpu/******** directory=DPUMP dumpfile=test1.dmp logfile=test1.log tables=TEST Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TA...

EXPDP ERROR : oracle@Linux1:[/u01/app/oracle/dpump] $ expdp atoorpu directory=DPUMP dumpfile=SCOTT_24Sep2015.dmp logfile=SCOTT_24Sep2015.log schemas=SCOTT Export: Release 11.2.0.4.0 - Production on Thu Sep 24 11:04:40 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORA-39001: invalid argument value ORA-39021: Database compatibility version 11.2.0.4.4.4.4 is not supported. Solution : oracle@Linux1:[/u01/app/oracle/dpump] $ expdp atoorpu directory=DPUMP dumpfile=SCOTT_24Sep2015.dmp logfile=SCOTT_24Sep2015.log schemas=SCOTT version=11.2.0

Today I encountered a problem while importing a scehma into my local database. I have exported a schema from ORCL (lets say) using expdp command. I tried to import it to another database and I was getting this error.   ORA-31633: unable to create master table [oracle@orcl dpump]$ impdp sam/oracle directory=DPUMP dumpfile=abc_2014_11_14.dmp logfile=abc_imp.log schemas=sam1,sam2 Import: Release 11.2.0.4.0 - Production on Fri Nov 14 13:59:56 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORA-31626: job does not exist ORA-31633: unable to create master table "SAM.SYS_IMPORT_FULL_05" ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: at "SYS.KUPV$FT", line 1038 ORA-01031: insufficient privileges I tried again and again same error. The...

  IMPDP  TABLE_EXISTS_ACTION PARAMETER EXPLAINED  Data Pump IMPDP TABLE_EXISTS_ACTION = APPEND, REPLACE, [SKIP] and TRUNCATE In conventional import utility (IMP) we have ignore =y option which will ignore the error when the object is already exist with the same name. When it comes to the data pump there is one enhanced option of ignore=y which is called TABLE_EXISTS_ACTION. The values for this parameter give 4 different options to handle the existing table and data. $ impdp help=y     TABLE_EXISTS_ACTION     Action to take if imported object already exists.     Valid keywords are: APPEND , REPLACE , [ SKIP ] and TRUNCATE .     SKIP : Default value for this parameter is SKIP. This parameter is exactly same as the IGNORE=Y option in conventional import utility.     APPEND : This option appends the data from the data dump. The extra rows in the dump will be appended to the table and the existing dat...