Thanks for the question, Fan.

Asked: February 15, 2003 - 6:15 pm UTC

Last updated: May 15, 2012 - 1:48 pm UTC

Version: 8.X

Viewed 10K+ times! This question is

You Asked

Hi,

I just wanted to calculate the monthly database growth. We are not using any tools for database monitoring. Pl. let me know how can I calculate the database growth per month, mean how much data size increased in the last month.

Thanks in advance,

Regards,

Fan kumar

and Tom said...

create some tables:

create table my_dba_data_files
as
select sysdate dt, dba_data_files.* from dba_data_files;

create table my_dba_segments
as
select sysdate dt, dba_segments.* from dba_segments;

and any others you are inserted in "watching". then create a procedure:

create procedure collect_sizes
as
begin
insert into my_dba_data_files select trunc(sysdate), dba_data_files.*
from dba_data_files;
insert into my ......
end;

and use dbms_job to schedule that to run on the first of each month. You'll have a history of exactly how large everything was for as far back as you wish to keep.

You can use that to see where you've been and more importantly -- where you are going.

Rating

(23 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

what if ?

A reader, March 04, 2004 - 1:25 pm UTC

I need to know as of today how much DB grew over the last year.Assuming I dont have those tables you asked to create.
Any clues to this sir? Any v$ tables storing such info or any system views ?

March 04, 2004 - 2:30 pm UTC

nope, if you didn't write down how big it was last year somewhere you won't be able to figure this out.

the 9i and before databases did not have a "long term memory"

database growth

kelly, February 08, 2006 - 4:42 pm UTC

How about free space and autoextent on? Don't we need to add those into consideration? My datafile can be 1000M but I may have 800M free space in there and it won't show the growth, will it?

Thanks,

February 10, 2006 - 8:52 am UTC

if you

a) measure the size of somthing

b) come back later and measure it again

then subtract from (B) the values of (A) you have just measured the growth.

I don't see what an autoextend datafile has to do with it?

how about 10g?

A reader, February 27, 2007 - 3:17 pm UTC

Hi Tom,

Does 10g have an easier/automatic way to get at this information?

February 27, 2007 - 3:40 pm UTC

8i did
7.x did

it was part of enterprise manager, the capacity planning pack.

Predicting the Growth of a Segment

TongucY, March 02, 2007 - 2:30 pm UTC

..
Just ask 10g; you will be surprised how accurately and intelligently it can make that prediction for you. You simply issue this query:

select * from
table(dbms_space.OBJECT_GROWTH_TREND
('ARUP','BOOKINGS','TABLE'));

The function dbms_space.object_growth_trend() returns record in PIPELINEd format, which can be displayed by the TABLE() casting. Here is the output:

TIMEPOINT SPACE_USAGE SPACE_ALLOC QUALITY
------------------------------ ----------- ----------- ------------
05-MAR-04 08.51.24.421081 PM 8586959 39124992 INTERPOLATED
06-MAR-04 08.51.24.421081 PM 8586959 39124992 INTERPOLATED
07-MAR-04 08.51.24.421081 PM 8586959 39124992 INTERPOLATED
08-MAR-04 08.51.24.421081 PM 126190859 1033483971 INTERPOLATED
09-MAR-04 08.51.24.421081 PM 4517094 4587520 GOOD
10-MAR-04 08.51.24.421081 PM 127469413 1044292813 PROJECTED
11-MAR-04 08.51.24.421081 PM 128108689 1049697234 PROJECTED
12-MAR-04 08.51.24.421081 PM 128747966 1055101654 PROJECTED
13-MAR-04 08.51.24.421081 PM 129387243 1060506075 PROJECTED
14-MAR-04 08.51.24.421081 PM 130026520 1065910496 PROJECTED
..
Reference: http://www.oracle.com/technology/pub/articles/10gdba/week15_10gdba.html

growth of a segment in 10g

deba, June 11, 2008 - 6:46 pm UTC

Hi,

I would like to find out growth of objects in my database. So I got dba_hist_seg_stat.

1) I would like to know the meanings of following 4 columns :

space_used_total
space_used_delta
SPACE_ALLOCATED_TOTAL
SPACE_ALLOCATED_DELTA

2) what is difference between space_used_delta and SPACE_ALLOCATED_DELTA ?

3) I can see lot of negative values in space_used_delta for indexes. Why is it negative ? Can you
give some example please ?

SELECT t.snap_id,space_used_total,space_used_delta,SPACE_ALLOCATED_TOTAL,SPACE_ALLOCATED_DELTA,object_name
FROM dba_hist_seg_stat t,dba_hist_snapshot s,dba_objects o
WHERE t.snap_id = s.snap_id AND s.snap_id IN (SELECT snap_id
FROM sys.dba_hist_snapshot
WHERE TO_CHAR(end_interval_time, 'HH24') = '00')
AND obj#=o.object_id
AND owner='STOWNER' AND space_used_delta< 0 --AND SPACE_ALLOCATED_DELTA > 0
ORDER BY snap_id DESC

SNAP_ID SPACE_USED_TOTAL SPACE_USED_DELTA SPACE_ALLOCATED_TOTAL SPACE_ALLOCATED_DELTA OBJECT_NAME
---------- ---------------- ---------------- --------------------- --------------------- --------------------------------
2787 426360 -2042 0 0 SSP_TMP_CMCL_BREAK_PATN_IDX1
2787 608500 -2042 0 0 ADC_SPOT_TXM_AREA_DTL_IDX3
2787 66976 -4084 0 0 ACF_CM_LINK_REQUEST_DTL_IDX1
2787 -2042 -4084 0 0 SDC_AUDDTL_CHGRP_CPT_IDX4
2787 19194 -4084 0 0 TRF_AVSUBROTATION_IDX2
2787 48598 -2042 0 0 SSP_CMCL_BREAK_PATTERN_IDX6
2787 13884 -12252 0 0 SSP_CMCL_BREAK_PATTERN_IDX3
2787 130676 -18378 0 0 SSP_PROGRAMME_BREAK_IDX3
2787 -4084 -4084 0 0 SSP_EPISODE_PK
2787 100464 -2042 0 0 TRF_AVROTATION_PATTERN_UK1
2787 -4084 -4084 0 0 TRF_COPY_UK1

4) I can find some indexes where space_used_delta is negative but SPACE_ALLOCATED_DELTA > 0. Why ?

