Bo's Oracle Station

查看: 2904|回复: 0

活动第43/44次(2018-07-28星期六下午)

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2018-7-28 09:29:42 | 显示全部楼层 |阅读模式
  1. select  * from dba_directories;

  2. create directory dir1 as '/home/oracle/dir1';

  3. create directory dir2 as '/home/oracle/dir2';

  4. grant read,write on directory dir1 to hr;

  5. grant read,write on directory dir2 to hr;

  6. select  * from dba_tab_privs tp where tp.grantee='HR';
复制代码
   GRANTEEOWNERTABLE_NAMEGRANTORPRIVILEGEGRANTABLEHIERARCHYCOMMONTYPE
1HRSYSDBMS_STATSSYSEXECUTENONONOPACKAGE
2HRSYSDIR1SYSREADNONONODIRECTORY
3HRSYSDIR1SYSWRITENONONODIRECTORY
4HRSYSDIR2SYSREADNONONODIRECTORY
5HRSYSDIR2SYSWRITENONONODIRECTORY

  1. CREATE TABLE hr.extab_employees
  2.                   (employee_id       NUMBER(4),
  3.                    first_name        VARCHAR2(20),
  4.                     last_name         VARCHAR2(25),
  5.                      hire_date         DATE)
  6. ORGANIZATION EXTERNAL
  7.      ( TYPE ORACLE_LOADER DEFAULT DIRECTORY dir1
  8.        ACCESS PARAMETERS
  9.        ( records delimited by newline
  10.          badfile dir1:'empxt%a_%p.bad'
  11.          logfile dir1:'empxt%a_%p.log'
  12.          fields terminated by ','
  13.          missing field values are null
  14.      ( employee_id, first_name, last_name,
  15.       hire_date char date_format date mask "dd-mon-yyyy"))
  16.      LOCATION (dir1:'empxt1.dat', dir2:'empxt2.dat') )
  17.      PARALLEL  REJECT LIMIT UNLIMITED;
  18.      
  19. select * from dba_tables t where t.owner='HR';   
  20.      
复制代码
   OWNERTABLE_NAMETABLESPACE_NAMECLUSTER_NAMEIOT_NAMESTATUSPCT_FREEPCT_USEDINI_TRANSMAX_TRANSINITIAL_EXTENTNEXT_EXTENTMIN_EXTENTSMAX_EXTENTSPCT_INCREASEFREELISTSFREELIST_GROUPSLOGGINGBACKED_UPNUM_ROWSBLOCKSEMPTY_BLOCKSAVG_SPACECHAIN_CNTAVG_ROW_LENAVG_SPACE_FREELIST_BLOCKSNUM_FREELIST_BLOCKSDEGREEINSTANCESCACHETABLE_LOCKSAMPLE_SIZELAST_ANALYZEDPARTITIONEDIOT_TYPETEMPORARYSECONDARYNESTEDBUFFER_POOLFLASH_CACHECELL_FLASH_CACHEROW_MOVEMENTGLOBAL_STATSUSER_STATSDURATIONSKIP_CORRUPTMONITORINGCLUSTER_OWNERDEPENDENCIESCOMPRESSIONCOMPRESS_FORDROPPEDREAD_ONLYSEGMENT_CREATEDRESULT_CACHECLUSTERINGACTIVITY_TRACKINGDML_TIMESTAMPHAS_IDENTITYCONTAINER_DATAINMEMORYINMEMORY_PRIORITYINMEMORY_DISTRIBUTEINMEMORY_COMPRESSIONINMEMORY_DUPLICATE
1HRSSMMSSMM VALID10401255 YESN00000000         1         1    NENABLED07/15/2018 9:10:56 AMNO NNNODEFAULTDEFAULTDEFAULTDISABLEDYESNO DISABLEDYES DISABLEDDISABLED NONONODEFAULTNO NONODISABLED
2HRSSMAUSERS VALID10 1255 YESN00000000         1         1    NENABLED07/15/2018 9:10:57 AMNO NNNODEFAULTDEFAULTDEFAULTDISABLEDYESNO DISABLEDYES DISABLEDDISABLED NONONODEFAULTNO NONODISABLED
3HRT05207_ASSMM VALID80205255 YESN00000000         1         1    NENABLED07/15/2018 9:10:57 AMNO NNNODEFAULTDEFAULTDEFAULTDISABLEDYESNO DISABLEDYES DISABLEDDISABLED NONONODEFAULTNO NONODISABLED
4HREMPLOYEESEXAMPLE VALID10 125565536104857612147483645 NON10750006900         1         1    NENABLED1077/7/2014 6:56:25 AMNO NNNODEFAULTDEFAULTDEFAULTDISABLEDYESNO DISABLEDYES DISABLEDDISABLED NONOYESDEFAULTNO NONODISABLED
5HREXTAB_EMPLOYEES没有表空间
VALID0000 YESN    DEFAULT   DEFAULT    NENABLED NO NNNODEFAULTDEFAULTDEFAULTDISABLEDNONO DISABLEDNO DISABLEDDISABLEDBASICNONOYESDEFAULTNO NONODISABLED
6HRJOBSEXAMPLE VALID10 125565536104857612147483645 NON1950003300         1         1    NENABLED197/7/2014 6:56:25 AMNO NNNODEFAULTDEFAULTDEFAULTDISABLEDYESNO DISABLEDYES DISABLEDDISABLED NONOYESDEFAULTNO NONODISABLED
7HRT04209_UNAMETBSUNIFORM VALID10 1255524288524288121474836450 YESN1000002470001200         1         1    NENABLED1000007/15/2018 9:10:58 AMNO NNNODEFAULTDEFAULTDEFAULTDISABLEDYESNO DISABLEDYES DISABLEDDISABLED NONOYESDEFAULTNO NONODISABLED
8HRREGIONSEXAMPLE VALID10 125565536104857612147483645 NON450001400         1         1    NENABLED47/7/2014 6:56:26 AMNO NNNODEFAULTDEFAULTDEFAULTDISABLEDYESNO DISABLEDYES DISABLEDDISABLED NONOYESDEFAULTNO NONODISABLED
9HRT_BANK_INVOICEUSERS VALID10 125565536104857612147483645 YESN          1         1    NENABLED NO NNNODEFAULTDEFAULTDEFAULTDISABLEDNONO DISABLEDYES DISABLEDDISABLED NONOYESDEFAULTNO NONODISABLED
10HRJOB_HISTORYEXAMPLE VALID10 125565536104857612147483645 NON1050003100         1         1    NENABLED107/7/2014 6:56:25 AMNO NNNODEFAULTDEFAULTDEFAULTDISABLEDYESNO DISABLEDYES DISABLEDDISABLED NONOYESDEFAULTNO NONODISABLED
11HRT05207_BUSERS VALID10 125510485760104857612147483645 YESN01006000000         1         1    NENABLED07/15/2018 9:10:58 AMNO NNNODEFAULTDEFAULTDEFAULTDISABLEDYESNO DISABLEDYES DISABLEDDISABLED NONOYESDEFAULTNO NONODISABLED
12HRCOUNTRIES VALID0000 N25 00150          1         1    NENABLED257/7/2014 6:56:24 AMNOIOTNNNODEFAULTDEFAULTDEFAULTDISABLEDYESNO DISABLEDYES DISABLEDDISABLEDBASICNONOYESDEFAULTNO NONODISABLED
13HRLOCATIONSEXAMPLE VALID10 125565536104857612147483645 NON2350004900         1         1    NENABLED237/7/2014 6:56:26 AMNO NNNODEFAULTDEFAULTDEFAULTDISABLEDYESNO DISABLEDYES DISABLEDDISABLED NONOYESDEFAULTNO NONODISABLED
14HRDEPARTMENTSEXAMPLE VALID10 125565536104857612147483645 NON2750002100         1         1    NENABLED277/7/2014 6:56:25 AMNO NNNODEFAULTDEFAULTDEFAULTDISABLEDYESNO DISABLEDYES DISABLEDDISABLED NONOYESDEFAULTNO NONODISABLED

  1. select  * from dba_external_tables et
  2. where et.owner='HR' ;
