Enabling and Disabling Automatic Extension for a Datafile:
You can create datafiles or alter existing datafiles so that they automatically increase in size when more space is needed in the database. The file size increases in specified increments up to a specified maximum.
Setting your datafiles to extend automatically provides these advantages:
- Reduces the need for immediate intervention when a tablespace runs out of space
- Ensures applications will not halt or be suspended because of failures to allocate extents
To determine whether a datafile is auto-extensible, query the
DBA_DATA_FILES
view and examine the AUTOEXTENSIBLE
column.
SQL> select tablespace_name ,autoextensible from dba_data_files;
TABLESPACE_NAME AUT
------------------------------ ---
SYSTEM YES
SYSAUX YES
UNDOTBS1 YES
USERS YES
TABLESPACE_NAME AUT
------------------------------ ---
SYSTEM YES
SYSAUX YES
UNDOTBS1 YES
USERS YES
You can specify automatic file extension by specifying an
AUTOEXTEND ON
clause when you create datafiles using the following SQL statements:CREATE
DATABASE
ALTER DATABASE
CREATE
TABLESPACE
ALTER
TABLESPACE
You can enable or disable automatic file extension for existing datafiles, or manually resize a datafile, using the
ALTER
DATABASE
statement. For a bigfile tablespace, you are able to perform these operations using the ALTER
TABLESPACE
statement.
The following example enables automatic extension for a datafile added to the
users
tablespace:ALTER TABLESPACE users ADD DATAFILE '/u02/oracle/rbdb1/users03.dbf' SIZE 10M AUTOEXTEND ON NEXT 512K MAXSIZE 250M;
The value of
NEXT
is the minimum size of the increments added to the file when it extends. The value of MAXSIZE
is the maximum size to which the file can automatically extend.
The next example disables the automatic extension for the datafile.
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' AUTOEXTEND OFF;
Manually Resizing a Datafile
You can manually increase or decrease the size of a datafile using theALTER DATABASE
statement. This enables you to add more space to your database without adding more datafiles. This is beneficial if you are concerned about reaching the maximum number of datafiles allowed in your database.For a bigfile tablespace you can use theALTER
TABLESPACE
statement to resize a datafile. You are not allowed to add a datafile to a bigfile tablespace.Manually reducing the sizes of datafiles enables you to reclaim unused space in the database. This is useful for correcting errors in estimates of space requirements.In the next example, assume that the datafile /u02/oracle/rbdb1/users03.dbf has extended up to 250M. However, because its tablespace now stores smaller objects, the datafile can be reduced in size.The following statement decreases the size of datafile /u02/oracle/rbdb1/users03.dbf:ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' RESIZE 250M;
Best Regards.
No comments:
Post a Comment