SELECT t.snap_id,object_name,space_used_total,space_used_delta,SPACE_ALLOCATED_TOTAL,SPACE_ALLOCATED_DELTA
FROM dba_hist_seg_stat t,dba_hist_snapshot s,dba_objects o
WHERE t.snap_id = s.snap_id AND s.snap_id IN (SELECT snap_id
FROM sys.dba_hist_snapshot
WHERE TO_CHAR(end_interval_time, 'HH24') = '00')
AND obj#=o.object_id
AND owner='STOWNER' AND space_used_delta< 0 AND SPACE_ALLOCATED_DELTA > 0
ORDER BY snap_id DESC

SNAP_ID SPACE_USED_TOTAL SPACE_USED_DELTA SPACE_ALLOCATED_TOTAL SPACE_ALLOCATED_DELTA OBJECT_NAME
---------- ---------------- ---------------- --------------------- --------------------- ---------------------------
2551 2761840 -1139072 67108864 67108864 SSP_POST_TXM_INVENTORY_IDX3
2383 144970124 -2599050 469762048 67108864 SSP_POST_TXM_INVENTORY_IDX3
2311 7527008 -594932 33554432 8388608 SSP_POST_TXM_INVENTORY_IDX2

If possible , please try to give your famous examples.

Thanks
Deba

June 11, 2008 - 9:26 pm UTC

objects have allocated space - when you create a table, we allocate space, but we haven't USED any of it. Eventually you add a row and we use some (but not all) of the allocated space.

So, allocated is allocated - used is the "high water mark", where we've used space up to.

indexes grow and shrink their used space, when a block is put on the free list in an index, it is not "used" anymore (unlike a table)

an index that grew (allocated more space) and then had lots of deletes might be observed to have allocated grow, used shrink over that span of time.

growth of a segment in 10g

deba, June 12, 2008 - 5:00 pm UTC

Hi Tom,

After doing a test, I feel that data in dba_hist_seg_stat are not correct. Plesae see the test below:

SQL> create table t1 (x int, constraint t_pk primary key(x));

Table created.

SQL> analyze table t1 compute statistics;

Table analyzed.

SQL> analyze index t_pk validate structure;

Index analyzed.

SQL> exec dbms_workload_repository.create_snapshot();

PL/SQL procedure successfully completed.

SQL> set lines 180
SQL> set linesize 2000;

object_id -> object_name
========================
22040842 -> table T1 id
22040843 -> t_pk primary key

TESTING AT X1 POINT OF TIME
===========================

At this point of time ( x1 ), there is no row in table as well index.

Now I issue the following sql 

SELECT (SELECT TO_CHAR(end_interval_time,'dd/mm/yyyy hh24:mi:ss') FROM dba_hist_snapshot WHERE snap_id=a.snap_id) snap_time, 
a.* FROM ( 
SELECT t.snap_id,obj#,SUM(space_used_delta),SUM(space_allocated_delta) 
FROM dba_hist_seg_stat t,dba_hist_snapshot s,dba_objects o 
WHERE t.snap_id = s.snap_id AND s.snap_id >=2835
AND obj#=o.object_id AND object_id IN (22040842,22040843)
--AND owner='STOWNER' 
GROUP BY t.snap_id,obj# 
ORDER BY snap_id DESC 
) a;

no rows selected

Here it is showing no data about 2 objects.

Now I issue follwoing sql:

SELECT num_rows,blocks,empty_blocks FROM dba_tables WHERE table_name='T1';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
         0          0            7

it means at x1 point of time , table t1 has been created and allocated 7 blocks which does not get reflected in dba_hist_seg_stat.

Now issue another sql :

select lf_blks,br_blks,btree_space,height,BLOCKS,USED_SPACE from index_stats;

   LF_BLKS    BR_BLKS BTREE_SPACE     HEIGHT     BLOCKS USED_SPACE
---------- ---------- ----------- ---------- ---------- ----------
         1          0        7996          1          8


Here the value of space used by this index t_pk is around 7996  where as dba_hist_seg_stat shows nothing. I don't know why.

Conclusion : At x1 point of time, data in dba_hist_seg_stat seems to be incorrect.

TESTING AT X2 POINT OF TIME
===========================

Now I try to find out the no. of blocks in freelist.

set serveroutput on;

declare
l_freelist_blocks number;
begin
dbms_space.free_blocks
(segment_owner => user,
segment_name => 'T_PK',
segment_type => 'INDEX',
freelist_group_id=>0,
free_blks=> l_freelist_blocks);
dbms_output.put_line('blocks in freelist :'||l_freelist_blocks);
end;
/

blocks in freelist :0

PL/SQL procedure successfully completed.

So at the point of time x2 , there is no block in freelist of the index.

Now I insert the data.

begin
for i in 1..500000 loop
insert into t1 values(i);
end loop;
commit;
end;
/

After data insert, I do followings :

SQL> analyze table t1 compute statistics;

Table analyzed.

SQL> analyze index t_pk validate structure;

Index analyzed.

SQL> exec dbms_workload_repository.create_snapshot();

PL/SQL procedure successfully completed.

SQL> declare
  2  l_freelist_blocks number;
  3  begin
  4  dbms_space.free_blocks
  5  (segment_owner => user,
  6  segment_name => 'T_PK',
  7  segment_type => 'INDEX',
  8  freelist_group_id=>0,
  9  free_blks=> l_freelist_blocks);
 10  dbms_output.put_line('blocks in freelist :'||l_freelist_blocks);
 11  end;
 12  /
blocks in freelist :1

PL/SQL procedure successfully completed.

Then I do the same thing again

SELECT (SELECT TO_CHAR(end_interval_time,'dd/mm/yyyy hh24:mi:ss') FROM dba_hist_snapshot WHERE snap_id=a.snap_id) snap_time, 
a.* FROM ( 
SELECT t.snap_id,obj#,SUM(space_used_delta),SUM(space_allocated_delta) 
FROM dba_hist_seg_stat t,dba_hist_snapshot s,dba_objects o 
WHERE t.snap_id = s.snap_id AND s.snap_id >=2835
AND obj#=o.object_id AND object_id IN (22040842,22040843)
--AND owner='STOWNER' 
GROUP BY t.snap_id,obj# 
ORDER BY snap_id DESC 
) a;

SNAP_TIME                         SNAP_ID       OBJ# SUM(SPACE_USED_DELTA) SUM(SPACE_ALLOCATED_DELTA)
------------------------------ ---------- ---------- --------------------- --------------------------
12/06/2008 21:05:27                  2837   22040842                     0                    6291456
12/06/2008 21:05:27                  2837   22040843               6676536                    8388608

