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;
page revision: 3, last edited: 19 Jan 2018 08:52