首页 » ORACLE 9i-23ai » expdp with sys or Include Multiple tables like

expdp with sys or Include Multiple tables like

[oracle@orazhang oracle]$ expdp sys/oracle directory=expdir schemas=anbob dumpfile=v.dmp include=TABLE:\"LIKE  \'TEST%\'\" include=view:\"like \'V_%\'\"              

Export: Release 10.2.0.1.0 - Production on 星期四, 14 7月, 2011 15:38:30

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

UDE-00008: operation generated ORACLE error 28009
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

[oracle@orazhang oracle]$ expdp \'sys/oracle as sysdba\' directory=expdir schemas=anbob dumpfile=v.dmp include=TABLE:\"LIKE  \'TEST%\'\" include=view:\"like \'V_%\'\"

Export: Release 10.2.0.1.0 - Production on 星期四, 14 7月, 2011 15:28:03

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  'sys/******** AS SYSDBA' directory=expdir schemas=anbob dumpfile=v.dmp include=TABLE:"LIKE 'TEST%'" include=view:"like 'V_%'" 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
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/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "ANBOB"."TESTASC"                           5.609 KB       3 rows
. . exported "ANBOB"."TESTFBK2"                          5.273 KB       1 rows
. . exported "ANBOB"."TESTLOCK"                          5.421 KB      19 rows
. . exported "ANBOB"."TESTCUR"                               0 KB       0 rows
. . exported "ANBOB"."TESTFBK1"                              0 KB       0 rows
. . exported "ANBOB"."TESTINT"                               0 KB       0 rows
. . exported "ANBOB"."TESTINTE"                              0 KB       0 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/v.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:28:09


[oracle@orazhang oracle]$ impdp \'sys/oracle as sysdba\' directory=expdir remap_schema=anbob:weejar dumpfile=v.dmp

Import: Release 10.2.0.1.0 - Production on 星期四, 14 7月, 2011 15:30:19

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  'sys/******** AS SYSDBA' directory=expdir remap_schema=anbob:weejar dumpfile=v.dmp 
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "WEEJAR"."TESTASC"                          5.609 KB       3 rows
. . imported "WEEJAR"."TESTFBK2"                         5.273 KB       1 rows
. . imported "WEEJAR"."TESTLOCK"                         5.421 KB      19 rows
. . imported "WEEJAR"."TESTCUR"                              0 KB       0 rows
. . imported "WEEJAR"."TESTFBK1"                             0 KB       0 rows
. . imported "WEEJAR"."TESTINT"                              0 KB       0 rows
. . imported "WEEJAR"."TESTINTE"                             0 KB       0 rows
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/VIEW/VIEW
ORA-39082: Object type VIEW:"WEEJAR"."V_DL_TEST" created with compilation warnings
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 15:30:24

sys@ORCL> conn weejar/weejar
Connected.
weejar@ORCL> select view_name from user_views;

VIEW_NAME
------------------------------
V_DL_TEST

Include multiple tables like ‘a%’ or like ‘b%’ from schema

-- do a select query on it when exporting
expdp directory=DATA_PUMP_DIR dumpfile=xxxxx.dmp include=table:\"in \(select table_name from dba_tables where   owner =\'xxx\' and  table_name like \'A%\' or table_name like \'B%\' \)\"

-- Specify all table names
expdp system directory=dump_bkp dumpfile=xxx .dmp logfile=xxx.log schemas=xxx   exclude=table:"in\('A1'\,'B1'\,'A2'\)"
打赏

, , ,

目前这篇文章有1条评论(Rss)评论关闭。

  1. Porter Walbridge | #1
    2011-12-21 at 04:56

    Wow that was strange. I just wrote an extremely long comment but after I clicked submit my comment didn’t appear. Grrrr… well I’m not writing all that over again. Anyway, just wanted to say fantastic blog!

上一篇：

下一篇：