Note: The reference notes did not contain this topic. The following answer is based on standard Oracle Database architecture as taught in Database Management Systems courses at TU BSc CSIT level. --- Oracle Database has a well-defined ar...

Privilege in Oracle Database

What is Privilege?

A privilege is a right or permission granted to a database user to perform a specific operation or to access a specific database object. Privileges control what actions a user can perform in the database system.

There are two types of privileges in Oracle:

• System Privileges - Allow users to perform specific database operations (e.g., CREATE TABLE, CREATE SESSION)
• Object Privileges - Allow users to perform operations on specific database objects like tables, views, etc. (e.g., SELECT, INSERT, UPDATE, DELETE on a table)

Oracle Database Commands

1. Create User

CREATE USER username IDENTIFIED BY password;

Example:

CREATE USER john IDENTIFIED BY john123;

2. Grant Privilege

GRANT privilege_name TO username;

Examples:

-- Grant system privilege
GRANT CREATE SESSION TO john;
GRANT CREATE TABLE TO john;

-- Grant object privilege
GRANT SELECT, INSERT ON employees TO john;

-- Grant all privileges
GRANT ALL PRIVILEGES TO john;

The WITH GRANT OPTION allows the user to further grant the privilege to others:

GRANT SELECT ON employees TO john WITH GRANT OPTION;

3. Revoke Privilege

REVOKE privilege_name FROM username;

Examples:

-- Revoke system privilege
REVOKE CREATE TABLE FROM john;

-- Revoke object privilege
REVOKE SELECT, INSERT ON employees FROM john;

4. Drop User

DROP USER username;

Example:

DROP USER john;

-- To drop user along with all their objects (tables, views, etc.)
DROP USER john CASCADE;

The CASCADE option is used when the user owns database objects; it removes the user and all associated objects.

Summary Table

Backup, Restore, and Recovery in Oracle Database

Note: Reference notes were not available for this topic. The following answer is based on standard Oracle Database concepts as taught in BSc CSIT curriculum.

1. Key Terminology

Backup

A backup is a copy of data (database files, control files, archived redo logs, etc.) that can be used to reconstruct lost or damaged data. It protects the database against data loss due to hardware failure, user errors, or disasters.

"A backup is a copy of data that can be used to reconstruct data."

Restore

Restore is the process of copying backup files back to their original (or new) location. It is the physical act of placing backup files onto disk so that recovery can proceed.

Example: Copying a datafile from tape/backup location back to the original directory.

Recovery

Recovery is the process of applying redo logs (archived and online) to a restored backup to bring the database to a consistent and up-to-date state. Recovery happens after restore.

Backup --> Restore (copy files back) --> Recovery (apply redo logs) --> Database Online

2. Types of Backups in Oracle

A. Physical Backup

A copy of the actual physical files used in storing and recovering the database.

i. Cold Backup (Offline Backup)

• Database is shut down before taking backup.
• All datafiles, control files, and redo log files are copied.
• Simple but causes downtime.

Example:

SHUTDOWN IMMEDIATE;
-- Copy all datafiles, controlfiles, redo logs to backup location
-- (OS level copy)
STARTUP;

ii. Hot Backup (Online Backup)

• Database remains open and available during backup.
• Requires database to be in ARCHIVELOG mode.
• Used for 24x7 production systems.

Example using RMAN:

RMAN> BACKUP DATABASE;

B. Logical Backup

A backup of the logical data (tables, schemas, procedures) using Oracle utilities like Data Pump (expdp/impdp) or the older exp/imp.

Example:

expdp system/password FULL=Y DUMPFILE=fullbackup.dmp LOGFILE=backup.log

To restore:

impdp system/password FULL=Y DUMPFILE=fullbackup.dmp LOGFILE=restore.log

C. Full Backup

A backup of the entire database -- all datafiles, control files, and archived redo logs.

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

D. Incremental Backup

Only backs up data blocks that have changed since the last backup. Saves time and storage.

Example:

-- Level 0 (Full baseline)
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

-- Level 1 (Changed blocks only)
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

E. Cumulative vs Differential Incremental

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

3. Recovery Process in Oracle

A. Complete Recovery

Recovers the database to the most current state -- no data loss. All redo logs are applied.

Steps:

-- Step 1: Restore the database
RMAN> RESTORE DATABASE;

