Shulou(Shulou.com)06/01 Report--

This article will explain in detail how to carry out partition table split operation and maxvalue processing, the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

CREATE TABLE DFMS.WIP_D_WO_DETAIL

(

WORK_ORDER VARCHAR2 (25 CHAR) NOT NULL

PART_NO VARCHAR2 (35 BYTE) NOT NULL

PART_REV VARCHAR2 (10 BYTE) DEFAULT'Nama A'NOT NULL

ALT_FLAG VARCHAR2 (1 BYTE) DEFAULT'0' NOT NULL

SUBSTITUTE_PN VARCHAR2 (35 BYTE)

UNIT_QTY NUMBER DEFAULT 0 NOT NULL

SOURCE_DEPT VARCHAR2 (5 BYTE) DEFAULT 'SFC' NOT NULL

CREATOR VARCHAR2 (20 BYTE) NOT NULL

CREATE_DATE DATE DEFAULT SYSDATE

UPDATER VARCHAR2 (20 BYTE)

UPDATE_DATE DATE

SCH_PART_NO VARCHAR2 (35 BYTE)

MAIN_WHID VARCHAR2 (10 BYTE)

REPLACE_GROUP VARCHAR2 (50 BYTE) NOT NULL

MOD_NO VARCHAR2 (20 BYTE)

PHANTOM_FLAG VARCHAR2 (10 BYTE)

)

TABLESPACE PART_D_TS1

PCTUSED 0

PCTFREE 10

INITRANS 1

MAXTRANS 255

LOGGING

PARTITION BY RANGE (CREATE_DATE)

(

PARTITION P200907 VALUES LESS THAN (TO_DATE ('2009-07-01 00 SYYYY-MM-DD HH24:MI:SS')

'NLS_CALENDAR=GREGORIAN'))

LOGGING

NOCOMPRESS

TABLESPACE PART_D_TS1

PCTFREE 10

INITRANS 1

MAXTRANS 255

STORAGE (

INITIAL 50M

NEXT 50M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

),

PARTITION P201001 VALUES LESS THAN (TO_DATE ('2010-01-0100: 00 SYYYY-MM-DD HH24:MI:SS')

'NLS_CALENDAR=GREGORIAN'))

LOGGING

NOCOMPRESS

TABLESPACE PART_D_TS1

PCTFREE 10

INITRANS 1

MAXTRANS 255

STORAGE (

INITIAL 50M

NEXT 50M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

),

PARTITION P201007 VALUES LESS THAN (TO_DATE ('2010-07-0100: 00 TO_DATE,' SYYYY-MM-DD HH24:MI:SS'

'NLS_CALENDAR=GREGORIAN'))

LOGGING

NOCOMPRESS

TABLESPACE PART_D_TS1

PCTFREE 10

INITRANS 1

MAXTRANS 255

STORAGE (

INITIAL 50M

NEXT 50M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

),

PARTITION P201101 VALUES LESS THAN (TO_DATE ('2011-01-01-01-0015-0015-0015-0015-0012) SYYYY-MM-DD HH24:MI:SS'

'NLS_CALENDAR=GREGORIAN'))

LOGGING

NOCOMPRESS

TABLESPACE PART_D_TS1

PCTFREE 10

INITRANS 1

MAXTRANS 255

STORAGE (

INITIAL 50M

NEXT 50M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

),

PARTITION P201107 VALUES LESS THAN (TO_DATE ('2011-07-01 0015-0015-0015-0015-0012) SYYYY-MM-DD HH24:MI:SS'

'NLS_CALENDAR=GREGORIAN'))

LOGGING

NOCOMPRESS

TABLESPACE PART_D_TS1

PCTFREE 10

INITRANS 1

MAXTRANS 255

STORAGE (

INITIAL 50M

NEXT 50M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

),

PARTITION P201201 VALUES LESS THAN (TO_DATE ('2012-01-01-0015-0015-0015-0015-0015),' SYYYY-MM-DD HH24:MI:SS'

'NLS_CALENDAR=GREGORIAN'))

LOGGING

NOCOMPRESS

TABLESPACE PART_D_TS1

PCTFREE 10

INITRANS 1

MAXTRANS 255

STORAGE (

INITIAL 50M

NEXT 50M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

),

PARTITION P201207 VALUES LESS THAN (TO_DATE ('2012-07-01 0015-0015-0015-0015-0015),' SYYYY-MM-DD HH24:MI:SS'

'NLS_CALENDAR=GREGORIAN'))

LOGGING

NOCOMPRESS

TABLESPACE PART_D_TS1

PCTFREE 10

INITRANS 1

MAXTRANS 255

STORAGE (

INITIAL 50M

NEXT 50M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

),

