Run SQL Script under SYS for other Schema

[Jeff Lambert]

Using Oracle 8i on Win2k. Here's my problem:

I can connect with SYS and can select from any table from schema JEFFX
by prefixing tables with said schema name. eg: SELECT * FROM
JEFFX.PROJ;

However I want to run a "recompile all invalid objects" script on my
JEFFX user / schema. Namely, RDBMS\ADMIN\utl rp.sql. Running it under
SYS yields no errors. When I try to run it when connected as JEFFX, I
get errors such as:

PLS-00201: identifier 'OBJ$' must be declared

The user JEFFX has very limited rights indeed and that's how I want to
keep it. I want to be able to run the script under SYS but for the
JEFFX schema. I tried this command:

ALTER SESSION SET CURRENT_SCHEMA = JEFFX;

But it doesn't do much it seems? In fact it's like I'm back to using
the stripped down user, right? I'm sure there's a very simple
explanation to this. I'm also willing to add the required right to
this user provided it doesn't turn him into a full blown DBA.

Thanks in advance!

Jeff Lambert
Hypershell, Inc.

[Frank van Bortel]

Re: Run SQL Script under SYS for other Schema

Jeff Lambert wrote:
[color=blue]
> Using Oracle 8i on Win2k. Here's my problem:
>
> I can connect with SYS and can select from any table from schema JEFFX
> by prefixing tables with said schema name. eg: SELECT * FROM
> JEFFX.PROJ;
>
> However I want to run a "recompile all invalid objects" script on my
> JEFFX user / schema. Namely, RDBMS\ADMIN\utl rp.sql. Running it under
> SYS yields no errors. When I try to run it when connected as JEFFX, I
> get errors such as:
>
> PLS-00201: identifier 'OBJ$' must be declared
>
> The user JEFFX has very limited rights indeed and that's how I want to
> keep it. I want to be able to run the script under SYS but for the
> JEFFX schema. I tried this command:
>
> ALTER SESSION SET CURRENT_SCHEMA = JEFFX;
>
> But it doesn't do much it seems? In fact it's like I'm back to using
> the stripped down user, right? I'm sure there's a very simple
> explanation to this. I'm also willing to add the required right to
> this user provided it doesn't turn him into a full blown DBA.
>
> Thanks in advance!
>
> Jeff Lambert
> Hypershell, Inc.[/color]

dbms_utility.re compile_schema( 'JEFFX', false);
--

Regards,
Frank van Bortel

[Joel Garry]

Re: Run SQL Script under SYS for other Schema

jeffl@hypershel l.com (Jeff Lambert) wrote in message news:<f17e7766. 0401080750.283d fdb9@posting.go ogle.com>...[color=blue]
> Using Oracle 8i on Win2k. Here's my problem:
>
> I can connect with SYS and can select from any table from schema JEFFX
> by prefixing tables with said schema name. eg: SELECT * FROM
> JEFFX.PROJ;
>
> However I want to run a "recompile all invalid objects" script on my
> JEFFX user / schema. Namely, RDBMS\ADMIN\utl rp.sql. Running it under
> SYS yields no errors. When I try to run it when connected as JEFFX, I
> get errors such as:
>
> PLS-00201: identifier 'OBJ$' must be declared
>
> The user JEFFX has very limited rights indeed and that's how I want to
> keep it. I want to be able to run the script under SYS but for the
> JEFFX schema. I tried this command:
>
> ALTER SESSION SET CURRENT_SCHEMA = JEFFX;
>
> But it doesn't do much it seems? In fact it's like I'm back to using
> the stripped down user, right? I'm sure there's a very simple
> explanation to this. I'm also willing to add the required right to
> this user provided it doesn't turn him into a full blown DBA.
>
> Thanks in advance!
>
> Jeff Lambert
> Hypershell, Inc.[/color]

Note this group has been superceded by comp.databases. oracle.* (where
* is server for this type of question).

http://download-west.oracle.com/docs/cd/A87860_01/doc/index.htm

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=9q9fg30bbb%40drn.newsguy.com

jg
--
@home.com is bogus.

http://www.automobilemag.com/auto_shows/04det_chevcorvette/

[Jeff Lambert]

Re: Run SQL Script under SYS for other Schema

> Note this group has been superceded by comp.databases. oracle.* (where[color=blue]
> * is server for this type of question).[/color]

Allright see I always thought the server NG was for actual "physical"
server maintenance. I'm sorry I'm just a C++ programmer trying to play
the role of a DBA. I'll keep it in mind for future posts.
[color=blue]
> http://download-west.oracle.com/docs.../doc/index.htm
>
> http://groups.google.com/groups?hl=e...rn.newsguy.com[/color]

Perfect! Thank you.

[Hans Forbrich]

Re: Run SQL Script under SYS for other Schema

Jeff Lambert wrote:[color=blue]
>[color=green]
> > Note this group has been superceded by comp.databases. oracle.* (where
> > * is server for this type of question).[/color]
>
> Allright see I always thought the server NG was for actual "physical"
> server maintenance. I'm sorry I'm just a C++ programmer trying to play
> the role of a DBA. I'll keep it in mind for future posts.[/color]

In those cases, you want to head for comp.databases. oracle.misc Since
cdo itself was voted out in the mid 90's, some ISPs don't carry it and
some of our senior people don't have direct access.
[color=blue]
>[color=green]
> > http://download-west.oracle.com/docs.../doc/index.htm
> >
> > http://groups.google.com/groups?hl=e...rn.newsguy.com[/color]
>
> Perfect! Thank you.[/color]

One minor additional point: SYS is superuser, SYSTEM is 'master' DBA.
If at all possible avoid using SYS (simply because a slip of the fingers
could destroy your database).