This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Jan 25, 2013 1:24 PM by rp0428 RSS

dbms_redefinition - change table from non partitioned to partitioned

user650888 Newbie
Currently Being Moderated
CREATE TABLE test_t1
    (x                              NUMBER ,
    y                              NUMBER NOT NULL,
    pv                             NUMBER);
I want to partition test_t1 based on the column pv, I wrote a stored procedure that will populate pv column with correct values accordingly. Once I run the procedure, pv column will be populated,

Here is what I think should be approach

1. Rename test_t1 to test_t1_bkp

2. Write the logic (procedure that updates pv value in test_t1_bkp)

3. Create new table test_t1 with partitions (so this table is empty)

CREATE TABLE test_t1
( x number, y number, pv number )
PARTITION BY RANGE (pv)
( PARTITION p0 VALUES LESS THAN (1) ,
PARTITION p1 VALUES LESS THAN (2),
PARTITION p3 VALUES LESS THAN (MAXVALUE)
)
PARALLEL ENABLE ROW MOVEMENT ;

3. Now instead of inserting the data from test_t1_bkp to test_t1 and also taking care of
indexes, constraints, can I use dbms_redefinition and apply it on test_t1_bkp itself
so I do not have to

1. Drop the constraints, indexes on test_t1_bkp
2. Insert into test_t1 select * from test_t1_bkp
3.Create indexes, constraints on test_t1

I logged into my development environment and tried to execute dbms_redefinition procedures, but I do not
have rights, What privileges are actually needed ? Looks like dbms_redefinition does not belong to sys

SQL> desc dbms_redefinition;
ERROR:
ORA-04043: object "SYS"."DBMS_REDEFINITION" does not exist


