Unused&Constraint
1 constraints defined on the column would be removed by the UNUSED command：
SQL> desc LOCATIONS;
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------
LOCATION_ID NOT NULL NUMBER(4)
STREET_ADDRESS VARCHAR2(40)
POSTAL_CODE VARCHAR2(12)
CITY NOT NULL VARCHAR2(30)
STATE_PROVINCE VARCHAR2(25)
COUNTRY_ID CHAR(2)
SQL> select count(*) from LOCATIONS;
COUNT(*)
----------
23
SQL> select constraint_name,constraint_type from user_constraints
2 where table_name='LOCATIONS';
CONSTRAINT_NAME C
------------------------------ -
LOC_CITY_NN C
LOC_ID_PK P
LOC_C_ID_FK R
SQL> select column_name from user_cons_columns
2 where constraint_name='LOC_CITY_NN';
COLUMN_NAME
--------------------------------------------------------------------------------
CITY
SQL> alter table hr.LOCATIONS set unused (CITY);
表已更改。
------------------
SQL> select object_id from dba_objects where object_name='LOCATIONS';
OBJECT_ID
----------
51900
52376
SQL>
SQL> select col#,segcol#,name,intcol# from col$ where obj#=51900;
COL# SEGCOL# NAME INTCOL#
---------- ---------- ------------------------------ ----------
1 1 LOCATION_ID 1
2 2 STREET_ADDRESS 2
3 3 POSTAL_CODE 3
0 4 SYS_C00004_12031823:21:22$ 4
4 5 STATE_PROVINCE 5
5 6 COUNTRY_ID 6
已選擇6行。
--------------------------
SQL> select constraint_name,constraint_type from user_constraints
2 where table_name='LOCATIONS';
CONSTRAINT_NAME C
------------------------------ -
LOC_ID_PK P
LOC_C_ID_FK R
2 recovery:
SQL> select col#,segcol#,name,intcol# from col$ where obj#=51900;
COL# SEGCOL# NAME INTCOL#
---------- ---------- ------------------------------ ----------
1 1 LOCATION_ID 1
2 2 STREET_ADDRESS 2
3 3 POSTAL_CODE 3
0 4 SYS_C00004_12031823:21:22$ 4
4 5 STATE_PROVINCE 5
5 6 COUNTRY_ID 6
已選擇6行。
-------------------
SQL> UPDATE COL$ SET COL#=INTCOL# WHERE OBJ#=51900;
已更新6行。
SQL> UPDATE TAB$ SET COLS=COLS+1 WHERE OBJ#=51900;
已更新 1 行。
SQL> commti;
SP2-0042: 未知命令 "commti" - 其餘行忽略。
SQL> commit;
提交完成。
SQL> select header_file,header_block from dba_segments where segment_name='LOCATIONS';
HEADER_FILE HEADER_BLOCK
----------- ------------
5 35
SQL> select col#,segcol#,name,intcol# from col$ where obj#=51900;
COL# SEGCOL# NAME INTCOL#
---------- ---------- ------------------------------ ----------
1 1 LOCATION_ID 1
2 2 STREET_ADDRESS 2
3 3 POSTAL_CODE 3
4 4 SYS_C00004_12031823:21:22$ 4
5 5 STATE_PROVINCE 5
6 6 COUNTRY_ID 6
已選擇6行。
SQL> select constraint_name,constraint_type from user_constraints
2 where table_name='LOCATIONS';
CONSTRAINT_NAME C
------------------------------ -
LOC_ID_PK P
LOC_C_ID_FK R
---------before set unused:
SQL> select constraint_name,constraint_type from user_constraints
2 where table_name='LOCATIONS';
CONSTRAINT_NAME C
------------------------------ -
LOC_CITY_NN C
LOC_ID_PK P
LOC_C_ID_FK R
SQL> desc LOCATIONS;
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------
LOCATION_ID NOT NULL NUMBER(4)
STREET_ADDRESS VARCHAR2(40)
POSTAL_CODE VARCHAR2(12)
SP2-0642: SQL*Plus 內部錯誤狀態 2131, 上下文 0:0:0
繼續執行將不安全
continue:
SQL> update col$ set name='CITY' where obj#=51900 and SEGCOL#=4;
已更新 1 行。
SQL> update col$ set PROPERTY=0 where obj#=51900;
已更新6行。
SQL> commit;
提交完成。
SQL> select col#,segcol#,name,intcol# from col$ where obj#=51900;
COL# SEGCOL# NAME INTCOL#
---------- ---------- ------------------------------ ----------
1 1 LOCATION_ID 1
2 2 STREET_ADDRESS 2
3 3 POSTAL_CODE 3
4 4 CITY 4
5 5 STATE_PROVINCE 5
6 6 COUNTRY_ID 6
已選擇6行。
after bounced my oracle db:
SQL> desc LOCATIONS;
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------
LOCATION_ID NOT NULL NUMBER(4)
STREET_ADDRESS VARCHAR2(40)
POSTAL_CODE VARCHAR2(12)
CITY VARCHAR2(30)
STATE_PROVINCE VARCHAR2(25)
COUNTRY_ID CHAR(2)
SQL> select constraint_name,constraint_type from user_constraints
2 where table_name='LOCATIONS';
CONSTRAINT_NAME C
------------------------------ -
LOC_ID_PK P
LOC_C_ID_FK R
after all,now This can be summarized as follows：
1 constraints defined on the column would be removed by the UNUSED
2 after update col$ constraints not recoveried;
------------end--------------
refence:
col$ base insert as follows：
Insert into SYS.COL$
(OBJ#, COL#, SEGCOL#, SEGCOLLENGTH, OFFSET,
NAME, TYPE#, LENGTH, FIXEDSTORAGE, PRECISION#,
SCALE, NULL$, DEFLENGTH, DEFAULT$, INTCOL#,
PROPERTY, CHARSETID, CHARSETFORM, SPARE1, SPARE2,
SPARE3, SPARE4, SPARE5, SPARE6)
Values
(16416, 1, 1, 22, 0,
'X', 2, 22, 0, NULL,
NULL, 0, NULL, NULL, 1,
0, 0, 0, 0, 0,
0, NULL, NULL, NULL);
Insert into SYS.COL$
(OBJ#, COL#, SEGCOL#, SEGCOLLENGTH, OFFSET,
NAME, TYPE#, LENGTH, FIXEDSTORAGE, PRECISION#,
SCALE, NULL$, DEFLENGTH, DEFAULT$, INTCOL#,
PROPERTY, CHARSETID, CHARSETFORM, SPARE1, SPARE2,
SPARE3, SPARE4, SPARE5, SPARE6)
Values
(16416, 2, 2, 22, 0,
'Y', 2, 22, 0, NULL,
NULL, 0, NULL, NULL, 2,
0, 0, 0, 0, 0,
0, NULL, NULL, NULL);
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/13750068/viewspace-718907/，如需轉載，請註明出處，否則將追究法律責任。