Two things I learned this week...
Posted
by noreply(at)blogger.com (Thomas Kyte)
on Oracle Blogs
See other posts from Oracle Blogs
or by noreply(at)blogger.com (Thomas Kyte)
Published on Tue, 20 Mar 2012 14:44:36 +0000
Indexed on
2012/03/20
17:34 UTC
Read the original article
Hit count: 363
Filed under:
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>
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....)
© Oracle Blogs or respective owner
