파티션 테이블 관련 테스트

Notice

Recent Posts

Recent Comments

Link

« 2024/05 »
일	월	화	수	목	금	토
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31

Tags more

Archives

Today

Total

관리 메뉴

DBA가 되고 싶은 병아리

파티션 테이블 관련 테스트 본문

Oracle Study

파티션 테이블 관련 테스트

미스틱스 2021. 4. 28. 13:27

파티션 테이블 작성용 스크립트

CREATE TABLE sales

(sales_no NUMBER,

sale_year INT NOT NULL,

sale_month INT NOT NULL,

sale_day INT NOT NULL,

customer_name VARCHAR2(30),

price NUMBER)

PARTITION BY RANGE (sale_year, sale_month, sale_day)

(PARTITION sales_q1 VALUES LESS THAN (2020,02,01) TABLESPACE USERS,

PARTITION sales_q2 VALUES LESS THAN (2020,03,01) TABLESPACE USERS,

PARTITION sales_q3 VALUES LESS THAN (2020,04,01) TABLESPACE USERS,

PARTITION sales_q4 VALUES LESS THAN (2020,05,01) TABLESPACE USERS,

PARTITION sales_q5 VALUES LESS THAN (2020,06,01) TABLESPACE USERS,

PARTITION sales_max VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE) TABLESPACE USERS);

update SALES set PRICE=200 where sales_no='180';

alter table SALES split partition sales_max at (2020,07,01) into (partition sales_q6, partition sales_max);

desc sales;

col Type for a20

select TABLE_NAME,HIGH_VALUE from user_tab_partitions;

select owner,INDEX_NAME,TABLE_NAME from dba_indexes where TABLE_NAME='SALES';

alter table sales drop partition sales_q6;

alter table table_name drop partition partition_name;

파티션 테이블 데이터 스크립트

INSERT INTO sales VALUES(1,2020,1,1,'scott',3000);

INSERT INTO sales VALUES(2,2020,1,2,'tiger',3100);

INSERT INTO sales VALUES(3,2020,1,3,'rina',3200);

INSERT INTO sales VALUES(4,2020,1,4,'karim',3300);

INSERT INTO sales VALUES(5,2020,1,5,'orelian',3400);

INSERT INTO sales VALUES(6,2020,1,6,'Amelia',3500);

INSERT INTO sales VALUES(7,2020,1,7,'Avery',3600);

INSERT INTO sales VALUES(8,2020,1,8,'Adela',3700);

INSERT INTO sales VALUES(9,2020,1,9,'Adalee',3800);

INSERT INTO sales VALUES(10,2020,1,10,'Bibiane',3900);

INSERT INTO sales VALUES(11,2020,1,11,'Bianca',4000);

INSERT INTO sales VALUES(12,2020,1,12,'Bonita',4100);

INSERT INTO sales VALUES(13,2020,1,13,'Bono',4200);

INSERT INTO sales VALUES(14,2020,1,14,'Belita',4300);

INSERT INTO sales VALUES(15,2020,1,15,'Charlotte',4400);

INSERT INTO sales VALUES(16,2020,1,16,'Cynthia',4500);

INSERT INTO sales VALUES(17,2020,1,17,'Dana',4600);

INSERT INTO sales VALUES(18,2020,1,18,'Dior',4700);

INSERT INTO sales VALUES(19,2020,1,19,'Doris',4800);

INSERT INTO sales VALUES(20,2020,1,20,'Edith',4900);

INSERT INTO sales VALUES(21,2020,1,21,'Emma',5000);

INSERT INTO sales VALUES(22,2020,1,22,'Erica',5100);

INSERT INTO sales VALUES(23,2020,1,23,'Frances',5200);

INSERT INTO sales VALUES(24,2020,1,24,'Grace',5300);

INSERT INTO sales VALUES(25,2020,1,25,'Grania',5400);

INSERT INTO sales VALUES(26,2020,1,26,'Hannah',5500);

INSERT INTO sales VALUES(27,2020,1,27,'Haisley',5600);

