课程第10次（2018-10-25星期四）

botang

课程第10次（2018-10-25星期四）
SYS：

1. grant create public database link to hr;
   
2. 
   
3. alter user sh identified by oracle_4U account unlock;
   
4. 
   
5. drop public database link dblink1;

复制代码

1. [oracle@station90 ~]$ sqlplus /nolog
   
2. 
   
3. SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 25 19:26:02 2018
   
4. 
   
5. Copyright (c) 1982, 2011, Oracle.  All rights reserved.
   
6. 
   
7. SQL> conn hr/oracle_4U
   
8. Connected.
   
9. SQL> select salary from employees where employee_id=100;
   
10. 
    
11.     SALARY
    
12. ----------
    
13.      24007
    
14. 
    
15. SQL> create database link dblink1  connect to hr identified by hr using 'utforcl';
    
16. 
    
17. Database link created.
    
18. 
    
19. SQL> create  public  database link dblink1  connect to hr identified by hr using 'utforcl';
    
20. create        public        database link dblink1  connect to hr identified by hr using 'utforcl'
    
21.                               *
    
22. ERROR at line 1:
    
23. ORA-01031: insufficient privileges
    
24. 
    
25. 
    
26. SQL> create  public  database link dblink1  connect to hr identified by hr using 'utforcl';
    
27. 
    
28. Database link created.
    
29. 
    
30. SQL> select salary from employees where employee_id=100;
    
31. 
    
32.     SALARY
    
33. ----------
    
34.      24007
    
35. 
    
36. SQL> select salary from employees@dblink1 where employee_id=100;
    
37. 
    
38.     SALARY
    
39. ----------
    
40.      33333
    
41. 
    
42. SQL> conn sh/oracle_4U
    
43. Connected.
    
44. SQL> select salary from hr.employees@dblink1 where employee_id=100;
    
45. 
    
46.     SALARY
    
47. ----------
    
48.      33333
    
49. 
    
50. SQL> /
    
51. select salary from hr.employees@dblink1 where employee_id=100
    
52.                                 *
    
53. ERROR at line 1:
    
54. ORA-02019: connection description for remote database not found
    
55. 
    
56. 
    
57. SQL> conn hr/oracle_4U
    
58. Connected.
    
59. SQL> select salary from hr.employees@dblink1 where employee_id=100;
    
60. 
    
61.     SALARY
    
62. ----------
    
63.      33333
    
64. 
    
65. SQL>
    

复制代码

PARFILE：

1. directory=dir1
   
2. dumpfile=my2.dmp  
   
3. schemas=hr
   
4. job_name=my2_job  
   
5. exclude=table:"in ('T_BANK_INVOICE')", procedure:"in ('SECURE_DML')"  
   
6. query='employees:where department_id=90'
   
7. flashback_time='2018-10-25:20:14:22'
   
8. version=11.2.0.1
   

复制代码

目的地：



网络链的模式：

1. directory=dirwin
   
2. schemas=hr
   
3. job_name=my3_job  
   
4. exclude=table:"in ('T_BANK_INVOICE')", procedure:"in ('SECURE_DML')"  
   
5. query='hr.employees:where department_id=90'
   
6. flashback_time='2018-10-25:20:14:22'
   
7. network_link=dblinkwin
   
8. remap_schema=hr:usern

复制代码

1. impdp  system/oracle_4U  parfile=parfile1.txt

复制代码

FULL / attach:

1. expdp system/oracle_4U directory=dir1 dumpfile=full_%U.dmp     filesize=2G        full=y  job_name=myfull

复制代码

datapump外部表的导出，就是把任何一个视图变成操作系统上可移动的一个文件：

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

复制代码

导入的时候列名要一样，精度>=原来的精度：

1. [oracle@station90 dir1]$ strings emp1.exp

复制代码

1. "SYS"."U"
   
2. x86_64/Linux 2.4.xx
   
3. AL32UTF8
   
4. 11.02.00.00.00
   
5. 001:001:000001:000001
   
6. i<?xml version="1.0"?>
   
7. <ROWSET>
   
