Solved

Dynamically allocating new partition

Posted on 2007-11-28
4
6,242 Views
Last Modified: 2013-12-19
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
Comment
Question by:slim7
  • 2
  • 2
4 Comments
 
LVL 34

Expert Comment

by:johnsone
ID: 20368119
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
 

Author Comment

by:slim7
ID: 20368337
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
 

Author Comment

by:slim7
ID: 20368454
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
 
LVL 34

Accepted Solution

by:
johnsone earned 250 total points
ID: 20368615
Yes, that is it.  Sorry, I didn't look at the declaration of v_future_date, I thought it was a date.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question