[oracle] exp/imp 방법

출처 : http://blog.naver.com/egg45pig/80055553825

참고 : http://blog.naver.com/keypush/10076745763

 

== exp ==

1.tablespace data위치 확인

sql> select t.name, d.status, d.name from v$tablespace t, v$datafile d where t.ts#=d.ts#;

 

2.user 확인

sql> select tablespace_name from dba_tablespaces;

 

3.exp 백업

# exp your_user/your_pass file='백업파일명'

 

== imp ==

1.user / tablespace 확인. 있을시 삭제

sql> drop user your_user cascade;

--> user접속으로 인하여 삭제 안될때 확인 : select sid, serial# from v$session where username='your_user';

sql> drop tablespace your_tablespace_data including contents;

sql> drop tablespace your_tablespace_idx including contents;

sql> drop tablespace your_tablespace_temp including contents;

 

2.tablespace dbf 파일 삭제(확인된 위치의 tablespace 삭제)

sql> select t.name, d.status. d.name from v$tablespace t, v$datafile d where t.ts# = d.ts#;

 

3.tablespace 생성

sql> create tablespace your_tablespace_data datafile '/oracle/oradata/your_tablespace_data.dbf' size 300m autoextend on;

sql> create tablespace your_tablespace_idx datafile '/oracle/oradata/your_tablespace_idx.dbf' size 200m autoextend on;

sql> create tablespace your_tablespace_temp datafile '/oracle/oradata/your_tablespace_temp.dbf' size 200m autoextend on;

 

4.user 생성

sql> create user your_user identified by your_pass default tablespace your_tablespace_data temporary tablespaceyour_tablespace_temp;

 

5.user 권한

sql> grant connect, dba, resource, exp_full_database, imp_full_database to your_user;

 

6.imp

# imp your_user/your_pass file='백업파일명'

[출처] oracle exp/imp 방법|작성자 아작