复制代码
a.png

b.png


由于控制文件和外部表都是识别行和列,所以应该可以从控制文件推出外部表:
  1. sqlldr  hr/oracle_4U  control=ccb.ctl external_table=GENERATE_ONLY log=ccb.sql

  2. SQL*Loader: Release 12.1.0.2.0 - Production on Sat Jul 28 10:01:43 2018

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

  4. Path used:      External Table
复制代码
看看ccb.sql:
  1. drop table hr.T_BANK_INVOICE;

  2. CREATE TABLE hr.T_BANK_INVOICE
  3. (
  4.   "ACCOUNT_NUMBER" VARCHAR2(255),
  5.   "SUB_NUMBER" VARCHAR2(255),
  6.   "TRANSAC_DATE" DATE,
  7.   "TRANSAC_ADDRESS" VARCHAR2(600),
  8.   "OUT_AMOUNT" NUMBER(20,2),
  9.   "IN_AMOUNT" NUMBER(20,2),
  10.   "BALANCE" NUMBER(20,2),
  11.   "OPPOSITE_NUMBER" NUMBER,
  12.   "OPPOSITE_USERNAME" VARCHAR2(100),
  13.   "CURRENCY" VARCHAR2(100),
  14.   "TRANSAC_COMMENT" VARCHAR2(600)
  15. )
  16. ORGANIZATION external
  17. (
  18.   TYPE oracle_loader
  19.   DEFAULT DIRECTORY dir1
  20.   ACCESS PARAMETERS
  21.   (
  22.     RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
  23.     BADFILE dir1:'ccb.bad'
  24.     LOGFILE dir1:'ccb%a_%p.log'
  25.     READSIZE 1048576
  26.     FIELDS TERMINATED BY "," LDRTRIM
  27.     MISSING FIELD VALUES ARE NULL
  28.     REJECT ROWS WITH ALL NULL FIELDS
  29.     (
  30.       "ACCOUNT_NUMBER" CHAR(255)
  31.         TERMINATED BY ",",
  32.       "SUB_NUMBER" CHAR(255)
  33.         TERMINATED BY ",",
  34.       "TRANSAC_DATE" (1:8) CHAR(8)
  35.         DATE_FORMAT DATE MASK "YYYYMMDD",
  36.       "TRANSAC_ADDRESS" (10) CHAR(255)
  37.         TERMINATED BY ",",
  38.       "OUT_AMOUNT" CHAR(255)
  39.         TERMINATED BY ",",
  40.       "IN_AMOUNT" CHAR(255)
  41.         TERMINATED BY ",",
  42.       "BALANCE" CHAR(255)
  43.         TERMINATED BY ",",
  44.       "OPPOSITE_NUMBER" CHAR(255)
  45.         TERMINATED BY ",",
  46.       "OPPOSITE_USERNAME" CHAR(255)
  47.         TERMINATED BY ",",
  48.       "CURRENCY" CHAR(255)
  49.         TERMINATED BY ",",
  50.       "TRANSAC_COMMENT" CHAR(255)
  51.         TERMINATED BY ","
  52.     )
  53.   )
  54.   location
  55.   (
  56.     'ccb.dat'
  57.   )
  58. )REJECT LIMIT UNLIMITED;
复制代码
外部表都是只读的:
c.png

工行网银的控制文件:
  1. LOAD DATA
  2. characterset ZHS16GBK
  3. INFILE 'icbc.dat'
  4. DISCARDFILE 'icbc.dis'
  5. append
  6. INTO table t_bank_invoice
  7. when (5) ='^'
  8. FIELDS TERMINATED BY '^'
  9. TRAILING NULLCOLS
  10. (
  11.    account_number "1402029101000999999",
  12.    sub_number "00000",
  13.    transac_date position(6:15) DATE(10) "YYYY-MM-DD" TERMINATED BY WHITESPACE,
  14.    transac_address TERMINATED BY WHITESPACE
  15.     "substr(:transac_address,2)",
  16.    transac_comment TERMINATED BY WHITESPACE
  17.     "substr(:transac_comment,2)",
  18.    currency TERMINATED BY WHITESPACE
  19.     "substr(:currency,2)",
  20.    remit TERMINATED BY WHITESPACE
  21.     "substr(:remit,2)",
  22.    in_amount TERMINATED BY WHITESPACE
  23.     "TO_NUMBER(substr(:in_amount,2),'999,999,999,999.00')",
  24.    out_amount TERMINATED BY WHITESPACE
  25.     "TO_NUMBER(substr(:out_amount,2),'999,999,999,999.00')",
  26.    balance TERMINATED BY WHITESPACE
  27.     "TO_NUMBER(substr(:balance,2),'999,999,999,999.00')",
  28.    all_comment TERMINATED BY WHITESPACE
  29.     "substr(:all_comment,2)"
  30. )
复制代码
  1. [oracle@station90 ~]$ sqlldr  hr/oracle_4U  control=icbc.ctl external_table=GENERATE_ONLY log=icbc.sql

  2. SQL*Loader: Release 12.1.0.2.0 - Production on Sat Jul 28 10:29:02 2018

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

  4. Path used:      External Table
复制代码
icbc.sql的内容:
  1. drop table hr.T_BANK_INVOICE;

  2. CREATE TABLE  hr.T_BANK_INVOICE
  3. (
  4.   "TRANSAC_DATE" DATE,
  5.   "TRANSAC_ADDRESS" VARCHAR2(600),
  6.   "TRANSAC_COMMENT" VARCHAR2(600),
  7.   "CURRENCY" VARCHAR2(255),
  8.   "REMIT" VARCHAR2(255),
  9.   "IN_AMOUNT" VARCHAR2(255),
  10.   "OUT_AMOUNT" VARCHAR2(255),
  11.   "BALANCE" VARCHAR2(255),
  12.   "ALL_COMMENT" VARCHAR2(2000)
  13. )
  14. ORGANIZATION external
  15. (
  16.   TYPE oracle_loader
  17.   DEFAULT DIRECTORY dir1
  18.   ACCESS PARAMETERS
  19.   (
  20.     RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
  21.     LOAD WHEN ((5: 5) = "^")
  22.     BADFILE dir1:'icbc.bad'
  23.     DISCARDFILE dir1:'icbc.dis'
  24.     LOGFILE dir1:'icbc%a_%p.log'
  25.     READSIZE 1048576
  26.     FIELDS TERMINATED BY "^" LDRTRIM
  27.     MISSING FIELD VALUES ARE NULL
  28.     REJECT ROWS WITH ALL NULL FIELDS
  29.     (           
  30.       "TRANSAC_DATE" (6:15) CHAR(10)
  31.         DATE_FORMAT DATE MASK "YYYY-MM-DD" ,
  32.       "TRANSAC_ADDRESS"   char(255)
  33.          TERMINATED BY     "^",
  34.       "TRANSAC_COMMENT" (48) CHAR(255)
  35.          TERMINATED BY "^",
  36.       "CURRENCY" CHAR(255)
  37.          TERMINATED BY "^",
  38.       "REMIT" CHAR(255)
  39.          TERMINATED BY "^",
  40.       "IN_AMOUNT" CHAR(255)
  41.          TERMINATED BY "^",
  42.       "OUT_AMOUNT" CHAR(255)
  43.          TERMINATED BY "^",
  44.       "BALANCE" CHAR(255)
  45.          TERMINATED BY "^",
  46.       "ALL_COMMENT" CHAR(255)
  47.          TERMINATED BY "^"
  48.     )
  49.   )
  50.   location
  51.   (
  52.     'icbc.dat'
  53.   )
  54. )REJECT LIMIT UNLIMITED;

