'2010/03/04'에 해당되는 글 3건

  1. 2010.03.04 Table : Partitioned Table
  2. 2010.03.04 Table : 테이블의 공간 할당과 해제
  3. 2010.03.04 Table : Row Migration과 Chaining

Table : Partitioned Table

Oracle 10g/10g - 실습
posted by I유령I 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

Table : 테이블의 공간 할당과 해제

Oracle 10g/10g - 실습
posted by I유령I 2010. 3. 4. 11:30
실습: 테이블에 대한 공간 할당과 해제


SQL> analyze table hr.employees compute statistics;
analyze table hr.employees compute statistics
*
ERROR at line 1:
ORA-01502: index 'HR.EMP_NAME_IX' or partition of such index is in unusable state


SQL> select table_name, index_name, status from dba_indexes
  2  where table_name = 'EMPLOYEES';

TABLE_NAME                     INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
EMPLOYEES                      EMP_EMAIL_UK                   UNUSABLE
EMPLOYEES                      EMP_EMP_ID_PK                  UNUSABLE
EMPLOYEES                      EMP_DEPARTMENT_IX              UNUSABLE
EMPLOYEES                      EMP_JOB_IX                     UNUSABLE
EMPLOYEES                      EMP_MANAGER_IX                 UNUSABLE
EMPLOYEES                      EMP_NAME_IX                    UNUSABLE

6 rows selected.

SQL> alter index hr.emp_email_uk rebuild;

Index altered.

SQL> alter index hr.emp_emp_id_pk rebuild;

Index altered.

SQL> alter index hr.emp_department_ix rebuild;

Index altered.

SQL> alter index hr.emp_job_ix rebuild;

Index altered.

SQL> alter index hr.emp_manager_ix rebuild;

Index altered.

SQL> alter index hr.emp_name_ix rebuild;

Index altered.

SQL> select table_name, index_name, status from dba_indexes
  2  where table_name = 'EMPLOYEES';

TABLE_NAME                     INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
EMPLOYEES                      EMP_EMAIL_UK                   VALID
EMPLOYEES                      EMP_EMP_ID_PK                  VALID
EMPLOYEES                      EMP_DEPARTMENT_IX              VALID
EMPLOYEES                      EMP_JOB_IX                     VALID
EMPLOYEES                      EMP_MANAGER_IX                 VALID
EMPLOYEES                      EMP_NAME_IX                    VALID

6 rows selected.

SQL> analyze table hr.employees compute statistics;

Table analyzed.

SQL> select num_rows, blocks,empty_blocks as empty, avg_space, avg_row_len
  2  from dba_tables
  3  where owner = 'HR' and table_name = 'EMPLOYEES';

  NUM_ROWS     BLOCKS      EMPTY  AVG_SPACE AVG_ROW_LEN
---------- ---------- ---------- ---------- -----------
       107          5          3       4129          71
#NUM_ROWS : table에 저장된 row의 수
#BLOCKS : 사용중인 block (H/W 왼쪽 block의 수)
#EMPTY : 사용한 적이 없는 block (H/W 오른쪽 block의 수)
#AVG_SPACE : 사용중인 block의 평균 빈 공간 (Byte)
#AVG_ROW_LEN : row의 평균 길이

SQL> alter table hr.employees allocate extent (size 1m);

Table altered.

SQL> analyze table hr.employees compute statistics;

Table analyzed.

SQL> select num_rows, blocks,empty_blocks as empty, avg_space, avg_row_len
  2  from dba_tables
  3  where owner = 'HR' and table_name = 'EMPLOYEES';

  NUM_ROWS     BLOCKS      EMPTY  AVG_SPACE AVG_ROW_LEN
---------- ---------- ---------- ---------- -----------
       107          5        251       4129          71

SQL> alter table hr.employees deallocate unused;

Table altered.

SQL> select num_rows, blocks,empty_blocks as empty, avg_space, avg_row_len
  2  from dba_tables
  3  where owner = 'HR' and table_name = 'EMPLOYEES';

  NUM_ROWS     BLOCKS      EMPTY  AVG_SPACE AVG_ROW_LEN
---------- ---------- ---------- ---------- -----------
       107          5        251       4129          71

SQL> analyze table hr.employees compute statistics;

Table analyzed.

SQL> select num_rows, blocks,empty_blocks as empty, avg_space, avg_row_len
  2  from dba_tables
  3  where owner = 'HR' and table_name = 'EMPLOYEES';

  NUM_ROWS     BLOCKS      EMPTY  AVG_SPACE AVG_ROW_LEN
---------- ---------- ---------- ---------- -----------
       107          5          3       4129          71

SQL> select table_name, initial_extent, min_extents          
  2  from dba_tables
  3  where owner = 'HR' and table_name = 'EMPLOYEES';

TABLE_NAME                     INITIAL_EXTENT MIN_EXTENTS
------------------------------ -------------- -----------
EMPLOYEES                               65536           1

SQL> select tablespace_name, block_size, initial_extent, min_extents
  2  from dba_tablespaces
  3  where tablespace_name = 'EXAMPLE';

TABLESPACE_NAME      BLOCK_SIZE INITIAL_EXTENT MIN_EXTENTS
-------------------- ---------- -------------- -----------
EXAMPLE                    8192          65536           1

SQL> 
저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

Constraints 실습 (제약 조건 테스트)  (0) 2010.03.05
Table : Partitioned Table  (0) 2010.03.04
Table : Row Migration과 Chaining  (0) 2010.03.04
Undo Tablespace 생성과 관리 (9i)  (0) 2010.03.02
Temporary Tablespace 생성과 Default Temporary Tablespace 지정  (0) 2010.03.02

Table : Row Migration과 Chaining

Oracle 10g/10g - 실습
posted by I유령I 2010. 3. 4. 11:19
실습: 테이블의 상태 확인과 Rebuild


[oracle@ghost sql]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 4 10:52:20 2010

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> col tablespace_name format a20
SQL> analyze table hr.employees compute statistics;

Table analyzed.

SQL> select num_rows, chain_cnt from dba_tables where table_name = 'EMPLOYEES';

  NUM_ROWS  CHAIN_CNT
---------- ----------
       107          0

SQL> create tablespace test         
  2  datafile '/home/oracle/oradata/testdb/test01.dbf' size 10m
  3  segment space management auto;

Tablespace created.

SQL> alter user hr quota 1m on test;

User altered.

SQL> conn hr/hr
Connected.
SQL> alter table employees move tablespace test;

Table altered.

SQL> conn / as sysdba
Connected.
SQL> select owner, table_name, tablespace_name from dba_tables
  2  where owner = 'HR' and table_name = 'EMPLOYEES';

OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ --------------------
HR                             EMPLOYEES                      TEST

SQL> alter table hr.employees move tablespace example;

Table altered.

SQL> select owner, table_name, tablespace_name from dba_tables
  2  where owner = 'HR' and table_name = 'EMPLOYEES';

OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ --------------------
HR                             EMPLOYEES                      EXAMPLE

SQL> 
저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

Table : Partitioned Table  (0) 2010.03.04
Table : 테이블의 공간 할당과 해제  (0) 2010.03.04
Undo Tablespace 생성과 관리 (9i)  (0) 2010.03.02
Temporary Tablespace 생성과 Default Temporary Tablespace 지정  (0) 2010.03.02
Tablespace 실습 #6  (0) 2010.02.27

티스토리툴바