Skip to content

content datapump parameter

by Mohamed Azar on November 8, 2010

This post show you , Easy understanding about CONTENT datapump parameter.

Default : ALL

Purpose :

Enables you to filter what Export unloads: data only, metadata only, or both.

Syntax and Description :

CONTENT={ALL | DATA_ONLY | METADATA_ONLY}
  • ALL unloads both data and metadata. This is the default.
  • DATA_ONLY unloads only table row data; no database object definitions are unloaded.
  • METADATA_ONLY unloads only database object definitions; no table row data is unloaded.

Restrictions

The CONTENT=METADATA_ONLY parameter cannot be used in conjunction with the parameter TRANSPORT_TABLESPACES (transportable-tablespace-mode).

See Example :


SQL> conn sys/Admin123@azardb as sysdba
Connected.
SQL> create user dpuser identified by dpuser;

User created.

SQL> grant connect,resource,dba to dpuser;

Grant succeeded.

SQL> grant read,write on directory data_pump_dir to dpuser;

Grant succeeded.

SQL> conn dpuser/dpuser;
Connected.
SQL> create table employee as select  * from scott.emp;

Table created.

Now I’m go to  Export Only Metadata


C:\Users\mazar>expdp dpuser/dpuser@azardb directory=data_pump_dir dumpfile=contentmeta.dmp  log=contentmeta.log content=metadata_only

Export: Release 11.2.0.1.0 - Production on Mon Nov 8 10:10:33 2010

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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "log=contentmeta.log" Location: Command Line, Replaced with: "logfile=contentmeta.log"
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting "DPUSER"."SYS_EXPORT_SCHEMA_01":  dpuser/********@azardb directory=data_pump_dir dumpfile=contentmeta.dmp logfile=contentmeta.log content=metadata_on
y reuse_dumpfiles=true
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Master table "DPUSER"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DPUSER.SYS_EXPORT_SCHEMA_01 is:
  C:\APP\ORACLE\MAZAR\ADMIN\AZARDB\DPDUMP\CONTENTMETA.DMP
Job "DPUSER"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:14:10

** You can see No row data Exported, Only metadata information only exported.


C:\Users\mazar>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 8 10:53:55 2010

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: dpuser/dpuser

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop table employee;

Table dropped.

I dropped the table and them inport what i was exported (METADATA).

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIN$F3pNm4h2RDelNFamba47MA==$0 TABLE


C:\Users\mazar>impdp dpuser/dpuser@azardb directory=data_pump_dir dumpfile=contentmeta.dmp  log=contentmeta.log

Import: Release 11.2.0.1.0 - Production on Mon Nov 8 10:55:05 2010

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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "log=contentmeta.log" Location: Command Line, Replaced with: "logfile=contentmeta.log"
Master table "DPUSER"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "DPUSER"."SYS_IMPORT_FULL_01":  dpuser/********@azardb directory=data_pump_dir dumpfile=contentmeta.dmp logfile=contentmeta.log
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"DPUSER" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Job "DPUSER"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 10:55:12

You can see No rows imported, only metadata



 C:\Users\mazar>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 8 10:55:18 2010

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: dpuser/dpuser

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
EMPLOYEE                       TABLE

SQL> select * from employee;

no rows selected

CONTENT= data_only :

SQL> create table employee as select * from scott.emp;

Table created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options




C:\Users\mazar>expdp dpuser/dpuser@azardb directory=data_pump_dir dumpfile=contentmeta.dmp  log=contentmeta.log content=data_only

Export: Release 11.2.0.1.0 - Production on Mon Nov 8 10:56:38 2010

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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "log=contentmeta.log" Location: Command Line, Replaced with: "logfile=contentmeta.log"
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting "DPUSER"."SYS_EXPORT_SCHEMA_01":  dpuser/********@azardb directory=data_pump_dir dumpfile=contentmeta.dmp logfile=contentmeta.log content=data_only r
use_dumpfiles=true
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
. . exported "DPUSER"."EMPLOYEE"                         8.585 KB      14 rows
Master table "DPUSER"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DPUSER.SYS_EXPORT_SCHEMA_01 is:
  C:\APP\ORACLE\MAZAR\ADMIN\AZARDB\DPDUMP\CONTENTMETA.DMP
Job "DPUSER"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:56:57




Enter user-name: dpuser/dpuser

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop table employee;

Table dropped.

SQL> commit;

Commit complete.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options



C:\Users\mazar>impdp dpuser/dpuser@azardb directory=data_pump_dir dumpfile=contentmeta.dmp  log=contentmeta.log content=data_only

Import: Release 11.2.0.1.0 - Production on Mon Nov 8 11:11:08 2010

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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "log=contentmeta.log" Location: Command Line, Replaced with: "logfile=contentmeta.log"
Master table "DPUSER"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "DPUSER"."SYS_IMPORT_FULL_01":  dpuser/********@azardb directory=data_pump_dir dumpfile=contentmeta.dmp logfile=contentmeta.log content=data_only
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UPATE_TD_ROW_IMP [62]
TABLE_DATA:"DPUSER"."EMPLOYEE"
ORA-31603: object "EMPLOYEE" of type TABLE not found in schema "DPUSER"

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 8171

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
290C5B9C     18990  package body SYS.KUPW$WORKER
290C5B9C      8192  package body SYS.KUPW$WORKER
290C5B9C     18552  package body SYS.KUPW$WORKER
290C5B9C      4105  package body SYS.KUPW$WORKER
290C5B9C      8875  package body SYS.KUPW$WORKER
290C5B9C      1649  package body SYS.KUPW$WORKER
2A56420C         2  anonymous block

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UPATE_TD_ROW_IMP [62]
TABLE_DATA:"DPUSER"."EMPLOYEE"
ORA-31603: object "EMPLOYEE" of type TABLE not found in schema "DPUSER"

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 8171

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
290C5B9C     18990  package body SYS.KUPW$WORKER
290C5B9C      8192  package body SYS.KUPW$WORKER
290C5B9C     18552  package body SYS.KUPW$WORKER
290C5B9C      4105  package body SYS.KUPW$WORKER
290C5B9C      8875  package body SYS.KUPW$WORKER
290C5B9C      1649  package body SYS.KUPW$WORKER
2A56420C         2  anonymous block

Job "DPUSER"."SYS_IMPORT_FULL_01" stopped due to fatal error at 11:11:13
You can see NO TABLE avilable, So you couldnot import data without table.

Ref :

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_export.htm

From: Datapump
←
→
No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s

%d bloggers like this: