DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Monday, August 31, 2015

Data Pump and export Parameters - Tablespace



Data Pump and export Parameters  - Tablespaces



Data Pump Tablespace level
***********************

expdp userid=system/oracle tablespaces=users  dumpfile=tablespaces.dmp logfile=tablespaces.log directory=test_dir

[oracle@oracle11g ~]$ expdp userid=system/oracle tablespaces=users  dumpfile=tablespaces.dmp logfile=tablespaces.log directory=test_dir

Export: Release 11.2.0.3.0 - Production on Mon Aug 31 10:32:44 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLESPACE_01":  userid=system/******** tablespaces=users dumpfile=tablespaces.dmp logfile=tablespaces.log directory=test_dir
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 960 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39181: Only partial table data may be exported due to fine grain access control on "OE"."PURCHASEORDER"
. . exported "OE"."PURCHASEORDER"                        243.9 KB     132 rows
. . exported "OE"."SUBCATEGORY_REF_LIST_NESTEDTAB"       6.585 KB      21 rows
. . exported "OE"."PRODUCT_REF_LIST_NESTEDTAB"           12.51 KB     288 rows
. . exported "OE"."CATEGORIES_TAB"                       14.15 KB      22 rows
. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows
. . exported "SCOTT"."EMP"                               8.570 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.867 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLESPACE_01 is:
  /u01/dump/tablespaces.dmp

Job "SYSTEM"."SYS_EXPORT_TABLESPACE_01" completed with 1 error(s) at 10:34:08




Export Tablespace Level
**********************



exp system/oracle tablespaces=users file=/u01/users.dmp log=/u01/users.log




[oracle@oracle11g ~]$ exp system/oracle tablespaces=users file=/u01/users.dmp log=/u01/users.log

Export: Release 11.2.0.3.0 - Production on Mon Aug 31 10:54:46 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)

About to export selected tablespaces ...
For tablespace USERS ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                          BONUS          0 rows exported
. . exporting table                           DEPT          4 rows exported
. . exporting table                            EMP         14 rows exported
. . exporting table                       SALGRADE          5 rows exported
. . exporting table                 CATEGORIES_TAB         22 rows exported
. . exporting table     PRODUCT_REF_LIST_NESTEDTAB        288 rows exported
. . exporting table SUBCATEGORY_REF_LIST_NESTEDTAB         21 rows exported
EXP-00079: Data in table "PURCHASEORDER" is protected. Conventional path may only be exporting partial table.
. . exporting table                  PURCHASEORDER        132 rows exported
. exporting referential integrity constraints
. exporting triggers

Export terminated successfully with warnings.



No comments: