Best way to delete millions rows from hundred millions table

The best way to it with a lower impact to the users and a very good time is:

1 – If possible drop the indexes (it´s not mandatory, it will just save time)

2 – Run the delete using bulk collection like the example below

declare
cursor crow is
select rowid rid
from big_table where filter_column=’OPTION’ ;
type brecord is table of rowid index by binary_integer;
brec brecord;
begin
open crow;
FOR vqtd IN 1..500 loop
fetch c bulk collect into brec limit 20000;
forall vloop in 1 .. brec.count
delete from big_table where rowid = brec(vloop);
exit when crow%notfound;
commit;
dbms_lock.sleep(5);
end loop;
close crow;
end;
/

3 – Adjust the values vqtd  , limit  and sleep to best to your case.

In my case using these values (vqtd:=500 , limit:=20000 and sleep:=5) each whole execution took an hour.

Try begin with vqtd 10  , and then check your redo/archive generation and the v$system_event, to make adjust for your environment.

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.