Oracle-L: RE: Set unused column to normal

From: <Jared.Still_at_radisys.com>
Date: Fri, 23 Aug 2002 11:45:27 -0800
Message-ID: <F001.004BE95D.20020823114527@fatcity.com>

Not only is it not supported, it has been know on occasion to trash a database.

I tried it years ago, several procedures could not be recompiled after doing so.

An instructor from one Oracle class said he blew away a database by doing this.

Caveat emptor.

Jared

"Sinardy Xing" <SinardyXing_at_bkgcomsvc.com> Sent by: root_at_fatcity.com
08/22/2002 07:53 PM
Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        RE: Set unused column to normal

Hi George,

This is the step:

0. SOP = Backup your DB before you make any changes

1a. What is your column name :_________________
1b. What is your column name :_________________

The order of your column that you drop is very important (I mean if they are in the same table)

2. Query dba_unused_col_tabs

                 Take note the information of those unused columns

3. Query col$(obj#, col#, name, intcol#, property)

Query tab$(obj#, cols, intcols, property)

                 Optional
                                 Query obj$(obj#, name, subname, status)

4. modify col$
                 If you see name = SYS_bla_bla_bla_bla, property = 32800 
then
                 set 
                                 name = your column_name 
                                 property = 0
                                 col# = max(col#) + 1

   repeat this step if you have more than 1 columns
                 the one your drop first should have smaller rowid compare

to the second one

5. modify tab$

                 select obj# from obj$ where name=your table name

                 select tab$  <- step3
                 where obj#=the one you get above

                 set cols = max_columns
                     intcols = max_columns
                     property = 0

This is not supported by Oracle make sure you know what you are doing. Check metalink
article by Mike Ault

Sinardy

-----Original Message-----
[mailto:George.Geordi_at_ci.austin.tx.us]
Sent: 22 August 2002 18:41
To: Sinardy Xing

Thank you very much for your reply. I did not drop them yet. How can I recover those..?

-----Original Message-----
Sent: Wednesday, August 21, 2002 9:18 PM To: George.Geordi_at_ci.austin.tx.us

Hi George,

Have you drop those columns that you mark unsued?

if yes then you cannot recover them

check your dba_unsued_col_tabs (count will tell you how many unsued columns
you have that we can recover)

Sinardy

-----Original Message-----
[mailto:George.Geordi_at_ci.austin.tx.us]
Sent: 21 August 2002 04:00
To: Sinardy Xing

Hi,

                 I need some help. I set some columns in my table to

unused. Is there
any way, can I make those back to normal? Thanks
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Sinardy Xing
INET: SinardyXing_at_bkgcomsvc.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Jared.Still_at_radisys.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------