|
Primary Key column become NULLable during online table redifinition
#39293516
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
Участник
Откуда: Боярышник
Сообщения: 2 193
|
|
Зацените феномен, проявляющийся при использовании Online table redefinition :
До:OWNER CONSTRAINT_NAMECSEARCH_CONDITIONR_OWNERR_CONSTRAINT_NAMESTATUS DEFERRED VALIDATEDINVALIDCOLUMN_NAMEPOSITIONNCLFXINT_OWNERT1_PK P ENABLEDIMMEDIATEVALIDATED TRADE_ID 1N
После:OWNER CONSTRAINT_NAMECSEARCH_CONDITIONR_OWNERR_CONSTRAINT_NAMESTATUS DEFERRED VALIDATEDINVALIDCOLUMN_NAMEPOSITIONNCLFXINT_OWNERT1_PK P ENABLEDIMMEDIATEVALIDATED TRADE_ID 1Y
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL>-- Create table
SQL>create table t1
2 (
3 trade_id NUMBER(18) constraint t1_pk primary key,
4 be_id NUMBER
5 )
6 ;
Table created.
SQL>select c.owner, c.constraint_name, c.constraint_type, c.search_condition, c.r_owner, c.r_constraint_name,
2 c.status, c.deferred, c.validated, c.invalid,
3 cc.column_name, cc.position,
4 tc.nullable
5 from all_constraints c
6 inner join all_cons_columns cc
7 on (c.owner = cc.owner and c.constraint_name = cc.constraint_name and c.table_name = cc.table_name)
8 inner join all_tab_columns tc
9 on (c.owner = tc.owner and c.table_name = tc.table_name and cc.column_name = tc.column_name)
10 where c.owner = user
11 and c.table_name = 'T1'
12 order by c.constraint_name, cc.position
13 ;
OWNER CONSTRAINT_NAME C SEARCH_CONDITION R_OWNER R_CONSTRAINT_NAME STATUS DEFERRED VALIDATED INVALID COLUMN_NAME POSITION N
------------- --------------- - ---------------- ------- ----------------- ------- --------- --------- ------- ----------- -------- -
CLFXINT_OWNER T1_PK P ENABLED IMMEDIATE VALIDATED TRADE_ID 1 N
SQL>create table i_t1
2 (
3 trade_id NUMBER(18),
4 be_id NUMBER,
5 hot_yn CHAR(1) DEFAULT 'N' NOT NULL,
6 CONSTRAINT t1_check_hot_yn CHECK (hot_yn IN ('Y','N'))
7 )
8 PARTITION BY LIST (be_id)
9 SUBPARTITION BY LIST (hot_yn)
10 ( PARTITION t1_DEFAULT VALUES (DEFAULT)
11 (SUBPARTITION t1_DEFAULT_HOT_Y VALUES('Y'),
12 SUBPARTITION t1_DEFAULT_HOT_N VALUES('N')
13 )
14 )
15 ;
Table created.
SQL>alter table i_t1 enable row movement;
Table altered.
SQL>BEGIN
2 DBMS_REDEFINITION.can_redef_table(USER, 'T1');
3 END;
4 /
PL/SQL procedure successfully completed.
SQL>--1. Start redefinition
SQL>BEGIN
2 DBMS_REDEFINITION.start_redef_table(USER, 'T1','I_T1');
3 END;
4 /
PL/SQL procedure successfully completed.
SQL>--2. Copy table dependents
SQL>DECLARE
2 v_num_errors NUMBER;
3 BEGIN
4 DBMS_REDEFINITION.copy_table_dependents(
5 uname => USER,
6 orig_table => 'T1',
7 int_table => 'I_T1',
8 copy_indexes => 0,
9 copy_triggers => TRUE,
10 copy_constraints => TRUE,
11 copy_privileges => TRUE,
12 ignore_errors => FALSE,
13 num_errors => v_num_errors,
14 copy_statistics => FALSE,
15 copy_mvlog => FALSE
16 );
17
18 IF v_num_errors > 0 THEN
19 DBMS_REDEFINITION.abort_redef_table(
20 uname => USER,
21 orig_table => 'T1',
22 int_table => 'I_T1'
23 );
24
25 RAISE_APPLICATION_ERROR(-20001,'Error in DBMS_REDEFINITION.copy_table_dependents');
26 END IF;
27
28 DBMS_REDEFINITION.sync_interim_table(
29 uname => USER,
30 orig_table => 'T1',
31 int_table => 'I_T1'
32 );
33
34 DBMS_REDEFINITION.finish_redef_table(
35 uname => USER,
36 orig_table => 'T1',
37 int_table => 'I_T1'
38 );
39 end;
40 /
PL/SQL procedure successfully completed.
SQL>select c.owner, c.constraint_name, c.constraint_type, c.search_condition, c.r_owner, c.r_constraint_name,
2 c.status, c.deferred, c.validated, c.invalid,
3 cc.column_name, cc.position,
4 tc.nullable
5 from all_constraints c
6 inner join all_cons_columns cc
7 on (c.owner = cc.owner and c.constraint_name = cc.constraint_name and c.table_name = cc.table_name)
8 inner join all_tab_columns tc
9 on (c.owner = tc.owner and c.table_name = tc.table_name and cc.column_name = tc.column_name)
10 where c.owner = user
11 and c.table_name = 'T1'
12 order by c.constraint_name, cc.position
13 ;
OWNER CONSTRAINT_NAME C SEARCH_CONDITION R_OWNER R_CONSTRAINT_NAME STATUS DEFERRED VALIDATED INVALID COLUMN_NAME POSITION N
------------- --------------- - -------------------- ------- ----------------- ------- --------- --------- ------- ----------- -------- -
CLFXINT_OWNER SYS_C0042071 C "HOT_YN" IS NOT NULL ENABLED IMMEDIATE VALIDATED HOT_YN N
CLFXINT_OWNER T1_CHECK_HOT_YN C hot_yn IN ('Y','N') ENABLED IMMEDIATE VALIDATED HOT_YN N
CLFXINT_OWNER T1_PK P ENABLED IMMEDIATE VALIDATED TRADE_ID 1 Y
|
|
|
