A reader, May 09, 2002 - 2:21 am UTC
adding new datafile after 2 gb
OCT, December 23, 2002 - 10:45 pm UTC
Thanks ,I have a further add on to this
we have two Tablespace Data and Index in LMT mode.
both have one datafile size 1.6 gb data and 1.8gb index on different HDD.
Now as you mentioned that "never growing a file over 2gig."
1. why?please brief.
2.what is the best choice when we add a new datafile to above tablespace:-1.on the same HDD as the old one
or on different HDD.
Our DBase growing at the rate of 5-6 mb /week.
December 24, 2002 - 8:27 am UTC
1) i never say never - except to say that I never say never.
I said in most cases i PREFER to keep the file at 2gig or less as all OS utilities will work with 2gig and less files, some will not and not all file systems support files over 2gig.
However, if I'm working on a really big database -- hundreds of gigabytes -- large files will be something to consider.
2) you want to add the datafile to the drive that is currently experiencing the least amount of IO.
A reader, December 24, 2002 - 10:14 pm UTC
A reader, May 06, 2003 - 8:09 pm UTC
Currently I have only one datafile created in the tablespace. And created 1m size with the autoextend and maxsize 10G. I want to change the maxsize to be 5G. Is it possible?
May 06, 2003 - 8:57 pm UTC
sure alter database datafile 'foo' autoextend on maxsize ...;
V.Vijay Kaushik, April 01, 2004 - 6:02 am UTC
You can change the size from 10G to 5G by the above mentioned command,but if your datafile already has datas exceeding 5G you will get error when you try to resize from 10G to 5G.Hope this helps.
Just a clarification
A reader, December 24, 2004 - 2:49 pm UTC
Can we use the ALTER DATABASE .. RESIZE command for datafiles belonging to SYSTEM tablespace. Shouldn't the database be brought down if it involves SYSTEM tablespace?
The database needs to be shutdown only when you want to rename a file belonging to the SYSTEM tablespace. Is that correct?
If you want to rename a datafile belonging to a tablespace other than SYSTEM tablespace, it is enough if the tablespace is brought offline and then brought back online after the file is renamed. Is that correct? If not please advise
December 24, 2004 - 3:25 pm UTC
no -- resizing is just fine.
you need to shutdown to rename (move) the system datafiles, but not to resize them (they grow as needed with autoextend as well).
you only need to offline a tablespace other than system to rename its files.
reader, December 24, 2004 - 8:15 pm UTC
From manageability point of view, why would DBAs prefer to use 10g Big File tablespace? If the file grows to 20G in a bigfile tablespace for example, and if I loose that file for some reason, I have to restore the entire file from backup and my whole tablespace(application) is offline until the recovery is complete. Thanks.
December 24, 2004 - 9:52 pm UTC
you've had block level recovery (you know, 2k at a time if that is what you are using) since 9i.
the tablespace hasn't been the unit of recovery for years.
Muhammd Harshad, March 23, 2005 - 7:49 am UTC
I was totally desperated , without this help, now My application is running by executing your commands,
Thanks again really really good
Jack Douglas, February 06, 2006 - 1:55 pm UTC
Tom said: "you've had block level recovery (you know, 2k at a time if that is what you are using) since 9i."
For anyone not running Enterprise Edition you will need to be aware that this feature is not available. I think this means you might well want to consider keeping the maximum file size of datafiles down in order to keep MTTR down.
Please correct me if I'm wrong here.
February 07, 2006 - 1:09 am UTC
if your unit of recovery is a file, then yes, the size of the file will be one of the major factors in determing the time to repair (however, the time to get a single file of any size off of a sequential IO device like a simple tape drive is probably the largest and if you are small enough to be using SE, you are likely using a relatively inexpensive tape unit as well - so the time to repair is likely less a function of the file size and mostly a function of how fast your tape drive can seek)
Bate A Obennjock, May 17, 2021 - 2:20 am UTC
I added datafile but is still showing 100 percent full. I not using the maxsize i don't know why.
May 17, 2021 - 12:25 pm UTC
What's showing 100% full?