|
课程目标： - 移动数据的方式
- 创建和使用目录对象
- 使用SQL*Loader
- 使用外部表
- Data Pump
1、移动数据：通用架构
DBMS_DATAPUMP：包含API，用于批量数据和元数据的导出到导入 Direct Path API（DPAPI）：使用Direct Path API进行加载和卸载数据，最小化数据转换和解析 DBMS_METADATA：处理元数据的加载和卸载，使用XML存储数据库对象的定义 External Table API：使用ORACLE_DATAPUMP和ORACLE_LOADER驱动使用外部表 SQL*Loader：与外部表集成 EXPDP和IMPDP：调用DBMS_DATAPUMP进行数据泵操作 Other clients
2、Data Pump：概览
目前主要使用Data Pump进行数据的导入导出，通过调用DBMS_DATAPUMP包来进行。Oracle提供了3种工具： 支持4种数据移动方法： - 数据文件拷贝
- 直接路径，避开SGA，从磁盘直接读取数据文件
- 外部表
- 网络连接
可以从多个位置监控Data Pump任务，可以重启中止的任务。
3、Data Pump：好处
相比exp和imp，Data Pump的好处有： 可以使用EXCLUDE，INCLUDE和CONTENT参加进行细粒度的对象和数据选项 可以使用VERSION参数为导出的文件指定数据库版本以便用于先前版本的数据库 可以使用PARALLEL参数指定并行度 可以使用ESTIMATE_ONLY参数估计导出数据到占用多少空间而不需要实际执行 可以使用Network模式，在分布式环境中通过数据链接导入导出 可以使用REMAP，在导入的时候更改目标数据文件名称，模式名称和表空间名称 可以使用SAMPLE参数指定导出数据的百分比 可以使用COMPRESSION进行压缩，节约空间 可以对元数据，数据，字段进行加密，指定加密算法，安全类型等 使用非压缩的CLOB格式导出XML类型字段 可以使用Legacy模式支持exp和imp脚本
4、Data Pump目录对象
通过目录对象指定操作系统目录，目录对象属于SYS用户，名称唯一。 由于Data Pump是一个服务器端的程序，所有需要通过目录对象指定dump文件目录。 通过EM查看目录对象。
使用SQL查询目录对象。 SQL> select * from dba_directories; OWNER DIRECTORY_NAME DIRECTORY_PATH ---------- ------------------------------ -------------------------------------------------------------------------------- SYS SUBDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry//2002/Sep SYS SS_OE_XMLDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry/ SYS LOG_FILE_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/log/ SYS MEDIA_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/product_media/ SYS DATA_FILE_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/sales_history/ SYS XMLDIR /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/dbhome_1/ccr/hosts/oracletest1/state SYS DATA_PUMP_DIR /u01/app/oracle/admin/stone/dpdump/ SYS ORACLE_OCM_CONFIG_DIR2 /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state 9 rows selected.
5、创建目录对象
使用EM创建目录对象。
指定目录对象的名称和路径，路径需要先创建，可以点击“测试文件系统”测试路径是否存在。
使用SQL创建目录对象。 SQL> create directory test1 as '/home/oracle'; Directory created. SQL> grant read,write on directory test1 to hr; Grant succeeded. SQL> select * from dba_directories; OWNER DIRECTORY_NAME DIRECTORY_PATH ---------- ------------------------------ -------------------------------------------------------------------------------- SYS TEST /home/oracle SYS SUBDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry//2002/Sep SYS SS_OE_XMLDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry/ SYS TEST1 /home/oracle SYS LOG_FILE_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/log/ SYS MEDIA_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/product_media/ SYS DATA_FILE_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/sales_history/ SYS XMLDIR /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/dbhome_1/ccr/hosts/oracletest1/state SYS DATA_PUMP_DIR /u01/app/oracle/admin/stone/dpdump/ SYS ORACLE_OCM_CONFIG_DIR2 /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state 11 rows selected.
6、Data Pump导出和导入客户端：概览
使用expdp导出数据库数据到操作系统的dump文件，再使用impdp导入dump文件到目标数据库。在服务器端进行，不在客户端进行。 通过使用Network模式，源数据库到目标数据库直接加载。避免了在文件系统上创建转储文件，可以最大限度地减少导出和导入操作的总消耗时间。 使用Master Table（MT）来维护导出操作，开始导出操作时创建该表，导出完成后写入到dump文件中，导入时先加载MT到数据库当前模式用于按顺序创建导入对象，导入完成后会删除MT。
7、Data Pump工具：接口和模式
可以通过以下方式使用Data Pump： - 命令行
- 参数文件：除了prafile参数不能指定，可以指定其他所有参数
- 交互命令：通过[Ctrl]+[C]进入
- Web接口：使用EM
可以指定不同的模式：
8、使用EM导出数据
可以使用EM导出和导入数据。
授予hr用户select_catalog_role和exp_full_database系统权限，使用hr账户登录。 SQL> grant select_catalog_role to hr; Grant succeeded. SQL> grant exp_full_database to hr; Grant succeeded. SQL> grant write,read on directory test to hr; Grant succeeded. SQL> grant execute on DBMS_STATS to hr; Grant succeeded. SQL> conn sysman/123456 Connected. SQL> execute MGMT_USER.MAKE_EM_USER('HR'); PL/SQL procedure successfully completed.
9、Data Pump导出示例：基本选项
通过“导出作业中的最大线程数”指定并行度，对应命令行parallel参数。应该小于等于dump文件数。 估算导出作业将占用的磁盘空间 (以字节计)。只针对表中的行数据, 而不包括元数据。可以通过块大小乘以块数量进行进行计算，也可以使用最近的统计信息。对应命令行estimate参数。 可以选择生成日志文件到目录对象指定的路径下，对应命令行logfile参数。
10、Data Pump导出示例：高级选项
使用高级选项指定导出内容，对应content参数，include参数和exclude参数。 导出的时候可以使用闪回查询导出以前版本的数据。 可以通过查询选项指定导出导入数据满足的条件。对应query参数。
11、Data Pump导出示例：文件
指定dump文件的目录和名称，对应dumpfile参数。可以指定多个，如果不指定，默认使用expdat.dmp名称。可以使用%U来产生文件序列号，%D产生日期。 可以指定最大文件大小，对应filesize参数，如果指定了%U，则自动生成新文件，如果没有指定，则需要手动添加文件。 默认不会覆盖同名的文件，会提示错误并停止，可以使用参数reuse_dumpfile=y指定覆盖。 Oracle提供了一个叫DATA_PUMP_DIR的目录对象用于导出。如果设置了ORACLE_BASE，则默认路径为/admin/DB_UNIQUE_NAME/dpdump，如果没有设置ORACLE_BASE,设置了ORACLE_HOME，则默认路径为/admin/DB_UNIQUE_NAME/dpdump。
12、Data Pump导出示例：调度
创建任务计划。
13、Data Pump导出示例：复查
使用命令导出数据。 [oracle@oracletest1 ~]$ expdp hr/hr tables=hr.emp_exp dumpfile=hr_emp.dmp directory=test logfile=hr_emp.log;
14、Data Pump导入示例：impdp
使用命令行导入数据。在另外一个数据库创建目录对象并授予权限 SQL> create directory test as '/home/oracle'; Directory created. SQL> grant read,write on directory test to hr; Grant succeeded. [oracle@oracletest ~]$ impdp hr/hr directory=test dumpfile=hr_emp.dmp parallel=1 content=all tables="emp_exp" logfile=hr_emp.log
15、Data Pump导入示例：转换
导入的时候可以进行转换，包括： REMAP_DATAFILE：转换数据文件名称，跨平台的时候可以使用 REMAP_TABLESPACE：可以将对象导入到指定的表空间 REMPA_SCHEMA：可以将对象导入到指定的模式 REMAP_TABLE：导入时重命名表 REMAP_DATA：导入时更改数据，一般用于重新生成主键 [oracle@oracletest ~]$ impdp hr/hr directory=test dumpfile=hr_emp.dmp parallel=1 content=all tables="emp_exp" logfile=hr_emp.log remap_table=emp_exp:empexp
16、使用EM监控Data Pump任务
可以使用EM监控所有Data Pump任务，可以执行、停止和暂停任务。
17、使用Data Pump传统模式迁移数据
允许在Data Pump中使用之前的exp和imp脚本。会把出现在expdp/impdp命令中的exp/imp参数，自动转换为expdp/impdp所支持的参数上。
18、Data Pump传统模式
exp导出的文件只能由imp导入。expdp导出的文件只能由impdp导入。当在expdp/impdp命令中有exp/imp参数时，进入到传统模式。规则如下： - 相同参数用法不改变
- 相似的参数结果有可能不一样
- 替代的参数被忽略
- 参数混用会失败
19、Data Pump传统模式
传统模式下： - 一些参数会做映射，功能一样
- 参数映射后功能有变化
- Data Pump没有的参数会报错，volsize是exp指定磁带卷大小
20、管理文件位置
由于exp/imp是基于客户端的，所有需要指定完整的路径名称，而Data Pump是基于服务器段的，使用目录对象指定文件位置，默认的目录对象是DATA_PUMP_DIR。 可以为某个用户创建专门的目录对象DATA_PUMP_DIR_schema-name，并授权，当用户没有EXP_FULL_DATABASE权限，没有使用directory参数指定目录对象时，就是用这个目录对象。
21、SQL*Loader：概览
使用SQL*Loader加载外部文件到表。 会使用到下面的文件： - Input data files（输入数据文件）：源数据所在文件
- Control file（控制文件）：文本文件，指定输入数据文件的位置以及解析方式等，分三部分：
- 包括会话级别的信息，包括全局选项，记录的格式，加载的数据等
- 表信息，表的名字和字段
- 可选部分，包含输入数据
- Log file（日志文件）：存储日志记录信息
- Bad file（问题记录文件）：存储包含错误的记录
- Discard file（丢弃文件）：存储被拒绝和无法插入的记录
22、使用SQL*Loader加载数据
在EM使用使用SQL*Loader加载数据
[oracle@oracletest1 ~]$ cat sqlload.dat Ellen Abel Sundar Ande Mozhe Atkinson David Austin Hermann Baer Shelli Baida Amit Banda Elizabeth Bates Sarah Bell David Bernstein
SQL> create table empldr as select first_name,last_name from employees where 1=0; Table created.
SQL> select * from empldr; FIRST_NAME LAST_NAME -------------------- ------------------------- Ellen Abel Sundar Ande Mozhe Atkinson David Austin Hermann Baer Shelli Baida Amit Banda Elizabeth Bates Sarah Bell David Bernstein 10 rows selected. [oracle@oracletest1 ~]$ cat sqlload.CTL LOAD DATA APPEND INTO TABLE HR.EMPLDR ( FIRST_NAME POSITION(1:20) CHAR, LAST_NAME POSITION(21:45) CHAR ) [oracle@oracletest1 ~]$ sqlldr control='/home/oracle/sqlload.CTL' Username:hr Password: SQL*Loader: Release 11.2.0.4.0 - Production on Wed Dec 30 17:41:10 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Commit point reached - logical record count 10
23、SQL*Loader控制文件
使用控制文件： - 指定其他文件位置
- 指定数据格式
- 配置：内存管理，记录的拒绝规则
- 如何操作加载的数据
控制文件例子： 1 -- This is a sample control file 2LOAD DATA 3INFILE ’SAMPLE.DAT’ 4BADFILE ’sample.bad’ 5DISCARDFILE ’sample.dsc’ 6APPEND 7INTO TABLE emp 8WHEN (57) = ’.’ 9TRAILING NULLCOLS 10(hiredate SYSDATE, deptno POSITION(1:2) INTEGER EXTERNAL(3) NULLIF deptno=BLANKS, job POSITION(7:14) CHAR TERMINATED BY WHITESPACE NULLIF job=BLANKS "UPPER(:job)", mgr POSITION(28:31) INTEGER EXTERNAL TERMINATED BY WHITESPACE, NULLIF mgr=BLANKS, ename POSITION(34:41) CHAR TERMINATED BY WHITESPACE "UPPER(:ename)", empno POSITION(45) INTEGER EXTERNAL TERMINATED BY WHITESPACE, sal POSITION(51) CHAR TERMINATED BY WHITESPACE "TO_NUMBER(:sal,’$99,999.99’)", comm INTEGER EXTERNAL ENCLOSED BY ’(’ AND ’%’ ":comm * 100" ) （1）第一行为注释，可以位于任何地方，“--”开头。 （2）LOAD DATA表示这是一个新数据加载的开始，如果要继续之前的加载，可以使用CONTINUE LOAD DATA （3）INFILE指定数据所在文件名称 （4）BADFILE指定问题记录保存文件 （5）DISCARDFILE指定丢弃记录保存文件 （6）APPEND用于加载数据到一个已经有数据的表，如果是空表，使用INSERT关键字 （7）INTO TABLE指定导入数据到哪个表 （8）WHEN语句指定每条记录需要满足的域值条件，WHEN (57) = '.'表示记录的第57个字符为'.' （9）TRAILING NULLCOLS语句不存在的字段为NULL （10）剩下的是字段列表和格式
24、加载方法
常规导入通过使用 INSERT语句来导入数据。Direct导入可以跳过数据库的相关逻辑(DIRECT=TRUE)，而直接将数据导入到数据文件中，可以提高导入数据的性能。 传统路径（conventional path）：SQLLDR 会利用SQL插入加载数据。 直接路径（direct path）：采用这种模式，SQLLDR不使用SQL；而是直接格式化数据库块，而绕过整个SQL引擎和UNDO生成，同时还可能避开REDO生成。要在一个没有任何数据的库中充分加载数据，最快的方法就是采用并行直接路径加载。总是在表的最高水位之上插入数据，这种方式消除了用于搜索部分填充块的时间。
25、外部表
外部表是存在在操作系统上的文件，数据库通过访问驱动可以直接读取，但是外部表只读，不支持DML和创建索引。 有2种访问驱动： - ORACLE_LOADER：只能用于读取外部表和加载到数据库，使用文本文件作为数据源。
- ORACLE_DATAPUMP：既可以加载数据到数据库，也可以导出数据到外部文件，使用二进制文件，与impdp和expdp格式一样。
26、外部表好处
可以直接读取外部表数据，无需加载到数据库，也可以使用ORACLE_DATAPUMP访问驱动将复杂查询的结果导出到外部文件。
27、使用ORACLE_LOADER定义外部表
使用SQL语言创建外部表元数据，ORACLE_LOADER访问驱动使用SQL*Loader语法定义外部表，不会创建外部文件。 如果数据量很大，可以启用并行DML： SQL> alter session enable parallel dml; Session altered.
28、使用ORACLE_DATAPUMP进行外部表操作
使用外部表导出多表连接查询结果到文件。可以指定并行度，数量不超过文件数量。导出的文件可以使用impdp导入。 SQL> create table ext_emp_query_results1(first_name,last_name,department_name) 2 organization external( 3 type oracle_datapump 4 default directory test 5 location('emp11.exp','emp12.exp','emp13.exp')) 6 PARALLEL 3 7 as 8 select e.first_name,e.last_name,d.department_name 9 from employees e,departments d 10 where e.department_id=d.department_id 11 and d.department_name in ('Marketing','Purchasing'); Table created. [oracle@oracletest1 ~]$ ls emp*.exp emp11.exp emp12.exp emp13.exp
29、使用外部表
30、数据字典
[DBA|ALL|USER]_EXTERNAL_TABLES: 外部表属性 [DBA|ALL|USER]_EXTERNAL_LOCATIONS: 外部表位置 [DBA|ALL|USER]_TABLES: 表信息 [DBA|ALL|USER]_TAB_COLUMNS: 表列信息 [DBA|ALL]_DIRECTORIES: 目录对象信息 SQL> select table_name,TYPE_NAME,DEFAULT_DIRECTORY_NAME from user_external_tables; TABLE_NAME TYPE_NAME DEFAULT_DIRECTORY_NAME ------------------------------ ------------------------------ ------------------------------ EXT_EMP_QUERY_RESULTS1 ORACLE_DATAPUMP TEST SQL> select * from user_external_locations; TABLE_NAME LOCATION DIR DIRECTORY_NAME ------------------------------ ------------------------------ --- ------------------------------ EXT_EMP_QUERY_RESULTS1 emp11.exp SYS TEST EXT_EMP_QUERY_RESULTS1 emp12.exp SYS TEST EXT_EMP_QUERY_RESULTS1 emp13.exp SYS TEST
31、相关习题： （1）You want to move all objects of the APPS user in the test database to the DB_USR schema of the production database.
Which option of IMPDP would you use to accomplish this task?
A. FULL
B. SCHEMAS
C. TRANSFORM
D. REMAP_SCHEMA
E. REMAP_TABLESPACE 答案：D （2）You plan to move data from a flat file to a table in your database. You decide to use SQL*Loader direct path load method to perform this task. The table in which you plan to load data in an important table having various integrity constraint defined on it.
Which constraints will remain enabled by default during this operation? (Choose all that apply.)
A. CHECK
B. UNIQUE
C. NOT NULL
D. PRIMARY KEY
E. FOREIGN KEY 答案：BCD （3）Which two statements are true regarding the usage of the SQL*LOADER utility? (Choose two.)
A. You can load data into multiple tables during the same load session
B. You can load data from multiple files to a table during the same load session
C. You cannot perform selective data loading based on the values available in the records
D. You can use an export file generated by the EXPDP utility as an input data file to load the data
E. You can load data only if the input file is available on the disk and tape but not a named pipe 答案：AB （4）Which two operations can be performed on an external table? (Choose two.)
A. Create a view on the table
B. Create an index on the table
C. Create a synonym on the table
D. Add a virtual column to the table
E. Update the table using the UPDATE statement
F. Delete rows in the table using the DELETE command 答案：AC （5）Note the following points describing various utilities in Oracle Database 11g:
Which point describes the Oracle Data Pump utility?
A．1
B. 2
C. 3
D. 4
E. 1 and 3
F. 1, 2, 3 and 4 答案：A （6）Which statement is true about loading data using the conventional path of SQL*Loader?
A. Redo is not generated while performing conventional path loads
B. Only PRIMARY KEY, UNIQUE KEY and NOT NULL constraints are checked
C. No exclusive locks are acquired when the conventional path loads are performed
D. Instead of performing transactions, SQL*Loader directly writes data blocks to the data files
E. INSERT triggers are disabled before the conventional path load and reenabled at the end of the load 答案：C （7）database, DEVDB, to the production database, PRODDB. A database link devdb.us.oracle.com is created between PRODDB and DEVDB. You execute the following command on the PRODDB database server:
$ impdp system/manager directory = DB_DATA
dumpfile = schemas.dat
schemas = hr
flashback_time = "TO_TIMESTAMP('25-08-2007 14:35:00',
'DD-MM-YYYY HH24:MI:SS')"
The command fails displaying the following error:
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/home/oracle/schema/schemas.dat" for read
ORA-27037: unable to obtain file status
What would you do to overcome the error?
A. Add the user, SYSTEM, to the schemas option.
B. Add the network_link = devdb.us.oracle.com option.
C. Change the dumpfile option to schema.dat@devdb.us.oracle.com.
D. Replace the schemas option with the network_link = devdb.us.oracle.com
E. Replace the dumpfile option with the network_link = devdb.us.oracle.com 答案：E （8）The TRANS_SUMMARY table contains product-wise transaction details that get updated with every transaction in the system. Each row has cumulative transaction details of a single product and every product is identified by a product code, which is the primary key.
As part of the archival process, the company wants to transfer the rows in the TRANS_SUMMARY table to the TRANS_SUMMARY_DUP table at the end of every quarter of the year. Along with existing products, the company deals with many new products during every quarter.
Which method is best suited for this quarterly data transfer?
A. Using the MERGE command
B. Using the SQL*Loader utility
C. Using the correlated UPDATE command
D. Using the INSERT command to perform bulk operation 答案：A （9）You have a large amount of historical data in a flat file. Some analysts in your organization need to query this data in the file. The file is too large to load the data in it into your current database.
Which is the most effective method to access this data in your database?
A. Use the database link.
B. Use the SQL*Loader utility.
C. Use the Oracle Data Pump utility.
D. Create an external table and leave the data in the flat file. 答案：D （10）You are using flat files as the data source for one of your data warehousing applications. To optimize the application performance, you plan to move the data from the flat files to clustered tables in an Oracle database. While migrating the data, you want to have minimal impact on the database performance and optimize the data load operation.
Which method would you use to load data into the Oracle database?
A. Use the external table population.
B. Use the Oracle Data Pump export and import utility.
C. Use the conventional path data load of the SQL*Loader utility.
D. Use the INSERT INTO...SELECT command to load the data. 答案：Ｃ （11）Which two statements are true regarding the Oracle Data Pump export and import operations? (Choose two.)
A. You cannot export data from a remote database.
B. You can rename tables during an import operation.
C. You can overwrite existing dump files during an export operation.
D. You can compress the data during export but not the metadata because it is not supported. 答案：BC
| 
|手机版|小黑屋|重庆思庄Oracle、Redhat认证学习论坛
( 渝ICP备12004239号-4 )
发表于 2016-2-19 10:17:47
QQ好友和群
QQ空间
腾讯微博
腾讯朋友
收藏
支持
反对