Resolving ORA-01691 for tablespace APPS_TS_MEDIA

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

Problem: When uploading attachments to EBS, the following error appears:

ORA-01691: unable to extend lob segment APPLSYS.SYS_LOB000000000000000123043$$ 
by 16 in tablespace APPS_TS_MEDIA

Solution: Add another datafile to the APPS_TS_MEDIA tablespace using the command:

alter tablespace APPS_TS_MEDIA add datafile
 '/path/to/data/files/db/apps_st/data/a_mediaxx.dbf' size 4096M;

To find the name of the last datafile added, 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:

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

FILE_NAME
---------------------------------------
BYTES/1024/1024 MAXBYTES/1024/1024 AUT
--------------- ------------------ ---
/u01/test/db/apps_st/data/a_media01.dbf
           1373                  0 NO

/u01/test/db/apps_st/data/a_media02.dbf
           2048                  0 NO

/u01/test/db/apps_st/data/a_media03.dbf
           2048                  0 NO


FILE_NAME
---------------------------------------
BYTES/1024/1024 MAXBYTES/1024/1024 AUT
--------------- ------------------ ---
/u01/test/db/apps_st/data/a_media04.dbf
           2048                  0 NO

/u01/test/db/apps_st/data/a_media05.dbf
           2048                  0 NO

/u01/test/db/apps_st/data/a_media06.dbf
           2048                  0 NO


FILE_NAME
---------------------------------------
BYTES/1024/1024 MAXBYTES/1024/1024 AUT
--------------- ------------------ ---
/u01/test/db/apps_st/data/a_media07.dbf
           2048                  0 NO

/u01/test/db/apps_st/data/a_media08.dbf
           2048                  0 NO

/u01/test/db/apps_st/data/a_media09.dbf
           2048                  0 NO


FILE_NAME
---------------------------------------
BYTES/1024/1024 MAXBYTES/1024/1024 AUT
--------------- ------------------ ---
/u01/test/db/apps_st/data/a_media10.dbf
           4096               4096 YES

/u01/test/db/apps_st/data/a_media11.dbf
           4096               4096 YES

/u01/test/db/apps_st/data/a_media12.dbf
           4096               4096 YES


FILE_NAME
---------------------------------------
BYTES/1024/1024 MAXBYTES/1024/1024 AUT
--------------- ------------------ ---
/u01/test/db/apps_st/data/a_media13.dbf
           4096               4096 YES

/u01/test/db/apps_st/data/a_media14.dbf
           4096               4096 YES

/u01/test/db/apps_st/data/a_media15.dbf
           4096               4096 YES


FILE_NAME
---------------------------------------
BYTES/1024/1024 MAXBYTES/1024/1024 AUT
--------------- ------------------ ---
/u01/test/db/apps_st/data/a_media16.dbf
          10240              10240 YES


16 rows selected.

SQL>

2. Next, add another datafile in the same directory for the APPS_TS_MEDIA tablespace:

alter tablespace APPS_TS_MEDIA add datafile 
'/u01/test/db/apps_st/data/a_media17.dbf' 
size 4096M;

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

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

FILE_NAME
----------------------------------------
BYTES/1024/1024 MAXBYTES/1024/1024 AUT
--------------- ------------------ ---
/u01/test/db/apps_st/data/a_media01.dbf
           1373                  0 NO

/u01/test/db/apps_st/data/a_media02.dbf
           2048                  0 NO

/u01/test/db/apps_st/data/a_media03.dbf
           2048                  0 NO


FILE_NAME
----------------------------------------
BYTES/1024/1024 MAXBYTES/1024/1024 AUT
--------------- ------------------ ---
/u01/test/db/apps_st/data/a_media04.dbf
           2048                  0 NO

/u01/test/db/apps_st/data/a_media05.dbf
           2048                  0 NO

/u01/test/db/apps_st/data/a_media06.dbf
           2048                  0 NO


FILE_NAME
----------------------------------------
BYTES/1024/1024 MAXBYTES/1024/1024 AUT
--------------- ------------------ ---
/u01/test/db/apps_st/data/a_media07.dbf
           2048                  0 NO

/u01/test/db/apps_st/data/a_media08.dbf
           2048                  0 NO

/u01/test/db/apps_st/data/a_media09.dbf
           2048                  0 NO


FILE_NAME
----------------------------------------
BYTES/1024/1024 MAXBYTES/1024/1024 AUT
--------------- ------------------ ---
/u01/test/db/apps_st/data/a_media10.dbf
           4096               4096 YES

/u01/test/db/apps_st/data/a_media11.dbf
           4096               4096 YES

/u01/test/db/apps_st/data/a_media12.dbf
           4096               4096 YES


FILE_NAME
----------------------------------------
BYTES/1024/1024 MAXBYTES/1024/1024 AUT
--------------- ------------------ ---
/u01/test/db/apps_st/data/a_media13.dbf
           4096               4096 YES

/u01/test/db/apps_st/data/a_media14.dbf
           4096               4096 YES

/u01/test/db/apps_st/data/a_media15.dbf
           4096               4096 YES


FILE_NAME
----------------------------------------
BYTES/1024/1024 MAXBYTES/1024/1024 AUT
--------------- ------------------ ---
/u01/test/db/apps_st/data/a_media16.dbf
          10240              10240 YES

/u01/test/db/apps_st/data/a_media17.dbf
           4096                  0 NO


17 rows selected.

SQL>