GaussDB(DWS)如何查询表的创建时间？

一、背景描述

在项目交付中，经常有人会问“如何在数据库中查询表的创建时间？” ，那么究竟如何在GaussDB(DWS)中查找对象的创建时间呢？本文提供3种方法作为参考，包括dba_objecs视图查看方法、审计日志查看方法和CN日志查看方法。

二、操作演练

方法1：视图查询方法

DBA_OBJECTS视图存储了数据库中所有数据库对象的相关信息, GaussDB(DWS)支持通过DBA_OBJECTS视图进行查询，字段和详细说明如下：

表1 DBA_OBJECTS视图字段
名称	类型	描述
owner	name	对象的所有者
object_name	name	对象的名称
object_id	oid	对象的OID
object_type	name	对象的类型
namespace	oid	对象所在的命名空间
created	timestamp with time zone	对象的创建时间
last_ddl_time	timestamp with time zone	对象最后修改时间，修改行为包括ALTER操作和GRANT、REVOKE操作。

注意：需要有系统管理员权限才可以访问。

其中对象的类型object_type字段会包括TABLE、INDEX、VIEW、RULE, PROCEDURE、TYPE、OPERATOR等，查询时可根据该字段进行筛选：

1. 创建测试表

创建测试表，用于后续查询测试。

--定义一个表，使用HASH分布。

CREATE TABLE employee_info

(

id INTEGER NOT NULL,

name VARCHAR(16) NOT NULL,

tel VARCHAR(11),

addr varchar(10)

)DISTRIBUTE BY HASH(id);

--查看当前系统时间。

select current_timestamp;

测试表创建完成后查看当前系统时间作为参考。

2. 查询创建时间

通过DBA_OBJECTS视图查看表对象创建时间。

select * from dba_objects where object_type='TABLE' and object_name='employee_info';

查询结果如下：

3. 更新测试表

更新测试表employee_info，测试dba_objects视图是否可以保存对象的最后修改时间，修改行为包括ALTER操作和GRANT、REVOKE操作：

--向表中增加一个varchar列group_info。

ALTER TABLE employee_info ADD group_info varchar(30);

--查看当前系统时间。

select current_timestamp;

4. 查看最后更新时间

通过DBA_OBJECTS视图查看对象的最后更新时间。

select * from dba_objects where object_type='TABLE' and object_name='employee_info';

查询结果如下：

方法2：审计日志查看方法

审计日志统计信息更全，打开审计总开关audit_enabled=on，并设置审计项audit_system_object。

audit_system_object参数说明：

该参数决定是否对GaussDB A数据库对象的CREATE、DROP、ALTER操作进行审计。GaussDB A数据库对象包括DATABASE、USER、schema、TABLE等。通过修改该配置参数的值，可以只审计需要的数据库对象的操作。

取值范围：整型，0～524287

Ø 0代表关闭数据库对象的CREATE、DROP、ALTER操作审计功能。

Ø 非0代表只审计某类或者某些数据库对象的CREATE、DROP、ALTER操作。

默认值：12295 换算成19位二进制为000 0011 0000 0000 0111

取值说明：该参数的值由19个二进制位的组合求出，这19个二进制位分别代表GaussDB (DWS)的19类数据库对象。如果对应的二进制位取值为0，表示不审计对应的数据库对象的CREATE、DROP、ALTER操作；取值为1，表示审计对应的数据库对象的CREATE、DROP、ALTER操作。这19个二进制位代表的具体审计内容请参见表2。

表2 audit_system_object取值含义说明
二进制位	含义	取值说明
第0位	是否审计DATABASE对象的CREATE、DROP、ALTER操作。	0表示不审计该对象的CREATE、DROP、ALTER操作；
		1表示审计该对象的CREATE、DROP、ALTER操作。
第1位	是否审计SCHEMA对象的CREATE、DROP、ALTER操作。	0表示不审计该对象的CREATE、DROP、ALTER操作；
		1表示审计该对象的CREATE、DROP、ALTER操作。
第2位	是否审计USER对象的CREATE、DROP、ALTER操作。	0表示不审计该对象的CREATE、DROP、ALTER操作；
		1表示审计该对象的CREATE、DROP、ALTER操作。
第3位	是否审计TABLE对象的CREATE、DROP、ALTER、TRUNCATE操作。	0表示不审计该对象的CREATE、DROP、ALTER、TRUNCATE操作；
		1表示审计该对象的CREATE、DROP、ALTER、TRUNCATE操作。
第4位	是否审计INDEX对象的CREATE、DROP、ALTER操作。	0表示不审计该对象的CREATE、DROP、ALTER操作；
		1表示审计该对象的CREATE、DROP、ALTER操作。
第5位	是否审计VIEW对象的CREATE、DROP操作。	0表示不审计该对象的CREATE、DROP操作；
		1表示审计该对象的CREATE、DROP操作。
