8.3 Use Cases

The following use cases illustrate how to use the DATAPUMP command to import and export data.

Use Case 1

Copy tables, views and functions in current schema from database to database.

  1. Export the current schema into DATA_PUMP_DIR.
  2. Import the schema from DATA_PUMP_DIR.
-- Export the current schema into DATA_PUMP_DIR
SQL> connect <db-connect1-string>
SQL> dp export -dumpfile my_dump.dmp -includelist table,view,function

-- Import from DATA_PUMP_DIR
SQL> connect <db-connect2-string>
SQL> dp import -dumpfile my_dump.dmp

Use Case 2

Copy current schema from database to Autonomous Database. Encryption is done using prompt for password.

  1. Set up for Oracle access to Oracle Object Store using an OCI profile.
  2. Export current schema into an encrypted dump file in DATA_PUMP_DIR and copy it to the CS bucket using profile.
  3. Import encrypted file from Oracle Object Store using credential.
-- Set up for Oracle access to Oracle Object Store using an OCI profile
SQL> oci profile my-profile
SQL> cs https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/abc123/testing-bucket/

-- Export current schema into an encrypted dump file in DATA_PUMP_DIR and copy it to the CS bucket using profile
SQL> connect <db-connect-string>
SQL> dp export -copycloud -dumpfile my_dump.dmp -encryptionpassword

-- Import encrypted file from Oracle Object Store using credential
SQL> set cloudconfig <wallet>
SQL> connect <cloud-connect-string>
SQL> dp import -dumpuri /o/my_dump.dmp -encryptionpassword -c SWIFTCRED

Use Case 3

Copy multiple schemas from database to Autonomous Database with remap schemas.

  1. Set up for Oracle access to Oracle Object Store using an OCI profile.
  2. Export schemas into database directory and copy to cloud.
  3. Import from Oracle Object Store using credential. Remap the schemas.
-- Set up for Oracle access to Oracle Object Store using an OCI profile
SQL> oci profile my-profile
SQL> cs https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/abc123/testing-bucket/

-- Export schemas into database directory and copy to cloud
SQL> connect <db-connect-string>
SQL> dp export -schemas dpumptest1,dpumptest11 -dumpfile DPUMPTEST1_11.DMP -cc

-- Import from Oracle Object Store using credential. Remap the schemas
SQL> set cloudconfig <wallet>
SQL> connect <cloud-connect-string>
SQL> dp import -dumpuri /o/DPUMPTEST1_11.DMP -c SWIFTCRED -rs dpumptest1=dpumptest2,dpumptest11=dpumptest21

Use Case 4

Copy multiple schemas from database to Autonomous Database with remap tablespace using OCI profile only.

  1. Set up for Oracle access to Oracle Object Store using an OCI profile.
  2. Export the current schema into DATA_PUMP_DIR and copy it to the CS bucket using profile.
  3. Copy from Oracle Object Store into directory and import from directory. Remap the tablespace.
-- Set up for Oracle access to Oracle Object Store using an OCI profile
SQL> oci profile my-profile
SQL> cs https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/abc123/testing-bucket/

-- Export the current schema into DATA_PUMP_DIR and copy it to the CS bucket using profile
SQL> connect <db-connect-string>
SQL> dp export -copycloud

-- Copy from Oracle Object Store into directory and import from directory. Remap the tablespace.
SQL> set cloudconfig <wallet>
SQL> connect <cloud-connect-string>
SQL> dp import -copycloud -dumpuri /o/ESQL_<n>.DMP -rt DATA=USERS

Use Case 5

Copy current schema from database to Autonomous Database using parallel processors.

  1. Set up for Oracle access to Oracle Object Store using an OCI profile.
  2. Export the current schema into DATA_PUMP_DIR and copy all files to the CS bucket using profile.
  3. Import from Oracle Object Store using credential.
-- Set up for Oracle access to Oracle Object Store using an OCI profile
SQL> oci profile my-profile
SQL> cs https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/abc123/testing-bucket/

-- Export the current schema into DATA_PUMP_DIR and copy all files to the CS bucket using profile
SQL> connect <db-connect-string>
SQL> dp export -copycloud -parallel 3 -dumpfile my_dump1.dmp,my_dump2.dmp,my_dump3.dmp

-- Import from Oracle Object Store using credential
SQL> set cloudconfig <wallet>
SQL> connect <cloud-connect-string>
SQL> dp import -dumpuri /o/my_dump1.dmp,/o/my_dump2.dmp,/o/my_dump3.dmp -c SWIFTCRED