RE: Tablespace with more than one file

First of all a warning: if your filesystem only allows files of max 2GB, then don't make your datafiles exactly 2GB in size. Oracle adds one block to the given size:

Freek D'Hooge
Uptime
Oracle Database Administrator
e-mail: freek.dhooge_at_uptime.be
tel. +32 (0)3 451 23 82
http://www.uptime.be
disclaimer

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] On Behalf Of gidhin joy [gidhin_at_gmail.com] Sent: 12 October 2009 09:05
To: gheibia_at_gmail.com
Cc: Toon Koppelaars; oracle-l-freelists
Subject: Re: Tablespace with more than one file

You are right only if the file #1 is fully used. Check whether file #1 is auto extendable to a size more than 2G, if so make auto extendable off and resize it to maxsize of 2G so that further writes will go to file #2.  

alter database datafile '/datavol/oradata/sid/file1.DBF' autoextend off; alter database datafile '/datavol/oradata/sid/file1.DBF' resize 2G;  

Best Regards
~Gidhin  

2009/10/12 Amir Gheibi <gheibia_at_gmail.com>

Yea, But if I do that, Oracle just gonna continues writing the second file automatically?

On Mon, Oct 12, 2009 at 2:28 PM, Toon Koppelaars <toon_at_rulegen.com> wrote:

But the question is how can I prevent the first file from growing? Should I do anything at all or Oracle automatically continues writing on the new file and abandon the first file (as it doesn't write anything there anymore)?

You need to set the AUTOEXTEND property of that datafile to OFF. Here's a url that describes that.

http://hnawri.wordpress.com/2007/05/04/alter-datafile-from-autoextend-on-to-off/  

On Mon, Oct 12, 2009 at 8:02 AM, Amir Gheibi <gheibia_at_gmail.com> wrote:

Hi Listers,

I have a 10gR2 database running on a HP-UX (HP Unix B.11.11 U 9000/800 1801480297). The server's file system allows only files with the maximum size of 2G. My biggest tablespace has one file and right now is about 800 MB in size. I thought one way to do this is to add a new file to the tablespace. But the question is how can I prevent the first file from growing? Should I do anything at all or Oracle automatically continues writing on the new file and abandon the first file (as it doesn't write anything there anymore)?

Cheers,
Amir Gheibi

-- 
Toon Koppelaars
RuleGen BV
+31-615907269
Toon.Koppelaars_at_RuleGen.com
www.RuleGen.com
TheHelsinkiDeclaration.blogspot.com

(co)Author: "Applied Mathematics for Database Professionals"
www.RuleGen.com/pls/apex/f?p=14265:13--
http://www.freelists.org/webpage/oracle-l