By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,502 Members | 1,878 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,502 IT Pros & Developers. It's quick & easy.

Run SQL Script under SYS for other Schema

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
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

P: n/a
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

P: n/a
> 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

P: n/a
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.