Shulou(Shulou.com)05/31 Report--

This article mainly introduces the delete logic of the partition table in the database, which has a certain reference value. Interested friends can refer to it. I hope you will gain a lot after reading this article.

The main discussions are as follows:

1. Delete logic of interval partition table

two。 How to deal with ORA-14758 error

Create interval Partition

Create table t_interval

(

A DATE

B int

C int

)

PARTITION BY RANGE (a)

INTERVAL (numtodsinterval (3meme daylight))

(

PARTITION P1 VALUES LESS THAN (TO_DATE ('2018-1-2,' YYYY-MM-DD'))

);

Insert into t_interval values (TO_DATE ('2018-01-01 10 YYYY-MM-DD HH24:MI:SS'), 1Magne1)

Insert into t_interval values (TO_DATE ('2018-01-02 10 YYYY-MM-DD HH24:MI:SS'), 1Magne1)

Insert into t_interval values (TO_DATE ('2018-01-05 10 YYYY-MM-DD HH24:MI:SS'), 1 min1)

Insert into t_interval values (TO_DATE ('2018-01-08 10 YYYY-MM-DD HH24:MI:SS'), 1 min1)

Insert into t_interval values (TO_DATE ('2018-01-11 10 YYYY-MM-DD HH24:MI:SS'), 1Magne1)

Insert into t_interval values (TO_DATE ('2018-01-14 10 YYYY-MM-DD HH24:MI:SS'), 1Magne1)

Commit

09:45:19 SQL > select PARTITION_NAME,HIGH_VALUE,PARTITION_POSITION,INTERVAL from dba_tab_partitions where table_name='T_INTERVAL'

PARTITION_NAME HIGH_VALUE PARTITION_POSITION INTERVAL

P1 TO_DATE ('2018-01-02 00 TO_DATE,' SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 1 NO

SYS_P2876 TO_DATE ('2018-01-05 00 YES,' SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 2)

SYS_P2877 TO_DATE ('2018-01-08 00 NLS_CALENDAR=GREGORIA NLS_CALENDAR=GREGORIA 3 YES)

SYS_P2878 TO_DATE ('2018-01-11 00 NLS_CALENDAR=GREGORIA NLS_CALENDAR=GREGORIA 4 YES)

SYS_P2879 TO_DATE ('2018-01-14 00 NLS_CALENDAR=GREGORIA 00 YES, 5 YES)

SYS_P2880 TO_DATE ('2018-01-17 00 NLS_CALENDAR=GREGORIA 00 YES, 6 YES)

6 rows selected.

The interval column indicates whether the partition is a spaced partition. The partition specified when the table is created is not in the category of spaced partitions.

Change the partition interval of the table

Alter table t_interval set interval (NUMTODSINTERVAL (2)); 09:46:57 SQL > select PARTITION_NAME,HIGH_VALUE,PARTITION_POSITION,INTERVAL from dba_tab_partitions where table_name='T_INTERVAL'

PARTITION_NAME HIGH_VALUE PARTITION_POSITION INTERVAL

P1 TO_DATE ('2018-01-02 00 TO_DATE,' SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 1 NO

SYS_P2876 TO_DATE ('2018-01-05 00 NO,' SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 2)

SYS_P2877 TO_DATE ('2018-01-08 00 NLS_CALENDAR=GREGORIA NLS_CALENDAR=GREGORIA 3 NO)

SYS_P2878 TO_DATE ('2018-01-11 00 NLS_CALENDAR=GREGORIA NLS_CALENDAR=GREGORIA 4 NO)

SYS_P2879 TO_DATE ('2018-01-14 00 NLS_CALENDAR=GREGORIA 00 NO, 5 NO)

SYS_P2880 TO_DATE ('2018-01-17 00 NLS_CALENDAR=GREGORIA 00 NO, 6 NO)

6 rows selected.

Elapsed: 00:00:00.00

After the partition interval is changed, the interval column becomes NO.

Insert data to create a new partition

Insert into t_interval values (TO_DATE ('2018-01-18 10 YYYY-MM-DD HH24:MI:SS'), 1 min1)

Insert into t_interval values (TO_DATE ('2018-01-20 10 YYYY-MM-DD HH24:MI:SS'), 1Magne1)

Insert into t_interval values (TO_DATE ('2018-01-22 10 YYYY-MM-DD HH24:MI:SS'), 1Magne1)

Insert into t_interval values (TO_DATE ('2018-01-24 10 YYYY-MM-DD HH24:MI:SS'), 1Magne1)

Insert into t_interval values (TO_DATE ('2018-01-26 10 YYYY-MM-DD HH24:MI:SS'), 1Magne1)

Commit; 09:48:55 SQL > select PARTITION_NAME,HIGH_VALUE,PARTITION_POSITION,INTERVAL from dba_tab_partitions where table_name='T_INTERVAL'

PARTITION_NAME HIGH_VALUE PARTITION_POSITION INTERVAL

P1 TO_DATE ('2018-01-02 00 TO_DATE,' SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 1 NO

SYS_P2876 TO_DATE ('2018-01-05 00 NO,' SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 2)

SYS_P2877 TO_DATE ('2018-01-08 00 NLS_CALENDAR=GREGORIA NLS_CALENDAR=GREGORIA 3 NO)

SYS_P2878 TO_DATE ('2018-01-11 00 NLS_CALENDAR=GREGORIA NLS_CALENDAR=GREGORIA 4 NO)

SYS_P2879 TO_DATE ('2018-01-14 00 NLS_CALENDAR=GREGORIA 00 NO, 5 NO)

SYS_P2880 TO_DATE ('2018-01-17 00 NLS_CALENDAR=GREGORIA 00 NO, 6 NO)

SYS_P2881 TO_DATE ('2018-01-19 00 NLS_CALENDAR=GREGORIA NLS_CALENDAR=GREGORIA 7 YES)

SYS_P2882 TO_DATE ('2018-01-21 00 SYS_P2882 TO_DATE,' SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 8 YES

SYS_P2883 TO_DATE ('2018-01-23 00 NLS_CALENDAR=GREGORIA NLS_CALENDAR=GREGORIA 9 YES)

SYS_P2884 TO_DATE ('2018-01-25 00 NLS_CALENDAR=GREGORIA 00 YES, 10 YES)

SYS_P2885 TO_DATE ('2018-01-27 00 NLS_CALENDAR=GREGORIA 00 YES, 11 YES)

11 rows selected.

The newly generated partition belongs to the spaced partition.

Attempt to delete partition

09:49:26 SQL > alter table t_interval drop partition SYS_P2880

Alter table t_interval drop partition SYS_P2880

*

ERROR at line 1:

ORA-14758: Last partition in the range section cannot be dropped

After many attempts, it was found that the last partition of the interval partition, NO, could not be deleted.

If you have to delete the last partition that is NO, you need to change the interval column of the partition table dba_tab_partitions to NO.

The method is simple: specify the interval property as the current value, and execute it again:

Alter table t_interval set interval (NUMTODSINTERVAL (1)

Then delete

09:55:32 SQL > alter table t_interval drop partition SYS_P2880

Table altered.

Elapsed: 00:00:00.01

Then insert the data to create a new interval partition

Insert into t_interval values (TO_DATE ('2018-02-01 10 YYYY-MM-DD HH24:MI:SS'), 1)

Insert into t_interval values (TO_DATE ('2018-02-02 10 YYYY-MM-DD HH24:MI:SS'), 1 min1)

Insert into t_interval values (TO_DATE ('2018-02-03 10 YYYY-MM-DD HH24:MI:SS'), 1Magne1)

Commit

Grab the ddl of the table

Set pagesize 0

Set long 90000

Select dbms_metadata.get_ddl ('TABLE','T_INTERVAL','MING') from dual; 10:35:19 SQL > select dbms_metadata.get_ddl (' TABLE','T_INTERVAL','MING') from dual

CREATE TABLE "MING". "T_INTERVAL"

("A" DATE

"B" NUMBER (*, 0)

"C" NUMBER (*, 0)

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

STORAGE (

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CAC

HE DEFAULT)

TABLESPACE "TBS_MING"

PARTITION BY RANGE ("A") INTERVAL (NUMTODSINTERVAL (1)

(PARTITION "SYS_P2877" VALUES LESS THAN (TO_DATE ('20

18-01-08 00 SYYYY-MM-DD HH24:MI:SS', NLS_C

ALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

STORAGE (INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS

2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "TBS_MING"

. . . Omit...

PARTITION "SYS_P2890" VALUES LESS THAN (TO_DATE ('2018-01-300:

0014 0000, 'SYYYY-MM-DD HH24:MI:SS',' NLS_CALENDAR=GREG

ORIAN')) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

STORAGE (INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE

DEFAULT)

TABLESPACE "TBS_MING"

PARTITION "SYS_P2891" VALUES LESS THAN (TO_DATE ('2018-01-31 00:00

: 0000, 'SYYYY-MM-DD HH24:MI:SS',' NLS_CALENDAR=GREGOR

IAN')) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

STORAGE (INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DE

FAULT)

TABLESPACE "TBS_MING")

You will find that the interval partition definition does not appear in the ddl definition statement of the table. When the table partition property interval is changed to NO, the partition is converted to a range partition before it is added to the table's ddl statement.

1. When the last partition whose interval is NO cannot be deleted and becomes NO, any partition can be deleted.

The 2.alter table t_interval set interval command changes interval properties to NO.

3. The interval partition definition does not appear in the ddl statement of the table, and the interval is not added to the ddl statement of the table until it becomes NO.

Thank you for reading this article carefully. I hope the article "Delete Logic of spaced Partition tables in Database" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.