Bo's Oracle Station

查看: 2278|回复: 0

课程第56次(2018-06-04星期一)

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2018-6-4 19:58:26 | 显示全部楼层 |阅读模式
数据泵大作业中断:
  1. [oracle@station90 ~]$ expdp system/oracle_4U@pdb2 attach=myfull

  2. Export: Release 12.1.0.2.0 - Production on Mon Jun 4 19:42:41 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, Real Application Testing
  6. and Unified Auditing options

  7. Job: MYFULL
  8.   Owner: SYSTEM                        
  9.   Operation: EXPORT                        
  10.   Creator Privs: TRUE                           
  11.   GUID: 6DD0639E4BFD2D2EE0535A00A8C0B771
  12.   Start Time: Monday, 04 June, 2018 19:42:42
  13.   Mode: FULL                           
  14.   Instance: cdb2
  15.   Max Parallelism: 1
  16.   Timezone: -07:00
  17.   Timezone version: 18
  18.   Endianness: LITTLE
  19.   NLS character set: AL32UTF8
  20.   NLS NCHAR character set: AL16UTF16
  21.   EXPORT Job Parameters:
  22.   Parameter Name      Parameter Value:
  23.      CLIENT_COMMAND        system/********@pdb2 directory=dir1 dumpfile=full.dmp full=y job_name=myfull
  24.   State: IDLING                        
  25.   Bytes Processed: 0
  26.   Current Parallelism: 1
  27.   Job Error Count: 0
  28.   Dump File: /home/oracle/dir1/full.dmp
  29.     bytes written: 4,096
  30.   
  31. Worker 1 Status:
  32.   Instance ID: 1
  33.   Instance name: cdb2
  34.   Host name: station90.example.com
  35.   Process Name: DW00
  36.   State: UNDEFINED                     

  37. Export> status_job
  38. UDE-00053: unrecognized client command 'status_job'

  39. Export> status

  40. Job: MYFULL
  41.   Operation: EXPORT                        
  42.   Mode: FULL                           
  43.   State: IDLING                        
  44.   Bytes Processed: 0
  45.   Current Parallelism: 1
  46.   Job Error Count: 0
  47.   Dump File: /home/oracle/dir1/full.dmp
  48.     bytes written: 4,096
  49.   
  50. Worker 1 Status:
  51.   Instance ID: 1
  52.   Instance name: cdb2
  53.   Host name: station90.example.com
  54.   Process Name: DW00
  55.   State: UNDEFINED                     

  56. Export> start_job

  57. Export> status

  58. Job: MYFULL
  59.   Operation: EXPORT                        
  60.   Mode: FULL                           
  61.   State: EXECUTING                     
  62.   Bytes Processed: 0
  63.   Current Parallelism: 1
  64.   Job Error Count: 0
  65.   Dump File: /home/oracle/dir1/full.dmp
  66.     bytes written: 106,496
  67.   
  68. Worker 1 Status:
  69.   Instance ID: 1
  70.   Instance name: cdb2
  71.   Host name: station90.example.com
  72.   Process Name: DW00
  73.   State: EXECUTING                     
  74.   Object Schema: SYS
  75.   Object Name: FGA_LOG$FOR_EXPORT
  76.   Object Type: DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
  77.   Completed Objects: 7
  78.   Worker Parallelism: 1

  79. Export> exit
复制代码
  1. select  * from cdb_directories;

  2. select  * from cdb_datapump_jobs;

  3. select  * from system.myfull;
复制代码

导入:

  1. [oracle@station90 ~]$ impdp system/oracle_4U directory=dir1  dumpfile=full.dmp tables=hr.t04209_uname

  2. Import: Release 12.1.0.2.0 - Production on Mon Jun 4 19:56: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. Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
  7. Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/******** directory=dir1 dumpfile=full.dmp tables=hr.t04209_uname
  8. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
  9. ORA-39083: Object type TABLE:"HR"."T04209_UNAME" failed to create with error:
  10. ORA-00959: tablespace 'LDATA' does not exist
  11. Failing sql is:
  12. CREATE TABLE "HR"."T04209_UNAME" ("UNAME" VARCHAR2(60 BYTE), "UVALUE" NUMBER(9,0)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "LDATA"
  13. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
  14. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/AUDIT_OBJ
  15. ORA-39112: Dependent object type AUDIT_OBJ skipped, base object type TABLE:"HR"."T04209_UNAME" creation failed
  16. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
  17. Processing object type DATABASE_EXPORT/STATISTICS/MARKER
  18. Job "SYSTEM"."SYS_IMPORT_TABLE_01" completed with 2 error(s) at Mon Jun 4 19:56:29 2018 elapsed 0 00:00:20

  19. [oracle@station90 ~]$ impdp system/oracle_4U directory=dir1  dumpfile=full.dmp tables=hr.t04209_uname remap_tablespace=ldata:users

  20. Import: Release 12.1.0.2.0 - Production on Mon Jun 4 19:57:21 2018

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

  22. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  23. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
  24. Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
  25. Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/******** directory=dir1 dumpfile=full.dmp tables=hr.t04209_uname remap_tablespace=ldata:users
  26. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
  27. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
  28. . . imported "HR"."T04209_UNAME"                         1.510 MB  100000 rows
  29. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/AUDIT_OBJ
  30. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
  31. Processing object type DATABASE_EXPORT/STATISTICS/MARKER
  32. Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Mon Jun 4 19:57:46 2018 elapsed 0 00:00:21

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

第二个例子:
expparfile.txt:
  1. schemas=hr
  2. directory=dir1
  3. dumpfile=dir1:my1%U.dmp,dir2:my2%U.dmp
  4. version=11.2
  5. filesize=1M
  6. parallel=4
  7. sample=t04209_uname:90
  8. job_name=myschema
  9. exclude=table:"in ('T_BANK_INVOICE')", procedure:"in ('PROC2')"
  10. query='employees:where employee_id<>102'
  11. flashback_time='2018-06-04:20:15:42'
复制代码
  1. [oracle@station90 ~]$ expdp hr/oracle_4U@pdb2  parfile=/home/oracle/expparfile.txt

  2. Export: Release 12.1.0.2.0 - Production on Mon Jun 4 20:24:27 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, Real Application Testing
  6. and Unified Auditing options
  7. Starting "HR"."MYSCHEMA":  hr/********@pdb2 parfile=/home/oracle/expparfile.txt
  8. Estimate in progress using BLOCKS method...
  9. Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
  10. Total estimation using BLOCKS method: 2.049 MB
  11. . . exported "HR"."T05207_A"                             5.031 KB       0 rows
  12. . . exported "HR"."T05207_M"                             5.046 KB       1 rows
  13. . . exported "HR"."T06303_A"                             5.046 KB       1 rows
  14. . . exported "HR"."T05207_B"                                 0 KB       0 rows
  15. Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
  16. . . exported "HR"."T04209_UNAME"                         1.359 MB   89989 rows
  17. . . exported "HR"."EMPLOYEES"                            6.875 KB       2 rows
  18. Processing object type SCHEMA_EXPORT/TABLE/TABLE
  19. Processing object type SCHEMA_EXPORT/TABLE/COMMENT
  20. Processing object type SCHEMA_EXPORT/TABLE/AUDIT_OBJ
  21. Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
  22. Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
  23. Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
  24. Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
  25. Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
  26. Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
  27. Master table "HR"."MYSCHEMA" successfully loaded/unloaded
  28. ******************************************************************************
  29. Dump file set for HR.MYSCHEMA is:
  30.   /home/oracle/dir1/my101.dmp
  31.   /home/oracle/dir2/my201.dmp
  32.   /home/oracle/dir1/my102.dmp
  33.   /home/oracle/dir2/my202.dmp
  34. Job "HR"."MYSCHEMA" successfully completed at Mon Jun 4 20:24:50 2018 elapsed 0 00:00:23

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

[oracle@station90 dir2]$ ls
extab2.dat  my201.dmp  my202.dmp
[oracle@station90 dir2]$ mv my201.dmp  my202.dmp   /home/oracle/dir1/

impparfile.txt:
  1. schemas=hr
  2. directory=dir1
  3. dumpfile=my1%U.dmp,my2%U.dmp
  4. parallel=4
  5. remap_table=employees:emp2
  6. remap_tablespace=ldata:users
复制代码
  1. [oracle@station90 ~]$ impdp hr/oracle_4U  parfile=impparfile.txt

  2. Import: Release 11.2.0.3.0 - Production on Mon Jun 4 20:30:35 2018

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

  4. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
  5. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
  6. and Real Application Testing options
  7. Master table "HR"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
  8. Starting "HR"."SYS_IMPORT_SCHEMA_01":  hr/******** parfile=impparfile.txt
  9. Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
  10. Processing object type SCHEMA_EXPORT/TABLE/TABLE
  11. ORA-39083: Object type TABLE:"HR"."T05207_M" failed to create with error:
  12. ORA-00959: tablespace 'TBS05207_M' does not exist
  13. Failing sql is:
  14. CREATE TABLE "HR"."T05207_M" ("A" NUMBER) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "TBS05207_M"
  15. ORA-39083: Object type TABLE:"HR"."EXTAB" failed to create with error:
  16. ORA-06564: object DIR2 does not exist
  17. Failing sql is:
  18. CREATE TABLE "HR"."EXTAB" ("EMPLOYEE_ID" NUMBER(4,0), "FIRST_NAME" VARCHAR2(20 BYTE), "LAST_NAME" VARCHAR2(25 BYTE), "HIRE_DATE" DATE) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY "DIR1" ACCESS PARAMETERS ( records delimited by newline
  19.          badfile dir1:'extab%a_%p.bad'
  20.          logfile dir1:'extab%a_%p.log'
  21.          fields terminated by ','
  22.          missing field
  23. Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
  24. . . imported "HR"."T04209_UNAME"                         1.359 MB   89989 rows
  25. . . imported "HR"."EMP2"                                 6.875 KB       2 rows
  26. . . imported "HR"."T05207_A"                             5.031 KB       0 rows
  27. . . imported "HR"."T06303_A"                             5.046 KB       1 rows
  28. . . imported "HR"."T05207_B"                                 0 KB       0 rows
  29. Processing object type SCHEMA_EXPORT/TABLE/AUDIT_OBJ
  30. Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
  31. Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
  32. Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
  33. ORA-39112: Dependent object type INDEX:"HR"."I05207_M" skipped, base object type TABLE:"HR"."T05207_M" creation failed
  34. Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
  35. ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"HR"."I05207_M" creation failed
  36. Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
  37. ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"HR"."EXTAB" creation failed
  38. ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"HR"."T05207_M" creation failed
  39. Job "HR"."SYS_IMPORT_SCHEMA_01" completed with 6 error(s) at 20:30:39

  40. [oracle@station90 ~]$
复制代码
第三个例子:
netparfile:

  1. schemas=hr
  2. directory=dir1
  3. network_link=mylink
  4. version=11.2
  5. parallel=4
  6. job_name=myschema
  7. exclude=table:"in ('T_BANK_INVOICE')", procedure:"in ('PROC2')"
  8. query='employees:where employee_id<>102'
  9. flashback_time='2018-06-04:20:15:42'
  10. remap_tablespace=ldata:users
复制代码

数据库链:
  1. SQL> conn hr/oracle_4U
  2. Connected.
  3. SQL> create database link mylink connect to hr identified by oracle_4U using 'pdb2';
复制代码
  1. [oracle@station90 ~]$ impdp hr/oracle_4U  parfile=netparfile.txt

  2. Import: Release 11.2.0.3.0 - Production on Mon Jun 4 21:04:45 2018

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

  4. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
  5. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
  6. and Real Application Testing options
  7. Starting "HR"."MYSCHEMA":  hr/******** parfile=netparfile.txt
  8. Estimate in progress using BLOCKS method...
  9. Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
  10. Total estimation using BLOCKS method: 2.25 MB
  11. Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
  12. Processing object type SCHEMA_EXPORT/TABLE/TABLE
  13. ORA-39083: Object type TABLE:"HR"."T05207_M" failed to create with error:
  14. ORA-00959: tablespace 'TBS05207_M' does not exist
  15. Failing sql is:
  16. CREATE TABLE "HR"."T05207_M" ("A" NUMBER) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "TBS05207_M"
  17. ORA-39083: Object type TABLE:"HR"."EXTAB" failed to create with error:
  18. ORA-06564: object DIR2 does not exist
  19. Failing sql is:
  20. CREATE TABLE "HR"."EXTAB" ("EMPLOYEE_ID" NUMBER(4,0), "FIRST_NAME" VARCHAR2(20 BYTE), "LAST_NAME" VARCHAR2(25 BYTE), "HIRE_DATE" DATE) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY "DIR1" ACCESS PARAMETERS ( records delimited by newline
  21.          badfile dir1:'extab%a_%p.bad'
  22.          logfile dir1:'extab%a_%p.log'
  23.          fields terminated by ','
  24.          missing field
  25. . . imported "HR"."T04209_UNAME"                         100000 rows
  26. . . imported "HR"."T05207_B"                                  0 rows
  27. . . imported "HR"."T05207_A"                                  0 rows
  28. . . imported "HR"."T06303_A"                                  1 rows
  29. . . imported "HR"."EMPLOYEES"                                 2 rows
  30. Processing object type SCHEMA_EXPORT/TABLE/AUDIT_OBJ
  31. Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
  32. Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
  33. Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
  34. ORA-39112: Dependent object type INDEX:"HR"."I05207_M" skipped, base object type TABLE:"HR"."T05207_M" creation failed
  35. Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
  36. ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"HR"."I05207_M" creation failed
  37. Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
  38. ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"HR"."T05207_M" creation failed
  39. ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"HR"."EXTAB" creation failed
  40. Job "HR"."MYSCHEMA" completed with 6 error(s) at 21:04:52

  41. [oracle@station90 ~]$ sqlplus /nolog

  42. SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 4 21:04:59 2018

  43. Copyright (c) 1982, 2011, Oracle.  All rights reserved.

  44. SQL> conn hr/oracle_4U
  45. Connected.
  46. SQL> select  * from t06303_a;

  47.          A
  48. ----------
  49.          1

  50. SQL> select  * from employees;

  51. EMPLOYEE_ID EMAIL                     SALARY COMMISSION_PCT DEPARTMENT_ID
  52. ----------- -------------------- ---------- -------------- -------------
  53.         100 XXX                       30000                              90
  54.         101 YYY                       14000                              90

  55. SQL> select  object_name from user_objects where object_type='PROCEDURE';

  56. OBJECT_NAME
  57. --------------------------------------------------------------------------------
  58. PROC1

  59. SQL>
复制代码
数据泵外部表的“导出”:

  1. CREATE TABLE ext_emp_query_results
  2. ORGANIZATION EXTERNAL
  3.   (
  4.     TYPE ORACLE_DATAPUMP
  5.     DEFAULT DIRECTORY dir1
  6.     LOCATION (dir1:'emp1.dmp',dir2:'emp2.dmp')
  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');


  15. select  * from user_external_tables;

  16. select  * from user_external_locations;

  17. select  * from user_tab_columns tc
  18. where tc.table_name='EXT_EMP_QUERY_RESULTS'
  19. order by tc.COLUMN_ID;
复制代码
[oracle@station90 dir2]$ ls -l em*
-rw-r----- 1 oracle asmadmin 12288  6月  4 21:20 emp2.dmp
[oracle@station90 dir2]$ ls -l em*
-rw-r----- 1 oracle asmadmin 12288  6月  4 21:20 emp2.dmp
[oracle@station90 dir2]$ cd ..
[oracle@station90 ~]$ cd dir1
[oracle@station90 dir1]$ ls -l em*
-rw-r----- 1 oracle asmadmin 12288  6月  4 21:20 emp1.dmp
[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.dmp',dir2:'emp2.dmp')
  8.   )
  9. PARALLEL ;

  10. select  * from ext_emp_query_results;
复制代码










回复

使用道具 举报

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

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-28 18:05 , Processed in 0.041014 second(s), 24 queries .

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