FAQ
Hey all,

I'm looking to regularly purge the dba_recyclebin on a 10.1.0.5.0 DB on AIX.
For various reasons, I'd like this in PL/SQL. But since the command
requires SYSDBA, I'm not able to find any documentation on the availability
of that priv in a procedure (i.e. I know roles are not active in a
procedure, but SYSDBA isn't a role). I've done something similar to this:

CONNECT / AS SYSDBA

GRANT SYSDBA TO my_dba;
CREATE OR REPLACE PROCEDURE my_dba.purge_dba_recycle_bin AS

v_statement VARCHAR2(100) := 'PURGE DBA_RECYCLEBIN';
BEGIN

EXECUTE IMMEDIATE v_statement;
END;

/

But when I try and execute the procedure as a non-SYSDBA user, I of course
get the ORA-1031 insufficient privs error. Also, the MY_DBA user was setup
specifically to not allow logins.

Thinking that the password file privs are only active for logins, I created
the proc under SYS (but don't tell anyone), and I get the same error on
executing it. I'm unable to find confirmation on my theory via MetaLink,
Tahiti, nor Google.

I can live without the procedure, but the "why" is now bugging me to no end.

Thoughts anyone?
TIA!

Rich

Search Discussions

Discussion Posts

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 1 of 4 | next ›
Discussion Overview
grouporacle-l @
categoriesoracle
postedDec 6, '07 at 9:09p
activeDec 7, '07 at 6:50p
posts4
users2
websiteoracle.com

2 users in discussion

Rich Jesse: 3 posts Yasin Baskan: 1 post

People

Translate

site design / logo © 2022 Grokbase