Export Import einzelnen Oracle-Table

1.     Export
2.     Import
3.     Export mit parfile
4.     Import mit parfile. 4

 

 

Szenario:

Exportieren von 6 Tabellen vom User „SCC“ und Importieren von nur einzelnen Tabelle (autom. mit Index).

 

 

 

1.    Export

-- Verzeichniss für dpump anlegen.

agisora@tgx03[SCCD001T] $ mkdir /var/opt/data/oracle/SCCD001T/import/dpump

-- DIRECTORY definieren.

SQL> CREATE DIRECTORY dmpdir AS '/var/opt/data/oracle/SCCD001T/import/dpump';

Directory created.

OWNER    DIRECTORY_NAME  DIRECTORY_PATH
-------- --------------- -------------------------------------------------------
SYS      DMPDIR          /var/opt/data/oracle/SCCD001T/import/dpump
SYS      DATA_PUMP_DIR   /opt/oracle/product/10.2.0.3/rdbms/log/
SYS      ORA_ETC         /opt/SXCoratbx/etc

  

 

Zusatz Info: welche Privilegien hat z.B. SYS auf DMPDIR

SQL> SELECT privilege, directory_name FROM user_tab_privs t, all_directories dWHERE t.table_name(+)=d.directory_name ORDER BY 2,1; 

PRIVILEGE                      DIRECTORY_NAME
------------------------------ ------------------------------
READ                           DMPDIR
WRITE                          DMPDIR

 

 

-- Export bestimmten Tables vom User SCC.

$ expdp system/sc1t tables=scc.CLIENT,scc.NATIVECLIENT,scc.NATIVERELATION,
scc.CROSSREFERENCE,scc.NTDOMAIN,scc.OLD_BRCFILESUBSCRIBER
directory=DMPDIR dumpfile=testdpump.dmp logfile=exp_testdpump.log  job_name=jobtestdpump

  

 

-- Export Monitoring.

SQL> select * from dba_datapump_jobs; 

OWNER_NAME   JOB_NAME     OPERATION   JOB_MODE  STATE   EGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
------------------------------ ------------------------------ --------------------
SYSTEM       JOBTESTDPUMP EXPORT      TABLE     EXECUTING  1    1       
3

 

 

--  Output der Log Datei

$ more exp_testdpump.log

;;; 
Export: Release 10.2.0.3.0 - 64bit Production on Freitag, 16 Oktober, 2009 10:48:06
Copyright (c) 2003, 2005, Oracle.  All rights reserved.;;; 

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

