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