SQL> SELECT num_rows,blocks,empty_blocks FROM dba_tables WHERE table_name='T1';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
    500000        759            8

SQL> select lf_blks,br_blks,btree_space,height,BLOCKS,USED_SPACE from index_stats;

   LF_BLKS    BR_BLKS BTREE_SPACE     HEIGHT     BLOCKS USED_SPACE
---------- ---------- ----------- ---------- ---------- ----------
       937          3     7516336          3       1024    7495121

From the above result set we can find followings :

dba_hist_seg_stat shows 0 bytes space consumption about table 6291456 bytes space allocation for the table.

dba_tables shows 6217728 (HWM) space consumption and 65536 byes space allocation after HWM. Altogether we can say that 6217728+65536=6283264
bytes allocated to this table at x2 point of time.Now If I do 6291456 - 6283264 then I get extra 8192 bytes or extra 8 blocks. Now if we
see the no.of blocks allocated at x1 point of time, then it is 7. So data for space_allocation_delta in dba_hist_seg_stat for table T1
is more or less correct ( difference is 1 blcok only ) though these 7 blocks allocated at x1 point of time should not be considered because
it was not recorded in the 1st snap afetr the table creation.

dba_hist_seg_stat shows 6676536 bytes space consumption about index and 8388608 bytes space allocation for the index.

index_stats shows 7495121 bytes space consumption and 1024 blocks ( or 8388608 bytes ) allocated for this index.

From the above it is clear that only space_allocated_delta gives correct figure in case of index also.

Conclusion : space_allocated_delta in dba_hist_seg_stat table shows correct for index only and other data in dba_hist_seg_stat seems to be incorrect.

Let me if I am wrong in my test. 

Thanks
Deba

June 12, 2008 - 7:28 pm UTC

the table has a block used for the extent map as overhead. It seems OK to me?

data growth in 10g

deba, June 13, 2008 - 3:29 am UTC

Hi Tom,

Thanks for the reply. You are saying "the table has a block used for the extent map as overhead. It seems OK to me?" - not clear to me .

The problems I mentioned above are:

1) In the firts snap ( at x1 point of time ) , there was no entry for either table or index. Is it correct ?

2) In the second snap ( at x2 point of time ), no data is correct for table from my test case but you are saying that it is correct. Could you please show me the calculation ?

3) In the second snap ( at x2 point of time ), space used for index is not correct but you are it is ok. Could you please show me the calculation ?

I really depend on you an I will be highle oblised if u kindly show me the calculation.

Thanks
Deba

June 13, 2008 - 8:14 am UTC

1) well, in my runs - the index was there, but not the table - nothing was used in the table - the index always has an allocated and used block the leaf. I'm using 10.2.0.2


ops$tkyte%ORA10GR2> create or replace view hist_seg_stat
  2  as
  3  SELECT to_char(s.end_interval_time,'dd/mm/yyyy hh24:mi:ss') time,
  4         t.snap_id,obj#,(select object_name from user_objects where object_id = obj#) nm, space_used_delta,space_allocated_delta
  5    FROM dba_hist_seg_stat t,dba_hist_snapshot s,dba_objects o
  6   WHERE t.snap_id = s.snap_id
  7     AND s.snap_id = (select max(snap_id) from dba_hist_snapshot)
  8     AND obj#=o.object_id
  9     AND object_id IN (select object_id from user_objects where object_name in ('T1', 'T_PK') )
 10  /

View created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t1 (x int, constraint t_pk primary key(x)) tablespace MSSM;

Table created.

ops$tkyte%ORA10GR2> analyze table t1 compute statistics;

Table analyzed.

ops$tkyte%ORA10GR2> analyze index t_pk validate structure;

Index analyzed.

ops$tkyte%ORA10GR2> exec dbms_workload_repository.create_snapshot();

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from hist_seg_stat;

TIME                   SNAP_ID       OBJ# NM         SPACE_USED_DELTA SPACE_ALLOCATED_DELTA
------------------- ---------- ---------- ---------- ---------------- ---------------------
13/06/2008 08:04:09      11563     160006 T_PK                   8086                 65536

ops$tkyte%ORA10GR2> select segment_name, bytes from user_segments where segment_name in ('T1','T_PK');

SEGMENT_NAME                        BYTES
------------------------------ ----------
T1                                  65536
T_PK                                65536

ops$tkyte%ORA10GR2> select lf_blks,br_blks,btree_space,height,BLOCKS,USED_SPACE from index_stats;

   LF_BLKS    BR_BLKS BTREE_SPACE     HEIGHT     BLOCKS USED_SPACE
---------- ---------- ----------- ---------- ---------- ----------
         1          0        7996          1          8

2) the data is correct, the delta from "no data available" is "0" and the space allocated is correct down to the byte:


ops$tkyte%ORA10GR2> insert into t1 select level from dual connect by level <= 500000;

500000 rows created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> analyze table t1 compute statistics;

Table analyzed.

ops$tkyte%ORA10GR2> analyze index t_pk validate structure;

Index analyzed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_workload_repository.create_snapshot();

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from hist_seg_stat;

TIME                   SNAP_ID       OBJ# NM         SPACE_USED_DELTA SPACE_ALLOCATED_DELTA
------------------- ---------- ---------- ---------- ---------------- ---------------------
13/06/2008 08:04:17      11564     160006 T_PK                6668450               8323072
13/06/2008 08:04:17      11564     160005 T1                        0               <b>6291456</b>

ops$tkyte%ORA10GR2> select segment_name, bytes from user_segments where segment_name in ('T1','T_PK');

SEGMENT_NAME                        BYTES
------------------------------ ----------
T1                                <b>6291456</b>
T_PK                              8388608

3) you are not showing the space used, so how could it be wrong? You are showing a delta and allocated space.

data growth in 10g

deba, June 13, 2008 - 5:24 pm UTC

Hi Tom,

Thanks for the reply.

I am using 10.2.0.3 64 bit on Sun solaris 10.5 . Os is also 64 bit.

1) If I need to use dba_segments then why should I use analyze the table ? My aim was to get the accurate data which I can get if I analyze the table.

2) You are saying " well, in my runs - the index was there, but not the table - nothing was used in the table "

But if you see your test , then you can see following things

In case of 1st snap , there was some space allocated to the table ( reflected in dba_segments ) and that space was not
recorded in dba_hist_seg_stat and next time when the table gets recorded in dba_hist_seg_stat, then spaced_used_delta
is showing 0. All consumed space has gone to "allocated" column. It means after the insert , table has not consumed any space . Is it correct ?

