表空间预测

1.通过脚本实现，每天收集信息，做一个线性分析

#! /bin/ksh

#set -x

SID=$1

ORACLE_SID=stat10g
ORACLE_HOME=/oracle10g/product/10.2
PATH=$PATH:/usr/bin:/usr/sbin:$ORACLE_HOME/bin:.:/usr/local/bin
export ORACLE_SID ORACLE_HOME PATH

DATE=`date +%Y%m%d`
BASEDIR=/home/oracle/monitor/segment_size
LOGDIR=/database/log/segment_size

#Window DBs
DATABASE="boeprod testdb"

#LOGG=/${BASEDIR}/win.log
#>LOGG

for i in ${DATABASE}
do
LOG=/${LOGDIR}/log/${i}_`date '+%Y%m%d'`.log

sqlplus oper/oper123<< EOF > ${LOG}
set echo off
--heading on feedback on head on serveroutput on
prompt "tbs utilization:"
set line 142;
set pagesize 1000;
col tablespace_name for a30;
set num 10;
col instance_name for a15
col used_ for 999,990.90;
col free_ for a15;
col free_percentage for a15;

select a.tablespace_name,all_sum "all_sum(M)",
to_char(nvl(free_sum,0),'9,999,990.00')||'M' free_,
to_char(100*nvl(free_sum,0)/all_sum,'900.00')||'%' free_percentage,instance_name
from
(select tablespace_name,sum(bytes)/1024/1024 all_sum
from dba_data_files@${i}
group by tablespace_name) a,
(select tablespace_name,sum(bytes)/1024/1024 free_sum
from dba_free_space@${i}
group by tablespace_name) c, v\$instance@${i}
where
a.tablespace_name = c.tablespace_name(+)
order by 4
/

