Oracle Partition Table online redefinition field not null

Oracle Partition Table online redefinition field not null

Oracle uses DBMS_REDEFINITION to redefine a table online. It synchronizes data to an intermediate table in the new structure based on the materialized view, and then implements it by renaming the table. The DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS stored procedure also copies the relevant dependency information to the intermediate table. However, if the source table has the not null constraint, pay attention to it.

The following tests:

Environment:
OS: CentOS 6.6
Db: 11.2.0.4

-- Create a test table source table
Create table scott. tb_source as select * from dba_objects;
-- Modify the two fields in the source table as not null to generate an error in subsequent steps.
Alter table scott. tb_source modify owner not null;
Alter table scott. tb_source modify object_name not null;
-- Update the Date Field of the source table and Scatter Data
Update scott. tb_source
Set created = to_date ('20140901', 'yyyymmdd') + dbms_random.value (20150101 );
Commit;

-- Create an intermediate table in the test table. The table structure is the table structure to be converted to the final source table.
-- In this test, a partitioned table with sub-partitions is used. A partitioned table without sub-partitions can also be used.
Create table scott. tb_mid
(
Owner VARCHAR2 (30) not null,
Object_name VARCHAR2 (128) not null,
Subobject_name VARCHAR2 (30 ),
Object_id NUMBER,
Data_object_id NUMBER,
Object_type VARCHAR2 (19 ),
Created DATE,
Last_ddl_time DATE,
Timestamp VARCHAR2 (19 ),
Status VARCHAR2 (7 ),
Temporary VARCHAR2 (1 ),
Generated VARCHAR2 (1 ),
Secondary VARCHAR2 (1 ),
Namespace NUMBER,
Edition_name VARCHAR2 (30)
)
Partition by range (created)
Subpartition by list (owner)
(
PARTITION p_2015 values less than (to_date ('201312', 'yyyymmdd '))
(Subpartition p_2015_sys values ('sys '),
Subpartition p_2015_system values ('system '),
Subpartition p_2015_other values (default)
),
PARTITION p_2016 values less than (to_date ('201312', 'yyyymmdd '))
(Subpartition p_2016_sys values ('sys '),
Subpartition p_2016_system values ('system '),
Subpartition p_2016_other values (default)
),
PARTITION p_max values less than (maxvalue)
(Subpartition p_max_sys values ('sys '),
Subpartition p_max_system values ('system '),
Subpartition p_max_other values (default)
)
);

-- Online redefinition
-- 1. Check whether the source table can be redefined.
-- The options_flag here selects DBMS_REDEFINITION.cons_use_pk or 1 based on the primary key in the source table, and does not have the primary key DBMS_REDEFINITION.cons_use_rowid or 2.
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE ('Scott ', 'tb _ source', options_flag => DBMS_REDEFINITION.cons_use_rowid );
END;
-- 2. Open parallel (optional)
Alter session force parallel dml parallel 4;
Alter session force parallel query parallel 4;

-- 3. Start Online restructuring
-- The options_flag here selects DBMS_REDEFINITION.cons_use_pk or 1 based on the primary key in the source table, and does not have the primary key DBMS_REDEFINITION.cons_use_rowid or 2.
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE ('Scott ', 'tb _ source', 'tb _ mid', options_flag => DBMS_REDEFINITION.cons_use_rowid );
END;

-- 4. Copy the related dependency information of the table, such as index, trigger, constraint, privilege, and statistics.
-- The stored procedure parameters are as follows:
/* PROCEDURE 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 );*/
-- Ignore_errors => true when the stored procedure is called, and errors when copying dependency information are ignored
DECLARE
Num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS ('Scott ', 'tb _ source', 'tb _ mid ',
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, num_errors );
END;

-- 5. View error message
-- The following error is reported because the not null constraint exists.
-- Solution to this problem: 1. Ignore, as long as the customs clearance day does not null constraint error, because the field in the intermediate table has not null
-- 2. Remove not null when creating an intermediate table, so that this error does not occur.
Select object_name, base_table_name, to_char (ddl_txt) from DBA_REDEFINITION_ERRORS;
/*
OBJECT_NAME BASE_TABLE_NAME TO_CHAR (DDL_TXT)
SYS_C0011143 TB_SOURCE alter table "SCOTT". "TB_MID" MODIFY ("OBJECT_NAME" CONSTRAINT "TMP $ _ SYS_C00111430" not null enable novalidate)
SYS_C0011142 TB_SOURCE alter table "SCOTT". "TB_MID" MODIFY ("OWNER" CONSTRAINT "TMP $ _ SYS_C00111420" not null enable novalidate)
*/


-- 6. Synchronize the source table to the intermediate table. This process can be synchronized multiple or zero times based on the data changes in the source table.
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE ('Scott ', 'tb _ source', 'tb _ mid ');
END;

-- 7. Complete online Reorganization
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE ('Scott ', 'tb _ source', 'tb _ mid ');
END;

-- 8. delete an intermediate table
Drop table scott. tb_mid purge;