INSERT INTO sales VALUES(28,2020,1,28,'Iliana',5700);

INSERT INTO sales VALUES(29,2020,1,29,'Irene',5800);

INSERT INTO sales VALUES(30,2020,1,30,'Isabel',5900);

INSERT INTO sales VALUES(31,2020,2,1,'scott',6000);

INSERT INTO sales VALUES(32,2020,2,2,'tiger',6100);

INSERT INTO sales VALUES(33,2020,2,3,'rina',6200);

INSERT INTO sales VALUES(34,2020,2,4,'karim',6300);

INSERT INTO sales VALUES(35,2020,2,5,'orelian',6400);

INSERT INTO sales VALUES(36,2020,2,6,'Amelia',6500);

INSERT INTO sales VALUES(37,2020,2,7,'Avery',6600);

INSERT INTO sales VALUES(38,2020,2,8,'Adela',6700);

INSERT INTO sales VALUES(39,2020,2,9,'Adalee',6800);

INSERT INTO sales VALUES(40,2020,2,10,'Bibiane',6900);

INSERT INTO sales VALUES(41,2020,2,11,'Bianca',7000);

INSERT INTO sales VALUES(42,2020,2,12,'Bonita',7100);

INSERT INTO sales VALUES(43,2020,2,13,'Bono',7200);

INSERT INTO sales VALUES(44,2020,2,14,'Belita',7300);

INSERT INTO sales VALUES(45,2020,2,15,'Charlotte',7400);

INSERT INTO sales VALUES(46,2020,2,16,'Cynthia',7500);

INSERT INTO sales VALUES(47,2020,2,17,'Dana',7600);

INSERT INTO sales VALUES(48,2020,2,18,'Dior',7700);

INSERT INTO sales VALUES(49,2020,2,19,'Doris',7800);

INSERT INTO sales VALUES(50,2020,2,20,'Edith',7900);

INSERT INTO sales VALUES(51,2020,2,21,'Emma',8000);

INSERT INTO sales VALUES(52,2020,2,22,'Erica',8100);

INSERT INTO sales VALUES(53,2020,2,23,'Frances',8200);

INSERT INTO sales VALUES(54,2020,2,24,'Grace',8300);

INSERT INTO sales VALUES(55,2020,2,25,'Grania',8400);

INSERT INTO sales VALUES(56,2020,2,26,'Hannah',8500);

INSERT INTO sales VALUES(57,2020,2,27,'Haisley',8600);

INSERT INTO sales VALUES(58,2020,2,28,'Iliana',8700);

INSERT INTO sales VALUES(59,2020,2,29,'Irene',8800);

INSERT INTO sales VALUES(60,2020,2,30,'Isabel',8900);

INSERT INTO sales VALUES(61,2020,3,1,'scott',9000);

INSERT INTO sales VALUES(62,2020,3,2,'tiger',9100);

INSERT INTO sales VALUES(63,2020,3,3,'rina',9200);

INSERT INTO sales VALUES(64,2020,3,4,'karim',9300);

INSERT INTO sales VALUES(65,2020,3,5,'orelian',9400);

INSERT INTO sales VALUES(66,2020,3,6,'Amelia',9500);

INSERT INTO sales VALUES(67,2020,3,7,'Avery',9600);

INSERT INTO sales VALUES(68,2020,3,8,'Adela',9700);

INSERT INTO sales VALUES(69,2020,3,9,'Adalee',9800);

INSERT INTO sales VALUES(70,2020,3,10,'Bibiane',9900);

INSERT INTO sales VALUES(71,2020,3,11,'Bianca',10000);

INSERT INTO sales VALUES(72,2020,3,12,'Bonita',10100);

INSERT INTO sales VALUES(73,2020,3,13,'Bono',10200);

INSERT INTO sales VALUES(74,2020,3,14,'Belita',10300);

INSERT INTO sales VALUES(75,2020,3,15,'Charlotte',10400);

INSERT INTO sales VALUES(76,2020,3,16,'Cynthia',10500);

INSERT INTO sales VALUES(77,2020,3,17,'Dana',10600);

