[DB] Oracle metric tablespace

For Oracle DBAs, table space monitoring is an important task of daily and regular monitoring.

Table space monitoring includes table space status, data files, table space usage, and remaining conditions.

1. Table space list:

SQL> select * from V $ tablespace;

TS # name Inc
-------------------------------------------
0 system Yes
1 undotbs1 Yes
3 indx Yes
4 tools Yes
5 Users Yes
2 temp Yes
7 SP Yes
6 test Yes

Eight rows have been selected.

It mainly checks the recent increase or decrease changes in the tablespace. Is it normal.

Second. Table space status

SQL> select tablespace_name, block_size, status, segment_space_management
From dba_tablespaces;

Tablespace_name block_size status segmen
-------------------------------------------------------
System 16384 online manual
Undotbs1 16384 online manual
Temp 16384 online manual
Indx 16384 online auto
Tools 16384 online auto
Users 16384 online auto
Test 16384 online manual
SP 16384 online manual

If the status of a tablespace is offline, check the cause to see if the table is set manually or if the system is offline.

Third. Check the data file status:

SQL> select ts #, file #, name, status from V $ datafile;

TS # file # name status
-------------------------------------------------------------------
0 1 D:/Oracle/oradata/ora92/system01.dbf System
1 2 D:/Oracle/oradata/ora92/undotbs01.dbf online
3 3 D:/Oracle/oradata/ora92/indx01.dbf online
4 4 D:/Oracle/oradata/ora92/tools01.dbf online
5 5 D:/Oracle/oradata/ora92/users01.dbf online
7 6 D:/Oracle/oradata/ora92/sp01.dbf online
6 7 d:/Oracle/oradata/ora92/test01.dbf online

You have selected 7 rows.

If the status of a number of files is offline, consider restoring them.

Third, the use of tablespace space:

SQL>

Select tablespace_name,
Max_m,
Count_blocks free_blk_cnt,
Sum_free_m,
To_char (100 * sum_free_m/sum_m, '99. 99') | '%' As pct_free
From (select tablespace_name, sum (bytes)/1024/1024 as sum_m
From dba_data_files
Group by tablespace_name ),
(Select tablespace_name as fs_ts_name,
Max (bytes)/1024/1024 as max_m,
Count (blocks) as count_blocks,
Sum (Bytes/1024/1024) as sum_free_m
From dba_free_space
Group by tablespace_name)
Where tablespace_name = fs_ts_name
Order by pct_free

 

Tablespace_name max_m free_blk_cnt sum_free_m pct_fre
---------------------------------------------------------------------
SP 28.1875 1 28.1875 23.49%
System 80.9375 2 81.25 32.50%
Undotbs1 29 19 36.6875 73.38%
Users 22.9375 1 22.9375 91.75%
Test 9.9375 1 9.9375 99.38%
Tools 9.9375 1 9.9375 99.38%
Indx 24.9375 1 24.9375 99.75%

You have selected 7 rows.

Check the table space growth and space surplus. If there is a tablespace with abnormal growth, you need to find the cause and manually expand the table space that is about to be full.

Through the above aspects, we can effectively monitor the situation between tables.