Forum Stats

  • 3,781,142 Users
  • 2,254,484 Discussions
  • 7,879,589 Comments

Discussions

spliting partition by the month

nodex
nodex Member Posts: 35
edited Nov 13, 2008 10:10AM in SQL & PL/SQL
Hi everyone,

I'm facing a small trouble. I want to split a table parition from OVERFLOW in a PL/SQL procedure.
My inputs are: table_name and period.
I wrote a procedure as below but it doesn't work.


CREATE OR REPLACE PROCEDURE Add_Partition (p_tablename IN VARCHAR2,
p_part_range IN VARCHAR2
)
AS
mask VARCHAR2 (6);
str VARCHAR2 (500);

BEGIN


mask := 'yyyymm';


str := 'ALTER TABLE' || table_name || ' split PARTITION '
|| partition_name || ' AT ('|| to_date(p_part_range, mask) || ')
INTO
(PARTITION ' || partition_name ||'_' || p_part_range || ', PARTITION '|| partition_name || '_OVERFLOW)';


BEGIN EXECUTE IMMEDIATE str;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (str || ' - FAILURE!');
DBMS_OUTPUT.put_line (SQLERRM);
END;

END Add_Partition;




Please take on that partition_name is a real name of partition.
The problem is with date. It converts date with function to_date(p_part_range, mask) from input (ex. 200802) into (01-FEB-08).
So the query really looks llike:

ALTER table_name split PARTITION
table_name AT (01-FEB-08)
INTO
(PARTITION partition_name_200802, PARTITION partition_name_OVERFLOW)


Somebody has an idea how to solve that problem?
Or how to split a partition by a month in a diffrent way? It has to be PL/SQL procedure.

Thanks a lot!
Tagged:

Answers

  • 561825
    561825 Member Posts: 646
    >
    ALTER table_name split PARTITION
    table_name AT (01-FEB-08)
    INTO
    (PARTITION partition_name_200802, PARTITION partition_name_OVERFLOW)
    >
    The above query should like
    ALTER table_name split PARTITION 
    table_name AT (to_date('01-FEB-2008','dd-mon-YYYY'))
    INTO 
    (PARTITION partition_name_200802, PARTITION partition_name_OVERFLOW) 
    So change your dynamic sql accordingly.

    Hope this helps.

    Regards

    Raj

    P.S : From next time if you could copy and paste the actual error message along with your full oracle version from v$version it will be really helpful
  • BluShadow
    BluShadow Member, Moderator Posts: 41,611 Red Diamond
    edited Nov 13, 2008 9:14AM
    || partition_name || ' AT (to_date('''|| to_date(p_part_range, mask) || ''','''||mask||''')
    Edit. however your date is being implicitly converted to char by the concatentation into the string.

    You should perhaps do
    || partition_name || ' AT (to_date('''||p_part_range|| ''','''||mask||''')
    Edited by: BluShadow on Nov 13, 2008 2:11 PM

    Edited by: BluShadow on Nov 13, 2008 2:13 PM
    think I had one quote too many. :)
    BluShadow
  • nodex
    nodex Member Posts: 35
    Probably BluShadow it will help me. I'm debugging it now - it may take a few minutes. It sholud be one more right parenthesis on the end but it's just a detail.
    Thank You both for help.
  • Boneist
    Boneist Member Posts: 4,983 Gold Trophy
    Do you have any indexes on your table? If so, are they all local, or do you have any global indexes?

    If the answer to the first question is yes, then you should consider adding UPDATE INDEXES / UPDATE GLOBAL INDEXES on the end of your split partition statement.
    Boneist
  • nodex
    nodex Member Posts: 35
    I've one index - good hint Boneist, thank You.
  • nodex
    nodex Member Posts: 35
    Yes, I just want to commit - it helps me. It works fine :)
    Thanks! :)
This discussion has been closed.