- 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.
dumpfile=emp_acc1.dmp logfile=emp_acc.log directory=DIR tables=DBATEST.EMP_TAB CONTENT=METADATA_ONLY Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options FLASHBACK 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_ONLY Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Master 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)
dumpfile=emp_acc2.dmp logfile=emp_acc.log directory=DIR tables="DBATEST"."EMP_TAB" CONTENT=DATA_ONLY FLASHBACK 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_ONLY Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 23 MB . . exported "DBATEST"."EMP_TAB" 19.16 MB 175340 rows Master 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.
select table_name from dba_tables where table_name='EMP_TAB'; no rows selected Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_FULL_01": /******** AS SYSDBA dumpfile=emp_acc2.dmp logfile=emp_acc.log directory=DIR Processing object type TABLE_EXPORT/TABLE/TABLE_DATA ORA-39034: Table TABLE_DATA:"DBATEST"."EMP_TAB" does not exist. ---- >>>>>>>> ERROR Job "SYS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Fri Nov 24 14:45:01 2024 elapsed 0 00:00:54