| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: sys corrupted in warehouse, sev1 tar open - resolved
Here's some login trigger code (cloned from Ixora with a few modifications), similar to what Bruce mentions.
CREATE TABLE system.default_schema (
user_name varchar2(30),
schema_name varchar2(30)
)
TABLESPACE tools ;
ALTER TABLE system.default_schema
ADD CONSTRAINT pk_default_schema
PRIMARY KEY (user_name)
USING INDEX TABLESPACE tools ;
CREATE OR REPLACE trigger system.set_current_schema after logon on
database
DECLARE
default_schema varchar2(30); nodata boolean := FALSE;
BEGIN BEGIN SELECT schema_name into default_schema FROM system.default_schema where user_name = user;
EXCEPTION
WHEN NO_DATA_FOUND then
nodata := TRUE;
execute immediate 'alter session set current_schema = ' ||default_schema ;
END IF ;
END ;
/
"Reardon, Bruce (CALBBAY)" wrote:
>
> Jack,
> Something you may find useful if you're not already aware is the schemaname field in v$session.
> Compare this to username and this may help determine if set current_schema is being used.
>
> We use the set current_schema in a login trigger, though the trigger has smarts in it to only do it for application users and not for schema owners, sys, system etc.
>
> HTH,
> Bruce Reardon
>
> -----Original Message-----
> Sent: Thursday, 13 June 2002 4:20
> To: Multiple recipients of list ORACLE-L
>
> It was the "alter system set current_schema=x"
> statement after all.
>
> I am sure that this raises some interesting questions,
> if only I had time to dwell on it. Just now cleaning
> up all the broken indexes from the loads that abended
> when the db went down.
>
> I love this job. Where else do you get to play at
> work?
>
> jack
>
> > --- Jack Silvey <jack_silvey_at_yahoo.com> wrote: >
> > All,
> > >
> > > thanks for the input. Looks like someone
> > implemented
> > > a
> > > login trigger. haven't seen the code yet, but I
> > > would
> > > venture a guess he used the unsupported "alter
> > > system
> > > set current_schema=x".
> > >
> > > sometimes you live and learn, sometimes you just
> > > live!
> > >
> > > thx,
> > >
> > > jack silvey
> > >
> > >
> > > --- Hately Mike <Mike.Hately_at_churchill.com> wrote:
> > > > I don't hold out much hope here Jack.
> > > > It sounds like data dictionary corruption; maybe
> > > > somewhere round user$(?).
> > > > That's not to say the situation's irretrievable;
> > > > I've seen OTS fix some bad
> > > > situations in my time but I'm not sure that I'd
> > > want
> > > > to keep the database
> > > > even if Oracle Support can fix the problem.
> > > >
> > > > Regards,
> > > > Mike
> > > >
> > > > -----Original Message-----
> > > > Sent: 12 June 2002 14:23
> > > > To: Multiple recipients of list ORACLE-L
> > > >
> > > >
> > > > Listers,
> > > >
> > > > Our warehouse now has a split personality and we
> > > > have
> > > > a sev1 open on it. Suspect recovery is in the
> > > cards,
> > > > but want to avoid if possible.
> > > >
> > > > Yesterday, users unable to get to their own
> > > > functions.
> > > > Soon after, RMAN cannot find package
> > > > dbms_backup_restore, even though it exists under
> > > > sys.
> > > > Oncall ran the sql script to recreate - and the
> > > > pacakge was recreated under a schema called
> > > > dma_rbate2. RMAN now finds the package under
> > > > dma_rbate2, although it is invalid. Drop the
> > > package
> > > > under dma_rbate2, and now RMAN cannot find the
> > > > package
> > > > any longer, although it still exists under sys.
> > > >
> > > > Logged in as sys. Tried to desc
> > > dbms_backup_restore
> > > > -
> > > > no luck. Tried to desc sys.dbms_backup_restore -
> > > > success.
> > > >
> > > > Analyst reccomends running catalog.sql. Oncall
> > > does
> > > > so, and it creates as many packages as it is
> > able
> > > > under dma_rbate2.
> > > >
> > > > I get up this AM and can't login, because the
> > > > sessions
> > > > can't find the package
> > > > dma_rbate2.dbms_application_info.
> > > >
> > > > Anyone? Buhler? Buhler?
> > > >
> > > > thx,
> > > >
> > > > jack
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Reardon, Bruce (CALBBAY)
> INET: Bruce.Reardon_at_comalco.riotinto.com.au
>
> 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: Suzy Vordos INET: lvordos_at_qwest.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).Received on Thu Jun 20 2002 - 17:50:05 CDT
 |
 |