471,108 Members | 1,264 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,108 software developers and data experts.

Run SQL Script under SYS for other Schema

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.
Jul 19 '05 #1
4 8628
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

Jul 19 '05 #2
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/
Jul 19 '05 #3
> 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.
Jul 19 '05 #4
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).
Jul 19 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by wishkid via DBMonster.com | last post: by
6 posts views Thread by Ollie Riches | last post: by
3 posts views Thread by nednieuws | charles | last post: by
6 posts views Thread by bikkaran | last post: by
4 posts views Thread by Jeff Lambert | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.