# Performing common database tasks for Oracle DB instances
Database tasks

Following, you can find how to perform certain common DBA tasks related to databases on your Amazon RDS DB instances running Oracle. To deliver a managed service experience, Amazon RDS doesn't provide shell access to DB instances. Amazon RDS also restricts access to some system procedures and tables that require advanced privileges. 

**Topics**
+ [

# Changing the global name of a database
](Appendix.Oracle.CommonDBATasks.RenamingGlobalName.md)
+ [

# Working with tablespaces in RDS for Oracle
](Appendix.Oracle.CommonDBATasks.TablespacesAndDatafiles.md)
+ [

# Working with tempfiles in RDS for Oracle
](Appendix.Oracle.CommonDBATasks.using-tempfiles.md)
+ [

# Resizing tablespaces, data files, and tempfiles in RDS for Oracle
](Appendix.Oracle.CommonDBATasks.ResizeTempSpaceReadReplica.md)
+ [

# Moving data between storage volumes in RDS for Oracle
](Appendix.Oracle.CommonDBATasks.MovingDataBetweenVolumes.md)
+ [

# Working with external tables in RDS for Oracle
](Appendix.Oracle.CommonDBATasks.External_Tables.md)

# Changing the global name of a database
Changing the global name of a database

To change the global name of a database, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.rename_global_name`. The `rename_global_name` procedure has the following parameters. 


****  

| Parameter name | Data type | Default | Required | Description | 
| --- | --- | --- | --- | --- | 
|  `p_new_global_name`  |  varchar2  |  —  |  Yes  |  The new global name for the database.  | 

The database must be open for the name change to occur. For more information about changing the global name of a database, see [ALTER DATABASE](http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_1004.htm#SQLRF52547) in the Oracle documentation. 

The following example changes the global name of a database to `new_global_name`.

```
EXEC rdsadmin.rdsadmin_util.rename_global_name(p_new_global_name => 'new_global_name');
```

# Working with tablespaces in RDS for Oracle
Working with Oracle tablespaces

You can use tablespaces with RDS for Oracle, which is a logical storage unit that stores the database's data.

**Important**  
If your DB instance has replicas, we recommend using parameter group settings instead of session-level changes to manage default file locations. Session-level changes to default file locations in the primary instance are not automatically reflected in the replicas. Using parameter group settings ensures consistent file locations across your primary and replica instances.

**Topics**
+ [

## Specifying database file locations in RDS for Oracle
](#Appendix.Oracle.CommonDBATasks.DatabaseFileLocations)
+ [

## Creating and sizing tablespaces in RDS for Oracle
](#Appendix.Oracle.CommonDBATasks.CreatingTablespacesAndDatafiles)
+ [

## Creating tablespaces on additional storage volumes in RDS for Oracle
](#Appendix.Oracle.CommonDBATasks.CreatingTablespacesWithFileLocations)
+ [

## Setting the default tablespace in RDS for Oracle
](#Appendix.Oracle.CommonDBATasks.SettingDefaultTablespace)
+ [

## Setting the default temporary tablespace in RDS for Oracle
](#Appendix.Oracle.CommonDBATasks.SettingDefTempTablespace)
+ [

## Creating a temporary tablespace on the instance store
](#Appendix.Oracle.CommonDBATasks.creating-tts-instance-store)

## Specifying database file locations in RDS for Oracle
Managing database file locations

RDS for Oracle uses Oracle Managed Files (OMF) to name database files. When you create database files the database derives the setting based on the current setting of the `DB_CREATE_FILE_DEST` initialization parameter.

The default value of the `DB_CREATE_FILE_DEST` initialization parameter is `/rdsdbdata/db` for standalone databases and `/rdsdbdata/db/pdb` for containerized (CDB/MT) architecture. If your DB instance has additional storage volumes, then you can set `DB_CREATE_FILE_DEST` to your volume locations. For example, if your instance has a volume mounted on `/rdsdbdata/db`, you can set `DB_CREATE_FILE_DEST` to this value.

You can modify the `DB_CREATE_FILE_DEST` parameter at either the session level or Oracle database instance level.

### Modifying DB\$1CREATE\$1FILE\$1SET at the instance level
Instance level

To modify the parameter at the instance level, update the parameter in the parameter group assigned to your DB instance and apply it. For more information, see [RDS for Oracle initialization parameters](Oracle.Concepts.FeatureSupport.Parameters.md) and [Modifying parameters in a DB parameter group in Amazon RDS](USER_WorkingWithParamGroups.Modifying.md).

### Modifying DB\$1CREATE\$1FILE\$1DEST at the session level
Session level

You can modify the parameter at the session level by executing an `ALTER SESSION` statement. This approach is useful when you want to create database files in a specific location for a particular session without affecting the entire instance.

The following example shows how to check the current parameter value and modify it for the session:

```
SHOW PARAMETER db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /rdsdbdata/db

