--SET DEFINE OFF;
--SET FEEDBACK      OFF
--SET TRIMSPOOL     ON
--SET VERIFY       OFF
--SET LINESIZE 3500

--SET LINESIZE 3500
--SET PAGESIZE   0
--SET VERIFY     OFF
SET FEEDBACK   OFF
SET LINESIZE 3500
SET VERIFY     OFF

alter session set nls_numeric_characters = ',.';

-- Disect the input argument, and get the owner name and table name into two seperate 
-- substitution variables. The owner name defaults to the current user.



@@header_dbinfo 107 DC

CLEAR COLUMNS;

COLUMN FILE_TYPE                 FORMAT A14                        HEADING "File Type";
COLUMN PERCENT_SPACE_USED        FORMAT 9G999G999G999              HEADING "% Space used";
COLUMN PERCENT_SPACE_RECLAIMABLE FORMAT 9G999G999G999              HEADING "% Space reclaimable";
COLUMN NUMBER_OF_FILES           FORMAT 9G999G999G999              HEADING "N. Files";


SELECT FILE_TYPE, PERCENT_SPACE_USED, PERCENT_SPACE_RECLAIMABLE, NUMBER_OF_FILES
FROM V$FLASH_RECOVERY_AREA_USAGE;

CLEAR COLUMNS;

COLUMN NAME              FORMAT A30                        HEADING "Name";
COLUMN SPACE_LIMIT       FORMAT 9G999G999G999              HEADING "Space limit (Gb)";
COLUMN SPACE_USED        FORMAT 9G999G999G999              HEADING "Space used (Gb)";
COLUMN SPACE_RECLAIMABLE FORMAT 9G999G999G999              HEADING "Space reclaimable (Gb)";
COLUMN NUMBER_OF_FILES   FORMAT 9G999G999G999              HEADING "N. Files";

SELECT NAME, SPACE_LIMIT/1024/1024/1024 as SPACE_LIMIT, SPACE_USED/1024/1024/1024 SPACE_USED, 
             SPACE_RECLAIMABLE/1024/1024/1024 SPACE_RECLAIMABLE, NUMBER_OF_FILES
FROM V$RECOVERY_FILE_DEST;


exec dbms_output.new_line;
exec dbms_output.new_line;
exec dbms_output.put_line(chr(10)||chr(10)||chr(10)||'Espacio ocupado por backups + espacio no reclamable del resto de los tipos de ficheros');

select 'Required space: ' || round((A.espacio_backup*C.SPACE_LIMIT)/100/1024/1024/1024 + (B.espacio_resto*C.SPACE_LIMIT)/100/1024/1024/1024) as espacio_requerido_fra_GB
from
(select PERCENT_SPACE_USED as espacio_backup from V$FLASH_RECOVERY_AREA_USAGE where FILE_TYPE = 'BACKUPPIECE') A,
(select sum(PERCENT_SPACE_USED - PERCENT_SPACE_RECLAIMABLE) as espacio_resto from V$FLASH_RECOVERY_AREA_USAGE where FILE_TYPE <> 'BACKUPPIECE') B,
(select SPACE_LIMIT from v$recovery_file_dest) C;

SET FEEDBACK ON
CLEAR BREAKS
CLEAR COMPUTE