查看表空间
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 ;
参考