 |
|
Dropping Table Columns
Oracle Tips by Burleson
|
Prior to Oracle8i, if you created a table with
a column you later found you didn’t need, you had to rebuild the
entire table to get rid of it. Not so in Oracle8i and Oracle.
Finally, after years of users requesting it, Oracle has added the drop
column capability to the ALTER TABLE command. The new ALTER TABLE drop
column clause has the following options:
SET UNUSED
[COLUMN column_name]|[(column_name(s))]
[CASCADE|INVALIDATE CONSTRAINTS]
DROP [COLUMN
column_name]|[(column_name(s))]
[CASCADE|INVALIDATE CONSTRAINTS]
[CHECKPOINT integer]
DROP UNUSED
COLUMNS|COLUMNS CONTNUE [CHECKPOINT integer]
The DROP clause will drop the column(s),
dependent constraints, and triggers specified in the command, even if
those items also are used with other columns. So be careful to fully
understand the implications of using the DROP clause before you use
it.
The SET UNUSED clause allows you to deactivate
the column without dropping it. This allows a preliminary test of
whether the column is really able to be dropped without damaging
existing data. It also allows you to cascade the action to dependent
constraints.
The INVALIDATE option allows you to invalidate
all directly and indirectly dependent objects such as procedures,
functions, and so on.
The DROP clause allows either a drop of an
active column or columns or just a drop of columns previously marked
as unused. As with the UNUSED clause, constraints can be cascaded
(values are deleted) and objects invalidated. The DROP clause also
allows you to force a checkpoint. Note that a drop of any column in a
table with columns marked as UNUSED will result in a drop of all
UNUSED columns, as well as the explicitly stated column.
Reclaiming a Column Marked as UNUSED
Oops! You marked the wrong column as UNUSED
and now you find that you can’t go back…or can you? During the testing
of this feature, I found a method to reclaim a column marked as
UNUSED. Once you DROP a column that has been marked as UNUSED, it is
gone for good, but up to the point where it is actually dropped, it
can be reclaimed. This is how the technique works (I suggest
practicing before doing the reclaim on a production table):
The col$ has obj#, col#, name and property
fields, which tie into the columns for each table where obj# will
correspond to the obj# in the tab$ table. When a column is marked
unused, the col# is set to 0, the name is set to a system-generated
name, and the property is set to 32800 from its initial value of 0
(for a normal, nontype column). In the tab$ table, the number of
columns is decremented by one in the cols column. The obj$ table
stores the link between the object name and owner and the obj#; you
then use that obj# to pull relevant data from the tab$ and col$
tables.
In order to reverse the unused state, reset
the col# value to its original value, reset the name column to the
proper name, and reset the property to 0. In the tab$ table, reset the
cols column to the appropriate value.
You will have to flush the shared pool, or
even restart to flush the object caches and dictionary caches, to see
the column again. To test this process, perform the following steps:
1.
Create a small table with two to three columns
2.
Look at the entries for obj$, tab$, and col$ for this table,
noting the values for COLS in tab$ and COL#, INTCOL#, PROPERTY, and
NAME in col$ (from SYS or INTERNAL user).
3.
Add data to the table, and commit.
4.
Select from the table to confirm the data and columns.
5.
Use ALTER TABLE SET UNUSED to mark a column unused.
6.
Select from the table to confirm the column is unavailable.
7.
Log in as SYS or INTERNAL, and check tab$ and col$ as before.
8.
Update tab$ and col$ to before the ALTER conditions, and
commit.
9.
Flush the shared pool to get rid of the postcondition SELECT
statement parse.
Issue a SELECT against the table to confirm
the column reappeared.
Note: Sometimes you may need to shut
down and restart before the column will reappear.
That should do it. Of course, if you actually
drop the column, you can't reclaim it. And remember, dropping any
column in a table will result in loss of all columns marked UNUSED,
whether mentioned in the DROP or not.
Table Statistics and Validity: The ANALYZE
Command
To help a DBA determine whether a table’s data
and indexes have integrity, and to calculate the statistics used by
the cost-based optimizer, all versions of Oracle since version 7
provide the ANALYZE command, which is used to analyze the structure of
a table and its indexes. The schema object to be analyzed must be in
your own schema or you must have the ANALYZE ANY system privilege.
If you want to list chained rows of a table or
cluster into a list table, the list table must be in your own schema
and you must have INSERT privilege on the list table or the INSERT ANY
TABLE system privilege.
If you want to validate a partitioned table,
you must have the INSERT privilege on the table, into which you list
analyzed rowids, or you must have the INSERT ANY TABLE system
privilege.
Note: The Oracle syntax for ANALYZE is
contained in the SQL reference manual on the Oracle technet website.
Some options for the ANALYZE command require a
table. This table is named using the INTO clause. Usually, this
specifies a table into which Oracle lists the migrated and chained
rows. If you omit schema, Oracle assumes the list table is in your own
schema. If you omit this clause altogether, Oracle assumes that the
table is named CHAINED_ROWS. The script used to create this table is
UTLCHAIN.SQL. The list table must be on your local database.
The structure under Oracle for the table is:
CREATE TABLE
chained_rows (
owner_name
varchar2(30),
table_name
varchar2(30),
cluster_name varchar2(30),
partition_name varchar2(30),
head_rowid
rowid,
analyze_timestamp date);
To analyze index-only tables, you must create
a separate chained-rows table for each index-only table created to
accommodate the primary key storage of index-only tables. Use the SQL
scripts DBMSIOTC.SQL and PRVTIOTC.PLB to define the DBMS_IOT package.
The tables themselves are built with a call to the BUILD_
CHAIN_ROWS_TABLE(owner VARCHAR2, iot_name VARCAHR2, chainrow_table_
name VARCHAR2) procedure. Execution of this procedure creates an
IOT_CHAINED_ ROWS table for an index-only table. The IOT_CHAINED_ROWS
table has the structure:
CREATE TABLE
iot_chained_rows (
owner_name
varchar2(30),
table_name
varchar2(30),
cluster_name varchar2(30),
partition_name
varchar2(30),
head_rowid
rowid,
timestamp
date,
test1
varchar2(6) <- This is the primary key
);
from the table being analyzed
See
Code Depot for Full Scripts
 |
This is an excerpt
from Mike Ault, bestselling author of "Oracle
10g Grid and Real Application Clusters".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
|
