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:


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



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;

&hello world


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....)



Blogger Jeff Hunter said....

You know you've been with the product a long time when you remember you forgot something that you learned before!

Tue Mar 20, 10:57:00 AM EDT  

Blogger Mark W. Farnham said....

All very useful in context Tom.
YAUW (Yet Another Useful Way)
set escape \

SQL> set escape \
SQL> show esc
escape "\" (hex 5c)
SQL> select '\&hello world' from dual;

&hello world

Your mileage may of course vary and all these are useful from time to time depending on the context.

Tue Mar 20, 01:22:00 PM EDT  

Anonymous Anonymous said....

My suggestion:

select unistr('\0026hello world') from dual;

Tue Mar 20, 04:40:00 PM EDT  

Blogger Boaz said....

If you look in to Google and search Oracle LTD you get:
70,400,000 Related links
6,210,000 Images
797,000 movies
452,000 books
1,490,000 Blogs
974,000 discussions
What this numbers means?
I do not know. Maybe nothing.
But 2 new things a day sounds like a good pace.

Wed Mar 21, 06:59:00 AM EDT  

Blogger RAJESH said....

Thanks for sharing Tom.

I learnt your Trick#1 sometime long ago


Thu Mar 22, 04:33:00 AM EDT  

Anonymous Pointers said....

Hi Tom,

As usual, thank you very much for your university.

These two new tricks new to me, good to know these.

Coming to the first part, I got to have a similar kind of scenario recently in my production environment.

The problem was --

"Our daily job faied due to the temp tablespace issue i.e. unable to extend the temp segment error,
we have contacted DBA and asked DBA to increase the temp tablespace but that could not help us,
though the temptablespace was increased multiple times, our daily job faied with the same reason multiple times,
upon analysis (i am not a DBA) , I found that the temptablespace was created using the dictionary managed mode, and one more interesting thing was that the temp tablespace was not releasing the temp space used.

So I have informed my senior team that this dictinary managed mode could be a reason for not releasing the temp tablespace and suggested that we would
create locally managed temp tablespace, but ironically I got a strange answer that our senior team found that the temp tablespace was created in dictionary mode 2 years back (before me :) )
so they thought in the same way as i thought and they created a locally managed temp tablespace in production, but locally managed temp tablespace reduced the application performance quit a lot,
they asked Oracle help, they could not help us, finally they reverted back to the dictionary managed temptablespace."

I am not a DBA, I would like to get some clarity on the below questions, would request your help on this.

1. Why the temptablespace was not releasing the space used, we are using Oracle 10g. I was under impression that the temptablespace would release the space used after the transaction is over or after the session is closed accordingly.
Could you please share in what are the scenarios the temp tablespace would not be released.
2. If temp tablespace is not released, how to get back the space.
3. Is this behaviour expected i.e. in some cases dictionary managed temptablespaces are faster than locally managed temptablespaces.
4. Finally, we have removed parallel hint from a query from our daily job and the job ran successfully without any temp tablespace issue, could you please explain the releation between parallel hint and the temp tablespace.

Thanks a lot in advance.


Sun Mar 25, 04:53:00 PM EDT  


<< Home