Re: Columns: Dropping, Altering???

In article <3f51ak$84c_at_usenet.ucs.indiana.edu> tepple_at_silver.ucs.indiana.edu (Todd Matthew Epple) writes:

> I'm a complete Newbie, new to Oracle and to SQL in general. Despite the
> 50lbs + of books which came with Ora 7 for Netware, it isn't too difficult.
> The concepts book really helps.
>
> My question is how do you drop, rename, or shrink the size of columns in
> a table? The only thing I found was the ability to ALTER TABLE your way to
> shrinked columns, as long as the column is all NULLs. These seem like
> everyday commands, and even if they aren't supported that fact should at
> least be pointed out in the manuals.
>
> I have the OraFAQ and notice that (sec 2.18) a column can not be renamed
> using normal SQL...the only way is direct manipulation of SYS data dictionary
> files. How could this be true and why?

Disclaimer: I do not speak for Oracle; this is only my personal

            opinion based on my knowledge...

You can rename a column by updating the NAME column in the data dictionary table COL$. Once you bounce the database, the new column name will be in effect. I do find a potential problem: PL/SQL seems to have a private copy of the data dictionary. Once you changed a column name this way, you might encounter ORA-0600 type errors if you execute PL/SQL with references to the renamed column.

The official supported method, I think, is do a "CREATE TABLE ... AS SELECT..." I know it's a pain for a giga-byte table.

--
"And this is all I have to say about that..."   - F. Gump
      ___
     (o o)
+-oo0-\_/-0oo---------------------------------------------------------------+
|  Alvin W. Law ..... Oracle Corporation ....... Email: alaw_at_us.oracle.com  |
+---------------------------------------------------------------------------+

ORA-03113: end-of-file on communication channel