0

In my project we have used temporary tablespace say X_TEMP ,assume below is tablepsace code , I have found this code in tablespace section 

CREATE TEMPORARY TABLESPACE X_TEMP
TEMPFILE '/oradata/mytemp_01.tmp' SIZE 800M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Now I want to check how it is getting called and where I have used this X_TEMP in my procedure,functions,code anywhere where ?

Any idea how can find where We have used it ?

Andrea
  • 11,801
  • 17
  • 65
  • 72

1 Answers1

1

The question doesn't make a whole lot of sense.

Unless you happened to create temporary tables in that tablespace (which would be unusual in Oracle but which would let you tie some usage to particular pieces of code), your user's temporary tablespace (I assume that after creating this temporary tablespace you made it the temporary tablespace for some user) would be used whenever Oracle needed to page data to disk. A query that needs to sort data, for example, might use temporary tablespace. Or it might not, execution to execution, depending on data volumes, how much PGA the session is able to get, the query plan used, etc. Any query this user executes could use temporary tablespace at any time. Or none of its queries might use temporary tablespace because they can all be done in memory. Or they might not use temporary tablespace today and start using it tomorrow because someone else is running some code that limits how much PGA Oracle can give the user's sessions.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Thanks for reply The thing is I have read on net about temporary tablespace I understood that we will use this whenever we need to do operations like sorting and join on huge records. this is ok . But as my confusion is from where it is getting call or triggered . What is pre-procedure of this tablespace From your answer I am getting that System will automatically call this whenever there is operations like sorting etc.. –  Sep 02 '15 at 09:28
  • Do u know how and when it gets used ? –  Sep 02 '15 at 09:32
  • @Suresh - I'm not sure that I understand the question. If you are asking whether you need to write code to specifically tell Oracle to use space in the temporary tablespace, no, you don't (other than telling Oracle what the default temporary tablespace for a particular user is). When that user runs a query that requires more temporary space than it can allocate in RAM, Oracle automatically knows to use the temporary tablespace. – Justin Cave Sep 02 '15 at 10:00
  • Hi Still i have one more Question . We do not tell oracle to run this operations on tablespace . this is clear. but if i am doing any normal select operation.. each my select request will use tablespace or what ? If not then is there any criteria to used tablespace only while doing any special operations like join & sort ? –  Sep 03 '15 at 06:46
  • @Suresh - Oracle will use temporary tablespace when it needs to. If you have a query that needs to sort some data, Oracle would prefer to do that sort in memory. If the session can't do the sort in memory, then it will use the temporary tablespace. Whether the session can do the sort in memory depends on how much PGA memory the session can get (which depends on a variety of configuration parameters and what other sessions are doing) along with how much data needs to be sorted. – Justin Cave Sep 03 '15 at 18:34