Re: Set unused column to normal

Rachel, can you give me the email address of your user? I'd like to send him the following procedure for renaming users, I'm sure that both of you will love it:

1. Change the user name: update sys.user$ set name = 'NewUserName' where name='OldUserName';
2. Shutdown/startup the database
3. Reset the renamed users password: alter user NewUserName identified by NewPassword;
4. Check for synonyms pointing back the old user name: select owner, synonym_name,table_name,db_link from dba_synonyms where table_owner = 'OldUserName';
5. Drop and recreate these synonyms using NewUserName instead of OldUserName for the table_owner.

Wouldn't you, as a DBA, love a user coming to you with a suggestion like the one above? Please no threats to me or my pets.

On 2002.08.23 21:28 Rachel Carmichael wrote:
> today our data warehouse modeler came to me and said he had misnamed a
> column and how could he change it....
>
> I said, since we are on 9.2, it's a simple alter table command.
>
> He said "oh I found something on the web about a way to go into the
> data dictionary and you change a system table....."
>
> after I stopped screaming at him, he agreed that *I* would be the DBA
> and handle all database work and he would be the modeler. It helped
> that I had my foot on his throat at the time.
>
> He said he bookmarked that page and would send me the url. And then I'm
> gonna beat up the person who posted THAT.
>
> sigh.... maybe I should add that to the Top 20 Mistakes?
>
>
> --- Joe Testa <jtesta_at_dmc-it.com> wrote:
> > besides its just being stupid, newbies/kiddies, even the experienced
> > DBAs dont do this stuff on the sand database.
> >
> > joe
> >
> >
> > Jared.Still_at_radisys.com wrote:
> >
> > >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: Joe Testa
> > INET: jtesta_at_dmc-it.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).
>
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Finance - Get real-time stock quotes
> http://finance.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Rachel Carmichael
> INET: wisernet100_at_yahoo.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).
>

-- 
Mladen Gogala
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mladen Gogala
  INET: mgogala_at_adelphia.net

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).