8. <ROW>
   
9.   <STRMTABLE_T>
   
10.    <VERS_MAJOR>1</VERS_MAJOR>
    
11.    <VERS_MINOR>0 </VERS_MINOR>
    
12.    <VERS_DPAPI>3</VERS_DPAPI>
    
13.    <ENDIANNESS>0</ENDIANNESS>
    
14.    <CHARSET>AL32UTF8</CHARSET>
    
15.    <NCHARSET>AL16UTF16</NCHARSET>
    
16.    <DBTIMEZONE>+00:00</DBTIMEZONE>
    
17.    <OWNER_NAME>HR</OWNER_NAME>
    
18.    <NAME>EXT_EMP_QUERY_RESULTS</NAME>
    
19.    <COL_LIST>
    
20.      <COL_LIST_ITEM>
    
21.       <COL_NUM>1</COL_NUM>
    
22.       <NAME>FIRST_NAME</NAME>
    
23.       <TYPE_NUM>1</TYPE_NUM>
    
24.       <LENGTH>20</LENGTH>
    
25.       <PRECISION_NUM>0</PRECISION_NUM>
    
26.       <SCALE>0</SCALE>
    
27.       <CHARSETID>873</CHARSETID>
    
28.       <CHARSETFORM>1</CHARSETFORM>
    
29.       <CHARLENGTH>20</CHARLENGTH>
    
30.      </COL_LIST_ITEM>
    
31.      <COL_LIST_ITEM>
    
32.       <COL_NUM>2</COL_NUM>
    
33.       <NAME>LAST_NAME</NAME>
    
34.       <TYPE_NUM>1</TYPE_NUM>
    
35.       <LENGTH>25</LENGTH>
    
36.       <PRECISION_NUM>0</PRECISION_NUM>
    
37.       <SCALE>0</SCALE>
    
38.       <CHARSETID>873</CHARSETID>
    
39.       <CHARSETFORM>1</CHARSETFORM>
    
40.       <CHARLENGTH>25</CHARLENGTH>
    
41.      </COL_LIST_ITEM>
    
42.      <COL_LIST_ITEM>
    
43.       <COL_NUM>3</COL_NUM>
    
44.       <NAME>DEPARTMENT_NAME</NAME>
    
45.       <TYPE_NUM>1</TYPE_NUM>
    
46.       <LENGTH>30</LENGTH>
    
47.       <PRECISION_NUM>0</PRECISION_NUM>
    
48.       <SCALE>0</SCALE>
    
49.       <CHARSETID>873</CHARSETID>
    
50.       <CHARSETFORM>1</CHARSETFORM>
    
51.       <CHARLENGTH>30</CHARLENGTH>
    
52.      </COL_LIST_ITEM>
    
53.    </COL_LIST>
    
54.   </STRMTABLE_T>
    
55. </ROW>
    
56. </ROWSET>
    
57. Michael        Hartstein        Marketing<
    
58. Fay        Marketing<
    
59. Raphaely
    
60. Purchasing<
    
61.         Alexander
    
62. Khoo
    
63. Purchasing<
    
64. Shelli
    
65. Baida
    
66. Purchasing<
    
67. Sigal
    
68. Tobias
    
69. Purchasing<
    
70. Himuro
    
71. Purchasing<
    
72. Karen
    
73. Colmenares
    
74. Purchasing
    
75. [oracle@station90 dir1]$
    

复制代码

上完1Z0-052 第17章 导入导出  （7/40）  

jarlean

imp_parfile.txt

1. directory=EXP_PATH
   
2. dumpfile=my.dmp  
   
3. schemas=test
   
4. job_name=my_job  
   
5. exclude=table:"in ('T_BANK_INVOICE')", procedure:"in ('SECURE_DML')"  
   
6. query='test:where 1=1'
   
7. version=11.2.0.1

复制代码

老师，我直接执行impdp，参数文件里面没有提到用哪个导出文件进行导入，这个时候impdp，是根据什么来确定要用哪个导出的文件进行导入的？

1. impdp test/test PARFILE=imp_parfile.txt

复制代码