OCP认证考试指南(9):操纵数据库数据(2)

1、Data Pump

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

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

Data Pump导出数据:

###############################################
# 修改参数文件,在最后一行加上导出目录 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

 

###############################################
# 重新生成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 sysdba
Connected to an idle instance.
SQL> create spfile from pfile;
 
File created.
 
SQL> startup
ORACLE instance started.
 
Total System Global Area  264241152 bytes
Fixed Size                  1218868 bytes
Variable Size              88082124 bytes
Database Buffers          171966464 bytes
Redo Buffers                2973696 bytes
Database 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/test
Connected.
SQL> create table dp_test as select * from all_users;
 
Table created.
 
SQL> exit
$ 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 - Production
With the Partitioning, OLAP and Data Mining options
Starting "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_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type 
SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
. . exported "TEST"."DP_TEST"                       5.929 KB      21 rows
Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
************************************************************************
Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
  /dp_dir/test.dmp
Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:38:00

Data Pump导入数据:

SQL> conn test/test
Connected.
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
$ 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 - Production
With the Partitioning, OLAP and Data Mining options
Master table "TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_FULL_01":  userid=test/******** 
dumpfile=test.dmp directory=dp_dir 
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."DP_TEST"                       5.929 KB      21 rows
Job "TEST"."SYS_IMPORT_FULL_01" successfully completed at 16:04:09
SQL> conn test/test
Connected.
SQL> select count(*) from dp_test;
 
  COUNT(*)
----------
        21

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

2、SQL*Loader

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

chown -R oracle:oinstall /ora01
# chmod -R 777 /ora01
# su oracle
 
$ vi /ora01/test.ctl
load data
infile '/ora01/test.bat'
append
into table dp_test
fields terminated by ','
(username,user_id)
 
$ vi /ora01/test.bat
hn,555
Damir,666
John,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,加载成功!不行,进入查看,会发现,多了三条记录了。

SQL> conn test/test
Connected.
SQL> select count(*) from dp_test;
 
  COUNT(*)
----------
        24
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 2
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值