数据迁移

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
_____________________________________________________________
加群验证问题：哪些SGA结构是必需的，哪些是可选的？否则拒绝申请!!!

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

Oracle@Paradise 　总群:127149411

Oracle@Paradise No.1群:177089463(已满)

Oracle@Paradise No.2群:121341761

Oracle@Paradise No.3群:140856036