oracle queries

oracle

Audit invalid logon attempts

audit create session whenever not successful;
set linesize 120
column OS_USERNAME format a20
column USERHOST format a20
column TERMINAL format a20
column CLIENT_ID format a20
select * from dba_audit_session where returncode != 0;
noaudit create session whenever not successful;

more on auditing

logging table

CREATE TABLE log_messages (
id NUMBER NOT NULL
,message varchar2(255) not null
,logged_time date not null
,username varchar2(38) not null
,sid number not null
) TABLESPACE app_support;

CREATE SEQUENCE log_message_id;

CREATE OR REPLACE PROCEDURE log_msg (p_message IN VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_sid NUMBER;
BEGIN
SELECT sid INTO l_sid FROM v$mystat WHERE ROWNUM=1;
INSERT INTO log_messages (id, message, logged_time, username, sid) VALUES (log_message_id.nextval, p_message, SYSDATE, USER, l_sid);
COMMIT;
END;
/

AQ coalesce

Metalink note 271855.1 has a script aqcoalesce.sql
To quote from the note
The procedure performs the following operations relating to AQ objects

alter table AQ$_ < QUEUE_TABLE_NAME > _X coalesce;
where X=I (dequeue), T (time_manager), and H (history) IOTS for multi-consumer queue tables and

alter index AQ$_ < QUEUE_TABLE_NAME > _Y rebuild;
where Y=I (dequeue), and T (time-manager) indexes for single-consumer queue tables

oracle - tracing

session
on: dbms_system.set_ev(sid,serial#,10046,level,'');
off: dbms_system.set_ev(sid,serial#,10046,0,'');
system wide
on: alter system set events '10046 trace name context forever, level ';
off: alter system set events '10046 trace name context off';

levels
4=binds
8=waits
12=binds and waits

oracle - High Water Mark

Metalink article 262353.1

essentially

select count (distinct dbms_rowid.rowid_block_number(r.rowid)) "used blocks", blocks "below hwm", empty_blocks "above hwm"
from r, dba_tables
where table_name = '&table_name'
group by blocks, empty_blocks;