oracle unused 语法_Oracle中关于恢复unused列的方法

设置unused的作用是为了在cpu、内存等资源不充足的时候，先做上unused标记再等数据库资源空闲的时候用drop set unused删除，设置

设置unused的作用是为了在cpu、内存等资源不充足的时候，先做上unused标记再等数据库资源空闲的时候用drop set unused删除

设置unused列之后，并不是将该列数据立即删除，而是被隐藏起来，物理上还是存在的，以下为恢复步骤：对数据字典不熟悉的朋友测试前做好备份工作

SQL> conn scott/Oracle

Connected.

SQL> create table xs (id number, name char(10),age number);

Table created.

SQL> insert into xs values(1,'JACK',20);

1 row created.

SQL> insert into xs values(2,'BILL',21);

1 row created.

SQL> insert into xs values(3,'TOM',22);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from xs;

>select * from xs;

ID NAME AGE

---------- ---------- ----------

1 JACK 20

2 BILL 21

3 TOM 22

SQL> alter table xs set unused column AGE;

Table altered.

SQL> select * from xs;

ID NAME

---------- ----------

1 JACK

2 BILL

3 TOM

SQL> SET LINESIZE 200 设置显示列宽

SQL> SELECT OBJECT_ID,OBJECT_NAME FROM USER_OBJECTS;

OBJECT_ID OBJECT_NAME

-------------------------------------------------------------------------------------------------- 51147 PK_DEPT

51146 DEPT

51148 EMP

51149 PK_EMP

51150 BONUS

51151 SALGRADE

52613 D

52614 D1

52615 D3

52696 BIG

52717 XS------------------------ OBJECT_ID=52717

11 rows selected.

SQL> conn / as sysdba

Connected.

SQL> select col#,intcol#,name from col$ where obj#=52717;

COL# INTCOL# NAME

---------- ---------- ------------------------------

1 1 ID

2 2 NAME

0 3 SYS_C00003_12092313:06:51$----------原来的列名为C，，被系统修了.

SQL> select cols from tab$ where obj#=71930;

COLS

----------

2 -----------------------系统的字段数目也发生了变化

SQL> update col$ set col#=intcol# where obj#=52717;

3 rows updated.

SQL> update tab$ set cols=cols+1 where obj#=52717;

1 row updated.

SQL> update col$ set where obj#=52717 and col#=3;

1 row updated.

SQL> update col$ set property=0 where obj#=52717;

3 rows updated.

SQL> commit;

Commit complete.

SQL> startup force; -----------这一步是必不可少的

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 92276304 bytes

Database Buffers 188743680 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.

SQL> select * from scott.xs;

ID NAME AGE

---------- ---------- ----------

1 JACK 20

2 BILL 21

3 TOM 22

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：php中文网