How to monitor Oracle database tables and table spaces

How to monitor the status of Oracle database tables and table spaces

Declare

UNF number;

UNFB number;

FS1 number;

FS1B number;

FS2 number;

FS2B number;

FS3 number;

FS3B number;

FS4 number;

FS4B number;

Full number;

FULLB number;

Begin

Dbms_space.space_usage (' &1 ',

' &2 ',

' TABLE ',

unf

UNFB,

FS1,

FS1B,

FS2,

FS2B,

FS3,

FS3B,

FS4,

FS4B,

Full

FULLB);

Dbms_output.put_line (' unformatted_blocks: ' | | | unf);

Dbms_output.put_line (' Full_blocks (0% free): ' | |

Dbms_output.put_line (' Fs1_blocks (0-25% free): ' | | fs1);

Dbms_output.put_line (' Fs2_blocks (25-50% free): ' | | fs2);

Dbms_output.put_line (' Fs3_blocks (50-75% free): ' | | fs3);

Dbms_output.put_line (' Fs4_blocks (75-100% free): ' | | fs4);

End

/

2. Table space usage history (output only the first statistical results per day)

Select B.name,

A.rtime,

A.tablespace_usedsize,

A.tablespace_size,

Round (a.tablespace_usedsize/a.tablespace_size) used_percent

From Dba_hist_tbspc_space_usage A,

(Select T2.name,

Min (rtime) Rtime,

Min (tablespace_id) tablespace_id

From Dba_hist_tbspc_space_usage T1

Inner join v$tablespace t2 on t1.tablespace_id = t2. ts#

where t2.name = Upper (' &1 ')

Group by name, substr (rtime,1,10)

) b

where a.tablespace_id = b.tablespace_id

and a.rtime = B.rtime

Order BY A.rtime;

3. Residual space projections for the table

SELECT * FROM table (dbms_space. Object_growth_trend (' ecc_cc ', ' ytkc_orderrelevance ', ' TABLE ')