This discussion is archived
9 Replies Latest reply: Jul 10, 2012 11:32 PM by 915754 RSS

Please help: Table partition

915754 Newbie
Currently Being Moderated
I want to add table partition in a existing partitioned table. But the following code is not working.

Table:

CREATE TABLE CPPROD.TEST_PART
(
A VARCHAR2(30 BYTE),
B NUMBER,
C VARCHAR2(20 BYTE),
D DATE,
CREATED TIMESTAMP(6),
E VARCHAR2(40 BYTE),
P08101 VARCHAR2(30 BYTE),
P08112 VARCHAR2(30 BYTE)
)
PARTITION BY RANGE (CREATED)
(
PARTITION P0323 VALUES LESS THAN (TIMESTAMP' 2012-03-24 23:59:59')
),
PARTITION P0305 VALUES LESS THAN (TIMESTAMP' 2012-03-25 23:59:59')
);

Procedure Code:

create or replace procedure test_home
is

v_table_name Varchar2(31):='TEST_PART'; -- Input table name
v_column_name varchar2(31); -- Input column name
v_data_type date; -- data type

v_exist number;
i number;
month_date number;
c number;
var1 varchar2(30);
var2 varchar2(100);

begin

select count(*) into v_exist from user_tab_cols where table_name=v_table_name ;
dbms_output.put_line(v_exist);

if v_exist > 0 then

select to_number(to_char(last_day(add_months(sysdate,1)),'DD')) into month_date from dual;

FOR i IN 1..2-- (select to_number(to_char(last_day(sysdate),'DD')) from dual)
LOOP

c:= i-1;
select to_char(add_months(sysdate,1)+c, 'MMDD') into var1 from dual;
v_column_name := 'P'||var1 ;
select to_char(add_months(sysdate,1)+c, 'YYYY-MM-DD') into var2 from dual;

dbms_output.put_line(var2);
var2 := var2||' 23:59:59';
dbms_output.put_line(var2);
v_data_type := to_date(var2,'YYYY-MM-DD HH24:MI:SS');
dbms_output.put_line(v_data_type);

execute immediate 'Alter table '||v_table_name||' add PARTITION '||v_column_name ||'VALUES LESS THAN('||v_data_type ||')';

--execute immediate 'Alter table '||v_table_name||' add ('||v_column_name||i ||' '||v_data_type||')';

end loop;

end if;
end;
/
sho err

Please help.
  • 1. Re: Please help: Table partition
    Paul Horth Expert
    Currently Being Moderated
    Look closely at what v_data_type looks like when you dbms_output it.

    Does it look like TIMESTAMP' 2012-03-25 23:59:59'? which is what the add partition is expecting.

    BTW, don't use 23:59:59 as your boundary condition. It is a VALUES LESS THAN so if you
    want everything on 23 March and before to be in a partition use:

    values less than (timestamp '2012-03-24 00:00:00')

    for example. Otherwise you get times of 23:59:59.001 etc. going in what is probably the wrong partition.
  • 2. Re: Please help: Table partition
    915754 Newbie
    Currently Being Moderated
    I as you can see from the procedure code.... I printed the values and the output is like

    8
    2012-08-10
    2012-08-10 23:59:59
    10-AUG-12
  • 3. Re: Please help: Table partition
    915754 Newbie
    Currently Being Moderated
    Can anyone help plz?

    Edited by: 912751 on Jul 10, 2012 3:53 AM
  • 4. Re: Please help: Table partition
    Paul Horth Expert
    Currently Being Moderated
    912751 wrote:
    I as you can see from the procedure code.... I printed the values and the output is like

    8
    2012-08-10
    2012-08-10 23:59:59
    10-AUG-12
    And?

    Well what should it be?

    Have a look again at your original create table: what is the format of
    VALUES LESS THAN (TIMESTAMP' 2012-03-24 23:59:59')

    Then look at your execute immediate:
    execute immediate 'Alter table '||v_table_name||' add PARTITION '||v_column_name ||'VALUES LESS THAN('||v_data_type ||')';

    Then think what will go between the brackets in:
    VALUES LESS THAN('||v_data_type ||')
  • 5. Re: Please help: Table partition
    915754 Newbie
    Currently Being Moderated
    I have tried so many things... but I am not able to get the required format. Each time its comes like 10-AUG-12
    If I am using it as timestamp then its giving ORA-14020

    Could you please help me?
  • 6. Re: Please help: Table partition
    Paul Horth Expert
    Currently Being Moderated
    912751 wrote:
    I have tried so many things... but I am not able to get the required format. Each time its comes like 10-AUG-12
    If I am using it as timestamp then its giving ORA-14020

    Could you please help me?
    I am trying to get you to think for yourself: you won't get very far if people just spoon-feed you.

    One more time:

    you need something that looks like

    VALUES LESS THAN (TIMESTAMP' 2012-03-24 23:59:59') *

    in your execute immediate
    but v_data_type has only got 2012-08-10 23:59:59 in it, so
    'Alter table '||v_table_name||' add PARTITION '||v_column_name ||'VALUES LESS THAN('||v_data_type ||')'
    gives you
    Alter table TEST_PART add PARTITION P0810 VALUES LESS THAN (2012-03-24 23:59:59)

    Compare that values clause with what it should be (*): it should be obvious it is not the required syntax.

    Now, you put some work in and figure out how to alter your execute immediate.

    Two other points, don't forget what I said earlier about not using 23:59:59 as an endpoint. You'll put 23:59:59.001
    rows in the wrong partition.

    Your variable names are confusing: v_column_name should surely be v_partition_name for example. This helps
    when someone is trying to understand your code later.
  • 7. Re: Please help: Table partition
    915754 Newbie
    Currently Being Moderated
    Thanks for your reply.

    I have used :

    execute immediate 'Alter table '||v_table_name||' add PARTITION '||v_column_name ||q'['VALUES LESS THAN(TIMESTAMP ' ]'|| var2||q'[')]';

    But its also giveing ORA-14020.

    When I am using the following code independently, its working fine.

    Alter table TEST_PART add PARTITION P0924 VALUES LESS THAN (to_date('2012-09-24 23:59:59','YYYY-MM-DD HH24:MI:SS'))

    Please help
  • 8. Re: Please help: Table partition
    sb92075 Guru
    Currently Being Moderated
    The standard advice when (ab)using EXECUTE IMMEDIATE is to compose the SQL statement in a single VARCHAR2 variable
    Then print the variable before passing it to EXECUTE IMMEDIATE.
    COPY the statement & PASTE into sqlplus to validate its correctness.

    bcm@bcm-laptop:~$ oerr ora 14020
    14020, 00000, "this physical attribute may not be specified for a table partition"
    // *Cause:   unexpected option was encountered while parsing physical 
    //           attributes of a table partition; valid options for Range or 
    //           Composite Range partitions are INITRANS, MAXTRANS, TABLESPACE,  
    //           STORAGE, PCTFREE, and PCTUSED; only TABLESPACE may be specified 
    //           for Hash partitions
    // *Action:  remove invalid option(s) from the list of physical attributes 
    //           of a table partition
    // *Comment: this error could have resulted from omission of a
    //           terminating (right) parenthesis following the list of 
    //           partition descriptions
  • 9. Re: Please help: Table partition
    915754 Newbie
    Currently Being Moderated
    Thanks :)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points