-- Step 2: Recover (apply all redo logs)
RMAN> RECOVER DATABASE;

-- Step 3: Open the database
RMAN> ALTER DATABASE OPEN;

B. Incomplete Recovery (Point-in-Time Recovery)

Recovers the database to a specific point in time before a failure or user error (e.g., accidental table drop).

Types:

Example (Time-based):

RMAN> RUN {
  SET UNTIL TIME "TO_DATE('2024-01-15 10:00:00','YYYY-MM-DD HH24:MI:SS')";
  RESTORE DATABASE;
  RECOVER DATABASE;
  ALTER DATABASE OPEN RESETLOGS;
}

Note: After incomplete recovery, database must be opened with RESETLOGS.

C. Tablespace Recovery

Recover a single tablespace without affecting the rest of the database.

RMAN> RESTORE TABLESPACE users;
RMAN> RECOVER TABLESPACE users;
RMAN> ALTER TABLESPACE users ONLINE;

D. Datafile Recovery

Recover a single damaged or missing datafile.

RMAN> RESTORE DATAFILE '/u01/oradata/users01.dbf';
RMAN> RECOVER DATAFILE '/u01/oradata/users01.dbf';

4. ARCHIVELOG Mode vs NOARCHIVELOG Mode

To enable it:

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

5. A Complete Worked Example

Suppose the datafile users01.dbf of a production database is lost at 11:00 AM. A Level 0 backup exists from Sunday, Level 1 backups exist from each weekday night, and the database runs in ARCHIVELOG mode.

RMAN> SQL 'ALTER DATABASE DATAFILE ''/u01/oradata/users01.dbf'' OFFLINE';
RMAN> RESTORE DATAFILE '/u01/oradata/users01.dbf';   -- restore: copy from backup
RMAN> RECOVER DATAFILE '/u01/oradata/users01.dbf';   -- recovery: apply redo
RMAN> SQL 'ALTER DATABASE DATAFILE ''/u01/oradata/users01.dbf'' ONLINE';

RMAN restores the datafile from the Level 0 image, rolls it forward with the Level 1 incrementals, then applies the archived and online redo logs generated since that backup, so the datafile is brought back to 11:00 AM with no committed transaction lost. The rest of the database stays open for users throughout.

If instead a user had dropped a critical table at 10:00 AM, the loss is logical rather than physical, so an incomplete (point-in-time) recovery to 09:59 AM would be used and the database opened with RESETLOGS.

6. Conclusion

Backup, restore and recovery are three distinct stages of one protection strategy: backup creates the redundant copy in advance, restore puts that copy back on disk, and recovery applies redo to make it consistent and current. Oracle supports physical backups (cold and hot) and logical backups (Data Pump), full and incremental levels, and complete or point-in-time recovery. Running the database in ARCHIVELOG mode is the single decision that makes online backup and complete recovery possible, which is why every production Oracle database is configured that way.

Roles and Responsibilities of Database Administrator (DBA)

A Database Administrator (DBA) is a person responsible for managing, maintaining, and securing the database system in an organization. The DBA acts as a central authority over the database environment.

Key Roles and Responsibilities of a DBA

1. Schema Definition

• The DBA is responsible for creating the original database schema by writing a set of definitions in the DDL (Data Definition Language).
• This includes defining tables, views, constraints, and relationships.

2. Storage Structure and Access Method Definition

• The DBA defines how data is physically stored on storage devices.
• Decides on appropriate access methods (e.g., indexing, hashing) to ensure efficient data retrieval.

3. Schema and Physical Organization Modification

• When requirements change, the DBA modifies the schema or physical organization.
• This includes altering tables, adding new attributes, or reorganizing storage to improve performance.

4. Granting Authorization for Data Access

• The DBA controls who can access what data by granting or revoking access privileges.
• Ensures that only authorized users can read, write, or modify specific data.

5. Routine Maintenance

The DBA performs regular maintenance tasks such as:

• Backup and Recovery: Periodically backing up data and recovering it in case of failure.
• Performance Monitoring: Monitoring the database for slow queries and tuning performance.
• Disk Space Management: Ensuring sufficient storage is available for database operations.

6. Integrity Constraint Definition

• The DBA defines integrity constraints (e.g., primary keys, foreign keys, check constraints) to maintain data accuracy and consistency.

