Importing using Oracle SQL*Loader - Amazon Relational Database Service (original) (raw)

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);  

1. 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);  

1. 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  

1. 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