GreenPlum DBA常用SQL

GreenPlum DBA常用SQL 查看对象大小(表、索引、数据库等) select pg_size_pretty(pg_relation_size($schema.$table)); 查看用户(非系统)表和索引 select * from pg_stat_user_tables; seect * from pg_stat_user_indexes; www.2cto.com 查看表分区 select b.

GreenPlum DBA常用SQL

查看对象大小(表、索引、数据库等)

select pg_size_pretty(pg_relation_size(’$schema.$table’));

查看用户(非系统)表和索引

select * from pg_stat_user_tables;

seect * from pg_stat_user_indexes;

www.2cto.com

查看表分区

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 = ‘$schema’

and a.relname = ‘$table’

and a.oid = c.parrelid

and c.oid = d.paroid

order by parname;

查看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 = ‘$schema’

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

查看当前存活的查询

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;

表上被用作外键的字段名

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. 查看表使用空间

SELECT *

FROM PUBLIC.dba_segments

WHERE owner LIKE 'owber_name'

AND table_name LIKE '%table_name%'

ORDER BY table_name;

2. GP中查看分区：

select partitionname,partitionboundary from pg_partitions where tablename='table_name';

select partitionname,partitionboundary from pg_catalog.pg_partitions where tablename='table_name';

3. 查看正在运行的sql

select * from pg_stat_activity;

4. 修改表的owner语句

Alter table table_name owner to owner_name;

www.2cto.com

5. 增加表分区

ALTER TABLE table_name ADD PARTITION P20091001 START (DATE '2009-10-01') INCLUSIVE END (DATE '2009-10-02') EXCLUSIVE WITH(appendonly=true,compresslevel=5);

6. 修改列类型

ALTER TABLE table_name ALTER COLUMN a TYPE varchar(2048);

7. 修改distributed 列

alter table table_name set distributed by(column_1);