5-2 Data Warehouse Management - Partition Maintenance Operations

본 실습: 5-1 챕터에서 생성한 파티션 테이블 사용. 

https://yooliberalhouse.tistory.com/94

5-1 Data Warehouse Management - Partitioned tables and indexes using appropriate methods and keys

 

 

 

난이도: ★★★☆☆

문제 1:  range, hash, list 파티션 테이블에 새 파티션을 추가하라. 그리고 파티션 테이블을 interval partitioned table로 변환시켜 보아라. (7분)

 

ALTER TABLE ref_part ADD PARTITION p3 VALUES LESS THAN (30);

ALTER TABLE list_part ADD PARTITION part_sapporo VALUES ('SAPPORO');

ALTER TABLE tbl_hash_part1 ADD PARTITION;

/* 파티션 테이블을 Interval 파티션 테이블로 변환하기 */
ALTER TABLE range_part SET INTERVAL (NUMTOYMINTERVAL(1,'YEAR'));

 

 

 

 

 

난이도: ★★☆☆☆

문제 2:  range-list partitioned table에 새 파티션과 sub파티션을 추가하라. (7분)

 

CREATE TABLE tbl_list_range_test
(
	id		NUMBER,
	course	VARCHAR2(10)
)
PARTITION BY LIST (course)
	SUBPARTITION BY RANGE (id)
(
PARTITION part1 VALUES ('Oracle')
	(
	 SUBPARTITION part1_sub1 VALUES LESS THAN (10)
	)
);




ALTER TABLE tbl_list_range_test ADD PARTITION part2 VALUES ('PLSQL');
ALTER TABLE tbl_list_range_test MODIFY PARTITION part1 ADD SUBPARTITION part1_sub2 VALUES LESS THAN (20);

 

 

 

 

 

난이도: ★☆☆☆☆

문제 3: 파티션 테이블에서 파티션과 sub파티션을 drop하여라. (4분)

ALTER TABLE tbl_list_range_test DROP PARTITION part2;
ALTER TABLE tbl_list_range_test DROP SUBPARTITION part1_sub2;


ALTER TABLE tbl_list_range_test DROP SUBPARTITION part1_sub2 UPDATE INDEXES;

 

 

 

 

 

난이도: ★☆☆☆☆

문제 4:  hash 파티션에서 파티션을 coalescing하여라. (4분)

 

SELECT	COUNT(*)
FROM	dba_tab_partitions
WHERE	table_name = 'HASH_PART';


ALTER TABLE hash_part COALESCE PARTITION;


SELECT	COUNT(*)
FROM	dba_tab_partitions
WHERE	table_name = 'HASH_PART';

 

 

 

 

 

난이도: ★★☆☆☆

문제 5:  list 파티션 테이블을 추가하고, non 파티션 테이블로 convert하여라. (4분)

CREATE TABLE tbl_range_part_exch (id NUMBER)
PARTITION BY RANGE (id)
(
PARTITION part1 VALUES LESS THAN (100),
PARTITION part2 VALUES LESS THAN (200)
);
INSERT INTO tbl_range_part_exch VALUES (1);
INSERT INTO tbl_range_part_exch VALUES (150);
SELECT * FROM tbl_range_part_exch;


CREATE TABLE tbl_non_part_exch (id NUMBER);

ALTER TABLE tbl_range_part_exch EXCHANGE PARTITION part1 WITH TABLE tbl_non_part_exch;

/* EXCHANGE SUBPARTITION 구문도 가능하다.*/

 

 

 

 

 

난이도: ★★★★☆

문제 6:  online redefinition 기능을 사용하여 non 파티션 테이블을 파티션 테이블로 바꾸어라. (8분)

DBMS_REDEFINITION.CAN_REDEF_TABLE

DBMS_REDEFINITION.START_REDEF_TABLE

DBMS_REDEFINITION.SYNC_INTERIM_TABLE

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS  : (num_errors를 pls_integer로 출력할 필요)

DBMS_REDEFINITION.FINISH_REDEF_TABLE

CONN ryu/asdf0930k

CREATE TABLE tbl_non_part2 AS SELECT * FROM dba_objects;	
DELETE tbl_non_part2 WHERE object_id IS NULL;
ALTER TABLE tbl_non_part2 ADD CONSTRAINT table_pk PRIMARY KEY (object_id);


EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('RYU','TBL_NON_PART2');


