Oracle-L: RE: Oracle 9i and Tables Owned by SYS

From: MacGregor, Ian A. <ian_at_SLAC.Stanford.EDU>
Date: Wed, 22 May 2002 08:48:40 -0800
Message-ID: <F001.00467C68.20020522084840@fatcity.com>

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_at_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_at_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_at_cust9> variable a1 number
mcdonac_at_cust9> variable a2 number
mcdonac_at_cust9> variable a3 number
mcdonac_at_cust9> variable a4 number
mcdonac_at_cust9> variable a5 number
mcdonac_at_cust9> variable a6 number
mcdonac_at_cust9> variable a7 number
mcdonac_at_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_at_cust9> set role none;

Role set.

mcdonac_at_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_at_cust9> set role DBA;

Role set.

mcdonac_at_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." <ian_at_SLAC.Stanford.EDU> 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
> ian_at_SLAC.Stanford.edu
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: MacGregor, Ian A.
> INET: ian_at_SLAC.Stanford.EDU
>
> 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: ListGuru_at_fatcity.com (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: hamcdc_at_yahoo.co.uk

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: ListGuru_at_fatcity.com (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: ian_at_SLAC.Stanford.EDU

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: ListGuru_at_fatcity.com (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).

Received on Wed May 22 2002 - 11:48:40 CDT