Oracle设置和删除不可用列

–count列代表不可用列数量

6、使用案例？

SCOTT@orcl> create table tmp_all_objects
  2  AS
  3  SELECT object_id, object_name
  4  from dba_objects
  5  ;

表已创建。

SYS@orcl> exec show_space(‘TMP_ALL_OBJECTS‘,‘SCOTT‘);
Unformatted Blocks  ....................               0
FS1 Blocks (0-25)   ....................               0
FS2 Blocks (25-50)  ....................               0
FS3 Blocks (50-75)  ....................               0
FS4 Blocks (75-100) ....................               0
Full Blocks         ....................             352
Total Blocks ...........................             384
Total Bytes  ...........................       3,145,728
Total MBytes ...........................               3
Unused Blocks...........................              18
Unused Bytes ...........................         147,456
Last Used Ext FileId....................               4
Last Used Ext BlockId...................          14,592
Last Used Block.........................             110

PL/SQL 过程已成功完成。




SYS@orcl> DESC DBA_UNUSED_COL_TABS
 名称                                     是否为空? 类型
 ---------------------------------------- -------- ---------------------------
 OWNER                                    NOT NULL VARCHAR2(30)
 TABLE_NAME                               NOT NULL VARCHAR2(30)
 COUNT                                             NUMBER

SYS@orcl> SELECT * FROM DBA_UNUSED_COL_TABS;

未选定行

SCOTT@orcl> ALTER TABLE TMP_ALL_OBJECTS SET UNUSED(OBJECT_NAME);

表已更改。

SYS@orcl> SELECT * FROM DBA_UNUSED_COL_TABS;

OWNER                          TABLE_NAME                          COUNT
------------------------------ ------------------------------ ----------
SCOTT                          TMP_ALL_OBJECTS                         1

SYS@orcl> exec show_space(‘TMP_ALL_OBJECTS‘,‘SCOTT‘);
Unformatted Blocks  ....................               0
FS1 Blocks (0-25)   ....................               0
FS2 Blocks (25-50)  ....................               0
FS3 Blocks (50-75)  ....................               0
FS4 Blocks (75-100) ....................               0
Full Blocks         ....................             352
Total Blocks ...........................             384
Total Bytes  ...........................       3,145,728
Total MBytes ...........................               3
Unused Blocks...........................              18
Unused Bytes ...........................         147,456
Last Used Ext FileId....................               4
Last Used Ext BlockId...................          14,592
Last Used Block.........................             110

PL/SQL 过程已成功完成。

--删除不可用列
SCOTT@orcl> ALTER TABLE TMP_ALL_OBJECTS DROP UNUSED COLUMNS CHECKPOINT 250;

表已更改。

SYS@orcl> SELECT * FROM DBA_UNUSED_COL_TABS;

未选定行


SYS@orcl> exec show_space(‘TMP_ALL_OBJECTS‘,‘SCOTT‘);
Unformatted Blocks  ....................               0
FS1 Blocks (0-25)   ....................               0
FS2 Blocks (25-50)  ....................               1
FS3 Blocks (50-75)  ....................             350
FS4 Blocks (75-100) ....................               1
Full Blocks         ....................               0
Total Blocks ...........................             384
Total Bytes  ...........................       3,145,728
Total MBytes ...........................               3
Unused Blocks...........................              18
Unused Bytes ...........................         147,456
Last Used Ext FileId....................               4
Last Used Ext BlockId...................          14,592
Last Used Block.........................             110

PL/SQL 过程已成功完成。

--move操作，减少碎片
SCOTT@orcl> ALTER TABLE TMP_ALL_OBJECTS MOVE;

表已更改。


SYS@orcl> exec show_space(‘TMP_ALL_OBJECTS‘,‘SCOTT‘);
Unformatted Blocks  ....................               0
FS1 Blocks (0-25)   ....................               0
FS2 Blocks (25-50)  ....................               0
FS3 Blocks (50-75)  ....................               0
FS4 Blocks (75-100) ....................               0
Full Blocks         ....................             113
Total Blocks ...........................             128
Total Bytes  ...........................       1,048,576
Total MBytes ...........................               1
Unused Blocks...........................               5
Unused Bytes ...........................          40,960
Last Used Ext FileId....................               4
Last Used Ext BlockId...................          14,808
Last Used Block.........................               3

PL/SQL 过程已成功完成。

--可以看到总块数下降

--删除测试表
SCOTT@orcl> drop table TMP_ALL_OBJECTS;

表已删除。

7、关于不可用列的恢复（以下摘自网络）
刚才有个人问我如何修复被设置为UNUSED的字段，我考虑了一下，以下的方法可以恢复（以下步骤执行前要做好备份），没有经验的DBA不要轻易尝试。

1、创建实验表TTTA

SQL> CREATE TABLE TTTA ( A INTEGER,B INTEGER,C VARCHAR2(10),D INTEGER);
表已创建。
SQL> INSERT INTO TTTA VALUES (1,2,‘3‘,4);
已创建 1 行。
SQL> INSERT INTO TTTA VALUES (2,3,‘4‘,5);
已创建 1 行。
SQL> COMMIT;
提交完成。
ALTER TABLE TTTA SET UNUSED COLUMN C;

2、以下进行恢复

SQL> SELECT OBJ# FROM OBJ$ WHERE NAME=‘TTTA‘;
      OBJ#
----------
     32067
SELECT COL#,INTCOL#,NAME FROM COL$ WHERE OBJ#=32067;
      COL#    INTCOL# NAME
---------- ---------- ------------------------------
         1          1 A
         2          2 B
         0          3 SYS_C00003_08031720:09:55$   被UNUSED的字段
         3          4 D
SQL> SELECT COLS FROM TAB$ WHERE OBJ#=32067;
      COLS
----------
         3      ------字段数变为3了


SQL> UPDATE COL$ SET COL#=INTCOL# WHERE OBJ#=32067;
已更新4行。
SQL> UPDATE TAB$ SET COLS=COLS+1 WHERE OBJ#=32067;
已更新 1 行。
UPDATE COL$ SET NAME=‘C‘ WHERE OBJ#=32067 AND COL#=3;
UPDATE COL$ SET PROPERTY=0 WHERE OBJ#=32067;
SQL> COMMIT;

3、重启数据库

SQL> SELECT * FROM SCOTT.TTTA;
         A          B C                   D
---------- ---------- ---------- ----------
         1          2 3                   4
         2          3 4                   5

恢复完成

版权声明：本文为博主原创文章，未经博主允许不得转载。

Oracle设置和删除不可用列

标签：oracle   不可用列