Tablespace export, import

tip) putty 창에서 실행

set linesize 100

col tablespace_name format a25
col file_name format a60

#) tablespace 생성

create tablespace insa_tbs datafile '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf' size 10m;

create table hr.emp tablespace insa_tbs as select * from hr.employees;

select tablespace_name, file_name from dba_data_files;

#) 생성되었는지 확인 

select f.file_name
from dba_extents e, dba_data_files f
where f.file_id = e.file_id
and e.segment_name = 'EMP'
and e.owner = 'HR';

 

[oracle1@oracle ~]$ exp system/oracle file=insa_tbs.dmp tablespaces=insa_tbs(세그멘트까지)

[oracle1@oracle ~]$ ls -lh insa_tbs.dmp

 

#) oracle 접속후 hr.emp 확인 

drop tablespace insa_tbs including contents and datafiles;
select count(*) from hr.emp;

해결방법
1) drop한 tablespace 생성

create tablespace insa_tbs datafile '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf' size 10m;

2) 생성된 tablespace 확인

select tablespace_name, file_name from dba_data_files;

3) tablespace 를 import 하기
SQL> !
[oracle1@oracle ~]$ imp system/oracle file=insa_tbs.dmp tablespaces=insa_tbs full=y

4) tablespace 확인해보기

select f.file_name
from dba_extents e, dba_data_files f
where f.file_id = e.file_id
and e.segment_name = 'EMP'
and e.owner = 'HR';

5) hr.emp 확인 

select count(*) from hr.emp;

 

#) 필요없는 insa_tbs 삭제

drop tablespace insa_tbs including contents and datafiles;

select tablespace_name, file_name from dba_data_files;