Content Parameter in Datapump

Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
  • User AvatarANKUSH THAVALI
  • 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

Hope It Helps!