반응형
/********************************************************************************************
-- Title : [8i] 테이블 관리[RID, HWM, Chaining, Migration] (OLN)
-- Reference : OLN
-- Key word : table rowid high water mark
********************************************************************************************/
/****************************************************************************************
-- 사용되지 않은 열을 가진 테이블 식별
****************************************************************************************/
SQL> SELECT *
2 FROM dba_unused_col_tabs;
-- Title : [8i] 테이블 관리[RID, HWM, Chaining, Migration] (OLN)
-- Reference : OLN
-- Key word : table rowid high water mark
********************************************************************************************/
/****************************************************************************************
-- 사용되지 않은 열을 가진 테이블 식별
****************************************************************************************/
SQL> SELECT *
2 FROM dba_unused_col_tabs;
OWNER TABLE_NAME COUNT
------- --------------- ---------
SCOTT EMP_TTT 1
------- --------------- ---------
SCOTT EMP_TTT 1
/****************************************************************************************
-- 행 이전(Row Migration) 검사
****************************************************************************************/
SQL> ANALYZE TABLE scott.employees
2 ESTIMATE STATISTICS;
Table analyzed.
SQL> SELECT chain_cnt
2 FROM dba_tables
3 WHERE table_name = 'EMPLOYEES'
4 AND owner = 'SCOTT';
2 FROM dba_tables
3 WHERE table_name = 'EMPLOYEES'
4 AND owner = 'SCOTT';
CHAIN_CNT
-------------
0
-------------
0
/****************************************************************************************
-- 테이블 정보 검색
****************************************************************************************/
-- 테이블에 대한 정보 보기 예
SQL> SELECT t.table_name, o.data_object_id, s.header_file, s.header_block
2 FROM dba_tables t, dba_objects o, dba_segments s
3 WHERE t.owner=o.owner
4 AND t.table_name=o.object_name
5 AND t.owner=s.owner
6 AND t.table_name=s.segment_name
7 AND t.owner='SCOTT';
TABLE_NAME DATA_OBJECT_ID HEADER_FILE HEADER_BLOCK
----------- --------------- ------------ -------------
BONUS 3074 1 6977
DEPT 3070 1 6945
EMP 3072 1 6961
SALGRADE 3075 1 6985
...
-- 블록 사용법과 연결(chaning) 정보
SQL> SELECT blocks AS HWM, empty_blocks, chain_cnt AS "Chained Blocks"
2 FROM dba_tables
3 WHERE owner = 'SCOTT'
4 AND table_name = 'EMPLOYEES';
SQL> SELECT blocks AS HWM, empty_blocks, chain_cnt AS "Chained Blocks"
2 FROM dba_tables
3 WHERE owner = 'SCOTT'
4 AND table_name = 'EMPLOYEES';
HWM EMPTY_BLOCKS Chained Blocks
---------- ------------ --------------
3 28 0
---------- ------------ --------------
3 28 0
/****************************************************************************************
-- 익스텐트 정보 검색
****************************************************************************************/
-- 할당된 공간 분포
SQL> SELECT file_id, COUNT(*) AS Extents, SUM(blocks) AS Blocks
2 FROM dba_extents
3 WHERE owner = 'SCOTT'
4 AND segment_name = 'EMPLOYEES'
5 GROUP BY file_id;
FILE_ID EXTENTS BLOCKS
---------- ---------- ----------
5 1 32
---------- ---------- ----------
5 1 32
/****************************************************************************************
-- DBMS_ROWID 패키지 함수
****************************************************************************************/
이름 설명
────────────┬─────────────────────────────
ROWID_CREATE │ 각 구성 요소로부터 ROWID 생성
ROWID_OBJECT │ ROWID에 대해 오브젝트 식별자 리턴
ROWID_RELATIVE_FNO │ ROWID에 대해 상대적 파일 번호 리턴
ROWID_BLOCK_NUMBER │ ROWID에 대해 블록 번호 리턴
ROWID_ROW_NUMBER │ ROWID에 대해 열 번호 리턴
ROWID_TO_ABSOLUTE_FNO │ ROWID에 대해 절대 파일 번호 리턴
ROWID_TO_EXTENDED │ 구 rowid 타입을 새 rowid 타입으로 전환
ROWID_TO_RESTRICTED │ 새 rowid 타입을 구 rowid 타입으로 전환
────────────┴─────────────────────────────
-- 테이블내의 물리적 위치
SQL> SELECT deptno, ROWID,
2 DBMS_ROWID.ROWID_OBJECT(ROWID) AS OBJECT,
3 DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) AS "RELATIVE FILE",
4 DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) AS BLOCK
5 FROM scott.employees;
DEPTNO ROWID OBJECT RELATIVE FILE BLOCK
------- ------------------ ------- -------------- ------
20 AAAAxYAAFAAAAADAAA 3160 5 3
30 AAAAxYAAFAAAAADAAB 3160 5 3
30 AAAAxYAAFAAAAADAAC 3160 5 3
20 AAAAxYAAFAAAAADAAD 3160 5 3
...
SQL> SELECT deptno, ROWID,
2 DBMS_ROWID.ROWID_OBJECT(ROWID) AS OBJECT,
3 DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) AS "RELATIVE FILE",
4 DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) AS BLOCK
5 FROM scott.employees;
DEPTNO ROWID OBJECT RELATIVE FILE BLOCK
------- ------------------ ------- -------------- ------
20 AAAAxYAAFAAAAADAAA 3160 5 3
30 AAAAxYAAFAAAAADAAB 3160 5 3
30 AAAAxYAAFAAAAADAAC 3160 5 3
20 AAAAxYAAFAAAAADAAD 3160 5 3
...
-- 절대 파일 번호(Absolute File Number) 산출
SQL> SELECT deptno, ROWID,
2 DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO(ROWID, 'SCOTT', 'EMPLOYEES') AS "FILE"
3 FROM scott.employees;
SQL> SELECT deptno, ROWID,
2 DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO(ROWID, 'SCOTT', 'EMPLOYEES') AS "FILE"
3 FROM scott.employees;
DEPTNO ROWID FILE
------- ------------------- -----
20 AAAAxYAAFAAAAADAAA 5
30 AAAAxYAAFAAAAADAAB 5
30 AAAAxYAAFAAAAADAAC 5
20 AAAAxYAAFAAAAADAAD 5
30 AAAAxYAAFAAAAADAAE 5
...
------- ------------------- -----
20 AAAAxYAAFAAAAADAAA 5
30 AAAAxYAAFAAAAADAAB 5
30 AAAAxYAAFAAAAADAAC 5
20 AAAAxYAAFAAAAADAAD 5
30 AAAAxYAAFAAAAADAAE 5
...
/****************************************************************************************
-- 정규(Regular) 테이블 생성 예
****************************************************************************************/
SQL> CREATE TABLE emp_T
2 ( empno CHAR(5) PRIMARY KEY
3 , ename VARCHAR2(10)
4 , salary NUMBER(5)
5 );
create table test
( name varchar2(100)
)
pctfree 20
pctused 60
initrans 2
maxtrans 121
storage (initial 10K
next 10K
pctincrease 50
minextents 2
)
tablespace user loggin;
( name varchar2(100)
)
pctfree 20
pctused 60
initrans 2
maxtrans 121
storage (initial 10K
next 10K
pctincrease 50
minextents 2
)
tablespace user loggin;
SQL> CREATE TABLE employees
2 ( empno NUMBER(4)
3 , last_name VARCHAR2(30)
4 , deptno NUMBER(2)
5 )
6 PCTFREE 20
7 PCTUSED 50
8 STORAGE(INITIAL 200K
9 NEXT 200K
10 PCTINCREASE 0
11 MAXEXTENTS 50
12 ) TABLESPACE user_data;
Table created.
2 ( empno NUMBER(4)
3 , last_name VARCHAR2(30)
4 , deptno NUMBER(2)
5 )
6 PCTFREE 20
7 PCTUSED 50
8 STORAGE(INITIAL 200K
9 NEXT 200K
10 PCTINCREASE 0
11 MAXEXTENTS 50
12 ) TABLESPACE user_data;
Table created.
/****************************************************************************************
-- 임시 테이블
****************************************************************************************/
SQL> CREATE GLOBAL TEMPORARY TABLE employee_temp
2 AS SELECT * FROM emp;
-- 임시 테이블
****************************************************************************************/
SQL> CREATE GLOBAL TEMPORARY TABLE employee_temp
2 AS SELECT * FROM emp;
Table created.
/****************************************************************************************
-- 기존 테이블 복사
****************************************************************************************/
SQL> CREATE TABLE new_emp
2 STORAGE(INITIAL 200K
3 NEXT 200K
4 PCTINCREASE 0
5 MAXEXTENTS 50
6 )
7 NOLOGGING
8 TABLESPACE user_data
9 AS
10 SELECT *
11 FROM scott.employees;
Table created.
/****************************************************************************************
-- 스토리지와 블록 활용 파라미터 변경
****************************************************************************************/
SQL> ALTER TABLE scott.employees
2 PCTFREE 30
3 PCTUSED 50
4 STORAGE(NEXT 500K
5 MINEXTENTS 2
6 MAXEXTENTS 100
7 );
Table altered.
/****************************************************************************************
-- 수동으로 익스텐트 할당
****************************************************************************************/
SQL> ALTER TABLE scott.employees
2 ALLOCATE EXTENT(size 500K
3 DATAFILE '/export/home/oracle8i/iORCL/data/user_data_01.dbf');
Table altered.
/****************************************************************************************
-- 분할되지 않은 테이블 재구성
****************************************************************************************/
-- 한 테이블스페이스에서 다른 테이블스페이스로 테이블을 이동할 경우
-- 행 이전을 제거하기 위해 테이블을 재구성할 경우
-- 재구성 후 인덱스 재구성 필요
SQL> ALTER TABLE employees
2 MOVE TABLESPACE users;
Table altered.
/****************************************************************************************
-- DBMS_SPACE.UNUSED_SPACE를 이용하여 High Water Mark 찾기
****************************************************************************************/
SQL> set serveroutput on
SQL> DECLARE
2 v_owner VARCHAR2(30) := 'SCOTT';
3 v_segment_name VARCHAR2(30) := 'EMPLOYEES';
4 v_segment_type VARCHAR2(30) := 'TABLE';
5 v_total_blocks NUMBER;
6 v_total_bytes NUMBER;
7 v_unused_blocks NUMBER;
8 v_unused_bytes NUMBER;
9 v_last_used_extent_file_id NUMBER;
10 v_last_used_extent_block_id NUMBER;
11 v_last_used_block NUMBER;
12
13 BEGIN
14 dbms_space.unused_space( v_owner
15 , v_segment_name
16 , v_segment_type
17 , v_total_blocks
18 , v_total_bytes
19 , v_unused_blocks
20 , v_unused_bytes
21 , v_last_used_extent_file_id
22 , v_last_used_extent_block_id
23 , v_last_used_block
24 );
25
26 dbms_output.put_line(INITCAP(v_segment_type)||' : '||v_owner||'.'||v_segment_name);
27 dbms_output.put_line('Total Blocks : '||TO_CHAR(v_total_blocks));
28 dbms_output.put_line('Blocks above HWM : '||TO_CHAR(v_unused_blocks));
29 END;
2 v_owner VARCHAR2(30) := 'SCOTT';
3 v_segment_name VARCHAR2(30) := 'EMPLOYEES';
4 v_segment_type VARCHAR2(30) := 'TABLE';
5 v_total_blocks NUMBER;
6 v_total_bytes NUMBER;
7 v_unused_blocks NUMBER;
8 v_unused_bytes NUMBER;
9 v_last_used_extent_file_id NUMBER;
10 v_last_used_extent_block_id NUMBER;
11 v_last_used_block NUMBER;
12
13 BEGIN
14 dbms_space.unused_space( v_owner
15 , v_segment_name
16 , v_segment_type
17 , v_total_blocks
18 , v_total_bytes
19 , v_unused_blocks
20 , v_unused_bytes
21 , v_last_used_extent_file_id
22 , v_last_used_extent_block_id
23 , v_last_used_block
24 );
25
26 dbms_output.put_line(INITCAP(v_segment_type)||' : '||v_owner||'.'||v_segment_name);
27 dbms_output.put_line('Total Blocks : '||TO_CHAR(v_total_blocks));
28 dbms_output.put_line('Blocks above HWM : '||TO_CHAR(v_unused_blocks));
29 END;
Table : SCOTT.EMPLOYEES
Total Blocks : 32
Blocks above HWM : 28
Total Blocks : 32
Blocks above HWM : 28
PL/SQL procedure successfully completed.
/****************************************************************************************
-- 사용되지 않은 공간 할당 해제
****************************************************************************************/
SQL> ALTER TABLE scott.employees
2 DEALLOCATE UNUSED;
Table altered.
/****************************************************************************************
-- 테이블 잘라 버리기(Truncate)
****************************************************************************************/
SQL> TRUNCATE TABLE TEST_T;
Table truncated.
SQL> desc TEST_T;
Name Null? Type
----------------- -------- ----------------------------
COL1 NUMBER
Name Null? Type
----------------- -------- ----------------------------
COL1 NUMBER
/****************************************************************************************
-- 테이블 삭제(Drop)
****************************************************************************************/
SQL> DROP TABLE scott.emp1
2 CASCADE CONSTRAINTS;
Table dropped.
/****************************************************************************************
-- 테이블 열 삭제
****************************************************************************************/
SQL> ALTER TABLE sales_T
2 DROP COLUMN person
3 CASCADE CONSTRAINTS CHECKPOINT 1000;
Table altered.
-- 아래 SQL문은 컬럼 삭제시 에러가 나서 중단된 경우 작업을 재개하는 명령문이다.
SQL> ALTER TABLE sales_T
2 DROP COLUMNS CONTINUE;
SQL> ALTER TABLE sales_T
2 DROP COLUMNS CONTINUE;
/****************************************************************************************
-- 테이블 열 삭제(UNUSED 옵션 사용)
****************************************************************************************/
SQL> ALTER TABLE new_emp /* unused로 사용되지 않은 열로 표시 */
2 SET UNUSED COLUMN deptno
3 CASCADE CONSTRAINTS;
-- 테이블 열 삭제(UNUSED 옵션 사용)
****************************************************************************************/
SQL> ALTER TABLE new_emp /* unused로 사용되지 않은 열로 표시 */
2 SET UNUSED COLUMN deptno
3 CASCADE CONSTRAINTS;
Table altered.
SQL> ALTER TABLE new_emp /* 사용되지 않은 열로 표시된 컬럼 삭제 */
2 DROP UNUSED COLUMNS CHECKPOINT 500;
2 DROP UNUSED COLUMNS CHECKPOINT 500;
Table altered.
SQL> ALTER TABLE new_emp /* 열 삭제 작업 오류 후 계속 수행 */
2 DROP COLUMNS CONTINUE CHECKPOINT 1000;
2 DROP COLUMNS CONTINUE CHECKPOINT 1000;
/****************************************************************************************
-- 테이블 구조 검증
****************************************************************************************/
SQL> ANALYZE TABLE scott.employees
2 VALIDATE STRUCTURE;
-- 테이블 구조 검증
****************************************************************************************/
SQL> ANALYZE TABLE scott.employees
2 VALIDATE STRUCTURE;
Table analyzed.
/****************************************************************************************
-- 행 이전(Row Migration) 검사
****************************************************************************************/
SQL> ANALYZE TABLE scott.employees
2 ESTIMATE STATISTICS;
Table analyzed.
SQL> SELECT chain_cnt
2 FROM dba_tables
3 WHERE table_name = 'EMPLOYEES'
4 AND owner = 'SCOTT';
2 FROM dba_tables
3 WHERE table_name = 'EMPLOYEES'
4 AND owner = 'SCOTT';
CHAIN_CNT
-------------
0
-------------
0
/****************************************************************************************
-- CHAINING & MIGRATION 검사 예
****************************************************************************************/
-- 테이블 생성
SQL> create table chain (name varchar2(100));
Table created.
-- 데이터 입력(여러번)
SQL> insert into chain values ('a');
SQL> insert into chain values ('a');
1 row created.
SQL> insert into chain select * from chain;
1 row created.
-- 데이터 수정
SQL> update chain
2 set name = 'sdfsdf asdfasdfa asdfasdfasdf asdfasdfasdf asdfasdfas asdfasdf ';
SQL> update chain
2 set name = 'sdfsdf asdfasdfa asdfasdfasdf asdfasdfasdf asdfasdfas asdfasdf ';
64 rows updated.
SQL> commit;
Commit complete.
-- 제공되는 스크립트 실행
SQL> @$ORACLE_HOME/rdbms/admin/utlchain
SQL> @$ORACLE_HOME/rdbms/admin/utlchain
Table created.
-- 테이블 체인 분석
SQL> analyze table chain list chained rows;
SQL> analyze table chain list chained rows;
Table analyzed.
-- 체인된 로우 확인
select * from chained_rows;
select * from chained_rows;
-- 체인된 ROW 제거하고 다시 입력
create table chain_temp
as select * from chain
where rowid in (select head_rowid
from chained_rows
where table_name = 'CHAIN'
);
create table chain_temp
as select * from chain
where rowid in (select head_rowid
from chained_rows
where table_name = 'CHAIN'
);
delete from chain
where rowid in (select head_rowid
from chained_rows
where table_name = 'CHAIN'
);
where rowid in (select head_rowid
from chained_rows
where table_name = 'CHAIN'
);
commit;
insert into chain select * from chain_temp;
commit;
-- 다시 분석, 이상 없을 시 chain_temp 삭제
delete from chained_rows;
analyze table chain list chained rows;
select * from chained_rows;
drop table chain_temp;
delete from chained_rows;
analyze table chain list chained rows;
select * from chained_rows;
drop table chain_temp;
/****************************************************************************************
-- Partiotioned 테이블 생성 예
****************************************************************************************/
SQL> CREATE TABLE sales_T
2 ( acct_no NUMBER(5)
3 , person VARCHAR2(30)
4 , sales_amount NUMBER(8)
5 , week_no number(2)
6 ) PARTITION BY RANGE(week_no)
7 ( PARTITION P1 VALUES LESS THAN (8) TABLESPACE users
8 , PARTITION P2 VALUES LESS THAN (20) TABLESPACE user_data
9 );
Table created.
/****************************************************************************************
-- VARRAY와 NESTED TABLE을 사용한 예
****************************************************************************************/
SQL> CREATE TYPE empno_array_type AS varray(30) OF NUMBER(5);
Type created.
SQL> CREATE TABLE dept_varray
2 ( deptno NUMBER(3)
3 , dname VARCHAR2(20)
4 , empno empno_array_type);
2 ( deptno NUMBER(3)
3 , dname VARCHAR2(20)
4 , empno empno_array_type);
Table created.
SQL> CREATE TYPE empno_table_type AS TABLE OF NUMBER(5);
Type created.
SQL> CREATE TABLE dept_table
2 ( deptno NUMBER(3)
3 , dname VARCHAR2(20)
4 , empno empno_table_type);
ORA-22913: must specify table name for nested table column or attribute /* 왜 에러날까? */
2 ( deptno NUMBER(3)
3 , dname VARCHAR2(20)
4 , empno empno_table_type);
ORA-22913: must specify table name for nested table column or attribute /* 왜 에러날까? */
/****************************************************************************************
-- REF을 사용한 예
****************************************************************************************/
SQL> CREATE TYPE emp_type AS OBJECT
2 ( empno NUMBER(5)
3 , ename VARCHAR2(20)
4 );
Type created.
SQL> CREATE TABLE emp_ref of emp_type;
Table created.
SQL> CREATE TABLE dept_ref2
2 ( deptno NUMBER(5)
3 , dname VARCHAR2(20)
4 , mgr REF emp_type SCOPE IS emp_ref);
2 ( deptno NUMBER(5)
3 , dname VARCHAR2(20)
4 , mgr REF emp_type SCOPE IS emp_ref);
Table created.
반응형