Bo's Oracle Station

查看: 2571|回复: 0

课程第54次(2017-07-15星期六上午)

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2017-7-15 10:02:59 | 显示全部楼层 |阅读模式
上完1Z0-052第17章导入和导出
1Z0-052
19章(上完17章),1Z0-05321章(上完17章)和1Z0-063多租户部分共9章(上完0章)
总共上完全部49章中的34
  1. expdp system/oracle_4U  directory=dir1  dumpfile=dir1:my_full2_%U,dir2:my_full2_%U     full=y parallel=10 job_name=my_full2

  2. expdp  system/oracle_4U attach=my_full2
复制代码
  1. impdp system/oracle_4U  directory=dir1 dumpfile=dir1:my_full2_%U,dir2:my_full2_%U   schemas=hr remap_schema=hr:user1  parallel=8 job_name=imp_my_full2

  2. impdp system/oracle_4U attach=imp_my_full2
复制代码
  1. SQL>create database link dblink1  connect to system identified by  oracle_4U  using 'orcl';

  2. Database link created.

  3. SQL>select  * from employees@dblink1;
复制代码
  1. impdp system/oracle_4U  directory=dira  network_link=dblink1  schemas=hr  remap_tablespace=tbs1:users remap_schema=hr:user2
复制代码
  1. SQL> conn hr/oracle_4U
  2. Connected.
  3. SQL> create database link  mydblink connect to hr identified by oracle_4U  using 'orcl';
复制代码
在命令行:
  1. expdp hr/oracle_4U directory=dir1 dumpfile=my.dmp  schemas=hr job_name=my_job  exclude=table:"in \(\'T_BANK_INVOICE\'\)", procedure:" in \(\'SECURE_DML\'\)"  "query='employees:where department_id=90'"
复制代码
parfile:

  1. directory=dira
  2. network_link=mydblink
  3. schemas=hr
  4. job_name=myjob
  5. include=table:"in ('T_BANK_INVOICE','EMPLOYEES2')", procedure:"in ('PROC2')"
  6. query='employees2:where department_id=90'
  7. flashback_time='2017-07-15:10:41:02'
  8. content=data_only
复制代码
  1. select   o.object_name,
  2.     o.object_type  from dba_objects o
  3.   where o.owner='HR'
  4.   order by 2;
复制代码
  1. impdp  system/oracle_4U  directory=dira  network_link=dblink1  tablespaces=tbs1   remap_Schema=hr:user3  remap_tablespace=tbs1:users
复制代码
  1. expdp system/oracle_4U directory=dir1   dumpfiles=dir1:file_%U,dir2:file_%U   schemas=hr filesize=1M   parallel=20
复制代码
  1. impdp system/oracle_4U directory=dira  network_link=dblink1  tables=hr.employees  fromuser=hr  touser=user3     

  2. Import: Release 12.1.0.2.0 - Production on Sat Jul 15 11:23:47 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, Automatic Storage Management, OLAP, Advanced Analytics
  6. and Real Application Testing options
  7. Legacy Mode Active due to the following parameters:
  8. Legacy Mode Parameter: "fromuser=hr" Location: Command Line, Replaced with: "remap_schema"
  9. Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/******** directory=dira network_link=dblink1 tables=hr.employees remap_schema=hr:user3
  10. Estimate in progress using BLOCKS method...
  11. Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
  12. Total estimation using BLOCKS method: 64 KB
复制代码
  1. CREATE TABLE hr.ext_emp_query_results
  2. ORGANIZATION EXTERNAL
  3.   (
  4.     TYPE ORACLE_DATAPUMP
  5.     DEFAULT DIRECTORY dir1
  6.     LOCATION (dir1:'emp1.dmp',dir2:'emp2.dmp',dir2:'emp3.dmp')
  7.   )
  8. PARALLEL 3
  9. AS
  10. SELECT e.first_name,e.last_name,d.department_name
  11. FROM   hr.employees e, hr.departments d
  12. WHERE  e.department_id = d.department_id AND
  13.        d.department_name in
  14.                      ('Marketing', 'Purchasing');
  15. -----

  16. CREATE TABLE user3.ext
  17. (  first_name  varchar2(20)   ,last_name  varchar2(20)   ,department_name    varchar2(30) )
  18. ORGANIZATION EXTERNAL
  19.   (
  20.     TYPE ORACLE_DATAPUMP
  21.     DEFAULT DIRECTORY dira
  22.     LOCATION (dira:'emp1.dmp',dirb:'emp2.dmp',dirb:'emp3.dmp')
  23.   );
复制代码






回复

使用道具 举报

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

本版积分规则

QQ|手机版|Bo's Oracle Station   

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

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