Redshift useful administration SQL

manager Wed, 08/12/2020 - 14:01

Redshift Useful Queries

/* Show tables and owners */

SELECT

    u.usename,

    s.schemaname,

    has_schema_privilege(u.usename,s.schemaname,'create') AS user_has_select_permission,

    has_schema_privilege(u.usename,s.schemaname,'usage') AS user_has_usage_permission

FROM

    pg_user u

CROSS JOIN

    (SELECT DISTINCT schemaname FROM pg_tables) s

WHERE

   s.schemaname = 'namexxx';



/* List all Users */

select * from pg_shadw;

​

/* List all non-builtin schemas */

select * from pg_catalog.pg_namespace where nspowner != '1';

​

/* List recent queries from particular user (myuser) */

SELECT * from ORDERS where query like '%myuser%'

/* List currently executing queries */

SELECT * from STV_INFLIGHT;

​

select has_table_privilege('useraccountxxx', 'schema.orders', 'insert');

​

/* kill session, Redshift ends session and rolls back all transactions that are associated with it. */

select pg_terminate_backend(12855)

​

/* Show locked Tables */

SELECT

  current_time,

  c.relname,

  l.database,

  l.transaction,

  l.pid,

  a.usename,

  l.mode,

  l.granted

FROM pg_locks l

JOIN pg_catalog.pg_class c ON c.oid = l.relation

JOIN pg_catalog.pg_stat_activity a ON a.procpid = l.pid

WHERE l.pid <> pg_backend_pid();

​

/* show locks oldest first */

select

  table_id,

  last_update,

  last_commit,

  lock_owner_pid,

  lock_status

from stv_locks

order by last_update asc

​

​

/* Active sessions */

SELECT

    procpid,

    datname,

    usename,

    current_query,

    query_start

FROM

    pg_catalog.pg_stat_activity;



/* blocking queries */

select

  b.*,

  w.pid as blocked_pid,

  w.txn_owner as blocked_owner,

  DATEDIFF(minutes, b.txn_start, getdate()) as blocked_for_mints

from SVV_TRANSACTIONS b inner join SVV_TRANSACTIONS w

    on b.txn_db = w.txn_db and b.relation = w.relation

where b.granted='t' and w.granted = 'f'

  and DATEDIFF(minutes, b.txn_start, getdate()) > 5

order by txn_start, b.pid;

​

/* TABLE OWNERSHIP */

SELECT n.nspname AS schema_name

 , pg_get_userbyid(c.relowner) AS table_owner

 , c.relname AS table_name

 , CASE WHEN c.relkind = 'v' THEN 'view' ELSE 'table' END

   AS table_type

 , d.description AS table_description

 FROM pg_class As c

 LEFT JOIN pg_namespace n ON n.oid = c.relnamespace

 LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace

 LEFT JOIN pg_description As d

      ON (d.objoid = c.oid AND d.objsubid = 0)

 WHERE c.relkind IN('r', 'v')

ORDER BY n.nspname, c.relname ;