Oracle periodic monitoring: tablespace monitoring

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
2 from (select tablespace_name, sum (bytes)/1024/1024 as sum_m from dba_data_files group by tables
Pace_name ),
3 (select tablespace_name as fs_ts_name, max (bytes)/1024/1024 as max_m, count (blocks) as count_ B
Locks, sum (Bytes/1024/1024) as sum_free_m from dba_free_space group by tablespace_name)
4 where tablespace_name = fs_ts_name
5 order by pct_free
6/

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.