DBA Notes: 2011/09/09

Cheng Li

(Database: Oracle 10g, OS: AIX 5L)

How to handle ORA-01654

From daily alerts, we are reported an ORA-01645 oracle error, as following:

18:43:32 java.sql.SQLException: ORA-01654: unable to extend index OIS.IDX_IMON_HIST_DWFMAPP2 by 512 in tablespace OISDAT

18:43:33 java.sql.SQLException: ORA-01653: unable to extend table OIS.IMON_HIST_DWFMAPP1 by 512 in tablespace OISDAT

………………………….

        This is a typical issue for DBA daily operation.

1)    Adding new data file to tablespace

Specify ADD to add to the tablespace a datafile or tempfile specified by file_specification. Use the datafile_tempfile_spec form. of file_specification (see file_specification ) to list regular datafiles and tempfiles in an operating system file system or to list Automatic Storage Management disk group files.

For locally managed temporary tablespaces, this is the only clause you can specify at any time.

If you omit file_specification, then Oracle Database creates an Oracle-managed file of 100M with AUTOEXTEND enabled.

You can add a datafile or tempfile to a locally managed tablespace that is online or to a dictionary managed tablespace that is online or offline. Ensure the file is not in use by another database.

Adding a Datafile: Example

The following statement adds a datafile to the tablespace.

ALTER TABLESPACE OISDAT

    ADD DATAFILE 'oisdat_14.dbf'

    SIZE 5160M

    AUTOEXTEND ON

    NEXT 100M

    MAXSIZE 10240M;

Adding an Oracle-managed Datafile: Example

The following example adds an Oracle-managed datafile to the OISDAT tablespace:

ALTER TABLESPACE OISDAT ADD DATAFILE;

2)      Extended data file MAXSIZE. In this case we enlarge the data file size limitation from 5000MB to 10240MB. 

Be noted, we need to alter database for change existing data file size.

ALTER DATABASE
    DATAFILE '/disk2/oradata/oisdb/oisdat_13.dbf' AUTOEXTEND
    ON MAXSIZE  10240M