ALTER SESSION SET db_create_file_dest = '/rdsdbdata2/db';

Session altered.

SHOW PARAMETER db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /rdsdbdata2/db
```

## Creating and sizing tablespaces in RDS for Oracle
Creating and sizing tablespaces

When you create tablespaces, the database creates the data files in the storage volume specified by the `DB_CREATE_FILE_DEST` initialization parameter at the time of creation. By default, if you don't specify a data file size, tablespaces are created with the default of `AUTOEXTEND ON`, and no maximum size. In the following example, the tablespace *users1* is autoextensible.

```
CREATE TABLESPACE users1;
```

Because of these default settings, tablespaces can grow to consume all allocated storage. We recommend that you specify an appropriate maximum size on permanent and temporary tablespaces, and that you carefully monitor space usage. 

The following example creates a tablespace named *users2* with a starting size of 1 gigabyte. Because a data file size is specified, but `AUTOEXTEND ON` isn't specified, the tablespace isn't autoextensible.

```
CREATE TABLESPACE users2 DATAFILE SIZE 1G;
```

The following example creates a tablespace named *users3* with a starting size of 1 gigabyte, autoextend turned on, and a maximum size of 10 gigabytes.

```
CREATE TABLESPACE users3 DATAFILE SIZE 1G AUTOEXTEND ON MAXSIZE 10G;
```

The following example creates a temporary tablespace named *temp01*.

```
CREATE TEMPORARY TABLESPACE temp01;
```

You can resize a bigfile tablespace by using `ALTER TABLESPACE`. You can specify the size in kilobytes (K), megabytes (M), gigabytes (G), or terabytes (T). The following example resizes a bigfile tablespace named *users\$1bf* to 200 MB.

```
ALTER TABLESPACE users_bf RESIZE 200M;
```

The following example adds an additional data file to a smallfile tablespace named *users\$1sf*.

```
ALTER TABLESPACE users_sf ADD DATAFILE SIZE 100000M AUTOEXTEND ON NEXT 250m MAXSIZE UNLIMITED;
```

## Creating tablespaces on additional storage volumes in RDS for Oracle
Creating tablespaces on additional storage volumes

To create a tablespace on an additional storage volume, modify the `DB_CREATE_FILE_DEST` parameter to the volume location. The following example sets the file location to `/rdsdbdata2/db`.

```
ALTER SESSION SET db_create_file_dest = '/rdsdbdata2/db';

Session altered.
```

In the following example, you create a tablespace on the additional volume `/rdsdbdata2/db`.

```
CREATE TABLESPACE new_tablespace DATAFILE SIZE 10G;

Tablespace created.

SELECT tablespace_name,file_id,file_name FROM dba_data_files
WHERE tablespace_name = 'NEW_TABLESPACE';

TABLESPACE_NAME              FILE_ID FILE_NAME
------------------------- ---------- --------------------------------------------------------------------------------
NEW_TABLESPACE                     7 /rdsdbdata2/db/ORCL_A/datafile/o1_mf_newtable_a123b4c5_.dbf
```

To create a smallfile tablespace and spread its data files across different storage volumes, add data files to the tablespace after you create it. In the following example, you create a tablespace with the data files in the default location of `/rdsdbdata/db`. Then you set the default destination to `/rdsdbdata/db2`. When you add a data file to your newly created tablespace, the database stores the file in `/rdsdbdata/db2`.

```
ALTER SESSION SET db_create_file_dest = '/rdsdbdata/db';

