oracle, oracle교육, oracle강좌, oraclejava, javaoracle, 오라클, 오라클자바, 자바오라클, 오라클교육, 오라클강좌, 구로디지털 오라클, 구로 오라클, 가산 오라클
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 된다는 것이다.
===================================================================
