domingo, 23 de fevereiro de 2014

Descobrir tamanho do Banco de Dados Oracle

Para saber qual o espaço do banco de dados instalado em sua máquina, use o código abaixo.
SET serveroutput ON  DECLARE    dbf NUMBER;    tmpdbf NUMBER;    lgf NUMBER;    ctl NUMBER;    soma NUMBER;  BEGIN    SELECT TRUNC(SUM(bytes/1024/1024),2) INTO dbf FROM v$datafile;    SELECT TRUNC(SUM(bytes/1024/1024),2) INTO tmpdbf FROM v$tempfile;    SELECT TRUNC(SUM(bytes/1024/1024),2) INTO lgf FROM v$log l, v$logfile lf where l.group# = lf.group#;    SELECT TRUNC(SUM(block_size*file_size_blks/1024/1024),2) INTO ctl FROM v$controlfile;    SELECT TRUNC((dbf+tmpdbf+lgf+ctl)/1024,2) INTO soma FROM dual;    DBMS_OUTPUT.PUT_LINE(chr(10));    DBMS_OUTPUT.PUT_LINE('Datafiles: '|| dbf ||' MB');    DBMS_OUTPUT.PUT_LINE(chr(0));    DBMS_OUTPUT.PUT_LINE('Tempfiles: '|| tmpdbf ||' MB');    DBMS_OUTPUT.PUT_LINE(chr(0));    DBMS_OUTPUT.PUT_LINE('Logfiles: '|| lgf ||' MB');    DBMS_OUTPUT.PUT_LINE(chr(0));    DBMS_OUTPUT.PUT_LINE('Controlfiles: '|| ctl ||' MB');    DBMS_OUTPUT.PUT_LINE(chr(0));    DBMS_OUTPUT.PUT_LINE('Total Tamanho: '|| soma ||' GB');  END;