21、移植数据(数据仓库)

前端之家收集整理的这篇文章主要介绍了21、移植数据(数据仓库)前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

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;


删除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

猜你在找的Oracle相关文章