Procedure to move Aud$ table to non system tablespaces
Note:1019377.6: Script to move SYS.AUD$ table out of SYSTEM tablespace Note:166301.1 : How to Reorganize SYS.AUD$ Table Note:73408.1 : How to Truncate, Delete, or Purge Rows from the Audit Trail Table SYS.AUD$ Note:72460.1 : Moving AUD$ to another tablespace and adding triggers to AUD$
select a.executions, a.disk_reads, a.disk_reads/a.executions dre, a.buffer_gets, a.buffer_gets/a.executions bge, b.username, a.first_load_time, A.HASH_VALUE from v$sql a, all_users b where a.executions > 0 and a.disk_reads > 0 and a.parsing_user_id = b.user_id AND A.SQL_TEXT LIKE '%SELECT%' order by 7,2
SELECT substr(s1.username,1,12) "WAITING User", substr(s1.osuser,1,8) "OS User", substr(to_char(w.session_id),1,5) "Sid", P1.spid "PID", substr(s2.username,1,12) "HOLDING User", substr(s2.osuser,1,8) "OS User", substr(to_char(h.session_id),1,5) "Sid", P2.spid "PID" FROM sys.v_$process P1, sys.v_$process P2, sys.v_$session S1, sys.v_$session S2, sys.dba_locks w, sys.dba_locks h WHERE (((h.mode_held != 'None') and (h.mode_held != 'Null') and ((h.mode_requested = 'None') or (h.mode_requested = 'Null'))) and (((w.mode_held = 'None') or (w.mode_held = 'Null')) and ((w.mode_requested != 'None') and (w.mode_requested != 'Null')))) and w.lock_type = h.lock_type and w.lock_id1 = h.lock_id1 and w.lock_id2 = h.lock_id2 and w.session_id != h.session_id and w.session_id = S1.sid and h.session_id = S2.sid AND S1.paddr = P1.addr AND S2.paddr = P2.addr;
whatsql.sql
SELECT /*+ ORDERED */ s.sid, s.username, s.osuser, nvl(s.machine, '?') machine, nvl(s.program, '?') program, s.process F_Ground, p.spid B_Ground, X.sql_text FROM sys.v_$session S, sys.v_$process P, sys.v_$sqlarea X WHERE s.osuser like lower(nvl('&OS_User','%')) AND s.username like upper(nvl('&Oracle_User','%')) AND s.sid like nvl('&SID','%') AND s.paddr = p.addr AND s.type != 'BACKGROUND' AND s.sql_address = x.address AND s.sql_hash_value = x.hash_value ORDER BY S.sid;
set pagesize 66 col c1 for a9 col c1 heading "OS User" col c2 for a9 col c2 heading "Oracle User" col b1 for a9 col b1 heading "Unix PID" col b2 for 9999 justify left col b2 heading "SID" col b3 for 99999 justify left col b3 heading "SERIAL#" col sql_text for a35 break on b1 nodup on c1 nodup on c2 nodup on b2 nodup on b3 skip 3 select c.spid b1, b.osuser c1, b.username c2, b.sid b2, b.serial# b3, a.sql_text from v$sqltext a, v$session b, v$process c where a.address = b.sql_address -- and b.status = 'ACTIVE' /* YOU CAN CHOOSE THIS OPTION ONLY TO SEE -- ACTVE TRANSACTION ON THAT MOMENT */ and b.paddr = c.addr and a.hash_value = b.sql_hash_value order by c.spid,a.hash_value,a.piece /
Stop Users Granting Access To Their Own Objects To Any Other Users [ID 759699.1]
VERSION 10.1.2 ++
CREATE or replace TRIGGER grant_t1 BEFORE GRANT ON database WHEN (ora_dict_obj_name like '%' and ora_dict_obj_owner='') BEGIN RAISE_APPLICATION_ERROR(-20001,'Protected : You are not allowed to grant access to the objects you own to any other users'); END; /
set FEED off set timing off set HEA OFF WITH greeting_info AS ( SELECT TO_NUMBER(TO_CHAR(SYSDATE,'HH24')) AS time , 12 AS noon , 17 AS evening FROM dual ) SELECT CASE WHEN time < noon THEN 'Good Morning Abul' WHEN time BETWEEN noon AND evening THEN 'Good After noon Abul ' --'Good Afternoon Abul !' ELSE 'Good Evening Abul' END FROM greeting_info; set HEA ON
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS'; set FEED on col schemaname new_value name --select lower(user) schemaname from v$session where sid=(select sid from v$mystat where rownum =1); set sqlprompt "&&_USER@&&_CONNECT_IDENTIFIER>" set linesize 9999 set timing on set time on
Tracking Oracle initialization parameter changes Oracle Tips by Kamran Agayev Agamehdi May 29, 2009
Providing a history of changes to your Oracle database parameters is not only a DBA best practice, it's absolutely required in a mission-critical production environment. Back when Oracle parameters were kept in a flat file on the server (the init.ora file) tracking changes was difficult.
However, once Oracle introduced the "spfile" feature it became easier to track changes to initialization parameters. Below are two common techniques for tracking Oracle initialization parameters, auditing, and using the extra-cost AWR method.
Note: If you have purchased the extra cost performance pack and diagnostic pack (and have access to the AWR dba_hist_parameter table), it's easy to run a script to track all changes to your initialization parameters.
Oracle guru Kerry Osborne offers this nice method for tracking init.ora parm changes:
-- parm_mods.sql -- -- Shows all parameters (including hidden) that have been modified. -- Uses the lag function so that a single record is returned for each change. -- It uses AWR data - so only snapshots still in the database will be included. -- -- The script prompts for a parameter name (which can be wild carded). -- Leaving the parameter name blank matches any parameter (i.e. it will show all changes). -- Calculated hidden parameters (those that start with two underscores like "__shared_pool_size") -- will not be displayed unless requested with a Y. -- -- Kerry Osborne -- -- Note: I got this idea from Jeff White. -- set linesize 155 col time for a15 col parameter_name format a50 col old_value format a30 col new_value format a30 break on instance skip 3 select instance_number instance, snap_id, time, parameter_name, old_value, new_value from ( select a.snap_id,to_char(end_interval_time,'DD-MON-YY HH24:MI') TIME, a.instance_number, parameter_name, value new_value, lag(parameter_name,1) over (partition by parameter_name, a.instance_number order by a.snap_id) old_pname, lag(value,1) over (partition by parameter_name, a.instance_number order by a.snap_id) old_value , decode(substr(parameter_name,1,2),'__',2,1) calc_flag from dba_hist_parameter a, dba_Hist_snapshot b , v$instance v where a.snap_id=b.snap_id and a.instance_number=b.instance_number and parameter_name like nvl('¶meter_name',parameter_name) and a.instance_number like nvl('&instance_number',v.instance_number) ) where new_value != old_value and calc_flag not in (decode('&show_calculated','Y',3,2)) order by 1,2 /
set FEED off set timing off set HEA OFF WITH greeting_info AS ( SELECT TO_NUMBER(TO_CHAR(SYSDATE,'HH24')) AS time , 12 AS noon , 15 AS evening FROM dual ) SELECT CASE WHEN time < noon THEN 'Good Morning Abul' WHEN time BETWEEN noon AND evening THEN 'Good After noon Abul ' --'Good Afternoon Abul !' ELSE 'Good Evening Abul' END FROM greeting_info; set HEA ON
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS'; set FEED on
set sqlprompt "&&_USER@&&_CONNECT_IDENTIFIER>" set linesize 9999 set timing on set time on
column c1 format a30 heading "Constraint|name" column c2 format a30 heading "Table|name" column c3 format a20 heading "Column|name" column c4 format 99 heading "Position"
SELECT acc1.constraint_name c1, acc1.table_name c2, acc1.column_name c3, acc1.position c4 FROM all_cons_columns acc1, all_constraints ac1 WHERE ac1.constraint_name = acc1.constraint_name AND ac1.constraint_type = 'R' and acc1.owner ='&1' AND ac1.owner = acc1.owner AND (acc1.owner, acc1.table_name, acc1.column_name, acc1.position) IN (SELECT acc.owner, acc.table_name, acc.column_name, acc.position FROM all_cons_columns acc, all_constraints ac WHERE ac.constraint_name = acc.constraint_name and acc.owner=acc1.owner and ac.owner=acc.owner AND ac.constraint_type = 'R' MINUS SELECT table_owner, table_name, column_name, column_position FROM all_ind_columns ) ORDER BY acc1.owner, acc1.constraint_name, acc1.column_name, acc1.position;
Lock Due to Missing Forign Key Index http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4530093713805#26568859366976 http://www.jonadams.net/fk_constraints_and_indexing.htm http://vsbabu.org/oracle/sect16.html http://www.devx.com/getHelpOn/10MinuteSolution/16595/1954
comparsions with and without index http://www.akadia.com/services/ora_exchange_partition.html
PROCEDURE SEND_MAIL IS (p_recipient IN VARCHAR2, p_sender IN VARCHAR2, p_sender_text IN VARCHAR2, -- Appear in e-mail as 'From:' p_subject IN VARCHAR2, p_message IN VARCHAR2) AS c utl_smtp.connection; v_msg_string VARCHAR2(4000); -- BEGIN c := utl_smtp.open_connection('*** name of SMTP server ***'); utl_smtp.helo(c, '*** name of SMTP server ***'); utl_smtp.mail(c, p_sender); utl_smtp.rcpt(c, p_recipient); utl_smtp.open_data(c); utl_smtp.write_data(c, 'From' || ': ' || p_sender_text || utl_tcp.CRLF); utl_smtp.write_data(c, 'To' || ': ' || p_recipient || utl_tcp.CRLF); utl_smtp.write_data(c, 'Subject' || ': ' || p_subject|| utl_tcp.CRLF); utl_smtp.write_data(c, utl_tcp.CRLF || p_message); utl_smtp.close_data(c); utl_smtp.quit(c); -- EXCEPTION -- WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN BEGIN utl_smtp.quit(c); EXCEPTION WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN NULL; -- When the SMTP server is down or unavailable, we don't have -- a connection to the server. The quit call will raise an -- exception that we can ignore. END; RAISE_APPLICATION_ERROR(-20000, 'Failed to send mail due to the following error: ' || SQLERRM); END;
This is called by the Check Alert log procedure:
IS mail_msg VARCHAR2(150); line_text VARCHAR2(250); var_count NUMBER := 0; alert_file UTL_FILE.file_type; alert_file_newname VARCHAR2(100); BEGIN alert_file := UTL_FILE.fopen('ALERT_LOG_DIR',**alert log file name','R'); IF UTL_FILE.is_open(alert_file) THEN LOOP EXIT WHEN var_count > 5; -- UTL_FILE.get_line(alert_file, line_text); -- -- -- This check is ONLY for 'ORA-' errors ... -- IF instr(line_text,'ORA-') > 0 THEN var_count := var_count + 1; mail_msg := 'The following error has been detected in the Alert Log:' ||CHR(10)||line_text; SEND_MAIL('****recipient****','Null','xxxxx','xxxxxxx', TO_NUMBER(var_count)||' - '||TO_CHAR(SYSDATE,'DD-Mon-YY HH24:MI')||' - '||mail_msg ); END IF; -- END LOOP; UTL_FILE.fclose(alert_file); END IF; -- -- EXCEPTION WHEN utl_file.invalid_mode THEN RAISE_APPLICATION_ERROR (-20051, 'Invalid Mode Parameter'); WHEN utl_file.invalid_path THEN RAISE_APPLICATION_ERROR (-20052, 'Invalid File Location'); WHEN utl_file.invalid_filehandle THEN RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle'); WHEN utl_file.invalid_operation THEN RAISE_APPLICATION_ERROR (-20054, 'Invalid Operation'); WHEN utl_file.read_error THEN RAISE_APPLICATION_ERROR (-20055, 'Read Error'); WHEN utl_file.internal_error THEN RAISE_APPLICATION_ERROR (-20057, 'Internal Error'); WHEN utl_file.charsetmismatch THEN RAISE_APPLICATION_ERROR (-20058, 'Opened With FOPEN_NCHAR But Later I/O Inconsistent'); WHEN utl_file.file_open THEN RAISE_APPLICATION_ERROR (-20059, 'File Already Opened'); WHEN utl_file.invalid_maxlinesize THEN RAISE_APPLICATION_ERROR(-20060,'Line Size Exceeds 32K'); WHEN utl_file.invalid_filename THEN RAISE_APPLICATION_ERROR (-20061, 'Invalid File Name'); WHEN utl_file.access_denied THEN RAISE_APPLICATION_ERROR (-20062, 'File Access Denied By'); WHEN utl_file.invalid_offset THEN RAISE_APPLICATION_ERROR (-20063,'FSEEK Param Less Than 0'); -- -- No data found can be raised when the GET_LINE action reaches EOF... -- WHEN no_data_found THEN NULL; WHEN others THEN RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error'|| SQLERRM); END;
Make sure you have ALERT_LOG_DIR already created as an External Directory, so that UTL_FILE can find the alert log
As you can see I'm no PLSQL guru myself. It only checks for the first 4 ORA_errors as you may get your mail box filled in your absence!
Oracle log switch SQL reports rem rem Name: log_stat.sql rem rem FUNCTION: Provide a current status for redo logs rem rem COLUMN first_change# FORMAT 99999999 HEADING Change# COLUMN group# FORMAT 9,999 HEADING Grp# COLUMN thread# FORMAT 999 HEADING Th# COLUMN sequence# FORMAT 999,999 HEADING Seq# COLUMN members FORMAT 999 HEADING Mem COLUMN archived FORMAT a4 HEADING Arc? COLUMN first_time FORMAT a21 HEADING 'Switch|Time' BREAK ON thread# SET PAGES 60 LINES 131 FEEDBACK OFF START title132 'Current Redo Log Status' SPOOL rep_out\&db\log_stat SELECT thread#,group#,sequence#,bytes, members,archived, status,first_change#, TO_CHAR(first_time, 'DD-MM-YYYY HH24:MI:SS') first_time FROM sys.v_$log ORDER BY thread#, group#; SPOOL OFF PAUSE Press Enter to continue SET PAGES 22 LINES 80 FEEDBACK ON CLEAR BREAKS CLEAR COLUMNS TTILE OFF
Monitoring Redo Log Switches In addition to the alert logs, the frequency of log switches can also be monitored via the v$log_history and v$archived_log views. This script shows an example of a script to monitor archive log switches:
REM NAME :log_hist.sql REM PURPOSE :Provide info on logs for last 24 hours since last REM PURPOSE :log switch REM USE : From SQLPLUS REM Limitations : None REM MRA 10/14/01 Updated for Oracle9i REM COLUMN thread# FORMAT 999 HEADING 'Thrd#' COLUMN sequence# FORMAT 99999 HEADING 'Seq#' COLUMN first_change# HEADING 'SCN Low#' COLUMN next_change# HEADING 'SCN High#' COLUMN archive_name FORMAT a50 HEADING 'Log File' COLUMN first_time FORMAT a20 HEADING 'Switch Time' COLUMN name FORMAT a30 HEADING 'Archive Log' SET LINES 132 FEEDBACK OFF VERIFY OFF START title132 "Log History Report" SPOOL rep_out\&db\log_hist REM SELECT X.recid,a.thread#, a.sequence#,a.first_change#, a.switch_change#, TO_CHAR(a.first_time,'DD-MON-YYYY HH24:MI:SS') first_time, x.name FROM v$loghist a, v$archived_log x WHERE a.first_time> (SELECT b.first_time-1 FROM v$loghist b WHERE b.switch_change# = (SELECT MAX(c.switch_change#) FROM v$loghist c)) AND x.recid(+)=a.sequence#; SPOOL OFF SET LINES 80 VERIFY ON FEEDBACK ON CLEAR COLUMNS TTITLE OFF PAUSE Press Enter to continue Monitoring Redo Statistics
There are no views in Oracle that allow the user to look directly at a log file’s statistical data. These statistics are in the views V$STATNAME, V$SESSION, V$PROCESS, V$SESSTAT, V$LATCH, and V$LATCHNAME.
REM REM NAME : rdo_stat.sql REM PURPOSE : Show REDO latch statistics REM USE : from SQLPlus REM Limitations : Must have access to v$_ views REM SET PAGES 56 LINES 78 VERIFY OFF FEERemote DBACK OFF START title80 "Redo Latch Statistics" SPOOL rep_out/&&db/rdo_stat rem COLUMN name FORMAT a30 HEADING Name COLUMN percent FORMAT 999.999 HEADING Percent COLUMN total HEADING Total rem SELECT l2.name, immediate_gets+gets Total, immediate_gets "Immediates", misses+immediate_misses "Total Misses", DECODE (100.*(GREATEST(misses+immediate_misses,1)/ GREATEST(immediate_gets+gets,1)),100,0) Percent FROM v$latch l1, v$latchname l2 WHERE l2.name like '%redo%' and l1.latch#=l2.latch# ; rem PAUSE Press Enter to continue rem rem Name: Redo_stat.sql rem rem Function: Select redo statistics from v$sysstat
COLUMN name FORMAT a30 HEADING 'Redo|Statistic|Name' COLUMN value FORMAT 999,999,999 HEADING 'Redo|Statistic|Value' SET PAGES 80 LINES 60 FEERemote DBACK OFF VERIFY OFF START title80 'Redo Log Statistics' SPOOL rep_out/&&db/redo_stat SELECT name, value FROM v$sysstat WHERE name LIKE '%redo%' ORDER BY statistic#; SPOOL OFF SET LINES 24 FEERemote DBACK ON VERIFY ON TTITLE OFF CLEAR COLUMNS CLEAR BREAKS
spool c:\log_hist.txt SET PAGESIZE 90 SET LINESIZE 150 set heading on column "00:00" format 9999 column "01:00" format 9999 column "02:00" format 9999 column "03:00" format 9999 column "04:00" format 9999 column "05:00" format 9999 column "06:00" format 9999 column "07:00" format 9999 column "08:00" format 9999 column "09:00" format 9999 column "10:00" format 9999 column "11:00" format 9999 column "12:00" format 9999 column "13:00" format 9999 column "14:00" format 9999 column "15:00" format 9999 column "16:00" format 9999 column "17:00" format 9999 column "18:00" format 9999 column "19:00" format 9999 column "20:00" format 9999 column "21:00" format 9999 column "22:00" format 9999 column "23:00" format 9999
step 1 : Create procedure ========================= create or replace procedure ssas_externalalertlog(in_instance in varchar2) as path_bdump varchar2(4000); name_alert varchar2(100) := 'alert_' || in_instance || '.log'; begin select value into path_bdump from sys.v_$parameter where name = 'background_dump_dest';
--select --'alert_' || value || '.log' into name_alert --from --sys.v_$parameter --where --name = 'db_name';
step 2 : execute the procedure ============================== execute ssas_externalalertlog('ORACLE_SID')
step 3 : issue this sql statement ================================= col lineno noprint col ora_error noprint col msg_line format a132 set pages 0 lines 300 trimspool on trim on alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss'; alter session set sql_trace=false; break on thedate prompt prompt ERROR IN ALERT LOG FILE - LAST 3 DAYS prompt ===================================== select "LINENO","THEDATE","ORA_ERROR","MSG_LINE" from ( select * from ( select lineno, msg_line, thedate, max( case when (ora_error like 'ORA-%' or ora_error like 'PLS-%') then rtrim(substr(ora_error,1,instr(ora_error,' ')-1),':') else null end ) over (partition by thedate) ora_error from ( select lineno, msg_line, max(thedate) over (order by lineno) thedate, lead(msg_line) over (order by lineno) ora_error from ( select rownum lineno, substr( msg_line, 1, 132 ) msg_line, case when msg_line like '___ ___ __ __:__:__ ____' then to_date( msg_line, 'Dy Mon DD hh24:mi:ss yyyy' ) else null end thedate from ssas_alertlog ) ) ) ) where ora_error is not null and thedate >= (trunc(sysdate) - 3) order by thedate /
step 4 : drop the external table ================================ drop table ssas_alertlog /
Trigger SQL_trace automatically By admin, on July 12th, 2010
Sometimes it is necessary to automatically trigger SQL trace. Automatically here means that code must be added somewhere.
The simplest approach is to create a logon trigger at the database level. To avoid enabling SQL trace for all users, I usually suggest creating a role (named sqltrace in the following example) and temporarily granting it only to the user utilized for the test.
CREATE ROLE sqltrace;
CREATE OR REPLACE TRIGGER enable_sqltrace AFTER LOGON ON DATABASE BEGIN IF (dbms_session.is_role_enabled('SQLTRACE')) THEN EXECUTE IMMEDIATE 'ALTER SESSION SET timed_statistics = TRUE'; EXECUTE IMMEDIATE 'ALTER SESSION SET max_dump_file_size = unlimited'; dbms_monitor.session_trace_enable; END IF; END; /
Find trace file using session id in oracle By admin, on July 8th, 2010
Sometimes it becomes very tough to find the relevant oracle trace file using session id in oracle.You can alter your session before setting an event to identify trace file quite easily but following method doesn't require alteration of session.
SELECT s.sid, s.server, lower( CASE WHEN s.server IN ('DEDICATED','SHARED') THEN i.instance_name || '_' || nvl(pp.server_name, nvl(ss.name, 'ora')) || '_' || p.spid || '.trc' ELSE NULL END ) AS trace_file_name FROM v$instance i, v$session s, v$process p, v$px_process pp, v$shared_server ss WHERE s.paddr = p.addr AND s.sid = pp.sid (+) AND s.paddr = ss.paddr(+) AND s.type = 'USER' ORDER BY s.sid;
set lines 155 col execs for 999,999,999 col avg_etime for 999,999.999 col avg_lio for 999,999,999.9 col begin_interval_time for a30 col node for 99999 break on plan_hash_value on startup_time skip 1 select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value, nvl(executions_delta,0) execs, (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime, (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS where sql_id = nvl('&sql_id','4dqs2k5tynk61') and ss.snap_id = S.snap_id and ss.instance_number = S.instance_number and executions_delta > 0 order by 1, 2, 3 /
Setting Up SQL Developer for MS SQL SERVER 2000 1) Install/Extract SQL Developer 1.2.1 in C:\Program Files\Oracle Making the {$sqldevhome} = C:\Program Files\Oracle\sqldeveloper 2) Obtain the JDBC SQL SERVER Pluggin (jtds-1.2.2-dist.zip) from: http://sourceforge.net/project/showfiles.php?group_id=33291&package_id=25350 3) Unzip jtds-1.2.2-dist.zip Creates: jtds-1.2.2.jar \x86\SSO\ntlmauth.dll
4) Copy the jtds-1.2.2.jar into: {$sqldevhome}\jlib Directory. 5) Copy the the ntlmauth.dll dll in the \x86\SSO subdir into: {$sqldevhome}\jdk\jre\bin\ntlmauth.dll 6) In SQL Developer: Got Menu "Tools" - Preferences... Expand the "[+] Database Choose "Third Party JDBC Drivers" Click "Add Entry" Then Browse for your copy of "{$sqldevhome}\jlib\jtds-1.2.2.jar" Note: Use the jar filename, not its parent directory for entry.
7) Restart SQL Developer and try your SQL Server 2000 Connection
Database Logon Audit Using Trigger: ----------------------------------- Part 1: Create Table Part 2: Create After Success Logon Trigger Part 3: Create After Failure Logon Trigger Part 4: Create before Logoff Trigger Part 5: Formatted Query.
Part 2: Create After Success Logon Trigger ------------------------------------------
create or replace trigger logon_f AFTER SERVERERROR on database BEGIN if (is_servererror(1017)) OR (is_servererror(1012)) then begin insert into v$light_audit_logon ( audit_date, db_user_name, os_user_name, computer_name, ip_address, is_dba, module, comments, SID, SERIAL# ) values ( sysdate, sys_context('userenv','session_user'), sys_context('userenv','os_user'), sys_context('userenv','host'), sys_context('userenv','ip_address'), sys_context('userenv','isdba'), NULL, --sys_context('userenv','module'), 'Failure Connection', --sqlerrm, NULL, NULL ); end; -- insert block end if; end logon_failures;
Part 4: Create before Logoff Trigger ------------------------------------ create or replace trigger ses_logoff BEFORE LOGOFF ON DATABASE BEGIN update sys.v$light_audit_logon set logoff_time = sysdate where ( sid,audit_date) = (select sid,max(audit_date) from sys.v$light_audit_logon where sid = (select sid from sys.v_$session where sid=(select sid from v$mystat where rownum=1)) GROUP BY sid); COMMIT; END;
Part 5: Formatted Query 1/2 -----------------------
SET PAGESIZE 90 SET LINESIZE 200
set heading on column "00:00" format 9999 column "01:00" format 9999 column "02:00" format 9999 column "03:00" format 9999 column "04:00" format 9999 column "05:00" format 9999 column "06:00" format 9999 column "07:00" format 9999 column "08:00" format 9999 column "09:00" format 9999 column "10:00" format 9999 column "11:00" format 9999 column "12:00" format 9999 column "13:00" format 9999 column "14:00" format 9999 column "15:00" format 9999 column "16:00" format 9999 column "17:00" format 9999 column "18:00" format 9999 column "19:00" format 9999 column "20:00" format 9999 column "21:00" format 9999 column "22:00" format 9999 column "23:00" format 9999
col comments format a20 col db_user_name format a18 col OS_USER_NAME format a18 col COMPUTER_NAME format a30
Sample Database Auditing using Scripts
ReplyDeletehttp://www.petefinnigan.com/papers/audit.sql
Simple Datbase auditing
ReplyDeletehttp://www.symantec.com/connect/articles/introduction-simple-oracle-auditing
Oracle Database Tips and Tricks by Oracle
ReplyDeletehttp://www.akadia.com/html/publications.html#Oracle%20Tips%20of%20the%20Week
Oralce Magazine archives
ReplyDeletehttp://www.oracle.com/technetwork/issue-archive/index.html
Reduce database recovery time, using the Oracle flash recovery area.
ReplyDeletehttp://www.oracle.com/technetwork/issue-archive/2007/07-jan/o17recovery-087778.html
Pavans DBA Script Collections
ReplyDeletehttp://pavandba.wordpress.com/category/scripts/
ORA-00600 [723],[10424],[10424] Memory Leak
ReplyDeletehttp://www.bestremotedba.com/ora-00600-7231042410424-memory-leak/
before Logoff Trigger causes ora-600 [Bytes][Memory Leak] pga memory leak problem
ReplyDeleteORA-00600: internal error code, arguments: [723], [34580], [34580], [memory leak], [], [], [], []
ORA-03113: end-of-file on communication channel
Refer ORA-600 [723] "PGA memory leak" [ID 39308.1]
work around
Set the following in init.ora for example to disable space
leaks less than 4000 bytes:
event = "10262 trace name context forever, level 4000"
and stop and restart the database.
Procedure to move Aud$ table to non system tablespaces
ReplyDeleteNote:1019377.6: Script to move SYS.AUD$ table out of SYSTEM tablespace
Note:166301.1 : How to Reorganize SYS.AUD$ Table
Note:73408.1 : How to Truncate, Delete, or Purge Rows from the Audit
Trail Table SYS.AUD$
Note:72460.1 : Moving AUD$ to another tablespace and adding triggers
to AUD$
Discussion ablut logoff trigger causes high cpu utulization
ReplyDeletehttp://dbaspot.com/forums/oracle-server/12792-oracle-exe-high-cpu-utilization-database-logoff-yes-logoff-2.html
Upgrading Forms 6i with Applications 11i
ReplyDelete125767.1 (Upgrading Developer 6i with Oracle Applications 11i
RESOURCE INTENSIVE QUERIES
ReplyDeleteselect a.executions,
a.disk_reads,
a.disk_reads/a.executions dre,
a.buffer_gets,
a.buffer_gets/a.executions bge,
b.username,
a.first_load_time,
A.HASH_VALUE
from v$sql a, all_users b
where
a.executions > 0
and a.disk_reads > 0
and a.parsing_user_id = b.user_id
AND A.SQL_TEXT LIKE '%SELECT%'
order by 7,2
Locks and Waits
ReplyDelete---------------
wait.sql
SELECT substr(s1.username,1,12) "WAITING User",
substr(s1.osuser,1,8) "OS User",
substr(to_char(w.session_id),1,5) "Sid",
P1.spid "PID",
substr(s2.username,1,12) "HOLDING User",
substr(s2.osuser,1,8) "OS User",
substr(to_char(h.session_id),1,5) "Sid",
P2.spid "PID"
FROM sys.v_$process P1, sys.v_$process P2,
sys.v_$session S1, sys.v_$session S2,
sys.dba_locks w, sys.dba_locks h
WHERE
(((h.mode_held != 'None') and (h.mode_held != 'Null')
and ((h.mode_requested = 'None') or (h.mode_requested = 'Null')))
and (((w.mode_held = 'None') or (w.mode_held = 'Null'))
and ((w.mode_requested != 'None') and (w.mode_requested != 'Null'))))
and w.lock_type = h.lock_type
and w.lock_id1 = h.lock_id1
and w.lock_id2 = h.lock_id2
and w.session_id != h.session_id
and w.session_id = S1.sid
and h.session_id = S2.sid
AND S1.paddr = P1.addr
AND S2.paddr = P2.addr;
whatsql.sql
SELECT /*+ ORDERED */
s.sid, s.username, s.osuser,
nvl(s.machine, '?') machine,
nvl(s.program, '?') program,
s.process F_Ground, p.spid B_Ground,
X.sql_text
FROM sys.v_$session S,
sys.v_$process P,
sys.v_$sqlarea X
WHERE s.osuser like lower(nvl('&OS_User','%'))
AND s.username like upper(nvl('&Oracle_User','%'))
AND s.sid like nvl('&SID','%')
AND s.paddr = p.addr
AND s.type != 'BACKGROUND'
AND s.sql_address = x.address
AND s.sql_hash_value = x.hash_value
ORDER
BY S.sid;
Using the workload capture and replay in 11G
ReplyDelete[ID 445116.1]
SQL PERFORMANCE ANALYZER 10.2.0.x to 10.2.0.y EXAMPLE SCRIPTS
ReplyDelete[ID 742644.1]
Current User Activities In Database
ReplyDeleteset pagesize 66
col c1 for a9
col c1 heading "OS User"
col c2 for a9
col c2 heading "Oracle User"
col b1 for a9
col b1 heading "Unix PID"
col b2 for 9999 justify left
col b2 heading "SID"
col b3 for 99999 justify left
col b3 heading "SERIAL#"
col sql_text for a35
break on b1 nodup on c1 nodup on c2 nodup on b2 nodup on b3 skip 3
select c.spid b1, b.osuser c1, b.username c2, b.sid b2, b.serial# b3,
a.sql_text
from v$sqltext a, v$session b, v$process c
where a.address = b.sql_address
-- and b.status = 'ACTIVE' /* YOU CAN CHOOSE THIS OPTION ONLY TO SEE
-- ACTVE TRANSACTION ON THAT MOMENT */
and b.paddr = c.addr
and a.hash_value = b.sql_hash_value
order by c.spid,a.hash_value,a.piece
/
Script To Monitor Memory Usage By Database Sessions [ID 239846.1]
ReplyDeleteOracle Performance Diagnostic Guide (OPDG) [ID 390374.1]
Toolkit for dynamic marking of Library Cache objects as Kept (PIND) [ID 301171.1]
ReplyDeleteAll About Security: User, Privilege, Role, SYSDBA, O/S Authentication, Audit, Encryption, OLS, Database Vault, Audit Vault [ID 207959.1]
ReplyDeleteMS-Windows: Quick steps to solve ORA-04030 errors on 10G [ID 762031.1]
ReplyDeleteStop Users Granting Access To Their Own Objects To Any Other Users [ID 759699.1]
ReplyDeleteVERSION 10.1.2 ++
CREATE or replace TRIGGER grant_t1
BEFORE GRANT ON database
WHEN (ora_dict_obj_name like '%' and ora_dict_obj_owner='')
BEGIN
RAISE_APPLICATION_ERROR(-20001,'Protected : You are not allowed to grant access to the objects you
own to any other users');
END;
/
Tuning I/O-related waits [ID 223117.1]
ReplyDeleteWhich Server Supports What Kind of Replication? [ID 67145.1]
ReplyDeleteOracle Multiple Buffer Pools Feature [ID 135223.1]
ReplyDeleteTop Oracle 9i init.ora Parameters Affecting Performance [ID 223299.1]
ReplyDeleteMigrating to the Cost-Based Optimizer [ID 222627.1]
TROUBLESHOOTING: Server Upgrade Results in Slow Query Performance [ID 160089.1]
Wait Events List
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/waitevents003.htm
Diagnosing Query Tuning Problems [ID 233112.1]
ReplyDeleteHow to Identify Resource Intensive SQL for Tuning [ID 232443.1]
print custom values in sql prompt
ReplyDeleteset FEED off
set timing off
set HEA OFF
WITH greeting_info AS
( SELECT TO_NUMBER(TO_CHAR(SYSDATE,'HH24')) AS time
, 12 AS noon
, 17 AS evening
FROM dual )
SELECT CASE
WHEN time < noon THEN 'Good Morning Abul'
WHEN time BETWEEN noon AND evening THEN 'Good After noon Abul '
--'Good Afternoon Abul !'
ELSE 'Good Evening Abul'
END
FROM greeting_info;
set HEA ON
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS';
set FEED on
col schemaname new_value name
--select lower(user) schemaname from v$session where sid=(select sid from v$mystat where rownum =1);
set sqlprompt "&&_USER@&&_CONNECT_IDENTIFIER>"
set linesize 9999
set timing on
set time on
V$LOCKED_OBJECT -
ReplyDeleteThis view lists all locks acquired by every transaction on the system.
In order to see locked object query,
SQL> set linesize 130
SQL> set pages 100
SQL> col username format a20
SQL> col sess_id format a10
SQL> col object format a25
SQL> col mode_held format a10
SQL> select oracle_username || ' (' || s.osuser || ')' username
, s.sid || ',' || s.serial# sess_id
, owner || '.' || object_name object
, object_type
, decode( l.block
, 0, 'Not Blocking'
, 1, 'Blocking'
, 2, 'Global') status
, decode(v.locked_mode
, 0, 'None'
, 1, 'Null'
, 2, 'Row-S (SS)'
, 3, 'Row-X (SX)'
, 4, 'Share'
, 5, 'S/Row-X (SSX)'
, 6, 'Exclusive', TO_CHAR(lmode)) mode_held
from v$locked_object v
, dba_objects d
, v$lock l
, v$session s
where v.object_id = d.object_id
and v.object_id = l.id1
and v.session_id = s.sid
order by oracle_username
, session_id
/
To know the details of free and used memory use,
ReplyDeleteSQL> select round(sum(bytes)/1024/1024,2)||' MB' total_sga, round(round(sum(bytes)/1024/1024,2) - round(sum(decode(name,'free memory',bytes,0))/1024/1024,2))||' MB' used, round(sum(decode(name,'free memory',bytes,0))/1024/1024,2)||' MB' free from v$sgastat;
Tracking Oracle initialization parameter changes
ReplyDeleteOracle Tips by Kamran Agayev Agamehdi
May 29, 2009
Providing a history of changes to your Oracle database parameters is not only a DBA best practice, it's absolutely required in a mission-critical production environment. Back when Oracle parameters were kept in a flat file on the server (the init.ora file) tracking changes was difficult.
However, once Oracle introduced the "spfile" feature it became easier to track changes to initialization parameters. Below are two common techniques for tracking Oracle initialization parameters, auditing, and using the extra-cost AWR method.
Note: If you have purchased the extra cost performance pack and diagnostic pack (and have access to the AWR dba_hist_parameter table), it's easy to run a script to track all changes to your initialization parameters.
Oracle guru Kerry Osborne offers this nice method for tracking init.ora parm changes:
-- parm_mods.sql
--
-- Shows all parameters (including hidden) that have been modified.
-- Uses the lag function so that a single record is returned for each change.
-- It uses AWR data - so only snapshots still in the database will be included.
--
-- The script prompts for a parameter name (which can be wild carded).
-- Leaving the parameter name blank matches any parameter (i.e. it will show all changes).
-- Calculated hidden parameters (those that start with two underscores like "__shared_pool_size")
-- will not be displayed unless requested with a Y.
--
-- Kerry Osborne
--
-- Note: I got this idea from Jeff White.
--
set linesize 155
col time for a15
col parameter_name format a50
col old_value format a30
col new_value format a30
break on instance skip 3
select instance_number instance, snap_id, time, parameter_name, old_value, new_value from (
select a.snap_id,to_char(end_interval_time,'DD-MON-YY HH24:MI') TIME, a.instance_number, parameter_name, value new_value,
lag(parameter_name,1) over (partition by parameter_name, a.instance_number order by a.snap_id) old_pname,
lag(value,1) over (partition by parameter_name, a.instance_number order by a.snap_id) old_value ,
decode(substr(parameter_name,1,2),'__',2,1) calc_flag
from dba_hist_parameter a, dba_Hist_snapshot b , v$instance v
where a.snap_id=b.snap_id
and a.instance_number=b.instance_number
and parameter_name like nvl('¶meter_name',parameter_name)
and a.instance_number like nvl('&instance_number',v.instance_number)
)
where
new_value != old_value
and calc_flag not in (decode('&show_calculated','Y',3,2))
order by 1,2
/
http://www.dba-oracle.com/t_tracking_auditing_changes_initialization_parameters.htm
Forms 6i Application Server webforms Confiurations
ReplyDeletehttp://192.168.1.56/dev60cgi/ifcgi60.exe?config=welcome
$ORACLE_HOME\server\FORMSWEB.CFG
In formsweb.cfg make the url entry accordingly
[welcome]
form=e:\FORMS_DEMO\TEST\LOGON.fmx
userid=scott/tiger@devdb
otherparams=
pageTitle=dev
HTMLbodyAttrs=
HTMLbeforeForm=
HTMLafterForm=
width=800
height=600
separateFrame=false
splashScreen=yes
background=
lookAndFeel=generic
colorScheme=teal
serverApp=default
serverPort=9001
serverHost=192.168.1.56
serverURL=
archive_jini=f60all_jinit.jar
archive_ie=f60all.cab
connectMode=Socket
archive=f60web.jar
archive_jini=f60all_jinit.jar
archive_ie=f60all.cab
Installing Oracle9i R2 on Linux - (Fedora Core 2)
ReplyDeletehttp://www.idevelopment.info/data/Oracle/DBA_tips/Linux/LINUX_8.shtml
Oracle DBA Tips
ReplyDeletehttp://www.idevelopment.info/cgi/ORACLE_dba_tips.cgi
DBA Tips
ReplyDeletehttp://www.rampant-books.com/oracle_tips.htm
Mandatory to sell helmets with two-wheelers: Supreme Court
ReplyDeletehttp://indiatoday.intoday.in/site/Story/104558/India/mandatory-to-sell-helmets-with-twowheelers-supreme-court.html
Database Sripts
ReplyDeletehttp://www.oracle-books.com/oracle/tips.html
http://www.remote-dba.net/oracle_tips.htm
http://www.oraclefans.com/?page_id=2
Yoga Video
ReplyDeletehttp://www.ehow.com/video_7534890_yoga-exercises-reduce-stomach.html
Dead Lock scenarios
ReplyDeletehttp://www.experts-exchange.com/Database/Oracle/A_2356-Blocking-Locks-and-Rows.html?sfQueryTermInfo=1+30+databas+oracl
My Glogin
ReplyDeleteset FEED off
set timing off
set HEA OFF
WITH greeting_info AS
( SELECT TO_NUMBER(TO_CHAR(SYSDATE,'HH24')) AS time
, 12 AS noon
, 15 AS evening
FROM dual )
SELECT CASE
WHEN time < noon THEN 'Good Morning Abul'
WHEN time BETWEEN noon AND evening THEN 'Good After noon Abul '
--'Good Afternoon Abul !'
ELSE 'Good Evening Abul'
END
FROM greeting_info;
set HEA ON
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS';
set FEED on
set sqlprompt "&&_USER@&&_CONNECT_IDENTIFIER>"
set linesize 9999
set timing on
set time on
Missed/UN-Indexed Forign Key Columns.
ReplyDeletecolumn c1 format a30 heading "Constraint|name"
column c2 format a30 heading "Table|name"
column c3 format a20 heading "Column|name"
column c4 format 99 heading "Position"
SELECT acc1.constraint_name c1, acc1.table_name c2,
acc1.column_name c3, acc1.position c4
FROM all_cons_columns acc1, all_constraints ac1
WHERE ac1.constraint_name = acc1.constraint_name
AND ac1.constraint_type = 'R'
and acc1.owner ='&1'
AND ac1.owner = acc1.owner
AND (acc1.owner, acc1.table_name, acc1.column_name, acc1.position)
IN (SELECT acc.owner, acc.table_name, acc.column_name, acc.position
FROM all_cons_columns acc, all_constraints ac
WHERE ac.constraint_name = acc.constraint_name and
acc.owner=acc1.owner and ac.owner=acc.owner
AND ac.constraint_type = 'R'
MINUS
SELECT table_owner, table_name, column_name, column_position
FROM all_ind_columns
)
ORDER BY acc1.owner, acc1.constraint_name, acc1.column_name,
acc1.position;
Articles About Missing Indexes and Dead Lock due to Unindexed Columns
ReplyDeleteORA-4020 & ORA-60
http://www.oratechinfo.co.uk/deadlocks.html
http://yong321.freeshell.org/computer/deadlocks.txt
COMPARE Schemas
http://www.oracle-base.com/dba/Script.php?category=miscellaneous&file=compare_schemas.sql
Lock Due to Missing Forign Key Index
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4530093713805#26568859366976
http://www.jonadams.net/fk_constraints_and_indexing.htm
http://vsbabu.org/oracle/sect16.html
http://www.devx.com/getHelpOn/10MinuteSolution/16595/1954
comparsions with and without index
http://www.akadia.com/services/ora_exchange_partition.html
PROCEDURE SEND_MAIL IS
ReplyDelete(p_recipient IN VARCHAR2,
p_sender IN VARCHAR2,
p_sender_text IN VARCHAR2, -- Appear in e-mail as 'From:'
p_subject IN VARCHAR2,
p_message IN VARCHAR2)
AS
c utl_smtp.connection;
v_msg_string VARCHAR2(4000);
--
BEGIN
c := utl_smtp.open_connection('*** name of SMTP server ***');
utl_smtp.helo(c, '*** name of SMTP server ***');
utl_smtp.mail(c, p_sender);
utl_smtp.rcpt(c, p_recipient);
utl_smtp.open_data(c);
utl_smtp.write_data(c, 'From' || ': ' || p_sender_text || utl_tcp.CRLF);
utl_smtp.write_data(c, 'To' || ': ' || p_recipient || utl_tcp.CRLF);
utl_smtp.write_data(c, 'Subject' || ': ' || p_subject|| utl_tcp.CRLF);
utl_smtp.write_data(c, utl_tcp.CRLF || p_message);
utl_smtp.close_data(c);
utl_smtp.quit(c);
--
EXCEPTION
--
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
utl_smtp.quit(c);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
NULL; -- When the SMTP server is down or unavailable, we don't have
-- a connection to the server. The quit call will raise an
-- exception that we can ignore.
END;
RAISE_APPLICATION_ERROR(-20000,
'Failed to send mail due to the following error: ' || SQLERRM);
END;
This is called by the Check Alert log procedure:
IS
mail_msg VARCHAR2(150);
line_text VARCHAR2(250);
var_count NUMBER := 0;
alert_file UTL_FILE.file_type;
alert_file_newname VARCHAR2(100);
BEGIN
alert_file := UTL_FILE.fopen('ALERT_LOG_DIR',**alert log file name','R');
IF
UTL_FILE.is_open(alert_file) THEN
LOOP
EXIT WHEN var_count > 5;
--
UTL_FILE.get_line(alert_file, line_text);
--
--
-- This check is ONLY for 'ORA-' errors ...
--
IF
instr(line_text,'ORA-') > 0 THEN
var_count := var_count + 1;
mail_msg := 'The following error has been detected in the Alert Log:' ||CHR(10)||line_text;
SEND_MAIL('****recipient****','Null','xxxxx','xxxxxxx',
TO_NUMBER(var_count)||' - '||TO_CHAR(SYSDATE,'DD-Mon-YY HH24:MI')||' - '||mail_msg );
END IF;
--
END LOOP;
UTL_FILE.fclose(alert_file);
END IF;
--
--
EXCEPTION
WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR (-20051, 'Invalid Mode Parameter');
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR (-20052, 'Invalid File Location');
WHEN utl_file.invalid_filehandle THEN
RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle');
WHEN utl_file.invalid_operation THEN
RAISE_APPLICATION_ERROR (-20054, 'Invalid Operation');
WHEN utl_file.read_error THEN
RAISE_APPLICATION_ERROR (-20055, 'Read Error');
WHEN utl_file.internal_error THEN
RAISE_APPLICATION_ERROR (-20057, 'Internal Error');
WHEN utl_file.charsetmismatch THEN
RAISE_APPLICATION_ERROR (-20058, 'Opened With FOPEN_NCHAR
But Later I/O Inconsistent');
WHEN utl_file.file_open THEN
RAISE_APPLICATION_ERROR (-20059, 'File Already Opened');
WHEN utl_file.invalid_maxlinesize THEN
RAISE_APPLICATION_ERROR(-20060,'Line Size Exceeds 32K');
WHEN utl_file.invalid_filename THEN
RAISE_APPLICATION_ERROR (-20061, 'Invalid File Name');
WHEN utl_file.access_denied THEN
RAISE_APPLICATION_ERROR (-20062, 'File Access Denied By');
WHEN utl_file.invalid_offset THEN
RAISE_APPLICATION_ERROR (-20063,'FSEEK Param Less Than 0');
--
-- No data found can be raised when the GET_LINE action reaches EOF...
--
WHEN no_data_found THEN
NULL;
WHEN others THEN
RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error'|| SQLERRM);
END;
Make sure you have ALERT_LOG_DIR already created as an External Directory, so that UTL_FILE can find the alert log
As you can see I'm no PLSQL guru myself. It only checks for the first 4 ORA_errors as you may get your mail box filled in your absence!
Restrict Oracle Database connections from unauthorised hosts in Listener level
ReplyDeleteuse the following parameter in sqlnet.ora and restart the listener.
tcp.validnode_checking = yes
tcp.invited_nodes = (localhost,terminal1,192.168.0.55,terminal2)
Restricted users will get the following error message.
TNS-12547: TNS:lost contact
ORA-12537: TNS:connection closed
Alert Log monitoring using VB Script
ReplyDeletehttp://books.google.co.in/books?id=tUNtyMCwDWQC&pg=PA127&lpg=PA127&dq=alert+log+monitoring+script+in+windows&source=bl&ots=qfK2WeYnIm&sig=Ysl1S9Ce_7aqbGvRov7WgPYhIPs&hl=en&ei=xd7tTMWMK4ymvQP2-eGuDQ&sa=X&oi=book_result&ct=result&resnum=9&ved=0CFAQ6AEwCA#v=onepage&q=alert%20log%20monitoring%20script%20in%20windows&f=false
Info from
ReplyDeletehttp://www.dba-oracle.com/t_sql_redo_log_switch_reports.htm
Oracle log switch SQL reports
rem
rem Name: log_stat.sql
rem
rem FUNCTION: Provide a current status for redo logs
rem
rem
COLUMN first_change# FORMAT 99999999 HEADING Change#
COLUMN group# FORMAT 9,999 HEADING Grp#
COLUMN thread# FORMAT 999 HEADING Th#
COLUMN sequence# FORMAT 999,999 HEADING Seq#
COLUMN members FORMAT 999 HEADING Mem
COLUMN archived FORMAT a4 HEADING Arc?
COLUMN first_time FORMAT a21 HEADING 'Switch|Time'
BREAK ON thread#
SET PAGES 60 LINES 131 FEEDBACK OFF
START title132 'Current Redo Log Status'
SPOOL rep_out\&db\log_stat
SELECT thread#,group#,sequence#,bytes,
members,archived,
status,first_change#,
TO_CHAR(first_time, 'DD-MM-YYYY HH24:MI:SS') first_time
FROM
sys.v_$log
ORDER BY
thread#,
group#;
SPOOL OFF
PAUSE Press Enter to continue
SET PAGES 22 LINES 80 FEEDBACK ON
CLEAR BREAKS
CLEAR COLUMNS
TTILE OFF
Monitoring Redo Log Switches
In addition to the alert logs, the frequency of log switches can also be monitored via the v$log_history and v$archived_log views. This script shows an example of a script to monitor archive log switches:
REM NAME :log_hist.sql
REM PURPOSE :Provide info on logs for last 24 hours since last
REM PURPOSE :log switch
REM USE : From SQLPLUS
REM Limitations : None
REM MRA 10/14/01 Updated for Oracle9i
REM
COLUMN thread# FORMAT 999 HEADING 'Thrd#'
COLUMN sequence# FORMAT 99999 HEADING 'Seq#'
COLUMN first_change# HEADING 'SCN Low#'
COLUMN next_change# HEADING 'SCN High#'
COLUMN archive_name FORMAT a50 HEADING 'Log File'
COLUMN first_time FORMAT a20 HEADING 'Switch Time'
COLUMN name FORMAT a30 HEADING 'Archive Log'
SET LINES 132 FEEDBACK OFF VERIFY OFF
START title132 "Log History Report"
SPOOL rep_out\&db\log_hist
REM
SELECT
X.recid,a.thread#,
a.sequence#,a.first_change#,
a.switch_change#,
TO_CHAR(a.first_time,'DD-MON-YYYY HH24:MI:SS') first_time,
x.name
FROM
v$loghist a, v$archived_log x
WHERE
a.first_time>
(SELECT b.first_time-1
FROM v$loghist b WHERE b.switch_change# =
(SELECT MAX(c.switch_change#) FROM v$loghist c)) AND
x.recid(+)=a.sequence#;
SPOOL OFF
SET LINES 80 VERIFY ON FEEDBACK ON
CLEAR COLUMNS
TTITLE OFF
PAUSE Press Enter to continue
Monitoring Redo Statistics
Info from
ReplyDeletehttp://www.dba-oracle.com/t_sql_redo_log_switch_reports.htm
There are no views in Oracle that allow the user to look directly at a log file’s statistical data.
These statistics are in the views V$STATNAME, V$SESSION, V$PROCESS, V$SESSTAT, V$LATCH, and V$LATCHNAME.
REM
REM NAME : rdo_stat.sql
REM PURPOSE : Show REDO latch statistics
REM USE : from SQLPlus
REM Limitations : Must have access to v$_ views
REM
SET PAGES 56 LINES 78 VERIFY OFF FEERemote DBACK OFF
START title80 "Redo Latch Statistics"
SPOOL rep_out/&&db/rdo_stat
rem
COLUMN name FORMAT a30 HEADING Name
COLUMN percent FORMAT 999.999 HEADING Percent
COLUMN total HEADING Total
rem
SELECT
l2.name,
immediate_gets+gets Total,
immediate_gets "Immediates",
misses+immediate_misses "Total Misses",
DECODE (100.*(GREATEST(misses+immediate_misses,1)/
GREATEST(immediate_gets+gets,1)),100,0) Percent
FROM
v$latch l1,
v$latchname l2
WHERE
l2.name like '%redo%'
and l1.latch#=l2.latch# ;
rem
PAUSE Press Enter to continue
rem
rem Name: Redo_stat.sql
rem
rem Function: Select redo statistics from v$sysstat
COLUMN name FORMAT a30 HEADING 'Redo|Statistic|Name'
COLUMN value FORMAT 999,999,999 HEADING 'Redo|Statistic|Value'
SET PAGES 80 LINES 60 FEERemote DBACK OFF VERIFY OFF
START title80 'Redo Log Statistics'
SPOOL rep_out/&&db/redo_stat
SELECT
name,
value
FROM
v$sysstat
WHERE
name LIKE '%redo%'
ORDER BY statistic#;
SPOOL OFF
SET LINES 24 FEERemote DBACK ON VERIFY ON
TTITLE OFF
CLEAR COLUMNS
CLEAR BREAKS
Log switch History
ReplyDeletespool c:\log_hist.txt
SET PAGESIZE 90
SET LINESIZE 150
set heading on
column "00:00" format 9999
column "01:00" format 9999
column "02:00" format 9999
column "03:00" format 9999
column "04:00" format 9999
column "05:00" format 9999
column "06:00" format 9999
column "07:00" format 9999
column "08:00" format 9999
column "09:00" format 9999
column "10:00" format 9999
column "11:00" format 9999
column "12:00" format 9999
column "13:00" format 9999
column "14:00" format 9999
column "15:00" format 9999
column "16:00" format 9999
column "17:00" format 9999
column "18:00" format 9999
column "19:00" format 9999
column "20:00" format 9999
column "21:00" format 9999
column "22:00" format 9999
column "23:00" format 9999
source :
http://ss64.com/orav/V$LOG_HISTORY_perhour.html
SELECT * FROM (
ReplyDeleteSELECT * FROM (
SELECT TO_CHAR(FIRST_TIME, 'DD/MM') AS "DAY"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0), '99')) "00:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0), '99')) "01:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0), '99')) "02:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0), '99')) "03:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0), '99')) "04:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0), '99')) "05:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0), '99')) "06:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0), '99')) "07:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0), '99')) "08:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0), '99')) "09:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0), '99')) "10:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0), '99')) "11:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0), '99')) "12:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0), '99')) "13:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0), '99')) "14:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0), '99')) "15:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0), '99')) "16:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0), '99')) "17:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0), '99')) "18:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0), '99')) "19:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0), '99')) "20:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0), '99')) "21:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0), '99')) "22:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0), '99')) "23:00"
FROM V$LOG_HISTORY
WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate)
GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM')
) ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD/MM') DESC
) WHERE ROWNUM <8;
spool off
Read Alert Log from External Table
ReplyDeletestep 1 : Create procedure
=========================
create or replace procedure ssas_externalalertlog(in_instance in varchar2) as
path_bdump varchar2(4000);
name_alert varchar2(100) := 'alert_' || in_instance || '.log';
begin
select
value into path_bdump
from
sys.v_$parameter
where
name = 'background_dump_dest';
--select
--'alert_' || value || '.log' into name_alert
--from
--sys.v_$parameter
--where
--name = 'db_name';
execute immediate 'create or replace directory ssas_BDUMP as ''' ||
path_bdump || '''';
execute immediate
'create table ssas_ALERTLOG ' ||
' (MSG_line varchar2(4000) ) ' ||
' organization external ' ||
' (type oracle_loader ' ||
' default directory ssas_BDUMP ' ||
' access parameters ( ' ||
' records delimited by newline ' ||
' nobadfile ' ||
' nologfile ' ||
' nodiscardfile ' ||
' skip 0 ' ||
' READSIZE 1048576 ' ||
' FIELDS LDRTRIM ' ||
' REJECT ROWS WITH ALL NULL FIELDS ' ||
' (MSG_LINE (1:1000) CHAR(1000)) ' ||
' ) ' ||
' location (''' || name_alert || ''') )' ||
' reject limit unlimited ' ||
' noparallel nomonitoring ';
end;
/
step 2 : execute the procedure
==============================
execute ssas_externalalertlog('ORACLE_SID')
step 3 : issue this sql statement
=================================
col lineno noprint
col ora_error noprint
col msg_line format a132
set pages 0 lines 300 trimspool on trim on
alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
alter session set sql_trace=false;
break on thedate
prompt
prompt ERROR IN ALERT LOG FILE - LAST 3 DAYS
prompt =====================================
select "LINENO","THEDATE","ORA_ERROR","MSG_LINE"
from (
select *
from (
select lineno,
msg_line,
thedate,
max( case when (ora_error like 'ORA-%' or ora_error like 'PLS-%')
then rtrim(substr(ora_error,1,instr(ora_error,' ')-1),':')
else null
end ) over (partition by thedate) ora_error
from (
select lineno,
msg_line,
max(thedate) over (order by lineno) thedate,
lead(msg_line) over (order by lineno) ora_error
from (
select rownum lineno,
substr( msg_line, 1, 132 ) msg_line,
case when msg_line like '___ ___ __ __:__:__ ____'
then to_date( msg_line, 'Dy Mon DD hh24:mi:ss yyyy' )
else null
end thedate
from ssas_alertlog
)
)
)
)
where ora_error is not null
and thedate >= (trunc(sysdate) - 3)
order by thedate
/
step 4 : drop the external table
================================
drop table ssas_alertlog
/
Source :
http://sysdbaonline.com/?p=695
Trigger SQL_trace automatically
ReplyDeleteBy admin, on July 12th, 2010
Sometimes it is necessary to automatically trigger SQL trace. Automatically here means that code must be added somewhere.
The simplest approach is to create a logon trigger at the database level. To avoid enabling SQL trace for all users, I usually suggest creating a role (named sqltrace in the following example) and temporarily granting it only to the user utilized for the test.
CREATE ROLE sqltrace;
CREATE OR REPLACE TRIGGER enable_sqltrace AFTER LOGON ON DATABASE
BEGIN
IF (dbms_session.is_role_enabled('SQLTRACE'))
THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET timed_statistics = TRUE';
EXECUTE IMMEDIATE 'ALTER SESSION SET max_dump_file_size = unlimited';
dbms_monitor.session_trace_enable;
END IF;
END;
/
Find trace file using session id in oracle
ReplyDeleteBy admin, on July 8th, 2010
Sometimes it becomes very tough to find the relevant oracle trace file using session id in oracle.You can alter your session before setting an event to identify trace file quite easily but following method doesn't require alteration of session.
SELECT s.sid,
s.server,
lower(
CASE
WHEN s.server IN ('DEDICATED','SHARED') THEN
i.instance_name || '_' ||
nvl(pp.server_name, nvl(ss.name, 'ora')) || '_' ||
p.spid || '.trc'
ELSE NULL
END
) AS trace_file_name
FROM v$instance i,
v$session s,
v$process p,
v$px_process pp,
v$shared_server ss
WHERE s.paddr = p.addr
AND s.sid = pp.sid (+)
AND s.paddr = ss.paddr(+)
AND s.type = 'USER'
ORDER BY s.sid;
source :
http://sysdbaonline.com/?p=866
Materialized views
ReplyDeletehttp://sysdbaonline.com/?p=661
Tune slow running query in 10g
ReplyDeleteset lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id','4dqs2k5tynk61')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/
(sources from :
http://sysdbaonline.com/?p=905 )
Create database silently in oracle
ReplyDelete[oracle@db1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db
[oracle@db1 ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@db1 ~]$ dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbName orcl.sysdbaonline.com -sysPassword oracle -systemPassword oracle -emConfiguration NONE -datafileDestination /u01/app/oracle/oradata -storageType FS
source :
http://sysdbaonline.com/?p=903
create user lile other users privs
ReplyDeletehttp://www.orafaq.com/forum/m/120560/42800/?srch=cr_user_like#msg_120560
connect SQL Developer with SQL Server
ReplyDeleteSetting Up SQL Developer for MS SQL SERVER 2000
1) Install/Extract SQL Developer 1.2.1 in C:\Program Files\Oracle
Making the {$sqldevhome} = C:\Program Files\Oracle\sqldeveloper
2) Obtain the JDBC SQL SERVER Pluggin (jtds-1.2.2-dist.zip) from:
http://sourceforge.net/project/showfiles.php?group_id=33291&package_id=25350
3) Unzip jtds-1.2.2-dist.zip
Creates: jtds-1.2.2.jar
\x86\SSO\ntlmauth.dll
4) Copy the jtds-1.2.2.jar into:
{$sqldevhome}\jlib Directory.
5) Copy the the ntlmauth.dll dll in the \x86\SSO subdir into:
{$sqldevhome}\jdk\jre\bin\ntlmauth.dll
6) In SQL Developer:
Got Menu "Tools"
- Preferences...
Expand the "[+] Database
Choose "Third Party JDBC Drivers"
Click "Add Entry"
Then Browse for your copy of "{$sqldevhome}\jlib\jtds-1.2.2.jar"
Note: Use the jar filename, not its parent directory for entry.
7) Restart SQL Developer and try your SQL Server 2000 Connection
Tuning Log Switches in Oracle
ReplyDeletehttp://www.ixora.com.au/tips/tuning/log_switch.htm
Oracle Common Script collections
ReplyDeletehttp://www.disperu.com/turn-oracle-common-script/
ORacle TIPS and Tricks
ReplyDeletehttp://gavinsoorma.com/category/scripts/page/4/
JDBC Drivers for SQL Developer to connect all the Databases.
ReplyDeletehttp://sqldeveloper.solyp.com/drivers/index.html
Row Chaining and Migration Procedure
ReplyDeletecreate or replace
PROCEDURE rpt_chained_row
IS
num_chained_rows NUMBER;
target_table VARCHAR2(30);
statement VARCHAR2(256);
my_cursor INTEGER;
ignore INTEGER;
-- Define Cursor to LOOP Thru each user table
CURSOR all_tables_cur IS
SELECT table_name
FROM user_tables;
BEGIN
-- Increase Buffer Size
dbms_output.enable(1000000);
-- Print Title
dbms_output.put_line(chr(10));
dbms_output.put_line(chr(9)||chr(9)||'Chained Row(s) on User Tables');
dbms_output.put_line('----------------------------------------');
-- Print Header
DBMS_OUTPUT.PUT_LINE(RPAD('Table Name',20)||
'Chained Row(s)');
dbms_output.put_line('----------------------------------------');
--DELETE
--FROM CHAINED_ROWS;
FOR t_name IN all_tables_cur
LOOP
target_table := RTRIM(t_name.table_name);
-- Define SELECT statement for dynamic query
statement := 'ANALYZE TABLE ' ||target_table ||
' LIST CHAINED ROWS';
my_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(my_cursor,statement,DBMS_SQL.V7);
ignore := DBMS_SQL.EXECUTE(my_cursor);
DBMS_SQL.CLOSE_CURSOR(my_cursor);
SELECT COUNT(*)
INTO num_chained_rows
FROM CHAINED_ROWS;
DBMS_OUTPUT.PUT_LINE(RPAD(target_table,20)||
num_chained_rows);
END LOOP;
END;
CREATE
ReplyDeleteTABLE "CHAINED_ROWS"
(
"NUM_CHAINED_ROWS" NUMBER,
"TARGET_TABLE" VARCHAR2(30 BYTE),
"STATEMENT" VARCHAR2(256 BYTE),
"MY_CURSOR" NUMBER(*,0),
"IGNORE" NUMBER(*,0)
);
Database Logon Audit Using Trigger:
ReplyDelete-----------------------------------
Part 1: Create Table
Part 2: Create After Success Logon Trigger
Part 3: Create After Failure Logon Trigger
Part 4: Create before Logoff Trigger
Part 5: Formatted Query.
Part 1: Create Table
ReplyDelete--------------------
CREATE
TABLE "SYS"."V$LIGHT_AUDIT_LOGON"
(
"AUDIT_DATE" DATE,
"DB_USER_NAME" VARCHAR2(30 BYTE),
"OS_USER_NAME" VARCHAR2(30 BYTE),
"COMPUTER_NAME" VARCHAR2(50 BYTE),
"IP_ADDRESS" VARCHAR2(20 BYTE),
"IS_DBA" VARCHAR2(10 BYTE),
"MODULE" VARCHAR2(30 BYTE),
"COMMENTS" VARCHAR2(500 BYTE),
"SID" VARCHAR2(20 BYTE),
"SERIAL#" VARCHAR2(20 BYTE),
"LOGOFF_TIME" DATE,
"ELAPSED_MINUTES" NUMBER,
"LOGOFF_DAY" DATE
)
TABLESPACE "PPMA_DATA" ;
Part 2: Create After Success Logon Trigger
ReplyDelete------------------------------------------
create or replace
trigger logon_f
AFTER SERVERERROR
on database
BEGIN
if (is_servererror(1017)) OR (is_servererror(1012)) then
begin
insert into v$light_audit_logon (
audit_date,
db_user_name,
os_user_name,
computer_name,
ip_address,
is_dba,
module,
comments,
SID,
SERIAL#
)
values (
sysdate,
sys_context('userenv','session_user'),
sys_context('userenv','os_user'),
sys_context('userenv','host'),
sys_context('userenv','ip_address'),
sys_context('userenv','isdba'),
NULL, --sys_context('userenv','module'),
'Failure Connection',
--sqlerrm,
NULL,
NULL
);
end; -- insert block
end if;
end logon_failures;
Part 3: After Failure Logon Trigger
ReplyDelete------------------------------------------
create or replace
trigger logon_S after logon on database
DECLARE
sid V$SESSION.SID%TYPE;
SERIAL# V$SESSION.SERIAL#%TYPE;
module V$SESSION.module%TYPE;
begin -- insert block
SELECT sid,SERIAL#,module INTO sid,SERIAL#,module FROM v$session where sid=(select sid from v$MYSTAT WHERE rownum =1);
insert into v$light_audit_logon (
audit_date,
db_user_name,
os_user_name,
computer_name,
ip_address,
IS_DBA,
module,
comments,
SID,
SERIAL#
)
values (
sysdate,
user,
sys_context('userenv','os_user'),
sys_context('userenv','host'),
sys_context('userenv','ip_address'),
sys_context('userenv','isdba'),
module, --NULL ,--sys_context('userenv','module'),
'Successful Logon',
SID,--,sys_context('userenv','SESSIONID'),
SERIAL#
);
exception when others then
null;
commit;
end; -- insert block
Part 4: Create before Logoff Trigger
ReplyDelete------------------------------------
create or replace
trigger
ses_logoff
BEFORE LOGOFF ON DATABASE
BEGIN
update
sys.v$light_audit_logon
set
logoff_time = sysdate where ( sid,audit_date) = (select sid,max(audit_date) from sys.v$light_audit_logon where
sid = (select sid from sys.v_$session where sid=(select sid from v$mystat where rownum=1)) GROUP BY sid);
COMMIT;
END;
Part 5: Formatted Query 1/2
ReplyDelete-----------------------
SET PAGESIZE 90
SET LINESIZE 200
set heading on
column "00:00" format 9999
column "01:00" format 9999
column "02:00" format 9999
column "03:00" format 9999
column "04:00" format 9999
column "05:00" format 9999
column "06:00" format 9999
column "07:00" format 9999
column "08:00" format 9999
column "09:00" format 9999
column "10:00" format 9999
column "11:00" format 9999
column "12:00" format 9999
column "13:00" format 9999
column "14:00" format 9999
column "15:00" format 9999
column "16:00" format 9999
column "17:00" format 9999
column "18:00" format 9999
column "19:00" format 9999
column "20:00" format 9999
column "21:00" format 9999
column "22:00" format 9999
column "23:00" format 9999
col comments format a20
col db_user_name format a18
col OS_USER_NAME format a18
col COMPUTER_NAME format a30
Part 5: Formatted Query 2/2
ReplyDelete----------------------------
SELECT * FROM (
SELECT * FROM (
SELECT
TO_CHAR(AUDIT_DATE, 'DD/MM') AS "DAY"
, db_user_name
, decode(os_user_name,null,'JOBS',os_user_name) OS_USER_NAME
, SUM(TO_NUMBER(DECODE(TO_CHAR(AUDIT_DATE, 'HH24'), '00', 1, 0), '99')) "00:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(AUDIT_DATE, 'HH24'), '01', 1, 0), '99')) "01:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(AUDIT_DATE, 'HH24'), '02', 1, 0), '99')) "02:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(AUDIT_DATE, 'HH24'), '03', 1, 0), '99')) "03:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(AUDIT_DATE, 'HH24'), '04', 1, 0), '99')) "04:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(AUDIT_DATE, 'HH24'), '05', 1, 0), '99')) "05:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(AUDIT_DATE, 'HH24'), '06', 1, 0), '99')) "06:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(AUDIT_DATE, 'HH24'), '07', 1, 0), '99')) "07:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(AUDIT_DATE, 'HH24'), '08', 1, 0), '99')) "08:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(AUDIT_DATE, 'HH24'), '09', 1, 0), '99')) "09:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(AUDIT_DATE, 'HH24'), '10', 1, 0), '99')) "10:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(AUDIT_DATE, 'HH24'), '11', 1, 0), '99')) "11:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(AUDIT_DATE, 'HH24'), '12', 1, 0), '99')) "12:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(AUDIT_DATE, 'HH24'), '13', 1, 0), '99')) "13:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(AUDIT_DATE, 'HH24'), '14', 1, 0), '99')) "14:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(AUDIT_DATE, 'HH24'), '15', 1, 0), '99')) "15:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(AUDIT_DATE, 'HH24'), '16', 1, 0), '99')) "16:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(AUDIT_DATE, 'HH24'), '17', 1, 0), '99')) "17:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(AUDIT_DATE, 'HH24'), '18', 1, 0), '99')) "18:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(AUDIT_DATE, 'HH24'), '19', 1, 0), '99')) "19:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(AUDIT_DATE, 'HH24'), '20', 1, 0), '99')) "20:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(AUDIT_DATE, 'HH24'), '21', 1, 0), '99')) "21:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(AUDIT_DATE, 'HH24'), '22', 1, 0), '99')) "22:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(AUDIT_DATE, 'HH24'), '23', 1, 0), '99')) "23:00"
, comments,COMPUTER_NAME,sum(1)
FROM V$LIGHT_AUDIT_LOGON
WHERE extract(year FROM AUDIT_DATE) = extract(year FROM sysdate)
GROUP BY TO_CHAR(AUDIT_DATE, 'DD/MM'),db_user_name,comments,OS_USER_NAME,COMPUTER_NAME
) ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD/MM') ASC
) WHERE DAY = '&Date';