Increasing Data Storage Capacity

When crawling a large number of documents, the default Oracle SES tablespaces may not be big enough to complete a crawl. You or your DBA can add more data files to the Oracle SES tablespaces in Oracle Database. If a crawl fails because of insufficient space, then you can add data files to the Oracle SES tablespaces (permanent tablespace, temporary tablespace, and the undo tablespace), and restart the crawl.

These tablespaces are used by Oracle SES:

SEARCH_DATA: Stores the crawled document metadata and cached documents. If the Preserve Document Cache option is enabled, then this tablespace requires a large data file to host all the crawled documents.
SEARCH_INDEX: Stores the SES index.
SEARCH_TEMP: A temporary tablespace that the crawler uses while processing the data.

Fusion tablespaces such as FUSION_TS_TX_IDX, an index for transactional data, may also require additional space.

See Also:

Oracle Secure Enterprise Search Administration API Guide for creating partitioned tablespaces.
Oracle Fusion Applications Installation Guide for managing Fusion tablespaces.

The following procedures use SQL*Plus, but you can use Enterprise Manager or another tool of your choosing.

To check the current tablespace usage for auto-extensible data files:

Open SQL*Plus and log in to Oracle Database as a privileged user, such as SYS or SYSTEM.
Query the DBA_DATA_FILES data dictionary view, using a SELECT statement like the following:
```
SELECT tablespace_name, SUM(bytes)/1024/1024 "Used Megabytes", SUM(maxbytes)/1024/1024 "Max Megabytes" FROM dba_data_files WHERE tablespace_name IN ('SEARCH_DATA', 'SEARCH_INDEX') GROUP BY tablespace_name;
```
The query returns the number of megabytes currently used and the maximum number of megabytes available in autoextensible SES tablespaces, as shown in the following example. When the number of used megabytes approaches the maximum, add more data files. By adding more space before the next crawl, you can avoid having the crawl fail for lack of space.
```
TABLESPACE_NAME      Used Megabytes Max Megabytes
-------------------- -------------- -------------
SEARCH_DATA                     420    32767.9844
SEARCH_INDEX                    120    32767.9844
```

Auto-extensible data files are typically used for Oracle SES, but if you have fixed-size data files, then query the DBA_FREE_SPACE data dictionary view.

To add data files to the Oracle SES permanent tablespace:

Open SQL*Plus and log in to Oracle Database as a privileged user, such as SYS or SYSTEM.

Query the DBA_DATA_FILES data dictionary view for the full path to the SEARCH_DATA and SEARCH_INDEX tablespaces:

SELECT tablespace_name, file_name FROM dba_data_files
   WHERE tablespace_name IN ('SEARCH_DATA','SEARCH_INDEX');

Issue ALTER TABLESPACE commands like the one shown here for each additional data file. Replace tablespace with SEARCH_DATA or SEARCH_INDEX, path/filename with the name of the new data file, and bytes with the desired initial size.
```
ALTER TABLESPACE tablespace ADD DATAFILE
   'path/filename.dbf' SIZE bytes
   AUTOEXTEND ON MAXSIZE UNLIMITED;
```
Query DBA_DATA_FILES again to list the new data files. (Optional)

To add temp files to the Oracle SES temporary tablespace:

In SQL*Plus, query the DBA_TEMP_FILES data dictionary view for the full path to the SEARCH_TEMP tablespace:
```
SELECT tablespace_name, file_name FROM dba_temp_files
   WHERE tablespace_name='SEARCH_TEMP';
```
Issue ALTER TABLESPACE commands like the one shown here for each additional temp file. Replace tablespace with SEARCH_TEMP, path/filename with the name of the new temp file, and bytes with the desired initial size.
```
ALTER TABLESPACE SEARCH_TEMP ADD TEMPFILE
   'path/filename.dbf' SIZE bytes
   AUTOEXTEND ON MAXSIZE UNLIMITED;
```
Query DBA_TEMP_FILES again to list the new temp files. (Optional)

To add data files to the Oracle SES undo tablespace:

Open SQL*Plus and log in to Oracle Database as a privileged user, such as SYS or SYSTEM.

