达梦8统计信息之表空间、用户、表

1. 表空间

--------------------查看表空间使用情况
SELECT 
	TABLESPACE_NAME AS 表空间名,
	ROUND(SUM(TOTAL_SIZE) / 1024.0 / 1024.0, 2) AS "总大小(MB)",
	ROUND(SUM(USED_SIZE)/ 1024.0 / 1024.0, 2) AS "已使用大小(MB)",
	CAST(ROUND(SUM(USED_SIZE) * 1.0 / SUM(TOTAL_SIZE) * 100,2) AS VARCHAR) || '%' AS 使用率
FROM
(
	SELECT 
		UPPER(T1.TABLESPACE_NAME) AS TABLESPACE_NAME,
		T1.BYTES AS TOTAL_SIZE,
		T1.BYTES - T2.BYTES AS USED_SIZE
	FROM
		DBA_DATA_FILES T1,
		DBA_FREE_SPACE T2
	WHERE
		T1.TABLESPACE_NAME = T2.TABLESPACE_NAME
		AND T1.FILE_ID = T2.FILE_ID
)
GROUP BY
	TABLESPACE_NAME;
--------------------查看表空间同数据文件对应关系
SELECT 
	TS.NAME AS 表空间名,
	DF.PATH AS 数据文件 
FROM 
	V$TABLESPACE AS TS,
	V$DATAFILE AS DF
WHERE 
	TS.ID = DF.GROUP_ID
ORDER BY 
	1;

2. 用户

--------------------查看所有用户、创建时间、默认表空间、等基本信息
SELECT 
	USERNAME AS 用户名,
	DEFAULT_TABLESPACE AS 默认数据表空间,
	DEFAULT_INDEX_TABLESPACE AS 默认索引表空间,
	TEMPORARY_TABLESPACE AS 临时表空间,
	DECODE(ACCOUNT_STATUS,'OPEN','正常','LOCKED','锁定','未知') AS 用户状态,
	CREATED AS 创建时间
FROM
	DBA_USERS;
--------------------查看用户数据库限制
SELECT 
	T1.USERNAME AS 用户名,
	DECODE(T2.AUTHENT_TYPE,1,'数据库密码认证',2,'操作系统认证',3,'远程认证','未知认证方式') AS 用户认证方式,
	T2.SESS_PER_USER AS 用户最大会话数,
	T2.CONN_IDLE_TIME AS "用户空闲期（分钟1-1440）",
	T2.FAILED_NUM AS 用户登录失败次数限制,
	T2.LIFE_TIME AS "口令有效期（天0-365）",
	T2.REUSE_TIME AS "口令等待期（天0-365）",
	T2.REUSE_MAX AS 口令变更次数,
	T2.LOCK_TIME AS "用户锁定时间（分1-1440）",
	T2.GRACE_TIME AS "口令宽限期1-30",
	T2.PASSWORD AS 密码策略,
	T2.RN_FLAG AS 只读,
	T2.ALLOW_ADDR AS 允许访问的IP,
	T2.NOT_ALLOW_ADDR AS 不允许访问的IP,
	T2.ALLOW_DT AS 允许访问的时间,
	T2.NOT_ALLOW_DT AS 不允许访问的时间,
	T2.LAST_LOGIN_DTID AS 上次登录时间,
	T2.LAST_LOGIN_IP AS 上次登录IP,
	T2.FAILED_ATTEMPS AS 自上一次登录成功以来失败次数
FROM 
	DBA_USERS T1,
	SYSUSERS T2 
WHERE 
	 T1.USER_ID = T2.ID;
--------------------查看用户对象统计信息
SELECT  
	S2.NAME AS 模式名, 
	S1.TYPE$ AS 主类型, 
	S1.SUBTYPE$ AS 子类型, 
	COUNT(*) AS 对象数量
FROM 
	SYSOBJECTS S1, 
	SYSOBJECTS S2 
WHERE 
	S1.SCHID = S2.ID 
	AND S2.TYPE$ = 'SCH' 
GROUP BY
	S2.NAME, 
	S1.TYPE$, 
	S1.SUBTYPE$ 
ORDER BY 
	S2.NAME, 
	S1.TYPE$, 
	S1.SUBTYPE$;
--------------------查看用户占用空间大小
SELECT 
	USERNAME AS 用户名,
	USER_USED_SPACE(USERNAME) * PAGE / 1024.0 / 1024.0 AS "用户占用空间(MB)"
FROM 
	DBA_USERS 
ORDER BY 
	2 DESC;

3. 表

--------------------统计所有用户表行数
SELECT
	T2.NAME AS 模式名,
	T1.NAME AS 表名,
	TABLE_ROWCOUNT(T2.NAME,T1.NAME) AS 行数
FROM
	SYSOBJECTS T1,
	SYSOBJECTS T2
WHERE
	T1.SCHID = T2.ID
	AND T1.SUBTYPE$ = 'UTAB'
	AND T2."TYPE$" = 'SCH'
ORDER BY 3 DESC;
--------------------统计所有用户表行数以及筛查某行数级别以上表行数 输入参数1：百万，千万，亿...等等，也可以共存，复制一行
SELECT 
	模式名,
	COUNT(表名) AS 表数量,
	COUNT(CASE WHEN 行数 > ? THEN 行数 ELSE NULL END) AS 百万表数量
FROM
(
	SELECT
		T2.NAME AS 模式名,
		T1.NAME AS 表名,
		TABLE_ROWCOUNT(T2.NAME,T1.NAME) AS 行数
	FROM
		SYSOBJECTS T1,
		SYSOBJECTS T2
	WHERE
		T1.SCHID = T2.ID
		AND T1.SUBTYPE$ = 'UTAB'
		AND T2."TYPE$" = 'SCH'
	--ORDER BY 3 DESC
)
GROUP BY 模式名
--------------------获取所有用户表定义
SELECT
	T2.NAME AS 模式名, 
	T1.NAME AS 表名,
	T1.CRTDATE AS 创建时间,
	DBMS_LOB.SUBSTR(DBMS_METADATA.GET_DDL('TABLE',T1.NAME,T2.NAME)) AS 表定义
FROM 
	SYSOBJECTS T1, 
	SYSOBJECTS T2
WHERE 
	T1."SUBTYPE$" = 'UTAB' 
  	AND T1.SCHID = T2.ID 
  	AND T2."TYPE$" = 'SCH';
--------------------统计用户表列信息
SELECT
	S1.NAME AS 表名,
	S2.NAME AS 列名,
	S2."TYPE$" AS 字段类型,
	S2."LENGTH$" AS 字段长度
FROM
	SYSOBJECTS S1,
	SYSCOLUMNS S2
WHERE
	S1."SUBTYPE$" = 'UTAB'
	AND S1.ID = S2.ID;
--------------------查看表占用空间大小
SELECT
	S2.NAME AS 模式名,
	S1.NAME AS 表名,
	TABLE_USED_SPACE(S2.NAME,S1.NAME) * PAGE /1024.0/1024.0 AS "表占用空间(MB)"
FROM
	SYSOBJECTS S1,
	SYSOBJECTS S2
WHERE
	S1.SCHID = S2.ID
	AND S1."SUBTYPE$" = 'UTAB'
	AND S2."TYPE$" = 'SCH'
ORDER BY
	3 DESC;