Common GreenPlumDBA SQL

GreenPlumDBA commonly used SQL to view the object size (table, index, database, etc.) selectpg_size_pretty (pg_relation_size ($ schema. $ table); view the user (non-system) table and index select * frompg_stat_user_tables; seect * frompg_stat_user_indexes; www.2cto.com view the table partition selectb.

GreenPlum DBA commonly uses SQL to view the object size (tables, indexes, databases, etc.) select pg_size_pretty (pg_relation_size ($ schema. $ table); view the user (non-system) table and index select * from pg_stat_user_tables; seect * from pg_stat_user_indexes; www.2cto.com view the table partition select B.

Common GreenPlum DBA SQL

View object size (tables, indexes, databases, etc)

Select pg_size_pretty (pg_relation_size ('$ schema. $ table '));

View user (non-system) tables and Indexes

Select * from pg_stat_user_tables;

Seect * from pg_stat_user_indexes;

Www.2cto.com

View table partitions

Select B. nspname | '.' | a. relname as tablename, d. parname as partname

From pg_class a, pg_namespace B, pg_partition c, pg_partition_rule d

Where a. relnamespace = B. oid

And B. nspname = '$ Scheme'

And a. relname = '$ table'

And a. oid = c. parrelid

And c. oid = d. paroid

Order by parname;

View Distributed key

Select B. attname

From pg_class a, pg_attribute B, pg_type c, gp_distribution_policy d, pg_namespace e

Where d. localoid = a. oid

And a. relnamespace = e. oid

And e. nspname = '$ Scheme'

And a. relname = '$ table'

And a. oid = B. attrelid

And B. atttypid = c. oid

And B. attnum> 0

And B. attnum = any (d. attrnums)

Order by attnum;

Www.2cto.com

View the currently alive Query

Select procpid as pid, sess_id as session, usename as user, current_query as query, waiting,

Date_trunc ('second', query_start) as start_time, client_addr as useraddr

From pg_stat_activity

Where datname = '$ pgdatabase'

And current_query not like '% from pg_stat_activity % where datname = %'

Order by start_time;

Field name used as a foreign key on the table

Select f. conname, pg_get_constraintdef (f. oid), t2.relname

From pg_class t, pg_class t2, pg_constraint f

Where f. confrelid = t. oid

And f. conrelid = t2.oid

And f. contype = 'F'

And t. relname = '$ table ';

Www.2cto.com

1. view the table space

SELECT *

From public. dba_segments

WHERE owner LIKE 'owber _ name'

AND table_name LIKE '% table_name %'

Order by table_name;

2. view the partition in GP:

Select partitionname, partitionboundary from pg_partitions where tablename = 'table _ name ';

Select partitionname, partitionboundary from pg_catalog.pg_partitions where tablename = 'table _ name ';

3. view the running SQL

Select * from pg_stat_activity;

4. Modify the table owner statement

Alter table table_name owner to owner_name;

Www.2cto.com

5. Add Table partitions

Alter table table_name add partition P20091001 START (DATE '2017-10-01 ') inclusive end (DATE '2017-10-02') exclusive with (appendonly = true, compresslevel = 5 );

6. Modify the column type

Alter table table_name alter column a TYPE varchar (2048 );

7. Modify the distributed Column

Alter table table_name set distributed by (column_1 );