Importing using Oracle SQL*Loader - Amazon Relational Database Service
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Importing using Oracle SQL*Loader

You might consider Oracle SQL*Loader for large databases that contain a limited number of objects. Because the process of exporting from a source database and loading to a target database is specific to the schema, the following example creates the sample schema objects, exports from a source, and then loads the data into a target database.

The easiest way to install Oracle SQL*Loader is to install the Oracle Instant Client. To download the software, go to https://www.oracle.com/database/technologies/instant-client.html. For documentation, see Instant Client for SQL*Loader, Export, and Import in the Oracle Database Utilities manual.

To import data using Oracle SQL*Loader

  1. Create a sample source table using the following SQL statement.

    CREATE TABLE customer_0 TABLESPACE users 
   AS (SELECT ROWNUM id, o.* 
       FROM   ALL_OBJECTS o, ALL_OBJECTS x 
       WHERE  ROWNUM <= 1000000);

  2. On the target RDS for Oracle DB instance, create a destination table for loading the data. The clause WHERE 1=2 ensures that you copy the structure of ALL_OBJECTS, but don't copy any rows.

    CREATE TABLE customer_1 TABLESPACE users 
  AS (SELECT 0 AS ID, OWNER, OBJECT_NAME, CREATED
      FROM   ALL_OBJECTS
      WHERE  1=2);

  3. Export the data from the source database to a text file. The following example uses SQL*Plus. For your data, you will likely need to generate a script that does the export for all the objects in the database. 

    ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS'

SET LINESIZE 800 HEADING OFF FEEDBACK OFF ARRAY 5000 PAGESIZE 0
SPOOL customer_0.out 
SET MARKUP HTML PREFORMAT ON
SET COLSEP ','

SELECT id, owner, object_name, created 
FROM   customer_0; 

SPOOL OFF

  4. Create a control file to describe the data. You might need to write a script to perform this step. 

    cat << EOF > sqlldr_1.ctl 
load data
infile customer_0.out
into table customer_1
APPEND
fields terminated by "," optionally enclosed by '"'
(
  id           POSITION(01:10)    INTEGER EXTERNAL,
  owner        POSITION(12:41)    CHAR,
  object_name  POSITION(43:72)    CHAR,
  created      POSITION(74:92)    date "YYYY/MM/DD HH24:MI:SS"
)

    If needed, copy the files generated by the preceding code to a staging area, such as an Amazon EC2 instance.

  5. Import the data using SQL*Loader with the appropriate user name and password for the target database. 

    sqlldr cust_dba@targetdb CONTROL=sqlldr_1.ctl BINDSIZE=10485760 READSIZE=10485760 ROWS=1000