prompt "tbs percentage utilization:"
--tbs percentage utilization
select tablespace_name, to_char(sysdate,'YYYY-MM') "Date", org_mb total, free_mb free, pct_free pct_free,
--CASE WHEN ( (((0.8*org_mb)-free_mb)/(1-0.8)) < 1) THEN 0
-- ELSE (((0.8*org_mb)-free_mb)/(1-0.8))
-- END as "Free80%-Add",
CASE WHEN ( (((0.2*org_mb)-free_mb)/(1-0.2)) < 1) THEN 0
ELSE (((0.2*org_mb)-free_mb)/(1-0.2))
END as "Free20%-Add",
CASE WHEN ( (((0.17*org_mb)-free_mb)/(1-0.17)) < 1) THEN 0
ELSE (((0.17*org_mb)-free_mb)/(1-0.17))
END as "Free17%-Add",
CASE WHEN ( (((0.16*org_mb)-free_mb)/(1-0.16)) < 1) THEN 0
ELSE (((0.16*org_mb)-free_mb)/(1-0.16))
END as "Free16%-Add",
CASE WHEN ( (((0.15*org_mb)-free_mb)/(1-0.15)) < 1) THEN 0
ELSE (((0.15*org_mb)-free_mb)/(1-0.15))
END as "Free15%-Add"
from
( select a.tablespace_name
,((sum(a.bytes)/1024/1024)/max(b.Org_Mb))*100 Pct_free
,max(b.Org_Mb) Org_Mb
,max(b.Org_Mb) - sum(a.bytes)/1024/1024 Used_Mb
,sum(a.bytes)/1024/1024 Free_Mb
,max(a.bytes)/1024/1024 Max_Mb
from dba_free_space@${i} a,
(select tablespace_name ,sum(bytes)/1024/1024 Org_Mb from dba_data_files@${i} group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name
order by 1 desc
)
/

col file_name for a55
col tablespace_name for a30
select tablespace_name,file_name,autoextensible,sum(maxbytes)/1024/1024 totalbytes,sum(bytes)/1024/1024 bytes from dba_data_Files@${i} group by tablespace_name,file_name,autoextensible order by tablespace_name,file_name
/

select to_char(s1.run_date,'YYYYMMDD HH24:MI:SS') origin_date,s1.owner, s1.segment_type seg_type,s1.segment_name, s1.extents "last_exts", s2.extents "cur_exts", s1.bytes "last_bytes", s2.bytes "cur_bytes", round(((s2.bytes - s1.bytes)/s1.bytes)*100,2) "%Growth+"
from ${i}_segmon_statistics s1, dba_segments@${i} s2
where s1.segment_name = s2.segment_name
and s1.owner = s2.owner
and s1.segment_type = s1.segment_type
and nvl(s1.partition_name,'*') = nvl(s2.partition_name,'*')
and ( ((s2.extents - s1.extents)/s1.extents) * 100 >= &incr_percent or ((s2.bytes - s1.bytes)/s1.bytes)*100 >= &incr_percent)
and s2.bytes > &lower_bound
and s1.run_date = (select min(run_date) from ${i}_segmon_statistics)
order by 9,2,3,4
/

column capture_date format A11
column segment_name format A35
column segment_type format A15
column owner format A20
column MB format 99,999.99

select to_char(sysdate,'YYYY-MON-DD') capture_date,owner,segment_name,segment_type,tablespace_name,sum(bytes)/1024/1024 MB
from dba_segments@${i}
where owner not in ('OUTLN','SCOTT','SYSTEM')
group by owner,segment_name,segment_type,tablespace_name having (sum(bytes)/1024/1024 >5)
order by MB desc
/
exit;
EOF

cat ${LOG}
#/bin/mailx -s "Temporary monitor ${i} tablespace" a@bb.com < ${LOG};
#cat ${LOG}>>${LOGG}
done

###add for 12c cdb

for i in ${DATABASE_12C}
do

LOG=/${LOGDIR}/log/${i}_`date '+%Y%m%d'`.log

sqlplus c##oper/oper123<< EOF >> ${LOG}
set echo off
--heading on feedback on head on serveroutput on
prompt "tbs utilization:"
set line 142;
set pagesize 1000;
col tablespace_name for a30;
set num 10;
col instance_name for a15
col used_ for 999,990.90;
col free_ for a15;
col free_percentage for a15;

prompt"top20seg:"
--top20seg.sql
--def run_dt = sysdate - 6 months
def incr_percent = 0
def lower_bound = 65536
col owner for a12
col seg_type for a12
col segment_name for a35
col "%Growth+" for '999'
select to_char(s1.run_date,'YYYYMMDD HH24:MI:SS') origin_date,s1.owner, s1.segment_type seg_type,s1.segment_name, s1.extents "last_exts", s2.extents "cur_exts", s1.bytes "last_bytes", s2.bytes "cur_bytes", round(((s2.bytes - s1.bytes)/s1.bytes)*100,2) "%Growth+"
from ${i}_segmon_statistics s1, dba_segments@${i} s2
where s1.segment_name = s2.segment_name
and s1.owner = s2.owner
and s1.segment_type = s1.segment_type
and nvl(s1.partition_name,'*') = nvl(s2.partition_name,'*')
and ( ((s2.extents - s1.extents)/s1.extents) * 100 >= &incr_percent or ((s2.bytes - s1.bytes)/s1.bytes)*100 >= &incr_percent)
and s2.bytes > &lower_bound
and s1.run_date = (select min(run_date) from ${i}_segmon_statistics)
order by 9,2,3,4
/

column capture_date format A11
column segment_name format A35
column segment_type format A15
column owner format A20
column MB format 99,999.99

cat ${LOG}
#/bin/mailx -s "Temporary monitor ${i} tablespace" a@bb.com < ${LOG};
#cat ${LOG}>>${LOGG}
done
#######

2。

LOBSEGMENT过大的处理

http://blog.csdn.net/lijun_blue/article/details/7238589

3.sample:

1.cd /database/log/segment_size/log_<db_name>
grep 204288 *

dbprod_segments_20170829.log:TB_L 204288 20,182.63M 09.88% dbprod
dbprod_segments_20170830.log:TB_L 204288 19,699.63M 09.64% dbprod
dbprod_segments_20170831.log:TB_L 204288 30,214.19M 14.79% dbprod

sed -n '61,72p' dbprod_segments_20170830.log
sed -n '61,72p' dbprod_segments_20170831.log

--db_CONT_AUDIT_LOG decrease 5G
--db_DAILY_INVENTORY decrease 5G

SQL>
CAPTURE_DAT OWNER SEGMENT_NAME TYPE TABLESPACE_NAME MB
----------- -------------------- ----------------------------------- --------------- ------------------------------ ----------
2017-AUG-30 dbDATA db_CONT_AUDIT_LOG TABLE PARTITION TB_L 34,035.63
2017-AUG-30 dbDATA db_SUMM_CONT_YARD_INVENTORY TABLE TB_L 22,519.00
2017-AUG-30 dbDATA db_DAILY_INVENTORY TABLE PARTITION TB_L 15,023.94
2017-AUG-30 dbDATA db_CONT_YC_MOVEMENT TABLE PARTITION TB_L 10,422.69
2017-AUG-30 dbDATA db_SUMM_IN_YARD_DWELL_TIME TABLE TB_L 8,388.00
2017-AUG-30 dbDATA db_CONT_AUDIT_LOG_PK INDEX IDX_L 8,168.00
2017-AUG-30 dbDATA db_SUMM_GATE_MOVEMENT TABLE TB_L 7,839.00
2017-AUG-30 dbDATA db_CONT_HOLD_RELEASE TABLE TB_L 6,119.00
2017-AUG-30 dbDATA db_CONFIRMED_GATE_MOVEMENT TABLE PARTITION TB_L 4,938.81

3.
sed -n '61,72p' dbprod_segments_20170926.log
sed -n '61,72p' dbprod_segments_20170927.log

4.趋势，每天长400M,剩余空间20+16=36, 支持40天。20170927
　
dbprod_segments_20170919.log:TB_L 204288 23,678.56M 11.59% dbprod
dbprod_segments_20170920.log:TB_L 204288 23,199.56M 11.36% dbprod
dbprod_segments_20170921.log:TB_L 204288 22,733.56M 11.13% dbprod
dbprod_segments_20170922.log:TB_L 204288 22,337.56M 10.93% dbprod
dbprod_segments_20170923.log:TB_L 204288 21,943.56M 10.74% dbprod
dbprod_segments_20170924.log:TB_L 204288 21,576.56M 10.56% dbprod
dbprod_segments_20170925.log:TB_L 204288 21,285.56M 10.42% dbprod
dbprod_segments_20170926.log:TB_L 204288 20,891.56M 10.23% dbprod
dbprod_segments_20170927.log:TB_L 204288 20,452.00M 10.01% dbprod

TB_L /dbproddb/data/TB_l_50.dbf YES 4096 8192

TB_L /dbproddb/data/TB_l_51.dbf YES 4096 8192

TB_L /dbproddb/data/TB_l_52.dbf YES 4096 8192

TB_L /dbproddb/data/TB_l_53.dbf YES 4096 8192

###转载

-for tablespace;

https://www.oraclenext.com/2014/02/tablespace-growth-history-and-expected.html

Tablespace Growth History and Forecast for 10g and 11g

Finding space usage of tablespaces and database is what many DBAs want to find. In this article I will explain how to find out space usage history and forecasting future growth of tablespaces. For 10g and 11g database growth history and forecast see script Database Growth History and Forecast for 10g and 11g.
For 12c and above; Tablespace and Database growth history and forecast, please use following scripts
Tablespace Growth History and Forecast for 12c and Above.
Database Growth History and Forecast for 12c and Above.
For segments space usage history and forecast, see this document
Starting Oracle 10G, Oracle records tablespaces usage (allocated, used etc.) in AWR which can be retrieved by querying the data dictionary view dba_hist_tbspc_space_usage. Following scripts can be used to view the history of tablespace(s) usage and predict the expected growth for the future. Growth forecast is based on daily growth in the past.

Things to note:
1) This script is based on AWR. If your AWR retention period is 7 days, this script can only tell the growth history of last 7 days and predict based on last 7 days growth. I would recommend to change AWR retention to at least 35 days - this will also be more helpful in case of performance tuning situation as you will have a longer window from the past to look into for performance comparisons.
2) You may edit this scrip according to your requirement to forecast for a period which suites your requirements. By default it will predict expected growth for next 30, 60 and 90 days.
3) Save this code in an sql script.
4) Log in as user SYS on SQLPLUS and execute the script or copy and paste the following code. You will be prompted for the tablespace name

Script for Single Tablespace

##############################################
set serverout on
set verify off
set lines 200
set pages 2000
DECLARE
v_ts_id number;
not_in_awr EXCEPTION;
v_ts_name varchar2(200) := UPPER('&Tablespace_Name');
v_ts_block_size number;
v_begin_snap_id number;
v_end_snap_id number;
v_begin_snap_date date;
v_end_snap_date date;
v_numdays number;
v_ts_begin_size number;
v_ts_end_size number;
v_ts_growth number;
v_count number;
v_ts_begin_allocated_space number;
v_ts_end_allocated_space number;
BEGIN
SELECT ts# into v_ts_id FROM v$tablespace where name = v_ts_name;
SELECT count(*) INTO v_count FROM dba_hist_tbspc_space_usage where tablespace_id=v_ts_id;
IF v_count = 0 THEN
RAISE not_in_awr;
END IF ;
SELECT block_size into v_ts_block_size FROM dba_tablespaces where tablespace_name = v_ts_name;
SELECT min(snap_id), max(snap_id), min(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS'))), max(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS')))
into v_begin_snap_id,v_end_snap_id, v_begin_snap_date, v_end_snap_date from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id;
v_numdays := v_end_snap_date - v_begin_snap_date;

SELECT round(max(tablespace_size)*v_ts_block_size/1024/1024,2) into v_ts_begin_allocated_space from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_begin_snap_id;
SELECT round(max(tablespace_size)*v_ts_block_size/1024/1024,2) into v_ts_end_allocated_space from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_end_snap_id;
SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024,2) into v_ts_begin_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_begin_snap_id;
SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024,2) into v_ts_end_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_end_snap_id;
v_ts_growth := v_ts_end_size - v_ts_begin_size;
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Tablespace Block Size: '||v_ts_block_size);
DBMS_OUTPUT.PUT_LINE('---------------------------');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Summary');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space: '||v_ts_end_allocated_space||' MB'||' ('||round(v_ts_end_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Used Space: '||v_ts_end_size||' MB'||' ('||round(v_ts_end_size/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space Percentage: '||round(v_ts_end_size/v_ts_end_allocated_space*100,2)||' %');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('History');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space on '||v_begin_snap_date||': '||v_ts_begin_allocated_space||' MB'||' ('||round(v_ts_begin_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Current Allocated Space on '||v_end_snap_date||': '||v_ts_end_allocated_space||' MB'||' ('||round(v_ts_end_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space on '||v_begin_snap_date||': '||v_ts_begin_size||' MB'||' ('||round(v_ts_begin_size/1024,2)||' GB)' );
DBMS_OUTPUT.PUT_LINE('4) Current Used Space on '||v_end_snap_date||': '||v_ts_end_size||' MB'||' ('||round(v_ts_end_size/1024,2)||' GB)' );
DBMS_OUTPUT.PUT_LINE('5) Total growth during last '||v_numdays||' days between '||v_begin_snap_date||' and '||v_end_snap_date||': '||v_ts_growth||' MB'||' ('||round(v_ts_growth/1024,2)||' GB)');
IF (v_ts_growth <= 0 OR v_numdays <= 0) THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('!!! NO DATA GROWTH WAS FOUND FOR TABLESPCE '||V_TS_NAME||' !!!');
ELSE
DBMS_OUTPUT.PUT_LINE('6) Per day growth during last '||v_numdays||' days: '||round(v_ts_growth/v_numdays,2)||' MB'||' ('||round((v_ts_growth/v_numdays)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Expected Growth');
DBMS_OUTPUT.PUT_LINE('===============');
DBMS_OUTPUT.PUT_LINE('1) Expected growth for next 30 days: '|| round((v_ts_growth/v_numdays)*30,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*30)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Expected growth for next 60 days: '|| round((v_ts_growth/v_numdays)*60,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*60)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Expected growth for next 90 days: '|| round((v_ts_growth/v_numdays)*90,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*90)/1024,2)||' GB)');
END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('!!! TABLESPACE DOES NOT EXIST !!!');
WHEN NOT_IN_AWR THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('!!! TABLESPACE USAGE INFORMATION NOT FOUND IN AWR !!!');

END;
/
##############################################

Sample Output

Enter value for tablespace_name: TEST

Tablespace Block Size: 8192
---------------------------

Summary
========
1) Allocated Space: 2048 MB (2 GB)
2) Used Space: 1558.44 MB (1.52 GB)
3) Used Space Percentage: 76.1 %

History
========
1) Allocated Space on 06-DEC-14: 2048 MB (2 GB)
2) Current Allocated Space on 10-JAN-15: 2048 MB (2 GB)
3) Used Space on 06-DEC-14: 1273 MB (1.24 GB)
4) Current Used Space on 10-JAN-15: 1558.44 MB (1.52 GB)
5) Total growth during last 35 days between 06-DEC-14 and 10-JAN-15: 285.44 MB (.28 GB)
6) Per day growth during last 35 days: 8.16 MB (.01 GB)

