how to reduce size of datafile

[ramji]

Dear all,

I am facing problem as below

i hv temp tablespace in my db. the name of
tablespace : temp (file name temp01.dbf) by default it was in autoextend datafile mode when full)
due to autoextend my developer has done some mistake in writing query statement (in loop). my temp size increase upto maximum extend & full(defined space on OS).
Now i have remove this query & restart database now used temp space is free (only zero). but now
i will try to reduce the size of temp01.dbf file but it giving error
(we can not reduce the size ..)
how to reduce the size of temp01.dbf ? please advice

regs
ramji

[clio_usa]

You have to query the data dictionary views, then find the min size you can shrink the datafile. A ready to use script is posted Here (Script for calculating the MAX size to which datafile can shrink )


Hope that helps,

clio_usa

OCP 8/8i/9i DBA

[shandj]

ALTER DATABASE DATAFILE '' resize 200m;

What are the storage parameters of your temp tablespace?? Is contents of your tablespace permanent or temporary. If it permanent change it to temporary.

[akhadar]

Create one more temp tablespace of required size and drop the old one.

[julian]

Originally posted by clio_usa
You have to query the data dictionary views, then find the min size you can shrink the datafile. A ready to use script is posted Here (Script for calculating the MAX size to which datafile can shrink )


Hope that helps,

clio_usa

OCP 8/8i/9i DBA

The query you refer to addresses dba_data_files. He wants to reduce the TEMP file. Even so, resizing TEMP files is often pointless for their HWM is usually hitting the top.

In his case, the best solution is to:

CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '/u03/oradata1/.../temp02.dbf' size 15M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

drop tablespace temp including contents and datafiles;

CREATE TEMPORARY TABLESPACE temp TEMPFILE '/u03/oradata1/.../temp01.dbf' SIZE 1024M reuse
AUTOEXTEND ON NEXT 256K MAXSIZE 6144M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

[Ablakios]

ramji

If you have a locally managed temporary tablespace you are stucked. I particularly went thru that problem where I find myself adding space to the LMTT because
1) I was unable to resize it
2) will not deallocate even when there is no sorting going and I hate
to see 8 gig allocated to temporary when I could help with load balancing.
So I drop and recreated a DMTT(Dictionary Managed Temporary Tablespace)where I can take control of manually deallocate the temp tablespce as follows:

1) FIND USERS WHO ARE SORTING IN THE TEMP TABLESPACE:
**************************
SELECT t1. username, t2.tablespace, t2.contents, t2.extents, t2.blocks
FROM v$session t1, V$sort_usage t2
WHERE t1.saddr = t2.session_addr ;

If no is returned proceed withn the 2nd step.

2) SECOND RUN THIS TO COALESCE FREE EXTENTS*
********************************
****Alter tablespace temp coalesce ;

3) THIRD RUN THIS TO RECLAIM THE SPACE
***************************
alter tablespace temp
default storage (pctincrease 1);

FOLLOWED BY

alter tablespace temp
default storage (pctincrease 0);

On more thing about autoextend on. after reading all docs I personnally have my system tablespace autoextend on that was it.
HTH

[ramji]

Step:2 ,3 are not runing . it display error
"SQL> Alter tablespace temp coalesce ;
Alter tablespace temp coalesce
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE



SQL> alter tablespace temp
2 default storage (pctincrease 1);
alter tablespace temp
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
--------------------------------------------------------"
Regs
ramji

Originally posted by julian
The query you refer to addresses dba_data_files. He wants to reduce the TEMP file. Even so, resizing TEMP files is often pointless for their HWM is usually hitting the top.

In his case, the best solution is to:

CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '/u03/oradata1/.../temp02.dbf' size 15M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

drop tablespace temp including contents and datafiles;

CREATE TEMPORARY TABLESPACE temp TEMPFILE '/u03/oradata1/.../temp01.dbf' SIZE 1024M reuse
AUTOEXTEND ON NEXT 256K MAXSIZE 6144M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

[akhadar]

What u have done as per the julian reply?

[slimdave]

Originally posted by Ablakios
ramji

If you have a locally managed temporary tablespace you are stucked. I particularly went thru that problem where I find myself adding space to the LMTT because
1) I was unable to resize it
2) will not deallocate even when there is no sorting going and I hate
to see 8 gig allocated to temporary when I could help with load balancing.
So I drop and recreated a DMTT(Dictionary Managed Temporary Tablespace)where I can take control of manually deallocate the temp tablespce as follows:

1) FIND USERS WHO ARE SORTING IN THE TEMP TABLESPACE:
**************************
SELECT t1. username, t2.tablespace, t2.contents, t2.extents, t2.blocks
FROM v$session t1, V$sort_usage t2
WHERE t1.saddr = t2.session_addr ;

If no is returned proceed withn the 2nd step.

2) SECOND RUN THIS TO COALESCE FREE EXTENTS*
********************************
****Alter tablespace temp coalesce ;

3) THIRD RUN THIS TO RECLAIM THE SPACE
***************************
alter tablespace temp
default storage (pctincrease 1);

FOLLOWED BY

alter tablespace temp
default storage (pctincrease 0);

On more thing about autoextend on. after reading all docs I personnally have my system tablespace autoextend on that was it.
HTH

Sounds like you've got yourself a job for life there.

How often do you have to resize TEMP? Pretty rarely, i would think.

Why do you need to coalesce free space?
Because you are using a dictionary managed tablespace.

Why do you hate it that the temp space is allocated in an LMT, even when it's not actually in use?
Because it's not what you're used to.

You know how to load balance TEMP? Use a LMT, uniform extent size equal to sort_area_size, and multiple datafiles spread across all your devices. Very easy.


So for interfering with progress and artificially inflating your own workload at the expense of good DB management techniques, you're fired.

[DaPi]

Originally posted by julian
CREATE TEMPORARY TABLESPACE temp TEMPFILE '/u03/oradata1/.../temp01.dbf' SIZE 1024M reuse
AUTOEXTEND ON NEXT 256K MAXSIZE 6144M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Julian, is there a reason for you suggesting the autoextend value being 256K when (presumably) the extra space will be needed in units of 1M? Or did you mean 256M? Tx.