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 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |