Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: # of datafiles per tablespace

RE: # of datafiles per tablespace

From: Wong, Bing <bing.wong_at_IngramMicro.com>
Date: Tue, 05 Mar 2002 09:33:21 -0800
Message-ID: <F001.0041FBCE.20020305093321@fatcity.com>


Having 500mb, and then 4 50mb data files is over killed. Have multiple files per tablespace is generally good pratice and should make them same size. We have limited the database files to 2G for just one reason. That reason is that if you have to recover a database or move to another box, it is easier to manage them in terms of the disk size of file system on that target box--just in case the target box file system size could not fit the large file.

-----Original Message-----
Sent: Tuesday, March 05, 2002 5:59 AM
To: Multiple recipients of list ORACLE-L

Ah, but we use partitioning. However, the design you described is slightly flawed me thinks. I had to do something similar at the last job and what we did is have a separate tablespace for each month, which in turn produces a separate data file of course. Not that there was anything wrong in what you said per say its just that it really does not simulate partitioning if they are all in the same tablespace. It would be purely a load balancing thing.

That being said, I am not really anticipating a load balance problem on this server. Not saying its not possible, I am just not anticipating it. But with 9i it would be fairly easy to reorganize after the fact if I do experience it.

-----Original Message-----
Sent: Tuesday, March 05, 2002 4:58 AM
To: Multiple recipients of list ORACLE-L

Not using the RBS tablespace as the tablespace of discussion because it has special requirements and can create a lot of discussion.  I can fore see a reason for using multiple datafiles in a tablespace. Lets say that you have a large table than contains information based on dates. you load the table with data each year and at the years end you resize the datafile to eliminate the unused space. Then you create another datafile for the tablespace to use for the next years data and load the data for the new year. The new data is still part of the same table and tablespace but in a separate datafile. It could be a method of creating partitions when you can't afford the option or it is not available to you (pre 8). Then you would eliminate some of the bottlenecks with the IO to the drives if the datafiles are on different drives. The users would see an improvement in response time if the were querying different date based data.
Also the multiple datafile concept could be used during the backup/restore process. The user could have limits to the max tape size available but still want to backup the database. I know that it could take a lot of tapes to backup a 70GIG database when your tape machine has a 2GIG limit on the tape capacity. They do still exist. ROR mm

>>> wisernet100_at_yahoo.com 03/04/02 09:28PM >>>
no reason. I can see creating multiple files under those conditions only because you want to keep files to a specific size.

Now, I did once find that the rollback datafiles were a bottleneck on a
system I had. So we built TWO rollback tablespaces, with datafiles on different mount points etc and the rollback segments divided between the two tablespaces.

cleared up that bottleneck like a dream

other than that though.. why?


Do You Yahoo!?
Yahoo! Sports - sign up for Fantasy Baseball http://sports.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
  INET: wisernet100_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the H
ELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ron Rogers
  INET: RROGERS_at_galottery.org

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Kimberly Smith
  INET: ksmith2_at_myfirstlink.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Wong, Bing
  INET: bing.wong_at_IngramMicro.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Tue Mar 05 2002 - 11:33:21 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US