There is a difference in how privileges are handled in anonymous PL/SQL blocks vs.
stored procedures, packages etc. I should have made it more clear that this was not
from an anonymous block, but from a package.
Ian
-----Original Message-----
Sent: Wednesday, May 22, 2002 2:38 AM
To: Multiple recipients of list ORACLE-L
mcdonac@cust9> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.0.1.3.0 -
Production
PL/SQL Release 9.0.1.3.0 - Production
CORE 9.0.1.2.0 Production
TNS for Solaris: Version 9.0.1.3.0 - Production
NLSRTL Version 9.0.1.3.0 - Production
mcdonac@cust9> select * from session_roles;
ROLE
------------------------------
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
JAVA_ADMIN
JAVA_DEPLOY
9 rows selected.
mcdonac@cust9> variable a1 number
mcdonac@cust9> variable a2 number
mcdonac@cust9> variable a3 number
mcdonac@cust9> variable a4 number
mcdonac@cust9> variable a5 number
mcdonac@cust9> variable a6 number
mcdonac@cust9> variable a7 number
mcdonac@cust9> begin
2
sys.dbms_space.unused_space('SYS','PARTLOB$','TABLE',
3 :a1, :a2, :a3, :a4, :a5, :a6, :a7 );
4 end;
5 /
PL/SQL procedure successfully completed.
mcdonac@cust9> set role none;
Role set.
mcdonac@cust9> begin
2
sys.dbms_space.unused_space('SYS','PARTLOB$','TABLE',
3 :a1, :a2, :a3, :a4, :a5, :a6, :a7 );
4 end;
5 /
begin
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SPACE", line 59
ORA-06512: at line 2
mcdonac@cust9> set role DBA;
Role set.
mcdonac@cust9> begin
2
sys.dbms_space.unused_space('SYS','PARTLOB$','TABLE',
3 :a1, :a2, :a3, :a4, :a5, :a6, :a7 );
4 end;
5 /
PL/SQL procedure successfully completed.
hth
Connor
--- "MacGregor, Ian A." <[EMAIL PROTECTED]>
wrote: > I am in the process of moving a small
database to
> Oracle 9.0.1.3. I have installed my own space
> management spaces. It measures growth of objects
> and tablespace usage and works well in Oracle 8i.
> The package does not work well with the 9i SYS
> tables; it returns "table or view does not exist",
> when calling dbms_space.unused_space. I'm not sure
> if this is for all SYS tables or just a few.
>
> The owner of the package has select any table,
> select any dictionary, and I even tried the "under
> any view" privilege, but no luck. The select any
> table privilege, N.B., this is a privilege not a
> role, which obviously must be judiciously given,
> allowed, one running Oracle 8i to write packages
> referencing SYS objects without having to do
> individual grants on the tables. It is still doing
> so for non-SYS tables, the calls to dbms_space work
> fine for them. Any idea on the SYS tables? Must
> they be granted individually?
>
>
> Ian MacGregor
> Stanford Linear Accelerator Center
> [EMAIL PROTECTED]
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: MacGregor, Ian A.
> INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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).
=====
Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net
"Some days you're the pigeon, some days you're the statue"
__________________________________________________
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: MacGregor, Ian A.
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
