Top n Growing tables in BW & R/3 with Oracle env

[Former Member]

Hi all,

We are on BW 7.0 and R/3 4.6c with oracle 10.2.0.2 , please let me know how to get top N growing tables & Top N largest tables...

I remember collecting these stats from DB02 with Sqlserver as DB, but wit oracle not unable to find these options in Db02 or dbacockpit..

Please suggest...

Thanks,

Subhash.G

[Former Member]

Thanks

[Former Member]

Hi Subhash,

You can use the following SQL query:

select * from

(select owner, segment_name, segment_type, tablespace_name, sum (bytes/1024/1024) MB

from dba_extents

where segment_type = 'TABLE'

group by owner, segment_name, segment_type, tablespace_name

order by MB desc)

where rownum <= N;

Put an appropriate value for N to find out top N growing tables & Top N largest tables.

regards,

Subhajit.

[Former Member]

Hi,

From snote # 1002840 i checked the DB02_COLL_PLAN and lot od methods are in status I means inactive.

Please let me know how i can activate the methods.

Thanks,

Subhash.G

[Former Member]

Subhash,

Goto tcode DB02OLD>Detailed Analysis>Object Name *, Tablespace , Objetc Type tab. You will get list of all table, you take out top 50 table from this list.

Earlywatch report also gives list of top 20 tables. Check your earlywatch report for this.

Hope it helps

Thanks

Sushil

Edited by: Sushil Suryawanshi on Jun 12, 2009 7:55 PM