Bo's Oracle Station

查看: 2063|回复: 0

课程第58次(2017-11-23星期四)

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2017-11-23 19:56:43 | 显示全部楼层 |阅读模式
暂停和继续作业:
  1. [oracle@station26 ~]$ expdp system/oracle_4U  parfile=my.txt

  2. Export: Release 12.1.0.2.0 - Production on Wed Dec 13 19:53:52 2017

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

  4. UDE-28002: operation generated ORACLE error 28002
  5. ORA-28002: the password will expire within 2 days

  6. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  7. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
  8. Starting "SYSTEM"."MY_JOB":  system/******** parfile=my.txt
  9. Estimate in progress using BLOCKS method...
  10. Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
  11. Total estimation using BLOCKS method: 4.172 GB
  12. Processing object type SCHEMA_EXPORT/USER
  13. Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
  14. Processing object type SCHEMA_EXPORT/ROLE_GRANT
  15. Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
  16. Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
  17. Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
  18. ^C
  19. Export> stop_job
  20. Are you sure you wish to stop this job ([yes]/no): yes

  21. [oracle@station26 ~]$ expdp  system/oracle_4U  attach=my_job

  22. Export: Release 12.1.0.2.0 - Production on Wed Dec 13 19:54:28 2017

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

  24. UDE-28002: operation generated ORACLE error 28002
  25. ORA-28002: the password will expire within 2 days

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

  28. Job: MY_JOB
  29.   Owner: SYSTEM                        
  30.   Operation: EXPORT                        
  31.   Creator Privs: TRUE                           
  32.   GUID: 60386BD9F3C2356FE0531A00A8C0EF12
  33.   Start Time: Wednesday, 13 December, 2017 19:53:53
  34.   Mode: SCHEMA                        
  35.   Instance: orcl
  36.   Max Parallelism: 1
  37.   Timezone: +00:00
  38.   Timezone version: 18
  39.   Endianness: LITTLE
  40.   NLS character set: AL32UTF8
  41.   NLS NCHAR character set: AL16UTF16
  42.   EXPORT Job Parameters:
  43.   Parameter Name      Parameter Value:
  44.      CLIENT_COMMAND        system/******** parfile=my.txt         
  45.      FLASHBACK_TIME        17-DEC-13 07:39:58 PM                  
  46.   State: STOP PENDING                  
  47.   Bytes Processed: 0
  48.   Current Parallelism: 1
  49.   Job Error Count: 0
  50.   Dump File: /home/oracle/mydir/my.dmp
  51.     bytes written: 4,096
  52.   
  53. Worker 1 Status:
  54.   Instance ID: 1
  55.   Instance name: orcl
  56.   Host name: station26.example.com
  57.   Process Name: DW00
  58.   State: EXECUTING                     
  59.   Object Schema: HR
  60.   Object Name: SCHEDULER_TEST_SEQ
  61.   Object Type: SCHEMA_EXPORT/SEQUENCE/SEQUENCE
  62.   Completed Objects: 4
  63.   Worker Parallelism: 1

  64. Export>
复制代码

Screenshot.png

带并行度地(多个目录对象,限制导出文件大小)导出:
  1. expdp system/oracle_4U directory=mydir dumpfile=mydir:mydir_%U.dmp,mydir2:mydir2_%U.dmp filesize=100M full=y  parallel=20
复制代码
impdp可以局部调dumpfile中的内容,有必要时要remap_schema,remap_tablespace, remap_table:

  1. [oracle@station37 mydir]$ impdp system/oracle_4U  directory=mydir dumpfile=my.dmp tables=user4.t_bank_invoice remap_schema=user4:hr

  2. Import: Release 11.2.0.4.0 - Production on Thu Nov 23 20:34:23 2017

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

  4. UDI-28002: operation generated ORACLE error 28002
  5. ORA-28002: the password will expire within 7 days

  6. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  7. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
  8. and Real Application Testing options
  9. Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
  10. Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/******** directory=mydir dumpfile=my.dmp tables=user4.t_bank_invoice remap_schema=user4:hr
  11. Processing object type SCHEMA_EXPORT/TABLE/TABLE
  12. Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
  13. . . imported "HR"."T_BANK_INVOICE"                       10.96 KB      10 rows
  14. Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
  15. Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Thu Nov 23 20:34:26 2017 elapsed 0 00:00:02
复制代码
采样扫描:
  1. expdp hr/oracle_4U directory=mydir dumpfile=mydir:a.dmp   tables=countries,jobs  sample=10
复制代码
  1. [oracle@station26 mydir]$ expdp hr/oracle_4U directory=mydir dumpfile=mydir:a.dmp   tables=t_big,t_bank_invoice   sample=10

  2. Export: Release 12.1.0.2.0 - Production on Wed Dec 13 20:57:43 2017

  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 "HR"."SYS_EXPORT_TABLE_04":  hr/******** directory=mydir dumpfile=mydir:a.dmp tables=t_big,t_bank_invoice sample=10
  7. Estimate in progress using BLOCKS method...
  8. Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
  9. Total estimation using BLOCKS method: 211.8 MB
  10. Processing object type TABLE_EXPORT/TABLE/TABLE
  11. Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
  12. Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
  13. Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
  14. Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
  15. . . exported "HR"."T_BIG"                                16.82 MB  130305 rows
  16. . . exported "HR"."T_BANK_INVOICE"                       10.34 KB       0 rows
  17. Master table "HR"."SYS_EXPORT_TABLE_04" successfully loaded/unloaded
  18. ******************************************************************************
  19. Dump file set for HR.SYS_EXPORT_TABLE_04 is:
  20.   /home/oracle/mydir/a.dmp
  21. Job "HR"."SYS_EXPORT_TABLE_04" successfully completed at Wed Dec 13 20:58:11 2017 elapsed 0 00:00:27

  22. [oracle@station26 mydir]$ expdp hr/oracle_4U directory=mydir dumpfile=mydir:b.dmp   tables=t_big,t_bank_invoice   sample=hr.t_big:10

  23. Export: Release 12.1.0.2.0 - Production on Wed Dec 13 20:58:50 2017

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

  25. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  26. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
  27. Starting "HR"."SYS_EXPORT_TABLE_04":  hr/******** directory=mydir dumpfile=mydir:b.dmp tables=t_big,t_bank_invoice sample=hr.t_big:10
  28. Estimate in progress using BLOCKS method...
  29. Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
  30. Total estimation using BLOCKS method: 211.8 MB
  31. Processing object type TABLE_EXPORT/TABLE/TABLE
  32. Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
  33. Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
  34. Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
  35. Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
  36. . . exported "HR"."T_BIG"                                16.83 MB  130982 rows
  37. . . exported "HR"."T_BANK_INVOICE"                       10.96 KB      10 rows
  38. Master table "HR"."SYS_EXPORT_TABLE_04" successfully loaded/unloaded
  39. ******************************************************************************
  40. Dump file set for HR.SYS_EXPORT_TABLE_04 is:
  41.   /home/oracle/mydir/b.dmp
  42. Job "HR"."SYS_EXPORT_TABLE_04" successfully completed at Wed Dec 13 20:58:59 2017 elapsed 0 00:00:08

  43. [oracle@station26 mydir]$ sqlplus /nolog

  44. SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 13 20:59:07 2017

  45. Copyright (c) 1982, 2014, Oracle.  All rights reserved.

  46. SQL> conn hr/oracle_4U
  47. Connected.
  48. SQL> select  count(*) from t_big;

  49.   COUNT(*)
  50. ----------
  51.    1305388

  52. SQL>
复制代码
非传送表空间,不会报自包含集错误:
  1. [oracle@station26 mydir]$ expdp system/oracle_4U directory=mydir dumpfile=mydir:c.dmp   tablespaces=tbsutf  version=11.2.0.4
  2. Export: Release 12.1.0.2.0 - Production on Wed Dec 13 21:10:44 2017

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

  4. UDE-28002: operation generated ORACLE error 28002
  5. ORA-28002: the password will expire within 2 days

  6. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  7. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
  8. Starting "SYSTEM"."SYS_EXPORT_TABLESPACE_01":  system/******** directory=mydir dumpfile=mydir:c.dmp tablespaces=tbsutf version=11.2.0.4
  9. Estimate in progress using BLOCKS method...
  10. Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
  11. Total estimation using BLOCKS method: 64 KB
  12. Processing object type TABLE_EXPORT/TABLE/TABLE
  13. . . exported "HR"."TUTF"                                 5.046 KB       1 rows
  14. Master table "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
  15. ******************************************************************************
  16. Dump file set for SYSTEM.SYS_EXPORT_TABLESPACE_01 is:
  17.   /home/oracle/mydir/c.dmp
  18. Job "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully completed at Wed Dec 13 21:10:56 2017 elapsed 0 00:00:10

  19. [oracle@station26 mydir]$
复制代码
非传送表空间在目的地是不会建这个表空间的,它是表定义导出了而已:
  1. [oracle@station37 ~]$ impdp system/oracle_4U directory=mydir  dumpfile=c.dmp                  

  2. Import: Release 11.2.0.4.0 - Production on Thu Nov 23 20:57:02 2017

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

  4. UDI-28002: operation generated ORACLE error 28002
  5. ORA-28002: the password will expire within 7 days

  6. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  7. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
  8. and Real Application Testing options
  9. Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
  10. Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=mydir dumpfile=c.dmp
  11. Processing object type TABLE_EXPORT/TABLE/TABLE
  12. ORA-39083: Object type TABLE:"HR"."TUTF" failed to create with error:
  13. ORA-00959: tablespace 'TBSUTF' does not exist
  14. Failing sql is:
  15. CREATE TABLE "HR"."TUTF" ("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 "TBSUTF"
  16. Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
  17. Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Thu Nov 23 20:57:04 2017 elapsed 0 00:00:01
复制代码
  1. [oracle@station37 ~]$ impdp system/oracle_4U directory=mydir  dumpfile=c.dmp   remap_tablespace=tbsutf:users

  2. Import: Release 11.2.0.4.0 - Production on Thu Nov 23 20:59:42 2017

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

  4. UDI-28002: operation generated ORACLE error 28002
  5. ORA-28002: the password will expire within 7 days

  6. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  7. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
  8. and Real Application Testing options
  9. Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
  10. Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=mydir dumpfile=c.dmp remap_tablespace=tbsutf:users
  11. Processing object type TABLE_EXPORT/TABLE/TABLE
  12. Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
  13. . . imported "HR"."TUTF"                                 5.046 KB       1 rows
  14. Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Thu Nov 23 20:59:44 2017 elapsed 0 00:00:01

  15. [oracle@station37 ~]$
复制代码
关于content=data_only:
  1. expdp hr/oracle_4U directory=mydir dumpfile=mydir:d.dmp   tables=t_big  version=11.2.0.4 content=data_only
复制代码
导入:
  1. SQL> conn user4/oracle_4U
  2. Connected.
  3. SQL> select  count(*)  from t_big;

  4.   COUNT(*)
  5. ----------
  6.    1305388

  7. SQL> exit
  8. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  9. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
  10. and Real Application Testing options
  11. [oracle@station37 ~]$ impdp system/oracle_4U directory=mydir  dumpfile=d.dmp   remap_schema=hr:user4

  12. Import: Release 11.2.0.4.0 - Production on Thu Nov 23 21:08:24 2017

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

  14. UDI-28002: operation generated ORACLE error 28002
  15. ORA-28002: the password will expire within 7 days

  16. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  17. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
  18. and Real Application Testing options
  19. Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
  20. Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=mydir dumpfile=d.dmp remap_schema=hr:user4
  21. Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
  22. . . imported "USER4"."T_BIG"                             167.9 MB 1305388 rows
  23. Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Thu Nov 23 21:08:33 2017 elapsed 0 00:00:08

  24. [oracle@station37 ~]$ sqlplus /nolog

  25. SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 23 21:08:40 2017

  26. Copyright (c) 1982, 2013, Oracle.  All rights reserved.

  27. SQL> conn user4/oracle_4U
  28. Connected.
  29. SQL> select  count(*)  from t_big;

  30.   COUNT(*)
  31. ----------
  32.    2610776

  33. SQL>
复制代码
关于legacy模式:
  1. [oracle@station37 ~]$ impdp  system/oracle_4U directory=mydir dumpfile=c.dmp   fromuser=hr touser=user5   remap_tablespace=tbsutf:users

  2. Import: Release 11.2.0.4.0 - Production on Thu Nov 23 21:30:53 2017

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

  4. UDI-28002: operation generated ORACLE error 28002
  5. ORA-28002: the password will expire within 7 days

  6. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  7. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
  8. and Real Application Testing options
  9. Legacy Mode Active due to the following parameters:
  10. Legacy Mode Parameter: "fromuser=hr" Location: Command Line, Replaced with: "remap_schema"
  11. Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
  12. Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=mydir dumpfile=c.dmp remap_schema=hr:user5 remap_tablespace=tbsutf:users
  13. Processing object type TABLE_EXPORT/TABLE/TABLE
  14. Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
  15. . . imported "USER5"."TUTF"                              5.046 KB       1 rows
  16. Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Thu Nov 23 21:30:55 2017 elapsed 0 00:00:01
复制代码










回复

使用道具 举报

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

本版积分规则

QQ|手机版|Bo's Oracle Station   

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

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