SET UNUSED In Oracle

Onur ARDAHANLI

6 months ago

In today’s article, we will talk about how to use the SET UNUSED command in cases where dropping the column will take time.

We use SET UNUSED in cases where dropping the column will take time.

The columns used with this expression are not actually deleted but are marked as unused.

Columns marked as unused do not appear in both queries and data dictionary views.

Its name has been deleted, a new column with the same name can be created.

Indexes, constraints and statistics of columns marked as Unused are deleted.

	SQL> alter table iptvmwc.account set unused (account_id) cascade constraints;  
	
	Table altered.
	
	
	SQL> select * from dba_unused_col_tabs;
	
	OWNER                          TABLE_NAME                          COUNT
	------------------------------ ------------------------------ ----------
	IPTVMWC                        ACCOUNT                                 1

SQL> alter table iptvmwc.account set unused (account_id) cascade constraints;

Table altered.

SQL> select * from dba_unused_col_tabs;

OWNER TABLE_NAME COUNT

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

IPTVMWC ACCOUNT 1

Unused columns are dropped as follows.

It is used to perform the checkpoint operation when the specified number of rows is undoed in order to prevent unnecessary undo formation by performing the checkpoint operation at certain intervals.

	SQL> alter table iptvmwc.account drop unused column checkpoint 250;
	
	Table altered.
	
	SQL> select * from dba_unused_col_tabs;
	
no rows selected

SQL> alter table iptvmwc.account drop unused column checkpoint 250;

Table altered.

SQL> select * from dba_unused_col_tabs;

no rows selected