CREATE TABLE tbl_partitioned_redef
PARTITION BY RANGE (object_id)
(
PARTITION part1 VALUES LESS THAN (100),
PARTITION part2 VALUES LESS THAN (1000),
PARTITION part3 VALUES LESS THAN (10000),
PARTITION part4 VALUES LESS THAN (maxvalue)
)
AS
SELECT * FROM dba_objects WHERE 1 = 2; 


EXEC DBMS_REDEFINITION.START_REDEF_TABLE('RYU','TBL_NON_PART2','TBL_PARTITIONED_REDEF');


EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('RYU','TBL_NON_PART2','TBL_PARTITIONED_REDEF');


DECLARE
	num_errors	PLS_INTEGER := 0;
BEGIN
	DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('RYU','TBL_NON_PART2','TBL_PARTITIONED_REDEF',1,TRUE,TRUE,TRUE,FALSE,NUM_ERRORS,FALSE,FALSE);

	DBMS_OUTPUT.PUT_LINE('errors := ' || to_char(num_errors));

END;
/


EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('RYU','TBL_NON_PART2','TBL_PARTITIONED_REDEF');




SELECT	table_name, partition_name
FROM	dba_tab_partitions
WHERE	table_name = 'TBL_PARTITIONED_REDEF';

 

 

 

 

 

 

난이도: ★★☆☆☆

문제 7: 4개의 파티션이 있는 Range 파티션을 생성하고, 2개를 merge하여라. (6분)

 

CREATE TABLE tbl_range_part_merge
PARTITION BY range (object_id)
(
PARTITION part1 VALUES LESS THAN (100),
PARTITION part2 VALUES LESS THAN (200),
PARTITION part3 VALUES LESS THAN (300),
PARTITION part4 VALUES LESS THAN (400)
)
AS
SELECT * FROM dba_objects WHERE object_id < 400;


ALTER TABLE tbl_range_part_merge MERGE PARTITIONS part3,part4 INTO PARTITION part3_new UPDATE INDEXES;


SELECT	table_name,partition_name
FROM	dba_tab_partitions
WHERE	table_name='TBL_RANGE_PART_MERGE';

 

 

 

 

 

난이도: ★☆☆☆☆

문제 8:  파티션을 Rename하여라. (2분)

 

ALTER TABLE tbl_range_part_merge RENAME PARTITION part1 TO part1_new;

 

 

 

 

 

난이도: ★★☆☆☆

문제 9:  파티션을 Truncate하여라. (1분)

 

ALTER TABLE tbl_range_part_merge TRUNCATE PARTITION part1_new;


SELECT count(*) FROM tbl_range_part_merge PARTITION (part1_new);

 

 

 

 

 

난이도: ★★☆☆☆

문제 10:  새 테이블스페이스를 생성하고, 테이블의 일부 파티션을 이 테이블스페이스로 옮기고, 결과를 확인하라. (4분)

 

SELECT name FROM v$tablespace;

ALTER TABLE tbl_range_part_merge MOVE PARTITION part2 TABLESPACE users PARALLEL (DEGREE 2);


SELECT	table_name, partition_name, tablespace_name
FROM	dba_tab_partitions
WHERE	table_name='TBL_RANGE_PART_MERGE';

 

 

 

 

 

난이도: ★★★☆☆

문제 11: MAXVALUE값이 있는 range 파티션테이블을 생성하고 2개의 서로 다른 파티션로 분할하라. (6분)

 

CREATE TABLE tbl_range_part_split
PARTITION BY RANGE (object_id)
(
PARTITION part1 VALUES LESS THAN (100),
PARTITION part2 VALUES LESS THAN (maxvalue)
)
AS
SELECT * FROM dba_objects;



SELECT count(*) FROM tbl_range_part_split PARTITION (part1);
ALTER TABLE tbl_range_part_split SPLIT PARTITION part1 AT (50) INTO (PARTITION part1_1, PARTITION part1_2);

SELECT count(1) FROM tbl_range_part_split PARTITION (part1_1);
SELECT count(1) FROM tbl_range_part_split PARTITION (part1_2);



SELECT count(*) FROM tbl_range_part_split PARTITION (part2);
ALTER TABLE tbl_range_part_split SPLIT PARTITION part2 AT (10000) INTO (PARTITION part2_1, PARTITION part2_2);



SELECT		partition_name, high_value
FROM		dba_tab_partitions
WHERE		table_name = 'TBL_RANGE_PART_SPLIT';