今天主要总结了一些Oracle表空间日增长和历史统计的脚本，仅供参考。11g统计表空间的每天增加量SELECTa.snap_id,c.tablespace_namets_name,to_char(to_date(a.rtime,'mm/dd/yyyyhh24:mi:ss'),'yyyy-mm-ddhh24:mi')rtime,圆（a.tablespace_size*c.block_size/1024/1024,2）ts_size_mb，round（a.tablespace_usedsize*c.block_size/1024/1024,2）ts_used_mb，round（（a.tablespace_size-a.tablespace_usedsize）*c。block_size/1024/1024,2)ts_free_mb,round(a.tablespace_usedsize/a.tablespace_size*100,2)pct_usedFROMdba_hist_tbspc_space_usagea,(SELECTtablespace_id,substr(rtime,1,10)rtime,max(snap_id)snap_idFROMdba_hist_tbspc_space_usagenb_id,1subtablespace_id,groupbystrtime,10))b,dba_tablespacesc,v$tablespacedwherea.snap_id=b.snap_idanda.tablespace_id=b.tablespace_idanda.tablespace_id=d.TS#andd.NAME=c.tablespace_nameandto_date(a.rtime,'mm/dd/yyyyhh24:mi:ss')>=sysdate-30orderbya.tablespace_id,to_date(a.rtime,'mm/dd/yyyyhh24:mi:ss')desc;12c统计表空间的每日增量SELECTa.snap_id,a.con_id,e.namepdbname,c.tablespace_namets_name,to_char(to_date(a.rtime,'mm/dd/yyyyhh24:mi:ss'),'yyyy-mm-ddhh24:mi')rtime,round(a.tablespace_size*c.block_size/1024/1024,2)ts_size_mb,round(a.tablespace_usedsize*c.block_size/1024/1024,2)ts_used_mb,round((a.tablespace_size-a.tablespace_usedsize)*c.block_size/1024/1024,2)ts_free_mb,round(a.tablespace_usedsize/a.tablespace_size*100,2)pct_usedFROMcdb_hist_tbspc_space_usagea,(SELECTtablespace_id,nb.con_id,substr(rtime,1,10)rtime,max(snap_id)snap_idFROMdba_hist_tbspc_space_usagenbgroupbytablespace_id,nb.con_id,substr(rtime,1,10))b,cdb_tablespacesc,v$tablespaced,V$CONTAINERSwherea.snap_id=b.snap_idanda.tablespace_id=b.tablespace_band=b.tablespace_id=.con_idanda.con_id=c.con_idanda.con_id=d.con_idanda.con_id=e.con_idanda.tablespace_id=d.TS#andd.NAME=c.tablespace_nameandto_date(a.rtime,'mm/dd/yyyyhh24:mi:ss')>=sysdate-30orderbya.CON_ID,a.tablespace_id,to_date(a.rtime,'mm/dd/yyyyhh24:mi:ss')desc;估算oracle数据库，数据库对象的历史增长，最近7天数据库的增长，这只是一个估算selectsum(space_used_total)/1024/1024/1024"last7daysdbincrease-G"fromdba_hist_seg_stats,dba_hist_seg_stat_objo,dba_hist_snapshotsnwheres.obj#=o.obj#andssn.snap_id=s.snap_idandbegin_interval_time>sysdate-8orderbybegin_interval_time查看数据库历史增长情况此处是通过计算数据库Thehistoricalgrowthofalltablespacesisusedtocalculatethedatabasehistoricalsituation.不包含undo和temp：withtmpas(selectrtime,sum(tablespace_usedsize_kb)tablespace_usedsize_kb,sum(tablespace_size_kb)tablespace_size_kbfrom(selectrtime,e.tablespace_id,(e.tablespace_usedsize)*(f.block_size)/1024tablespace_usedsize_kb,(e.tablespace_size)*(f.block_size)/1024tablespace_size_kbfromdba_hist_tbspc_space_usagee,dba_tablespacesf,v$tablespacegwheree.tablespace_id=g.TS#andf.tablespace_name=g.NAMEandf.contentsnotin('TEMPORARY','UNDO'))groupbyrtime)selecttmp.rtime,tablespace_usedsize_kb,tablespace_size_kb-usedsize,(kspaceLAG(tablespace_usedsize_kb,1,NULL)OVER(ORDERBYtmp.rtime))ASDIFF_KBfromtmp,(selectmax(rtime)rtimefromtmpgroupbysubstr(rtime,1,10))t2wheret2.rtime=tmp.rtime;含undo和temp：withtmpas(selectmin(rtime)rtime,sum(tablespace_usedsize_kb)tablespace_usedsize_kb,sum(tablespace_size_kb)tablespace_size_kbfrom(selectrtime,e.tablespace_id,(e.tablespace_usedsize)*(f.block_size)/1024tablespace_usedsize_kb,(e.tablespace_size)*(f.block_size)/1024tablespace_size_kbfromdba_hist_tbspc_space_usagee,dba_tablespacesf,v$tablespacegwheree.tablespace_id=g.TS#andf.tablespace_name=g.NAME)groupbyrtime)selecttmp.rtime,tablespace_usedsize_kb,tablespace_size_kb,(tablespace_usedsize_kb-LAG(tablespace_usedsize_kb,1,NULL)tmp.rtimeDERB)YASDIFF_KBfromtmp,(selectmin(rtime)rtimefromtmpgroupbysubstr(rtime,1,10))t2whereet2.rtime=tmp.rtime列出快照时间内相关段对象使用空间的历史变化信息selectobj.owner,obj.object_name,to_char(sn.BEGIN_INTERVAL_TIME,'RRRR-MON-DD')start_day,sum(a.db_block_changes_delta)block_increasefromdba_hist_seg_stata,dba_hist_snapshotsn,dba_objectsobjwheresn.snap_id=a.snap_idandobj.object_id=a.obj#andobj.ownernotin('SYS','SYSTEM')andend_interval_timebetweento_timestamp('01-OCT-2019','DD-MON-RRRR')andto_timestamp('09-OCT-2019','DD-MON-RRRR')groupbyobj.owner,obj.object_name,to_char(sn.BEGIN_INTERVAL_TIME,'RRRR-MON-DD')orderbyobj.owner,obj.object_name;