3) I have already mentioned that space_allocated_delta is correct for index but space_used_delta is not correct. In your test, I am not able to find out how the value "6668450" is coming. Could you please show me the calculation to get this value ?

Thanks
Deba

June 16, 2008 - 11:25 am UTC

1) you were looking at allocated space, you don't need to analyze to see allocated, we know what is allocated.

2) there we not any space used.

3) I didn't compute it, it was computed for me? It is the difference in the space used (under the high water mark) for the index itself.

data growth in 10g

deba, June 14, 2008 - 3:49 am UTC

Hi Tom,

Is there any update fro the above ? It is very very important.

Thanks
Deba

June 16, 2008 - 11:37 am UTC

Deba,

don't take this the wrong way but....

there is no way this is critically important - important to the degree you need to have a reply on a Saturday. This is not that big of a deal.

I too take weekends from time to time.

Regaring a tablespace issues

Michael holding, June 16, 2008 - 11:29 am UTC

Hi ,
I am deleting the large amount of data in table using the delete statements .After that I have checked tablespace, there is no change in tablespace memory. But when using truncate statements,I do see change in tablespaces.
My steps are below
1)CREATE TABLE CSA_BANK_RESPONSE
(
BANK_KEY NUMBER(9) NOT NULL,
BANK_ID VARCHAR2(30 BYTE) NOT NULL,
BANK_PSH_SUFFIX VARCHAR2(3 BYTE) NOT NULL,
RESPONSE_XML_BANK CLOB
)
TABLESPACE CSA_BANK_RESPONSE_TBS;

ALTER TABLE CSA_BANK_RESPONSE ADD (
CONSTRAINT CSA_BANK_RESPONSE
PRIMARY KEY
(BANK_KEY)
USING INDEX
TABLESPACE CSA_BANK_RESPONSE_NDX);

2) inserted large amount of data.

3) select sum(bytes)/1024/1024
from user_segments
where tablespace_name= CSA_BANK_RESPONSE_NDX;

The size is 345.654 MB.

4) deleted data what we inserted.

Delete from CSA_BANK_RESPONSE;
Commit;

After that I am checking tablespace,it shows a like

select sum(bytes)/1024/1024
from user_segments
where tablespace_name= CSA_BANK_RESPONSE ';

The size is 345.654 MB.
5) once again,I am inserting large amount of data into ' CSA_BANK_RESPONSE '.
Now size is

select sum(bytes)/1024/1024
from user_segments
where tablespace_name=' CSA_BANK_RESPONSE¿;

the size is 645.456MB.

What I expect is, similar to truncate statements if delete statements are used then there should be a decrease in tablespace.

product :oracle10g
OS :Linux

How can we resolve this issue?

June 16, 2008 - 1:44 pm UTC

A delete against a table will not ever return space to a 'tablespace', it frees it for use within the table itself. After the delete, the space still belongs to the table. This is how it has always and forever worked in pretty much every database.

A truncate releases all allocated space and reallocates new space - it is totally different.

If you are using automatic segment space management in 10gr2 - you can alter table t shrink space compact; alter table t shrink space; after the big delete to compact and then release any releasable free space.

But a delete - will never give space back to a tablespace, it just cannot.

data growth in 10g

deba, June 17, 2008 - 2:00 am UTC

Hi Tom,

"there is no way this is critically important - important to the degree you need to have a reply on a Saturday. This is not that big of a deal.

I too take weekends from time to time. " - sorry for that. I did not mean that.

1) Now you are saying "you were looking at allocated space, you don't need to analyze to see allocated, we know what is allocated" - here we are trying to see space allocated and space used as well. For space used, I thought , analyze would be the best way.
2) You are saying "there we not any space used" - but 65536 bytes were allocated. So it should have come in the 1st snap. Now if you say that since no space was used at that point of time , that's why it was not considered at all. Then it should have not come in the second snap as well becuase in the second snap, "space used" was showing 0 .
3) You are saying "It is the difference in the space used (under the high water mark) for the index itself." - Lets say, I need to find out the space used by index, not space allocated. Then I will analyze the index and try to find out the space used. I did same but no figure was coming close to the figure of space used against that index in the 2nd snap. Since you are expert , so could you please show me the calculation ?
4) If you see the second snap for the table, it shows that space used is 0 even after the inserting 500000. Is it possible because it is normal table ?

Thanks
Deba

June 17, 2008 - 9:54 am UTC

1) but you don't need to, we can see that information without analyzing.

2) if you believe this to be incorrect - please utilize support, I can only show what happens, what is happening, if you believe it 'wrong', I won't argue one way or the other - but I cannot change it. My personal opinion is "so what, it is noise, it is nothing I would lose sleep over" in this case.

3) then you might have to implement your own tracking if what we track is not sufficient for whatever you want. Analyzing the index (validate structure) locks the darn index - we'd never want to do that just to get size information.

4) there is nothing to delta from....

Piotr Jastrzebski, July 02, 2008 - 7:20 am UTC

Hi, I am not sure if it helps in any way, but... I have just tried to use dba_hist_seg_stat for monitoring segment grow, and it seems to me, that dba_hist_seg_stat view contains data only about 'top' segments based on some criteria as noted in:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_3165.htm#I1023436

Maybe this is the reason of lack of some data in particular snapshots?

I wonder if there is any way for dba to change these "criteria" for gathering stats about segments into dba_hist_seg_stat?

Regards
Piotr

A reader, August 05, 2008 - 12:08 pm UTC

Hi - We are on 10.2.0.3 on our new production database which went live about 2 months back. In this period, this database has grown considerably (30GB). We would like to determine which objects are contributing towards this growth. So we want to setup some scripts which will collect the growth rate for all tables/indexes. Do you have anything like that? I am seeing a lot of references to dba_hist_seg_stat view but dont know if that is the only way to go !! Can you please help ?

August 05, 2008 - 1:12 pm UTC

you can either

a) do it yourself
b) use the builtin features

I do not have a script to do it, you would write a procedure that would save say the contents of dba_segments every N units of time (you pick N) along with the "timestamp" of the copy - then you could query up the data at time X and at time Y and subtract to see what has grown and by how much.

or use the views already there.

Grid Control Storage Reports

Pascal Mwakuye, August 06, 2008 - 2:12 pm UTC

Hi,

I have also been searching for scripts or ways to predict database growth but did not find any useful scripts.
dba_hist_seg_stats does not have stats for all objects.