Expected Growth
===============
1) Expected growth for next 30 days: 244.66 MB (.24 GB)
2) Expected growth for next 60 days: 489.33 MB (.48 GB)
3) Expected growth for next 90 days: 733.99 MB (.72 GB)

PL/SQL procedure successfully completed.

/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/

Script for All Tablespace (Except UNDO and TEMP)
set serverout on
set verify off
set lines 200
set pages 2000
DECLARE
v_ts_id number;
not_in_awr EXCEPTION;
v_ts_block_size number;
v_begin_snap_id number;
v_end_snap_id number;
v_begin_snap_date date;
v_end_snap_date date;
v_numdays number;
v_ts_begin_size number;
v_ts_end_size number;
v_ts_growth number;
v_count number;
v_ts_begin_allocated_space number;
v_ts_end_allocated_space number;
cursor v_cur is select tablespace_name from dba_tablespaces where contents='PERMANENT';

BEGIN
FOR v_rec in v_cur
LOOP
BEGIN
SELECT ts# into v_ts_id FROM v$tablespace where name = v_rec.tablespace_name;
SELECT count(*) INTO v_count FROM dba_hist_tbspc_space_usage where tablespace_id=v_ts_id;
IF v_count = 0 THEN
RAISE not_in_awr;
END IF ;
SELECT block_size into v_ts_block_size FROM dba_tablespaces where tablespace_name = v_rec.tablespace_name;
SELECT min(snap_id), max(snap_id), min(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS'))), max(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS')))
into v_begin_snap_id,v_end_snap_id, v_begin_snap_date, v_end_snap_date from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id;
v_numdays := v_end_snap_date - v_begin_snap_date;

