give me the sql query which calculte the table size in oracle 10g ecc 6.0

[Former Member]

Hi expert,

Please give me the sql query which calculte the table size in oracle 10g ecc 6.0.

Regards

[Former Member]

DB02 -> Space -> Segments -> Detailed Analysis -> Enter segment name

[Former Member]

any sql query????

[Former Member]

select segment_name, sum(bytes)/(1024*1024) from dba_segments where segment_name = '<TABLE_NAME>' group by segment_name;

[volker_borowski2]

select segment_name, sum(bytes)/(1024*1024) from dba_segments where segment_name = '<TABLE_NAME>' group by segment_name;

Hi,

This delivers possibly wrong data in MCOD installations.

Depending on Oracle Version and Patchlevel dba_segments does not always have the correct data,

at any time esp. for indexes right after being rebuild parallel (Even in DB02 because it is using USER_SEGMENTS).

Takes a day to get the data back in line (never found out, who did the correction at night, could be RSCOLL00 ?).

Use above statement with "OWNER = " in WHERE for MCOD or connect as schema owner and use USER_SEGMENTS.

Use with

segment_name LIKE '<TABLE_NAME>%'

if you like to see the related indexes as well.

For partitioned objects, a join from dba_tables / dba_indexes to dba_tab_partitions/dba_ind_partitions to dba_segments

might be needed, esp. for hash partitioned tables, depending on how they have been created ( partition names SYS_xxxx).

Volker

[Former Member]

ok

thanks to all.

[Former Member]

Hi Vivek,

Why don't you use DB02 or BRtools to find out the table size?

Best regards,

Orkun Gedik