유저 단위 export시 테이블스페이스 관련

 

Q :

     유저 단위로 export를 받았습니다. 그러나 이 유저의 오브젝트들은 테이블스페이스가 제각각입니다. 이 dump파일을 한테이블 스페이스로 import 할 수 있습니까?

 

A :

    예, 할 수 있습니다.

  

 

## 테스트

1. 테스트용 테이블스페이스 생성
col tablespace_name for a50
col file_name for a50
set lines 200

select tablespace_name, file_name from dba_data_files;

TABLESPACE_NAME        FILE_NAME
-------------------------------------------------- --------------------------------------------------
SYSTEM         /u01/app/oracle/oradata/test/system01.dbf
UNDOTBS1        /u01/app/oracle/oradata/test/undotbs01.dbf
SYSAUX         /u01/app/oracle/oradata/test/sysaux01.dbf
USERS         /u01/app/oracle/oradata/test/users01.dbf

SQL> create tablespace test datafile '/u01/app/oracle/oradata/test/test01.dbf' size 100m;

Tablespace created.

SQL> create tablespace test2 datafile '/u01/app/oracle/oradata/test/test2_01.dbf' size 100m;

Tablespace created.

 

2. 유저 생성후 default tablespace를 지정(test 테이블스페이스)

SQL> create user test identified by test
  2  default tablespace test;

User created.

SQL> grant resource, connect to test;

Grant succeeded

3. test 유저로 접속후 테이블 생성 / 인덱스 생성 / 데이터 입력
SQL> conn test/test
Connected.
SQL> create table test_table (col1 varchar2(10), col2 number);

Table created.

SQL> insert into test_table values ('tests',2);

1 row created.

SQL> insert into test_table values ('eeee',3);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_table;

COL1      COL2
-------------------- ----------
tests         2
eeee         3

# index 생성 : 유저 default로 생성
SQL> create index test_index on test_table(col1);

Index created

# index 생성 : 테이블스페이스 test2로 지정
SQL> create index test_index2 on test_table(col1,col2)
  2  tablespace test2;

Index created.

# table 생성 : 테이블스페이스 test2로 지정
SQL> create table test_table2 (col1 number, col2 varchar2(10))
  2  tablespace test2;      

Table created.

SQL> insert into test_table2 values (1,'test');

1 row created.

SQL> commit;

4. export(test 유저)
[oracle@test ~]$ exp test/test file=exp.dmp log=exp.log

Export: Release 10.2.0.4.0 - Production on 월 9월 24 19:37:48 2012

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in KO16MSWIN949 character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TEST
About to export TEST's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export TEST's tables via Conventional Path ...
. . exporting table                     TEST_TABLE          2 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                    TEST_TABLE2          1 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.

5. 유저 삭제(오브젝트도 같이)
SQL> drop user test cascade;       

User dropped.

6. 테이블스페이스 삭제 : test2

SQL>  drop tablespace test2 including contents and datafiles;      

Tablespace dropped.

 

7. 유저 생성(import할 유저)
SQL> create user test identified by test
  2  default tablespace test;

User created.

SQL> grant resource, connect to test;

Grant succeeded.

8. import

[oracle@test ~]$ imp test/test file=exp.dmp log=imp.log

Import: Release 10.2.0.4.0 - Production on 월 9월 24 19:40:55 2012

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
import done in KO16MSWIN949 character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing TEST's objects into TEST
. . importing table                   "TEST_TABLE"          2 rows imported
. . importing table                  "TEST_TABLE2"          1 rows imported
Import terminated successfully without warnings.

8. 데이터 확인
  : 기존에 생성된 테이블과 인덱스 모두 유저의 default tablespace에 생성된다.
SQL> select table_name, tablespace_name from user_tables;

TABLE_NAME           TABLESPACE_NAME
------------------------------------------------------------ --------------------------------------------------
TEST_TABLE           TEST
TEST_TABLE2           TEST

SQL> select * from test_table;

COL1      COL2
-------------------- ----------
tests         2
eeee         3

SQL> select * from test_tables2;
select * from test_tables2
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select * from test_table2;

      COL1 COL2
---------- --------------------
  1 test

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------------------------------------
TEST_TABLE
TEST_TABLE2

SQL> select * from test_table;

COL1      COL2
-------------------- ----------
tests         2
eeee         3

SQL> select * from test_table2;

      COL1 COL2
---------- --------------------
  1 test

INDEX_NAME        TABLESPACE_NAME
-------------------------------------------------- --------------------------------------------------
TEST_INDEX        TEST
TEST_INDEX2        TEST