Re: Drop Column

From: <MTun1999_at_gmx.net>
Date: Sat, 06 Nov 1999 12:06:54 GMT
Message-ID: <38241AB6.8C259542@gmx.net>

All Documentation can be found at http://technet.oracle.com/ ... You must be a Member of Oracle technology Network, but Membership is free !!!! Check it out ;-))

Michael

From the Oracle 8i Documentation:
drop_column_clause: lets you free space in the database by dropping columns you no longer need, or by marking them to be dropped at a future time when the demand on system resources is less.

Restrictions on the drop_column_clause:

                                    Each of the parts of this clause can
be specified only once in the
                                    statement and cannot be mixed with
any other ALTER TABLE
                                    clauses. For example, the following

statements are not allowed:

                                    ALTER TABLE t1 DROP COLUMN f1 DROP

(f2);

                                    ALTER TABLE t1 DROP COLUMN f1 SET

UNUSED (f2);

                                    ALTER TABLE t1 DROP (f1) ADD (f2

NUMBER);

                                    ALTER TABLE t1 SET UNUSED (f3)

                                    ADD (CONSTRAINT ck1 CHECK (f2 > 0));



                                    You can drop an object type column
only as an entity. Dropping an
                                    attribute from an object type column

is not allowed.

                                    If you drop a nested table column,

its storage table is removed.

                                    If you drop a LOB column, the LOB
data and its corresponding
                                    LOB index segment are removed.



                                    If you drop a BFILE column, only the
locators stored in that column
                                    are removed, not the files

referenced by the locators.

                                    You can drop a column from an
index-organized table only if it is not
                                    a primary key column. The primary
key constraint of an
                                    index-organized table can never be
dropped, so you cannot drop a
                                    primary key column even if you have
specified CASCADE
                                    CONSTRAINTS.



                                    You can export tables with dropped
or unused columns. However,
                                    you can import a table only if all
the columns specified in the export
                                    files are present in the table (that
is, none of those columns has been
                                    dropped or marked unused).

Otherwise, Oracle returns an error.

                                    You cannot drop a column on which a

domain index has been built.

                               You cannot use this clause to drop:

                                    A pseudocolumn, clustered column, or
partitioning column. (You
                                    can drop nonpartitioning columns
from a partitioned table if all the
                                    tablespaces where the partitions
were created are online and in
                                    read-write mode.)

                                    A column from a nested table, an
object table, or a table owned by
                                    SYS

David Pomphrey wrote:

>
> From David P,
>
> Hi John,
> (prepare to be ?frustrated?) - unless you are running
> Oracle 8i you can't drop a column. If you are using Oracle 8i, sorry
> but I can't help - I only have access to the 8.0.5. Enterprise Edition
> documentation (not the 8.1.5. series i.e. Oracle 8i)
>
>
>
>
>
> ==============================================
>
> John Duran wrote:
>
>> What is the syntax for dropping a Column from a table.
>>
>> the ref mentions add columns, and modifing columns.
>>
>> the standard SQL command:
>>
>> ALTER TABLE MYTABLE
>> DROP MYCOLUMN
>>
>> Doesn't seem to work
>>
>> Sent via Deja.com http://www.deja.com/
>> Before you buy.
>
> ===============================================
Received on Sat Nov 06 1999 - 06:06:54 CST