search
HomeDatabaseMysql TutorialGreenPlum DBA常用SQL
GreenPlum DBA常用SQLJun 07, 2016 pm 02:53 PM
dbagreenplumsqlCommonly used

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);

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
SQL Server使用CROSS APPLY与OUTER APPLY实现连接查询SQL Server使用CROSS APPLY与OUTER APPLY实现连接查询Aug 26, 2022 pm 02:07 PM

本篇文章给大家带来了关于SQL的相关知识,其中主要介绍了SQL Server使用CROSS APPLY与OUTER APPLY实现连接查询的方法,文中通过示例代码介绍的非常详细,下面一起来看一下,希望对大家有帮助。

SQL Server解析/操作Json格式字段数据的方法实例SQL Server解析/操作Json格式字段数据的方法实例Aug 29, 2022 pm 12:00 PM

本篇文章给大家带来了关于SQL server的相关知识,其中主要介绍了SQL SERVER没有自带的解析json函数,需要自建一个函数(表值函数),下面介绍关于SQL Server解析/操作Json格式字段数据的相关资料,希望对大家有帮助。

聊聊优化sql中order By语句的方法聊聊优化sql中order By语句的方法Sep 27, 2022 pm 01:45 PM

如何优化sql中的orderBy语句?下面本篇文章给大家介绍一下优化sql中orderBy语句的方法,具有很好的参考价值,希望对大家有所帮助。

一文搞懂SQL中的开窗函数一文搞懂SQL中的开窗函数Sep 02, 2022 pm 04:55 PM

本篇文章给大家带来了关于SQL server的相关知识,开窗函数也叫分析函数有两类,一类是聚合开窗函数,一类是排序开窗函数,下面这篇文章主要给大家介绍了关于SQL中开窗函数的相关资料,文中通过实例代码介绍的非常详细,需要的朋友可以参考下。

SqlServer创建自动收缩事务日志任务的图文详解SqlServer创建自动收缩事务日志任务的图文详解Sep 09, 2022 pm 01:41 PM

本篇文章给大家带来了关于SQL server的相关知识,SQL Server数据库存在一个问题,如果你限制了它的日志文件的大小,那么当数据库日志达到这个大小的时候,数据库就会停止写入日志,下面这介绍了关于SqlServer创建自动收缩事务日志任务的相关资料,希望对大家有帮助。

SQL Server跨服务器操作数据库的图文方法(LinkedServer)SQL Server跨服务器操作数据库的图文方法(LinkedServer)Nov 02, 2022 pm 04:13 PM

本篇文章给大家带来了关于SQL的相关知识,其中主要介绍了SQL Server跨服务器操作数据库的图文方法,SQL Server Management Studio (SSMS) 是用于管理SQL Server 基础结构的集成环境,下面一起来看一下,希望对大家有帮助。

Spring注解大揭秘:常用注解解析Spring注解大揭秘:常用注解解析Dec 30, 2023 am 11:28 AM

Spring是一个开源框架,提供了许多注解来简化和增强Java开发。本文将详细解释常用的Spring注解,并提供具体的代码示例。@Autowired:自动装配@Autowired注解可以用于自动装配Spring容器中的Bean。当我们在需要依赖的地方使用@Autowired注解时,Spring将会在容器中查找匹配的Bean并自动注入。示例代码如下:@Auto

学习canvas框架 详解常用的canvas框架学习canvas框架 详解常用的canvas框架Jan 17, 2024 am 11:03 AM

探索Canvas框架:了解常用的Canvas框架有哪些,需要具体代码示例引言:Canvas是HTML5中提供的一个绘图API,通过它我们可以实现丰富的图形和动画效果。为了提高绘图的效率和便捷性,许多开发者开发了不同的Canvas框架。本文将介绍一些常用的Canvas框架,并提供具体代码示例,以帮助读者更深入地了解这些框架的使用方法。一、EaselJS框架Ea

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

DVWA

DVWA

Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

PhpStorm Mac version

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool

Safe Exam Browser

Safe Exam Browser

Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment