如何在oracle中重用列 [英] how to reused column in oracle

问题描述

ALTER TABLE abc SET UNUSED COLUMN abccolumn

这个查询我们可以设置一个未使用的列

但是我们可以重用这个列吗？

请回答

如果不是那么drop column和unused columns之间的差异是什么？

以及我们为什么不使用而不是放弃？

解决方案

如果您担心实际删除列所需的时间，基本上您将列设置为未使用。该列被处理为不存在。它不会显示在查询中，并且名称off to column可用于创建新列。

您可以对未使用的列执行的唯一操作是实际删除它，没有办法重新激活它。



DROP COLUMN和SET UNUSED之间的区别在于第一个实际删除了列并执行了所需的任何清理操作，第二个只是标记它处理。

Quote：更改表格 [ ^ ]

标记列未使用



如果您担心从大表中的所有行中删除列数据所需的时间长度，可以使用ALTER TABLE ... SET UNUSED语句。此语句将一个或多个列标记为未使用，但实际上不会删除目标列数据或还原这些列占用的磁盘空间。但是，标记为未使用的列不会显示在查询或数据字典视图中，并且会删除其名称，以便新列可以重用该名称。列中定义的所有约束，索引和统计信息也将被删除。



要将hiredate和mgr列标记为未使用，请执行以下语句：

  ALTER   TABLE  hr.admin_emp  SET  UNUSED（hiredate，mgr）; 

稍后您可以通过发出ALTER TABLE删除标记为未使用的列... DROP UNUSED COLUMNS声明。每当明确删除表中任何特定列或列时，也会从目标表中删除未使用的列。



数据字典查看USER_UNUSED_COL_TABS，ALL_UNUSED_COL_TABS或DBA_UNUSED_COL_TABS可用于列出包含未使用列的所有表。 COUNT字段显示表中未使用的列数。

  SELECT  *  FROM  DBA_UNUSED_COL_TABS; 

 OWNER TABLE_NAME COUNT 
 -------- ------------------- --------------------------- ---- -  
 HR ADMIN_EMP 2 

对于外部表，SET UNUSED语句将透明地转换为ALTER TABLE DROP COLUMN语句。由于外部表仅包含数据库中的元数据，因此DROP COLUMN语句与SET UNUSED语句等效执行。

删除未使用的列



ALTER TABLE ... DROP UNUSED COLUMNS语句是未使用列上允许的唯一操作。它物理地从表中删除未使用的列并回收磁盘空间。



在随后的ALTER TABLE语句中，指定了可选子句CHECKPOINT。此子句导致在处理指定行数后应用检查点，在本例中为250.检查点减少了在删除列操作期间累积的撤消日志量，以避免可能耗尽撤消空间。

  ALTER   TABLE  hr.admin_emp  DROP  UNUSED COLUMNS  CHECKPOINT   250 ; 

ALTER TABLE abc SET UNUSED COLUMN abccolumn
with this query we can set an "unused column"
but can we reused this column?
please answers
if not so whats diff between "drop column" and "unused column"?
and why we do unused rather than drop ?

解决方案

Basically you set a columns to unused if you are concerned about the time it would take to actually drop the column. The column is treaded as not being present. It will not show up in queries and the name off to column can be used to create a new column.
The only action you can perform on an unused column is to actually remove it, there is no way to reactivate it.

The difference between DROP COLUMN and SET UNUSED is that the first actually removed the column and performs any clean-up action needed and the second just marks it for disposal.

Quote: Altering Tables[^]

Marking Columns Unused

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 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. All constraints, indexes, and statistics defined on the column are also removed.

To mark the hiredate and mgr columns as unused, execute the following statement:

ALTER TABLE hr.admin_emp SET UNUSED (hiredate, mgr);

You can later remove columns that are marked as unused by issuing an 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.

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.

SELECT * FROM DBA_UNUSED_COL_TABS;

OWNER                       TABLE_NAME                  COUNT
--------------------------- --------------------------- -----
HR                          ADMIN_EMP                       2

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.
Removing Unused Columns

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.

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.

ALTER TABLE hr.admin_emp DROP UNUSED COLUMNS CHECKPOINT 250;

这篇关于如何在oracle中重用列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！