| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Calculate the BIGGEST table(in space) in one tablespace efficiently?
dominica_at_gmail.com wrote:
> Hi Everyone,
>
> I am running oracle 8.1.7.4 (32 bits) on solaris 5.9 platform.
>
> I have a 115 GIG tablespace (ABC), and
> I would like to find out the biggest table in this tablespace.
>
> I am not counting the number of rows(records).
> I know I could do "select count(*) from each_table".
> The reason why the max # of rows table does not mean the
> biggest space consumer, is as follows:-
>
> Some of my table has 1000 columns(don't ask me why there is so many
> columns,
> I did not design those tables) and some table might have only 3
> columns.
>
> I want to make a list to see the top 10 BIGGEST TABLES( the one
> consume the
> most space in this tablespace).
>
> I know I might able to do something with dba_segments table,
> but what is the most efficiently way to find out.
>
>
> Thank in advance,
>
>
>
> Dominica
DBA_SEGMENTS could help you with such a determination:
select s.owner, s.segment_name, s.ttl_bytes, s.ttl_blks
from
(select owner, segment_name, sum(bytes) ttl_bytes, sum(blocks)
ttl_blks
from dba_segments
group by owner, segment_name
order by 3 desc, 4 desc) s
where rownum <= 10;
(Code is based upon your statement of wanting to find the 'top 10' space consumers.) The query plan:
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE 1 0 COUNT (STOPKEY)
2 1 VIEW 3 2 SORT (ORDER BY STOPKEY) 4 3 SORT (GROUP BY) 5 4 VIEW OF 'SYS_DBA_SEGS' 6 5 UNION-ALL 7 6 NESTED LOOPS 8 7 NESTED LOOPS 9 8 NESTED LOOPS 10 9 NESTED LOOPS 11 10 NESTED LOOPS 12 11 VIEW OF 'SYS_OBJECTS' 13 12 UNION-ALL 14 13 TABLE ACCESS (FULL) OF 'TAB$' 15 13 TABLE ACCESS (FULL) OF 'TABPART$ ' 16 13 TABLE ACCESS (FULL) OF 'CLU$' 17 13 TABLE ACCESS (FULL) OF 'IND$' 18 13 TABLE ACCESS (FULL) OF 'INDPART$ ' 19 13 TABLE ACCESS (FULL) OF 'LOB$' 20 13 TABLE ACCESS (FULL) OF 'TABSUBPART$' 21 13 TABLE ACCESS (FULL) OF 'INDSUBPART$' 22 13 TABLE ACCESS (FULL) OF 'LOBFRAG$ ' 23 11 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' 24 23 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE) 25 10 TABLE ACCESS (CLUSTER) OF 'SEG$' 26 25 INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (NON-UNIQUE) 27 9 INDEX (UNIQUE SCAN) OF 'I_FILE2' (UNIQUE) 28 8 TABLE ACCESS (CLUSTER) OF 'TS$' 29 28 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE) 30 7 TABLE ACCESS (CLUSTER) OF 'USER$' 31 30 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE) 32 6 NESTED LOOPS 33 32 NESTED LOOPS 34 33 NESTED LOOPS 35 34 NESTED LOOPS 36 35 TABLE ACCESS (FULL) OF 'UNDO$' 37 35 INDEX (UNIQUE SCAN) OF 'I_FILE2' (UNIQUE) 38 34 TABLE ACCESS (CLUSTER) OF 'SEG$' 39 38 INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (NON-UNIQUE) 40 33 TABLE ACCESS (CLUSTER) OF 'TS$' 41 40 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE) 42 32 TABLE ACCESS (CLUSTER) OF 'USER$' 43 42 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE) 44 6 NESTED LOOPS 45 44 NESTED LOOPS 46 45 NESTED LOOPS 47 46 TABLE ACCESS (FULL) OF 'FILE$' 48 46 TABLE ACCESS (CLUSTER) OF 'SEG$' 49 48 INDEX (RANGE SCAN) OF 'I_FILE#_BLOCK#' (NON-UNIQUE) 50 45 TABLE ACCESS (CLUSTER) OF 'TS$' 51 50 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE) 52 44 TABLE ACCESS (CLUSTER) OF 'USER$' 53 52 INDEX (UNIQUE SCAN) OF 'I_USER#'(NON-UNIQUE) Statistics
0 recursive calls
0 db block gets
27731 consistent gets
4285 physical reads
0 redo size
1044 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
10 rows processed
If you're computing statistics on tables you could use dba_tables to provide such information:
select t.owner, t.table_name, t.occupied_blocks
from
(select owner, table_name, blocks-empty_blocks occupied_blocks
from dba_tables
order by 3 desc nulls last) t
where rownum <= 10;
The query plan:
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE 1 0 COUNT (STOPKEY)
2 1 VIEW 3 2 SORT (ORDER BY STOPKEY) 4 3 NESTED LOOPS 5 4 NESTED LOOPS 6 5 NESTED LOOPS (OUTER) 7 6 NESTED LOOPS (OUTER) 8 7 NESTED LOOPS (OUTER) 9 8 NESTED LOOPS (OUTER) 10 9 NESTED LOOPS 11 10 TABLE ACCESS (FULL) OF 'OBJ$' 12 10 TABLE ACCESS (CLUSTER) OF 'TAB$' 13 12 INDEX (UNIQUE SCAN) OF 'I_OBJ#' (NON-UNIQUE) 14 9 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' 15 14 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE) 16 8 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE) 17 7 TABLE ACCESS (CLUSTER) OF 'USER$' 18 17 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE) 19 6 TABLE ACCESS (CLUSTER) OF 'SEG$' 20 19 INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (NON-UNIQUE) 21 5 TABLE ACCESS (CLUSTER) OF 'TS$' 22 21 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE) 23 4 TABLE ACCESS (CLUSTER) OF 'USER$' 24 23 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
Statistics
0 recursive calls
0 db block gets
20987 consistent gets
120 physical reads
0 redo size
920 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
However you may not be computing stats on all tables; DBA_SEGMENTS would cover every table in your database and provide, in my opinion, the most complete 'coverage'.
Were you using either release of 9i or either release of 10g you could use the DBMS_SPACE package to compute space used using, interestingly enough, the SPACE_USED procedure. Though the DBMS_SPACE package is available with 8.1.7 the only procedures it contains are UNUSED_SPACE and FREE_BLOCKS, neither of which would help you as you're wanting to determine the top 10 tables based upon bytes/blocks consumed.
Other opinions/solutions may be forthcoming.
David Fitzjarrell Received on Thu Jan 26 2006 - 21:21:11 CST
 |
 |