Solved

Dynamically allocating new partition

Posted on 2007-11-28
4
6,253 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

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

821 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