INSERT INTO sales VALUES(78,2020,3,18,'Dior',10700);

INSERT INTO sales VALUES(79,2020,3,19,'Doris',10800);

INSERT INTO sales VALUES(80,2020,3,20,'Edith',10900);

INSERT INTO sales VALUES(81,2020,3,21,'Emma',11000);

INSERT INTO sales VALUES(82,2020,3,22,'Erica',11100);

INSERT INTO sales VALUES(83,2020,3,23,'Frances',11200);

INSERT INTO sales VALUES(84,2020,3,24,'Grace',11300);

INSERT INTO sales VALUES(85,2020,3,25,'Grania',11400);

INSERT INTO sales VALUES(86,2020,3,26,'Hannah',11500);

INSERT INTO sales VALUES(87,2020,3,27,'Haisley',11600);

INSERT INTO sales VALUES(88,2020,3,28,'Iliana',11700);

INSERT INTO sales VALUES(89,2020,3,29,'Irene',11800);

INSERT INTO sales VALUES(90,2020,3,30,'Isabel',11900);

INSERT INTO sales VALUES(91,2020,4,1,'scott',12000);

INSERT INTO sales VALUES(92,2020,4,2,'tiger',12100);

INSERT INTO sales VALUES(93,2020,4,3,'rina',12200);

INSERT INTO sales VALUES(94,2020,4,4,'karim',12300);

INSERT INTO sales VALUES(95,2020,4,5,'orelian',12400);

INSERT INTO sales VALUES(96,2020,4,6,'Amelia',12500);

INSERT INTO sales VALUES(97,2020,4,7,'Avery',12600);

INSERT INTO sales VALUES(98,2020,4,8,'Adela',12700);

INSERT INTO sales VALUES(99,2020,4,9,'Adalee',12800);

INSERT INTO sales VALUES(100,2020,4,10,'Bibiane',12900);

INSERT INTO sales VALUES(101,2020,4,11,'Bianca',13000);

INSERT INTO sales VALUES(102,2020,4,12,'Bonita',13100);

INSERT INTO sales VALUES(103,2020,4,13,'Bono',13200);

INSERT INTO sales VALUES(104,2020,4,14,'Belita',13300);

INSERT INTO sales VALUES(105,2020,4,15,'Charlotte',13400);

INSERT INTO sales VALUES(106,2020,4,16,'Cynthia',13500);

INSERT INTO sales VALUES(107,2020,4,17,'Dana',13600);

INSERT INTO sales VALUES(108,2020,4,18,'Dior',13700);

INSERT INTO sales VALUES(109,2020,4,19,'Doris',13800);

INSERT INTO sales VALUES(110,2020,4,20,'Edith',13900);

INSERT INTO sales VALUES(111,2020,4,21,'Emma',14000);

INSERT INTO sales VALUES(112,2020,4,22,'Erica',14100);

INSERT INTO sales VALUES(113,2020,4,23,'Frances',14200);

INSERT INTO sales VALUES(114,2020,4,24,'Grace',14300);

INSERT INTO sales VALUES(115,2020,4,25,'Grania',14400);

INSERT INTO sales VALUES(116,2020,4,26,'Hannah',14500);

INSERT INTO sales VALUES(117,2020,4,27,'Haisley',14600);

INSERT INTO sales VALUES(118,2020,4,28,'Iliana',14700);

INSERT INTO sales VALUES(119,2020,4,29,'Irene',14800);

INSERT INTO sales VALUES(120,2020,4,30,'Isabel',14900);

INSERT INTO sales VALUES(121,2020,5,1,'scott',15000);

INSERT INTO sales VALUES(122,2020,5,2,'tiger',15100);

INSERT INTO sales VALUES(123,2020,5,3,'rina',15200);

INSERT INTO sales VALUES(124,2020,5,4,'karim',15300);

INSERT INTO sales VALUES(125,2020,5,5,'orelian',15400);

INSERT INTO sales VALUES(126,2020,5,6,'Amelia',15500);

