Solved

Dynamically allocating new partition

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

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Number Format 1 45
Oracle Subquery bad Join 11 46
Convert Oracle data into XML document 2 39
PAYER_ID has both atributes 4 19
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…
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 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.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

747 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

11 Experts available now in Live!

Get 1:1 Help Now