Solved

Dynamically allocating new partition

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Parametric query in oracle 6 49
PL/SQL - Leading zeros 7 57
Clone Oracle 12c Database 5 42
ORA-00923: FROM keyword not found where expected 3 23
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…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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 at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

919 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now