SELECT round(max(tablespace_size)*v_ts_block_size/1024/1024,2) into v_ts_begin_allocated_space from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_begin_snap_id;
SELECT round(max(tablespace_size)*v_ts_block_size/1024/1024,2) into v_ts_end_allocated_space from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_end_snap_id;
SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024,2) into v_ts_begin_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_begin_snap_id;
SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024,2) into v_ts_end_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_end_snap_id;
v_ts_growth := v_ts_end_size - v_ts_begin_size;
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE(v_rec.tablespace_name||' Tablespace');
DBMS_OUTPUT.PUT_LINE('--------------------');
DBMS_OUTPUT.PUT_LINE('Tablespace Block Size: '||v_ts_block_size);
DBMS_OUTPUT.PUT_LINE('---------------------------');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Summary');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space: '||v_ts_end_allocated_space||' MB'||' ('||round(v_ts_end_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Used Space: '||v_ts_end_size||' MB'||' ('||round(v_ts_end_size/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space Percentage: '||round(v_ts_end_size/v_ts_end_allocated_space*100,2)||' %');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('History');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space on '||v_begin_snap_date||': '||v_ts_begin_allocated_space||' MB'||' ('||round(v_ts_begin_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Current Allocated Space on '||v_end_snap_date||': '||v_ts_end_allocated_space||' MB'||' ('||round(v_ts_end_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space on '||v_begin_snap_date||': '||v_ts_begin_size||' MB'||' ('||round(v_ts_begin_size/1024,2)||' GB)' );
DBMS_OUTPUT.PUT_LINE('4) Current Used Space on '||v_end_snap_date||': '||v_ts_end_size||' MB'||' ('||round(v_ts_end_size/1024,2)||' GB)' );
DBMS_OUTPUT.PUT_LINE('5) Total growth during last '||v_numdays||' days between '||v_begin_snap_date||' and '||v_end_snap_date||': '||v_ts_growth||' MB'||' ('||round(v_ts_growth/1024,2)||' GB)');
IF (v_ts_growth <= 0 OR v_numdays <= 0) THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('!!! NO DATA GROWTH WAS FOUND FOR TABLESPACE '||v_rec.tablespace_name||' !!!');
ELSE
DBMS_OUTPUT.PUT_LINE('6) Per day growth during last '||v_numdays||' days: '||round(v_ts_growth/v_numdays,2)||' MB'||' ('||round((v_ts_growth/v_numdays)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Expected Growth');
DBMS_OUTPUT.PUT_LINE('===============');
DBMS_OUTPUT.PUT_LINE('1) Expected growth for next 30 days: '|| round((v_ts_growth/v_numdays)*30,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*30)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Expected growth for next 60 days: '|| round((v_ts_growth/v_numdays)*60,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*60)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Expected growth for next 90 days: '|| round((v_ts_growth/v_numdays)*90,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*90)/1024,2)||' GB)');
END IF;
DBMS_OUTPUT.PUT_LINE('/\/\/\/\/\/\/\/\/\/\/\/ END \/\/\/\/\/\/\/\/\/\/\/\');

EXCEPTION
WHEN NOT_IN_AWR THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE(v_rec.tablespace_name||' Tablespace');
DBMS_OUTPUT.PUT_LINE('--------------------');
DBMS_OUTPUT.PUT_LINE('Tablespace Block Size: '||v_ts_block_size);
DBMS_OUTPUT.PUT_LINE('---------------------------');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('!!! TABLESPACE USAGE INFORMATION NOT FOUND IN AWR !!!');
DBMS_OUTPUT.PUT_LINE('/\/\/\/\/\/\/\/\/\/\/\/ END \/\/\/\/\/\/\/\/\/\/\/\');
NULL;
END;
END LOOP;
END;
/

Please give your feedback if you face any difficulty in executing these scripts or if these scripts do not work for you.

for segmnets:

http://salmandba.blogspot.com/2017/11/segment-space-growth-history-and.html

Segment Space Growth History and Forecast

I have already written articles to get tablespace space usage history and forecast (10g and 11g, 12c and above) and database space usage history and forecast (10g and 11g, 12c and above). Here, I will explain how we can get the same information about segments, and can forecast the future growth of segments.

Just like DBA_HIST_TBSPC_SPACE_USAGE which we can use to find out tablespaces space usage history from the AWR repository, we have another view, DBA_HIST_SEG_STAT, that can be used to get the historical space usage for segments, based on which a future forecast can be made. However, usage of this view has certain limitations. Information in this view is updated on every AWR snapshot, and if an instance restart happens, segments growth information between last AWR snapshot and instance restart will be lost, and would not be available in AWR; hence information of segments size would not reflect a correct value. Nevertheless, we can get the data growth information of segments since the last instance restart because information will be correct. Once we get the historical space usage of segments, we can guess about their future growth.

I always suggest setting the AWR retention to 35 days at least, and AWR capture frequency set to 15 minutes. This document explains how we change the AWR snapshot interval and retention settings.

Initially I planned to write a single script just like I wrote for tablespace/database growth history and forecast, but I found it very complex, so I will use different queries and step to find the historical growth of a segment, based on which a forecast can be made for a future growth.

Following points should be noted before I explain the steps.

For 12c and above, execute these steps in the respective container in which the segment exists for which space usage information is needed.
For partitioned tables/indexes, each partition is a segment, and we will need to check sizing information for each partition separately. You can add up the resulted size of each partition to get the sizing information for the whole table/index.
Information queried using these steps, is taken from the AWR data stored in the database.
If you have AWR retention set to 35 days, but, there was an instance restart 3 days ago, these steps would provide information based on the last 3 days AWR data, because it has already been explained above that instance restart means that some information of data insertion/deletion in/from the segment could not have been written to the AWR.
Sizing information here is in MB. You may modify the queries to match your requirement, if you want information to be returned in GBs, or in any other unit.

Steps to Get Segment Growth and Forecast

1)
Get the object ID (and current size, if needed) for the segment about which segment space usage information is needed. For this example, I am using a table TEST which is in a RAC database with 3 instances. Same queries will run perfectly on single instance database.

SQL> select owner,object_id from dba_objects where object_name='TEST';

OWNER OBJECT_ID

------------------------------ ----------

SALMAN 427055

SQL> select bytes/1024/1024 SIZE_MB from user_segments where segment_name='TEST';

SIZE_MB

-------------

588

2)
Check the startup time of each instance. Most importantly, the startup time of the instance that has been started lately because query will fetch size information after that time, because it will be most reliable information as explained above.

SQL> select instance_number,max(startup_time) STARTUPT_TIME, TRUNC(sysdate-trunc(max(startup_time))) NUM_DAYS from dba_hist_snapshot group by instance_number order by 2;

INSTANCE_NUMBER STARTUP_TIME NUM_DAYS

----------------------------- -------------------------------------------------------------------------

2 31-OCT-17 02.59.47.000 PM 8

3 01-NOV-17 12.12.26.000 AM 7

1 01-NOV-17 06.07.33.000 PM 7

Based on above information, instance 1 was the one started lately, around 7 days ago. Our coming query to fetch the data growth information will be based on AWR data captured after 01-NOV-17 06.07.33.000 PM, that is the startup time of instance 1.

3)
Query in the above step gives information about the instance startup time. However, we need first AWR Snapshot ID after this instance restart time. Data growth information will be based on AWR data captured starting that snapshot until the latest snapshot.

SQL> select instance_number,min(snap_id) snap_id from dba_hist_snapshot where (instance_number,startup_time) in (select instance_number,max(startup_time) from dba_hist_snapshot group by instance_number) group by instance_number order by snap_id;

INSTANCE_NUMBER SNAP_ID

--------------- ----------

2 220220

3 220256

1 220328

So the snapshot ID 220328 is the first snapshot taken after the instance 1 startup on 01-NOV-17 06.07.33.000 PM

4)
Following is the query that will show how much the segment TEST has grown since last 7 days (Since startup of the instance 1), how much was per day growth in last 7 days, and how much it may grow during next 30 days.
Highlighted in yellow is the value to calculate the size growth per day for last n number of days, and highlighted in red is to calculate the expected growth in next n number of days. If our last instance startup was 20 days ago, the value highlighted in yellow will be 20. To forecast the growth for next 45 days, the values highlighted in red will be 20*45

SQL> select ROUND(SUM(space_allocated/1024/1024)) growth_mb, ROUND(SUM(space_allocated/1024/1024)/7) per_day_growth_mb,

ROUND(SUM(space_allocated/1024/1024)/7*30) expected_growth_mb_next30days

from (select max(space_allocated_total) space_allocated from dba_hist_seg_stat whereobj#=427055 and snap_id>=220328 group by instance_number);

GROWTH_MB PER_DAY_GROWTH_MB EXPECTED_GROWTH_MB_NEXT30DAYS

--------------------- ------------------------------------- ---------------------------------------------------------

128 18 549

This method of finding segment growth history and forecast best suites the steadily growing segments. Forecasting about a segment that has sudden growth in size or sporadically growing segments may not have very accurate sizing information using this method.

###sample

####create table
create table DBMGR.tabspace_est
(
db_name CHAR(6),
time CHAR(10),
tablespace_name VARCHAR2(30),
allocated_days_ago NUMBER,
allocated_cur NUMBER,
use_days_ago NUMBER,
use_cur NUMBER,
growth_total NUMBER,
growth_per_day NUMBER,
est_rem_day NUMBER
)

1) Allocated Space on 25 days ago : 3185.99858 GB
2) Current Allocated Space on today : 3729.99832 GB
3) Used Space on 25 days ago : 2949.81151 GB
4) Current Used Space on today : 3201.90086 GB
5) Total growth during last 25 days between : 252.08935 GB
6) Per day growth during last 25 days: 10.08 GB
7) Estimated remaining days : 52.37

