資料遷移

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 [email protected]:/u01/app/oracle/oradata/EMREP/

   scp /home/oracle/dir/oltp.dmp  [email protected]:/home/oracle/dir

   B庫:

   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:    [email protected]

Blog:      http://blog.csdn.net/guoyJoe

ITPUB:   http://www.itpub.net/space-uid-28460966.html

OCM:    http://education.oracle.com/education/otn/YGuo.HTM

_____________________________________________________________

加群驗證問題：哪些SGA結構是必需的，哪些是可選的？否則拒絕申請!!!

答案在：http://blog.csdn.net/guoyjoe/article/details/8624392

Oracle@Paradise 　總群:127149411