tamano tablespaces

 set serveroutput on size 10000

clear breaks

clear computes

clear columns

set pagesize 50

set linesize 300

set heading on

PROMPT TAMAÑO TABLESPACES ...

column tablespace_name heading 'Tablespace' justify left format a20 truncated

column tbsize heading 'Size|(Mb) ' justify left format 9,999,999.99

column tbused heading 'Used|(Mb) ' justify right format 9,999,999.99

column tbfree heading 'Free|(Mb) ' justify right format 9,999,999.99

column tbusedpct heading 'Used |% ' justify left format a8

column tbfreepct heading 'Free |% ' justify left format a8

break on report

compute sum label 'Totals:' of tbsize tbused tbfree on report

select t.tablespace_name, round(a.bytes,2) tbsize,

nvl(round(c.bytes,2),'0') tbfree,

nvl(round(b.bytes,2),'0') tbused,

to_char(round(100 * (nvl(b.bytes,0)/nvl(a.bytes,1)),2)) || '%' tbusedpct,

to_char(round(100 * (nvl(c.bytes,0)/nvl(a.bytes,1)),2)) || '%' tbfreepct

from dba_tablespaces t,

(select tablespace_name, round(sum(bytes)/1024/1024,2) bytes

from dba_data_files

group by tablespace_name

union

select tablespace_name, round(sum(bytes)/1024/1024,2) bytes

from dba_temp_files

group by tablespace_name ) a,

(select e.tablespace_name, round(sum(e.bytes)/1024/1024,2) bytes

from dba_segments e

group by e.tablespace_name

union

select tablespace_name, sum(max_size) bytes

from v$sort_segment

group by tablespace_name) b,

(select f.tablespace_name, round(sum(f.bytes)/1024/1024,2) bytes

from dba_free_space f

group by f.tablespace_name

union

select tmp.tablespace_name, (sum(bytes/1024/1024) - sum(max_size)) bytes

from dba_temp_files tmp, v$sort_segment sort

where tmp.tablespace_name = sort.tablespace_name

group by tmp.tablespace_name) c

where

t.tablespace_name = a.tablespace_name (+)

and t.tablespace_name = b.tablespace_name (+)

and t.tablespace_name = c.tablespace_name (+)

order by t.tablespace_name

/

TAMANO TABLAS

select owner,table_name,avg_row_len,num_rows,

round(sum(num_rows*avg_row_len)) as bytes, 

round(sum(num_rows*avg_row_len/1024),2) as kilobytes,

round(sum(num_rows*avg_row_len/1024/1024),2) as megabytes,

round(sum(num_rows*avg_row_len/1024/1024/1024),2) as gigabytes

--round(sum(num_rows*avg_row_len/1024/1024/1024/1024),2) as terabytes

from dba_tables

where num_rows > 0

group by owner,table_name,avg_row_len,num_rows

order by gigabytes desc