Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6486
  • Last Modified:

Dynamically allocating new partition

I am trying to write a oracle script to handle partition maintenance.  The tables are a rolling 12  months of data.  The script will drop off the oldest partition (partition 1) and dynamically allocate new paritions but when I try pass in the date, I get an error message:

ORA-14120: incompletely specified partition bound for a DATE column
ORA-06512: at "SYS.DBMS_SYS_SQL", line 826
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at line 45
How do I reformat the date so that it will work?  Tried many variations, i.e change v_future_date to date field, etc.
Thanks



declare cursor c is
  select  table_name, partition_name, initial_extent, next_extent, max_extent 
  from user_tab_partitions where partition_position =
    (select min(partition_position) from user_tab_partitions where partition_position = '1');
  r  c%rowtype;
  v_future_partition_date    varchar2(05);
  v_future_partition_name    varchar2(50);
  v_future_date                    varchar2(9);
  v_cursor                            integer; 
  v_row_nums                     integer; 
  v_create_string                 varchar2(500);
  v_drop_string                    varchar2(200);            
begin
  for r in c
    loop 
       v_cursor :=DBMS_SQL.open_cursor;
      v_drop_string := 'alter table '||r.table_name|| ' drop partition '||r.partition_name;
       dbms_sql.parse (v_cursor, v_drop_string, 0);
       v_row_nums := dbms_sql.execute(v_cursor);
       dbms_sql.close_cursor(v_cursor);  
    
    select substr(r.partition_name,1,length(r.partition_name) -5)|| to_char(add_months(sysdate,17),'MONYY')
            into v_future_partition_name
            from user_tab_partitions 
            where partition_position = 1
            and table_name = r.table_name;
             dbms_output.put_line('New partition '||v_future_partition_name); 
 
           select to_date((last_day(add_months(sysdate,11)) + 1),'YY-MM-DD') 
            into v_future_date
            from dual;
             dbms_output.put_line('New date '||v_future_date); 
             
             
        v_create_string :=
            'alter table '|| r.table_name || ' add PARTITION '|| v_future_partition_name
            || ' VALUES LESS THAN (TO_DATE('''|| v_future_date
            ||''',''YY-MON-DD'')) '
            || 'TABLESPACE ABCD PCTFREE 1 PCTUSED 99 INITRANS 1 MAXTRANS 2 STORAGE (INITIAL 1048576 NEXT 1064960 PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 2147483645 )';
          v_cursor :=DBMS_SQL.open_cursor;
          DBMS_SQL.parse (v_cursor, v_create_string, 0 );
          v_row_nums := dbms_sql.execute (v_cursor);
          dbms_sql.close_cursor (v_cursor);  
            commit;
end loop
end;

Open in new window

0
slim7
Asked:
slim7
  • 2
  • 2
1 Solution
 
johnsoneSenior Oracle DBACommented:
This should work.  I believe it has something to do with your date formats.  You are using the default date format in the alter table to add the parition.  I just added the to_char on the v_future_date variable to get a consistent date format.
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
 declare cursor c is
  select  table_name, partition_name, initial_extent, next_extent, max_extent 
  from user_tab_partitions where partition_position =
    (select min(partition_position) from user_tab_partitions where partition_position = '1');
  r  c%rowtype;
  v_future_partition_date    varchar2(05);
  v_future_partition_name    varchar2(50);
  v_future_date                    varchar2(9);
  v_cursor                            integer; 
  v_row_nums                     integer; 
  v_create_string                 varchar2(500);
  v_drop_string                    varchar2(200);            
begin
  for r in c
    loop 
       v_cursor :=DBMS_SQL.open_cursor;
      v_drop_string := 'alter table '||r.table_name|| ' drop partition '||r.partition_name;
       dbms_sql.parse (v_cursor, v_drop_string, 0);
       v_row_nums := dbms_sql.execute(v_cursor);
       dbms_sql.close_cursor(v_cursor);  
    
    select substr(r.partition_name,1,length(r.partition_name) -5)|| to_char(add_months(sysdate,17),'MONYY')
            into v_future_partition_name
            from user_tab_partitions 
            where partition_position = 1
            and table_name = r.table_name;
             dbms_output.put_line('New partition '||v_future_partition_name); 
 
           select to_date((last_day(add_months(sysdate,11)) + 1),'YY-MM-DD') 
            into v_future_date
            from dual;
             dbms_output.put_line('New date '||v_future_date); 
             
             
        v_create_string :=
            'alter table '|| r.table_name || ' add PARTITION '|| v_future_partition_name
            || ' VALUES LESS THAN (TO_DATE('''|| to_char(v_future_date, 'yy-mon-dd')
            ||''',''YY-MON-DD'')) '
            || 'TABLESPACE ABCD PCTFREE 1 PCTUSED 99 INITRANS 1 MAXTRANS 2 STORAGE (INITIAL 1048576 NEXT 1064960 PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 2147483645 )';
          v_cursor :=DBMS_SQL.open_cursor;
          DBMS_SQL.parse (v_cursor, v_create_string, 0 );
          v_row_nums := dbms_sql.execute (v_cursor);
          dbms_sql.close_cursor (v_cursor);  
            commit;
end loop
end;
 

Open in new window

0
 
slim7Author Commented:
it did not like the to_char in the string.  
ORA-06550: line 37, column 50:
PLS-00307: too many declarations of 'TO_CHAR' match this call
ORA-06550: line 35, column 9:
PL/SQL: Statement ignored
0
 
slim7Author Commented:
figured it out by additing to_char to the select stmt to get consistent format.

       select to_char((last_day(add_months(sysdate,17)) + 1),'YYYY-MM-DD')
            into v_future_date
            from dual;
             dbms_output.put_line('New date '||v_future_date);
             
             
        v_create_string :=
            'alter table '|| r.table_name || ' add PARTITION '|| v_future_partition_name
            || ' VALUES LESS THAN (TO_DATE('''|| v_future_date
            ||''',''YYYY-MM-DD'',''NLS_CALENDAR=GREGORIAN'')) '
            || 'TABLESPACE CRDDATA PCTFREE 1 PCTUSED 99 INITRANS 1 MAXTRANS 2 STORAGE (INITIAL 1048576 NEXT 1064960 PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 2147483645 )';
 
0
 
johnsoneSenior Oracle DBACommented:
Yes, that is it.  Sorry, I didn't look at the declaration of v_future_date, I thought it was a date.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now