7. Liaison with Users

• Acts as a bridge between end users and the database system.
• Understands user requirements and translates them into appropriate database structures.

Summary Table

Note: The DBA holds the highest level of authority in a database environment and is essential for the smooth, secure, and efficient operation of any database system.

Tablespace and Datafile in Oracle

Tablespace

A tablespace is a logical storage unit in an Oracle database that groups related logical structures (tables, indexes, etc.) together. It is the bridge between the physical and logical components of an Oracle database. Every Oracle database has at least one tablespace called the SYSTEM tablespace.

Key points:

• It is a logical storage container
• A database is divided into one or more tablespaces
• Each tablespace belongs to only one database

Datafile

A datafile is a physical operating system file that actually stores the data on disk. Every tablespace is made up of one or more datafiles.

Key points:

• It is a physical file stored on the disk (e.g., .dbf file)
• One tablespace can have multiple datafiles
• One datafile belongs to only one tablespace

Relationship:

Database --> Tablespace (Logical) --> Datafile (Physical)

Adding a New Tablespace

Use the CREATE TABLESPACE statement:

CREATE TABLESPACE my_tablespace
DATAFILE '/u01/oradata/mydb/my_tablespace01.dbf'
SIZE 100M
AUTOEXTEND ON
NEXT 10M
MAXSIZE 500M;

Explanation of clauses:

Adding a New Datafile to an Existing Tablespace

Use the ALTER TABLESPACE statement:

ALTER TABLESPACE my_tablespace
ADD DATAFILE '/u01/oradata/mydb/my_tablespace02.dbf'
SIZE 50M
AUTOEXTEND ON
NEXT 5M
MAXSIZE 200M;

This adds a second datafile to the existing tablespace my_tablespace, increasing its total storage capacity.

Summary

Privilege in Oracle Database

What is Privilege?

A privilege is a right or permission granted to a database user to perform a specific operation or to access a specific database object. Privileges control what actions a user can perform in the database system.

There are two types of privileges in Oracle:

• System Privileges - Allow users to perform specific database operations (e.g., CREATE TABLE, CREATE SESSION)
• Object Privileges - Allow users to perform operations on specific database objects like tables, views, etc. (e.g., SELECT, INSERT, UPDATE, DELETE on a table)

Oracle Database Commands

1. Create User

CREATE USER username IDENTIFIED BY password;

Example:

CREATE USER john IDENTIFIED BY john123;

2. Grant Privilege

GRANT privilege_name TO username;

Examples:

-- Grant system privilege
GRANT CREATE SESSION TO john;
GRANT CREATE TABLE TO john;

-- Grant object privilege
GRANT SELECT, INSERT ON employees TO john;

-- Grant all privileges
GRANT ALL PRIVILEGES TO john;

The WITH GRANT OPTION allows the user to further grant the privilege to others:

GRANT SELECT ON employees TO john WITH GRANT OPTION;

3. Revoke Privilege

REVOKE privilege_name FROM username;

Examples:

-- Revoke system privilege
REVOKE CREATE TABLE FROM john;

-- Revoke object privilege
REVOKE SELECT, INSERT ON employees FROM john;

4. Drop User

DROP USER username;

Example:

DROP USER john;

-- To drop user along with all their objects (tables, views, etc.)
DROP USER john CASCADE;

The CASCADE option is used when the user owns database objects; it removes the user and all associated objects.

Summary Table

Note: The reference notes did not contain this topic. The following answer is based on standard Oracle Database architecture as taught in Database Management Systems courses at TU BSc CSIT level. --- Oracle Database has a well-defined ar...

Oracle AWR (Automatic Workload Repository)

What is Oracle AWR?

AWR (Automatic Workload Repository) is a built-in Oracle Database feature that automatically collects, processes, and maintains performance statistics for problem detection and self-tuning purposes.

Note: The following answer is based on standard Oracle Database documentation, as no specific curriculum notes were provided.

Key Characteristics of AWR:

• AWR is part of Oracle's self-management framework, introduced in Oracle Database 10g.
• It collects snapshots of database performance data at regular intervals (default: every 60 minutes).
• Snapshots are stored in the SYSAUX tablespace and retained for a default period of 8 days.
• It captures statistics such as:
  • SQL execution statistics
  • Wait events
  • System and session statistics
  • OS statistics
  • Memory usage