Find the undo tablespace name:

SHOW parameter undo_tablespace;

This will return the information similar to the one shown in the following example:

NAME                 TYPE           VALUE
-------------------- -------------- -------------
undo_tablespace      string         UNDOTBS1

Add a data file to the undo tablespace:

ALTER TABLESPACE UNDOTBS1 ADD DATAFILE
  '/var/opt/oracle/data/undo1_dbf' SIZE 100M
   AUTOEXTEND ON;

To restart a failed crawl:

Open the Oracle SES Administration GUI and log in.
Click the Schedules subtab on the Home page.
Select the failed schedule, then click the Start button.

Example: Adding Data Files to Oracle SES Tablespaces

This example adds two data files to SEARCH_DATA, a data file to SEARCH_INDEX, and a temp file to SEARCH_TEMP.

Open SQL*Plus:
```
sqlplus / as sysdba
```

Obtain the full paths to the Oracle SES tablespaces:

SELECT tablespace_name, file_name FROM dba_data_files
   WHERE tablespace_name IN ('SEARCH_DATA','SEARCH_INDEX');

TABLESPACE_NAME FILE_NAME
--------------- --------------------------------------------------
SEARCH_DATA     /oracle/product/oradata/ses/SEARCH_DATA_1.dbf
SEARCH_INDEX    /oracle/product/oradata/ses/SEARCH_INDEX_1.dbf

SELECT tablespace_name, file_name FROM dba_temp_files
   WHERE tablespace_name='SEARCH_TEMP';

TABLESPACE_NAME FILE_NAME
--------------- --------------------------------------------------
SEARCH_TEMP     /oracle/product/oradata/ses/SEARCH_TEMP_1.dbf

Add two data files to the SEARCH_DATA tablespace:

ALTER TABLESPACE search_data ADD DATAFILE
   '/oracle/product/oradata/ses/SEARCH_DATA_2.dbf' SIZE 10M
   AUTOEXTEND ON MAXSIZE UNLIMITED;

Tablespace altered.
 
ALTER TABLESPACE search_data ADD DATAFILE 
   '/oracle/product/oradata/ses/SEARCH_DATA_3.dbf' SIZE 10M
   AUTOEXTEND ON MAXSIZE UNLIMITED;

Tablespace altered.

Add a data file to the SEARCH_INDEX tablespace:

ALTER TABLESPACE search_index ADD DATAFILE
   '/oracle/product/oradata/ses/SEARCH_INDEX_2.dbf' SIZE 10M
   AUTOEXTEND ON MAXSIZE UNLIMITED;

Tablespace altered.

Add a temp file to the SEARCH_TEMP tablespace:

ALTER TABLESPACE search_index ADD TEMPFILE
   '/oracle/product/oradata/ses/SEARCH_TEMP_2.dbf' SIZE 10M
   AUTOEXTEND ON MAXSIZE UNLIMITED;

Tablespace altered.

Query the data dictionary for the new data files:

SELECT tablespace_name, file_name FROM dba_data_files
   WHERE tablespace_name IN ('SEARCH_DATA','SEARCH_INDEX')
   ORDER BY tablespace_name;

TABLESPACE_NAME FILE_NAME
--------------- ----------------------------------------------------------
SEARCH_DATA     /oracle/product/oradata/ses/SEARCH_DATA_1.dbf
SEARCH_DATA     /oracle/product/oradata/ses/SEARCH_DATA_2.dbf
SEARCH_DATA     /oracle/product/oradata/ses/SEARCH_DATA_3.dbf
SEARCH_INDEX    /oracle/product/oradata/ses/SEARCH_INDEX_1.dbf
SEARCH_INDEX    /oracle/product/oradata/ses/SEARCH_INDEX_2.dbf

SELECT tablespace_name, file_name FROM dba_temp_files
   WHERE tablespace_name='SEARCH_TEMP';

TABLESPACE_NAME FILE_NAME
--------------- ----------------------------------------------------------
SEARCH_TEMP     /oracle/product/oradata/ses/SEARCH_TEMP_1.dbf
SEARCH_TEMP     /oracle/product/oradata/ses/SEARCH_TEMP_2.dbf