export와 tablespace
오라클자바커뮤니티에서 설립한 오엔제이프로그래밍 실무교육센터
(신입사원채용무료교육, 오라클SQL, 튜닝, 힌트,자바프레임워크, 안드로이드, 아이폰, 닷넷)
oracle9i에서는 지정된 tablespace의 object를 export 받기위한 옵션으로 tablespace parameter를 사용할 수 있게 되었습니다. (8i에서 이 parameter는 다른 데이터베이스로 옮길 테이블스페이스를 지정하였으나 9i에서는 지정된 테이블스페이스의 object를 export 하겠다는 개념으로 바뀜)
이 parameter를 사용하기 위해서는 해당 user에게 export_full_database privilege가 부여되어 있어야 하는데 아래의 예제를 보면서 이해 바랍니다.
----------------------------------------------------------------
C:\Documents and Settings\이종철>sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Mar 11 08:48:31 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> create tablesapce test datafile
2 'c:\oracle\oradata\wink\test01.dbf' size 1m;
create tablesapce test datafile
*
ERROR at line 1:
ORA-00901: invalid CREATE command
SQL> create tablespace test datafile
2 'c:\oracle\oradata\wink\test01.dbf' size 1m;
Tablespace created.
SQL> conn scott/tiger
Connected.
SQL> create table mytest (
2 id number,
3 name varchar2(20),
4 addr varchar2(50),
5 amount number,
6 hiredate date)
7 partition by list(addr)
8 (
9 partition test_1 values ('서울','부산'),
10 partition test_2 values ('광주','대구'),
11 partition test_3 values ('제주','춘천') tablespace test,
12 partition test_4 values ('수원','대전') tablespace users
13 )
14 /
Table created.
SQL> insert into mytest values (1,'1길동','서울',10000, sysdate);
1 row created.
SQL> insert into mytest values (2,'2길동','부산',20000, sysdate);
1 row created.
SQL> insert into mytest values (3,'3길동','제주',30000, sysdate);
1 row created.
SQL> insert into mytest values (4,'4길동','춘천',40000, sysdate);
1 row created.
SQL> insert into mytest values (5,'5길동','대전',50000, sysdate);
1 row created.
SQL> select segment_name, partition_name
2 from user_segments
3 where tablespace_name = 'TEST';
SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME
------------------------------
MYTEST
TEST_3
SQL> conn / as sysdba
Connected.
SQL> grant exp_full_database to scott;
Grant succeeded.
SQL> grant imp_full_database to scott;
Grant succeeded.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
EXP-00000: Export terminated unsuccessfully
C:\Documents and Settings\이종철>exp userid=scott/tiger tablespaces=test
Export: Release 9.2.0.1.0 - Production on Fri Mar 11 08:59:16 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses KO16MSWIN949 character set (possible charset conversion)
About to export selected tablespaces ...
For tablespace TEST ...
. exporting cluster definitions
. exporting table definitions
. . exporting table MYTEST
. . exporting partition TEST_1 2 rows exported
. . exporting partition TEST_2 0 rows exported
. . exporting partition TEST_3 2 rows exported
. . exporting partition TEST_4 1 rows exported
. exporting referential integrity constraints
. exporting triggers
Export terminated successfully without warnings.
===================================================================
한가지 특이한 점은 지정된 tablespace 내의 object와 연관된 다른 tablespace의 object도 같이 export/import 된다는 것이다.
===================================================================
댓글 없음:
댓글 쓰기