the unused columns
<font color="#0000F0"><b>Marking Columns Unused</b></font><a id="sthref1871" name="sthref1871"></a><p>If you are concerned about the length of time it could take to drop
column data from all of the rows in a large table, you can use the <a id="sthref1872" name="sthref1872"></a>ALTER TABLE...SET UNUSED
statement. 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. However, 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. <font color="#F00000">All constraints,
indexes, and statistics defined on the column are also removed.</font></p>
<p>To mark the hiredate and mgr columns as unused, execute the following statement:</p>
ALTER TABLE hr.admin_emp SET UNUSED (hiredate, mgr);
<p><a id="sthref1873" name="sthref1873"></a>You can later remove columns that are marked as unused by issuing an <a id="sthref1874" name="sthref1874"></a>ALTER TABLE...DROP UNUSED COLUMNS
statement. Unused columns are also removed from the target table
whenever an explicit drop of any particular column or columns of the
table is issued.</p>
<p>The data dictionary views USER_UNUSED_COL_TABS, ALL_UNUSED_COL_TABS, or DBA_UNUSED_COL_TABS can be used to list all tables containing unused columns. The COUNT field shows the number of unused columns in the table.</p>
SELECT * FROM DBA_UNUSED_COL_TABS;
<br>OWNER TABLE_NAME COUNT<br> --------------------------- --------------------------- -----<br> HR ADMIN_EMP 2
<p>For external tables, the SET UNUSED statement is transparently converted into an ALTER TABLE DROP COLUMN statement. Because external tables consist of metadata only in the database, the DROP COLUMN statement performs equivalently to the SET UNUSED statement.</p>
<a id="ADMIN11666" name="ADMIN11666"></a>
<div class="sect3"><a id="sthref1875" name="sthref1875"></a>
<font color="#0000F0"><b>Removing Unused Columns
</b></font><p>The ALTER TABLE...DROP UNUSED COLUMNS statement is the
only action allowed on unused columns. It physically removes unused
columns from the table and reclaims disk space.</p>
<p>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 of 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 of undo space.<a id="sthref1876" name="sthref1876"></a></p>
ALTER TABLE hr.admin_emp DROP UNUSED COLUMNS CHECKPOINT 250;<br><br>原文来源： http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/tables006.htm<br></div>
页:
[1]