Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Oracle 9i and Tables Owned by SYS

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


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US