пятница, 6 сентября 2013 г.

Check tablespace size usage

Rewritten version from Top DBA Shell Scripts for Monitoring the Database based on MAXBYTES of each file within tablespace:
SELECT F.TABLESPACE_NAME,
       TO_CHAR ((T.ALLOCATED_SPACE - F.FREE_SPACE),'999,999') "USED (MB)",
       TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTAL (MB)",
       TO_CHAR (T.TOTAL_SPACE - (T.ALLOCATED_SPACE - F.FREE_SPACE), '999,999') "FREE (MB)",
       TO_CHAR ((ROUND (((T.TOTAL_SPACE - (T.ALLOCATED_SPACE - F.FREE_SPACE))/T.TOTAL_SPACE)*100)),'999')||' %' PER_FREE
FROM   (
       SELECT       TABLESPACE_NAME,
                    ROUND (SUM (BLOCKS*(SELECT VALUE/1024
                                        FROM V$PARAMETER
                                        WHERE NAME = 'db_block_size')/1024)
                           ) FREE_SPACE
       FROM DBA_FREE_SPACE
       GROUP BY TABLESPACE_NAME
       ) F,
       (
       SELECT TABLESPACE_NAME,
       ROUND(SUM (MAXBYTES/1048576)) TOTAL_SPACE, 
       ROUND (SUM (BYTES/1048576)) ALLOCATED_SPACE
       FROM DBA_DATA_FILES
       GROUP BY TABLESPACE_NAME
       ) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME
--AND ROUND (((T.TOTAL_SPACE - (T.ALLOCATED_SPACE - F.FREE_SPACE))/T.TOTAL_SPACE)*100) < 10;

Restore archivelog from backup by logseq/scn

See details in RMAN effective use
set archivelog destination to '/disk1/oracle/temp_restore';
#restore archivelog from scn=460779 until scn =506141
restore archivelog from logseq 1 until logseq 10;