Setting two defaults tablespaces to an schema

Today i saw this question on a forum and i think that it´s quite simple.

All you have to do is creating an event trigger that before a create DDL, it´ll add the tablespace information.

later when i have time, i´ll post the solution here

ORA-1652: unable to extend temp segment by 128 in tablespace

Sometimes you have a huge space on temporary tablespace and even so some bad query use it all.
First step is investigate the normal usage of sort area, and if you see an abnormal behavior, then it´s time to find out , what´s this query.

SQL> SELECT MAX(SORTS_TOTAL/EXECUTIONS_TOTAL) FROM DBA_HIST_SQLSTAT WHERE EXECUTIONS_TOTAL>0; 

MAX(SORTS_TOTAL/EXECUTIONS_TOTAL)
———————————
                           102173

The above query will show the top temporary resource consumer. Then all you have to do is

SQL> SELECT SQL_ID FROM DBA_HIST_SQLSTAT WHERE EXECUTIONS_TOTAL>0 AND SORTS_TOTAL/EXECUTIONS_TOTAL>=102173;

SQL_ID
————-
4w84ym20xy7da

And finally

SELECT SQL_TEXT FROM DBA_HIST_SQLTEXT WHERE SQL_ID=’4w84ym20xy7da’;

Virtual Index

The importance of virtual index on database is allow us try what will happen if we decide create the index.
The syntax is quite simple, just add “nosegment” on the end of the create index command.
You must also remember to temporary alter the hidden parameter “_use_nosegment_indexes” to true .
Then check if the execution plan change as you planned and back the _use_nosegment_indexes to false

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.