Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Adding a new datafile
- Thread starter baanman
- Start date
SantaMufasa
Technical User
Baanman (and others),
...and yet a third method (which we use exclusively versus static datafiles) to obtain more datafile space is:
ALTER DATABASE DATAFILE 'filename'
AUTOEXTEND ON NEXT 10M MAXSIZE 2000M;
The reason we limit files to 2000m (2GB) is because some operating systems (including some Unix o/s) cannot properly handle file > 2GB. Another reason for 2GB limits is easier file handling for backups.
The reason we use (dynamic) autoextend versus large static file sizes is just-in-time, as-needed file allocation leaves not-yet-used file space for other currently-in-need files.
To set up autoextend from the beginning for a tablespace, we use this CREATE TABLESPACE syntax:
CREATE TABLESPACE <ts-name> DATAFILE '<filename>' SIZE 10m
AUTOEXTEND ON NEXT 10M MAXSIZE 2000M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
The last line has nothing to do with AUTOEXTEND, but I added it for full disclosure on our CREATE TABLESPACE syntax; locally managed tablespaces is another VERY useful feature for efficiently running tablespaces...read up on it if you are not familiar with its benefits.
Dave
...and yet a third method (which we use exclusively versus static datafiles) to obtain more datafile space is:
ALTER DATABASE DATAFILE 'filename'
AUTOEXTEND ON NEXT 10M MAXSIZE 2000M;
The reason we limit files to 2000m (2GB) is because some operating systems (including some Unix o/s) cannot properly handle file > 2GB. Another reason for 2GB limits is easier file handling for backups.
The reason we use (dynamic) autoextend versus large static file sizes is just-in-time, as-needed file allocation leaves not-yet-used file space for other currently-in-need files.
To set up autoextend from the beginning for a tablespace, we use this CREATE TABLESPACE syntax:
CREATE TABLESPACE <ts-name> DATAFILE '<filename>' SIZE 10m
AUTOEXTEND ON NEXT 10M MAXSIZE 2000M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
The last line has nothing to do with AUTOEXTEND, but I added it for full disclosure on our CREATE TABLESPACE syntax; locally managed tablespaces is another VERY useful feature for efficiently running tablespaces...read up on it if you are not familiar with its benefits.
Dave
SantaMufasa
"The reason we use (dynamic) autoextend versus large static file sizes is just-in-time, as-needed file allocation leaves not-yet-used file space for other currently-in-need files"
I am assuming you have some method of checking free space on a daily basis ?
Therefore you know when a tablespace is running out of space and when you need to increase it.
What benefit is there of having the system do this for you, especially as you have a small maximum size as the limit ?
I could understand the use of autoextend if you had a maximum of say 1TB, but I just don't get it in this case
Alex
"The reason we use (dynamic) autoextend versus large static file sizes is just-in-time, as-needed file allocation leaves not-yet-used file space for other currently-in-need files"
I am assuming you have some method of checking free space on a daily basis ?
Therefore you know when a tablespace is running out of space and when you need to increase it.
What benefit is there of having the system do this for you, especially as you have a small maximum size as the limit ?
I could understand the use of autoextend if you had a maximum of say 1TB, but I just don't get it in this case
Alex
SantaMufasa
Technical User
Alex,
We allocate as many 2GB-maximum files as we anticipate necessary to accommodate the data for a tablespace. So if we anticipate needing (eventually) 40GB for an application, we allocate 20 autoextending 2GB-maximum files (which translate to 200MB initial allocation...20 10MB-initial files).
And, hey, if you have a good business/IT reason for doing it differently, then, by all means, go for it. I was just asserting a method to avoid horrendous pre-allocations of disk space that may NEVER get used if there was a mistake on initial estimates. Remember, it is not fun to try and whittle DOWN the size of a file once it's allocated.
And, no, we don't check free space on a daily basis; we run an automated report on Saturday nights that checks, and if we're within 20% of our file maximums for a tablespace, we add another 10mb, autoextending, 2GB-maximum datafile. So we manage it by exception.
Cheers,
Dave
We allocate as many 2GB-maximum files as we anticipate necessary to accommodate the data for a tablespace. So if we anticipate needing (eventually) 40GB for an application, we allocate 20 autoextending 2GB-maximum files (which translate to 200MB initial allocation...20 10MB-initial files).
And, hey, if you have a good business/IT reason for doing it differently, then, by all means, go for it. I was just asserting a method to avoid horrendous pre-allocations of disk space that may NEVER get used if there was a mistake on initial estimates. Remember, it is not fun to try and whittle DOWN the size of a file once it's allocated.
And, no, we don't check free space on a daily basis; we run an automated report on Saturday nights that checks, and if we're within 20% of our file maximums for a tablespace, we add another 10mb, autoextending, 2GB-maximum datafile. So we manage it by exception.
Cheers,
Dave
- Status
Similar threads
