Tuesday, March 20, 2012

Two things I learned this week...

I often say "I learn something new about Oracle every day".  It really is true - there is so much to know about it, it is hard to keep up sometimes.

Here are the two new things I learned - the first is regarding temporary tablespaces.  In the past - when people have asked "how can I shrink my temporary tablespace" I've said "create a new one that is smaller, alter your database/users to use this new one by default, wait a bit, drop the old one".  Actually I usually said first - "don't, it'll just grow again" but some people really wanted to make it smaller.

Now, there is an easier way:

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_3002.htm#SQLRF53578

Using alter tablespace temp shrink space .


The second thing is just a little sqlplus quirk that I probably knew at one point but totally forgot.  People run into problems with &'s in sqlplus all of the time as sqlplus tries to substitute in for an &variable.  So, if they try to select '&hello world' from dual - they'll get:


ops$tkyte%ORA11GR2> select '&hello world' from dual;
Enter value for hello: 
old   1: select '&hello world' from dual
new   1: select ' world' from dual


'WORLD
------
 world


ops$tkyte%ORA11GR2> 


One solution is to "set define off" to disable the substitution (or set define to some other character).  Another oft quoted solution is to use chr(38) - select chr(38)||'hello world' from dual.  I never liked that one personally.  

Today - I was shown another way



ops$tkyte%ORA11GR2> select '&' || 'hello world' from dual;

'&'||'HELLOW
------------
&hello world

ops$tkyte%ORA11GR2>

just concatenate '&' to the string, sqlplus doesn't touch that one!  I like that better than chr(38) (but a little less than set define off....)


Thursday, March 15, 2012

OpenWorld 2012 Call for papers...

The call for papers for Oracle OpenWorld is now open.  Give it a try and see if you can get a slot!