GREENPLUM常用维护脚本总结1.docx

GreenPlum Useful Scripts Series Part -1Red: - You have to replace these parameters in the scripts.?Last Analyzed or Vacuum or Create TABLE OR ETC…Query Idle for long time:How to find the largest table in the database?first 5 biggest table in the databaseHow to calculate database size in disk?How to calculate table size in disk?How to find size of the table (not including index)?How to generate a series of numbers and insert it into a table?How to count total number of rows in a table?Total number of rows with a specific column value is not null.How can I get the maximum value of a column in the table? -- First second?How can I get the second minimum value of a column in the table?-- First second?How to view the basic available data types in GP.Show segments, which are down.Find Current users:Checking for Active Sessions (Workload):Queries that are waiting in a queueViewing the List of DatabasesLast Analyzed or Vacuum or Create TABLE OR ETC…Select * from pg_stat_operations where schemaname=SCHEMA NAME and actionname in (ANALYZE,VACUUM) order by statime;Query Idle for long time:Select * from pg_stat_activity where now()-backend_start 2700;Select * from pg_stat_activity order by query_start,backend_start;How to find the largest table in the database?SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;If you want only the first 5 biggest table in the database then append the above query with limit as:SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 5;relname – name of the relation/table.relpages - relation pages ( number of pages, by default a page is 8kb )pg_class – system table, which maintains the details of relationslimit 1 – limits the output to display only one row.How to calculate database size in disk? SELECT pg_database_size(Database Name); -- size in kb or bytesSELECT pg_size_pretty(pg_database_size(Database Name)); -- size in MBHow to calculate table size in disk?SELECT pg_size_pretty(pg_total_relation_size(faa.d_airlines))