Groovy DBA

       

 SELECT     r.rolname AS role_name,     p.proname AS procedure_name,     n.nspname AS schema_name,     pg_get_function_identity_arguments(p.oid) AS arguments FROM     pg_proc p JOIN     pg_namespace n ON n.oid = p.pronamespace JOIN     pg_roles r ON has_function_privilege(r.rolname, p.oid, 'EXECUTE') ORDER BY     procedure_name, role_name;

 -- Replace 'schema_name', 'procedure_name', and argument types as needed SELECT     r.rolname AS role_name,     p.proname AS procedure_name,     n.nspname AS schema_name,     pg_get_function_identity_arguments(p.oid) AS arguments,     has_function_privilege(r.rolname, p.oid, 'EXECUTE') AS can_execute FROM     pg_proc p JOIN     pg_namespace n ON n.oid = p.pronamespace JOIN     pg_roles r ON has_function_privilege(r.rolname, p.oid, 'EXECUTE') WHERE     n.nspname = 'public'  -- schema name     AND p.proname = 'my_procedure'  -- procedure name     -- Optional: match argument types if overloaded     -- AND pg_get_function_identity_arguments(p.oid) = 'integer, text' ORDER BY     role_name; How it works: pg_proc — stores all functions and procedures. pg_namespace — stores schema names. pg_roles — stores all roles (users/groups). has_function_priv...

 Safe SQL to Cleanup SYSAUX Advisor Data SQL> SET SERVEROUTPUT ON DECLARE     CURSOR c_tasks IS         SELECT task_name         FROM   dba_advisor_log  -- Use user_advisor_log if you don't have DBA privileges         WHERE  owner = USER;    -- Restrict to current schema BEGIN     FOR rec IN c_tasks LOOP         BEGIN             DBMS_ADVISOR.DELETE_TASK(rec.task_name);             DBMS_OUTPUT.PUT_LINE('Deleted task: ' || rec.task_name);         EXCEPTION             WHEN OTHERS THEN                 DBMS_OUTPUT.PUT_LINE('Failed to delete task: ' || rec.task_name ||                                      ' - ' || ...

 set linesize 200 COLUMN occupant_name FORMAT A30 COLUMN schema_name FORMAT A20 COLUMN space_usage_mb FORMAT 999,999,999 SELECT occupant_name,        schema_name,        space_usage_kbytes / 1024 AS space_usage_mb,        move_procedure FROM   v$sysaux_occupants ORDER  BY space_usage_kbytes DESC; SM/ADVISOR                     SYS                          12,796

SELECT distinct information_schema . role_table_grants . grantee , information_schema . role_table_grants . privilege_type , information_schema . table_privileges . table_name FROM information_schema . role_table_grants , information_schema . table_privileges WHERE information_schema . role_table_grants . grantee = 'eisuser' and information_schema . role_table_grants . grantee = information_schema . table_privileges . grantee ;