Groovy DBA

 -- 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 ;

 SHOW VARIABLES LIKE 'wait_timeout'; -- Timeout for interactive connections SHOW VARIABLES LIKE 'interactive_timeout'; -- Timeout for initial connection attempts SHOW VARIABLES LIKE 'connect_timeout'; SHOW VARIABLES LIKE '%timeout%' ; connect_timeout 60 delayed_insert_timeout 300 have_statement_timeout YES innodb_flush_log_at_timeout 1.000000 innodb_lock_wait_timeout 50 innodb_rollback_on_timeout OFF interactive_timeout 28800 lock_wait_timeout 31536000 net_read_timeout 30 net_write_timeout 60 replica_net_timeout 30 rpl_stop_replica_timeout 31536000 rpl_stop_slave_timeout 31536000 slave_net_timeout 30 ssl_session_cache_timeout 300 thread_pool_idle_timeout 60 wait_timeout 28800 The most common timeout-related variables are: wait_timeout  – Timeout for non-interactive connections (in seconds). interactive_timeout  – Timeout for interactive sessions (e.g., MySQL CLI). connect_timeout  – Timeout for initial connection attempts. net_read_timeout  – Timeout f...

 To determine or check timeout settings in PostgreSQL, you can use the SHOW command for individual parameters or query the pg_settings system view for a comprehensive list.  1. View Current Timeout Settings  You can check the current value for any specific timeout parameter in your active session using the following SQL commands:  Query Timeout: SHOW statement_timeout; (Stops queries that run too long) Lock Timeout: SHOW lock_timeout; (Stops a command if it can't acquire a lock quickly enough) Idle Transaction Timeout: SHOW idle_in_transaction_session_timeout; (Closes sessions left open but inactive within a transaction) Idle Session Timeout: SHOW idle_session_timeout; (Closes sessions that are completely inactive) Transaction Timeout (v17+): SHOW transaction_timeout; (Sets a limit for the entire duration of a transaction)  2. Search All Timeout Parameters To find every setting that contains "timeout" in its name and see its current value, use the pg_settings vi...