Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Dynamically allocating new partition

Posted on 2007-11-28
4
Medium Priority
?
6,363 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 35

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 35

Accepted Solution

by:
johnsone earned 750 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

660 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