Consolidating Datafiles [message #139631] |
Wed, 28 September 2005 20:50 |
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 |
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 |
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 |
|
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 |
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.
|
|
|