在线重定义后会发现列not null没有copy过来,
执行sql alter table modify column not null
报如下错误
ORA-01442: column to be modified to NOT NULL is already NOT NULL通过sql查询发现
select constraint_name,constraint_type,status,validated
from user_constraints
where constraint_name in (select constraint_name from user_cons_columns where table_name='TEST' and column_name='TEST');
列都是 NOT VALIDATED 状态
查询MOS发现时一个bug mos 详细信息如下: ID 1089860.1
APPLIES TO:
Oracle Server - Enterprise Edition - Version 10.2.0.1 and laterInformation in this document applies to any platform.
Reviewed for relevance 24 April 2012
SYMPTOMS
After redefining a table using DBMS_REDEFINITION it is found that a describe of the table no longer shows the NOT NULL constraints
CAUSE
The cause of this condition is two fold
1) The constraint really was not copied ... due to COMPATIBLE not being 10.2 or higher
2) The constraint was copied ... but appears to not have been ... (ie a describe shows a for the 'Null?' column) The cause is due to the constraint not being enabled for VALIDATE
These are discussed in the INTERNAL ONLY bug
Bug:4396234 ET10.2OREDEF: NULLABLE COL OF *_TAB_COLUMNS TABLE NOT UPDATED AFTER ONLINE REDEF
"In order to copy Not Null constraints, COMPATIBLE must be set to 10.2 or higher. "
"Not null/Primary key constraints are copied in NOVALIDATE mode in order to speed up the redefinition process."
SOLUTION
1) If COMPATIBLE < 10.2 then NOT NULL constraints must be manually copied or the redefinition restarted with compatible set to 10.2 or higher
2) If COMPATIBLE => 10.2 the constraints will be copied ... but will be in NOVALIDATE mode
The not null constraint CAN be reenabled for VALIDATE using the ALTER TABLE ... ENABLE VALIDATE CONSTRAINT ... command
CASE STUDY
create user test identified by test;
grant dba to test;
alter user test default tablespace users;
connect test/test;
SHOW PARAMETER COMPATIBLE
-- NAME TYPE VALUE
-- ------------------------------------ ----------- ------------------------------
-- compatible string 11.2.0.0.0
-- CREATE THE TABLE TO BE REDEFINED
CREATE TABLE ORIGINAL (COL1 VARCHAR2(10) NOT NULL);
-- Table created.
-- CREATE THE INTERIM TABLE FOR THE REDEFINITION
CREATE TABLE INTERIM (COL1 VARCHAR2(20));
-- Table created.
-- DESCRIBE THE TWO TABLES JUST CREATED
DESC ORIGINAL
-- ---------------- -------- ----------------------------
-- COL1 NOT NULL VARCHAR2(10)
DESC INTERIM
-- ---------------- -------- ----------------------------
-- COL1 VARCHAR2(20)
-- PROVE THAT THE REDEFINITION CAN WORK
exec dbms_redefinition.can_redef_table('TEST','ORIGINAL', dbms_redefinition.cons_use_rowid);
-- PL/SQL procedure successfully completed.
-- START THE REDEFINITION
exec dbms_redefinition.start_redef_table('TEST','ORIGINAL','INTERIM','COL1 COL1',dbms_redefinition.cons_use_rowid);
-- PL/SQL procedure successfully completed.
-- COPY THE CONSTRIANTS FROM THE ORIGINAL TABLE TO THE INTERIM TABLE
SET SERVEROUTPUT ON
DECLARE
error_count pls_integer := 0;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('TEST',
'ORIGINAL',
'INTERIM',
dbms_redefinition.cons_orig_params ,
TRUE,
TRUE,
TRUE,
FALSE,
error_count);
DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/
-- PL/SQL procedure successfully completed.
-- FINISH THE REDEFINITION (THIS IS WHERE THE ROWS GET COPIED)
exec dbms_redefinition.finish_redef_table('TEST','ORIGINAL','INTERIM');
-- PL/SQL procedure successfully completed.
--LOOK AT THE DESCRIPTION OF THE NEWLY REDFINED TABLE
DESC ORIGINAL
-- Name Null? Type
-- ----------------------------------------- -------- ----------------------------
-- COL1 VARCHAR2(20)
-- ---------------- -------- ----------------------------
-- COL1 VARCHAR2(20)
-- NOTICE THAT OUR 'Null?' column no longer says 'NOT NULL'
-- this would imply that our constraint is gone
-- PROVE THAT THE CONSTRAINT STILL EXISTS
SELECT CONSTRAINT_NAME, VALIDATED FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'ORIGINAL';
-- ------------------------------ -------------
-- SYS_C0011548 NOT VALIDATED
-- THE CONSTRAINT EXISTS BUT IS 'NOT VALIDATED'
-- ALTER THE TABLE SO THAT THE NOT NULL CONSTRAINT IS NOW VALIDATED
ALTER TABLE ORIGINAL ENABLE VALIDATE CONSTRAINT SYS_C0011548;
-- Table altered.
-- REEXAMINE THE TABLE
DESC ORIGINAL
-- ---------------- -------- ----------------------------
-- COL1 NOT NULL VARCHAR2(20)
SELECT CONSTRAINT_NAME, VALIDATED FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'ORIGINAL';
-- CONSTRAINT_NAME VALIDATED
-- ------------------------------ -------------
-- SYS_C0011548 VALIDATED
-- ------------------------------ -------------
-- SYS_C0011548 VALIDATED
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15747463/viewspace-1184339/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15747463/viewspace-1184339/