Data Pump Consistent Export

http://www.dizwell.com/prod/node/112

Oracle Unbreakable Linux

Why Oracle call him Linux as Unbreakable?
Yesterday i saw the podcast Oracle OpenWorld Keynote Video
with Oracle CEO Larry Ellison’s  announcement
in his Oracle OpenWorld 2006 keynote presentation.
And i finally understand why Oracle claim Unbreakable.
It´s a amazing support that Oracle offers, if you have a big problem on
your Linux Red Hat, than Oracle will fix it on your Currently Version,
So you don´t have to wait until an new version that upgrade your SO to fix your bug.

WITH at start of statement

The With have two distinct propose, that could be combine.
First: make the query more clean (more readable) eg:

select col1 alias1,col2 alias2,col3 alias3,col4 alias4,Col5 alias5,col6 alias6,col7 alias 7,col7 alias8, col1+col2 alias1_2
from tab1,tab2,tab3,tab4,tab5,tab6
where
tab1.col10=tab2.col10 and
tab2.col11=tab3.col11 and
tab3.col12=tab4.col12 and
tab4.col13=tab5.col13 and
tab5.col14=tab6.col14
and tab1.col30=10
union all
select col1 alias1,col2 alias2,col3 alias3,col4 alias4,Col5 alias5,col6 alias6,col7 alias 7,col7 alias8, col1+col2 alias1_2
from tab1,tab2,tab3,tab4,tab5,tab6
where
tab1.col10=tab2.col10 and
tab2.col11=tab3.col11 and
tab3.col12=tab4.col12 and
tab4.col13=tab5.col13 and
tab5.col14=tab6.col14
and tab1.col30=15
union all
select col1 alias1,col2 alias2,col3 alias3,col4 alias4,Col5 alias5,col6 alias6,col7 alias 7,col7 alias8, col1+col2 alias1_2
from tab1,tab2,tab3,tab4,tab5,tab6
where
tab1.col10=tab2.col10 and
tab2.col11=tab3.col11 and
tab3.col12=tab4.col12 and
tab4.col13=tab5.col13 and
tab5.col14=tab6.col14
and tab1.col30=20
union all
select col1 alias1,col2 alias2,col3 alias3,col4 alias4,Col5 alias5,col6 alias6,col7 alias 7,col7 alias8, col1+col2 alias1_2
from tab1,tab2,tab3,tab4,tab5,tab6
where
tab1.col10=tab2.col10 and
tab2.col11=tab3.col11 and
tab3.col12=tab4.col12 and
tab4.col13=tab5.col13 and
tab5.col14=tab6.col14
and tab1.col30=25
union all
select col1 alias1,col2 alias2,col3 alias3,col4 alias4,Col5 alias5,col6 alias6,col7 alias 7,col7 alias8, col1+col2 alias1_2
from tab1,tab2,tab3,tab4,tab5,tab6
where
tab1.col10=tab2.col10 and
tab2.col11=tab3.col11 and
tab3.col12=tab4.col12 and
tab4.col13=tab5.col13 and
tab5.col14=tab6.col14
and tab1.col30=30;

Now compare With
With base_table as
(select col1 alias1,col2 alias2,col3 alias3,col4 alias4,Col5 alias5,col6 alias6,col7 alias 7,col7 alias8, col1+col2 alias1_2
from tab1,tab2,tab3,tab4,tab5,tab6
where
tab1.col10=tab2.col10 and
tab2.col11=tab3.col11 and
tab3.col12=tab4.col12 and
tab4.col13=tab5.col13 and
tab5.col14=tab6.col14)
select * from base_table where tab1.col30=10
union all
select * from base_table where tab1.col30=15
union all
select * from base_table where tab1.col30=20
union all
select * from base_table where tab1.col30=25
union all
select * from base_table where tab1.col30=30

I know that in both cases i could use OR but it´s just to ilustrate

So Oracle will have two diferents behaviours, 
If selectivity is bad , than Oracle will simply expand the base_table as if was a view.
But if selectivity is good than Oracle execute just once,  and give you only the result set.
Regards
Helio Dias
http://heliodias.com

In response to :

http://forums.oracle.com/forums/thread.jspa?messageID=2035401&#2035401

What’s blocking my lock?

http://orafaq.com/node/854

Find query that generate more redo logs

You may query

select s.sid, s.serial#,i.block_changes from v$session s, v$sess_io i where s.sid = i.sid order by 3

Regards

Hélio Dias
http://heliodias.com

http://forums.oracle.com/forums/thread.jspa?threadID=548141&tstart=30

11g hide hash password

It seems like 11g hidden hash password,

Now you can´t query password column from dba_users

Library cache pin

The library cache pin is simply a session marking a library object (e.g. cursor) for use – either shared (because it wants to use an existing cursor, say) or exclusive (because it needs to invalidate and recompile a cursor).

This should be a very rapid action but in your case it isn’t – your average wait is more than 2 seconds. It isn’t possible to say why this is happening, but if you are doing cross-instance invalidations (e.g. truncating tables regularly) AND you have a very busy system (lots of CPU in use) that can be enough to cause these symptoms.

On a long shot – the scattered reads (possible tablescans) may be causing some remastering (gc remaster or similar), and this can cause a freeze (gcs drm freeze or similar): this shouldn’t cause problems to the library cache – which operates under the ‘global enqueue’ processes rather than ‘global cache’ processes – but maybe there is some connection.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk

 Answering to http://forums.oracle.com/forums/thread.jspa?threadID=547850&tstart=60

Origin of the word Query

The word derives from the Latin quaere (the imperative form of
quaerere, meaning to ask or seek).

11g Performance improves

http://download.oracle.com/docs/cd/B28359_01/server.111/b28279/chapter1.htm#AREANO02327

11g Week by week

http://www.datasoftech.com/library.html