Oracle SQL*Loader - Amazon Relational Database Service

Oracle SQL*Loader

Oracle SQL*Loader is well suited for large databases that have a limited number of objects in them. Since the process involved in exporting from a source database and loading to a target database is very specific to the schema, the following example creates the sample schema objects, exports from a source, and then loads it into a target database.

To download Oracle SQL*Loader, go to http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html.

  1. Create a sample source table using the command below.

    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 Amazon RDS instance, create a destination table that is used to load the data. The clause WHERE 1=2 ensures that you copy the structure of ALL_OBJECTS, but don't copy any of the rows.

    CREATE TABLE customer_1 TABLESPACE users AS (SELECT 0 AS ID, OWNER, OBJECT_NAME, CREATED FROM ALL_OBJECTS WHERE 1=2);
  3. The data is exported from the source database to a flat file with delimiters. This example uses SQL*Plus for this purpose. 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. You need to create a control file to describe the data. Again, depending on your data, you need to build a script that does 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. Finally, import the data using SQL*Loader with the appropriate username and password for the target database.

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