3 Replies Latest reply: Jun 24, 2014 7:21 AM by 6198f3a2-51e3-421f-9ba1-1d05a98f049f RSS

    Insufficient privilege error with DBMS_REDEFINITION.start_redef_table

    6198f3a2-51e3-421f-9ba1-1d05a98f049f

        

       

      I am trying to partition existing tables without dropping and recreating it using Oracle.

      DBMS_REDEFINITION package in my Oracle 10g supporting application

      I have have given all the necessary permission to the user as per mentioned in the oracle document.

      grant CREATE ANY TABLE to DDUSER; grant ALTER ANY TABLE to DDUSER; grant DROP ANY TABLE to DDUSER; grant LOCK ANY TABLE to DDUSER; grant SELECT ANY TABLE to DDUSER; grant DROP ANY INDEX to DDUSER;
      grant CREATE ANY INDEX to DDUSER;
      grant execute on dbms_redefinition to DDUSER;

      I am able to execute below procedure

      begin Dbms_Redefinition.Can_Redef_Table('DDUSER', 'TABLE'); end; This throws no error neither any result (Assuming this is as expected)

      But when I am trying to run

      BEGIN DBMS_REDEFINITION.start_redef_table( uname  => 'DDUSER', orig_table => 'TABLE', int_table  => 'TABLE_1'); END;

      I am getting below error:

      Error report:
      ORA-01031: insufficient privileges
      ORA-06512: at "SYS.DBMS_REDEFINITION", line 50
      ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343
      ORA-06512: at line 2
      01031. 00000 - "insufficient privileges"

      Could you please help me, what privilege I am missing here? or if there is any idea about which operation is executed in line 50 at package DBMS_REDEFINITION?

        • 1. Re: Insufficient privilege error with DBMS_REDEFINITION.start_redef_table
          raj4tech

          Can you please paste the actual sample code which you have tried to check for the posibility of redefition. Based on the given grant it should work.

          • 2. Re: Insufficient privilege error with DBMS_REDEFINITION.start_redef_table
            raj4tech

            I have doubt over the interim table (TABLE_1) which you are refering here, please paste the script which you are trying.

            • 3. Re: Insufficient privilege error with DBMS_REDEFINITION.start_redef_table
              6198f3a2-51e3-421f-9ba1-1d05a98f049f

              Here it is.  I am using the following aproach
              (please assume name of the table as 'TABLE' , It has been replaced based on the compliance terms )

              ---**********************************

              --STEP1:

              ---**********************************

              --Please provide the below grants

              grant CREATE ANY TABLE to DDUSER;

              grant ALTER ANY TABLE to DDUSER;

              grant DROP ANY TABLE to DDUSER;

              grant LOCK ANY TABLE to DDUSER;

              grant SELECT ANY TABLE to DDUSER;

              grant execute on dbms_redefinition to DDUSER;

               

               

              ---**********************************

              --STEP2:

              ---**********************************

              DROP TABLE DDUSER.TABLE_1;

               

              -- Create the Temporary partitioned tables

              PROMPT CREATE TEMPORARY DDUSER.TABLE_1 table

               

              CREATE TABLE DDUSER.TABLE_1

              (

              COLUMN1 DATE NOT NULL,                                

              COLUMN2 NUMBER(5) NOT NULL,                             

              COLUMN3 CHAR(6) NOT NULL,                            

              COLUMN4 VARCHAR2(20) NOT NULL,                          

              COLUMN5 CHAR(8) NOT NULL,                               

              COLUMN6 DATE,                       

              COLUMN7 VARCHAR2(8)

              )

              PARTITION BY RANGE (COLUMN6)

              (

                 PARTITION P13 VALUES LESS THAN (TO_DATE('2013-01-01','YYYY-MM-DD')),

                 PARTITION P14 VALUES LESS THAN (TO_DATE('2014-01-01','YYYY-MM-DD')),

                 PARTITION P15 VALUES LESS THAN (TO_DATE('2015-01-01','YYYY-MM-DD')),

                 PARTITION P16 VALUES LESS THAN (TO_DATE('2016-01-01','YYYY-MM-DD')),

                 PARTITION P17 VALUES LESS THAN (TO_DATE('2017-01-01','YYYY-MM-DD')),

                 PARTITION P18 VALUES LESS THAN (TO_DATE('2018-01-01','YYYY-MM-DD')),

                 PARTITION P19 VALUES LESS THAN (TO_DATE('2019-01-01','YYYY-MM-DD')),

                 PARTITION P20 VALUES LESS THAN (TO_DATE('2020-01-01','YYYY-MM-DD'))

              );

               

               

              ---**********************************

              --STEP3:

              ---**********************************

              ------------------------------------------------------------

              -- Check whether main table and temporary tables are in sync

              ------------------------------------------------------------

              -- The below script should return 0 rows

              select

              OWNER,

              table_name,

              COLUMN_NAME,

              DATA_TYPE,

              DATA_TYPE_MOD,

              DATA_TYPE_OWNER,

              DATA_LENGTH,

              DATA_PRECISION,

              DATA_SCALE,

              NULLABLE,

              COLUMN_ID,

              DEFAULT_LENGTH

              from all_tab_columns where owner = 'DDUSER'

              and table_name in ('TABLE')

              MINUS

              select

              OWNER,

              substr(table_name,1,5),

              COLUMN_NAME,

              DATA_TYPE,

              DATA_TYPE_MOD,

              DATA_TYPE_OWNER,

              DATA_LENGTH,

              DATA_PRECISION,

              DATA_SCALE,

              NULLABLE,

              COLUMN_ID,

              DEFAULT_LENGTH

              from all_tab_columns where owner = 'DDUSER'

              and table_name  in ('TABLE_1');

               

               

              ---**********************************

              --STEP4:

              ---**********************************

              -- File to check whether the tables can be partitiones or not?

               

              EXEC Dbms_Redefinition.Can_Redef_Table('DDUSER', 'TABLE');

               

               

              ---**********************************

              --STEP5:

              ---**********************************

               

              --start redefinition

              BEGIN

                DBMS_REDEFINITION.start_redef_table(

                  uname      => 'DDUSER',       

                  orig_table => 'TABLE',

                  int_table  => 'TABLE_1');

              END;

              /