Groovy DBA

  DROP SCHEMA " coupons_keerthi " cascade ;

  SELECT pg_class.relname AS table_name, pg_locks.locktype, pg_locks.mode, pg_locks.granted, pg_stat_activity.usename, pg_stat_activity.query, pg_stat_activity.query_start FROM pg_locks JOIN pg_class ON pg_locks.relation = pg_class.oid JOIN pg_stat_activity ON pg_locks.pid = pg_stat_activity.pid WHERE pg_class.relname = 'your_table_name' ; -- Replace 'your_table_name'

  SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocked_activity.query AS blocked_statement, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocking_activity.query AS blocking_statement FROM pg_catalog.pg_locks AS blocked_locks JOIN pg_catalog.pg_stat_activity AS blocked_activity ON blocked_activity.pid = blocked_locks.pid JOIN pg_catalog.pg_locks AS blocking_locks ON blocking_locks.transactionid = blocked_locks.transactionid AND blocking_locks.pid != blocked_locks.pid JOIN pg_catalog.pg_stat_activity AS blocking_activity ON blocking_activity.pid = blocking_locks.pid WHERE NOT blocked_locks.granted;

  SELECT pg_class.relname AS table_name, pg_locks.locktype, pg_locks.mode, pg_locks.granted, pg_locks.pid FROM pg_locks JOIN pg_class ON pg_locks.relation = pg_class.oid WHERE pg_locks.relation IS NOT NULL ; -- Filter out non-relation locks for table names

  DO $$ DECLARE     v_schema text := 'cpnadmin';          -- << change schema name here     v_table  text := 'coupons_active';    -- << change table name here     v_user   text := 'cpnuser';           -- << change target user here     r record; BEGIN     RAISE NOTICE 'Granting privileges for all partitions of %.%', v_schema, v_table;     -- Loop through all child partitions of the given parent table     FOR r IN         SELECT child.relname AS partition_name         FROM pg_inherits i         JOIN pg_class parent ON i.inhparent = parent.oid         JOIN pg_class child  ON i.inhrelid = child.oid ...

  The biggest portion of a database's size comes from the datafiles.  To find out how many megabytes are allocated to ALL datafiles: select sum(bytes)/1024/1024 "Meg" from dba_data_files; To get the size of all TEMP files: select nvl(sum(bytes),0)/1024/1024 "Meg" from dba_temp_files; To get the size of the on-line redo-logs: select sum(bytes)/1024/1024 "Meg" from sys.v_$log; Putting it all together into a single query: col total_size format 999999999999999 select a.data_size+b.temp_size+c.redo_size "total_size" from ( select sum(bytes) data_size          from dba_data_files ) a,      ( select nvl(sum(bytes),0) temp_size          from dba_temp_files ) b,      ( select sum(bytes) redo_size          from sys.v_$log ) c; The size of the database is the space the files physically consume on disk. You can find this with: select sum(bytes)/1024/1024 size_in_mb from dba_data_files;...

Formatted Uptime  To display uptime in a more readable format (e.g., truncated to seconds): SELECT  date_trunc( 'second' ,  current_timestamp  - pg_postmaster_start_time())  AS  uptime; You can calculate the server uptime by querying the  pg_postmaster_start_time()  function, which returns the server's start time. Basic Query  To get the uptime directly: SELECT current_timestamp  - pg_postmaster_start_time()  AS  uptime;