Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Find a list of active Temp tables with Size and User information

In this post, I am sharing a script to find a list of Temp tables with their size and user information in PostgreSQL.
We all aware of the temp tables and many times developers are creating temp table for their ad-hoc testing purpose.
But unfortunately sometimes, they forget to drop unused temp tables, and temp tables keep active in those idle connections. It also requires unnecessary utilization of disk space.
So now, DBAs are responsible for identifying unused temp tables and then inform to developers so that they can drop their unused temp tables.
 n.nspname as SchemaName
 ,c.relname as RelationName
 ,CASE c.relkind
 WHEN 'r' THEN 'table'
 WHEN 'v' THEN 'view'
 WHEN 'i' THEN 'index'
 WHEN 'S' THEN 'sequence'
 WHEN 's' THEN 'special'
 END as RelationType
 ,pg_catalog.pg_get_userbyid(c.relowner) as RelationOwner               
 ,pg_size_pretty(pg_relation_size(n.nspname ||'.'|| c.relname)) as RelationSize
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n               
                ON n.oid = c.relnamespace
WHERE  c.relkind IN ('r','s') 
AND  (n.nspname !~ '^pg_toast' and nspname like 'pg_temp%')
ORDER BY pg_relation_size(n.nspname ||'.'|| c.relname) DESC;


Popular posts from this blog

rman interview and scenario based questions and answer and also rman(backup and recovery) discussions panel

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

PostgreSQL Introduction