At the moment i am using the 'Grid Control Reports Home Page' under the Storage Reports Section to predict DB Growth.
This way you can predict Growth up to the Tablespace Level, but not for individual Segments.
You can even use the 'Create Like' option and create your own Reports.
You can also modify the SQL Query used and run the Query yourself using the 'sysman' schema of the Grid Control Database.

Examples:
http://www.dbazine.com/blogs/blog-cf/chrisfoot/blogentry.2006-01-22.7407970480/02_report_listing.gif

Hope this helps,

Regards.

Pascal

UNUSABLE state

Michael, November 11, 2008 - 3:58 am UTC

Hi,
I have table with 40 partition.I have create the global index in this table.After i have inserted 10000 data into this tables.I got the error index "unusable" state.After i rebuild the index and inserted large amount of data.Once agin i got the same error.frequently i got the same error.

My steps:
********
create table bank
(id number(9),
name varchar2(12),
account varchar2(12),
street varchar2(12));

I have create range partition based on the id column.

create index id_pk (id);

insert into bank
select *
from ext_bank;
commit;

delete from bank where id=1;

It shows a id_pk index is unusable.

how can resolve this issue?

Thanks,
Michael

November 11, 2008 - 4:28 pm UTC

... i have inserted 10000 data into this tables.I got the error index
"unusable" state. ...

Nope, you are missing a step, you did something else and you are not telling us....

indexes do not go unusable by inserting.

displaying data growth horizontally

A reader, August 03, 2011 - 9:04 pm UTC

Hi Tom,
Is it possible to invert the output below so a horizontal graph with vertical asterisks (corresponding to the growth_mb/begin_interval_time) is displayed? I was able to dynamically display the begin_interval_time horizontally with corresponding growth_mb but am not sure if it is possible to invert the asterisks. I'm basically trying to avoid the need to output to a graphical package so the data can be displayed horizontally.
Thanks in advance!

column growth_mb_graph format a100

column graph format a100

column begin_interval_time format a25

select
        s.begin_interval_time,
        round(sum(h.space_used_delta/1024/1024),0) growth_mb,
        -- don't print the value, just the asterisk
        replace
                (
        rpad
                (
                (round(sum(h.space_used_delta/1024/1024),0)),
                (round(sum(h.space_used_delta/1024/1024),0)/1/1),
                '*'
                ),
                (round(sum(h.space_used_delta/1024/1024),0))
                )graph
from
        dba_hist_seg_stat h,
        dba_hist_snapshot s
where
        h.snap_id = s.snap_id
        and s.begin_interval_time > sysdate - 1/8
group by
        s.begin_interval_time
order by
        1 desc
/

this is the output:'BEGIN_INTERVAL_TIME GROWTH_MB GRAPH
------------------------- ---------- ----------------------------------------------------------------------------------------------------
2011-08-03 18:30:30.032 24 **********************
2011-08-03 18:20:29.550 38 ************************************
2011-08-03 18:10:28.986 15 *************
2011-08-03 18:00:28.444 13 ***********
2011-08-03 17:50:27.959 35 *********************************
2011-08-03 17:40:27.356 17 ***************
2011-08-03 17:30:26.851 23 *********************
2011-08-03 17:20:26.362 38 ************************************
2011-08-03 17:10:25.859 20 ******************
2011-08-03 17:00:25.256 19 *****************
2011-08-03 16:50:24.758 40 **************************************
2011-08-03 16:40:24.230 18 ****************
2011-08-03 16:30:23.736 24 **********************
2011-08-03 16:20:23.248 36 **********************************
2011-08-03 16:10:22.689 16 **************
2011-08-03 16:00:22.139 15 *************
2011-08-03 15:50:21.628 36 *********************************

this is the code for the horizontal column pivot:

set feedback off

set time off

set pagesize 0

spool sample.seg.growth.graph.vertical.tmp

select
        txt1,
        (
        -- don't place a comma after the last row
        case
                when txt3 = txt4 then txt2
                else txt2||','
        end)txt2