INSERT INTO sales VALUES(127,2020,5,7,'Avery',15600);

INSERT INTO sales VALUES(128,2020,5,8,'Adela',15700);

INSERT INTO sales VALUES(129,2020,5,9,'Adalee',15800);

INSERT INTO sales VALUES(130,2020,5,10,'Bibiane',15900);

INSERT INTO sales VALUES(131,2020,5,11,'Bianca',16000);

INSERT INTO sales VALUES(132,2020,5,12,'Bonita',16100);

INSERT INTO sales VALUES(133,2020,5,13,'Bono',16200);

INSERT INTO sales VALUES(134,2020,5,14,'Belita',16300);

INSERT INTO sales VALUES(135,2020,5,15,'Charlotte',16400);

INSERT INTO sales VALUES(136,2020,5,16,'Cynthia',16500);

INSERT INTO sales VALUES(137,2020,5,17,'Dana',16600);

INSERT INTO sales VALUES(138,2020,5,18,'Dior',16700);

INSERT INTO sales VALUES(139,2020,5,19,'Doris',16800);

INSERT INTO sales VALUES(140,2020,5,20,'Edith',16900);

INSERT INTO sales VALUES(141,2020,5,21,'Emma',17000);

INSERT INTO sales VALUES(142,2020,5,22,'Erica',17100);

INSERT INTO sales VALUES(143,2020,5,23,'Frances',17200);

INSERT INTO sales VALUES(144,2020,5,24,'Grace',17300);

INSERT INTO sales VALUES(145,2020,5,25,'Grania',17400);

INSERT INTO sales VALUES(146,2020,5,26,'Hannah',17500);

INSERT INTO sales VALUES(147,2020,5,27,'Haisley',17600);

INSERT INTO sales VALUES(148,2020,5,28,'Iliana',17700);

INSERT INTO sales VALUES(149,2020,5,29,'Irene',17800);

INSERT INTO sales VALUES(150,2020,5,30,'Isabel',17900);

INSERT INTO sales VALUES(151,2020,6,1,'scott',18000);

INSERT INTO sales VALUES(152,2020,6,2,'tiger',18100);

INSERT INTO sales VALUES(153,2020,6,3,'rina',18200);

INSERT INTO sales VALUES(154,2020,6,4,'karim',18300);

INSERT INTO sales VALUES(155,2020,6,5,'orelian',18400);

INSERT INTO sales VALUES(156,2020,6,6,'Amelia',18500);

INSERT INTO sales VALUES(157,2020,6,7,'Avery',18600);

INSERT INTO sales VALUES(158,2020,6,8,'Adela',18700);

INSERT INTO sales VALUES(159,2020,6,9,'Adalee',18800);

INSERT INTO sales VALUES(160,2020,6,10,'Bibiane',18900);

INSERT INTO sales VALUES(161,2020,6,11,'Bianca',19000);

INSERT INTO sales VALUES(162,2020,6,12,'Bonita',19100);

INSERT INTO sales VALUES(163,2020,6,13,'Bono',19200);

INSERT INTO sales VALUES(164,2020,6,14,'Belita',19300);

INSERT INTO sales VALUES(165,2020,6,15,'Charlotte',19400);

INSERT INTO sales VALUES(166,2020,6,16,'Cynthia',19500);

INSERT INTO sales VALUES(167,2020,6,17,'Dana',19600);

INSERT INTO sales VALUES(168,2020,6,18,'Dior',19700);

INSERT INTO sales VALUES(169,2020,6,19,'Doris',19800);

INSERT INTO sales VALUES(170,2020,6,20,'Edith',19900);

INSERT INTO sales VALUES(171,2020,6,21,'Emma',20000);

INSERT INTO sales VALUES(172,2020,6,22,'Erica',20100);

INSERT INTO sales VALUES(173,2020,6,23,'Frances',20200);

INSERT INTO sales VALUES(174,2020,6,24,'Grace',20300);

INSERT INTO sales VALUES(175,2020,6,25,'Grania',20400);

INSERT INTO sales VALUES(176,2020,6,26,'Hannah',20500);