what grants do I need ?
  • 1. Re: dbms_redefinition - change table from non partitioned to partitioned
    user650888 Newbie
    Currently Being Moderated
    http://psoug.org/reference/dbms_redefinition.html

    above link says {ORACLE_HOME}/rdbms/admin/dbmshord.sql has to be compiled, Should I ask the DBAs to compile it for me ?
  • 2. Re: dbms_redefinition - change table from non partitioned to partitioned
    SomeoneElse Guru
    Currently Being Moderated
    Defined here:

    http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables007.htm#sthref2297
  • 3. Re: dbms_redefinition - change table from non partitioned to partitioned
    Justin Cave Oracle ACE
    Currently Being Moderated
    Why do you want to use DBMS_REDEFINITION?

    There are cases where this would be the right approach. If, for example, you need to run this script while the application is running and modifying the data in test_t1, the extra overhead of doing an online table redefinition is probably worth it. In most cases, however, you would be doing this sort of modification during a downtime window in which case the most efficient approach would be to create a new partitioned table, copy the data over, create the indexes, drop the old table, and rename the new one. Particularly if you run the loads in parallel.

    Justin
  • 4. Re: dbms_redefinition - change table from non partitioned to partitioned
    user650888 Newbie
    Currently Being Moderated
    I agree with you, I was just exploring more options

    But here is what I already did

    1. Rename test_t1 to test_t1_bkp (so indexes, constraints, triggers are copied to test_t1_bkp)

    2. Create empty table test_t1 only with partitions (No indexes, constraints or triggers)

    3. Execute my procedure ( on test_t1_bkp) that populates the pv partition column accordingly

    4. Now insert into test_t1 select * from test_t1_bkp (using append hint to speed up)

    5. Drop indexes, constraints and trigger on test_t1_bkp

    6. Create indexes, constraints , triggers on test_t1,

    I think above approach is good
  • 5. Re: dbms_redefinition - change table from non partitioned to partitioned
    Justin Cave Oracle ACE
    Currently Being Moderated
    That will work. Is it fast enough? If so, you're done.

    It's a bit concerning to me that it sounds like you're adding the PY column and that you're then partitioning on that column. If you are trying to use partitioning to improve query performance, that would imply that virtually every query in the system will include a predicate on the PY column. But that seems most unlikely if you just added the column.

    Justin
  • 6. Re: dbms_redefinition - change table from non partitioned to partitioned
    user650888 Newbie
    Currently Being Moderated
    the pv column already exists, we are just putting some logic in a procedure to populate it and update...

    the approach ran pretty fast, 3 hours for a 50gb table for the update and 40 minutes for insert, however, DBAs are not happy when I discussed my approach, hence the need for dbms_redefenition...............
  • 7. Re: dbms_redefinition - change table from non partitioned to partitioned
    Justin Cave Oracle ACE
    Currently Being Moderated
    What, specifically, were the DBAs unhappy about? How do they expect DBMS_REDEFINITION to mitigate whatever issues they see? It's fine to talk about options but unless we know what problem we're trying to solve, it's impossible to talk reasonably about what the appropriate options for addressing that problem are.
    the pv column already exists, we are just putting some logic in a procedure to populate it and update...
    But if you are hoping to improve query performance and PV is the column you're partitioning on (virtually) every query in the system would need to include a predicate on PV. It seems very odd that you would need to run a procedure to modify the data in that column, presumably causing every query in the system to potentially return different results, as part of the process of preparing to partition the table.

    Justin
  • 8. Re: dbms_redefinition - change table from non partitioned to partitioned
    user650888 Newbie
    Currently Being Moderated
    the update will be a one time update, we do not worry about it once pv is populated correctly

    I am trying the following after getting all the grants from dba, I have the grants needed



    begin
    dbms_redefinition.can_redef_table( 'username', 'TEST_T1' );
    end;


    ORA-06550: line 2, column 1:
    PLS-00201: identifier 'SYS.DBMS_REDEFINITION' must be declared
    ORA-06550: line 2, column 1:
    PL/SQL: Statement ignored


    what am i missing
  • 9. Re: dbms_redefinition - change table from non partitioned to partitioned
    user650888 Newbie
    Currently Being Moderated
    Do you recommend local indexes on the pv ?
  • 10. Re: dbms_redefinition - change table from non partitioned to partitioned
    Justin Cave Oracle ACE
    Currently Being Moderated
    user650888 wrote:
    the update will be a one time update, we do not worry about it once pv is populated correctly
    Assuming that your goal in partitioning the table is to improve query performance, (virtually) every query in the system would already need to have a predicate on PV. But if every query is already filtering on PV, it would seem extremely unlikely that you could tolerate the data ever having been incorrect. The fact that you are doing a one-time update of the key you are partitioning on strongly implies that you are partitioning on the wrong column.
    I am trying the following after getting all the grants from dba, I have the grants needed
    Based on the error, it seems unlikely that you have the grants you need.
    begin
    dbms_redefinition.can_redef_table( 'username', 'TEST_T1' );
    end;


    ORA-06550: line 2, column 1:
    PLS-00201: identifier 'SYS.DBMS_REDEFINITION' must be declared
    ORA-06550: line 2, column 1:
    PL/SQL: Statement ignored


    what am i missing
    What did the DBA grant you?

    As for whether to create local indexes, it depends. Is your goal to improve query performance (I'm still not clear if this is the goal)? Do all your queries have predicates on PV? How are you partitioning the table? How many distinct PV values are there per partition? Is data evenly distributed across PV values?

    Justin
  • 11. Re: dbms_redefinition - change table from non partitioned to partitioned
    rp0428 Guru
    Currently Being Moderated
    >
    the update will be a one time update, we do not worry about it once pv is populated correctly
    >
    You didnt' answer Justin's question: what is it the DBAs are unhappy about?

    To partition a non-partitioned table ALL of the data has to be moved. If you have an outage window there isn't much point to using DBMS_REDEFINITION to do an online operation.

    Why are you populating 'pv' in a table that you don't want anymore? If I were your DBA that is the first thing I would look at. Using a procedure to populate a column in a table that you want to get rid of.

    Why not just create the partition key values at the same time you insert into the target table?

    Tell us what that procedure is doing and what the DBAs are unhappy about.
  • 12. Re: dbms_redefinition - change table from non partitioned to partitioned
    SomeoneElse Guru
    Currently Being Moderated
    and what the DBAs are unhappy about.
    It might be easier to ask what they're happy about.

    ;-)
  • 13. Re: dbms_redefinition - change table from non partitioned to partitioned
    user650888 Newbie
    Currently Being Moderated
    Ok,

    pv is a column in test_t1 table,

    Initially, test_t1 is non partitioned, pv has some constant value in it....

    I wrote a function that has logic about how the value of pv has to be arrived at, That function is also used in loading the value in pv column in test_t1 table

    So I wrote a stored procedure which will use the function to arrive at the correct value for pv, and update test_t1 with correct values

    This update is obviously a one time run, and so is the subsequent insert in my steps mentioned above, the DBA says because the table is huge, lets skip the insert by using any other approach

    After all the hard work I did :(, I am not happy
  • 14. Re: dbms_redefinition - change table from non partitioned to partitioned
    user650888 Newbie
    Currently Being Moderated
    As per dbms_redefinition, the error occurs I think is because it does not exist or we need a public synonym / grants to my schema,
1 2 Previous Next

Legend

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