Purpose of AWR:

• Identify performance bottlenecks
• Support capacity planning
• Enable trend analysis over time
• Used by Oracle's ADDM (Automatic Database Diagnostic Monitor) for automated diagnosis

How AWR Report is Viewed in Oracle

AWR reports can be generated and viewed using the following methods:

Method 1: Using SQL*Plus Script

-- Connect as SYSDBA
sqlplus / as sysdba

-- Run the AWR report script
@$ORACLE_HOME/rdbms/admin/awrrpt.sql

Steps during execution:

1. Choose report format: HTML or TEXT
2. Specify the number of days to list snapshots
3. Select the Begin Snapshot ID
4. Select the End Snapshot ID
5. Provide the output filename (e.g., awr_report.html)

Method 2: Using Oracle Enterprise Manager (OEM)

1. Log in to Oracle Enterprise Manager (OEM)
2. Navigate to: Performance > AWR > AWR Report
3. Select the desired time period / snapshot range
4. Click Generate Report
5. View the report directly in the browser (HTML format)

Method 3: Using DBMS_WORKLOAD_REPOSITORY Package

-- Generate AWR report programmatically
SELECT output 
FROM TABLE(
    DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
        l_dbid    => &dbid,
        l_inst_num => 1,
        l_bid     => &begin_snap,
        l_eid     => &end_snap
    )
);

AWR Report Contents:

Summary

AWR is a powerful Oracle performance diagnostic tool that automatically captures workload data. Reports are primarily viewed via the awrrpt.sql script in SQL*Plus or through Oracle Enterprise Manager, helping DBAs analyze and resolve database performance issues.

A Role is a named group of related privileges that can be granted to users or other roles. Instead of granting individual privileges to each user separately, a DBA can grant multiple privileges to a role and then assign that role to mult...

Database auditing is the process of monitoring and recording all activities performed on a database, including who accessed the data, what operations were performed, when they were performed, and from where they were performed. --- - Aud...

Backup, Restore, and Recovery in Oracle Database

Note: Reference notes were not available for this topic. The following answer is based on standard Oracle Database concepts as taught in BSc CSIT curriculum.

1. Key Terminology

Backup

A backup is a copy of data (database files, control files, archived redo logs, etc.) that can be used to reconstruct lost or damaged data. It protects the database against data loss due to hardware failure, user errors, or disasters.

"A backup is a copy of data that can be used to reconstruct data."

Restore

Restore is the process of copying backup files back to their original (or new) location. It is the physical act of placing backup files onto disk so that recovery can proceed.

Example: Copying a datafile from tape/backup location back to the original directory.

Recovery

Recovery is the process of applying redo logs (archived and online) to a restored backup to bring the database to a consistent and up-to-date state. Recovery happens after restore.

Backup --> Restore (copy files back) --> Recovery (apply redo logs) --> Database Online

2. Types of Backups in Oracle

A. Physical Backup

A copy of the actual physical files used in storing and recovering the database.

i. Cold Backup (Offline Backup)

• Database is shut down before taking backup.
• All datafiles, control files, and redo log files are copied.
• Simple but causes downtime.

Example:

SHUTDOWN IMMEDIATE;
-- Copy all datafiles, controlfiles, redo logs to backup location
-- (OS level copy)
STARTUP;

ii. Hot Backup (Online Backup)

• Database remains open and available during backup.
• Requires database to be in ARCHIVELOG mode.
• Used for 24x7 production systems.

Example using RMAN:

RMAN> BACKUP DATABASE;

B. Logical Backup

A backup of the logical data (tables, schemas, procedures) using Oracle utilities like Data Pump (expdp/impdp) or the older exp/imp.

Example:

expdp system/password FULL=Y DUMPFILE=fullbackup.dmp LOGFILE=backup.log

To restore:

impdp system/password FULL=Y DUMPFILE=fullbackup.dmp LOGFILE=restore.log

C. Full Backup

A backup of the entire database -- all datafiles, control files, and archived redo logs.

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

D. Incremental Backup

Only backs up data blocks that have changed since the last backup. Saves time and storage.

Example:

-- Level 0 (Full baseline)
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

-- Level 1 (Changed blocks only)
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

E. Cumulative vs Differential Incremental

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

3. Recovery Process in Oracle

A. Complete Recovery

