Bo's Oracle Station

查看: 2650|回复: 1

课程第10次(2018-10-25星期四)

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2018-10-25 19:12:49 | 显示全部楼层 |阅读模式
课程第10次(2018-10-25星期四)
SYS:
  1. grant create public database link to hr;

  2. alter user sh identified by oracle_4U account unlock;

  3. drop public database link dblink1;
复制代码
  1. [oracle@station90 ~]$ sqlplus /nolog

  2. SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 25 19:26:02 2018

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

  4. SQL> conn hr/oracle_4U
  5. Connected.
  6. SQL> select salary from employees where employee_id=100;

  7.     SALARY
  8. ----------
  9.      24007

  10. SQL> create database link dblink1  connect to hr identified by hr using 'utforcl';

  11. Database link created.

  12. SQL> create  public  database link dblink1  connect to hr identified by hr using 'utforcl';
  13. create        public        database link dblink1  connect to hr identified by hr using 'utforcl'
  14.                               *
  15. ERROR at line 1:
  16. ORA-01031: insufficient privileges


  17. SQL> create  public  database link dblink1  connect to hr identified by hr using 'utforcl';

  18. Database link created.

  19. SQL> select salary from employees where employee_id=100;

  20.     SALARY
  21. ----------
  22.      24007

  23. SQL> select salary from employees@dblink1 where employee_id=100;

  24.     SALARY
  25. ----------
  26.      33333

  27. SQL> conn sh/oracle_4U
  28. Connected.
  29. SQL> select salary from hr.employees@dblink1 where employee_id=100;

  30.     SALARY
  31. ----------
  32.      33333

  33. SQL> /
  34. select salary from hr.employees@dblink1 where employee_id=100
  35.                                 *
  36. ERROR at line 1:
  37. ORA-02019: connection description for remote database not found


  38. SQL> conn hr/oracle_4U
  39. Connected.
  40. SQL> select salary from hr.employees@dblink1 where employee_id=100;

  41.     SALARY
  42. ----------
  43.      33333

  44. 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
复制代码

目的地:
a.png


网络链的模式:
  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]$
复制代码

b.png

上完1Z0-052 第17章 导入导出  (7/40)  


回复

使用道具 举报

1

主题

16

帖子

280

积分

中级会员

Rank: 3Rank: 3

积分
280
QQ
发表于 2018-10-29 21:13:46 | 显示全部楼层
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
复制代码
回复 支持 反对

使用道具 举报

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

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-29 03:14 , Processed in 0.040269 second(s), 28 queries .

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