2010. 3. 4. 12:10
■ 리스트 분할 : 학과별 정보를 각각의 분할 세그먼트에 저장하는 예
SQL> select username, default_tablespace from dba_users
2 where username = 'HR';
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
HR USERS
SQL> alter user hr
2 identified by hr
3 account unlock;
User altered.
SQL> grant unlimited tablespace to hr;
Grant succeeded.
SQL> col tablespace_name format a20
SQL> col file_name format a45
SQL> create tablespace ts1
2 datafile '/home/oracle/oradata/testdb/ts01.dbf' size 2m
3 segment space management auto;
Tablespace created.
SQL> create tablespace ts2
2 datafile '/home/oracle/oradata/testdb/ts02.dbf' size 2m
3 segment space management auto;
Tablespace created.
SQL> create tablespace ts3
2 datafile '/home/oracle/oradata/testdb/ts03.dbf' size 2m
3 segment space management auto;
Tablespace created.
SQL> select tablespace_name, file_name
2 from dba_data_files
3 where tablespace_name like 'TS%';
TABLESPACE_NAME FILE_NAME
-------------------- ---------------------------------------------
TS1 /home/oracle/oradata/testdb/ts01.dbf
TS2 /home/oracle/oradata/testdb/ts02.dbf
TS3 /home/oracle/oradata/testdb/ts03.dbf
SQL> conn hr/hr
Connected.
SQL> @student <--- 없으므로 진행 불가 상태...
SQL> create table st (
2 sno varchar2(6) primary key,
3 sname varchar2(20),
4 sex varchar2(2),
5 syear number(1),
6 major varchar2(20),
7 avr number(4,2)
8 )
9 partition by list (major)
10 ( partition ma_ch values ('화학') tablespace ts1,
11 partition ma_ph values ('물리') tablespace ts2,
12 partition ma_all values ('생물', '식영', '유공') tablespace ts3);
Table created.
SQL> insert into st
2 select * from student;
select * from student
*
ERROR at line 2:
ORA-00942: table or view does not exist
SQL>
SQL> conn / as sysdba
Connected.
SQL> col owner format a10
SQL> col segment_name format a30
SQL> col tablespace_name format a20
SQL> col table_owner format a10
SQL> col table_name format a20
SQL> col partition_name format a20
SQL> select table_owner, table_name, partition_name, tablespace_name
2 from dba_tab_partitions
3 where table_owner = 'HR';
TABLE_OWNER TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ --------------------
HR ST MA_CH TS1
HR ST MA_PH TS2
HR ST MA_ALL TS3
SQL> select table_owner, table_name, partition_name, tablespace_name
2 from dba_tab_partitions
3 where table_owner = 'HR';
TABLE_OWNE TABLE_NAME PARTITION_NAME TABLESPACE_NAME
---------- -------------------- -------------------- --------------------
HR ST MA_CH TS1
HR ST MA_PH TS2
HR ST MA_ALL TS3
SQL> select owner, table_name, partitioned
2 from dba_tables
3 where owner = 'HR';
OWNER TABLE_NAME PAR
---------- -------------------- ---
HR REGIONS NO
HR LOCATIONS NO
HR DEPARTMENTS NO
HR JOBS NO
HR JOB_HISTORY NO
HR EMPLOYEES NO
HR STUDENT NO
HR COUNTRIES NO
HR ST YES
9 rows selected.
SQL>
주의사항
- Multicolumn Partition은 지원되지 않는다.
- NULL 값은 지정 가능하지만 MAXVAUES는 지정 불가능하다.
- 파티션 값의 리스트를 구성하는 문자열은 4K를 초과할 수 없다.
SQL> conn hr/hr
Connected.
SQL> alter table st
2 add partition ma_so values ('사회') tablespace ts1;
Table altered.
SQL> conn / as sysdba
Connected.
SQL> select table_owner, table_name, partition_name, tablespace_name
2 from dba_tab_partitions
3 where table_owner = 'HR';
TABLE_OWNE TABLE_NAME PARTITION_NAME TABLESPACE_NAME
---------- -------------------- -------------------- --------------------
HR ST MA_CH TS1
HR ST MA_SO TS1
HR ST MA_PH TS2
HR ST MA_ALL TS3
SQL> select owner, table_name, partitioned
2 from dba_tables
3 where owner = 'HR';
OWNER TABLE_NAME PAR
---------- -------------------- ---
HR REGIONS NO
HR LOCATIONS NO
HR DEPARTMENTS NO
HR JOBS NO
HR JOB_HISTORY NO
HR EMPLOYEES NO
HR STUDENT NO
HR COUNTRIES NO
HR ST YES
9 rows selected.
SQL>
■ 범위 분할
범위분할의 예제
create table sale (
no number primary key,
goods varchar2(20),
price number,
s_date date
)
partition by range (s_date)
( partition s1999 values less than (TO_DATE('01-01-2000', 'DD-MM-YYYY')) tablespace ts1,
partition s2000_01_03 values less than (TO_DATE('01-04-2000', 'DD-MM-YYYY')) tablespace ts2,
partition s2000_04_06 values less than (TO_DATE('01-07-2000', 'DD-MM-YYYY')) tablespace ts2,
partition s2000_07_09 values less than (TO_DATE('01-10-2000', 'DD-MM-YYYY')) tablespace ts3,
partition s2000_10 values less than (MAXVALUE) tablespace ts3);
SQL> conn hr/hr
Connected.
SQL> create table sale (
2 no number primary key,
3 goods varchar2(20),
4 price number,
5 s_date date
6 )
7 partition by range (price)
8 ( partition p100 values less than (100),
9 partition p200 values less than (200),
10 partition p300 values less than (300),
11 partition pr_max values less than (MAXVALUE));
Table created.
SQL> alter table sale
2 drop partition pr_max;
Table altered.
SQL> alter table sale
2 add partition p400 values less than (400);
Table altered.
SQL> alter table sale
2 add partition pr_max values less than (MAXVALUE)
Table altered.
SQL> conn / as sysdba
Connected.
SQL> select table_owner, table_name, partition_name, tablespace_name
2 from dba_tab_partitions
3 where table_owner = 'HR';
TABLE_OWNE TABLE_NAME PARTITION_NAME TABLESPACE_NAME
---------- -------------------- -------------------- --------------------
HR SALE PR_MAX USERS
HR SALE P100 USERS
HR SALE P200 USERS
HR SALE P300 USERS
HR SALE P400 USERS
HR ST MA_CH TS1
HR ST MA_SO TS1
HR ST MA_PH TS2
HR ST MA_ALL TS3
9 rows selected.
SQL> select owner, table_name, partitioned
2 from dba_tables
3 where owner = 'HR';
OWNER TABLE_NAME PAR
---------- -------------------- ---
HR REGIONS NO
HR LOCATIONS NO
HR DEPARTMENTS NO
HR JOBS NO
HR JOB_HISTORY NO
HR EMPLOYEES NO
HR STUDENT NO
HR COUNTRIES NO
HR ST YES
HR SALE YES
10 rows selected.
SQL>
■ 해시 분할
범위분할의 예제
create table st1 (
sno varchar2(6) primary key,
sname varchar2(20),
sex varchar2(2),
syear number(1),
major varchar2(20),
avr number(4,2)
)
partition by hash (sno)
partitions 8 store in (ts1, ts2, ts3);
SQL> conn / as sysdba
Connected.
SQL> create tablespace insa
2 datafile '/home/oracle/oradata/testdb/insa01.dbf' size 5m;
Tablespace created.
SQL> create user insa
2 identified by insa
3 default tablespace insa
4 quota unlimited on insa;
User created.
SQL> grant create session, create table to insa;
Grant succeeded.
SQL> conn insa/insa
Connected.
SQL> create table jikwon
2 ( sabun number(8),
3 name varchar2(10),
4 address varchar2(50),
5 salary number(8));
Table created.
#현재 로그인중인 계정이 사용 가능한 테이블 확인
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
JIKWON TABLE
#jikwon 테이블의 구조 확인
SQL> desc jikwon
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
SABUN NUMBER(8)
NAME VARCHAR2(10)
ADDRESS VARCHAR2(50)
SALARY NUMBER(8)
SQL> insert into jikwon
2 values (20071114, '천만득', '서울동작구', 200);
1 row created.
SQL> select * from jikwon;
SABUN NAME ADDRESS SALARY
---------- ---------- -------------------------------------------------- ----------
20071114 천만득 서울동작구 200
#서브쿼리절을 이용한 테이블 생성
SQL> create table jikwon2
2 as select * from jikwon;
Table created.
#서브쿼리절 이용시 특정 컬럼만으로 테이블 생성
SQL> create table jikwon3
2 as select sabun, name from jikwon;
Table created.
SQL> desc jikwon3;
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
SABUN NUMBER(8)
NAME VARCHAR2(10)
#서브쿼리절을 이용해 테이블 구조만 복사 (데이터는 복사하지 않음)
SQL> create table jikwon4
2 as select * from jikwon
3 where 1 = 2;
Table created.
#테이블 구조 변경
#컬럼 추가
SQL> alter table jikwon
2 add (phone number(10));
Table altered.
SQL> desc jikwon;
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
SABUN NUMBER(8)
NAME VARCHAR2(10)
ADDRESS VARCHAR2(50)
SALARY NUMBER(8)
PHONE NUMBER(10)
SQL> alter table jikwon
2 drop column phone;
Table altered.
SQL> desc jikwon;
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
SABUN NUMBER(8)
NAME VARCHAR2(10)
ADDRESS VARCHAR2(50)
SALARY NUMBER(8)
#컬럼 삭제
#컬럼 변경으로 데이터 타입, 크기, 기본값을 변경할 수있다.
#변경 대상 컬럼에 데이터가 없을 경우에는 자유롭지만
#데이터가 있을 경우 char, varchar2 타입만 변경이 가능하며 크기 변경 시 최소 같거나 커야만 한다.
SQL> alter table jikwon
2 modify name varchar2(15);
Table altered.
SQL> desc jikwon;
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
SABUN NUMBER(8)
NAME VARCHAR2(15)
ADDRESS VARCHAR2(50)
SALARY NUMBER(8)
#테이블 이름 변경
SQL> rename jikwon2 to jikwon6;
Table renamed.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
JIKWON TABLE
JIKWON3 TABLE
JIKWON4 TABLE
JIKWON6 TABLE
SQL> drop table jikwon6;
Table dropped.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
JIKWON TABLE
BIN$gPGFSrk7XRTgQKjADgB4bQ==$0 TABLE
JIKWON3 TABLE
JIKWON4 TABLE
SQL>
#테이블 삭제 시 다른 테이블과 제약조건이 걸려있으면 삭제가 되지 않는다.
#이럴 경우에는 cascade constraints 옵션을 사용하면 삭제가 가능하다.
SQL> drop table jikwon6 cascade constraints;
'Oracle 10g > 10g - 실습' 카테고리의 다른 글
| User(유저) 생성과 관리 (0) | 2010.03.05 |
|---|---|
| Constraints 실습 (제약 조건 테스트) (0) | 2010.03.05 |
| Table : 테이블의 공간 할당과 해제 (0) | 2010.03.04 |
| Table : Row Migration과 Chaining (0) | 2010.03.04 |
| Undo Tablespace 생성과 관리 (9i) (0) | 2010.03.02 |