“This is the 14th day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021.”

When a non-partitioned table is converted using the online redefinition function, the intermediate table needs to be indexed, constrained, and other dependencies to rebuild. Oracle provides two methods:

Reference for this article: oracle-base.com/articles/mi…

A, COPY_TABLE_DEPENDENTS

Procedure: DBMS_REDEFINITION. Copy_table_dependents Procedure: DBMS_REDEFINITION.

SET SERVEROUTPUT ON
DECLARE
  l_errors  NUMBER;
BEGIN
  DBMS_REDEFINITION.copy_table_dependents(
    uname            => USER,
    orig_table       => 'BIG_TABLE',
    int_table        => 'BIG_TABLE2',
    copy_indexes     => DBMS_REDEFINITION.cons_orig_params,
    copy_triggers    => TRUE,
    copy_constraints => TRUE,
    copy_privileges  => TRUE,
    ignore_errors    => FALSE,
    num_errors       => l_errors,
    copy_statistics  => FALSE,
    copy_mvlog       => FALSE);
    
  DBMS_OUTPUT.put_line('Errors=' || l_errors);
END;
/
Copy the code

Usage may refer to the official documentation: docs.oracle.com/en/database…

Table 134-7 COPY_TABLE_DEPENDENTS Procedure Parameters

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
   uname                    IN  VARCHAR2,
   orig_table               IN  VARCHAR2,
   int_table                IN  VARCHAR2,
   copy_indexes             IN  PLS_INTEGER := 1,
   copy_triggers            IN  BOOLEAN     := TRUE,
   copy_constraints         IN  BOOLEAN     := TRUE,
   copy_privileges          IN  BOOLEAN     := TRUE,
   ignore_errors            IN  BOOLEAN     := FALSE,
   num_errors               OUT PLS_INTEGER,
   copy_statistics          IN  BOOLEAN     := FALSE, 
   copy_mvlog               IN  BOOLEAN     := FALSE); 
Copy the code

Advantages and disadvantages of this approach:

Advantages: Depending on the parameters you pass in, you can select dependencies to copy, indexes, triggers, constraints, permissions, and statistics. After dbMS_redefinition. Finish_redef_table is redefined, these dependencies are automatically switched to the partition table without manual rename operation.

Disadvantages: An index copied in this mode retains the index type of a non-partitioned table and is still a GLOBAL index. It is not automatically converted to a LOCAL index based on partitions.

Notes: If you do not want to set the index as a LOCAL index, you can use the above method to replicate the index.

2. Create a vm manually

You can manually create an index by specifying the LOCAL index. However, you need to manually rename the dbMS_redefinition. Finish_redef_table.

-- Add new keys, FKs and triggers.
ALTER TABLE big_table2 ADD (
  CONSTRAINT big_table_pk2 PRIMARY KEY (id)
);

CREATE INDEX bita_created_date_i2 ON big_table2(created_date) LOCAL;

CREATE INDEX bita_look_fk_i2 ON big_table2(lookup_id) LOCAL;

ALTER TABLE big_table2 ADD (
  CONSTRAINT bita_look_fk2
  FOREIGN KEY (lookup_id)
  REFERENCES lookup(id)
);

-- Gather statistics on the new table.
EXEC DBMS_STATS.gather_table_stats(USER.'BIG_TABLE2', cascade => TRUE);


-- Remove original table which now has the name of the interim table.
DROP TABLE big_table2;

-- Rename all the constraints and indexes to match the original names.
ALTER TABLE big_table RENAME CONSTRAINT big_table_pk2 TO big_table_pk;
ALTER TABLE big_table RENAME CONSTRAINT bita_look_fk2 TO bita_look_fk;
ALTER INDEX big_table_pk2 RENAME TO big_table_pk;
ALTER INDEX bita_look_fk_i2 RENAME TO bita_look_fk_i;
ALTER INDEX bita_created_date_i2 RENAME TO bita_created_date_i;
Copy the code

Advantages: Dependencies such as indexes can be created in an appropriate manner according to user requirements.

Disadvantages: because is the user to create and switch, so need to have a certain basis, can not miss any dependence, need to consider completely.

COPY_TABLE_DEPENDENTS + Create index manually

You can also use combinations to copy other dependencies with COPY_TABLE_DEPENDENTS and create indexes manually.

-- Exclude index
SET SERVEROUTPUT ON
DECLARE
  l_errors  NUMBER;
BEGIN
  DBMS_REDEFINITION.copy_table_dependents(
    uname            => USER,
    orig_table       => 'BIG_TABLE',
    int_table        => 'BIG_TABLE2',
    copy_indexes     => 0,
    copy_triggers    => TRUE,
    copy_constraints => TRUE,
    copy_privileges  => TRUE,
    ignore_errors    => FALSE,
    num_errors       => l_errors,
    copy_statistics  => FALSE,
    copy_mvlog       => FALSE);
    
  DBMS_OUTPUT.put_line('Errors=' || l_errors);
END;
/

Create index LOCAL (primary key index LOCAL)
CREATE INDEX bita_created_date_i2 ON big_table2(created_date) tablespace USERS LOCAL;

CREATE INDEX bita_look_fk_i2 ON big_table2(lookup_id) tablespace USERS LOCAL;

After the redefinition is complete, rename the index name
-- Rename all the constraints and indexes to match the original names.
ALTER INDEX bita_look_fk_i2 RENAME TO bita_look_fk_i;
ALTER INDEX bita_created_date_i2 RENAME TO bita_created_date_i;
Copy the code

Advantages: Combining the above two methods, this method only needs to pay attention to whether the index is missing, and does not need to pay attention to dependencies such as triggers, permissions, and constraints.