Resolving ORA-01653, ORA-01658 for tablespace APPS_TS_TX_DATA

Posted by Vasudev Seeram on November 19, 2014 Leave a comment (0) Go to comments

Problem: Users are getting ORA-01653 or ORA-01658 for tablespace APPS_TS_TX_DATA.

Solution: Add another datafile to the APPS_TS_TX_DATA tablespace using the alter tablespace add datafile command:

alter tablespace APPS_TS_TX_DATA add datafile
 '/path/to/data/files/db/apps_st/data/a_txn_data0x.dbf' size 1950M;

1. First, source the database tier environment and then log on to SQL*Plus as the sys user. Check DBA_DATA_FILES for the existing datafiles in the APPS_TS_TX_DATA tablespace:

bash-3.00$ cd db/tech_st/11.2.0/
bash-3.00$ . TEST_six.env
bash-3.00$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 1 16:03:25 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select file_name, bytes/1024/1024, autoextensible from dba_data_files where tablespace_name = 'APPS_TS_TX_DATA' order by 1;

FILE_NAME
------------------------------------------
BYTES/1024/1024 AUT
--------------- ---
/u01/test/db/apps_st/data/a_txn_data01.dbf
           1800 NO

/u01/test/db/apps_st/data/a_txn_data02.dbf
        1862.25 NO

/u01/test/db/apps_st/data/a_txn_data03.dbf
           1800 NO


FILE_NAME
------------------------------------------
BYTES/1024/1024 AUT
--------------- ---
/u01/test/db/apps_st/data/a_txn_data04.dbf
            646 NO

/u01/test/db/apps_st/data/a_txn_data05.dbf
           1950 NO

/u01/test/db/apps_st/data/a_txn_data06.dbf
           1950 NO


FILE_NAME
------------------------------------------
BYTES/1024/1024 AUT
--------------- ---
/u01/test/db/apps_st/data/a_txn_data07.dbf
           1950 NO


7 rows selected.

SQL>

2. Add another datafile in the same location for the APPS_TS_TX_DATA tablespace:

SQL> alter tablespace APPS_TS_TX_DATA add datafile 
'/u01/test/db/apps_st/data/a_txn_data08.dbf' 
size 1950M;

Tablespace altered.

SQL>

3. Verify that the datafile was added to the APPS_TS_TX_DATA tablespace:

SQL> select file_name, bytes/1024/1024, autoextensible from dba_data_files where tablespace_name = 'APPS_TS_TX_DATA' order by 1;

FILE_NAME
------------------------------------------
BYTES/1024/1024 AUT
--------------- ---
/u01/test/db/apps_st/data/a_txn_data01.dbf
           1800 NO

/u01/test/db/apps_st/data/a_txn_data02.dbf
        1862.25 NO

/u01/test/db/apps_st/data/a_txn_data03.dbf
           1800 NO


FILE_NAME
------------------------------------------
BYTES/1024/1024 AUT
--------------- ---
/u01/test/db/apps_st/data/a_txn_data04.dbf
            646 NO

/u01/test/db/apps_st/data/a_txn_data05.dbf
           1950 NO

/u01/test/db/apps_st/data/a_txn_data06.dbf
           1950 NO


FILE_NAME
------------------------------------------
BYTES/1024/1024 AUT
--------------- ---
/u01/test/db/apps_st/data/a_txn_data07.dbf
           1950 NO

/u01/test/db/apps_st/data/a_txn_data08.dbf
           1950 NO


8 rows selected.

SQL>