Most production databases grow over the course of time. Planning for growth is a very important task of every professional Oracle DBA. such problems as the system being out of space are likely to be avoided. It is very good when the DBA proactively resolves such space related issues.
One of the most important features of Oracle10g is its ability to predict the growth of the segments. The object_growth_trend prediction mechanism is based on data collected and
stored by the AWR, and the growth trend reporting is also built into the Oracle database kernel and is available by default. This provides sufficient information, over time, to perform growth trending of individual objects in the database as well as the database as a whole.
The query below allows the estimation of the segment current allocate space for ICME.ICME_NOPROJECT_SCORE TABLE.
icmedb>select bytes from dba_segments where owner=’ICME’ AND segment_name=’ICME_NOPROJECT_SCORE';
BYTES
———-
4701814784
Note: the following Script requires AWR license
The query below allows the estimation of the segment growth trend for the ‘ICME.ICME_NOPROJECT_SCORE’ table last three days:
alter session set nls_timestamp_format='RRRR-MM-DD hh24:mi:ss';
col TIMEPOINT for a30
SELECT *
FROM TABLE(DBMS_SPACE.object_growth_trend ('ICME','ICME_NOPROJECT_SCORE','TABLE',null,sysdate-3))
ORDER BY timepoint;
TIMEPOINT      SPACE_USAGE SPACE_ALLOC QUALITY
------------------------------ ----------- ----------- --------------------
2012-12-23 10:54:53 4408834129 4634705920 GOOD
2012-12-23 13:18:53 4408850465 4634705920 INTERPOLATED
2012-12-23 15:42:53 4409037936 4634705920 INTERPOLATED
2012-12-23 18:06:53 4409103113 4634705920 INTERPOLATED
2012-12-23 20:30:53 4409260669 4634705920 INTERPOLATED
2012-12-23 22:54:53 4409456318 4634705920 GOOD
2012-12-24 01:18:53 4409517617 4634705920 INTERPOLATED
2012-12-24 03:42:53 4409539129 4634705920 INTERPOLATED
2012-12-24 06:06:53 4409544108 4634705920 INTERPOLATED
2012-12-24 08:30:53 4409233698 4634705920 INTERPOLATED
2012-12-24 10:54:53 4410617460 4634705920 GOOD
2012-12-24 13:18:53 4413688741 4701814784 INTERPOLATED
2012-12-24 15:42:53 4414351973 4701814784 INTERPOLATED
2012-12-24 18:06:53 4414435291 4701814784 INTERPOLATED
2012-12-24 20:30:53 4416715906 4701814784 INTERPOLATED
2012-12-24 22:54:53 4417056254 4701814784 GOOD
2012-12-25 01:18:53 4417161166 4701814784 INTERPOLATED
2012-12-25 03:42:53 4417163794 4701814784 INTERPOLATED
2012-12-25 06:06:53 4417149112 4701814784 INTERPOLATED
2012-12-25 08:30:53 4416864781 4701814784 INTERPOLATED
2012-12-25 10:54:53 4418027333 4701814784 GOOD
2012-12-25 13:18:53 4418637343 4701814784 INTERPOLATED
2012-12-25 15:42:53 4419910142 4701814784 INTERPOLATED
2012-12-25 18:06:53 4422665830 4701814784 INTERPOLATED
2012-12-25 20:30:53 4423143461 4701814784 INTERPOLATED
2012-12-25 22:54:53 4423431886 4701814784 GOOD
2012-12-26 01:18:53 4423642425 4701814784 INTERPOLATED
2012-12-26 03:42:53 4423656843 4701814784 INTERPOLATED
2012-12-26 06:06:53 4423656843 4701814784 INTERPOLATED
2012-12-26 08:30:53 4423429547 4701814784 INTERPOLATED
2012-12-26 10:54:53 4423903669 4701814784 INTERPOLATED
2012-12-26 13:18:53 4427752546 4761346841 PROJECTED
2012-12-26 15:42:53 4428362817 4764323444 PROJECTED
2012-12-26 18:06:53 4428973089 4767300047 PROJECTED
2012-12-26 20:30:53 4429583360 4770276650 PROJECTED
2012-12-26 22:54:53 4430193632 4773253253 PROJECTED
The space_usage column shows how many bytes the ICME.ICME_NOPROJECT_SCORE table actually consumes, and space_alloc reports the size, in bytes, of space used by the table.
QUALITY: A value indicating how well the requested reporting interval matches the actual recording of statistics. This information is useful because there is no guaranteed reporting interval for object size use statistics, and the actual reporting interval varies over time and from object to object.
The values of the QUALITY column are:
GOOD: The value whenever the value of TIME is based on recorded statistics with a recorded timestamp within 10% of the INTERVAL specified in the input parameters.
INTERPOLATED: The value did not meet the criteria for GOOD, but was based on recorded statistics before and after the value of TIME. Current in-memory statistics can be collected across all instances in a cluster and treated as the “recorded” value for the present time.
PROJECTION: The value of TIME is in the future as of the time the table was produced. In an Oracle Real Application Clusters environment, the rules for recording statistics allow each instance to choose independently which objects will be selected.
what is the value for 3rd parameter?
TABLE/INDEX/TABLE PARTITION/INDEX PARTITION/…
May lead to some bug in different versions
bug
EXCEPTION in chrow processing – code: -14551 msg: ORA-14551: cannot perform a DML operation inside a query
Solution
set serveroutput off
bug
ORA-600(ktspNextL1:4)
Solution
1. Ignore the error.
2. Upgrade to 11.1.0.7.
3. Rerun the job.
Oracle saves a lot of information about the oracle DB history , So we access some historic tables (included in the Diagnostic Pack) get this.
col stime for a20
col object_name for a30
SELECT to_char(savtime,'yyyy-mm-dd hh24:mi') stime,owner,object_name,rowcnt,blkcnt
FROM sys.WRI$_OPTSTAT_TAB_HISTORY w,
dba_objects o
WHERE
o.owner='ICME'
AND o.object_name='ICME_NOPROJECT_SCORE'
and o.object_id = W.OBJ#
ORDER BY w.savtime,o.owner, o.object_name;
noties the WRI$_OPTSTAT_TAB_HISTORY don't contains all the table information.
select  obj.owner, obj.object_name,
to_char(sn.BEGIN_INTERVAL_TIME,'RRRR-MON-DD') start_day,
sum(a.db_block_changes_delta) block_increase
from     dba_hist_seg_stat a,
dba_hist_snapshot sn,
dba_objects obj
where    sn.snap_id = a.snap_id
and     obj.object_id = a.obj#
and     obj.owner not in ('SYS','SYSTEM')
and     obj.object_name='ICME_NOPROJECT_SCORE'
and     end_interval_time between SYSDATE-10
and     SYSDATE
group by obj.owner, obj.object_name,
to_char(sn.BEGIN_INTERVAL_TIME,'RRRR-MON-DD')
order by obj.owner, obj.object_name
OWNER       OBJECT_NAME        START_DAY  BLOCK_INCREASE
--------------- ------------------ -------------- --------------
ICME        ICME_NOPROJECT_SCORE 2012-DEC-18  320
ICME        ICME_NOPROJECT_SCORE 2012-DEC-19  115376
ICME        ICME_NOPROJECT_SCORE 2012-DEC-20  102096
ICME        ICME_NOPROJECT_SCORE 2012-DEC-21  274576
ICME        ICME_NOPROJECT_SCORE 2012-DEC-22  31008
ICME        ICME_NOPROJECT_SCORE 2012-DEC-23  10928
ICME        ICME_NOPROJECT_SCORE 2012-DEC-24  131056
ICME        ICME_NOPROJECT_SCORE 2012-DEC-25  124080
ICME        ICME_NOPROJECT_SCORE 2012-DEC-26  30480
or show the detail by wrh$_seg_stat by AWR table for every snapshot,using following statement
select ss.begin_interval_time, space_used_delta, space_allocated_delta
from sys.wrh$_seg_stat st, sys.wrm$_snapshot ss,dba_objects obj
where st.snap_id = ss.snap_id and st.obj#=obj.object_id
--AND ss.begin_interval_time >SYSDATE-7
and obj.owner='ICME' AND OBJECT_NAME='ICME_NOPROJECT_SCORE'
order by st.snap_id;
other script:
select c.segment_type, sum(space_used_delta) / 1024 / 1024 "Space used (M)", sum(c.bytes) / 1024 / 1024 "Total Space (M)",
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) || '%' "Percent of Total Disk Usage"
from
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where end_interval_time > trunc(sysdate) - &days_back
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and space_used_delta > 0
and c.owner = '&schema_name'
group by rollup(segment_type);
SEGMENT_TYPE     Space used (M) Total Space (M) Percent of Total Disk Usage
------------------ -------------- --------------- -----------------------------------------
INDEX           1307.84976   663101.438 .2%
TABLE           54.8532715   1121512 0%
1362.70303   1784613.44 .08%
column "Percent of Total Disk Usage" justify right format 999.99
column "Space Used (MB)" justify right format 9,999,999.99
column "Total Object Size (MB)" justify right format 9,999,999.99
set linesize 150
set pages 80
set feedback off
select * from (select to_char(end_interval_time, 'MM/DD/YY') mydate, sum(space_used_delta) / 1024 / 1024 "Space used (MB)", avg(c.bytes) / 1024 / 1024 "Total Object Size (MB)",
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) "Percent of Total Disk Usage"
from
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where begin_interval_time > trunc(sysdate) -&days_back
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and c.segment_name = '&segment_name'
group by to_char(end_interval_time, 'MM/DD/YY'))
order by to_date(mydate, 'MM/DD/YY');
Enter value for days_back: 5
old 8: where begin_interval_time > trunc(sysdate) -&days_back
new 8: where begin_interval_time > trunc(sysdate) -5
Enter value for segment_name: ICME_NOPROJECT_SCORE
old 13: and c.segment_name = '&segment_name'
new 13: and c.segment_name = 'ICME_NOPROJECT_SCORE'
MYDATE   Space used (MB) Total Object Size (MB) Percent of Total Disk Usage
-------- --------------- ---------------------- ---------------------------
12/21/12        6.27          4,484.00                 .01
12/22/12        2.27          4,484.00                 .00
12/23/12         .95          4,484.00                 .00
12/24/12        7.27          4,484.00                 .01
12/25/12        6.08          4,484.00                 .01
12/26/12        2.01          4,484.00                 .00
Script #2: List object growth over last N days, sorted by tablespace growth asc
COL segment_name FOR A30 HEADING "Object Name"
SELECT *
FROM ( SELECT c.TABLESPACE_NAME,
c.segment_name,
b.object_type,
ROUND (SUM (space_used_delta) / 1024 / 1024, 2) "Growth (MB)"
FROM dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
WHERE   begin_interval_time > TRUNC (SYSDATE) - &days_back
AND sn.snap_id = a.snap_id
AND b.object_id = a.obj#
AND b.owner = c.owner
AND b.object_name = c.segment_name
AND c.owner = 'ICME'
GROUP BY c.TABLESPACE_NAME, c.segment_name, b.object_type)
ORDER BY 1, 4 ASC;
Script #3: To check Growth rate of Tablespace.
SELECT TO_CHAR (sp.begin_interval_time, 'YYYY-MM-DD') days,
ts.tsname,
MAX (
ROUND ( (tsu.tablespace_size * dt.block_size) / (1024 * 1024), 2))
cur_size_MB,
MAX (
ROUND ( (tsu.tablespace_usedsize * dt.block_size) / (1024 * 1024),
2))
usedsize_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu,
DBA_HIST_TABLESPACE_STAT ts,
DBA_HIST_SNAPSHOT sp,
DBA_TABLESPACES dt
WHERE tsu.tablespace_id = ts.ts#
AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name
AND ts.tsname NOT IN ('SYSAUX', 'SYSTEM')
GROUP BY TO_CHAR (sp.begin_interval_time, 'YYYY-MM-DD'), ts.tsname
ORDER BY ts.tsname, days;
Script #4: List object growth over last N days, sorted by growth
COL segment_name FOR A30 HEADING "Object Name"
SELECT *
FROM ( SELECT c.TABLESPACE_NAME,
c.segment_name,
b.object_type,
ROUND (SUM (space_used_delta) / 1024 / 1024, 2) "Growth (MB)"
FROM dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
WHERE begin_interval_time > TRUNC (SYSDATE) - &days_back
AND sn.snap_id = a.snap_id
AND b.object_id = a.obj#
AND b.owner = c.owner
AND b.object_name = c.segment_name
AND c.owner = '&SCHEMANAME'
GROUP BY c.TABLESPACE_NAME, c.segment_name, b.object_type)
ORDER BY 1,4 ASC;
References Donald K. Burleson.article

--转自