Starting "SYSTEM"."JOBTESTDPUMP": 
system/******** tables=scc.CLIENT,scc.NATIVECLIENT,scc.NATIVERELATION,scc.CROSSREFERENCE,scc.NTDOMAIN,scc.OLD_BRCFILESUBSCRIBER dir
ectory=DMPDIR dumpfile=testdpump.dmp logfile=exp_testdpump.log job_name=jobtestdpump 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 7.389 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
. . exported
"SCC"."NATIVECLIENT"                        3.268 GB 11250106 rows
. . exported
"SCC"."CLIENT"                              2.661 GB 11378260 rows
. . exported
"SCC"."CROSSREFERENCE"                      362.1 MB 10367905 rows
. . exported
"SCC"."NATIVERELATION"                      23.51 MB  193116 rows
. . exported
"SCC"."OLD_BRCFILESUBSCRIBER"               4.560 MB   23726 rows
. . exported
"SCC"."NTDOMAIN"                            8.039 KB      18 rows
Master table "SYSTEM"."JOBTESTDPUMP" successfully loaded/unloaded
****************************************************************************
Dump file set for SYSTEM.JOBTESTDPUMP is:
  /var/opt/data/oracle/SCCD001T/import/dpump/testdpump.dmp
Job
"SYSTEM"."JOBTESTDPUMP" successfully
completed at 10:55:19

 

 

 

 

2.    Import

-- Importieren von einer Tabelle auf Zielsystem.

SQL> select owner, bytes, SEGMENT_NAME, SEGMENT_TYPE from dba_segments where owner='SCC' ORDER BY BYTES

SCC                                 65536 HUHU                              TABLE
SCC                                 65536 COUNTRY                           TABLE
SCC                                 65536 IDX_COUNTRY_ENGLNAME              INDEX
SCC                                 65536 IDX_COUNTRY_GERMNAME              INDEX
SCC                                 65536 T1                                TABLE
SCC                                 65536 IDX_COUNTRY_ISO3LETTERCODE        INDEX
SCC                                 65536 IDX_COUNTRY_DBCOUNTRYCODE         INDEX
SCC                                 65536 SYS_C004498                       INDEX
SCC                                 65536 IDX_COUNTRY_ISO2LETTERCODE        INDEX
9 rows selected.

 

 

Info: wenn nötig alte directory löschen.
 

SQL> drop directory WORK_DIR;
SQL> CREATE DIRECTORY dmpdir AS '/var/opt/data/oracle/ASB4001T/data1/backup';

Directory created.

 

SQL> col owner form a5
SQL> col DIRECTORY_PATH form a44
SQL> select * from dba_directories; 

OWNER       DIRECTORY_NAME        DIRECTORY_PATH
----------- --------------------- ----------------------------------------
SYS         DATA_PUMP_DIR         /opt/oracle/product/10.2.0/rdbms/log/
SYS         ADMIN_DIR             /opt/oracle/product/10.2.0/md/admin
SYS         DMPDIR                /var/opt/data/oracle/ASB4001T/data1/backup

 

 

 

-- Import von einer Table (OLD_BRCFILESUBSCRIBER)

Info: 22 error werden angezeigt, da diese User nicht auf dieser DB bestehen,
      ist kein Fehler.

$ impdp system/xxxx directory=DMPDIR dumpfile=testdpump.dmp logfile=imp_eineTable.log tables=OLD_BRCFILESUBSCRIBER

Import: Release 10.2.0.1.0 - 64bit Production on Friday, 16 October, 2009 13:37:58
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/******** directory=DMPDIR dumpfile=testdpump.dmp logfile=imp_eineTable.log table 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported
"SCC"."OLD_BRCFILESUBSCRIBER"               4.560 MB   23726 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'F986942' does not exist
Failing sql is:
GRANT SELECT ON "SCC"."OLD_BRCFILESUBSCRIBER" TO "F986942"
 [……]

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/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_TABLE_01"
completed with 22 error(s) at 13:38:41

 

--  Zwei Objecte (index, table) wurden importiert. 

SQL> select owner, bytes, SEGMENT_NAME, SEGMENT_TYPE from dba_segments where owner='SCC' ORDER BY BYTES 

SCC                                 65536 HUHU                              TABLE
SCC                                 65536 COUNTRY                           TABLE
SCC                                 65536 IDX_COUNTRY_ENGLNAME              INDEX
SCC                                 65536 IDX_COUNTRY_GERMNAME              INDEX
SCC                                 65536 IDX_COUNTRY_ISO2LETTERCODE        INDEX
SCC                                 65536 IDX_COUNTRY_ISO3LETTERCODE        INDEX
SCC                                 65536 T1                                TABLE
SCC                                 65536 IDX_COUNTRY_DBCOUNTRYCODE         INDEX
SCC                                 65536 SYS_C004498                       INDEX
SCC                                655360 PK_OLD_BRCFILESUBSCRIBER          INDEX
SCC                               6291456 OLD_BRCFILESUBSCRIBER             TABLE
11 rows selected.

 

 

 

 

 

3.    Export mit parfile 

-- Als Oracle User
-- vorab info:
SQL> select * from dba_directories;

OWNER DIRECTORY_NAME                 DIRECTORY_PATH
----- ------------------------------ --------------------------------------------
SYS   DMPDIR                         /var/opt/data/oracle/SCCD001T/import/dpump
SYS   DATA_PUMP_DIR                  /opt/oracle/product/10.2.0.3/rdbms/log/
SYS   ORA_ETC                        /opt/SXCoratbx/etc

  

$ expdp system/sc01t parfile=/home/agisora/dba_scripts/exp_imp/expdp_Table.par 

$ more /home/agisora/dba_scripts/exp_imp/expdp_Table.par

 

 

 

 

 

 4.    Import mit parfile

$ impdp system/sc01t parfile=/home/agisora/dba_scripts/exp_imp/impdp_Table.par 

$ more /home/agisora/dba_scripts/exp_imp/impdp_Table.par