Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

New feature of oracle 11g Partition Table-pits in interval Partition

2025-01-28 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

New feature of oracle 11g Partition Table-pits in interval Partition

The new interval partition feature in the range partition table of oracle 11g, which does not need to be defined. MAXVALUE,Oracle dynamically allocates new partitions to accommodate data beyond the range according to the step size defined by the partition.

The problem that the original range partition needs to be manually written job to pre-allocate the partition is solved, but the partition name is named automatically.

Therefore, the partition query criteria for interval partition interval (number) or interval (NUMTODSINTERVAL (1 minutes day') numbers or date automatic partition can be:

Select * from test_part partition for (number/date); can you also use this method when automatically cleaning up partitions? Here comes the pit, use this method to write a stored procedure to delete the old partition that was 30 days ago:

Create or replace procedure pd01.deltestdata_new (v_keep_days NUMBER DEFAULT 30) Authid CURRENT_USER

Is

V_date date

V_part_number number

Begin

V_date: = sysdate-v_keep_days

Select count (partition_name) into v_part_number from all_tab_partitions where table_owner='PD01' and table_name='PD_TESTDATA'

If v_part_number > = v_keep_days then

Execute immediate 'ALTER TABLE PD01.PD_TESTDATA DROP PARTITION FOR (' | | v_date | |')'

Select count (partition_name) into v_part_number from all_tab_partitions where table_owner='PD01' and table_name='PD_TESTDATA'

Insert into pdms01.del_testdata_log (exec_time,dropped_partition_date,remain_partition_number) values (sysdate,to_char (v_date), v_part_number)

Commit

Else

Insert into pdms01.del_testdata_log (exec_time,dropped_partition_date,remain_partition_number) values (sysdate,'none',v_part_number)

Commit

End if

End

/

Exec pdms01.deltestdata_new;-I can't believe it was wrong.

ORA-14763: cannot resolve FOR VALUES clause to partition number

ORA-06512: in "PDMS01.TEST11", line 7

ORA-06512: in line 1

Reason:

The partition for syntax in the interval partition does not support the use of bound variables. What a hoax.

Automatic cleanup of partitions can only be achieved by getting the partition name in other ways.

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.

Views: 299

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report