檢視空間使用情況的指令碼

在資料庫中，我們可以使用如下的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 檢視錶的增長情況和預測，標黃的部分是預測的部分。

sqlplus -s n1/n1 < set linesize 200
set pages 20
select * from table(dbms_space.OBJECT_GROWTH_TREND(upper('$1'),upper('$2'),'TABLE'));

EOF

指令碼執行的結果如下：
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 檢視錶空間的使用情況

sqlplus -s n1/n1 < set linesize 200
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;

EOF

比如我們檢視錶空間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.