在数据库中，我们可以使用如下的3个脚本来查看表空间的使用情况，表空间的增长情况，表未使用的空间情况等等。
showunused.sh 可以查看未使用的空间情况
sqlplus -s n1/n1
prompt ------- $1.$2
prompt .
set serveroutput on
set feedback off
variable total_blocks number;
variable total_bytes number;
variable unused_blocks number;
variable unused_bytes number;
variable lastextf number;
variable last_extb number;
variable lastusedblock number;
EXEC DBMS_SPACE.UNUSED_SPACE(upper('$1'), upper('$2'), 'TABLE', :total_blocks, :total_bytes,:unused_blocks, :unused_bytes, :lastextf, :last_extb, :lastusedblock);
exec dbms_output.put_line('total_blocks: '||:total_blocks);
exec dbms_output.put_line('total_bytes: '||:total_bytes);
exec dbms_output.put_line('unused_blocks: '||:unused_blocks);
exec dbms_output.put_line('unused_bytes: '||:unused_bytes);
exec dbms_output.put_line('last used extent file: '||:lastextf);
exec dbms_output.put_line('last used extent block: '||:last_extb);
exec dbms_output.put_line('lastusedblock: '||:lastusedblock);
EOF
脚本运行的结果如下：
------- n1.data
.
total_blocks: 72
total_bytes: 589824
unused_blocks: 0
unused_bytes: 0
last used extent file: 7
last used extent block: 12840
lastusedblock: 8
showtabtrend.sh 查看表的增长情况和预测，标黄的部分是预测的部分。
set linesize 200
set pages 20
select * from table(dbms_space.OBJECT_GROWTH_TREND(upper('$1'),upper('$2'),'TABLE'));
TIMEPOINT SPACE_USAGE SPACE_ALLOC QUALITY
--------------------------------------------------------------------------- ----------- ----------- --------------------
22-AUG-14 07.53.17.112473 AM 503863 589824 INTERPOLATED
23-AUG-14 07.53.17.112473 AM 503863 589824 INTERPOLATED
24-AUG-14 07.53.17.112473 AM 503863 589824 INTERPOLATED
19-SEP-14 07.53.17.112473 AM 503863 589824 INTERPOLATED
20-SEP-14 07.53.17.112473 AM 503863 589824 INTERPOLATED
21-SEP-14 07.53.17.112473 AM 503863 589824 INTERPOLATED
22-SEP-14 07.53.17.112473 AM 503863 589824 PROJECTED
23-SEP-14 07.53.17.112473 AM 503863 589824 PROJECTED
24-SEP-14 07.53.17.112473 AM 503863 589824 PROJECTED
showtsusage.sh 查看表空间的使用情况
select b.name,
a.rtime,
a.tablespace_usedsize,
a.tablespace_size,
round(100 * 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;
比如我们查看表空间SYSTEM的情况。脚本运行的情况如下：
NAME RTIME TABLESPACE_USEDSIZE TABLESPACE_SIZE USED_PERCENT
------------------------------ ------------------------- ------------------- --------------- ------------
SYSTEM 03/15/2014 05:00:42 33096 41600 80
SYSTEM 03/16/2014 19:41:41 33096 41600 80
SYSTEM 09/13/2014 07:00:05 35264 41600 85
SYSTEM 09/14/2014 00:00:45 35264 41600 85
SYSTEM 09/15/2014 03:13:25 35264 41600 85
SYSTEM 09/16/2014 00:00:36 35264 41600 85
SYSTEM 09/17/2014 06:07:45 35264 41600 85
SYSTEM 09/19/2014 05:24:06 35264 41600 85
SYSTEM 09/20/2014 00:00:19 35280 41600 85
SYSTEM 09/21/2014 00:00:59 35280 41600 85
10 rows selected.