Oracle Database Administration/Moving Data

< Oracle Database Administration

This lesson introduces moving data.

Objectives and Skills

Objectives and skills for the moving portion of Oracle Database Administration I certification include:[1]

  • Moving Data
    • Describe and use methods to move data (Directory objects, SQL*Loader, External Tables)
    • Explain the general architecture of Oracle Data Pump
    • Use Data Pump Export and Import to move data between Oracle databases

Readings

  1. Oracle: Database Utilities SQL*Loader Concepts
  2. Oracle: Database Utilities Overview of Oracle Data Pump
  3. Oracle: Database Utilities Data Pump Export
  4. Oracle: Database Utilities Data Pump Import

Multimedia

  1. YouTube: Using Oracle SQL Loader
  2. YouTube: Data Pump, Oracle DBA, export whole schema and import selected objects

Activities

SQL*Loader

  1. Use SQL*Loader.
    1. Create a text file with the following information. Save the file as regions.txt.
      ID, REGION
      101, Eastern Europe
      102, Western Europe
      103, North America
      104, Central America
      105, South America
      106, Central Asia
      107, Eastern Asia
      108, Southern Asia
      109, Southeastern Asia
      110, Western Asia
      111, Eastern Africa
      112, Central Africa
      113, Northern Africa
      114, Southern Africa
      115, Western Africa
    2. Create an SQL*Loader control file with the following information. Save the file as regions.ctl.
      OPTIONS (SKIP=1)
      LOAD DATA
      INFILE 'regions.txt'
      BADFILE 'regions.bad'
      DISCARDFILE 'regions.dsc'
      APPEND
      INTO TABLE HR.REGIONS
      FIELDS TERMINATED BY ','
      TRAILING NULLCOLS
      (REGION_ID INTEGER EXTERNAL(3),
      REGION_NAME)
    3. Use SQL*Loader to load the data using the following command:
      sqlldr userid=system/<password> control=regions.ctl
    4. Verify the import using the following query:
      SELECT * FROM HR.REGIONS
    5. Clean up using the following query:
      DELETE FROM HR.REGIONS WHERE REGION_ID >= 101;

External Tables

  1. Use an external table.
    1. Create a directory object pointing to an existing operating system directory using the following query:
      CREATE DIRECTORY external_files AS '<path>';
    2. Put the regions.txt text file from above in the specified directory.
    3. Create an external table referencing the text file using the following query:
      CREATE TABLE HR.REGIONS_FILE
      (
        REGION_ID NUMBER(3),
        REGION_NAME VARCHAR2(25)
      )
      ORGANIZATION EXTERNAL
      (
        TYPE ORACLE_LOADER
        DEFAULT DIRECTORY external_files
        ACCESS PARAMETERS
        (
          RECORDS DELIMITED BY NEWLINE
          SKIP 1
          FIELDS TERMINATED BY ','
        )
        LOCATION ('regions.txt')
      )
    4. Select data from the external table using the following query:
      SELECT * FROM HR.REGIONS_FILE
    5. Use the following query to load the data into the REGIONS table:
      INSERT INTO HR.REGIONS(REGION_ID, REGION_NAME)
      (SELECT REGION_ID, REGION_NAME FROM HR.REGIONS_FILE);
    6. Verify the import using the following query:
      SELECT * FROM HR.REGIONS
    7. Clean up using the following queries:
      DELETE FROM HR.REGIONS WHERE REGION_ID >= 101;
      DROP TABLE HR.REGIONS_FILE;
      DROP DIRECTORY external_files;

Spool

  1. Use spool to export a comma-separated-values copy of the REGIONS table.
    1. Review Charito: How to Write to a CSV File Using Oracle SQL*Plus.
    2. Use SQL*PLUS and run the following commands:
      set colsep ,
      set pagesize 0
      set trimspool on

      spool regions.csv

      SELECT * FROM HR.REGIONS;

      spool off
    3. Exit SQL*PLUS and list the files in the current directory (DIR or LS). Open the regions.csv file and view the results.

SQL Developer

  1. Use SQL Developer to export a comma-separated-values copy of the REGIONS table.
    1. Review Oracle: Using SQL Developer for Importing and Exporting.
    2. Follow the instructions for the Oracle: Example: Exporting Data to a Microsoft Excel File

Oracle Data Pump

  1. Identify the data pump directory using the following query:
    SELECT * FROM DBA_DIRECTORIES;
  2. Use Enterprise Manager Data Pump Export.
    1. Use Enterprise Manager Database Control / Data Movement / Export to Export Files to export the HR.REGIONS table.
    2. Navigate to the directory specified in the export and open the exported file using a text editor. Observe the file format and contents.
  3. Use command-line Data Pump Export.
    1. Use the following command to export the HR schema:
      expdp system/<password> schemas=hr dumpfile=hr.dmp
  4. Use command-line Data Pump Import.
    1. Use the following command to import HR.REGIONS into the OE schema:
      impdp system/<password> dumpfile=hr.dmp remap_schema=hr:oe tables=hr.regions
    2. Use the following query to verify the import:
      SELECT * FROM OE.REGIONS;
    3. use the following query to clean up the import:
      DROP TABLE OE.REGIONS;

See Also

References

This article is issued from Wikiversity. The text is licensed under Creative Commons - Attribution - Sharealike. Additional terms may apply for the media files.