Recovers the database to the most current state -- no data loss. All redo logs are applied.

Steps:

-- Step 1: Restore the database
RMAN> RESTORE DATABASE;

-- Step 2: Recover (apply all redo logs)
RMAN> RECOVER DATABASE;

-- Step 3: Open the database
RMAN> ALTER DATABASE OPEN;

B. Incomplete Recovery (Point-in-Time Recovery)

Recovers the database to a specific point in time before a failure or user error (e.g., accidental table drop).

Types:

Example (Time-based):

RMAN> RUN {
  SET UNTIL TIME "TO_DATE('2024-01-15 10:00:00','YYYY-MM-DD HH24:MI:SS')";
  RESTORE DATABASE;
  RECOVER DATABASE;
  ALTER DATABASE OPEN RESETLOGS;
}

Note: After incomplete recovery, database must be opened with RESETLOGS.

C. Tablespace Recovery

Recover a single tablespace without affecting the rest of the database.

RMAN> RESTORE TABLESPACE users;
RMAN> RECOVER TABLESPACE users;
RMAN> ALTER TABLESPACE users ONLINE;

D. Datafile Recovery

Recover a single damaged or missing datafile.

RMAN> RESTORE DATAFILE '/u01/oradata/users01.dbf';
RMAN> RECOVER DATAFILE '/u01/oradata/users01.dbf';

4. ARCHIVELOG Mode vs NOARCHIVELOG Mode

To enable it:

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

5. A Complete Worked Example

Suppose the datafile users01.dbf of a production database is lost at 11:00 AM. A Level 0 backup exists from Sunday, Level 1 backups exist from each weekday night, and the database runs in ARCHIVELOG mode.

RMAN> SQL 'ALTER DATABASE DATAFILE ''/u01/oradata/users01.dbf'' OFFLINE';
RMAN> RESTORE DATAFILE '/u01/oradata/users01.dbf';   -- restore: copy from backup
RMAN> RECOVER DATAFILE '/u01/oradata/users01.dbf';   -- recovery: apply redo
RMAN> SQL 'ALTER DATABASE DATAFILE ''/u01/oradata/users01.dbf'' ONLINE';

RMAN restores the datafile from the Level 0 image, rolls it forward with the Level 1 incrementals, then applies the archived and online redo logs generated since that backup, so the datafile is brought back to 11:00 AM with no committed transaction lost. The rest of the database stays open for users throughout.

If instead a user had dropped a critical table at 10:00 AM, the loss is logical rather than physical, so an incomplete (point-in-time) recovery to 09:59 AM would be used and the database opened with RESETLOGS.

6. Conclusion

Backup, restore and recovery are three distinct stages of one protection strategy: backup creates the redundant copy in advance, restore puts that copy back on disk, and recovery applies redo to make it consistent and current. Oracle supports physical backups (cold and hot) and logical backups (Data Pump), full and incremental levels, and complete or point-in-time recovery. Running the database in ARCHIVELOG mode is the single decision that makes online backup and complete recovery possible, which is why every production Oracle database is configured that way.

Roles and Responsibilities of Database Administrator (DBA)

A Database Administrator (DBA) is a person responsible for managing, maintaining, and securing the database system in an organization. The DBA acts as a central authority over the database environment.

Key Roles and Responsibilities of a DBA

1. Schema Definition

• The DBA is responsible for creating the original database schema by writing a set of definitions in the DDL (Data Definition Language).
• This includes defining tables, views, constraints, and relationships.

2. Storage Structure and Access Method Definition

• The DBA defines how data is physically stored on storage devices.
• Decides on appropriate access methods (e.g., indexing, hashing) to ensure efficient data retrieval.

3. Schema and Physical Organization Modification

• When requirements change, the DBA modifies the schema or physical organization.
• This includes altering tables, adding new attributes, or reorganizing storage to improve performance.

4. Granting Authorization for Data Access

• The DBA controls who can access what data by granting or revoking access privileges.
• Ensures that only authorized users can read, write, or modify specific data.

5. Routine Maintenance

The DBA performs regular maintenance tasks such as:

• Backup and Recovery: Periodically backing up data and recovering it in case of failure.
• Performance Monitoring: Monitoring the database for slow queries and tuning performance.
• Disk Space Management: Ensuring sufficient storage is available for database operations.

6. Integrity Constraint Definition

