I got so excited about ORADEBUG feature I described in the previous post that completely forgot to mention the main news of the day - the second day of Tom’s seminar. It was an excellent day packed with lot of useful tricks and tips plus I cleaned quite a few things in my mind. The best one that I got straight now - so called “Write Consistency” in Tom’s terms.
Small but neat 10g R2 feature:
SQL> SET SERVEROUTPUT ON SIZE UNLIMITED
I can also put up to 32K lines using DBMS_OUTPUT.PUT_LINE. No comments.
And by the way, Tom’s story with Best Buy was real as he mentioned today.
Was playing with ORADEBUG today and figured out a cool feature - you can actually close trace file using ORADEBUG CLOSE_TRACE. The only way I knew before was to shrink the file. So now after 10046 trace you don’t need to restart the process to close trace file.
If your SMON, PMON, etc. Oracle process is dumping like crazy (kind of abnormal but who needs a DBA anyway if SMON is not dumping regularly?) - you can close this file and actually delete it without bouncing the instance. Never really dealt with Windoracle on that matter but I suspect that removing SMON’s trace file, for instance, is not possible (it’s locked) without stopping Oracle instance UNLESS one uses ORADEBUG CLOSE_TRACE.
How many times you got into situation when dump destination got full and someone (perhaps, you) cleaned it up using something trivial like ‘rm *trc’? Out of those cases how often you noticed that several GB filesystem with single 100 KB alert.log file is still 99% full after “clean up” because SMON and/or whatever other backgroung processes keep inodes open? My bet is - at least 50%.
Now you need simple:
SQL> oradebug setospid
Statement processed.
SQL> oradebug close_trace
Statement processed.
Isn’t that amazing? Just checked - it’s available at least in 9iR2, 10gR1 and 10gR2.
So here I am with my first blog after a while when everyone seems to have been blogging for at least half of their internet-enabled life. Let’s see how it goes.
Anyway… Today I’ve been at the first day of Tom Kyte’s 2 days seminar in Munich. I’ve enjoyed the day very much and I’m looking forward for tomorrow already. There is one thing that caught my attention - Oracle 10g Release 1 introduced a nice feature to string literals format. I can now forget about troublesome quoting in SQL and PL/SQL gererating dynamic SQL code. It works similar to Perl’s Quote Operator.
Imagine my PL/SQL procedure to create new partitions for next day in some table t1. Now instead of
stmt : = ‘ALTER TABLE t1 SPLIT PARTITION p_last AT (TO_DATE(”’
|| TO_CHAR (TRUNC(sysdate+2), ‘YYYY-MM-DD HH24:MI:SS’)
|| ”’,”SYYYY-MM-DD HH24:MI:SS”, ”NLS_CALENDAR=GREGORIAN”)) INTO (PARTITION p_’
|| TO_CHAR(sysdate+1,’YYYYMMDD’) ‘, PARTITION p_last);’
I can write simple
stmt : = q’[ALTER TABLE t1 SPLIT PARTITION p_last AT (TO_DATE(']‘
|| TO_CHAR(TRUNC(sysdate+2), ‘YYYY-MM-DD HH24:MI:SS’)
|| q’[','SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) INTO (PARTITION p_]‘
|| TO_CHAR(sysdate+1,’YYYYMMDD’) ‘, PARTITION p_last);’
Well, still it’s not that readable and elegant as in Perl. RTFM for details - Oracle® Database SQL Reference10g Release 2.
PS: After some editing I still couldn’t get the formating to look like I wanted it… WYSIWYG editor for blogger.com seems to be not the best one. Also some tags break the layour of the whole page even though closed correctly withing the post. What anoyed me most is that pipes || are getting constantly removed by WYSIWYG editor!
Recent Comments