table,tablespace监控

最新推荐文章于 2024-03-14 16:55:24 发布

bitko

最新推荐文章于 2024-03-14 16:55:24 发布

阅读量398

点赞数

分类专栏： Oracle-Scripts

本文链接：https://blog.csdn.net/gumengkai/article/details/51164743

版权

Oracle-Scripts 专栏收录该内容

13 篇文章 2 订阅

订阅专栏

TABLE

1.user_tables(dba_tables),表实际使用的空间

SQL> select table_name,tablespace_name,num_rows,blocks,empty_blocks from user_tables where table_name='T';

TABLE_NAME TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ------------------------------ ---------- ---------- ------------
T USERS 56287 834 0

2.user_segments(dba_segments),分配给表的空间，而不管空间是否被使用

SQL> select segment_name,bytes/1024/1024,blocks from user_segments where segment_name='T';

SEGMENT_NAME BYTES/1024/1024 BLOCKS
--------------------------------------------------------------------------------- --------------- ----------
T 7 896

esle:也可通过user_extents查看具体分配的区的信息

SQL> SELECT SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BYTES, BLOCKS
2 FROM USER_EXTENTS
3 WHERE SEGMENT_NAME = 'T';

3.通过dbms_rowid统计数据占用的真实block数

SQL> select count(distinct(dbms_rowid.rowid_block_number(rowid))) from t;

COUNT(DISTINCT(DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)))
-----------------------------------------------------
812

4.tom大师show_space的api查看

SQL> set serveroutput on;
SQL> exec show_space('T');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ...................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ............................ 812 --等于dbms_rowid得到的占用block数
Total Blocks............................ 896
Total Bytes............................. 7,340,032
Total MBytes............................ 7
Unused Blocks........................... 62 --这里unused block就等与分配给表的总的block数减去user_tables中已使用的block数
Unused Bytes............................ 507,904
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 1,024
Last Used Block......................... 66

TABLESPACE

1.表空间占用数据文件的大小

SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;

TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
UNDOTBS1 45
SYSAUX 560
USERS 10
SYSTEM 710

2.表空间实际占用的大小

SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_segments group by tablespace_name;

TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
UNDOTBS1 33.625
SYSAUX 530.625
USERS 7.4375
SYSTEM 701.6875

3.剩余表空间的大小

SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;

TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
SYSAUX 28.375
UNDOTBS1 10.375
USERS 1.5625
SYSTEM 7.3125

可以看出2和3加到一起即是1的结果。

3.查看表空间的使用率

SQL> SELECT T1.TABLESPACE_NAME, T1.TBS_SIZE, T2.FREE_SIZE,ROUND(T2.FREE_SIZE/T1.TBS_SIZE*100,2)||'%'
2 FROM (SELECT A1.TABLESPACE_NAME, SUM(A1.BYTES) / 1024 / 1024 TBS_SIZE
3 FROM DBA_DATA_FILES A1
4 GROUP BY A1.TABLESPACE_NAME) T1,
5 (SELECT A2.TABLESPACE_NAME, SUM(A2.BYTES) / 1024 / 1024 FREE_SIZE
6 FROM DBA_FREE_SPACE A2
7 GROUP BY A2.TABLESPACE_NAME) T2
8 WHERE T1.TABLESPACE_NAME = T2.TABLESPACE_NAME;

TABLESPACE_NAME TBS_SIZE FREE_SIZE ROUND(T2.FREE_SIZE/T1.TBS_SIZE*100,2)||'%
------------------------------ ---------- ---------- -----------------------------------------
SYSAUX 560 28.375 5.07%
UNDOTBS1 45 10.375 23.06%
USERS 10 1.5625 15.63%
SYSTEM 710 7.3125 1.03%