ORA SQL/PLUS

main

monitor

ddl

objects

dml

stato e configurazione

rman & dpump

pl/sql

cluster

ASM

main

config

start-shut

process

memory

monitor

diag

user

rman & dpump

pl/sql

cluster

ASM

Esempio di report con grand total

COLUMN DUMMY NOPRINT;
COMPUTE SUM OF SALARY ON DUMMY;
BREAK ON DUMMY;
SELECT NULL DUMMY,DEPARTMENT_ID, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE SALARY>12000
ORDER BY DEPARTMENT_ID;

Esempio di Stored Procedure

  CREATE OR REPLACE PROCEDURE "AUDIT_ADMIN"."JOB_PURGE_AND_COPY_FGA" as
 
  type rec_type  IS TABLE OF audit_admin.dba_fga_audit_t
rail%rowtype index by binary_integer;
  rec rec_type;
  Current_SCN NUMBER;
  CURSOR cur_collect_fga(current_scn number) IS
  select *
  FROM sys.dba_fga_audit_trail
  WHERE scn < current_scn;
 
  Deleted_Rows number;
 
  BEGIN
--      Current_Scn:= dbms_flashback.get_system_change_number;
      select max(scn) into Current_Scn from sys.dba_fga_audit_trail;
      for rec_collect_fga IN cur_collect_fga(Current_Scn)
      LOOP
        INSERT INTO audit_admin.dba_fga_audit_trail
         VALUES ( rec_collect_fga.SESSION_ID
                , rec_collect_fga.TIMESTAMP
                , rec_collect_fga.DB_USER
                , rec_collect_fga.OS_USER
                , rec_collect_fga.USERHOST
                , rec_collect_fga.CLIENT_ID
                , rec_collect_fga.ECONTEXT_ID
                , rec_collect_fga.EXT_NAME
                , rec_collect_fga.OBJECT_SCHEMA
                , rec_collect_fga.OBJECT_NAME
                , rec_collect_fga.POLICY_NAME
                , rec_collect_fga.SCN
                , rec_collect_fga.SQL_TEXT
                , rec_collect_fga.SQL_BIND
                , rec_collect_fga.COMMENT$TEXT
                , rec_collect_fga.STATEMENT_TYPE
                , rec_collect_fga.EXTENDED_TIMESTAMP
                , rec_collect_fga.PROXY_SESSIONID
                , rec_collect_fga.GLOBAL_UID
                , rec_collect_fga.INSTANCE_NUMBER
                , rec_collect_fga.OS_PROCESS
                , rec_collect_fga.TRANSACTIONID
                , rec_collect_fga.STATEMENTID
                , rec_collect_fga.ENTRYID);
      END LOOP;
 
      DELETE sys.dba_fga_audit_trail WHERE scn < Cur
rent_Scn;
      Deleted_Rows := sql%rowcount;
/*
      UPDATE audit_monitor SET record_inserted = record
_inserted - Deleted_Rows + 1,
                               timestamp = sysdate, pending_job= NULL;
*/
      UPDATE audit_monitor SET record_inserted = 0,
                               timestamp = sysdate,
pending_job= NULL;
      COMMIT;
   EXCEPTION
      WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR('-20002','Errore in copy and purge procedure', TRUE);
  END JOB_PURGE_AND_COPY_FGA;