每日分区计算错误

Question

Oracle ： 11.2.0.2我正在尝试使用脚本删除每月和每日分区。这适用于每月分区，但不适用于每日分区。以下是我在日志中看到的错误。计算时，月份中的某一天变为零。

2013-08-0|SYS_P328538|2|YES
DECLARE
*
ERROR at line 1:
ORA-01847: day of month must be between 1 and last day of month
ORA-06512: at line 43

下面是脚本。我认为高价值日期计算错误。

SQL> DECLARE
2      CURSOR tab_part_cur IS
3          select PARTITION_POSITION, PARTITION_NAME,HIGH_VALUE,INTERVAL from dba_tab_partitions where table_name = 'MO_USAGEDATA' 
and table_owner = 'WSMUSER17' 
order by  PARTITION_POSITION;
4      tab_part_rec tab_part_cur%ROWTYPE;
5      lHighValue LONG;
6      strPartitionLessThanDate VARCHAR2(100);
7      dtTestDate DATE;
8      DaysInPast NUMBER;
9      SQLstr varchar2(100);
10      strIntervalType varchar2(1000);
11      strRunType varchar2(20);
12  BEGIN
13      strRunType := 'DRY_RUN';
14      select INTERVAL into strIntervalType from dba_part_tables where table_name ='MO_USAGEDATA' and owner = 'WSMUSER17';
15      strIntervalType := REGEXP_SUBSTR(strIntervalType, '''[^'']+''');
16      DBMS_OUTPUT.PUT_LINE(strIntervalType);
17          CASE
18          WHEN strIntervalType = '''DAY''' THEN
19              DBMS_OUTPUT.PUT_LINE('Interval type = '||strIntervalType);
20  --            dtTestDate := CURRENT_DATE - 7 - 1;  Offset adjustment if necessary
21              dtTestDate := CURRENT_DATE - 7;
22              DBMS_OUTPUT.PUT_LINE('Test Date = '||dtTestDate);
23          WHEN strIntervalType = '''MONTH''' THEN
24              DBMS_OUTPUT.PUT_LINE('Interval type = '||strIntervalType);
25  --              dtTestDate := CURRENT_DATE - 90;
26                  dtTestDate := ADD_MONTHS(current_date,- 7);
27              DBMS_OUTPUT.PUT_LINE('TestDate = '||dtTestDate);
28          ELSE
29              DBMS_OUTPUT.PUT_LINE('Unexpected interval, exiting.');
30              GOTO EXIT;
31      END CASE;
32      OPEN tab_part_cur;
33      LOOP
34          FETCH tab_part_cur INTO tab_part_rec;
35          EXIT WHEN tab_part_cur%NOTFOUND;
36          DBMS_OUTPUT.PUT_LINE(tab_part_cur%ROWCOUNT);
37          lHighValue := tab_part_rec.high_value;
38          /* This next line seems redundant but is needed for conversion quirk from LONG to  VARCHAR2
39           */
40          strPartitionLessThanDate := lHighValue;
41          strPartitionLessThanDate := substr(strPartitionLessThanDate, 11, 10);
42 DBMS_OUTPUT.PUT_LINE(strPartitionLessThanDate ||'|'|| tab_part_rec.partition_name  ||'|'||  tab_part_rec.partition_position ||'|'|| tab_part_rec.interval);
43  DBMS_OUTPUT.PUT_LINE(TO_DATE(strPartitionLessThanDate, 'YYYY-MM-DD') ||'******'||dtTestDate);
44 IF TO_DATE(strPartitionLessThanDate, 'YYYY-MM-DD') < dtTestDate AND  tab_part_rec.partition_name <> 'PART_MINVALUE
' THEN
45  SQLstr := 'ALTER TABLE WSMUSER17.MO_USAGEDATA DROP PARTITION '||tab_part_rec.partition_name  ||' update Global indexes';
46              DBMS_OUTPUT.PUT_LINE('Targeted Partition !!!!!!!!');
47              IF strRunType = 'LIVE_RUN' THEN
48                  DBMS_OUTPUT.PUT_LINE('Dropping Partition !!!!!!!!');
49                execute immediate SQLstr;
50              END IF;
51          END IF;
52      END LOOP;
53      CLOSE tab_part_cur;
54      << EXIT >>
55     DBMS_OUTPUT.PUT_LINE('Partition purge complete');
56  END;
57  /
'DAY'
Interval type = 'DAY'
Test Date = 03-SEP-13
1
2012-06-1|PART_MINVALUE|1|NO
01-JUN-12******03-SEP-13
2
2013-08-0|SYS_P328538|2|YES
DECLARE
*
ERROR at line 1:
ORA-01847: day of month must be between 1 and last day of month
ORA-06512: at line 43

我正在尝试在每日分区表中保留 lat 7 分区并删除其余分区。但它并没有放弃它们。

Answer 1

好的，我创建了表，插入了一些数据并运行了一些查询，您的子字符串有问题：

SQL> CREATE TABLE "MO_USAGEDATA" (
  2  "REQUESTDTS" TIMESTAMP (9) NOT NULL ENABLE
  3  )
  4  partition by range ("REQUESTDTS") INTERVAL(NUMTODSINTERVAL(1,'DAY'))
  5  (partition PART_MINVALUE values less than(TIMESTAMP '2012-06-18 00:00:00'));
Table created
SQL> INSERT INTO MO_USAGEDATA 
  2     (SELECT SYSDATE + ROWNUM FROM dual CONNECT BY LEVEL <= 30);
30 rows inserted
SQL> SELECT high_value, INTERVAL
  2    FROM all_tab_partitions
  3   WHERE table_name = 'MO_USAGEDATA'
  4     AND table_owner = USER
  5   ORDER BY PARTITION_POSITION;
HIGH_VALUE                           INTERVAL
------------------------------------ ---------
[...]
TIMESTAMP' 2013-09-30 00:00:00'      YES
TIMESTAMP' 2013-10-01 00:00:00'      YES
TIMESTAMP' 2013-10-02 00:00:00'      YES
[...]
SQL> SELECT substr('TIMESTAMP'' 2013-10-02 00:00:00''', 11, 10) FROM dual;
SUBSTR('TIMESTAMP''2013-10-020
------------------------------
 2013-10-0

如您所见，您被一个角色所困扰。它适用于DATE列，但对于TIMESTAMP分区，您需要调整偏移量。