#说明如下：

create table DBMGR.tabspace_est
(
db_name CHAR(6),
time CHAR(10),
tablespace_name VARCHAR2(30),
allocated_days_ago NUMBER, <-25天前的最大支持tablespace空间maxtablespacesize
allocated_cur NUMBER, <-今天的最大支持tablespace空间maxtablespacesize
use_days_ago NUMBER, <-25天的已经使用的tablespace空间 usetablespace
use_cur NUMBER, <-今天的已经使用的tablespace空间 usetablespace
growth_total NUMBER, <-25天时间增长的tablespace已经使用的空间
growth_per_day NUMBER, <-平均每天的增长量
est_rem_day NUMBER <-预计剩余的天将达到最大空间
)

#### 存储过程

drop procedure tablespace_check;
truncate table DBMGR.tabspace_est;

create or replace procedure tablespace_check
as
v_db_name VARCHAR2(20);
v_numdays number;
v_ts_begin_size number;
v_ts_end_size number;
v_ts_growth number;
v_count number;
v_ts_begin_allocated_space number;
v_ts_end_allocated_space number;
v_estimated_day number;
e_count number;
cursor v_cur is select distinct(db_name) from dbmgr.tabpct where time=to_char(sysdate,'yyyy-mm-dd');

begin
FOR v_rec in v_cur LOOP
/* DBMS_OUTPUT.PUT_LINE(v_rec.db_name||' db_name');
*/
DBMS_OUTPUT.ENABLE(buffer_size => null);
v_db_name := v_rec.db_name;
DBMS_OUTPUT.PUT_LINE(v_db_name||' database ');

