Admin 1. 18 Moving Data

-- 데이터의 이전.

External Table   <- create (인식)-> insert into~

Loader   <- insert (빠름- log생성 X, 제약조건 X 가능)

expdp/impdp   <-10g 전용

exi/imp   <- 9i 전용 (BNR-블록 복구에 사용)

 

 

	data source	data target	복구 시점
BNF		자기 자신	현재 or 과거
export/import		다른 DB	과거(export 시점)

 

 

 

 

--exp/imp 사용법

 

[oracle@orcl ~]$ exp scott/tiger

       계속 엔터....(긍정적으로..ㅋ)

scott의 모든 내용을 하나의 파일로 저장

    Export file: expdat.dmp    <- 주의: 자동 덮어쓰기 됨. 기존파일 사라질 수 있음.

 

Export terminated successfully with warnings.

 

 S SYS> drop user scott cascade;

User dropped.
S SYS> create user scott identified by tiger;

User created.

S SYS> grant create session,create sequence, create tablespace,create table,

create procedure,create view, create synonym to scott;

Grant succeeded.

 

 

 

 [oracle@orcl ~]$ imp scott/tiger

 

 요거 하나 yes 할 것.

Import entire export file (yes/no): no > yes

 

 

 

 

 

 --Create Directory

S SYS> create directory dmpdir as '/home/oracle';

Directory created.

S SYS> grant write,read on directory dmpdir to scott,hr;

Grant succeeded.

S SCOTT> select * from all_directories;

OWNER                          DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------------------------------------
SYS                            DMPDIR
/home/oracle

 

 

 

 

 SQL*Loader

 -- 초간단 버전으로 연습.

 

[oracle@orcl ~]$ cat >> a.dat<<EOF
> 1,aa
> 2,bb
> 3,cc
> EOF

[oracle@orcl ~]$ cat >> a.ctl<<EOF
> load data
> infile 'a.dat'
> into table a
> fields terminated by ','
> (c1,c2)
> EOF

 

 S SCOTT> create table a(c1 number(4), c2 varchar2(20));

Table created.

S SCOTT> !
[oracle@orcl ~]$ sqlldr scott/tiger control=a.ctl log=a.log direct=y

SQL*Loader: Release 10.2.0.1.0 - Production on Tue Jun 16 14:35:08 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Load completed - logical record count 3.
[oracle@orcl ~]$ exit
exit

S SCOTT> select * from a;

        C1 C2
---------- --------------------
         1 aa
         2 bb
         3 cc

 

 p18-10 쪽 참고.

 

 

 

 Data Pump

 

S SYS> create directory dmpdir as '/home/oracle';

Directory created.

S SYS> grant write,read on directory dmpdir to scott,hr;

Grant succeeded.

S SCOTT> select * from all_directories;

OWNER                          DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------------------------------------
SYS                            DMPDIR
/home/oracle

 

 

[oracle@orcl ~]$ expdp help=y

 

 Export: Release 10.2.0.1.0 - Production on Tuesday, 16 June, 2009 15:23:32

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

The Data Pump export utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:

   Example: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

You can control how Export runs by entering the 'expdp' command followed
by various parameters. To specify parameters, you use keywords:

   Format:  expdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
   Example: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott
               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

Keyword               Description (Default)
------------------------------------------------------------------------------
ATTACH                Attach to existing job, e.g. ATTACH [=job name].
COMPRESSION           Reduce size of dumpfile contents where valid

......

 

 

 

[oracle@orcl ~]$ expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

 

 Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/scott.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:24:48

 

 

 

S SYS> drop user scott cascade;

User dropped.

S SYS> create user scott identified by tiger;

User created.

S SYS> grant dba to scott;   <------------- 이렇게 다 주면 안됨.... 귀찮으니 한거임

Grant succeeded.

 

[oracle@orcl ~]$ impdp scott/tiger directory=dmpdir dumpfile=scott.dmp

 

 

 [oracle@orcl ~]$ cat >>p.par<<EOF
> DIRECTORY=dmpdir dumpfile=scott2.dmp
> EOF

 [oracle@orcl ~]$ expdp scott/tiger parfile=p.par

 

 

-- Fine_Grained Object Selection

[oracle@orcl ~]$ expdp scott/tiger directory=dmpdir dumpfile=scott_emp30.dmp 

query=scott.emp:\"where deptno=30\" tables=emp

 

 

 --Transformations

    tablespace Import시 많이 사용됨.

remap_datafile='     ':'      '

 

remap_schema

[oracle@orcl ~]$ impdp system/oracle directory=dmpdir dumpfile=scott.dmp remap_schema='SCOTT':'HR'

    soctt의 expdp 한것을 hr에 impdp ..

 

 

 

 

 

 디렉토리 절 생략 가능.

 [oracle@orcl ~]$ expdp scott/tiger dumpfile=dmpdir:scott2.dmp

 

 [oracle@orcl ~]$ expdp scott/tiger dumpfile=dmpdir:scott3.dmp logfile=dmpdir:s.log