| How to flush a Locally Managed Temporary Tablespace? [message #59667] |
Mon, 15 December 2003 03:47  |
patrick
Messages: 83
Registered: December 2000
|
Member |
|
|
Does anyone know how to flush the Default Temporary Tablespace which is locally managed?
In dictionary managed tablespace we can run:
alter tablespace temp default storage (pctincrease 0);
But in Locally managed, it's impossible to alter the storage settings..
Thank you in advance for your help!
Regards,
Patrick.
|
|
|
|
| Re: How to flush a Locally Managed Temporary Tablespace? [message #59668 is a reply to message #59667] |
Mon, 15 December 2003 04:08  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
By 'flush' do you mean release the temp segments allocated ?
In true temporary tablespaces, you dont have to do it. The segments are reused for sorts(eliminating the performance overhead associated with dynamic allocation and deallocation of segments and their associated recursive sql due to data dictionary updates). The extents are allocated(& grow as needed) during the initial sorts and are maintained by Oracle thereafter.
Locally managed tablespaces are there to help you ;)
-Thiru
|
|
|
|
| Re: How to flush a Locally Managed Temporary Tablespace? [message #59670 is a reply to message #59668] |
Mon, 15 December 2003 04:40 |
patrick
Messages: 83
Registered: December 2000
|
Member |
|
|
Thank you for your answer!
Yes my default temporary tablespace is in locally managed tablesapace modus!
BUT: this temporary default tablespace is running out of space (99,9% used!)! And I have a 3000 MB tempfile for this tablespace! So I don't understand, when there is no activity, why this space is still required and is still remaining 99,9% used/full!!
Shouldn't it be used when huge sorting occuring and when it's over then my tablespace is recovering more free space?
Thank you again!
Regards,
Patrick.
|
|
|
|
| Re: How to flush a Locally Managed Temporary Tablespace? [message #59671 is a reply to message #59670] |
Mon, 15 December 2003 05:22 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Patrick,
In case of true temporary tablespaces, the sort segments once allocated , DO NOT GET deallocated to minimise the performance overhead as I mentioned earlier. The space gets reused for subsequenct sorts. Hence its NORMAL to have them 99.9% full. You can monitor v$temp_space_header
to see the used/free space.
-Thiru
|
|
|
|
| Re: How to flush a Locally Managed Temporary Tablespace? [message #59673 is a reply to message #59671] |
Mon, 15 December 2003 05:38 |
patrick
Messages: 83
Registered: December 2000
|
Member |
|
|
Ok...
But does it mean practically that I have to shutdown and restart my database server (shutdown .. startup..)each time my Default Temporary Tablespace is getting near 99.9% full??
Is there another way than rebooting my instance to free my Default Temporary Tablespace?
Many thanks again!!
Regards,
Patrick.
|
|
|
|
| Re: How to flush a Locally Managed Temporary Tablespace? [message #59674 is a reply to message #59670] |
Mon, 15 December 2003 05:45 |
Sanjay Bajracharya
Messages: 279
Registered: October 2001
Location: Florida
|
Senior Member |
|
|
Patrick,
Looks like your TEMP tablspace is locally managed and 'permanent'.
Have you tried making it LOCAL + TEMPORARY as TEMP tablespaces are supposed to be (check the other thread also).... Isn't this why they created 'temporary' type of tablespace, specially for TEMP ... so that in every restart of the database, it is initialized and flushed ?
If you are hitting the 3 GB limit, then may be you wanna make it a little bigger. I mean, during your peak working period, 3 GB is not enough ...
Our data warehouse TEMP space is 12 GB ... geez. But it is DW and is needed.
And as Thiru mentioned, it may be shown as allocated, but is reused ...So you are hitting the 3 GB limit.
|
|
|
|
| Re: How to flush a Locally Managed Temporary Tablespace? [message #59675 is a reply to message #59673] |
Mon, 15 December 2003 06:17 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Patrick,
again as I mentioned earlier, it is NORMAL to have it 99.9% full in case of TRUE TEMPORARY(created using CREATE TEMPORARY TABLESPACE ..tempfile 'temp.dbf' ) tablespaces, since the space is reused . Oracle doesnt have to create a new sort segment or take care of releasing it when the query is done as they are too expensive. It is supposed to use all of the space(hence 100%).
At your peak usage, monitor v$temp_space_header, v$tempseg_usage,v$sort_segment(for tablespaces defined TEMPORARY ) etc for actual space usage used/free space and add space if required.
If you have defined your temporary tablespaces PERMANENT,then the sort segments are allocated & deallocated(well SMON is supposed to clean them) and 99.9% full 'could' indicate that you are running out of allocated space , in which case you may need to increase your tablespace size,if you need to ( are queries failing for lack of space ? ) . But before doing so, try to manually release the sort segments not in use (you could try switching the tablespace type between permanent & temporary like this
alter tablespace TEMP temporary;
alter tablespace TEMP permanent;
), to free up space.
HTH
-Thiru
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