/*end loop;
end;*/

FOR re IN (SELECT tablespace_name from dbmgr.tabpct where upper(db_name)=upper(v_db_name) and time=to_char(sysdate,'yyyy-mm-dd') and tablespace_name not like '%UNDO%') LOOP

DBMS_OUTPUT.PUT_LINE(re.tablespace_name);

SELECT count(*) into e_count from dbmgr.tabpct where tablespace_name=re.tablespace_name and upper(db_name)=upper(v_db_name) and time =to_char(sysdate-25,'yyyy-mm-dd');
if e_count > 0 then

SELECT "maxbyes_GB" into v_ts_begin_allocated_space from dbmgr.tabpct where tablespace_name=re.tablespace_name and upper(db_name)=upper(v_db_name) and time =to_char(sysdate-25,'yyyy-mm-dd') and rownum < 2;
DBMS_OUTPUT.PUT_LINE(v_ts_begin_allocated_space);

SELECT "maxbyes_GB" into v_ts_end_allocated_space from dbmgr.tabpct where tablespace_name=re.tablespace_name and upper(db_name)=upper(v_db_name) and time =to_char(sysdate,'yyyy-mm-dd') and rownum < 2;
SELECT "use_GB" into v_ts_begin_size from dbmgr.tabpct where tablespace_name=re.tablespace_name and upper(db_name)=upper(v_db_name) and time =to_char(sysdate-25,'yyyy-mm-dd') and rownum < 2;
SELECT "use_GB" into v_ts_end_size from dbmgr.tabpct where tablespace_name=re.tablespace_name and upper(db_name)=upper(v_db_name) and time =to_char(sysdate,'yyyy-mm-dd') and rownum < 2;
v_ts_growth := v_ts_end_size - v_ts_begin_size;