Session altered.

CREATE SMALLFILE TABLESPACE smalltbs DATAFILE SIZE 10G;

Tablespace created.

SELECT tablespace_name,file_id,file_name FROM dba_data_files
WHERE tablespace_name = 'SMALLTBS';

TABLESPACE_NAME              FILE_ID FILE_NAME
------------------------- ---------- --------------------------------------------------------------------------------
SMALLTBS                           8 /rdsdbdata/db/ORCL_A/datafile/o1_mf_smalltbs_n563yryk_.dbf

ALTER SESSION SET db_create_file_dest = '/rdsdbdata2/db';

Session altered.

ALTER TABLESPACE smalltbs ADD DATAFILE SIZE 10G;

Tablespace altered.

SELECT tablespace_name,file_id,file_name FROM dba_data_files
WHERE tablespace_name = 'SMALLTBS';

TABLESPACE_NAME              FILE_ID FILE_NAME
------------------------- ---------- --------------------------------------------------------------------------------
SMALLTBS                           8 /rdsdbdata/db/ORCL_A/datafile/o1_mf_smalltbs_n563yryk_.dbf
SMALLTBS                           9 /rdsdbdata2/db/ORCL_A/datafile/o1_mf_smalltbs_n564004g_.dbf
```

## Setting the default tablespace in RDS for Oracle
Setting the default tablespace

To set the default tablespace, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.alter_default_tablespace`. The `alter_default_tablespace` procedure has the following parameters. 


****  

| Parameter name | Data type | Default | Required | Description | 
| --- | --- | --- | --- | --- | 
|  `tablespace_name`  |  varchar  |  —  |  Yes  |  The name of the default tablespace.  | 

The following example sets the default tablespace to *users2*: 

```
EXEC rdsadmin.rdsadmin_util.alter_default_tablespace(tablespace_name => 'users2');
```

## Setting the default temporary tablespace in RDS for Oracle
Setting the default temporary tablespace

To set the default temporary tablespace, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.alter_default_temp_tablespace`. The `alter_default_temp_tablespace` procedure has the following parameters. 


****  

| Parameter name | Data type | Default | Required | Description | 
| --- | --- | --- | --- | --- | 
|  `tablespace_name`  |  varchar  |  —  |  Yes  |  The name of the default temporary tablespace.  | 

The following example sets the default temporary tablespace to *temp01*. 

```
EXEC rdsadmin.rdsadmin_util.alter_default_temp_tablespace(tablespace_name => 'temp01');
```

## Creating a temporary tablespace on the instance store
Creating temporary tablespaces

To create a temporary tablespace on the instance store, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.create_inst_store_tmp_tblspace`. The `create_inst_store_tmp_tblspace` procedure has the following parameters. 


****  

| Parameter name | Data type | Default | Required | Description | 
| --- | --- | --- | --- | --- | 
|  `p_tablespace_name`  |  varchar  |  —  |  Yes  |  The name of the temporary tablespace.  | 

The following example creates the temporary tablespace *temp01* in the instance store. 

```
EXEC rdsadmin.rdsadmin_util.create_inst_store_tmp_tblspace(p_tablespace_name => 'temp01');
```

