Verifica Tamanho Tabelas

SET SERVEROUTPUT ON;
SET PAGESIZE 1000;
SET LINESIZE 255;
SET FEEDBACK OFF;
COL "FILE NAME" for a45;
SELECT
    Substr(df.tablespace_name,1,20) "Tablespace Name",
    Substr(df.file_name,1,40) "File Name",
    Round(df.bytes/1024/1024,2) "Size (M)",
    Round(e.used_bytes/1024/1024,2) "Used (M)",
    Round(f.free_bytes/1024/1024,2) "Free (M)",
    Rpad(' '|| Rpad ('X',Round(e.used_bytes*10/df.bytes,0), 'X'),11,'-') "% Used"
FROM  
    DBA_DATA_FILES df,
        (SELECT
            file_id,
            Sum(Decode(bytes,NULL,0,bytes)) used_bytes
        FROM
            dba_extents
        GROUP by file_id) E,
            (SELECT
                Max(bytes) free_bytes,
                file_id
            FROM
                dba_free_space
            GROUP BY file_id) f
WHERE
        e.file_id (+) = df.file_id
AND   
        df.file_id  = f.file_id (+)
ORDER BY
        df.tablespace_name, df.file_name;
SET SERVEROUTPUT OFF;








TEMPORARY TABLESPACES

SET SERVEROUTPUT ON;
SET PAGESIZE 1000;
SET LINESIZE 255;
SET FEEDBACK OFF;
COL "FILE NAME" for a45;
SELECT
    Substr(df.tablespace_name,1,20) "Tablespace Name",
    Substr(df.file_name,1,40) "File Name",
    Round(df.bytes/1024/1024,2) "Size (M)",
    Round(e.used_bytes/1024/1024,2) "Used (M)",
    Round(f.free_bytes/1024/1024,2) "Free (M)",
    Rpad(' '|| Rpad ('X',Round(e.used_bytes*10/df.bytes,0), 'X'),11,'-') "% Used"
FROM  
    DBA_TEMP_FILES df,
        (SELECT
            file_id,
            Sum(Decode(bytes,NULL,0,bytes)) used_bytes
        FROM
            dba_extents
        GROUP by file_id) E,
            (SELECT
                Max(bytes) free_bytes,
                file_id
            FROM
                dba_free_space
            GROUP BY file_id) f
WHERE
        e.file_id (+) = df.file_id
AND   
        df.file_id  = f.file_id (+)
ORDER BY
        df.tablespace_name, df.file_name;
SET SERVEROUTPUT OFF;




SELECT t.table_name AS "Nome da Tabela",
       t.TABLESPACE_NAME AS "Nome Tablespace",
       t.num_rows AS "Numero de Linhas",
       t.avg_row_len AS "Avg Row Len",
       Trunc((t.blocks * p.value)/1024) AS "Tamanho em KB",
       t.last_analyzed AS "Last Analyzed"      
FROM   dba_tables t,
       v$parameter p
WHERE t.owner = 'LOGIX'
AND   p.name = 'db_block_size'
AND      t.table_name = 'LANCAMENTOS'
ORDER BY 5 desc



select
    owner,
    table_name,
    file_id,
    blocks,
    empty_blocks,
    num_rows,
    to_char(last_analyzed,'DD-MM-RRRR HH24:MI:SS') as "ANALYZE"
from dba_tables
where table_name = 'LANCAMENTOS';






select table_name, num_rows from dba_tables where owner = 'LOGIX' order by table_name;

select distinct table_name, column_id  from all_tab_cols where owner = 'LOGIX' order by table_name;

spool runme.sql
select 'select count(*) from '||table_name||';' from dba_tables where owner = 'XXX';
spool off
@runme

select uc0u8232 'a0'a0 table_name, u8232 'a0'a0 num_rows counter u8232 from u8232 'a0'a0 dba_tables u8232 where u8232 'a0'a0 owner = 'LOGIX'u8232 order by u8232 'a0'a0 table_name;













select
    segment_name as "None da Tabela",
    sum(((bytes)/1024)/1024)/1024 as "Tamanho em MB"
from
    dba_segments
where
    segment_name = 'LANCAMENTOS'
group by
    segment_name;





select (8192*10000000)/1024/1024 as "Tamanho" from dual;




select segment_name, sum(bytes)/1024/1024/1024 from dba_segments where segment_name = 'CTB_LANC_CTBL_EST' group by segment_name;




select
    segment_name,
    count(extent_id),
    sum(bytes)/1024/1024/1024
from
    dba_extents
where
    segment_name = 'CTB_LANC_CTBL_EST'
group by
    segment_name;





SELECT
    'analyze table ' || OWNER || '.' || TABLE_NAME || ' compute statistics;'
FROM
    dba_tables
WHERE owner = 'LOGIX'



analyze table LOGIX.LANCAMENTOS compute statistics;


select count(*) segments, round(sum(bytes)/1024/1024,2) size_mb from user_segments;


select count(*) segments, round(sum(bytes)/1024/1024,2) size_mb from user_segments;