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;
 
Search WWH ::




Custom Search