Home » RDBMS Server » Server Administration » Consolidating Datafiles
Consolidating Datafiles [message #139631] Wed, 28 September 2005 20:50 Go to next message
PompeytilIDie
Messages: 9
Registered: March 2005
Location: Western Australia
Junior Member
Hi All,

In the past when a datafile has run out of space, instead of extending the existing file, the previous DBA simply added a new file. The result now is there are loads of itty bitty files dotted all over.

Therefore, please can someone describe the procedure (and commands) to consolidate a tablespaces many datafiles.

Thanks in advance,
PTID
Re: Consolidating Datafiles [message #139685 is a reply to message #139631] Thu, 29 September 2005 03:04 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
hi,

Your Previous DBA did the right thing by adding file coz i think if he would have doen autoextensible ON for your datafile then it would have been difficult to manage the big file in long run.

It is not possible as per my knowledge to merge all datafile of one tablespace into single big datafile.

Regards
Always Friend Sunilkumar
Re: Consolidating Datafiles [message #139692 is a reply to message #139685] Thu, 29 September 2005 03:52 Go to previous messageGo to next message
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member
HI

First thing.. your dba did the right thing in having multiple datafiles.

Still, if you want to consolidate, add a new tablespace and move all objects in this tablespace to the new one & then drop this tablespace.

Regds
Girish
Re: Consolidating Datafiles [message #139705 is a reply to message #139692] Thu, 29 September 2005 04:35 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>The result now is there are loads of itty bitty files dotted all over.
If that means multiple small, obscure sized datafiles, then it is not very desired.


IMHO, there is more to look into ( multiple small datafiles Vs one large datafile).
It does matter for management (backup).
Now oracle has Bigfile tablespace concept which can hold terabyte datafiles.

quoting docs
Quote:

Consider Possible Limitations When Adding Datafiles to a Tablespace
You can add datafiles to traditional smallfile tablespaces, subject to the following limitations:
* Operating systems often impose a limit on the number of files a process can open simultaneously. More datafiles cannot be created when the operating system limit of open files is reached.
* Operating systems impose limits on the number and size of datafiles.
* The database imposes a maximum limit on the number of datafiles for any Oracle Database opened by any instance. This limit is operating system specific.
* You cannot exceed the number of datafiles specified by the DB_FILES initialization parameter.
* When you issue CREATE DATABASE or CREATE CONTROLFILE statements, the MAXDATAFILES parameter specifies an initial size of the datafile portion of the control file. However, if you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the control file will expand automatically so that the datafiles section can accommodate more files.

Consider the Performance Impact

The number of datafiles contained in a tablespace, and ultimately the database, <B>can</B> have an impact upon performance.

Oracle Database allows more datafiles in the database than the operating system defined limit. The database DBWn processes <B>can</B> open all online datafiles. Oracle Database is capable of treating open file descriptors as a cache, automatically closing files when the number of open file descriptors reaches the operating system-defined limit. This <B>can</B> have a negative performance impact. When possible, adjust the operating system limit on open file descriptors so that it is larger than the number of online datafiles in the database.


Please note that, documentaion says 'CAN'. So it depends on OS/oracle version ( likewise in certain windows edition, files more than 4g with autoextend on has problems).

[Updated on: Thu, 29 September 2005 04:36]

Report message to a moderator

Re: Consolidating Datafiles [message #139807 is a reply to message #139631] Thu, 29 September 2005 12:41 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Yeah, I would think that having multiple datafiles is not always good and not always bad. It all depends. That said, Girish has it right in that you just create a new tablespace, move the data, and drop the old ts and files.
Previous Topic: Question on Oracle 10g
Next Topic: i/o_slave_wait
Goto Forum:
  


Current Time: Sat Jan 25 08:28:41 CST 2025