Using Oracle Import to recover Database Objects

You have seen how to export database objects from your database. Now we will review the use of the Import utility to insert data objects into a database. The database that you insert objects into does not need to be the same database that was used for the extract. For example, many application providers will export a base image of their database to facilitate the installation of their customer databases.

• Import modes
  As with exports, there are three import modes:
  

  Mode	Description
  Table	Will allow you to import a selected table. You must have the appropriate user privileges to import other user's table.
  User	Will allow you to import a database object for a specific user.
  Full	Will allow you to import all database objects except for those owned by SYS. Users must have IMP_FULL_DATABASE role to import a full database export.

The three traditional import modes

1. Table,
2. User, and
3. Full

still apply in Oracle 23c, particularly when using Data Pump Import (`impdp`), which is Oracle’s recommended utility for data imports as of Oracle 10g and onward (including 23c).
Here is how these modes work in Oracle 23c:

1. ✅ Table Mode (TABLES=...)
   • Use Case: Import specific tables.
   • Command Example:
     

     impdp scott/password tables=emp,dept directory=DATA_PUMP_DIR dumpfile=scott_tables.dmp
     

   • Privileges Required:
     • You must own the tables or have READ or IMP_FULL_DATABASE privileges.
     • You can use REMAP_SCHEMA to import into a different user if permitted.
2. ✅ User Mode (SCHEMAS=...)
   • Use Case: Import all objects that belong to one or more users.
   • Command Example:
     

     impdp system/password schemas=hr directory=DATA_PUMP_DIR dumpfile=hr.dmp
     

     
     
   • Privileges Required:
     • If importing into your own schema: minimal privileges.
     • For other users: IMP_FULL_DATABASE role is required.
3. ✅ Full Mode (FULL=Y)
   • Use Case: Import an entire database (excluding some SYS-owned objects).
   • Command Example:
     

               impdp system/password full=y directory=DATA_PUMP_DIR dumpfile=full_backup.dmp
             

     
     
   • Privileges Required:
     • Must have the IMP_FULL_DATABASE role.
     • SYS-owned objects are not imported.

🆕 In Oracle 23c:
Oracle 23c introduces

1. JSON Relational Duality Views
2. AI Vector Search
3. Data Domains

but "import/export concepts and modes remain consistent" with previous versions when using `expdp`/`impdp`. These modes continue to be relevant in cloud-enabled environments, including 1) Oracle Autonomous Database and 2) Oracle Base Database Service on OCI, though with slight variations in managed service tooling and access controls.

When logged into an "Oracle 23c" database, whether on-premises or via Oracle Cloud Infrastructure (OCI) and connected through SQL*Plus (or any compatible SQL terminal), you can obtain online help using the following command:

HELP INDEX;

📘 Explanation:

• HELP INDEX; provides a list of all topics available through the HELP system.
• Once you see the available topics, you can get detailed help on a specific topic like this:

HELP CREATE TABLE;

✅ Prerequisites:

• The HELP system depends on the presence of the help data in the SYSTEM schema (typically in the HELP table).
• If HELP is not yet installed on your OCI instance, you may see:
  

  SP2-0171: HELP not accessible.

In that case, the DBA can run the following script (typically already included in Oracle Cloud images):

@?/sqlplus/admin/help/hlpbld.sql helpus.sql

Export and Import both provide online help. Enter

 
exp help=y 

on the command line to invoke Export help or

 imp help=y 

to invoke Import help.

• Online Oracle HELP
  Purpose: Provides information about Data Pump Import commands available in interactive command mode.
  Syntax and Description HELP
  Displays information about the commands available in interactive-command mode.
  Example

   
  Import> HELP
  

The next lesson concludes this module.