Re: Renaming or Deleting a Column

mharper_at_well.sf.ca.us (Michael J. Harper) writes:
>Is there any way to change the name of a column in a table on the fly?
>Or to delete a column from a table altogether, again on the fly?
>
>I really need to change the name of a column because I need to create
>a new column with the same name as the "old" column. So, the old column
>needs either to have its name changed or to be deleted altogether.
>
>I welcome any possible comments and solutions, however covert they may be!
>
>Michael J. Harper
>mharper_at_well.sf.ca.us

The following is NOT supported by Oracle Corp., nor do I accept responsibility for your actions in doing this. However, it does work for me under V6, and does have some interesting side effects in the data dictionary cache. The information below can be determined by inspection of the CATALOG.BSQ file used to initialize the database, and the Version 6 Database Dictionary views in $ORACLE_HOME/rdbms/admin/*.sql.

The OWNER and OBJECT_NAME form a Unique key into a table called OBJ$ which is owned by the user SYS. OBJ$ has a field OBJ# which is a surrogate key upon which MANY but not all other references to the object depend. (Exceptions include views and synonyms.) The OWNER's NAME of an object is stored in SYS.USER$.NAME referenced by SYS.USER$.USER#=SYS.OBJ$.OWNER# .

The names of columns are stored in SYS.COL$ and are referenced via the Object number defined above. So - information needed to rename a column:

  OWNER_name
  TABLE_name
  COLUMN_name
  NEW_column_name

The style given here is as subqueries. In the real world these are transformed into joins, and indeed the Oracle optimizer in most recent versions will put these into join form before execution.

Procedurally here is what you do:

• Find the USER# for the OWNER.

    SELECT user#
    FROM sys.user$
    WHERE name='&user_name'

• Find the OBJ# for the OWNER/TABLE combination.

    SELECT obj#, name
    FROM sys.obj$
    WHERE name='&table_name'

      AND owner#=(
          SELECT user#
          FROM sys.user$
       	  WHERE name='&user_name'
          )

• Find the COL# for the OWNER/TABLE/COLUMN combination.

    SELECT obj#, col#, name
    FROM sys.col$
    WHERE name='&column_name'

      AND obj#=(
	  SELECT obj#
	  FROM sys.obj$
	  WHERE name='&table_name'
	    AND owner#=(
		SELECT user#
		FROM sys.user$
		WHERE name='&user_name'
		)
	  )

• Now, update the column name IF AND ONLY IF the above query returned ONE and ONLY ONE record. Record the OBJ# and COL# values returned above.

    UPDATE sys.col$
    SET name='&new_column_name'
    WHERE obj#=9999 AND col#=9999

    Double check your entire actions now. Make sure you made no     typographical errors. Are you sure? Absolutely sure? Okay.

    COMMIT

• You have now renamed a column. Unfortunately the data dictionary cache probably has a record of the column in it. The only reliable non-system dependent way known to mortal man outside of Oracle Corp to flush the data dictionary cache is to shutdown and restart the database. (Other solutions may include referencing a gazillion other tables to try to flush things through, but now you have to know what value is in dc_columns.)

 SYS.OBJ$
 Name Null? Type

• -------- ---- OBJ# NOT NULL NUMBER Object Number OWNER# NOT NULL NUMBER USER# in USER$ NAME NOT NULL CHAR(30) Object Name TYPE NOT NULL NUMBER Object Type CTIME NOT NULL DATE Creation Date/Time MTIME DATE Modify Date/Time

 SYS.USER$
 Name Null? Type

• -------- ---- USER# NOT NULL NUMBER User Number NAME NOT NULL CHAR(30) User name PASSWORD CHAR(30) User encrypted PW [remainder deleted for brevity]

 SYS.COL$
 Name Null? Type

• -------- ---- OBJ# NOT NULL NUMBER COL# NOT NULL NUMBER NAME NOT NULL CHAR(30) [remainder deleted for brevity]

-- 
Andrew Finkenstadt, Vista-Chrome, Inc., Homes & Land Publishing Corporation
GEnie Unix RoundTable Manager, andy_at_vistachrome.com, andy_at_genie.geis.com.
  Send mail to ora-request_at_vistachrome.com to join Unix, CASE, and 
  Desktop Oracle RDBMS Database discussions.