复制代码
aaa.png

--------------------------------------二进制进出

ccccc.png

  1. [oracle@station90 dir1]$ expdp system/oracle_4U  directory=dir1 dumpfile=full.dmp  full=y job_name=fulljob

  2. Export: Release 12.1.0.2.0 - Production on Sat Jul 28 11:29:07 2018

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

  4. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  5. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
  6. Starting "SYSTEM"."FULLJOB":  system/******** directory=dir1 dumpfile=full.dmp full=y job_name=fulljob
  7. Estimate in progress using BLOCKS method...
  8. Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
  9. Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
  10. Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
  11. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
  12. Total estimation using BLOCKS method: 318.7 MB
  13. Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
  14. Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
  15. Processing object type DATABASE_EXPORT/TABLESPACE
  16. Processing object type DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION
  17. Processing object type DATABASE_EXPORT/PROFILE
  18. Processing object type DATABASE_EXPORT/SYS_USER/USER
  19. Processing object type DATABASE_EXPORT/SCHEMA/USER
  20. Processing object type DATABASE_EXPORT/ROLE
  21. Processing object type DATABASE_EXPORT/RADM_FPTM
  22. Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
  23. Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
  24. Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
  25. Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
  26. Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
  27. Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
  28. Processing object type DATABASE_EXPORT/RESOURCE_COST
  29. Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
  30. Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
  31. Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
  32. Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
  33. Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
  34. Processing object type DATABASE_EXPORT/SCHEMA/TYPE/INC_TYPE
  35. Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
  36. Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
  37. Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
  38. Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
  39. Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
  40. Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
  41. Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
  42. Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
  43. Processing object type DATABASE_EXPORT/SCHEMA/XMLSCHEMA/XMLSCHEMA
  44. Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE

  45. 这时,按了ctl+c

  46. Export> help
  47. ------------------------------------------------------------------------------

  48. The following commands are valid while in interactive mode.
  49. Note: abbreviations are allowed.

  50. ADD_FILE
  51. Add dumpfile to dumpfile set.

  52. CONTINUE_CLIENT
  53. Return to logging mode. Job will be restarted if idle.

  54. EXIT_CLIENT
  55. Quit client session and leave job running.

  56. FILESIZE
  57. Default filesize (bytes) for subsequent ADD_FILE commands.

  58. HELP
  59. Summarize interactive commands.

  60. KILL_JOB
  61. Detach and delete job.

  62. PARALLEL
  63. Change the number of active workers for current job.

  64. REUSE_DUMPFILES
  65. Overwrite destination dump file if it exists [NO].

  66. START_JOB
  67. Start or resume current job.
  68. Valid keyword values are: SKIP_CURRENT.

  69. STATUS
  70. Frequency (secs) job status is to be monitored where
  71. the default [0] will show new status when available.

  72. STOP_JOB
  73. Orderly shutdown of job execution and exits the client.
  74. Valid keyword values are: IMMEDIATE.


  75. Export> STOP_JOB
  76. Are you sure you wish to stop this job ([yes]/no): yes

  77. [oracle@station90 dir1]$ expdp system/oracle_4U  attach=fulljob

  78. Export: Release 12.1.0.2.0 - Production on Sat Jul 28 11:30:38 2018

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

  80. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  81. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

  82. Job: FULLJOB
  83.   Owner: SYSTEM                        
  84.   Operation: EXPORT                        
  85.   Creator Privs: TRUE                           
  86.   GUID: 7207D4B3F9CF3D4AE0535A00A8C00270
  87.   Start Time: Saturday, 28 July, 2018 11:30:40
  88.   Mode: FULL                           
  89.   Instance: orcl
  90.   Max Parallelism: 1
  91.   Timezone: +00:00
  92.   Timezone version: 18
  93.   Endianness: LITTLE
  94.   NLS character set: AL32UTF8
  95.   NLS NCHAR character set: AL16UTF16
  96.   EXPORT Job Parameters:
  97.   Parameter Name      Parameter Value:
  98.      CLIENT_COMMAND        system/******** directory=dir1 dumpfile=full.dmp full=y job_name=fulljob
  99.   State: IDLING                        
  100.   Bytes Processed: 0
  101.   Current Parallelism: 1
  102.   Job Error Count: 0
  103.   Dump File: /home/oracle/dir1/full.dmp
  104.     bytes written: 4,096
  105.   
  106. Worker 1 Status:
  107.   Instance ID: 1
  108.   Instance name: orcl
  109.   Host name: station90.example.com
  110.   Process Name: DW00
  111.   State: UNDEFINED                     

  112. Export> help
  113. ------------------------------------------------------------------------------

  114. The following commands are valid while in interactive mode.
  115. Note: abbreviations are allowed.

  116. ADD_FILE
  117. Add dumpfile to dumpfile set.

  118. CONTINUE_CLIENT
  119. Return to logging mode. Job will be restarted if idle.

  120. EXIT_CLIENT
  121. Quit client session and leave job running.

  122. FILESIZE
  123. Default filesize (bytes) for subsequent ADD_FILE commands.

  124. HELP
  125. Summarize interactive commands.

  126. KILL_JOB
  127. Detach and delete job.

  128. PARALLEL
  129. Change the number of active workers for current job.

  130. REUSE_DUMPFILES
  131. Overwrite destination dump file if it exists [NO].

  132. START_JOB
  133. Start or resume current job.
  134. Valid keyword values are: SKIP_CURRENT.

  135. STATUS
  136. Frequency (secs) job status is to be monitored where
  137. the default [0] will show new status when available.

  138. STOP_JOB
  139. Orderly shutdown of job execution and exits the client.
  140. Valid keyword values are: IMMEDIATE.


  141. Export> status

  142. Job: FULLJOB
  143.   Operation: EXPORT                        
  144.   Mode: FULL                           
  145.   State: IDLING                        
  146.   Bytes Processed: 0
  147.   Current Parallelism: 1
  148.   Job Error Count: 0
  149.   Dump File: /home/oracle/dir1/full.dmp
  150.     bytes written: 4,096
  151.   
  152. Worker 1 Status:
  153.   Instance ID: 1
  154.   Instance name: orcl
  155.   Host name: station90.example.com
  156.   Process Name: DW00
  157.   State: UNDEFINED                     

  158. Export> start_job

  159. Export>
复制代码
  1.    
  2. select  * from dba_datapump_jobs  dj
  3. where dj.job_name='FULLJOB';
  4.   

  5. select  * from system.fulljob;
复制代码

作业完成主表进了dumpfile,找不到了,这是正常的:
aaaaaaaa.png



导入的时候,主表先进来:
  1. [oracle@station90 ~]$ impdp user1/oracle_4U directory=dir1 dumpfile=full.dmp  tables=hr.employees remap_schema=hr:user1

  2. Import: Release 12.1.0.2.0 - Production on Sat Jul 28 11:39:28 2018

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

  4. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  5. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
  6. ORA-31626: job does not exist
  7. ORA-31633: unable to create master table "USER1.SYS_IMPORT_TABLE_05"
  8. ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
  9. ORA-06512: at "SYS.KUPV$FT", line 1048
  10. ORA-01031: insufficient privileges
复制代码
  1. grant read,write on directory dir1 to user1;
  2. alter user user1 account unlock;

  3. grant resource to user1;

  4. alter user user1 quota unlimited on example;
复制代码
  1. [oracle@station90 ~]$ impdp user1/oracle_4U directory=dir1 dumpfile=full.dmp  tables=hr.employees remap_schema=hr:user1

  2. Import: Release 12.1.0.2.0 - Production on Sat Jul 28 11:44:24 2018

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

  4. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  5. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
  6. Master table "USER1"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
  7. Starting "USER1"."SYS_IMPORT_TABLE_01":  user1/******** directory=dir1 dumpfile=full.dmp tables=hr.employees remap_schema=hr:user1
  8. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
  9. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
  10. . . imported "USER1"."EMPLOYEES"                         17.07 KB     107 rows
  11. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
  12. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
  13. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/AUDIT_OBJ
  14. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
  15. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
  16. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
  17. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
  18. ORA-39083: Object type REF_CONSTRAINT:"USER1"."EMP_DEPT_FK" failed to create with error:
  19. ORA-00942: table or view does not exist
  20. Failing sql is:
  21. ALTER TABLE "USER1"."EMPLOYEES" ADD CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID") REFERENCES "USER1"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE
  22. ORA-39083: Object type REF_CONSTRAINT:"USER1"."EMP_JOB_FK" failed to create with error:
  23. ORA-00942: table or view does not exist
  24. Failing sql is:
  25. ALTER TABLE "USER1"."EMPLOYEES" ADD CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID") REFERENCES "USER1"."JOBS" ("JOB_ID") ENABLE
  26. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
  27. Processing object type DATABASE_EXPORT/STATISTICS/MARKER
  28. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
  29. ORA-39083: Object type TRIGGER failed to create with error:
  30. ORA-31625: Schema SYS is needed to import this object, but is unaccessible
  31. ORA-01031: insufficient privileges
  32. Failing sql is:
  33. CREATE NONEDITIONABLE TRIGGER trg05211
  34. after update of salary on hr.employees
  35. referencing new as new old as old
  36. for each row
  37. begin
  38.   if :old.salary != :new.salary
  39.   then
  40.       insert into t05211_value  values( to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS')||' '||
  41.                                                   sys_context('usere
  42. ORA-39082: Object type TRIGGER:"USER1"."SECURE_EMPLOYEES" created with compilation warnings
  43. ORA-39082: Object type TRIGGER:"USER1"."UPDATE_JOB_HISTORY" created with compilation warnings
  44. Job "USER1"."SYS_IMPORT_TABLE_01" completed with 5 error(s) at Sat Jul 28 11:44:47 2018 elapsed 0 00:00:22


复制代码
  1. [oracle@station90 ~]$ impdp user1/oracle_4U directory=dir1 dumpfile=full.dmp  tables=hr.departments remap_schema=hr:user1  remap_tablespace=example:users

  2. Import: Release 12.1.0.2.0 - Production on Sat Jul 28 11:46:39 2018

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

  4. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  5. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
  6. Master table "USER1"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
  7. Starting "USER1"."SYS_IMPORT_TABLE_01":  user1/******** directory=dir1 dumpfile=full.dmp tables=hr.departments remap_schema=hr:user1 remap_tablespace=example:users
  8. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
  9. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
  10. . . imported "USER1"."DEPARTMENTS"                       7.125 KB      27 rows
  11. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
  12. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
  13. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
  14. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
  15. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
  16. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
  17. ORA-39083: Object type REF_CONSTRAINT:"USER1"."DEPT_LOC_FK" failed to create with error:
  18. ORA-00942: table or view does not exist
  19. Failing sql is:
  20. ALTER TABLE "USER1"."DEPARTMENTS" ADD CONSTRAINT "DEPT_LOC_FK" FOREIGN KEY ("LOCATION_ID") REFERENCES "USER1"."LOCATIONS" ("LOCATION_ID") ENABLE
  21. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
  22. Processing object type DATABASE_EXPORT/STATISTICS/MARKER
  23. Job "USER1"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Sat Jul 28 11:47:02 2018 elapsed 0 00:00:21

  24. [oracle@station90 ~]$
复制代码
  1. select  * from dba_tables t where t.table_name  in ('DEPARTMENTS',
  2. 'EMPLOYEES')
  3. ;
复制代码

   OWNERTABLE_NAMETABLESPACE_NAMECLUSTER_NAMEIOT_NAMESTATUSPCT_FREEPCT_USEDINI_TRANSMAX_TRANSINITIAL_EXTENTNEXT_EXTENTMIN_EXTENTSMAX_EXTENTSPCT_INCREASEFREELISTSFREELIST_GROUPSLOGGINGBACKED_UPNUM_ROWSBLOCKSEMPTY_BLOCKSAVG_SPACECHAIN_CNTAVG_ROW_LENAVG_SPACE_FREELIST_BLOCKSNUM_FREELIST_BLOCKSDEGREEINSTANCESCACHETABLE_LOCKSAMPLE_SIZELAST_ANALYZEDPARTITIONEDIOT_TYPETEMPORARYSECONDARYNESTEDBUFFER_POOLFLASH_CACHECELL_FLASH_CACHEROW_MOVEMENTGLOBAL_STATSUSER_STATSDURATIONSKIP_CORRUPTMONITORINGCLUSTER_OWNERDEPENDENCIESCOMPRESSIONCOMPRESS_FORDROPPEDREAD_ONLYSEGMENT_CREATEDRESULT_CACHECLUSTERINGACTIVITY_TRACKINGDML_TIMESTAMPHAS_IDENTITYCONTAINER_DATAINMEMORYINMEMORY_PRIORITYINMEMORY_DISTRIBUTEINMEMORY_COMPRESSIONINMEMORY_DUPLICATE
1HRDEPARTMENTSEXAMPLE VALID10 125565536104857612147483645 NON2750002100         1         1    NENABLED277/7/2014 6:56:25 AMNO NNNODEFAULTDEFAULTDEFAULTDISABLEDYESNO DISABLEDYES DISABLEDDISABLED NONOYESDEFAULTNO NONODISABLED
2HREMPLOYEESEXAMPLE VALID10 125565536104857612147483645 NON10750006900         1         1    NENABLED1077/7/2014 6:56:25 AMNO NNNODEFAULTDEFAULTDEFAULTDISABLEDYESNO DISABLEDYES DISABLEDDISABLED NONOYESDEFAULTNO NONODISABLED
3USER1EMPLOYEESEXAMPLE VALID10 125565536104857612147483645 NON10750006900         1         1    NENABLED1077/7/2014 6:56:25 AMNO NNNODEFAULTDEFAULTDEFAULTDISABLEDYESNO DISABLEDYES DISABLEDDISABLED NONOYESDEFAULTNO NONODISABLED
4USER1DEPARTMENTSUSERS VALID10 125565536104857612147483645 NON2750002100         1         1    NENABLED277/7/2014 6:56:25 AMNO NNNODEFAULTDEFAULTDEFAULTDISABLEDYESNO DISABLEDYES DISABLEDDISABLED NONOYESDEFAULTNO NONODISABLED

  1. [oracle@station90 ~]$ expdp system/oracle_4U directory=dir1 dumpfile=dir1:'sh_hr_%U.dmp',dir2:'sh_hr_%U.dmp'  schemas=sh,hr job_name=sh_hr1job  parallel=4

  2. Export: Release 12.1.0.2.0 - Production on Sat Jul 28 11:52:35 2018

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

  4. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  5. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
  6. Starting "SYSTEM"."SH_HR1JOB":  system/******** directory=dir1 dumpfile=dir1:sh_hr_%U.dmp,dir2:sh_hr_%U.dmp schemas=sh,hr job_name=sh_hr1job parallel=4
  7. Estimate in progress using BLOCKS method...
  8. Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
  9. Total estimation using BLOCKS method: 287.3 MB
  10. . . exported "HR"."T05207_B"                                 0 KB       0 rows
  11. Processing object type SCHEMA_EXPORT/USER
  12. . . exported "SH"."COSTS":"COSTS_Q1_1999"                183.7 KB    5884 rows
  13. . . exported "SH"."COSTS":"COSTS_Q1_1998"                139.6 KB    4411 rows
  14. Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
  15. . . exported "SH"."CUSTOMERS"                            10.27 MB   55500 rows
  16. . . exported "SH"."COSTS":"COSTS_Q1_2001"                228.0 KB    7328 rows
  17. . . exported "SH"."COSTS":"COSTS_Q1_2000"                120.7 KB    3772 rows
  18. Processing object type SCHEMA_EXPORT/ROLE_GRANT
  19. . . exported "SH"."COSTS":"COSTS_Q2_1998"                79.68 KB    2397 rows
  20. . . exported "SH"."COSTS":"COSTS_Q2_1999"                132.7 KB    4179 rows
  21. . . exported "SH"."COSTS":"COSTS_Q2_2000"                119.1 KB    3715 rows
  22. Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
  23. . . exported "SH"."COSTS":"COSTS_Q2_2001"                184.7 KB    5882 rows
  24. . . exported "SH"."COSTS":"COSTS_Q3_1998"                131.3 KB    4129 rows
  25. . . exported "SH"."COSTS":"COSTS_Q3_2000"                151.6 KB    4798 rows
  26. . . exported "SH"."COSTS":"COSTS_Q3_1999"                137.5 KB    4336 rows
  27. . . exported "SH"."COSTS":"COSTS_Q4_1999"                159.2 KB    5060 rows
  28. . . exported "SH"."COSTS":"COSTS_Q4_1998"                144.8 KB    4577 rows
  29. . . exported "SH"."COSTS":"COSTS_Q3_2001"                234.6 KB    7545 rows
  30. . . exported "SH"."COSTS":"COSTS_Q4_2000"                160.4 KB    5088 rows
  31. . . exported "SH"."COSTS":"COSTS_Q4_2001"                278.5 KB    9011 rows
  32. . . exported "SH"."SALES":"SALES_Q1_2000"                2.012 MB   62197 rows
  33. . . exported "SH"."SALES":"SALES_Q1_1998"                1.413 MB   43687 rows
  34. . . exported "SH"."SALES":"SALES_Q1_1999"                2.071 MB   64186 rows
  35. . . exported "SH"."SALES":"SALES_Q2_1998"                1.160 MB   35758 rows
  36. . . exported "SH"."SALES":"SALES_Q1_2001"                1.965 MB   60608 rows
  37. Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
  38. . . exported "SH"."SALES":"SALES_Q2_1999"                1.754 MB   54233 rows
  39. . . exported "SH"."SALES":"SALES_Q2_2000"                1.802 MB   55515 rows
  40. . . exported "SH"."SALES":"SALES_Q3_1998"                1.634 MB   50515 rows
  41. . . exported "SH"."SALES":"SALES_Q2_2001"                2.051 MB   63292 rows
  42. . . exported "SH"."SALES":"SALES_Q3_1999"                2.166 MB   67138 rows
  43. . . exported "SH"."SALES":"SALES_Q3_2000"                1.910 MB   58950 rows
  44. . . exported "SH"."SALES":"SALES_Q3_2001"                2.130 MB   65769 rows
  45. . . exported "SH"."SALES":"SALES_Q4_1998"                1.581 MB   48874 rows
  46. . . exported "SH"."SALES":"SALES_Q4_1999"                2.014 MB   62388 rows
  47. . . exported "SH"."SALES":"SALES_Q4_2000"                1.814 MB   55984 rows
  48. . . exported "SH"."SALES":"SALES_Q4_2001"                2.257 MB   69749 rows
  49. . . exported "SH"."SUPPLEMENTARY_DEMOGRAPHICS"           697.6 KB    4500 rows
  50. . . exported "HR"."T04209_UNAME"                         1.510 MB  100000 rows
  51. . . exported "SH"."FWEEK_PSCAT_SALES_MV"                 419.9 KB   11266 rows
  52. . . exported "SH"."PROMOTIONS"                           59.17 KB     503 rows
  53. . . exported "SH"."TIMES"                                381.7 KB    1826 rows
  54. . . exported "HR"."COUNTRIES"                            6.460 KB      25 rows
  55. . . exported "HR"."DEPARTMENTS"                          7.125 KB      27 rows
  56. . . exported "HR"."EMPLOYEES"                            17.07 KB     107 rows
  57. . . exported "HR"."JOBS"                                 7.109 KB      19 rows
  58. . . exported "HR"."JOB_HISTORY"                          7.195 KB      10 rows
  59. . . exported "HR"."LOCATIONS"                            8.437 KB      23 rows
  60. . . exported "HR"."REGIONS"                              5.546 KB       4 rows
  61. . . exported "SH"."CAL_MONTH_SALES_MV"                   6.382 KB      48 rows
  62. . . exported "SH"."CHANNELS"                             7.414 KB       5 rows
  63. . . exported "SH"."COUNTRIES"                            10.46 KB      23 rows
  64. . . exported "SH"."DIMENSION_EXCEPTIONS"                     0 KB       0 rows
  65. . . exported "SH"."PRODUCTS"                             26.71 KB      72 rows
  66. . . exported "HR"."SSMA"                                     0 KB       0 rows
  67. . . exported "HR"."SSMM"                                     0 KB       0 rows
  68. . . exported "HR"."T05207_A"                                 0 KB       0 rows
  69. . . exported "SH"."COSTS":"COSTS_1995"                       0 KB       0 rows
  70. . . exported "SH"."COSTS":"COSTS_1996"                       0 KB       0 rows
  71. . . exported "SH"."COSTS":"COSTS_H1_1997"                    0 KB       0 rows
  72. . . exported "SH"."COSTS":"COSTS_H2_1997"                    0 KB       0 rows
  73. . . exported "SH"."COSTS":"COSTS_Q1_2002"                    0 KB       0 rows
  74. . . exported "SH"."COSTS":"COSTS_Q1_2003"                    0 KB       0 rows
  75. . . exported "SH"."COSTS":"COSTS_Q2_2002"                    0 KB       0 rows
  76. . . exported "SH"."COSTS":"COSTS_Q2_2003"                    0 KB       0 rows
  77. . . exported "SH"."COSTS":"COSTS_Q3_2002"                    0 KB       0 rows
  78. . . exported "SH"."COSTS":"COSTS_Q3_2003"                    0 KB       0 rows
  79. . . exported "SH"."COSTS":"COSTS_Q4_2002"                    0 KB       0 rows
  80. . . exported "SH"."COSTS":"COSTS_Q4_2003"                    0 KB       0 rows
  81. . . exported "SH"."SALES":"SALES_1995"                       0 KB       0 rows
  82. . . exported "SH"."SALES":"SALES_1996"                       0 KB       0 rows
  83. . . exported "SH"."SALES":"SALES_H1_1997"                    0 KB       0 rows
  84. . . exported "SH"."SALES":"SALES_H2_1997"                    0 KB       0 rows
  85. . . exported "SH"."SALES":"SALES_Q1_2002"                    0 KB       0 rows
  86. . . exported "SH"."SALES":"SALES_Q1_2003"                    0 KB       0 rows
  87. . . exported "SH"."SALES":"SALES_Q2_2002"                    0 KB       0 rows
  88. . . exported "SH"."SALES":"SALES_Q2_2003"                    0 KB       0 rows
  89. . . exported "SH"."SALES":"SALES_Q3_2002"                    0 KB       0 rows
  90. . . exported "SH"."SALES":"SALES_Q3_2003"                    0 KB       0 rows
  91. . . exported "SH"."SALES":"SALES_Q4_2002"                    0 KB       0 rows
  92. . . exported "SH"."SALES":"SALES_Q4_2003"                    0 KB       0 rows
  93. Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
  94. Processing object type SCHEMA_EXPORT/TABLE/TABLE
  95. Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
  96. Processing object type SCHEMA_EXPORT/TABLE/COMMENT
  97. Processing object type SCHEMA_EXPORT/TABLE/AUDIT_OBJ
  98. Processing object type SCHEMA_EXPORT/TABLE/FGA_POLICY
  99. Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
  100. Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
  101. Processing object type SCHEMA_EXPORT/VIEW/VIEW
  102. Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
  103. Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
  104. Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
  105. Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
  106. Processing object type SCHEMA_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX
  107. Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
  108. Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
  109. Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
  110. Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
  111. Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
  112. Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
  113. Processing object type SCHEMA_EXPORT/DIMENSION
  114. Master table "SYSTEM"."SH_HR1JOB" successfully loaded/unloaded
  115. ******************************************************************************
  116. Dump file set for SYSTEM.SH_HR1JOB is:
  117.   /home/oracle/dir1/sh_hr_01.dmp
  118.   /home/oracle/dir2/sh_hr_01.dmp
  119.   /home/oracle/dir1/sh_hr_02.dmp
  120.   /home/oracle/dir2/sh_hr_02.dmp
  121. Job "SYSTEM"."SH_HR1JOB" successfully completed at Sat Jul 28 11:54:04 2018 elapsed 0 00:01:27

  122. [oracle@station90 ~]$
复制代码
  1. [oracle@station90 ~]$ impdp system/oracle_4U directory=dir1 dumpfile=dir1:'sh_hr_%U.dmp',dir2:'sh_hr_%U.dmp'  tables=sh.sales  parallel=4  remap_schema=sh:user1

  2. Import: Release 12.1.0.2.0 - Production on Sat Jul 28 11:55:36 2018

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

  4. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  5. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
  6. Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
  7. Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/******** directory=dir1 dumpfile=dir1:sh_hr_%U.dmp,dir2:sh_hr_%U.dmp tables=sh.sales parallel=4 remap_schema=sh:user1
  8. Processing object type SCHEMA_EXPORT/TABLE/TABLE




  9. ORA-39171: Job is experiencing a resumable wait.
  10. Resumable error: ORA-01658: unable to create INITIAL extent for segment in tablespace EXAMPLE
  11. Resumable stmt: CREATE TABLE "USER1"."SALES" ("PROD_ID" NUMBER NOT NULL ENABLE, "CUST_ID" NUMBER NOT NULL ENABLE, "TIME_ID" DATE NOT NULL ENABLE, "CHANNEL_ID" NUMBER NOT NULL ENABLE, "PROMO_ID" NUMBER NOT NULL ENABLE, "QUANTITY_SOLD" NUMBER(10,2) NOT NULL ENABLE, "AMOUNT_SOLD" NUMBER(10,2) NOT NULL ENABLE) PCTFREE 5 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS  NOLOGGING  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EXAMPLE"  PARTITION BY RANGE ("TIME_ID")  (PARTITION "SALES_1995"  VALUES LESS THAN (TO_DATE(' 1996-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC NOLOGGING  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EXAMPLE" , PARTITION "SALES_1996"  VALUES LESS THAN (TO_DATE(' 1997-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED PCTFREE 0 PCTUSED 40 INITRANS 1 MAX
  12. Resumable stmt status: SUSPENDED
  13. Resumable stmt start: 07/28/18 11:55:40 stmt suspend: 07/28/18 11:55:42
  14. ORA-39171: Job is experiencing a resumable wait.
  15. Resumable error: ORA-01658: unable to create INITIAL extent for segment in tablespace EXAMPLE
  16. Resumable stmt: CREATE TABLE "USER1"."SALES" ("PROD_ID" NUMBER NOT NULL ENABLE, "CUST_ID" NUMBER NOT NULL ENABLE, "TIME_ID" DATE NOT NULL ENABLE, "CHANNEL_ID" NUMBER NOT NULL ENABLE, "PROMO_ID" NUMBER NOT NULL ENABLE, "QUANTITY_SOLD" NUMBER(10,2) NOT NULL ENABLE, "AMOUNT_SOLD" NUMBER(10,2) NOT NULL ENABLE) PCTFREE 5 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS  NOLOGGING  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EXAMPLE"  PARTITION BY RANGE ("TIME_ID")  (PARTITION "SALES_1995"  VALUES LESS THAN (TO_DATE(' 1996-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC NOLOGGING  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EXAMPLE" , PARTITION "SALES_1996"  VALUES LESS THAN (TO_DATE(' 1997-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED PCTFREE 0 PCTUSED 40 INITRANS 1 MAX
  17. Resumable stmt status: SUSPENDED
  18. Resumable stmt start: 07/28/18 11:55:40 stmt suspend: 07/28/18 12:02:45
  19. Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
  20. . . imported "USER1"."SALES":"SALES_Q1_1998"             1.413 MB   43687 rows
  21. . . imported "USER1"."SALES":"SALES_Q1_1999"             2.071 MB   64186 rows
  22. . . imported "USER1"."SALES":"SALES_Q1_2000"             2.012 MB   62197 rows
  23. . . imported "USER1"."SALES":"SALES_Q1_2001"             1.965 MB   60608 rows
  24. . . imported "USER1"."SALES":"SALES_Q2_1998"             1.160 MB   35758 rows
  25. . . imported "USER1"."SALES":"SALES_Q2_1999"             1.754 MB   54233 rows
  26. . . imported "USER1"."SALES":"SALES_Q4_2001"             2.257 MB   69749 rows
  27. . . imported "USER1"."SALES":"SALES_1995"                    0 KB       0 rows
  28. . . imported "USER1"."SALES":"SALES_1996"                    0 KB       0 rows
  29. . . imported "USER1"."SALES":"SALES_Q3_2000"             1.910 MB   58950 rows
  30. . . imported "USER1"."SALES":"SALES_H1_1997"                 0 KB       0 rows
  31. . . imported "USER1"."SALES":"SALES_H2_1997"                 0 KB       0 rows
  32. . . imported "USER1"."SALES":"SALES_Q1_2002"                 0 KB       0 rows
  33. . . imported "USER1"."SALES":"SALES_Q1_2003"                 0 KB       0 rows
  34. . . imported "USER1"."SALES":"SALES_Q2_2002"                 0 KB       0 rows
  35. . . imported "USER1"."SALES":"SALES_Q2_2003"                 0 KB       0 rows
  36. . . imported "USER1"."SALES":"SALES_Q3_2002"                 0 KB       0 rows
  37. . . imported "USER1"."SALES":"SALES_Q3_2003"                 0 KB       0 rows
  38. . . imported "USER1"."SALES":"SALES_Q4_2002"                 0 KB       0 rows
  39. . . imported "USER1"."SALES":"SALES_Q4_2003"                 0 KB       0 rows
  40. . . imported "USER1"."SALES":"SALES_Q2_2000"             1.802 MB   55515 rows
  41. . . imported "USER1"."SALES":"SALES_Q3_2001"             2.130 MB   65769 rows
  42. . . imported "USER1"."SALES":"SALES_Q2_2001"             2.051 MB   63292 rows
  43. . . imported "USER1"."SALES":"SALES_Q4_1998"             1.581 MB   48874 rows
  44. . . imported "USER1"."SALES":"SALES_Q3_1998"             1.634 MB   50515 rows
  45. . . imported "USER1"."SALES":"SALES_Q4_1999"             2.014 MB   62388 rows
  46. . . imported "USER1"."SALES":"SALES_Q3_1999"             2.166 MB   67138 rows
  47. . . imported "USER1"."SALES":"SALES_Q4_2000"             1.814 MB   55984 rows
  48. Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
  49. Processing object type SCHEMA_EXPORT/TABLE/COMMENT
  50. Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
  51. ORA-39083: Object type REF_CONSTRAINT:"USER1"."SALES_PROMO_FK" failed to create with error:
  52. ORA-00942: table or view does not exist
  53. Failing sql is:
  54. ALTER TABLE "USER1"."SALES" ADD CONSTRAINT "SALES_PROMO_FK" FOREIGN KEY ("PROMO_ID") REFERENCES "USER1"."PROMOTIONS" ("PROMO_ID") ENABLE NOVALIDATE
  55. ORA-39083: Object type REF_CONSTRAINT:"USER1"."SALES_CUSTOMER_FK" failed to create with error:
  56. ORA-00942: table or view does not exist
  57. Failing sql is:
  58. ALTER TABLE "USER1"."SALES" ADD CONSTRAINT "SALES_CUSTOMER_FK" FOREIGN KEY ("CUST_ID") REFERENCES "USER1"."CUSTOMERS" ("CUST_ID") ENABLE NOVALIDATE
  59. ORA-39083: Object type REF_CONSTRAINT:"USER1"."SALES_PRODUCT_FK" failed to create with error:
  60. ORA-00942: table or view does not exist
  61. Failing sql is:
  62. ALTER TABLE "USER1"."SALES" ADD CONSTRAINT "SALES_PRODUCT_FK" FOREIGN KEY ("PROD_ID") REFERENCES "USER1"."PRODUCTS" ("PROD_ID") ENABLE NOVALIDATE
  63. ORA-39083: Object type REF_CONSTRAINT:"USER1"."SALES_TIME_FK" failed to create with error:
  64. ORA-00942: table or view does not exist
  65. Failing sql is:
  66. ALTER TABLE "USER1"."SALES" ADD CONSTRAINT "SALES_TIME_FK" FOREIGN KEY ("TIME_ID") REFERENCES "USER1"."TIMES" ("TIME_ID") ENABLE NOVALIDATE
  67. ORA-39083: Object type REF_CONSTRAINT:"USER1"."SALES_CHANNEL_FK" failed to create with error:
  68. ORA-00942: table or view does not exist
  69. Failing sql is:
  70. ALTER TABLE "USER1"."SALES" ADD CONSTRAINT "SALES_CHANNEL_FK" FOREIGN KEY ("CHANNEL_ID") REFERENCES "USER1"."CHANNELS" ("CHANNEL_ID") ENABLE NOVALIDATE
  71. Processing object type SCHEMA_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX
  72. Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
  73. Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
  74. Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
  75. Job "SYSTEM"."SYS_IMPORT_TABLE_01" completed with 7 error(s) at Sat Jul 28 12:06:42 2018 elapsed 0 00:11:04

  76. [oracle@station90 ~]$
复制代码


vvvv.png

如果不用parfile就很难把格式写清楚,
  1. [oracle@station90 ~]$ expdp user1/oracle_4U directory=dir1  dumpfile=user1.dmp schemas=user1 job_name=user1job   exclude=table: "in ('sales')", function: "in ('FUNC2')" query='employees:where department_id=10'
  2. LRM-00101: unknown parameter name 'department_id'

  3. [oracle@station90 ~]$ expdp user1/oracle_4U directory=dir1  dumpfile=user1.dmp schemas=user1 job_name=user1job   exclude=table: "in ('sales')", function: "in ('FUNC2')" query='employees:where department_id=10'
复制代码

parfile:

  1. userid=user1/oracle_4U
  2. directory=dir1
  3. dumpfile=user1.dmp
  4. schemas=user1
  5. job_name=user1job
  6. exclude=table:" in ('SALES')",function:" in ('FUNC2')"
  7. query="employees:where department_id=10"
复制代码

  1. [oracle@station90 ~]$ expdp parfile=/home/oracle/parfile.ora

  2. Export: Release 12.1.0.2.0 - Production on Sat Jul 28 15:02:19 2018

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

  4. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  5. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
  6. Starting "USER1"."USER1JOB":  user1/******** parfile=/home/oracle/parfile.ora
  7. Estimate in progress using BLOCKS method...
  8. Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
  9. Total estimation using BLOCKS method: 128 KB
  10. Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
  11. Processing object type SCHEMA_EXPORT/TABLE/TABLE
  12. Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
  13. Processing object type SCHEMA_EXPORT/TABLE/COMMENT
  14. Processing object type SCHEMA_EXPORT/TABLE/AUDIT_OBJ
  15. Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
  16. Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
  17. Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
  18. Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
  19. Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
  20. Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
  21. Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
  22. Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
  23. Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
  24. . . exported "USER1"."DEPARTMENTS"                       7.125 KB      27 rows
  25. . . exported "USER1"."EMPLOYEES"                         9.609 KB       1 rows
  26. Master table "USER1"."USER1JOB" successfully loaded/unloaded
  27. ******************************************************************************
  28. Dump file set for USER1.USER1JOB is:
  29.   /home/oracle/dir1/user1.dmp
  30. Job "USER1"."USER1JOB" successfully completed at Sat Jul 28 15:02:55 2018 elapsed 0 00:00:35

  31. [oracle@station90 ~]$
复制代码
  1. [oracle@station90 ~]$ impdp system/oracle_4U directory=dir1 dumpfile=user1.dmp schemas=user1 remap_schema=user1:user2
复制代码
  1. select  * from dba_objects where owner='USER2';
复制代码
   OWNEROBJECT_NAMESUBOBJECT_NAMEOBJECT_IDDATA_OBJECT_IDOBJECT_TYPECREATEDLAST_DDL_TIMETIMESTAMPSTATUSTEMPORARYGENERATEDSECONDARYNAMESPACEEDITION_NAMESHARINGEDITIONABLEORACLE_MAINTAINED
1USER2UPDATE_JOB_HISTORY 94097 TRIGGER7/28/2018 3:11:43 PM7/28/2018 3:11:57 PM2018-07-28:15:11:57INVALIDNNN3 NONEYN
2USER2SECURE_EMPLOYEES 94096 TRIGGER7/28/2018 3:11:43 PM7/28/2018 3:11:57 PM2018-07-28:15:11:57INVALIDNNN3 NONEYN
3USER2EMP_EMAIL_UK 9409594095INDEX7/28/2018 3:11:43 PM7/28/2018 3:11:43 PM2018-07-28:15:11:43VALIDNNN4 NONE N
4USER2DEPT_LOCATION_IX 9409494094INDEX7/28/2018 3:11:43 PM7/28/2018 3:11:43 PM2018-07-28:15:11:43VALIDNNN4 NONE N
5USER2DEPT_ID_PK 9409394093INDEX7/28/2018 3:11:43 PM7/28/2018 3:11:43 PM2018-07-28:15:11:43VALIDNNN4 NONE N
6USER2EMP_NAME_IX 9409294092INDEX7/28/2018 3:11:43 PM7/28/2018 3:11:43 PM2018-07-28:15:11:43VALIDNNN4 NONE N
7USER2EMP_MANAGER_IX 9409194091INDEX7/28/2018 3:11:43 PM7/28/2018 3:11:43 PM2018-07-28:15:11:43VALIDNNN4 NONE N
8USER2EMP_JOB_IX 9409094090INDEX7/28/2018 3:11:43 PM7/28/2018 3:11:43 PM2018-07-28:15:11:43VALIDNNN4 NONE N
9USER2EMP_DEPARTMENT_IX 9408994089INDEX7/28/2018 3:11:43 PM7/28/2018 3:11:43 PM2018-07-28:15:11:43VALIDNNN4 NONE N
10USER2EMP_EMP_ID_PK 9408894088INDEX7/28/2018 3:11:43 PM7/28/2018 3:11:43 PM2018-07-28:15:11:43VALIDNNN4 NONE N
11USER2FUNC1 94087 FUNCTION7/28/2018 3:11:42 PM7/28/2018 3:11:42 PM2018-07-28:14:53:30VALIDNNN1 NONEYN
12USER2DEPARTMENTS 9408694086TABLE7/28/2018 3:11:42 PM7/28/2018 3:11:43 PM2018-07-28:15:11:42VALIDNNN1 NONE N
13USER2EMPLOYEES 9408594085TABLE7/28/2018 3:11:42 PM7/28/2018 3:11:43 PM2018-07-28:15:11:42VALIDNNN1 NONE N

最后带网络链:
parfile:
  1. userid=user2/oracle_4U

  2. directory=dirwin
  3. network_link=dblink1

  4. version=11.2.0

  5. job_name=user1job

  6. exclude=table:" in ('SALES')",function:" in ('FUNC2')"

  7. query="user1.employees:where department_id=10"

  8. flashback_time='2018-07-28:15:22:29'
  9. remap_schema=user1:user2
复制代码


在win目的地这边:
impdp parfile=c:\data\parfile.ora

dddd.png


-------------------------------------------------------------------

datapump外部表:
  1. CREATE TABLE ext_emp_query_results

  2. ORGANIZATION EXTERNAL
  3.   (
  4.     TYPE ORACLE_DATAPUMP
  5.     DEFAULT DIRECTORY dir1
  6.     LOCATION (dir1:'emp1.exp',dir2:'emp2.exp')
  7.   )
  8. PARALLEL
  9. AS
  10. SELECT e.first_name,e.last_name,d.department_name
  11. FROM   employees e, departments d
  12. WHERE  e.department_id = d.department_id AND
  13.        d.department_name in
  14.                      ('Marketing', 'Purchasing');
复制代码

[oracle@station90 dir2]$ ls -l *exp
-rw-r----- 1 oracle oinstall 12288  7月 28 15:48 emp2.exp
[oracle@station90 dir2]$ ls -l ../dir1/*exp
-rw-r----- 1 oracle oinstall 12288  7月 28 15:48 ../dir1/emp1.exp
[oracle@station90 dir2]$

  1. [oracle@station90 dir1]$ strings  emp1.exp
  2. "HR"."U"
  3. x86_64/Linux 2.4.xx
  4. AL32UTF8
  5. 12.01.00.02.00
  6. 001:001:000001:000001
  7. i<?xml version="1.0"?>
  8. <ROWSET>
  9. <ROW>
  10.   <STRMTABLE_T>
  11.    <VERS_MAJOR>1</VERS_MAJOR>
  12.    <VERS_MINOR>0 </VERS_MINOR>
  13.    <VERS_DPAPI>3</VERS_DPAPI>
  14.    <ENDIANNESS>0</ENDIANNESS>
  15.    <CHARSET>AL32UTF8</CHARSET>
  16.    <NCHARSET>AL16UTF16</NCHARSET>
  17.    <DBTIMEZONE>+00:00</DBTIMEZONE>
  18.    <OWNER_NAME>HR</OWNER_NAME>
  19.    <NAME>EXT_EMP_QUERY_RESULTS</NAME>
  20.    <COL_LIST>
  21.      <COL_LIST_ITEM>
  22.       <COL_NUM>1</COL_NUM>
  23.       <NAME>FIRST_NAME</NAME>
  24.       <TYPE_NUM>1</TYPE_NUM>
  25.       <LENGTH>20</LENGTH>
  26.       <PRECISION_NUM>0</PRECISION_NUM>
  27.       <SCALE>0</SCALE>
  28.       <CHARSETID>873</CHARSETID>
  29.       <CHARSETFORM>1</CHARSETFORM>
  30.       <CHARLENGTH>20</CHARLENGTH>
  31.      </COL_LIST_ITEM>
  32.      <COL_LIST_ITEM>
  33.       <COL_NUM>2</COL_NUM>
  34.       <NAME>LAST_NAME</NAME>
  35.       <TYPE_NUM>1</TYPE_NUM>
  36.       <LENGTH>25</LENGTH>
  37.       <PRECISION_NUM>0</PRECISION_NUM>
  38.       <SCALE>0</SCALE>
  39.       <CHARSETID>873</CHARSETID>
  40.       <CHARSETFORM>1</CHARSETFORM>
  41.       <CHARLENGTH>25</CHARLENGTH>
  42.      </COL_LIST_ITEM>
  43.      <COL_LIST_ITEM>
  44.       <COL_NUM>3</COL_NUM>
  45.       <NAME>DEPARTMENT_NAME</NAME>
  46.       <TYPE_NUM>1</TYPE_NUM>
  47.       <LENGTH>30</LENGTH>
  48.       <PRECISION_NUM>0</PRECISION_NUM>
  49.       <SCALE>0</SCALE>
  50.       <CHARSETID>873</CHARSETID>
  51.       <CHARSETFORM>1</CHARSETFORM>
  52.       <CHARLENGTH>30</CHARLENGTH>
  53.      </COL_LIST_ITEM>
  54.    </COL_LIST>
  55.   </STRMTABLE_T>
  56. </ROW>
  57. </ROWSET>
  58. [oracle@station90 dir1]$
复制代码
  1. CREATE TABLE ext_emp_query_results
  2. (first_name  varchar2(20), last_name  varchar2(25), department_name varchar2(30))
  3. ORGANIZATION EXTERNAL
  4.   (
  5.     TYPE ORACLE_DATAPUMP
  6.     DEFAULT DIRECTORY dir1
  7.     LOCATION (dir1:'emp1.exp',dir2:'emp2.exp')
  8.   )
  9. PARALLEL
  10. ;
复制代码
  1. select  * from ext_emp_query_results;
复制代码
   FIRST_NAMELAST_NAMEDEPARTMENT_NAME
1ShelliBaidaPurchasing
2KarenColmenaresPurchasing
3PatFayMarketing
4MichaelHartsteinMarketing
5GuyHimuroPurchasing
6AlexanderKhooPurchasing
7DenRaphaelyPurchasing
8SigalTobiasPurchasing




回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-28 18:28 , Processed in 0.041214 second(s), 27 queries .

快速回复 返回顶部 返回列表