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 ')