Logon Trigger

Sometimes you need to something happen just after logon, e.g. an alter session , or an audit DML.

So you may use Logon trigger to do this:

 

GRANT ALTER SESSION TO SYSTEM;

GRANT CREATE SESSION TO SYSTEM;

create or replace trigger set_trace after logon on database
begin
if user not in (‘SYS’,'SYSTEM’) then
if user=’GERAL’ THEN
execute immediate ‘alter session set TRACEFILE_IDENTIFIER= ”CLIENT_TRACE10046”’;
execute immediate ‘alter session set timed_statistics=true’;
execute immediate ‘alter session set max_dump_file_size=unlimited’;
execute immediate ‘alter session set events ”10046 trace name context forever, level 12”’;
end if;
end if;
exception
when others then
null;
end;
/

 

GRANT ALTER SESSION TO SYSTEM;

GRANT CREATE SESSION TO SYSTEM;

create or replace trigger GC_set_session after logon on database
begin
if user not in (‘SYS’,'SYSTEM’) then
  execute immediate ‘alter session set session_cached_cursors=250′;
end if;
exception
when others then
null;
end;
/

Adjusting Oracle memory

To do a fine tuning on memory area, you should first look the shared pool, then go to PGA and at the end adjust the db_cache

SELECT SHARED_POOL_SIZE_FOR_ESTIMATE MB_ESTIMATE,SHARED_POOL_SIZE_FACTOR
FACTOR_PERCENT,
ESTD_LC_TIME_SAVED_FACTOR TIME_PERCENT FROM V$SHARED_POOL_ADVICE;

SELECT PGA_TARGET_FOR_ESTIMATE MB_ESTIMADO,PGA_TARGET_FACTOR
FATOR_PERCENT,
ESTD_PGA_CACHE_HIT_PERCENTAGE ACERTO_PERCENT FROM V$PGA_TARGET_ADVICE;

SELECT SIZE_FOR_ESTIMATE MB_ESTIMADO, SIZE_FACTOR
FATOR_PERCENT,
ESTD_PHYSICAL_READ_FACTOR TEMPO_PERCENT FROM V$DB_CACHE_ADVICE;

Below are good notes from metalink
Metalink Note: 223730.1 Automatic PGA Memory Management in 9i and 10g

Metalink Note: 62172.1 Understanding and Tuning Buffer Cache and DBWR

Metalink Note: 255409.1 Size Shared Pool using V$shared_pool_advice.

Using DBMS_SHARED_POOL.KEEP TO PINNING

CREATE TABLE GC_PINNING
(OBJ_OWNER VARCHAR2(50), OBJ_NAME VARCHAR2(50),OBJ_TYPE VARCHAR2(50),
OBJ_SIZE NUMBER,PINNING_DATE DATE DEFAULT SYSDATE,obj_LOADS NUMBER);

insert into GC_PINNING
(OBJ_OWNER , OBJ_NAME,OBJ_TYPE,
OBJ_SIZE,PINNING_DATE ,obj_LOADS)
(select
OWNER , NAME,TYPE,
sharable_mem,
sysdate
,LOADS
from v$db_object_cache where
type in (‘PACKAGE’,'PACKAGE BODY’,'TRIGGER’,'SEQUENCE’,'PROCEDURE’)
and nvl(sharable_mem,0)>0 and kept=’NO’);

CREATE OR REPLACE PROCEDURE GC_PINNER AS
/*
gc_Pinner by Helio Dias.com
Subject : pinning object that had been saved on GC_PINNING table
June 24, 2008,
*/
cursor c_pinning is
select OBJ_OWNER||’.'||OBJ_NAME OBJ_NAME,OBJ_TYPE from GC_PINNING;
V_NAME VARCHAR2(50);
V_TYPE VARCHAR(50);
begin
open c_pinning;
loop
fetch c_pinning into v_name,v_type;
exit when c_pinning%notfound;
IF V_TYPE=’FUNCTION’
THEN V_TYPE:=’P';
END IF;
IF V_TYPE=’SEQUENCE’
THEN V_TYPE:=’Q';
END IF;
IF V_TYPE=’TRIGGER’
THEN V_TYPE:=’R';
END IF;
V_TYPE:=SUBSTR(V_TYPE,1,1);
dbms_shared_pool.keep(V_NAME,V_TYPE);
end loop;
END;
/

Oracle interview questions

Every time a saw this type of ask on forums, so i decide to put some basics questions:

What is the difference between cold backup and hot backup?

What is the difference between and constraints and triggers?

What is normalization? what is the advantage of normalization (briefly)

What is structure of Database?

What is the difference between primary key, unique key, sorrougate key?

What is the different between Stand Alone Procedure and Package?

How create database statistics?

What is Mutating Trigger?

What is Full Table Scan?

What is Tablespace Quota? what is usefull views?

You are going to out of tablespace space , what are you gonna do?

Describe a dead lock

Is lock a bad thing?

What is ORA-1555?

What is ORA-0600?

What is ORA-04031?

Difference of Physical and Logical Backup?

CBO Means?

What is difference between SQLNET.ORA AND TNSNAMES.ORA AND LISTENER.ORA?

Most of this questions had been extracted from :
http://www.geekinterview.com/Interview-Questions/Oracle

Oracle Architecture and Tuning on AIX

http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP100883

45 pages of concise material

Finding ORA-04031 When not appear on alert

Sometimes just the user session receive the message, but it not logged on alert.
On this X$ Table you can see this.

select sum(kghlunfu) Total_4031 ,kghlunfs Size_miss from x$kghlu group by kghlunfs;

List of all X$ tables

select kqftanam from x$kqfta;

Crack Oracle Password

If you have the hash password, you can try this:

http://www.red-database-security.com/whitepaper/oracle_password_cracker.html

Find free space on tablespace x Allocated space %

Today i answered this question on OTN,

select * from (
Select tbs2,free_space,allocated,trunc((free_space/allocated)*100) pct_free from
(select tablespace_name tbs1,sum(bytes)/1024/1024 free_space from dba_free_space
group by tablespace_name),
(select tablespace_name tbs2,sum(bytes)/1024/1024 allocated from dba_data_files
group by tablespace_name)
where tbs2=tbs1(+) ) where pct_free <&min_pct
and TBS2 not in
(select tablespace_name from dba_TABLESPACES where contents=’UNDO’)
order by 3;

11g new features

Good doc.

http://www.ahmedbaraka.com/computer/docs/oracle_11g_nf_1.0.pdf