查看datafile的HWM,估算resize最小size脚本
SELECT a.tablespace_name,
file_name,
c.VALUE / 1024 "Blk.size(Kb)",
CEIL ((NVL (hwm, 1) *c.VALUE) / 1024 / 1024) "smallest(Mb) - HWM",
CEIL (blocks * c.VALUE /1024 / 1024) "currsize(Mb)",
CEIL (blocks * c.VALUE /1024 / 1024) - CEIL ((NVL (hwm, 1) * c.VALUE) / 1024 / 1024)"savings(Mb)"
FROM dba_data_files a,
(SELECT file_id, MAX(block_id + blocks - 1) hwm
FROMdba_extents
GROUP BY file_id)b,
(SELECT VALUE
FROMv$parameter
WHERE NAME ='db_block_size') c
WHERE a.file_id = b.file_id(+)SQL> /
Tablespace_Name File_Name Blk. size(Kb) smallest(Mb) - HWM currsize(Mb) savings(Mb)
--------------- ------------------------------------------------------------ ------------- ------------------ ------------ -----------
SYSTEM /oracle/oracle/oradata/tjyd2014/system01.dbf 8 1128 2048 920
USERS /oracle/oracle/oradata/tjyd2014/USERS_03.dbf 8 2048 2048 0
BACKUP /oracle/oracle/db_1/dbs/D:ORACLEPRODUCT10.2.0ORADATATJYDNGRM 8 22 2000 1978
UNDOTBS1 /oracle/oracle/oradata/tjyd2014/undotbs01.dbf 8 35 4096 4061
USERS /oracle/oracle/oradata/tjyd2014/users01.dbf 8 20868 20868 0
USERS /oracle/oracle/oradata/tjyd2014/USERS_02.dbf 8 2048 2048 0
USERS /oracle/oracle/oradata/tjyd2014/USERS_05.dbf 8 2048 2048 0
SYSAUX /oracle/oracle/oradata/tjyd2014/sysaux01.dbf 8 484 1024 540
USERS /oracle/oracle/oradata/tjyd2014/USERS_04.dbf 8 2048 2048 0
NRMSTJ /oracle/oracle/oradata/tjyd2014/NRMSTJ_01.dbf 8 254 1024 770
SDE /oracle/oracle/oradata/tjyd2014/SDE_01.dbf 8 688 2048 1360
Tablespace_Name File_Name Blk. size(Kb) smallest(Mb) - HWM currsize(Mb) savings(Mb)
--------------- ------------------------------------------------------------ ------------- ------------------ ------------ -----------
UNDOTBS2 /oracle/oracle/db_1/dbs/D:ORACLEPRODUCT10.2.0ORADATATJYDNGRM 8 1 20 19
USERS /oracle/oracle/oradata/tjyd2014/USERS_06.dbf 8 1 10240 10239
and a.status !='INVALID';
该脚本可以查询一个月一来表空间的变化情况,注意,该脚本只能在10g以及以上版本中运行,因为
9i没有dba_hist_tbspc_usage这个视图
select a.name,b.* from v$tablespace a,
(select tablespace_id,trunc(to_date(rtime,'mm/dd/yyyy hh24:mi:ss'))datetime,max(tablespace_usedsize*8/1024) used_size fromdba_hist_tbspc_space_usage where trunc(to_date(rtime,'mm/dd/yyyy hh24:mi:ss'))>trunc(sysdate-30) group by tablespace_id,
trunc(to_date(rtime,'mm/dd/yyyy hh24:mi:ss')) order by tablespace_id,trunc(to_date(rtime,'mm/dd/yyyy hh24:mi:ss')))b
where a.ts#=b.tablespace_id;