21、移植数据(数据仓库)
(1)数据泵
expdp
impdp
数据泵导出和导入情景：
全库、用户、表、表空间、可移动表空间。
案例模板：
mkdir -p /u02/dumpdir
chown -R oracle.dba /u02/dumpdir
desc system_privilege_map;//查看系统权限
授权给scott用户
grant create any directory to scott;
conn scott/tiger;
create directory dump_dir as '/u02/dumpdir';
select OWNER,DIRECTORY_NAME,DIRECTORY_PATH from dba_directories where DIRECTORY_NAME = 'DUMP_DIR';
授权给scott用户
grant read,write on directory dump_dir to scott;
1、按照表进行导出导入(tables=emp)
导出scott用户的emp表;
expdp scott/tiger directory=dump_dir dumpfile=expdp_scott_emp.dmp tables=emp;
导出多张表;
expdp scott/tiger directory=dump_dir dumpfile=expdp_scott_emp.dmp tables=emp,dept;
下面删除表emp;
drop table scott.emp purge;
下面导入emp表
impdp scott/tiger directory=dump_dir dumpfile=expdp_scott_emp.dmp tables=emp;
可以看到scott.emp表回来了。
2、按照用户(scott)进行导入导出(schemas=scott)
导出scott用户：
expdp system/oracle directory=dump_dir dumpfile=expdp_scott.dmp logfile=expdp_scott.log schemas=scott;
drop user scott cascade;
导入scott用户：
impdp system/oracle directory=dump_dir dumpfile=expdp_scott.dmp logfile=impdp_scott.log schemas=scott;
查看sql> select * from tab;
搞定！！！
3、导出全库(full=y)
expdp system/oracle directory=dump_dir dumpfile=expdp_db.dmp logfile=expdp_db.log full=y;
这里就不演示了。
数据泵的高级语法：
mkdir -p /u02/testdir
chown -R oracle.dba /u02/testdir
create directory test_dir as '/u02/testdir';
grant read,write on directory test_dir to scott,hr;
导出全库且文件放在多个目录下面，并行4导出，文件大小20M分开。
expdp system/oracle full=y parallel=4 dumpfile=dump_dir:expdp_db_1_%U.dmp,test_dir:expdp_db_2_%U.dmp filesize=20m;
4、使用参数文件，排除【导出scott用户（不包括emp表）】
vim /u02/dumpdir/expdp.parameter
exclude=table:"like 'EMP'"
schemas=scott
命令：
expdp system/oracle directory=dump_dir dumpfile=scott logfile=expdp_scott.log parfile=/u02/dumpdir/expdp.parameter
如果排除多张表：【使用in运算符】
exclude=table:"in ('EMP','DEPT')"
schemas=scott
如果排除某种类型的对象，比如排除表，参数文件如下：
exclude=table
schemas=scott
5、使用包含，和排除类似用include 替换exclude即可
6、只导出用户表的定义[content=Metadata_only]
expdp scott/tiger directory=dump_dir dumpfile=expdp_Meta.dmp content=Metadata_only
7、只把一个用户的表定义导出来，导进去另外一个用户【不存在就创建】
expdp system/oracle directory=dump_dir dumpfile=map logfile=map.log schemas=scott content=Metadata_only
导进去另外一个用户[scot]中去
impdp system/oracle directory=dump_dir dumpfile=map remap_schema=scott:scot
结果如下：
sql> conn scot/tiger;
Connected.
sql> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
select count(*) from EMP;//没有数据
搞定！！！！
8、只把一个用户数据导出来，不包括表定义【content=data_only】
expdp system/oracle directory=dump_dir dumpfile=dataonly logfile=dataonly.log schemas=scott content=data_only
9、根据步骤7和步骤8，把数据导进去到scot这个用户中去
impdp system/oracle directory=dump_dir dumpfile=dataonly remap_schema=scott:scot
成功后：select count(*) from EMP;//有数据
总结：
content=all 表示导出元数据和数据【默认情况下】
content=data_only 只导出数据
content=Metadata_only 只导出元数据
10、使用参数文件导出符合条件的数据
vim aa.parameter
exclude=table
tables=EMP
query=EMP:"where deptno=30"
expdp scott/tiger directory=dump_dir dumpfile=aa.dump parfile=/u02/dumpdir/aa.parameter
导进scot用户重新映射表名emp1;
impdp scot/tiger directory=dump_dir dumpfile=aa.dump remap_schema=scott:scot remap_table=emp:emp1;
11、采样(生产测试数据)---企业用得比较多[sample=10]// 百分之10
grant select on dba_objects to scott;
conn scott/tiger;
create table e as select * from dba_objects;
expdp scott/tiger directory=dump_dir dumpfile=e.dump tables=e sample=10
12、使用dblink实现分布式环境的数据复制
在异机上利用dblink做impdp数据导入,这样就节省了数据导出（expdp），然后再导入的过程。
源服务器：(192.168.175.250)
目标服务器：192.168.175.60
(1)源服务器(目标服务器)的网络配置如下：
[oracle@oracle250 admin]$ cat listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.175.250)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u02/oracle
LISTENER60 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.175.60)(PORT = 1521))
)
)
另外：
[oracle@oracle250 admin]$ cat tnsnames.ora
ORCL250 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.175.250)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.175.60)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
配置好之后分别在源服务器和目标服务器测试：
tnsping orcl250
tnsping orcl
都成功！！！！！！
(2)目标服务器创建 dblink
conn system/oracle;
CREATE PUBLIC DATABASE LINK PLINK1 CONNECT TO scott IDENTIFIED BY tiger USING 'ORCL250';
sql> CREATE PUBLIC DATABASE LINK PLINK1 CONNECT TO scott IDENTIFIED BY tiger USING 'ORCL250
';
Database link created.
sql> select * from dba_db_links;
OWNER DB_LINK USERNAME HOST CREATED
---------- --------------- -------- ---------- -------------------
PUBLIC PLINK1 SCOTT ORCL250 2017-10-12 09:38:21
测试：
sql> select count(*) from scott.e@PLINK1;
COUNT(*)
----------
86362
表示创建dblink 可以远程读取源服务器的数据
(3)源服务器授权
grant EXP_FULL_DATABASE to scott;
grant IMP_FULL_DATABASE to scott;
sql> grant EXP_FULL_DATABASE to scott;
Grant succeeded.
sql> grant IMP_FULL_DATABASE to scott;
Grant succeeded.
(4)目标服务器执行：
impdp system/oracle schemas=SCOTT network_link=PLINK1;
结果如下：截取部分日志
. . imported "SCOTT"."E" 86362 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed
表示成功了！！！！！！！！！！！
(2)sql loader
加载数据的工具。
(1)案例1
根据逗号分隔数据查询结果：
sql> select deptno ||','||
2 dname ||','||
3 loc from dept;
DEPTNO||','||DNAME||','||LOC
---------------------------------------------------------------------
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON
把结果保存在文件中/home/oracle/a.txt
vim /home/oracle/a.txt
10,BOSTON
创建表结构
create table scott.a as select * from scott.dept where 1=2;
sql> desc scott.a;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
创建控制文件
vim loader.ctl
load data
infile '/home/oracle/a.txt'
into table a
fields terminated by ','
(DEPTNO,DNAME,LOC)
执行命令，把数据加载到表a中去。
sqlldr scott/tiger control=/home/oracle/loader.ctl log=/home/oracle/loader.log
[oracle@oracle250 ~]$ sqlldr scott/tiger control=/home/oracle/loader.ctl log=/home/oracle/loader.log
sql*Loader: Release 11.2.0.4.0 - Production on Mon Oct 2 21:56:16 2017
Copyright (c) 1982,2011,Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 4
表示加载成功了四行！！！！！
另外：最复杂的控制文件
load data
infile '/home/oracle/b.txt'
badfile '/home/oracle/b.txt'
discardfile '/home/oracle/b.txt'
append
into table test_loader
when deptno='30'
fields terminated by ','
trailings nullcols
(empno,
ename,
job "substr(:job,1,4)",
mgr,
hiredate date "yyyy-mm-dd",
sal filler,
comm filler,
deptno)
解释如下：
infile 数据源文件（一般逗号）
badfile 没有导入成功的数据
discardfile 被拒接的数据
append 如果表里有数据就追加（另外replace 如果表里面有数据，就清空后追加）
when 加条件
fields terminated by ',' 声明分隔符
trailings nullcols 专门处理最后一个字符为空值的情况
filler 排除字段
加载方法：
sqlldr scott/tiger control=/home/oracle/loader.ctl log=/home/oracle/loader.log
(3)外部表
外部表是存储在数据库之外的文件，只读方式在数据库中为表使用。
文本、二进制文件
方法1、loader方式加载外部表
vim /home/oracle/xx/dept.txt 内容如下：
10,BOSTON
create directory user_dir as '/home/oracle/xx';
grant read,write on directory user_dir to scott;
创建外部表
conn scott/tiger;
create table test_ext
(deptno number(2),
dname varchar2(14),
loc varchar2(13)
)
organization external
(
type oracle_loader
default directory user_dir
access parameters
(
records delimited by newline
badfile 'test.bad'
discardfile 'test.dis'
logfile 'test.log'
fields terminated by ','
missing field values are null
)
location('dept.txt')
);
访问外部表：
查询结果如下：
sql> select * from test_ext;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
方法2、 oracle_datapump
create table test_ext2
(deptno,
dname
)
organization external
(
type oracle_datapump
default directory user_dir
location('load.testdp')
)
as
select deptno,dname from scott.dept;
最终结果保存在load.testdp文件中
[oracle@oracle250 xx]$ ll load.testdp
-rw-r----- 1 oracle dba 12288 Oct 7 11:44 load.testdp
[oracle@oracle250 xx]$
查询外部表：
select * from scott.test_ext2;
sql> select * from scott.test_ext2;
DEPTNO DNAME
---------- --------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
原文链接：https://www.f2er.com/oracle/207323.html