轉資料遷移

1、資料泵匯入匯出
create directory dump_file_dir as '/home/oracle/';
grant read,write on directory dump_file_dir to gyj;
expdp gyj/gyj directory=dump_file_dir dumpfile=gyj20121117.dmp nologfile=y;
create user gyj1 identified by gyj1 default tablespace t2;
grant dba to gyj1;
impdp test1/test1 directory=dump_file_dir dumpfile=gyj20120922.dmp remap_schema=gyj:test1 remap_tablespace=USERS:TP50,TP2:TP50,TP12:TP50 nologfile=y
注:select * from dba_datapump_jobs;
expdp gyj/gyj attach=gyj1.SYS_EXPORT_SCHEMA_01;
kill-->yes(kill_job此命令在11g中不能用);


2、表空間傳輸(Transportable Tablespace):OLTP
A庫:
exec dbms_tts.transport_set_check('OLTP',true); //檢查是否自包含(如索引,分割槽表,CLOB等不在一個表空間就不是自包含)
select * from transport_set_violations; //如果返回為空說明滿足自包含
create directory dir_prod as ‘/home/oracle/dir’;
alter tablespace oltp read only;
expdp system/oracle dumpfile=oltp.dmp directory=dir_prod TRANSPORT_TABLESPACES=oltp;
scp /u01/app/oracle/oradata/PROD/Disk1/oltp1.dbf oracle@192.168.0.3:/u01/app/oracle/oradata/EMREP/
scp /home/oracle/dir/oltp.dmp oracle@192.168.0.3:/home/oracle/dir
B庫:
create directory dir_prod as ‘/home/oracle/dir’;
create user oltp_user identified by oracle;
grant dba to oltp_user;
impdp system/oracle@emrep dumpfile='oltp.dmp' directory= dir_emrep transport_datafiles='/u01/app/oracle/oradata/EMREP/oltp1.dbf','/u01/app/oracle/oradata/EMREP/batch.dbf'
A、B庫：
alter tablespace oltp read write;
*************************************
alter database mount;
alter database datafile '/u01/app/oracle/oradata/bxdb/tp1.dbf' offline drop ;
alter database open;
drop tablespace tp1 including contents and datafiles cascade constraints;
***************************************

3、SQL*Loader
(1)建表
create table gyj_yktjsxxb(sfzh varchar2(20),xm varchar2(20));
(2)記事本中的資料
vi t_jzgxx_sm.txt
432325197810254019|Tom|
433101196303180048|Joe|
433101195709120024|Rose|
43310119691229004x|Jack|
433101196711270050|Linda|
433101197306101514|Jeny|

vi ytkjsxx.ctl
load data
infile '/home/oracle/t_jzgxx_sm.txt'
into table gyj_yktjsxxb
fields terminated by '|'
(sfzh,xm)

sqlldr userid=gyj/gyj control=/home/oracle/ytkjsxx.ctl


4、外部表
(1)、Loader生成外部表載入檔案：Administrator's Guide 15章結尾 create external table
In the scripts directory, you will find prod_master.dat and prod_master.ctl. Using the information found in these files,
create an external table names PROD_MASTER in the SH schema of the PROD database.
[oracle@rptdb1 external]$ cat prod_master.ctl
load data
infile '/home/oracle/dir/prod_master.dat'
append
into table costs
fields terminated by ","
(channel_id,prod_id,promo_id,time_id,unit_cost,unit_price)
[oracle@rptdb1 external]$
[oracle@rptdb1 external]$ cat prod_master.dat
6,6,6,6,6,600
7,7,7,7,7,700

create directory dir as '/opt/oracrs/external';
grant read,write on directory dir to sh;
create table costs(channel_id number,prod_id number,promo_id number,time_id number,unit_cost number,unit_price number
);
sqlldr gyj/gyj control=prod_master.ctl external_table=GENERATE_ONLY


(2)、Datapump外部表解除安裝：Database Data Warehousing Guide 12章結尾部分Extracting into Export Files Using External Tables
CREATE TABLE ext_t1
ORGANIZATION external
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY dump_file_dir
location ('t1.dat')
)
as SELECT * FROM T1 WHERE ID=1;


(3)、Datapump外部表載入
SQL> create table ext_t1
(id number,
name varchar2(100)
)
organization external
(
type oracle_datapump
default directory DUMP_FILE_DIR
location ('t1.dat')
);


**********本部落格所有內容均為原創，如有轉載請註明作者和出處!!!**********
Name: guoyJoe
QQ: 252803295
Email: oracledba_cn@hotmail.com
Blog:http://blog.csdn.net/guoyJoe
ITPUB:http://www.itpub.net/space-uid-28460966.html
OCM:http://education.oracle.com/education/otn/YGuo.HTM
_____________________________________________________________