/*,
        txt3,
        txt4
*/
from
(
select
        distinct
        -- the .12345 case else is to place a dummy number so the max can collapse the output to 1 row
        'max((case when s.begin_interval_time = ''' ||s.begin_interval_time||'''' as txt1,
        'then round(sum(h.space_used_delta/1024/1024),0) else .12345 end)) as c'||replace(replace(to_char(cast (begin_interval_time as date),'yyyymmdd hh24:mi'),' ',''),':','') as txt2,
        -- get begin_interval_time so it can be compared to txt4 so the last row doesn't contain a comma
        'c'||replace(replace(to_char(cast (s.begin_interval_time as date),'yyyymmdd hh24:mi'),' ',''),':','') txt3,
        (
        select
                -- get min begin_interval_time so the last row doesn't contain a comma
                min
                (
                'c'||replace(replace(to_char(cast (s.begin_interval_time as date),'yyyymmdd hh24:mi'),' ',''),':','')
                )
        from
                dba_hist_seg_stat h,
                dba_hist_snapshot s
        where
                h.snap_id = s.snap_id
                and s.begin_interval_time > sysdate - 1/8
        ) txt4
from
        dba_hist_seg_stat h,
        dba_hist_snapshot s
where
        h.snap_id = s.snap_id
        and s.begin_interval_time > sysdate - 1/8
group by
        s.begin_interval_time
order by
        txt2 desc
)
/

spool off


set feedback on
set time on
set echo on
set pagesize 500

select
        min(round(sum(h.space_used_delta/1024/1024),0)) min,
        max(round(sum(h.space_used_delta/1024/1024),0)) max,
        @@sample.seg.growth.graph.vertical.tmp
from
        dba_hist_seg_stat h,
        dba_hist_snapshot s
where
        h.snap_id = s.snap_id
        and s.begin_interval_time > sysdate - 1/8
group by
        s.begin_interval_time

and here's the output:
MIN MAX C201108031830 C201108031820 C201108031810 C201108031800 C201108031750 C201108031740 C201108031730 C201108031720 C201108031710 C201108031700 C201108031650 C201108031640 C201108031630 C201108031620 C201108031610 C201108031600 C201108031550
---------- ---------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- -------------
13 40 24 38 15 13 35 17 23 38 20 19 40 18 24 36 16 15 36

August 04, 2011 - 10:14 am UTC

this is the query I think you want ultimately:

ops$tkyte%ORA11GR2> with data
  2  as
  3  (
  4  select min(round(sum(h.space_used_delta/1024/1024),0)) min,
  5         max(round(sum(h.space_used_delta/1024/1024),0)) max,
  6         max((case when s.begin_interval_time = '29-JUL-11 03.32.19.717 PM'
  7                   then round(sum(h.space_used_delta/1024/1024),0)
  8                   else .12345 end)) as c201107291532,
  9         max((case when s.begin_interval_time = '29-JUL-11 12.00.44.722 PM'
 10                   then round(sum(h.space_used_delta/1024/1024),0)
 11                   else .12345 end)) as c201107291200,
 12         max((case when s.begin_interval_time = '29-JUL-11 11.00.32.537 AM'
 13                   then round(sum(h.space_used_delta/1024/1024),0)
 14                   else .12345 end)) as c201107291100,
 15         max((case when s.begin_interval_time = '29-JUL-11 10.10.52.713 AM'
 16                   then round(sum(h.space_used_delta/1024/1024),0)
 17                   else .12345 end)) as c201107291010,
 18         max((case when s.begin_interval_time = '29-JUL-11 08.00.40.000 AM'
 19                   then round(sum(h.space_used_delta/1024/1024),0)
 20                   else .12345 end)) as c201107290800
 21    from dba_hist_seg_stat h,
 22         dba_hist_snapshot s
 23   where h.snap_id = s.snap_id
 24         and s.begin_interval_time between
 25             to_date('29-jul-2011', 'dd-mon-yyyy')
 26             and
 27             to_date('30-jul-2011','dd-mon-yyyy')
 28   group by s.begin_interval_time
 29  ),
 30  lines
 31  as
 32  (select level l
 33     from dual
 34  connect by level <= 41)
 35  select min, max,
 36         case when l = 41 then to_char(c201107291532,'999,999.9')
 37              when (c201107291532-min)/((max-min)/40) >= l then '         *'
 38          end c201107291532x,
 39         case when l = 41 then to_char(c201107291200,'999,999.9')
 40              when (c201107291200-min)/((max-min)/40) >= l then '         *'
 41          end c201107291200x,
 42         case when l = 41 then to_char(c201107291100,'999,999.9')
 43              when (c201107291100-min)/((max-min)/40) >= l then '         *'
 44          end c201107291100x,
 45         case when l = 41 then to_char(c201107291010,'999,999.9')
 46              when (c201107291010-min)/((max-min)/40) >= l then '         *'
 47          end c201107291010x,
 48         case when l = 41 then to_char(c201107290800,'999,999.9')
 49              when (c201107290800-min)/((max-min)/40) >= l then '         *'
 50          end c201107290800x
 51    from data, lines
 52   order by l desc
 53  /

       MIN        MAX C201107291 C201107291 C201107291 C201107291 C201107290
---------- ---------- ---------- ---------- ---------- ---------- ----------
         0         92         .1       56.0       61.0        1.0       92.0
         0         92                                                      *
         0         92                                                      *
         0         92                                                      *
         0         92                                                      *
         0         92                                                      *
         0         92                                                      *
         0         92                                                      *
         0         92                                                      *
         0         92                                                      *
         0         92                                                      *
         0         92                                                      *
         0         92                                                      *
         0         92                                                      *
         0         92                                                      *
         0         92                                *                     *
         0         92                                *                     *
         0         92                     *          *                     *
         0         92                     *          *                     *
         0         92                     *          *                     *
         0         92                     *          *                     *
         0         92                     *          *                     *
         0         92                     *          *                     *
         0         92                     *          *                     *
         0         92                     *          *                     *
         0         92                     *          *                     *
         0         92                     *          *                     *
         0         92                     *          *                     *
         0         92                     *          *                     *
         0         92                     *          *                     *
         0         92                     *          *                     *
         0         92                     *          *                     *
         0         92                     *          *                     *
         0         92                     *          *                     *
         0         92                     *          *                     *
         0         92                     *          *                     *
         0         92                     *          *                     *
         0         92                     *          *                     *
         0         92                     *          *                     *
         0         92                     *          *                     *
         0         92                     *          *                     *

41 rows selected.

Now, while I appreciate your sqlplus trick above - I found it too obscure and preferring more straightforward things - I'm going to use plsql to construct a query, you could use an anonymous block if you don't want a function:

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace function show_hist( p_mindt in timestamp, p_maxdt in timestamp, p_lines in number default 40 )
  2  return sys_refcursor
  3  is
  4      l_query long :=
  5      'with data as (
  6       select min(round(sum(h.space_used_delta/1024/1024),0)) min,
  7              max(round(sum(h.space_used_delta/1024/1024),0)) max';
  8  
  9      l_cursor sys_refcursor;
 10  begin
 11      for x in (select to_char(begin_interval_time,'yyyymmddhh24mi') bit
 12                  from dba_hist_snapshot
 13                 where begin_interval_time between p_mindt and p_maxdt)
 14      loop
 15          l_query := l_query || replace(
 16          q'|,
 17          max((case when to_char(s.begin_interval_time,'yyyymmddhh24mi') = '%s'
 18                    then round(sum(h.space_used_delta/1024/1024),0)
 19                    else .12345
 20                end)) as c%s|'
 21          , '%s', x.bit );
 22      end loop;
 23  
 24      l_query := l_query || q'|
 25          from dba_hist_seg_stat h,
 26               dba_hist_snapshot s
 27         where h.snap_id = s.snap_id
 28               and s.begin_interval_time between :p_mindt and :p_maxdt
 29         group by s.begin_interval_time
 30         ),
 31         lines
 32         as
 33         (select level l
 34            from dual
 35         connect by level <= (:p_lines+1))
 36         select min, max|';
 37  
 38      for x in (select to_char(begin_interval_time,'yyyymmddhh24mi') bit
 39                  from dba_hist_snapshot
 40                 where begin_interval_time between p_mindt and p_maxdt)
 41      loop
 42          l_query := l_query ||
 43          replace(
 44          replace(
 45          q'|,
 46          case when l = (%d+1) then to_char(c%s,'999,999.9')
 47               when (c%s-min)/((max-min)/%d) >= l then '         *'
 48           end T%s|'
 49          , '%s', x.bit )
 50          , '%d', p_lines );
 51      end loop;
 52  
 53      l_query := l_query || ' from data, lines order by l desc';
 54  
 55      open l_cursor for l_query using p_mindt, p_maxdt, p_lines;
 56      return l_cursor;
 57  end;
 58  /

Function created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> variable c refcursor
ops$tkyte%ORA11GR2> exec :c := show_hist( to_timestamp('29-jul-2011', 'dd-mon-yyyy'), to_timestamp('30-jul-2011','dd-mon-yyyy'), 20 )

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> print c

       MIN        MAX T201107291 T201107291 T201107291 T201107290 T201107291
---------- ---------- ---------- ---------- ---------- ---------- ----------
         0         92       61.0        1.0         .1       92.0       56.0
         0         92                                           *
         0         92                                           *
         0         92                                           *
         0         92                                           *
         0         92                                           *
         0         92                                           *
         0         92                                           *
         0         92          *                                *
         0         92          *                                *          *
         0         92          *                                *          *
         0         92          *                                *          *
         0         92          *                                *          *
         0         92          *                                *          *
         0         92          *                                *          *
         0         92          *                                *          *
         0         92          *                                *          *
         0         92          *                                *          *
         0         92          *                                *          *
         0         92          *                                *          *
         0         92          *                                *          *

21 rows selected.

Brilliant!

A reader, August 05, 2011 - 10:10 am UTC

Hi Tom,
This is simply terrific. Over the years, it's been useful to have the vertical graph as a trend indicator but your brilliant architecture of the horizontal graph opens up a myriad of possibilities when displaying multiple trends in a single report.
Thanks again!

reader

Reader, October 26, 2011 - 2:50 pm UTC

1  select * from
  2  table(dbms_space.object_growth_trend
  3* ('OWN_NM','FACT_TABLE01','PARTITION','PART_xxx'))
SQL> /
table(dbms_space.object_growth_trend
      *
ERROR at line 2:
ORA-03200: the segment type specification is invalid
ORA-06512: at "SYS.DBMS_SPACE", line 4228
ORA-06512: at "SYS.DBMS_SPACE", line 4498
ORA-06512: at line 1

I want to know what is the value for 3rd parameter if I want to use it for finding growth trend of a partition

Thanks

October 26, 2011 - 3:39 pm UTC

it would be a table.

You name the partition, if you are interested in a partition, as the fourth parameter.

Database Size Growth

A reader, October 27, 2011 - 11:19 am UTC

excellent method.

A reader, March 07, 2012 - 5:31 am UTC

Hi,

I have loaded data into target table using bulk collect and FORALL. but i need some clarfication in FORALL.

see my code:

declare
cursor c1 is
select id,name,address
from emp;
type tab_cl_typ is table of c1%rowtype;
tab_cl_rec tab_cl_tab;
begin
open c1;
loop
fetch c1 bulk collect into tab_cl_rec LIMIT 1000;
forall i in tab_cl_rec.first..tab_cl_rec.last
insert into tartab
values tab_cl_rec(i);
exit when c1%notfound;
end loop;
close c1;
end;
/

it works fine.. but i got error 17/77 PL/SQL: ORA-22806: not an object or REF

declare
cursor c1 is
select id,name,address
from emp;
type tab_cl_typ is table of c1%rowtype;
tab_cl_rec tab_cl_tab;
begin
open c1;
loop
fetch c1 bulk collect into tab_cl_rec LIMIT 1000;
forall i in tab_cl_rec.first..tab_cl_rec.last
insert into tartab(ename)
values (tab_cl_rec(i).ename);
exit when c1%notfound;
end loop;
close c1;
end;
/

and i re create above block in below format.

declare
cursor c1 is
select id,name,address
from emp;
type tab_cl_typ is table of emp.id%type;
type tab_c2_typ is table of emp.name%type;
type tab_c3_typ is table of emp.address%type;

tab_cl_rec tab_cl_typ;
tab_c2_rec tab_c2_typ;
tab_c3_rec tab_c3_typ;

begin
open c1;
loop
fetch c1 bulk collect into tab_cl_rec,tab_c2_rec,tab_c3_rec LIMIT 1000;
forall i in tab_cl_rec.first..tab_cl_rec.last
insert into tartab(id,name)
values (tab_cl_rec(i),tab_c2_rec(i));
exit when c1%notfound;
end loop;
close c1;
end;
/

now it works fine. but i want to create one type based on the cursor or table rowtype and i want to use like
(tab_cl_rec(i).ename). Does its possible? why oracle shows an error for that.
could you please explain on that?

March 07, 2012 - 7:00 pm UTC

After MUCH FIGHTING WITH YOUR CODE (why, please - just someone, anyone, I don't care who - please tell me - WHY CAN'T PEOPLE BE BOTHERED TO POST VALID STUFF???? WHY? really - why not? Is their time more valuable than everyone elses? do they not care? what is up with this???)

where are the table creates?
why do the type names NOT MATCH UP?

type tab_cl_typ is table of c1%rowtype;
tab_cl_rec tab_cl_tab;

why do the column names not match?
ugh...

ops$tkyte%ORA11GR2> create table emp ( id number, ename varchar2(30), address varchar2(30) );

Table created.

ops$tkyte%ORA11GR2> create table tartab as select * from emp where 1=0;

Table created.

ops$tkyte%ORA11GR2> insert into emp select user_id, username, '123' from all_users;

49 rows created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2  cursor c1 is
  3  select id,ename,address
  4  from emp;
  5  type tab_cl_typ is table of c1%rowtype;
  6  tab_cl_rec tab_cl_typ;
  7  begin
  8  open c1;
  9  loop
 10  fetch c1 bulk collect into tab_cl_rec LIMIT 5;
 11  forall i in tab_cl_rec.first..tab_cl_rec.last
 12  insert into tartab(ename)
 13  values (tab_cl_rec(i).ename);
 14  exit when c1%notfound;
 15  end loop;
 16  close c1;
 17  end;
 18  /

PL/SQL procedure successfully completed.

You are using an older release of the database, in that release of the database - arrays could not be "intermingled" with other arrays in the forall. It is a feature of the database now, it wasn't back in whatever really old version you are using.

and if you are just copying data from one table to another - please do not write code, just use insert as select.

one line of 'code' only

PLS-00306: wrong number or types of arguments in call to 'MULTISET_INTERSECT_ALL'

Michael Holding, March 08, 2012 - 7:57 am UTC

Please given an idea about this error

Code: [Select all] [Show/ hide]CREATE OR REPLACE PACKAGE  pkg_mkt_hub_load_collection
AS

 PROCEDURE sp_final_load_mkt_hub;

END pkg_mkt_hub_load_collection;
/


CREATE OR REPLACE PACKAGE BODY pkg_mkt_hub_load_collection
AS

 c_default_limit CONSTANT PLS_INTEGER:=5000;

 PROCEDURE sp_final_lvl_idx_mon_hub;

 PROCEDURE sp_final_lvl_idx_mon_hub
   AS
   CURSOR cur_lvl_idx_mon IS
   SELECT idxmap.ssia_index_code,idxstg.start_date,idxstg.currency,idxstg.level1,idxstg.type, 
       idxstg.return_month,idxstg.return_3months, idxstg.return_6months, idxstg.return_ytd,
 idxstg.return_1year, 
       idxstg.return_3years, idxstg.return_5years,idxstg.return_10years,idxstg.market_cap,
       idxstg.mkt_file_id    
 FROM   mkt_total_lvl_indx_mon_stg idxstg, 
     md_vendor_index_map idxmap 
 WHERE  idxmap.source = idxstg.source 
     AND idxmap.base_currency = idxstg.currency 
     AND idxmap.return_type = idxstg.type 
     AND idxmap.mkt_index_id = idxstg.vendor_code
     AND idxmap.monthly = 'Y' 
     AND idxmap.file_type = 'T';
     
   TYPE cur_lvl_idx_tab is table of cur_lvl_idx_mon%ROWTYPE;
   cur_lvl_idx_rec cur_lvl_idx_tab;
   
   CURSOR cur_idx_mon_hub IS
   SELECT idxhub.ssia_index_id,idxhub.effective_date,idxhub.currency,index_level,idxhub.type, 
       idxhub.return_month,idxhub.return_3months, idxhub.return_6months, idxhub.return_ytd, 
idxhub.return_1year, 
       idxhub.return_3years, idxhub.return_5years,idxhub.return_10years,idxhub.market_cap,
       idxhub.mkt_file_id    
   FROM   mkt_total_lvl_indx_mon_hub idxhub;  

   TYPE cur_idx_mon_tab IS TABLE OF cur_idx_mon_hub%ROWTYPE;
   cur_idx_mon_rec cur_idx_mon_tab;   

   l_col_3 cur_idx_mon_tab;
   
   BEGIN
  
   OPEN cur_lvl_idx_mon;
   LOOP
   FETCH cur_lvl_idx_mon BULK COLLECT INTO cur_lvl_idx_rec LIMIT c_default_limit;
      
   EXIT WHEN cur_lvl_idx_mon%NOTFOUND;
   
   END LOOP;
   
   OPEN cur_idx_mon_hub;
   LOOP
   FETCH cur_idx_mon_hub BULK COLLECT INTO cur_idx_mon_rec LIMIT c_default_limit;
   
   EXIT WHEN cur_idx_mon_hub%NOTFOUND;
   
   END LOOP;
   
   l_col_3 := cur_lvl_idx_rec MULTISET INTERSECT cur_idx_mon_rec;

   
   CLOSE cur_lvl_idx_mon;
   
   CLOSE cur_idx_mon_hub;

   
  END sp_final_lvl_idx_mon_hub;

 
  
  PROCEDURE sp_final_load_mkt_hub
  AS
  BEGIN
  NULL;
  END sp_final_load_mkt_hub;
   
END pkg_mkt_hub_load_collection;   
/

show error



error code

Code: [Select all] [Show/ hide]SQL> @pkg_mkt_hub_load_collection.sql

Package created.


Warning: Package Body created with compilation errors.

Errors for PACKAGE BODY PKG_MKT_HUB_LOAD_COLLECTION:

LINE/COL ERROR
-------- -----------------------------------------------------------------
57/4     PL/SQL: Statement ignored
 PLS-00306: wrong number or types of arguments in call to 'MULTISET_INTERSECT_ALL'

SQL>



please give an idea about this error

March 09, 2012 - 9:11 am UTC

please provide an example that

a) is complete
b) yet is very concise

I don't think you need queries or anything - just give the smallest snippet of code humanly possible to demonstrate your issue (99 times out of 100 when I do that - I find my own mistake!)

but it will probably come down to this:

 TYPE cur_lvl_idx_tab is table of cur_lvl_idx_mon%ROWTYPE;
   cur_lvl_idx_rec cur_lvl_idx_tab;
..

   TYPE cur_idx_mon_tab IS TABLE OF cur_idx_mon_hub%ROWTYPE;
   cur_idx_mon_rec cur_idx_mon_tab;   
   
...

   l_col_3 := cur_lvl_idx_rec MULTISET INTERSECT cur_idx_mon_rec;

do you see they are different types? I don't care if the types "look" the same to you - they "look" different to the compiler - they are not subtypes of each other, they are not the same. They are the same like "twins" are the same - they look a lot alike, but they are different.

use the same type for both.

Tracking the Growth Of the Database

Santosh, May 14, 2012 - 10:51 am UTC

Hi Tom,

I need your help in tracking the growth of the database on daily/monthly basis,can you please help here..?

Thanks in Advance.

Thanks,
Santosh.

May 14, 2012 - 12:50 pm UTC

what enterprise manager packs do you have? the capacity management pack does just that.

A reader, May 15, 2012 - 4:06 am UTC

Tom,

I have 11G OEM but i am not able to locate the tab "capacity management" there..?

Thanks,
Santosh.

May 15, 2012 - 1:48 pm UTC

http://www.oracle.com/technetwork/oem/grid-control/downloads/chargeback-capacity-planning-498569.html

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

what if ?

database growth

how about 10g?

Predicting the Growth of a Segment

growth of a segment in 10g

growth of a segment in 10g

data growth in 10g

data growth in 10g

data growth in 10g

Regaring a tablespace issues

data growth in 10g

Grid Control Storage Reports

UNUSABLE state

displaying data growth horizontally

Brilliant!

reader

Database Size Growth

PLS-00306: wrong number or types of arguments in call to 'MULTISET_INTERSECT_ALL'

Tracking the Growth Of the Database

More to Explore

PL/SQL demos

PL/SQL docs

For calculating data growth

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

what if ?

database growth

how about 10g?

Predicting the Growth of a Segment

growth of a segment in 10g

growth of a segment in 10g

data growth in 10g

data growth in 10g

data growth in 10g

Regaring a tablespace issues

data growth in 10g

Grid Control Storage Reports

UNUSABLE state

displaying data growth horizontally

Brilliant!

reader

Database Size Growth

PLS-00306: wrong number or types of arguments in call to 'MULTISET_INTERSECT_ALL'

Tracking the Growth Of the Database

More to Explore

PL/SQL demos

PL/SQL docs