ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Unused&Constraint

Unused&Constraint

原创 Linux操作系统 作者:yyp2009 时间:2012-03-18 23:23:12 0 删除 编辑

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:

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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: DML Views
下一篇: 立桥沽高
请登录后发表评论 登录
全部评论

注册时间:2008-10-17

  • 博文量
    330
  • 访问量
    1018837