10 largest tables in database

[cougartrace]

Anyone have a script to give 10 largest tables in a database? Or a script that would give sizes of all tables in a database?

 

[SantaMufasa]

Cougar,

Try this:

ttitle 'Top Ten Tables (by size)'
set lines 75
col tab heading "Table Name" format a55
col bytes heading "Size|(in Bytes)" format 999,999,999,999
select bytes, tab
from (select owner||'.'||segment_name tab, bytes
      from dba_segments
      where segment_type = 'TABLE'
      order by bytes desc)
where rownum <= 10
/

Fri Aug 26                                                        page    1
                         Top Ten Tables (by size)

            Size
      (in Bytes) Table Name
---------------- -------------------------------------------------------
      55,533,568 SYS.SOURCE$
      45,088,768 SYSADM.CUSTOMER_BOOKINGS
      12,148,736 SYS.IDL_UB1$
       9,437,184 SYSADM.CUSTOMER_ORDER
       7,307,264 SYS.IDL_UB2$
       4,194,304 SYSADM.CUST_ORDER_LINE
       3,145,728 SYSADM.CASH_DISBURSEMENT
       3,145,728 SYSADM.CUST_ORDER_BINARY
       3,145,728 SYSADM.CASH_DISBURSE_DIST
       3,145,728 SYSADM.DEMAND_SUPPLY_LINK

Let us know how this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.