Thursday, August 2, 2012

import only one table ROWS


TASK :  I want to import 1 schema table rows from a full or full schema dump

SCHEMA: Chandra
Table     :  Demo
Dumpfile : dummy.dmp
Directory = TEST

$ impdp dumpfile=dummy.dmp directory=TEST content=data_only tables=DEMO logfile=impdp_demo.log

Import: Release 11.2.0.2.0 - Production on Thu Aug 2 14:59:52 2012

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39166: Object SYS.DEMO was not found.

REASON  : Here we are running this import as sysdba user so oracle will find and import the sys.table rows , since it is not found thrown an error above

$ impdp dumpfile=dummy.dmp directory=TEST content=DATA_ONLY tables=DEMO logfile=impdp_demo.log schemas=chandra

Import: Release 11.2.0.2.0 - Production on Thu Aug 2 15:00:46 2012

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
UDI-00010: multiple job modes requested, schema and tables

REASON : I tried to import table rows by using both SCHEMAS at the same time


$ impdp dumpfile=dummy.dmp directory=TEST content=DATA_ONLY tables=DEMO logfile=impdp_demo.log

Import: Release 11.2.0.2.0 - Production on Thu Aug 2 15:03:50 2012

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

Username: Chandra
Password : *********

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_02" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_02":  /******** AS SYSDBA dumpfile=chandra_0731.dmp directory=EXPORT_DIR content=data_only tables=demo logfile=impdp_0802.log
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "CHANDRA"."DEMO"                692.9 KB   15700 rows
Job "SYS"."SYS_IMPORT_TABLE_02" successfully completed at 15:03:58


Here it searched for Chandra.demo table and imported rows

(or)

 impdp dumpfile=dummy.dmp directory=TEST content=DATA_ONLY tables=chandra.DEMO logfile=impdp_demo.log
 Import: Release 11.2.0.2.0 - Production on Thu Aug 2 15:03:50 2012

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_02" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_02":  /******** AS SYSDBA dumpfile=chandra_0731.dmp directory=TEST content=data_only tables=chandra.demo logfile=impdp_0802.log
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "CHANDRA"."DEMO"                692.9 KB   15700 rows
Job "SYS"."SYS_IMPORT_TABLE_02" successfully completed at 15:03:58

NOTE :   schema “CHANDRA” from which you are importing should have ‘imp_full_database’ role granted, also READ and WRITE permissions on TEST Directory

Thank you !!! :-) 

0 comments:

Post a Comment

Auto Scroll Stop Scroll