and
September 7, 2009 - 3am Central time zone
Reviewer: Sokrates
... and ...
" Preprocessing Data for ORACLE_LOADER Access Driver in External Tables"
looks very nice, doesn't it ?
( http://download.oracle.com/docs/cd/E11882_01/server.112/e10881/chapter1.htm#FEATURENO08785 )
Now you can "mount" an Excel-xls-File as external-table, just write an appropriate pre-processor
(xls2csv)
... and ...
"Recursive with Clause"
http://download.oracle.com/docs/cd/E11882_01/server.112/e10881/chapter1.htm#FEATURENO08835
allows you to solve SQL-Puzzles very simple like Mikito Harakiri did
http://vadimtropashko.wordpress.com/2008/11/18/finally
(no need to write Fourier Transformations in SQL)
... and ...
IGNORE_ROW_ON_DUPKEY_INDEX Hint for INSERT Statement
http://download.oracle.com/docs/cd/E11882_01/server.112/e10881/chapter1.htm#FEATURENO08842
: kind of hidden "when others then null;"
?
correction
September 7, 2009 - 4am Central time zone
Reviewer: Sokrates
IGNORE_ROW_ON_DUPKEY_INDEX of course is not a hidden
"when others then null;"
but a hidden
"when dup_val_on_index then null;"
new possibilities of collect function
September 9, 2009 - 3pm Central time zone
Reviewer: RC from The Netherlands
I like the 2 new possibilities of the collect function.
1. the possibility to add distinct
2. the possibility to and order by.
Read here: http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/functions024.htm .
This is old news because they where already present in 11g1 but it escaped my attention because
those two new possibilities aren't mentioned in the new features guide of 11gr1 or 11gr2.
You can use them to make your own specialized 'listagg' functions.
upgrade from 10g to 11g
October 6, 2009 - 4pm Central time zone
Reviewer: tony wang from Los Angeles,CA USA
Hi Tom,
11g sounds great, but how to upgrade from 10g to 11g?
Thanks.
Tony
Followup October 8, 2009 - 7am Central time zone:
start by reading the answer to this same exact question you posted on another page.
why do people do that? post the same there here, there, everywhere?
LISTAGG
October 8, 2009 - 8am Central time zone
Reviewer: Martijn from Netherlands, #42 street
Hi Tom,
Regarding this great thread:
http://asktom.oracle.com/pls/asktom/f?p=100:11:2119231749211243::::P11_QUESTION_ID:229614022562
And in 11g nowadays we can use LISTAGG
http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/functions087.htm#SQLRF30030
Out-of-curiosity-question:
Were you in any way involved creating and/or designing LISTAGG?
Is it perhaps based on your TRANSPOSE function under it's covers?
October 8, 2009 - 9am Central time zone
Reviewer: A reader from 08831
While executing the following statement , I am getting the error ORA-01489.
This is because of the resultant value exceeds 4000 characters .
Per manual
<quote>
The return data type is RAW if the measure column is RAW; otherwise the return value is VARCHAR2.
</quote>
Is there are any way to circumvent this limitation of varchar2?
Select m_id , bii_tag , listagg(a_value , '~') within group ( order by seq_no ) ans_vald
from ca
where m_id = 100987
group by m_id , bii_tag
ORA-01489: result of string concatenation is too long
Server Pool Vs Services
October 8, 2009 - 12pm Central time zone
Reviewer: A reader from 08831
Tom .
What is the difference between Server Pool in 11gR2 and Services in 11r1/10g .... ?
Read the manual . I did not get differnce . Thanks for your time.
October 8, 2009 - 2pm Central time zone
Reviewer: Oleksandr Alesinskyy
With all due respect -
would be second volume of your "Expert Oracle Database Architecture: 9i and 10g ..." named "
Expert Oracle Database Architecture: 12x and 13x" ?
what version is better between 11gr1 and 11gr2
October 10, 2009 - 7pm Central time zone
Reviewer: John
Hi Tom,
if we have to upgrade to 11g, what release is better between the 11gr1 and 11gr2?
Thanks
recursive subquery factoring
October 11, 2009 - 11am Central time zone
Reviewer: James Su from Toronto
Dear Tom,
Can you show an example using the recursive subquery factoring feature that can not be achieved by
using "connect by"?
Thanks.
... and some bug
October 19, 2009 - 8am Central time zone
Reviewer: Massimiliano Piccinini mp from Italy
If you open the same cursor for thousands time (something like 10000)
in a 2 nodes rac, with CURSOR_SHARING similar, the gv$Sql_plan fill up of all similar lines, with
an ora0600 - 17059.
October 20, 2009 - 11am Central time zone
Reviewer: Alexander
Tom,
Can you explain why you like "Edition Based Redefinition" so much? I was reading about it, I guess
I don't get it. In my experience modifying objects does not take long. Also, if you are doing an
application upgrade, the application is going to be down, whether the database can remain active or
not, don't you think? Trying to think of a useful real world situation where this would really
save me.
Followup October 22, 2009 - 4pm Central time zone:
... Also, if you are doing an application upgrade, the
application is going to be down, whether the database can remain active or not,
don't you think? ...
No, it doesn't have to be - and that is the entire point.
Installing the code - can be impossible in a production system without taking an outage.
If you want to install 5 'fixed' procedures - you cannot do it while they are running - you need to a) stop everyone, b) compile the five procedures c) make sure everything went Ok, recompile dependents - fix whatever and then you can let them back in.
EBR (edition based redefinition) removes step (a). They continue while you are doing your thing.
Of the 36 hours it takes to upgrade Oracle Apps - how many hours do you think is spent compiling plsql? (hint: it is double digits of hours)
Any change requires an outage in the past, now it doesn't.
My next three Oracle Magazine columns (starting in April I thing) will be about EBR - doing the simple easy case (patching - something we do very very often - no schema changes but code fixes - no downtime code fixes now). Then a harder case - to minimize the downtime to go to version two - we'll stage not only our code but ALSO ALL OF OUR SCHEMA MODIFICATIONS. Then the last case whereby you run version 1 and version 2 at the same time for some period.
11g performance tuning features
October 23, 2009 - 2am Central time zone
Reviewer: A reader
Hi Tom
can you please list top 10 -20 new performance improvement featurs which are introduced in 11g.
I know only one - adoptive cursor sharing, which resolved the bind variable peeking issue and so in
turn reduce the chances of a query suddenly start taking a bad path.which is great.
I have also read about "Database Smart Flash Cache"
and then Stored Outlines Migration to SQL Plan Management and some more as given in new featurs
guide.
can you please list some more -specially with respect to code and server side .
Thanks
performance tuning improvements
October 24, 2009 - 1am Central time zone
Reviewer: A reader
Tom
thanks for the link, very useful it is.
thanks
RESULT_CACHE got just better
November 6, 2009 - 10am Central time zone
Reviewer: Guru from Chennai
With Oracle 11gR2, RELIES_ON clause is done away in RESULT_CACHE. From Release 11.2, result caches
in Oracle RAC Environment are no longer private. Nice ones though.
An example of "Recursive Subquery Factoring"
November 12, 2009 - 10am Central time zone
Reviewer: Anton from Utrecht
An example of something using "Recursive Subquery Factoring" which can't be done using "connect by":
http://technology.amis.nl/blog/6404/oracle-rdbms-11gr2-solving-a-sudoku-using-recursive-subquery-fac
toring
Followup November 15, 2009 - 2pm Central time zone:
Anton,
I hope you don't mind, but I've been using that in my "top 11 things about 11g" since I saw it appear on Reddit.com a while back :)
I open up your website as an embedded link in my presentation.
It never fails to get a good chuckle, well done!
great, anton !
November 16, 2009 - 2am Central time zone
Reviewer: Sokrates
November 16, 2009 - 9am Central time zone
Reviewer: Anton
Of course I don't mind if you mention my blog.
|