Two things I learned this week...
- by noreply(at)blogger.com (Thomas Kyte)
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#SQLRF53578Using 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 dualnew 1: select ' world' from dual'WORLD------ worldops$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 wayhttps://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4549764300346084350#4573022300346189787 ops$tkyte%ORA11GR2> select '&' || 'hello world' from dual;'&'||'HELLOW------------&hello worldops$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....)
