Re: Reduce tablespace physical file size

Alon Barnes wrote:

>
> Thanks for the information and sorry for my lack of info (Actually,
> I'm not an Oracle DBA, and the TS is one of many in a product I just
> joined its DEV team and assinged for this problem).
>
> I will try to add some more info:
>
> 1. Operating system is WinNT and oracle 8.17
> 2. TS is not the real name of the table space. here is the creation
> command of the read tablespace:
>
> CREATE TABLESPACE LSC_USERTEMP
> DATAFILE 'F:\database\LSC_USERTEMP.DBF'
> SIZE 5M AUTOEXTEND ON NEXT 4M MAXSIZE 4097M
> ONLINE DEFAULT STORAGE
> (INITIAL 128K NEXT 128K MINEXTENTS 1
> MAXEXTENTS UNLIMITED PCTINCREASE 0)
> TEMPORARY;
Ah, well, there you go. This is indeed a temporary tablespace (created, I might add in a way that is not actually recommended for 8.1.7). That's the sugnificance of that last 'TEMPORARY' keyword.

Knowing that, don't bother looking in dba_segments to find out what's inside it, because there won't be anything there.

Temporary tablespaces contain "temporary segments", which don't contain any real data, but are used when people run reports that require sorting or ordering. When the sort or ordering is finished, the temporary segment is simply marked for re-use by the next person who wants to run such a report... who then over-writes the other user's sort data. And so on.

So the *data* gets over-written, but the temporary segment stays put.

Therefore, if the fact that it's blown out to 4GB is a problem, don't bother trying to shrink the data file. Just drop the entire tablespace and re-create it. There's nothing in there that is needed by anyone, so re-creation is probably your best bet. You can't drop a temporary tablespace, however, if anyone is actually running a report which is making use of it, so you might stil want to shutdown immediate - startup restrict - drop tablespace LSC_USERTEMP. At this point, run off to the operating system and delete the associated dbf. And then re-create the tablespace.

On that subject, you should be creating your temporary tablespaces in 8i as follows:

create temporary tablespace LSC_USERTEMP tempfile 'F:\database\lsc_usertemp.dbf' size 50M
extent management local
uniform size 128K;

The "temporary" keyword moves up in the syntax, and you use "tempfiles", not "datafiles". The 50M size is a guess on my part...

Because the other thing to notice about this problem is that autoextend is a really bad idea. A rogue report will cause (indeed, has caused) the datafile to grow to its MAXSIZE limit. If it is truly a rogue report, it would have been better for it to have failed by not allowing the temp tablespace to grow to accomodate it. If it is a genuine report that needs to be run regularly, it is better to have the temp tablespace big enough to accomodate it from the word go. So you should work out how big the thing needs to be for normal running and create it at that size.

Hope that helps
Regards
HJR
>
> 3. Since this is a tablespace in a customer site, It will take me some
> time
> to run few of the queries written in the replyes here.
>
> Thanks again for your help! appreceate it a lot.
>
> Alon

-- 
--------------------------------------------
See my brand new website, soon to be full of 
new articles: www.dizwell.com.
Nothing much there yet, but give it time!!
--------------------------------------------