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