• The DBA defines integrity constraints (e.g., primary keys, foreign keys, check constraints) to maintain data accuracy and consistency.

7. Liaison with Users

• Acts as a bridge between end users and the database system.
• Understands user requirements and translates them into appropriate database structures.

Summary Table

Note: The DBA holds the highest level of authority in a database environment and is essential for the smooth, secure, and efficient operation of any database system.

Tablespace and Datafile in Oracle

Tablespace

A tablespace is a logical storage unit in an Oracle database that groups related logical structures (tables, indexes, etc.) together. It is the bridge between the physical and logical components of an Oracle database. Every Oracle database has at least one tablespace called the SYSTEM tablespace.

Key points:

• It is a logical storage container
• A database is divided into one or more tablespaces
• Each tablespace belongs to only one database

Datafile

A datafile is a physical operating system file that actually stores the data on disk. Every tablespace is made up of one or more datafiles.

Key points:

• It is a physical file stored on the disk (e.g., .dbf file)
• One tablespace can have multiple datafiles
• One datafile belongs to only one tablespace

Relationship:

Database --> Tablespace (Logical) --> Datafile (Physical)

Adding a New Tablespace

Use the CREATE TABLESPACE statement:

CREATE TABLESPACE my_tablespace
DATAFILE '/u01/oradata/mydb/my_tablespace01.dbf'
SIZE 100M
AUTOEXTEND ON
NEXT 10M
MAXSIZE 500M;

Explanation of clauses:

Adding a New Datafile to an Existing Tablespace

Use the ALTER TABLESPACE statement:

ALTER TABLESPACE my_tablespace
ADD DATAFILE '/u01/oradata/mydb/my_tablespace02.dbf'
SIZE 50M
AUTOEXTEND ON
NEXT 5M
MAXSIZE 200M;

This adds a second datafile to the existing tablespace my_tablespace, increasing its total storage capacity.

Summary

A user profile in Oracle is a named set of resource limits and password management parameters that can be assigned to one or more database users. It controls and restricts the use of database resources such as CPU time, logical reads, se...

Note: The reference notes did not contain material on this topic. The following answer is based on standard Oracle Database concepts as taught in TU BSc CSIT Database Administration curriculum. --- A Tablespace is the logical storage uni...

Data Pump in Oracle

What is Data Pump?

Oracle Data Pump is a high-speed utility introduced in Oracle 10g that allows fast bulk data and metadata movement between Oracle databases. It is an improved replacement for the older exp/imp utilities. Data Pump uses server-side processing, meaning the export/import operations are performed on the database server rather than the client side, making it significantly faster and more efficient.

Key features:

• Supports parallel execution for faster performance
• Allows fine-grained object selection
• Supports network-based data transfer (no dump file needed)
• Provides restart/resume capability for failed jobs

Exporting Data Using Data Pump (expdp)

The export utility is called expdp (Export Data Pump).

Step-by-Step Procedure:

Step 1: Create a Directory Object A directory object must be created in Oracle pointing to a physical OS directory where dump files will be stored.

CREATE OR REPLACE DIRECTORY dp_dir AS '/u01/backup/';
GRANT READ, WRITE ON DIRECTORY dp_dir TO scott;

Step 2: Run the expdp Command

expdp scott/tiger \
  DIRECTORY=dp_dir \
  DUMPFILE=scott_export.dmp \
  LOGFILE=scott_export.log \
  SCHEMAS=scott

Common Export Modes:

Importing Data Using Data Pump (impdp)

The import utility is called impdp (Import Data Pump).

Step-by-Step Procedure:

Step 1: Ensure Directory Object Exists The same or a new directory object must be available (as created above).

Step 2: Run the impdp Command

impdp scott/tiger \
  DIRECTORY=dp_dir \
  DUMPFILE=scott_export.dmp \
  LOGFILE=scott_import.log \
  SCHEMAS=scott

Common Import Options:

Example with remapping:

impdp system/manager \
  DIRECTORY=dp_dir \
  DUMPFILE=scott_export.dmp \
  REMAP_SCHEMA=scott:hr \
  TABLE_EXISTS_ACTION=REPLACE

Summary

Note: The notes for this topic were not available; this answer is based on standard Oracle Data Pump documentation and is consistent with TU BSc CSIT Database curriculum coverage.