课程第54次（2017-07-15星期六上午）

botang

上完1Z0-052第17章导入和导出
1Z0-052共19章（上完17章），1Z0-053共21章（上完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. 
   
3. 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. 
   
3. impdp system/oracle_4U attach=imp_my_full2
   

复制代码

1. SQL>create database link dblink1  connect to system identified by  oracle_4U  using 'orcl';
   
2. 
   
3. Database link created.
   
4. 
   
5. 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. 
   
3. Import: Release 12.1.0.2.0 - Production on Sat Jul 15 11:23:47 2017
   
4. 
   
5. Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
   
6. 
   
7. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
   
8. With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
   
9. and Real Application Testing options
   
10. Legacy Mode Active due to the following parameters:
    
11. Legacy Mode Parameter: "fromuser=hr" Location: Command Line, Replaced with: "remap_schema"
    
12. Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/******** directory=dira network_link=dblink1 tables=hr.employees remap_schema=hr:user3
    
13. Estimate in progress using BLOCKS method...
    
14. Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
    
15. 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. 
    
17. CREATE TABLE user3.ext
    
18. (  first_name  varchar2(20)   ,last_name  varchar2(20)   ,department_name    varchar2(30) )
    
19. ORGANIZATION EXTERNAL
    
20.   (
    
21.     TYPE ORACLE_DATAPUMP
    
22.     DEFAULT DIRECTORY dira
    
23.     LOCATION (dira:'emp1.dmp',dirb:'emp2.dmp',dirb:'emp3.dmp')
    
24.   );
    

复制代码