How to restore the unused column in Oracle

The purpose of setting unused is to use the unused flag to delete the unused when the cpu, memory, and other resources are insufficient.

After setting the unused column, the column data is not deleted immediately, but hidden physically. The recovery steps are as follows: back up a data dictionary before testing.

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 SET the display column width
SQL> SELECT OBJECT_ID, OBJECT_NAME FROM USER_OBJECTS;
OBJECT_ID OBJECT_NAME
Limit 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 $ ---------- the original column name is C and has been repaired by the system.
SQL> select cols from tab $ where obj #= 71930;
COLS
----------
2 ----------------------- the number of system fields has also changed
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 name = 'age' 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; ----------- this step is essential
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