**Important**  
When you run `rdsadmin_util.create_inst_store_tmp_tblspace`, the newly created temporary tablespace is not automatically set as the default temporary tablespace. To set it as the default, see [Setting the default temporary tablespace in RDS for Oracle](#Appendix.Oracle.CommonDBATasks.SettingDefTempTablespace).

For more information, see [Storing temporary data in an RDS for Oracle instance store](CHAP_Oracle.advanced-features.instance-store.md).

# Working with tempfiles in RDS for Oracle
Working with Oracle tempfiles

## Adding a tempfile to the instance store on a read replica


When you create a temporary tablespace on a primary DB instance, the read replica doesn't create tempfiles. Assume that an empty temporary tablespace exists on your read replica for either of the following reasons:
+ You dropped a tempfile from the tablespace on your read replica. For more information, see [Dropping tempfiles on a read replica](Appendix.Oracle.CommonDBATasks.dropping-tempfiles-replica.md).
+ You created a new temporary tablespace on the primary DB instance. In this case, RDS for Oracle synchronizes the metadata to the read replica.

You can add a tempfile to the empty temporary tablespace, and store the tempfile in the instance store. To create a tempfile in the instance store, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.add_inst_store_tempfile`. You can use this procedure only on a read replica. The procedure has the following parameters.


****  

| Parameter name | Data type | Default | Required | Description | 
| --- | --- | --- | --- | --- | 
|  `p_tablespace_name`  |  varchar  |  —  |  Yes  |  The name of the temporary tablespace on your read replica.  | 

In the following example, the empty temporary tablespace *temp01* exists on your read replica. Run the following command to create a tempfile for this tablespace, and store it in the instance store.

```
EXEC rdsadmin.rdsadmin_util.add_inst_store_tempfile(p_tablespace_name => 'temp01');
```

For more information, see [Storing temporary data in an RDS for Oracle instance store](CHAP_Oracle.advanced-features.instance-store.md).

# Dropping tempfiles on a read replica


You can't drop an existing temporary tablespace on a read replica. You can change the tempfile storage on a read replica from Amazon EBS to the instance store, or from the instance store to Amazon EBS. To achieve these goals, do the following:

1. Drop the current tempfiles in the temporary tablespace on the read replica.

1. Create new tempfiles on different storage.

To drop the tempfiles, use the Amazon RDS procedure `rdsadmin.rdsadmin_util. drop_replica_tempfiles`. You can use this procedure only on read replicas. The `drop_replica_tempfiles` procedure has the following parameters.


****  

| Parameter name | Data type | Default | Required | Description | 
| --- | --- | --- | --- | --- | 
|  `p_tablespace_name`  |  varchar  |  —  |  Yes  |  The name of the temporary tablespace on your read replica.  | 

Assume that a temporary tablespace named *temp01* resides in the instance store on your read replica. Drop all tempfiles in this tablespace by running the following command.

```
EXEC rdsadmin.rdsadmin_util.drop_replica_tempfiles(p_tablespace_name => 'temp01');
```

For more information, see [Storing temporary data in an RDS for Oracle instance store](CHAP_Oracle.advanced-features.instance-store.md).

# Resizing tablespaces, data files, and tempfiles in RDS for Oracle
Resizing Oracle tablespaces and files

By default, Oracle tablespaces are created with auto-extend turned on and no maximum size. Because of these default settings, tablespaces can sometimes grow too large. We recommend that you specify an appropriate maximum size on permanent and temporary tablespaces, and that you carefully monitor space usage.

## Resizing permanent tablespaces


To resize a permanent tablespace in an RDS for Oracle DB instance, use any of the following Amazon RDS procedures:
+ `rdsadmin.rdsadmin_util.resize_datafile`
+ `rdsadmin.rdsadmin_util.autoextend_datafile`

The `resize_datafile` procedure has the following parameters.


****  

| Parameter name | Data type | Default | Required | Description | 
| --- | --- | --- | --- | --- | 
|  `p_data_file_id`  |  number  |  —  |  Yes  |  The identifier of the data file to resize.  | 
|  `p_size`  |  varchar2  |  —  |  Yes  |  The size of the data file. Specify the size in bytes (the default), kilobytes (K), megabytes (M), or gigabytes (G).   | 

The `autoextend_datafile` procedure has the following parameters.


****  

| Parameter name | Data type | Default | Required | Description | 
| --- | --- | --- | --- | --- | 
|  `p_data_file_id`  |  number  |  —  |  Yes  |  The identifier of the data file to resize.  | 
|  `p_autoextend_state`  |  varchar2  |  —  |  Yes  |  The state of the autoextension feature. Specify `ON` to extend the data file automatically and `OFF` to turn off autoextension.   | 
|  `p_next`  |  varchar2  |  —  |  No  |  The size of the next data file increment. Specify the size in bytes (the default), kilobytes (K), megabytes (M), or gigabytes (G).  | 
|  `p_maxsize`  |  varchar2  |  —  |  No  |  The maximum disk space allowed for automatic extension. Specify the size in bytes (the default), kilobytes (K), megabytes (M), or gigabytes (G). You can specify `UNLIMITED` to remove the file size limit.  | 

The following example resizes data file 4 to 500 MB.

```
EXEC rdsadmin.rdsadmin_util.resize_datafile(4,'500M');
```

The following example turns off autoextension for data file 4. It also turns on autoextension for data file 5, with an increment of 128 MB and no maximum size.

```
EXEC rdsadmin.rdsadmin_util.autoextend_datafile(4,'OFF');
EXEC rdsadmin.rdsadmin_util.autoextend_datafile(5,'ON','128M','UNLIMITED');
```

## Resizing temporary tablespaces


To resize a temporary tablespaces in an RDS for Oracle DB instance, including a read replica, use any of the following Amazon RDS procedures:
+ `rdsadmin.rdsadmin_util.resize_temp_tablespace`
+ `rdsadmin.rdsadmin_util.resize_tempfile`
+ `rdsadmin.rdsadmin_util.autoextend_tempfile`

The `resize_temp_tablespace` procedure has the following parameters.


****  

| Parameter name | Data type | Default | Required | Description | 
| --- | --- | --- | --- | --- | 
|  `p_temp_tablespace_name`  |  varchar2  |  —  |  Yes  |  The name of the temporary tablespace to resize.  | 
|  `p_size`  |  varchar2  |  —  |  Yes  |  The size of the tablespace. Specify the size in bytes (the default), kilobytes (K), megabytes (M), or gigabytes (G).   | 

The `resize_tempfile` procedure has the following parameters.


****  

| Parameter name | Data type | Default | Required | Description | 
| --- | --- | --- | --- | --- | 
|  `p_temp_file_id`  |  number  |  —  |  Yes  |  The identifier of the temp file to resize.  | 
|  `p_size`  |  varchar2  |  —  |  Yes  |  The size of the temp file. Specify the size in bytes (the default), kilobytes (K), megabytes (M), or gigabytes (G).   | 

The `autoextend_tempfile` procedure has the following parameters.


****  

| Parameter name | Data type | Default | Required | Description | 
| --- | --- | --- | --- | --- | 
|  `p_temp_file_id`  |  number  |  —  |  Yes  |  The identifier of the temp file to resize.  | 
|  `p_autoextend_state`  |  varchar2  |  —  |  Yes  |  The state of the autoextension feature. Specify `ON` to extend the temp file automatically and `OFF` to turn off autoextension.   | 
|  `p_next`  |  varchar2  |  —  |  No  |  The size of the next temp file increment. Specify the size in bytes (the default), kilobytes (K), megabytes (M), or gigabytes (G).  | 
|  `p_maxsize`  |  varchar2  |  —  |  No  |  The maximum disk space allowed for automatic extension. Specify the size in bytes (the default), kilobytes (K), megabytes (M), or gigabytes (G). You can specify `UNLIMITED` to remove the file size limit.  | 

The following examples resize a temporary tablespace named `TEMP` to the size of 4 GB.

```
EXEC rdsadmin.rdsadmin_util.resize_temp_tablespace('TEMP','4G');
```

```
EXEC rdsadmin.rdsadmin_util.resize_temp_tablespace('TEMP','4096000000');
```

The following example resizes a temporary tablespace based on the temp file with the file identifier `1` to the size of 2 MB.

```
EXEC rdsadmin.rdsadmin_util.resize_tempfile(1,'2M');
```

The following example turns off autoextension for temp file 1. It also sets the maximum autoextension size of temp file 2 to 10 GB, with an increment of 100 MB.

```
EXEC rdsadmin.rdsadmin_util.autoextend_tempfile(1,'OFF');
EXEC rdsadmin.rdsadmin_util.autoextend_tempfile(2,'ON','100M','10G');
```

For more information about read replicas for Oracle DB instances see [Working with read replicas for Amazon RDS for Oracle](oracle-read-replicas.md).

# Moving data between storage volumes in RDS for Oracle
Moving data between volumes

You can move data files and database objects between your primary and additional storage volumes. Before you move data, consider the following points:
+ The source and target volumes must have sufficient free space.
+ Data movement operations consume I/O on both volumes.
+ Large data movements can impact database performance.
+ If you restore a snapshot, moving data between storage volumes might be slow if it is affected by EBS lazy loading.

**Topics**
+ [

## Moving data files between volumes in RDS for Oracle
](#Appendix.Oracle.CommonDBATasks.MovingDatafiles)
+ [

## Moving table data and indexes between volumes in RDS for Oracle
](#Appendix.Oracle.CommonDBATasks.MovingTableData)
+ [

## Managing LOB storage using additional volumes
](#Appendix.Oracle.CommonDBATasks.ManagingLargeLOBStorage)

## Moving data files between volumes in RDS for Oracle
Moving data files

To move data files between storage volumes, use the Amazon RDS procedure `rdsadmin.rdsadmin_util.move_datafile`. Note the following requirements:
+ You must use Oracle Enterprise Edition to run the `move_datafile` procedure.
+ You can't move tablespace `SYSTEM` and `RDSADMIN`.

The `move_datafile` procedure has the following parameters.


****  

| Parameter name | Data type | Required | Description | 
| --- | --- | --- | --- | 
|  `p_data_file_id`  |  number  |  Yes  |  The ID of the data file to be moved.  | 
|  `p_location`  |  varchar2  |  Yes  |  The storage volume to which you want to move the data file.  | 

The following example moves a tablespace from the default volume `rdsdbdata` to the additional volume `rdsdbdata2`.

```
SQL> SELECT tablespace_name,file_id,file_name FROM dba_data_files
 WHERE tablespace_name = 'MYNEWTABLESPACE';

TABLESPACE_NAME              FILE_ID FILE_NAME
------------------------- ---------- --------------------------------------------------------------------------------
MYNEWTABLESPACE                    6 /rdsdbdata/db/ORCL_A/datafile/o1_mf_mynewtab_n123abcd_.dbf

EXECUTE rdsadmin.rdsadmin_util.move_datafile( 6, 'rdsdbdata2');

PL/SQL procedure successfully completed.

SQL> SELECT tablespace_name,file_id,file_name FROM dba_data_files
  WHERE tablespace_name = 'MYNEWTABLESPACE';

TABLESPACE_NAME              FILE_ID FILE_NAME
------------------------- ---------- --------------------------------------------------------------------------------
MYNEWTABLESPACE                    6 /rdsdbdata2/db/ORCL_A/datafile/o1_mf_mynewtab_n356efgh_.dbf
```

## Moving table data and indexes between volumes in RDS for Oracle
Moving table data and indexes

You can optimize database storage by creating tablespaces on additional storage volumes. Then you can move objects such as tables, indexes, and partitions to these tablespaces using standard Oracle SQL. This approach is valuable for performance tuning when your database contains data with different access patterns. For example, you could store frequently accessed operational data on high-performance storage volumes while moving rarely accessed historical data to lower-cost storage volumes.

In the following example, you create a new tablespace on high-performance volume `rdsdbdata2`. Then you move a table to your additional storage volume while the table is online. You also move the index to the same volume. Moving tables and rebuilding indexes while online requires Oracle Enterprise Edition.

```
ALTER SESSION SET db_create_file_dest = '/rdsdbdata2/db';
CREATE TABLESPACE perf_tbs DATAFILE SIZE 10G;

ALTER TABLE employees
  MOVE TABLESPACE perf_tbs ONLINE;

ALTER INDEX employees_idx
  REBUILD ONLINE TABLESPACE perf_tbs;
```

In the following example, you create a tablespace on a low-cost volume. Then you move a table partition to your low-cost storage volume using an online operation.

```
ALTER SESSION SET db_create_file_dest = '/rdsdbdata3/db';
CREATE TABLESPACE hist_tbs DATAFILE SIZE 10G;

ALTER TABLE orders
  MOVE PARTITION orders_2022
  TABLESPACE hist_tbs ONLINE;
```

In the following example, you query active sessions long operations.

```
SELECT sid,opname,sofar,totalwork,time_remaining,elapsed_seconds 
  FROM v$session_longops 
  WHERE time_remaining > 0;
```

You can check your tablespaces usage with the following query.

```
SELECT tablespace_name, used_percent
  FROM dba_tablespace_usage_metrics
  ORDER BY used_percent DESC;
```

## Managing LOB storage using additional volumes
Managing LOB storage

Your database might contains tables with BLOB or CLOB objects that consume substantial storage but are infrequently accessed. To optimize storage, you can relocate these LOB segments to a tablespace on an additional storage volume.

In the following example, you create a tablespace for LOB data on a low-cost volume that is intended for low-access data. Then you create a table that stores data on this volume.

```
ALTER SESSION SET db_create_file_dest = '/rdsdbdata3/db';
CREATE TABLESPACE lob_data DATAFILE SIZE 5G AUTOEXTEND ON NEXT 1G;

CREATE TABLE documents (
    doc_id NUMBER PRIMARY KEY,
    doc_date DATE,
    doc_content CLOB
) TABLESPACE user_data
LOB(doc_content) STORE AS (TABLESPACE lob_data);
```

# Working with external tables in RDS for Oracle
Working with Oracle external tables

*Oracle external tables *are tables with data that is not in the database. Instead, the data is in external files that the database can access. By using external tables, you can access data without loading it into the database. For more information about external tables, see [Managing external tables](http://docs.oracle.com/database/121/ADMIN/tables.htm#ADMIN01507) in the Oracle documentation. 

With Amazon RDS, you can store external table files in directory objects. You can create a directory object, or you can use one that is predefined in the Oracle database, such as the DATA\$1PUMP\$1DIR directory. For information about creating directory objects, see [Creating and dropping directories in the main data storage space](Appendix.Oracle.CommonDBATasks.Misc.md#Appendix.Oracle.CommonDBATasks.NewDirectories). You can query the ALL\$1DIRECTORIES view to list the directory objects for your Amazon RDS Oracle DB instance.

**Note**  
Directory objects point to the main data storage space (Amazon EBS volume) used by your instance. The space used—along with data files, redo logs, audit, trace, and other files—counts against allocated storage.

You can move an external data file from one Oracle database to another by using the [ DBMS\$1FILE\$1TRANSFER](https://docs.oracle.com/database/121/ARPLS/d_ftran.htm#ARPLS095) package or the [UTL\$1FILE](https://docs.oracle.com/database/121/ARPLS/u_file.htm#ARPLS069) package. The external data file is moved from a directory on the source database to the specified directory on the destination database. For information about using `DBMS_FILE_TRANSFER`, see [Importing using Oracle Data Pump](Oracle.Procedural.Importing.DataPump.md).

After you move the external data file, you can create an external table with it. The following example creates an external table that uses the `emp_xt_file1.txt` file in the USER\$1DIR1 directory.

```
CREATE TABLE emp_xt (
  emp_id      NUMBER,
  first_name  VARCHAR2(50),
  last_name   VARCHAR2(50),
  user_name   VARCHAR2(20)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY USER_DIR1
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
    (emp_id,first_name,last_name,user_name)
  )
  LOCATION ('emp_xt_file1.txt')
)
PARALLEL
REJECT LIMIT UNLIMITED;
```

Suppose that you want to move data that is in an Amazon RDS Oracle DB instance into an external data file. In this case, you can populate the external data file by creating an external table and selecting the data from the table in the database. For example, the following SQL statement creates the `orders_xt` external table by querying the `orders` table in the database.

```
CREATE TABLE orders_xt
  ORGANIZATION EXTERNAL
   (
     TYPE ORACLE_DATAPUMP
     DEFAULT DIRECTORY DATA_PUMP_DIR
     LOCATION ('orders_xt.dmp')
   )
   AS SELECT * FROM orders;
```

In this example, the data is populated in the `orders_xt.dmp` file in the DATA\$1PUMP\$1DIR directory.