Oracle 11g 下表空间、表大小查看

查看表空间

SELECT
	tablespace_name as 表空间,
	round((sum_alloc - nvl(sum_free,0))/1024/1024,1) as 已用空间M,
	round(sum_max/1024/1024,1) as 总大小M,
	round(100*(sum_alloc - nvl(sum_free,0))/sum_max,1) As 使用百分比
FROM 
	( SELECT 
			tablespace_name, 
			sum(bytes) AS sum_alloc,
			sum(decode(maxbytes,0,bytes,maxbytes)) AS sum_max
		FROM dba_data_files
		GROUP BY 
			tablespace_name
	),
	( SELECT 
			tablespace_name AS fs_ts_name, 
			sum(bytes) AS sum_free
		FROM dba_free_space
		GROUP BY tablespace_name 
	)
WHERE 
	tablespace_name = fs_ts_name(+);

查看表空间下 – 表信息

SELECT 
	*
FROM 
	all_tables 
WHERE 
	tablespace_name = 'TABLESPACE_NAME';

统计表空间下 – 表个数

SELECT
	COUNT(TABLE_NAME) 
FROM 
	all_tables 
WHERE 
	tablespace_name = 'TABLESPACE_NAME';

查看表结构

SELECT
	* 
FROM
	USER_TAB_COLUMNS
WHERE
	TABLE_NAME = 'TABLE_NAME';

查看表空间下 – 表信息

通过表空间检索

SELECT t.OWNER,
       t.TABLESPACE_NAME,
       t.TABLE_NAME,
       t.NUM_ROWS * t.AVG_ROW_LEN / 1024 / 1024 AS TABLE_SIZE_BY_MB
from
     ALL_TABLES t
WHERE
    TABLESPACE_NAME = 'TABLESPACE_NAME'
ORDER BY
    NUM_ROWS
DESC NULLS LAST;

通过表名检索

SELECT t.OWNER,
       t.TABLESPACE_NAME,
       t.TABLE_NAME,
       t.NUM_ROWS * t.AVG_ROW_LEN / 1024 / 1024 AS TABLE_SIZE_BY_MB
from
     ALL_TABLES t
WHERE
    TABLE_NAME = 'TABLE_NAME'
ORDER BY
    NUM_ROWS
DESC NULLS LAST;

存在问题

• 问题

Count 表存在数据，ALL_TABLES 中 NUM_ROWS 为空。

• 解决办法

# 手动分析表 -- (全量、速度慢)
analyze table TABLE_NAME compute statistics ;

参考

• Oracle 如何对表进行分析
• Oracle 11g 创建表空间，并授权用户
• Oracle 临时表空间的增删改查操作 – 精品
• Oracle临时表空间查看、添加临时表空间数据文件、修改默认临时表空间