Groovy DBA

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

 1. View Locks on a Specific Table  Use this query to see all active locks for a specific table by name: sql SELECT      locktype,      relation::regclass AS table_name,      mode,      granted,      pid  FROM pg_locks  WHERE relation = 'your_table_name'::regclass; granted: t means the process has the lock; f means it is waiting for it. mode: The type of lock (e.g., AccessShareLock for selects, AccessExclusiveLock for schema changes).    2. Identify "Who is Blocking Whom" To find which session is holding a lock and preventing others from proceeding, use the pg_blocking_pids function:  sql SELECT      pid,      usename,      pg_blocking_pids(pid) AS blocked_by,      query AS blocked_query FROM pg_stat_activity  WHERE cardinality(pg_blocking_pids(pid)) > 0; The blocked_by column will show the PID of the process ho...

Role and Group Memberships Since users often inherit permissions from groups (roles), use this to see what roles they belong to:     SELECT r.rolname as username,         ARRAY(SELECT b.rolname               FROM pg_catalog.pg_auth_members m               JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)               WHERE m.member = r.oid) as member_of FROM pg_catalog.pg_roles r WHERE r.rolname = 'edduser';  Understanding Privilege Abbreviations When using commands like \dp, you will see a string of letters like arwdDxt. These correspond to: r: SELECT (read) w: UPDATE (write) a: INSERT (append) d: DELETE D: TRUNCATE x: REFERENCES t: TRIGGER U: USAGE (for schemas/sequences) C: CREATE (for databases/schemas) c: CONNECT (for databases)