Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Dynamically allocating new partition

Posted on 2007-11-28
4
Medium Priority
?
6,394 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 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

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.  …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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 explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

885 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