Database Reference
In-Depth Information
DISpLaYING OraCLe errOr MeSSaGeS aND aCtIONS
you can use the
oerr
utility to quickly display the cause of an error and simple instructions on what actions to
take; for example,
$ oerr ora 01653
here is the output for this example:
01653, 00000, "unable to extend table %s.%s by %s in tablespace %s"
// *Cause: Failed to allocate an extent of the required number of blocks for
// a table segment in the tablespace indicated.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
// files to the tablespace indicated.
the
oerr
utility's output gives you a fast and easy way to triage problems. if the information provided isn't
enough, then Google is a good second option.
Altering Tablespace Size
When you've determined which data file you want to resize, first make sure you have enough disk space to increase
the size of the data file on the mount point on which the data file exists:
$ df -h | sort
Use the
ALTER DATABASE DATAFILE ... RESIZE
command to increase the data file's size. This example resizes
the data file to 1GB:
SQL> alter database datafile '/u01/dbfile/O12C/users01.dbf' resize 1g;
If you don't have space on an existing mount point to increase the size of a data file, then you must add a data file.
To add a data file to an existing tablespace, use the
ALTER TABLESPACE ... ADD DATAFILE
statement:
SQL> alter tablespace users add datafile '/u02/dbfile/O12C/users02.dbf' size 100m;
With bigfile tablespaces, you have the option of using the
ALTER TABLESPACE
statement to resize the data file. This
works because only one data file can be associated with a bigfile tablespace:
SQL> alter tablespace inv_big_data resize 1P;
Resizing data files can be a daily task when you're managing databases with heavy transaction loads. Increasing
the size of an existing data file allows you to add space to a tablespace without adding more data files. If there isn't
enough disk space left on the storage device that contains an existing data file, you can add a data file in a different
location to an existing tablespace.
To add space to a temporary tablespace, first query the
V$TEMPFILE
view to verify the current size and location of
temporary data files:
SQL> select name, bytes from v$tempfile;