OCP认证考试指南（9）：操纵数据库数据（2）

1、Data Pump

Data Pump生成的文件具有下列3种形式：SQL文件、转储文件以及日志文件。

Data Pump在一个Oracle目录中读、写文件。DBA在数据库内创建一个目录，这个目录指向操作系统文件系统内的一个物理路径。Oracle目录始终属于用户SYS，但为了创建目录，必须授权“CREATE DIRECTORY”。

Data Pump导出数据：

^?[Copy to clipboard]View Code BASH

################################################ 修改参数文件，在最后一行加上导出目录 UTL_FILE_DIR=/dp_dir# 因为oracle用户没有创建目录权限，用root创建，再授权给oracle###############################################$ vi $ORACLE_HOME/dbs/initora.ora $ su口令： ＃ mkdir /dp_dir＃ chown oracle:oinstall /dp_dir＃ su oracle

 

^?[Copy to clipboard]View Code SQL

################################################ 重新生成spfile文件，让实例重新加载############################################### $ sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 26 15:13:04 2008 Copyright (c) 1982, 2005, Oracle.  All rights reserved. SQL> conn / as sysdbaConnected to an idle instance.SQL> create spfile from pfile; File created. SQL> startupORACLE instance started. Total System Global Area  264241152 bytesFixed Size                  1218868 bytesVariable Size              88082124 bytesDatabase Buffers          171966464 bytesRedo Buffers                2973696 bytesDatabase mounted.Database opened.SQL> create user test identified by test  2  default tablespace mydemo; User created. SQL> alter user test quota 5M on mydemo; User altered. SQL> grant create session, create table, create any directory to test; Grant succeeded. SQL> create directory dp_dir as 'dp_dir'; Directory created. SQL> grant all on directory dp_dir to public; Grant succeeded. SQL> conn test/testConnected.SQL> create table dp_test as select * from all_users; Table created. SQL> exit

^?[Copy to clipboard]View Code BASH

$ expdp test/test schemas=test directory=dp_dir dumpfile=test; Export: Release 10.2.0.1.0 - Production on Friday, 26 September, 2008 15:37:36 Copyright (c) 2003, 2005, Oracle.  All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsStarting "TEST"."SYS_EXPORT_SCHEMA_01":  test/******** schemas=test directory=dp_dir dumpfile=test Estimate in progress using BLOCKS method...Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 64 KBProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/COMMENT. . exported "TEST"."DP_TEST"                       5.929 KB      21 rowsMaster table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded************************************************************************Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:  /dp_dir/test.dmpJob "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:38:00

Data Pump导入数据：

^?[Copy to clipboard]View Code SQL

SQL> conn test/testConnected.SQL> drop table dp_test; Table dropped. SQL> select * from dp_test;select * from dp_test              *ERROR at line 1:ORA-00942: table or view does not exist

^?[Copy to clipboard]View Code BASH

$ impdp userid=test/test dumpfile=test.dmp directory=dp_dir; Import: Release 10.2.0.1.0 - Production on Friday, 26 September, 2008 16:04:02 Copyright (c) 2003, 2005, Oracle.  All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsMaster table "TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloadedStarting "TEST"."SYS_IMPORT_FULL_01":  userid=test/******** dumpfile=test.dmp directory=dp_dir Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/TABLE_DATA. . imported "TEST"."DP_TEST"                       5.929 KB      21 rowsJob "TEST"."SYS_IMPORT_FULL_01" successfully completed at 16:04:09

^?[Copy to clipboard]View Code SQL

SQL> conn test/testConnected.SQL> select count(*) from dp_test;   COUNT(*)----------        21

导出、导入都成功！！！耶~

2、SQL*Loader

需要批量上载第三方系统所生成的数据集，我们就可以用SQL*Loader。

^?[Copy to clipboard]View Code BASH

＃ chown -R oracle:oinstall /ora01＃ chmod -R 777 /ora01＃ su oracle $ vi /ora01/test.ctlload datainfile '/ora01/test.bat'appendinto table dp_testfields terminated by ','(username,user_id) $ vi /ora01/test.bathn,555Damir,666John,777 $ sqlldr userid=test/test control=/ora01/test.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Fri Sep 26 17:45:22 2008 Copyright (c) 1982, 2005, Oracle.  All rights reserved. Commit point reached - logical record count 4

OK，加载成功！不行，进入查看，会发现，多了三条记录了。

^?[Copy to clipboard]View Code SQL

SQL> conn test/testConnected.SQL> select count(*) from dp_test;   COUNT(*)----------        24