Printable View
Hello all..
My TEMP Tablespace in Oracel 817 on Sun 5.8 gets filled up everyday...so i manually issue the following command everyday.
ALTER TABLESPACE TEMP DEFAULT STORAGE (PCTINCREASE 0);
How do i make ORacle Run this Commnad automatically every day ?...
thanks
Ron
Why do you want to do this? Clearing the temporary tablespace? If that is what you are trying to do, then this command does not help.Quote:
Originally posted by vr76413
ALTER TABLESPACE TEMP DEFAULT STORAGE (PCTINCREASE 0);
This time drop your temp tablespace and create a big temp tablespace may size 1024 MB and initial size 400 k and next 400 k and unlimited extents .
Temporary do alter tablespace temp coalesce; may help
any way it should do auto coalesce;
Use a locally manged tablespace, with uniform size equal to your sort_area_size.
Be aware that space in temp doesn't get "deallocated" in the way that it did in earlier versions of oracle -- once it's allocated the segments just get reused.
I had the same problem as you have: a temporary tablespace became too big because of a wrong sql-statement.
It's better to limit the growth of the temp tablespace. Just let that SQL-statement fail! Otherwise your server will hang if the harddisk is filled up with temp-extents...
Also the statement you mentioned (ALTER TABLESPACE TEMP DEFAULT STORAGE (PCTINCREASE 0); ) is known to me: It's a way to trigger Oracle to flush the temporary tablespace.
(
I tried it once and after that SMON started to work on the ts. After SMON stopped (visible through the Task Manager of W2K) I tried to drop the TS, but somehow Oracle didn't want to release it. Oracle has some kind of handle with the ts.
I had to bounce the db before I could drop the damn thing.
)
If you really want to schedule it, then you could try to execute the statement via a PL/SQL procedure. But be sure the procedure is started by a user that has the right privileges.
Hope this helps.
This will take 10 minutes to read up in the doc and 5 minutes to do - and might save you hours . . . .Quote:
Originally posted by slimdave
Use a locally manged tablespace, with uniform size equal to your sort_area_size.