Setting and deleting unavailable columns in Oracle

Set and delete unavailable columns in Oracle. 1. What are unavailable columns? That is, one or more columns in the table are ALTERTABLEhellip; The SETUNUSED statement is set to program

Set and delete unavailable columns in Oracle. 1. What are unavailable columns? That is, one or more columns in the table are ALTER TABLEhellip; the set unused statement is SET to program

Setting and deleting unavailable columns in Oracle

1. What is an unavailable column?

That is, one or more columns in the TABLE are alter table... The set unused statement is SET to a column that cannot be used by the program.

2. Use Cases?
If you are concerned about the length of time it cocould take to drop column data from
All of the rows in a large table, you can use the alter table... Set unused statement.

If you are worried that it may take a lot of time to delete a column from a large TABLE, you can use alter table... Set unused statement.

If you want to delete some columns in a large table with frequent read/write operations,
If you directly execute alter table abc drop (COLUMN) during busy hours );
The ORA-01562-failed to extend rollback segment number string may be received,
This is because you may consume the entire rollback tablespace during the column deletion process, resulting in such an error.

3. Why (Principles and advantages )?
3.1 set unavailable Columns
This statement marks one or more columns as unused, but does not actually remove
The target column data or restore the disk space occupied by these columns.
A column that is marked as unused is not displayed in queries or data dictionary
Views, and its name is removed so that a new column can reuse that name.

This statement identifies one or more columns as unavailable, but does not actually remove column data or recycle the space occupied by these columns.
An unavailable column is not displayed in the query or data dictionary view. The column name is deleted so that the new column can be reused.

In most cases, constraints, indexes, and statistics defined on the column are also removed.
In most cases, column constraints, indexes, and statistics are also removed.

The exception is that any internal indexes for LOB columns that are marked unused are not removed.
The exception is that the internal indexes of the LOB column identified as unavailable are not removed.

3.2 delete unavailable Columns
Alter table... The drop unused columns statement is only applicable to unavailable COLUMNS. It is used to delete COLUMNS marked as unavailable (delete COLUMNS physically and reclaim occupied space ).

In the alter table statement that follows, the optional clause CHECKPOINT is specified.
This clause causes a checkpoint to be applied after processing the specified number
Rows, in this case 250. Checkpointing cuts down on the amount of undo logs
Accumulated during the drop column operation to avoid a potential exhaustion
Undo space.

In the next alter table statement, the optional condition CHECKPOINT is specified.
This condition will trigger a checkpoint when the processing reaches the specified number of rows, where 250. the checkpoint reduces the number of undo logs accumulated in the delete column operation to avoid potential undo space depletion.

Alter table hr. admin_emp drop unused columns checkpoint 250;

4. Restrictions?
1) the columns in the table belonging to SYS cannot be deleted.
2)

5. syntax structure?
Alter table... Set unused (C1, c2 ..)
Alter table... DROP UNUSED COLUMNS

For example:
Alter table hr. admin_emp set unused (hiredate, mgr );
Alter table hr. admin_emp drop unused columns;

5. Data Dictionary
USER_UNUSED_COL_TABS
ALL_UNUSED_COL_TABS
DBA_UNUSED_COL_TABS

SELECT * FROM DBA_UNUSED_COL_TABS;
OWNER TABLE_NAME COUNT
-----------------------------------------------------------
The HR ADMIN_EMP 2-count column indicates the number of unavailable columns.

6. Use Cases?

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

The table has been created. SYS @ orcl> exec show_space ('tmp _ ALL_OBJECTS ', 'Scott ');
Unformatted Blocks ...... 0
FS1 Blocks (0-25) ......
FS2 Blocks (25-50) ...... 0
FS3 Blocks (50-75) ...... 0
FS4 Blocks (75-100) ......
Full Blocks ......
Total Blocks ......
Total Bytes .............. 3,145,728
Total MBytes...
Unused Blocks ......
Unused Bytes...
Last Used Ext FileId...
Last Used Ext BlockId... 14,592
Last Used Block...

The PL/SQL process is successfully completed.

SYS @ orcl> DESC DBA_UNUSED_COL_TABS
Is the name empty? Type
---------------------------------------------------------------------------
Owner not null VARCHAR2 (30)
TABLE_NAME not null VARCHAR2 (30)
COUNT NUMBER

SYS @ orcl> SELECT * FROM DBA_UNUSED_COL_TABS;

Unselected row

SCOTT @ orcl> alter table TMP_ALL_OBJECTS set unused (OBJECT_NAME );

The table has been changed.

SYS @ orcl> SELECT * FROM DBA_UNUSED_COL_TABS;