Finding the frequency of log switch

select qtd,count(*) from (
select trunc(FIRST_TIME),trunc(count(*)/10)*10 qtd
from  v$log_history group by trunc(FIRST_TIME) order by 2
)group by qtd order by 1;

Usually is good pratice make 2 log switchs per hour

Writing on alert

Sometimes you want to log your own message on alert

just use package ksdwrt

e.g.: dbms_system.ksdwrt(2,’ORA-00600: URGENT – starting 4031 PROJECTS BY Helio Dias’);

I wrote the message started with 00600 to allow our monitoring tool “see” the error.

First parameter (2) write message on alert, (1) write on trace files

How to find database link user password

Just query the SYS.LINK$  name and password

Ucertify Challenge

Two months ago i´d received a very brave propose: “Test our product and say whatever you want.”

I´d thought , ohhh it must be good , or they must be crazy, cause if they make me lost my time testing a bad product i´ll write terrible things.

And i accepted try the Oracle z31 (administration one for OCA/OCP 9) cause to me this one of the best of Oracle, so a good or bad product will show it face on it.

And i started the trial of Ucertify , and ask to my brother try with me.

And the result is: Ucertify is without any doubt an amazing product and i recommend it to anyone that will try an Oracle certification.

Oracle Bug Status code on metalink

10 – Description Phase:
Development is requesting more information.

11 – Code Bug (Response/Resolution):
Bug is being worked by Development.

16 – Support bug screening:
Bug is being reviewed by our Bug Diagnostics group.

30 – Additional Information Requested:
Bug is being worked by Support and/or more information was requested by Development.

37 – To Filer for Review/Merge Required:
Bug has been fixed but the patch will be merged into the next patchset.

80 – Development to Q/A:
Bug is being regression tested for future release.

96 – Closed, Duplicate Bug:
Bug is closed as a duplicate bug.

How to finding Maximum SESSION_CACHED_CURSORS in use

select max(value)
from v$sesstat natural join v$statname
where name = ’session cursor cache count’ ;
Select amount,SESSION_CACHED_CURSORS*30+30 Cached_Cursors_Rounded from
(select trunc(value/30)  SESSION_CACHED_CURSORS,count(*) Amount
from V$sesstat natural join v$statname
where name = ’session cursor cache count’
group by trunc(value/30) order by 1);

    AMOUNT CACHED_CURSORS_ROUNDED
———- ———————-
       223                     30
        32                     60
        15                     90
         6                    120
         2                    150
         3                    180
         5                    210

7 rows selected.
As we can see 210 will allow every session to cache what they need.

How Adjust Open_cursors on Oracle Database

First you need to know how many cursors are you session using at maximum

select max(value) from V$sesstat natural join v$statname
where name = ‘opened cursors current’;
Then you can give some safe e.g. 30% and do an
alter system set open_cursors=&New_value_plus_20pct;
Select amount,Opened_cursos_rounded*10+10 from
(select trunc(value/10) Opened_cursos_rounded,count(*) Amount from V$sesstat natural join v$statname
where name = ‘opened cursors current’
group by trunc(value/10) order by 1);

    AMOUNT OPENED_CURSOS_ROUNDED*10
———- ————————
        35                        0
        59                       10
        10                       20
         3                       30
         2                       40
         1                       60
         1                       70
         1                       80
         1                      130
         1                      180
         1                      190
         1                      200
         1                      220
         2                      230

14 rows selected.
On my example you can see that most of sessions are using just 10 cursors, but my max are at 230.
So put a limit on 300 is sounds good.

Killing an Oracle process without OS access

Sometimes alter system kill session doesn´t work , and you don´t have access to SO.

So use Oradebug instead, but remember to change to correct session:

oradebug event immediate crash;

Killing an Oracle process from inside Oracle

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.