INSERT INTO sales VALUES(177,2020,6,27,'Haisley',20600);

INSERT INTO sales VALUES(178,2020,6,28,'Iliana',20700);

INSERT INTO sales VALUES(179,2020,6,29,'Irene',20800);

INSERT INTO sales VALUES(180,2020,6,30,'Isabel',20900);

테스트 진행 상황

고객 추가 요청 사항

안녕하세요

저희가 일자 파티션 생성시 split를 하고 그로 인해 partition local index가 unusable이 되기 때문에 rebuild를 합니다

예를 들어 아래와 같이 실행합니다.

alter table table_name split partition PMAX at (high_value)

into (partition 6일후날짜partition_name, partition PMAX);

alter index index_name rebuild partition PMAX;

그리고 삭제는 일반적으로 하시는 것처럼

alter table table_name drop partition partition_name;

로 수행합니다

테스트 중간에 set transaction read only를 한번 넣어보고 진행해 보시는 것도 좋을 것 같습니다

감사합니다

테스트 케이스 1

1. DROP 대상 테이블이 변경되는 상태에서 commit이 안된 경우 (UPDATE, INSERT)

-> 무결성에 걸려서 삭제가 되지 않음. 삭제가 필요한 경우 옵션이 필요함.

SQL> alter table sales drop partition sales_q1;

alter table sales drop partition sales_q1

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

테스트 케이스 2

2. 변경이 되고 있지 않는 경우

-> 정상적으로 삭제됨.

SQL> alter table sales drop partition sales_q5;

Table altered.

SQL>

테스트 케이스 3

3. DROP 대상이 아닌 파티션 테이블이 변경

-> insert 등의 작업이 commit이 될 때까지 지연될 가능성이 있음

SQL> alter table SALES split partition sales_max at (2020,07,01) into (partition sales_q6, partition sales_max);

alter table SALES split partition sales_max at (2020,07,01) into (partition sales_q6, partition sales_max)

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

SQL>

-> insert 세션이 끝나면서 자동으로 commit이 되고 재시도될 경우 성공함.

SQL> /

Table altered.

파티션 테이블 추가 예시

ALTER TABLE sales
SPLIT PARTITION sales_q2 AT (2005, 01, 01)

INTO (PARTITION sales_q1 TABLESPACE ASSM_TBS1,PARTITION sales_q2 TABLESPACE ASSM_TBS2);

ALTER TABLE sales

ADD PARTITION sales_q5 VALUES LESS THAN (MAXVALUE, MAXVALUE, MAXVALUE )

TABLESPACE ASSM_TBS5;

ALTER TABLE shipments

ADD PARTITION p_2007_jan

VALUES LESS THAN (TO_DATE('01-FEB-2007','dd-MON-yyyy')) COMPRESS

( SUBPARTITION p07_jan_e VALUES LESS THAN (TO_DATE('15-FEB-2007','dd-MON-yyyy'))

, SUBPARTITION p07_jan_a VALUES LESS THAN (TO_DATE('01-MAR-2007','dd-MON-yyyy'))

, SUBPARTITION p07_jan_l VALUES LESS THAN (TO_DATE('01-APR-2007','dd-MON-yyyy'))

) ;

출처: <https://docs.oracle.com/cd/B28359_01/server.111/b32024/part_admin.htm#i1007318>

출처: <http://www.gurubee.net/lecture/1908>

저작자표시 비영리 변경금지

'Oracle Study' 카테고리의 다른 글

오라클 RAC 시간동기화 (0)	2021.04.28
RAC 관리용 명령어 (0)	2021.04.28
구구단 PL/SQL (0)	2021.01.27
오라클 설치 도중에 네트워크 관련 에러 (0)	2020.09.23
분할 된 인덱스 구성 테이블 (0)	2018.08.06

'Oracle Study' Related Articles

DBA가 되고 싶은 병아리

파티션 테이블 관련 테스트 본문

파티션 테이블 관련 테스트

'Oracle Study' 카테고리의 다른 글

티스토리툴바