PARTITION P201301 VALUES LESS THAN (TO_DATE ('2013-01-01-01 00 SYYYY-MM-DD HH24:MI:SS')

'NLS_CALENDAR=GREGORIAN'))

LOGGING

NOCOMPRESS

TABLESPACE PART_D_TS1

PCTFREE 10

INITRANS 1

MAXTRANS 255

STORAGE (

INITIAL 50M

NEXT 50M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

),

PARTITION PMAX VALUES LESS THAN (MAXVALUE)

LOGGING

NOCOMPRESS

TABLESPACE PART_D_TS1

PCTFREE 10

INITRANS 1

MAXTRANS 255

STORAGE (

INITIAL 50M

NEXT 50M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

)

)

NOCOMPRESS

NOCACHE

NOPARALLEL

MONITORING

ENABLE ROW MOVEMENT

Assuming there is no maxvalue section, add the partition:

ALTER TABLE DFMS.WIP_D_WO_DETAIL

ADD PARTITION P201307 VALUES LESS THAN (TO_DATE ('2013-07-01 00 SYYYY-MM-DD HH24:MI:SS')

NLS_CALENDAR=GREGORIAN'))

LOGGING

NOCOMPRESS

TABLESPACE PART_D_TS1

PCTFREE 10

INITRANS 1

MAXTRANS 255

STORAGE (

INITIAL 50M

NEXT 50M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

)

There is a maxvalue section here, so split is required to join the partition:

Note:

When split partition, it splits into two partition according to the original partition of split point.

If one of the partition is empty, neither move's original partition nor rebuild is required

Index, which we call fast split.

However, if tablespace is specified when split, even if one of the partition is empty, and the

Unlike parent partition's tablespace, tablespace will move the entire partition to

In the new tablespace. Because of this tablespace parameter, the original fast split is not successful, and

Move segment and rebuild index are required.

ALTER TABLE DFMS.WIP_D_WO_DETAIL

SPLIT PARTITION PMAX AT (TO_DATE ('2013-07-01 00 SYYYY-MM-DD HH24:MI:SS', NLS_CALENDAR=GREGORIAN'))

INTO (PARTITION P201307 TABLESPACE PART_D_TS1, PARTITION PMAX TABLESPACE PART_D_TS1)

If you need to cancel the maxvalue section, then after several parts of split, check to see if the last part has a value, if not, directly

Drop dropped the last part where split went out. Then you can add partition directly.

ALTER TABLE DFMS.WIP_D_WO_DETAIL

SPLIT PARTITION PMAX AT (TO_DATE ('2013-07-01 00 SYYYY-MM-DD HH24:MI:SS', NLS_CALENDAR=GREGORIAN'))

INTO (PARTITION P201307 TABLESPACE PART_D_TS1, PARTITION P2014 TABLESPACE PART_D_TS1)

ALTER TABLE DFMS.WIP_D_WO_DETAIL

SPLIT PARTITION P2014 AT (TO_DATE ('2014-01-01 00 TO_DATE,' SYYYY-MM-DD HH24:MI:SS', NLS_CALENDAR=GREGORIAN'))

INTO (PARTITION P201401 TABLESPACE PART_D_TS1, PARTITION P201407 TABLESPACE PART_D_TS1)

ALTER TABLE DFMS.WIP_D_WO_DETAIL

SPLIT PARTITION P201407 AT (TO_DATE ('2014-07-01 00 TO_DATE,' SYYYY-MM-DD HH24:MI:SS', NLS_CALENDAR=GREGORIAN'))

INTO (PARTITION P201407 TABLESPACE PART_D_TS1, PARTITION P201501 TABLESPACE PART_D_TS1)

ALTER TABLE DFMS.WIP_D_WO_DETAIL DROP PARTITION P201501

Then you can add the following partiton as if you didn't set maxvalue.

ALTER TABLE DFMS.WIP_D_WO_DETAIL

ADD PARTITION P201501 VALUES LESS THAN (TO_DATE ('2015-01-01-01 00 SYYYY-MM-DD HH24:MI:SS')

NLS_CALENDAR=GREGORIAN'))

ALTER TABLE DFMS.WIP_D_WO_DETAIL

ADD PARTITION P201507 VALUES LESS THAN (TO_DATE ('2015-07-01 00 SYYYY-MM-DD HH24:MI:SS')

NLS_CALENDAR=GREGORIAN'))

.

If you change the tablespace during split, you need rebuild index.

The following statement is used for rebuild local index partitions or subpartitions

(generally speaking, a subpartition refers to a subpartition in a combined partition such as range-hash):

ALTER INDEX... REBUILD PARTITION/SUBPARTITION

ALTER TABLE... MODIFY PARTITION/SUBPARTITION... REBUILD UNUSABLE LOCAL INDEXES

This statement finds all the index of unusable, and then rebuild them.

On how to carry out partition table split operation and maxvalue processing to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.