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\utlrp.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]

Jeff Lambert wrote:

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\utlrp.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.

dbms_utility.recompile_schema('JEFFX', false);
--

Regards,
Frank van Bortel

[Joel Garry]

je***@hypershell.com (Jeff Lambert) wrote in message news:<f1**************************@posting.google. com>...

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\utlrp.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.

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

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

http://groups.google.com/groups?hl=e...rn.newsguy.com

jg
--
@home.com is bogus.
http://www.automobilemag.com/auto_sh..._chevcorvette/

[Jeff Lambert]

> Note this group has been superceded by comp.databases.oracle.* (where

* is server for this type of question).
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.
http://download-west.oracle.com/docs.../doc/index.htm

http://groups.google.com/groups?hl=e...rn.newsguy.com

Perfect! Thank you.

[Hans Forbrich]

Jeff Lambert wrote:

Note this group has been superceded by comp.databases.oracle.* (where
* is server for this type of question).
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.

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.

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

http://groups.google.com/groups?hl=e...rn.newsguy.com

Perfect! Thank you.

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