v_numdays := 25;

DBMS_OUTPUT.PUT_LINE(v_ts_growth);

if v_ts_growth > 0 then

v_estimated_day := round((v_ts_end_allocated_space-v_ts_end_size)/round(v_ts_growth/v_numdays,10),2);

else
v_estimated_day := 999999;

end if;

DBMS_OUTPUT.PUT_LINE(v_ts_begin_allocated_space);

DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE(re.tablespace_name||' Tablespace');
DBMS_OUTPUT.PUT_LINE('--------------------');
DBMS_OUTPUT.PUT_LINE('---------------------------');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Summary');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space: '||v_ts_end_allocated_space ||' GB ');
DBMS_OUTPUT.PUT_LINE('2) Used Space: '||v_ts_end_size||' GB');
DBMS_OUTPUT.PUT_LINE('3) Used Space Percentage: '||round(v_ts_end_size/v_ts_end_allocated_space*100,2)||' %');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('History');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space on '||'25 days ago '||': '||v_ts_begin_allocated_space||' GB');
DBMS_OUTPUT.PUT_LINE('2) Current Allocated Space on '||' today '||': '||v_ts_end_allocated_space||' GB');
DBMS_OUTPUT.PUT_LINE('3) Used Space on '||'25 days ago '||': '||v_ts_begin_size||' GB' );
DBMS_OUTPUT.PUT_LINE('4) Current Used Space on '||' today '||': '||v_ts_end_size||' GB' );
DBMS_OUTPUT.PUT_LINE('5) Total growth during last 25 days between '||': '||v_ts_growth||' GB');
IF (v_ts_growth < 0 OR v_numdays <= 0) THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('!!! NO DATA GROWTH WAS FOUND FOR TABLESPACE '||re.tablespace_name||' !!!');
ELSE
DBMS_OUTPUT.PUT_LINE('6) Per day growth during last 25 days: '||round(v_ts_growth/v_numdays,2)||' GB');
DBMS_OUTPUT.PUT_LINE('7) Estimated remaining days : '||v_estimated_day );

DBMS_OUTPUT.PUT_LINE(CHR(10));

END IF;

DBMS_OUTPUT.PUT_LINE('/\/\/\/\/\/\/\/\/\/\/\/ END \/\/\/\/\/\/\/\/\/\/\/\');

DBMS_OUTPUT.PUT_LINE('---------------------------');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('/\/\/\/\/\/\/\/\/\/\/\/ END \/\/\/\/\/\/\/\/\/\/\/\');

insert into DBMGR.tabspace_est values (v_db_name,to_char(sysdate,'yyyy-mm-dd'),re.tablespace_name,v_ts_begin_allocated_space,v_ts_end_allocated_space,v_ts_begin_size,v_ts_end_size,v_ts_growth,round(v_ts_growth/25,2),v_estimated_day);
end if;
END LOOP;
END LOOP;
COMMIT;
END;

##执行存储过程
exec tablespace_check;
##查看90天以内的扩展
select * from DBMGR.tabspace_est where est_rem_day < 90;

##存储过程转动态SQL

将

create or replace procedure tablespace_check
as

转成
DECLARE

即可

posted @ 2017-07-27 16:15 feiyun8616 阅读(273) 评论(0) 编辑收藏举报

刷新页面返回顶部

feiyun8616的作坊 (半个程序员and dba) 脚本化 python

表空间预测

Tablespace Growth History and Forecast for 10g and 11g

Segment Space Growth History and Forecast

公告