Script – Monitor Flashback Logs

PROMPT How Far Back Can We Flashback To (Time)?

PROMPT

select to_char(oldest_flashback_time,'dd-mon-yyyy hh24:mi:ss') "Oldest Flashback Time"

 from v$flashback_database_log;

PROMPT

PROMPT How Far Back Can We Flashback To (SCN)?

PROMPT

col oldest_flashback_scn format 99999999999999999999999999

select oldest_flashback_scn from v$flashback_database_log;

PROMPT

PROMPT Flashback Area Usage

SELECT * FROM   V$FLASH_RECOVERY_AREA_USAGE;

PROMPT

col ROUND(SPACE_LIMIT/1048576) heading "Space Allocated (MB)" format 999999

col round(space_used/1048576) heading "Space Used (MB)" format 99999

col name Heading "Flashback Location" format a40

select name, round(space_limit/1048576),round(space_used/1048576)

 from  v$RECOVERY_FILE_DEST;

How Far Back Can We Flashback To (Time)?

Oldest Flashback Time

-----------------------------

05-jul-2009 22:53:07

How Far Back Can We Flashback To (SCN)?

       OLDEST_FLASHBACK_SCN

---------------------------

                15321928761

Flashback Area Usage

FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES

------------ ------------------ ------------------------- ---------------

CONTROLFILE                   0                         0               0

ONLINELOG                     0                         0               0

ARCHIVELOG                16.33                     16.33              51

BACKUPPIECE               16.34                     16.34               6

IMAGECOPY                     0                         0               0

FLASHBACKLOG              45.94                     10.61             204

6 rows selected.

Flashback Location                       Space Allocated (MB) Space Used (MB)

---------------------------------------- -------------------- ---------------

/u05/oradata/flash_recovery_area                         5120            4025

Script – Sessions with high physical reads

-- Very handy script

set linesize 120

col os_user format a10

col username format a15

col pid format 9999999999

PROMPT SESSIONS SORTED BY PHYSICAL READS

PROMPT

select

  OSUSER os_user,username,

    PROCESS pid,

    ses.SID sid,

    SERIAL#,

    PHYSICAL_READS,

     BLOCK_CHANGES

 from       v$session ses,

   v$sess_io sio

  where      ses.SID = sio.SID

and username is not null

and status='ACTIVE'

 order      by PHYSICAL_READS;

Make Table read only

SQL> CREATE TABLE tbl_read_only (id NUMBER);
Table created.

SQL> INSERT INTO tbl_read_only VALUES(1);
1 row created.

SQL> COMMIT;
Commit complete.

SQL> SELECT * FROM tbl_read_only;
        ID


———-


         1

SQL> CREATE TRIGGER trg_tbl_read_only BEFORE DELETE OR INSERT OR UPDATE


  2  ON tbl_read_only


  3  BEGIN


  4  RAISE_APPLICATION_ERROR(-20202,’Table Status: READ ONLY!!!’);


  5  END;


  6  /
Trigger created.
SQL> INSERT INTO tbl_read_only VALUES(2);
INSERT INTO tbl_read_only VALUES(2)
            *
ERROR at line 1:

ORA-20202: Table Status: READ ONLY!!!

Block developers from using TOAD and other tools on production databases

CONNECT / AS SYSDBA;

CREATE OR REPLACE TRIGGER block_tools_from_prod

  AFTER LOGON ON DATABASE

DECLARE

  v_prog sys.v_$session.program%TYPE;

BEGIN

  SELECT program INTO v_prog

    FROM sys.v_$session

  WHERE  audsid = USERENV('SESSIONID')

    AND  audsid != 0  -- Don't Check SYS Connections

    AND  ROWNUM = 1;  -- Parallel processes will have the same AUDSID's

  IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR -- Toad

     UPPER(v_prog) LIKE '%SQLNAV%' OR     -- SQL Navigator

     UPPER(v_prog) LIKE '%PLSQLDEV%' OR -- PLSQL Developer

     UPPER(v_prog) LIKE '%BUSOBJ%' OR   -- Business Objects

     UPPER(v_prog) LIKE '%EXCEL%'       -- MS-Excel plug-in

  THEN

     RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed here.');

  END IF;

END;

/

SHOW ERRORS