In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
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.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.