Greenplum DBA Quick Reference

  • Fixing gp_bloat_diag view - showing bloated tables even there are no bloats on those tables Recently we found that gp_toolkit.gp_bloat_diag view shows some bloated tables which were recently reorg (vacuum full) and analyzed .Problem was due to incorrect data reported by ...
    Posted Aug 30, 2018, 11:12 AM by Sachchida Ojha
  • Dynamic UNIX script to change the owner of tables in Greenplum -- Change owner of all tables of a given schemafor tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" sachi` ; do  psql -c "alter table $tbl ...
    Posted Oct 26, 2014, 6:34 AM by Sachchida Ojha
  • Checking bloats with customized scripts gp_bloat_diag view helps to identify tables that need routine table maintenance (VACUUM and/or ANALYZE). See also : Creating a test case for bloat and understanding how it works ...
    Posted Oct 23, 2014, 6:08 PM by Sachchida Ojha
  • Sachi's SQL script collections for Greenplum DBA's SQL to list all parameter settings in GreenplumDCA Information -DCA software version, Database version, no of segments, space used/available on each segments, segment downtime report SQLSQL to ...
    Posted Nov 14, 2014, 3:28 PM by Sachchida Ojha
  • Greenplum gpconfig command to get and set GUC parameters [gpadmin@sachi ~]$ gpconfig -?COMMAND NAME: gpconfigSets server configuration parameters on all segments within a Greenplum Database system.*****************************************************SYNOPSIS*****************************************************gpconfig -c <param_name> -v <value> [-m <master_value> | --masteronly ...
    Posted Oct 11, 2014, 8:42 AM by Sachchida Ojha
  • Troubleshoot and fix gpfdist process dying abruptly when multiple gpload session starts from Informatica power center Debugging little more and looking at the log we found that Informatica is launching 3 gpload in parallel and each gpload process is launching have 10 gpfdist processes. The gpload ...
    Posted Oct 11, 2014, 8:27 AM by Sachchida Ojha
  • Greenplum database maintenance and monitoring - do it yourself script A custom designed, must have  script for all greenplum DBA. To get the script email us gpdba@greenplumdba.com==Greenplum database maintenance and monitoring Option ==############################################################################################## Please read the menu options ...
    Posted Oct 9, 2014, 8:07 AM by Sachchida Ojha
  • Reading QUERY PLAN output in Greenplum Query plans are a tree plan of nodes. Each node in the plan represents a single operation, such as table scan, join, aggregation or a sort. Plans should be read ...
    Posted Feb 9, 2014, 12:54 PM by Sachchida Ojha
  • Reclaim all expired row space in Greenplum A VACUUM FULL will reclaim all expired row space, but is a very expensive operation and may take an unacceptably long time to finish on large, distributed Greenplum Database tables ...
    Posted Feb 9, 2014, 12:39 PM by Sachchida Ojha
  • What happens when you run a SQL statement in Greenplum? When a user runs a SQL query in greenplum, it is the master who receives the SQL. Master then parse the query , optimize the query, creates execution plan of the ...
    Posted Feb 8, 2014, 7:46 AM by Sachchida Ojha
Showing posts 1 - 10 of 14. View more »
  • Public Yum Server The Oracle public yum server offers a free and convenient way to install the latest Oracle Linux packages as well as packages from the Oracle VM installation media via a ...
    Posted Apr 22, 2013, 8:43 AM by Sachchida Ojha
  • Oracle external table just like external table in Greenplum In the Greenplum database, we use external table combined with gpfdist to read the data from a  csv file located in in a directory and then then load that data ...
    Posted Feb 23, 2013, 5:08 PM by Sachchida Ojha
  • RESUMABLE SPACE ALLOCATION Oracle database provides a way to suspend long-running operations in the event of space allocation failure. Once the DBA is notified and space issue has been corrected, the long ...
    Posted Feb 4, 2012, 12:01 AM by Sachchida Ojha
Showing posts 1 - 3 of 19. View more »
  • Schema Check sum report set serveroutput onset feedback offset verify offset linesize 10000create or replace type chksumType as VARRAY(3000) of NUMBER;/declare  cursor c0 is    select OWNER, TABLE_NAME ...
    Posted Feb 22, 2013, 2:49 PM by Sachchida Ojha
  • User resource usage SELECT   DECODE(ses.username, NULL, 'SYS', ses.username) username,         TO_CHAR(ses.SID) SID, sn.NAME, sest.VALUE, ins.instance_name,         DECODE(ses.username, NULL, 'SYS', ses.username) || TO_CHAR ...
    Posted Sep 27, 2010, 6:38 AM by Sachchida Ojha
Showing posts 1 - 2 of 10. View more »
  • Configuring PostgreSQL on ORHEL6.1 -bash-4.1$ initdb -D /var/lib/pgsql/dataThe files belonging to this database system will be owned by user "postgres".This user must also own the server process ...
    Posted Apr 22, 2013, 5:47 AM by Sachchida Ojha
  • SECURITY DEFINER or AUTHID CURRENT USER I've just come to minor notice: PostgreSQL defaults to executing procedures with current user rights, Oracle - with definer's rights. It was rather interesting.Let's say we have ...
    Posted Apr 3, 2013, 5:54 PM by Sachchida Ojha
Showing posts 1 - 2 of 20. View more »