第6位	是否审计TRIGGER对象的CREATE、DROP、ALTER操作。	0表示不审计该对象的CREATE、DROP、ALTER操作；
		1表示审计该对象的CREATE、DROP、ALTER操作。
第7位	是否审计PROCEDURE/FUNCTION对象的CREATE、DROP、ALTER操作。	0表示不审计该对象的CREATE、DROP、ALTER操作；
		1表示审计该对象的CREATE、DROP、ALTER操作。
第8位	是否审计TABLESPACE对象的CREATE、DROP、ALTER操作。	0表示不审计该对象的CREATE、DROP、ALTER操作；
		1表示审计该对象的CREATE、DROP、ALTER操作。
第9位	是否审计RESOURCE POOL对象的CREATE、DROP、ALTER操作。	0表示不审计该对象的CREATE、DROP、ALTER操作；
		1表示审计该对象的CREATE、DROP、ALTER操作
第10位	是否审计WORKLOAD对象的CREATE、DROP、ALTER操作。	0表示不审计该对象的CREATE、DROP、ALTER操作；
		1表示审计该对象的CREATE、DROP、ALTER操作
第11位	是否审计SERVER FOR HADOOP对象的CREATE、DROP、ALTER操作。	0表示不审计该对象的CREATE、DROP、ALTER操作；
		1表示审计该对象的CREATE、DROP、ALTER操作
第12位	是否审计DATA SOURCE对象的CRAETE、DROP、ALTER操作。	0表示不审计该对象的CREATE、DROP、ALTER操作；
		1表示审计该对象的CREATE、DROP、ALTER操作。
第13位	是否审计NODE GROUP对象的CREATE、DROP操作。	0表示不审计该对象的CREATE、DROP操作；
		1表示审计该对象的CREATE、DROP操作。
第14位	是否审计ROW LEVEL SECURITY对象的CREATE、DROP、ALTER操作。	0表示不审计该对象的CREATE、DROP、ALTER操作；
		1表示审计该对象的CREATE、DROP、ALTER操作。
第15位	是否审计TYPE对象的CREATE、DROP、ALTER操作。	0表示不审计TYPE对象的CREATE、DROP、ALTER操作；
		1表示审计TYPE对象的CREATE、DROP、ALTER操作。
第16位	是否审计TEXT SEARCH对象（CONFIGURATION和DICTIONARY）的CREATE、DROP、ALTER操作。	0表示不审计TEXT SEARCH对象的CREATE、DROP、ALTER操作；
		1表示审计TEXT SEARCH对象的CREATE、DROP、ALTER操作。
第17位	是否审计DIRECTORY对象的CREATE、DROP、ALTER操作。	0表示不审计DIRECTORY对象的CREATE、DROP、ALTER操作；
		1表示审计DIRECTORY对象的CREATE、DROP、ALTER操作。
第18位	是否审计SYNONYM对象的CREATE、DROP、ALTER操作。	0表示不审计SYNONYM对象的CREATE、DROP、ALTER操作；
		1表示审计SYNONYM对象的CREATE、DROP、ALTER操作。

12295 换算成19位二进制为000 0011 0000 0000 0111，修改第3位的值为1，表示审计TABLE对象的CREATE、DROP、ALTER、TRUNCATE操作，修改后的值为12303（对应的19位二进制为000 0011 0000 0000 1111）

参数设置如下：

gs_guc reload -Z coordinator -Z datanode -N all -I all -c "audit_enabled=on"

gs_guc reload -Z coordinator -Z datanode -N all -I all -c "audit_system_object=12303"

参数设置命令截图:

设置成功：

按照方法1中的流程创建并更新测试表，记录系统当前时间作为参照：

查看审计日志：

select * from pgxc_query_audit('2021-05-27 16:10:00','2021-05-27 16:20:00') where operation_type='ddl' and object_name='employee_info';

截图如下：

方法3：CN日志查看方法

配置 postgresql.conf 配置文件，通过记录表的 DDL 信息，从而确定表的创建时间。

log_statement参数介绍：

log_statement参数说明：控制记录SQL语句。

该参数属于SUSET类型参数，请参考表1中对应设置方法进行设置。即使log_statement设置为all，包含简单语法错误的语句也不会被记录，因为仅在完成基本的语法分析并确定了语句类型之后才记录日志。

取值范围：枚举类型

•none表示不记录语句。

•ddl表示记录所有的数据定义语句，比如CREATE、ALTER和DROP语句。

•mod表示记录所有DDL语句，还包括数据修改语句INSERT、UPDATE、DELETE、TRUNCATE和COPY FROM 。

•all表示记录所有语句，PREPARE、EXECUTE和EXPLAIN ANALYZE语句也同样被记录。

默认值：none

log_statement参数设置方法：

--登录CN所在的数据节点，执行

source /opt/huawei/Bigdata/mppdb/.mppdbgs_profile

gs_guc reload -Z coordinator -Z datanode -N all -I all -c "log_statement=ddl"