Importing using Oracle SQL*Loader - Amazon Relational Database Service

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