- 24 Nov, 2023
- 0 Comments
- 2 Mins Read
Content Parameter in Datapump
CONTENT parameter is used in expdp or impdp, to load/unload data , metadata or both.
CONTENT Specifies data to unload.
Valid keyword values are: [ALL], DATA_ONLY and METADATA_ONLY.
CONTENT=ALL is the default value.
Let’s see how these three value works :
CONTENT=METADATA_ONLY:
It will take export only the metadata. It won’t export any of the data/rows.
1 2 3 4 5 6 7 8 9 10 11 12 13 | dumpfile=emp_acc1.dmplogfile=emp_acc.logdirectory=DIRtables=DBATEST.EMP_TABCONTENT=METADATA_ONLYConnected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsFLASHBACK automatically enabled to preserve database integrity.Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA dumpfile=emp_acc1.dmp logfile=emp_acc.log directory=DIR tables=DBATEST.EMP_TAB CONTENT=METADATA_ONLYProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSProcessing object type TABLE_EXPORT/TABLE/STATISTICS/MARKERMaster table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded |
CONTENT=DATA_ONLY: It will take export only the ROWS/DATA of the tables, by excluding the DDL(i.e metadata)
1 2 3 4 5 6 7 8 9 10 11 12 | dumpfile=emp_acc2.dmplogfile=emp_acc.logdirectory=DIRtables="DBATEST"."EMP_TAB"CONTENT=DATA_ONLYFLASHBACK automatically enabled to preserve database integrity.Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA dumpfile=emp_acc2.dmp logfile=emp_acc.log directory=DIR tables=DBATEST.EMP_TAB CONTENT=DATA_ONLYEstimate in progress using BLOCKS method...Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 23 MB. . exported "DBATEST"."EMP_TAB" 19.16 MB 175340 rowsMaster table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded |
Before importing the dump into another database, make sure that the table exists in that table, as we have taken export using DATA_ONLY,
which don’t contain the DDL. Else import will fail with an error as below.
Here we tried to load this dump into a database, where table doesn’t exist.
1 2 3 4 5 6 7 8 9 | select table_name from dba_tables where table_name='EMP_TAB';no rows selectedMaster table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloadedStarting "SYS"."SYS_IMPORT_FULL_01": /******** AS SYSDBA dumpfile=emp_acc2.dmp logfile=emp_acc.log directory=DIRProcessing object type TABLE_EXPORT/TABLE/TABLE_DATAORA-39034: Table TABLE_DATA:"DBATEST"."EMP_TAB" does not exist. ---- >>>>>>>> ERRORJob "SYS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Fri Nov 24 14:45:01 2024 elapsed 0 00:00:54 |