fbpx

Top 100 Oracle DBA Interview Questions

Software Engineer Coding 28

Contents show

1. What is an Oracle database?

Answer: Oracle database is a relational database management system (RDBMS) developed by Oracle Corporation. It is designed to store and retrieve data efficiently, ensuring data integrity and security.


2. How do you create a new user in Oracle?

Answer: To create a new user, you can use the CREATE USER command:

CREATE USER username IDENTIFIED BY password;

3. Explain the difference between CHAR and VARCHAR2 data types.

Answer: CHAR is a fixed-length character data type, while VARCHAR2 is a variable-length character data type. CHAR pads the value with spaces to the specified length, whereas VARCHAR2 stores only the actual characters.


4. What is a primary key?

Answer: A primary key is a column (or combination of columns) that uniquely identifies each row in a table. It enforces entity integrity and ensures that each record can be uniquely identified.


5. How do you perform a backup in Oracle?

Answer: To perform a full database backup, you can use the following RMAN command:

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

6. Explain the purpose of an index in Oracle.

Answer: An index is a database object that improves the speed of data retrieval operations on a table at the cost of additional storage space and decreased performance on data modification operations.


7. How do you find the nth highest salary in a table?

Answer: You can use a subquery and the ROWNUM pseudo-column to find the nth highest salary:

SELECT salary
FROM (
    SELECT salary, ROWNUM AS rnum
    FROM (
        SELECT salary
        FROM employees
        ORDER BY salary DESC
    )
)
WHERE rnum = n;

Replace n with the desired rank.


8. What is a deadlock in Oracle?

Answer: A deadlock occurs when two or more transactions are waiting for resources held by each other, resulting in a standstill where none of the transactions can proceed.


9. How do you add a new column to an existing table?

Answer: You can use the ALTER TABLE command to add a new column:

ALTER TABLE table_name
ADD column_name datatype;

10. Explain the purpose of the COMMIT statement.

Answer: The COMMIT statement finalizes all changes made during the current transaction. It makes the changes permanent and releases any locks held by the transaction.


11. How do you find the current date and time in Oracle?

Answer: You can use the SYSDATE function:

SELECT SYSDATE FROM dual;

12. Explain the difference between DELETE and TRUNCATE.

Answer: DELETE removes rows one by one and can be rolled back. TRUNCATE removes all rows at once and cannot be rolled back. TRUNCATE also resets high water mark.


13. What is a materialized view in Oracle?

Answer: A materialized view is a database object that stores the results of a query. It can be used to precompute and store aggregated or joined data, improving query performance.


14. What is the purpose of the Oracle listener?

Answer: The Oracle listener is a process that listens for incoming client connection requests and manages the traffic of these requests to the database. It routes the requests to the appropriate database service.


15. How do you perform a tablespace backup in Oracle?

Answer: To perform a tablespace backup, you can use the BACKUP TABLESPACE command in RMAN:

RMAN> BACKUP TABLESPACE tablespace_name;

16. Explain the difference between a function and a stored procedure.

Answer: A function returns a value and can be used in SQL queries, while a stored procedure does not return a value directly. Procedures can have both input and output parameters.


17. What is a trigger in Oracle?

Answer: A trigger is a PL/SQL block that is executed in response to a specific event, such as an insert, update, or delete operation on a table. Triggers are used to enforce business rules or perform actions based on certain conditions.


18. How do you create an index in Oracle?

Answer: To create an index, you can use the CREATE INDEX command:

CREATE INDEX index_name
ON table_name (column1, column2, ...);

19. Explain the purpose of the Oracle redo log.

Answer: The redo log is a set of files that record all changes made to a database. It ensures that the changes made to the database are recoverable in case of a failure.


20. How do you find the size of a table in Oracle?

Answer: You can use the following query to find the size of a table:

SELECT segment_name, segment_type, bytes
FROM user_segments
WHERE segment_name = 'table_name';

Replace 'table_name' with the actual table name.


21. What is a database link in Oracle?

Answer: A database link is a schema object in one database that enables you to access objects on another database. It allows you to perform distributed operations across different databases.


22. How do you grant privileges in Oracle?

Answer: To grant privileges, you can use the GRANT command:

GRANT privilege_name
ON object_name
TO user_name;

23. Explain the purpose of the Oracle Data Dictionary.

Answer: The Data Dictionary is a set of read-only tables and views that provide information about the database. It contains metadata about database objects, users, privileges, and more.


24. What is the purpose of the Oracle optimizer?

Answer: The optimizer is a component of the Oracle database that determines the most efficient way to execute a SQL statement. It analyzes various execution plans and chooses the best one based on cost.


25. What is the purpose of the Oracle redo log?

Answer: The redo log is a set of files that record all changes made to a database. It ensures that the changes made to the database are recoverable in case of a failure.


26. How do you find the size of a table in Oracle?

Answer: You can use the following query to find the size of a table:

SELECT segment_name, segment_type, bytes
FROM user_segments
WHERE segment_name = 'table_name';

Replace 'table_name' with the actual table name.


27. What is a database link in Oracle?

Answer: A database link is a schema object in one database that enables you to access objects on another database. It allows you to perform distributed operations across different databases.


28. How do you grant privileges in Oracle?

Answer: To grant privileges, you can use the GRANT command:

GRANT privilege_name
ON object_name
TO user_name;

29. Explain the purpose of the Oracle Data Dictionary.

Answer: The Data Dictionary is a set of read-only tables and views that provide information about the database. It contains metadata about database objects, users, privileges, and more.


30. What is the purpose of the Oracle optimizer?

Answer: The optimizer is a component of the Oracle database that determines the most efficient way to execute a SQL statement. It analyzes various execution plans and chooses the best one based on cost.


31. What is the difference between a primary key and a unique key?

Answer:

  • A primary key is a column or set of columns that uniquely identifies each row in a table. It enforces entity integrity.
  • A unique key ensures that all values in a column or set of columns are unique. It allows null values, but only one null per column.

32. Explain the purpose of the Oracle listener.

Answer: The Oracle listener is a process that listens for incoming client connection requests and manages the traffic of these requests to the database. It routes the requests to the appropriate database service.


33. What is the purpose of the Oracle RMAN utility?

Answer: Oracle Recovery Manager (RMAN) is a utility that enables you to perform backup and recovery operations on Oracle databases. It provides a comprehensive and efficient method for protecting data.


34. How do you perform a full database backup using RMAN?

Answer: To perform a full database backup using RMAN, you can use the following commands:

RMAN> CONNECT TARGET /
RMAN> BACKUP DATABASE;

This will create a backup of the entire database.


35. Explain the difference between a hot backup and a cold backup.

Answer:

  • A hot backup is taken while the database is still running and accessible to users. It requires the use of tools like RMAN to create a consistent backup.
  • A cold backup is taken when the database is shut down. It involves copying all the necessary files while the database is not in use.

36. What is the purpose of the Oracle Alert Log?

Answer: The Oracle Alert Log is a file that contains messages and events generated by the database. It provides critical information about the database’s health, including error messages, startup and shutdown messages, and more.


37. How do you monitor and manage tablespaces in Oracle?

Answer: To monitor and manage tablespaces in Oracle, you can use the following SQL queries and commands:

  • To view information about tablespaces:
SELECT tablespace_name, status, contents, bytes
FROM dba_tablespaces;
  • To add a data file to a tablespace:
ALTER TABLESPACE tablespace_name
ADD DATAFILE 'path_to_file' SIZE size;
  • To resize a data file:
ALTER DATABASE DATAFILE 'path_to_file' RESIZE size;

38. Explain the purpose of the Oracle Flashback Technology.

Answer: Oracle Flashback Technology provides the ability to view and manipulate previous versions of database objects and to recover from logical errors. It allows you to revert a table or entire database to a previous point in time.


39. What is the purpose of the Oracle Automatic Workload Repository (AWR)?

Answer: AWR collects and maintains performance statistics for the database. It helps in diagnosing and resolving performance problems by providing a historical perspective on database performance.


40. How do you create a user in Oracle?

Answer: To create a user in Oracle, you can use the CREATE USER command:

CREATE USER user_name IDENTIFIED BY password;

You can then grant necessary privileges to the user.


41. How do you perform a point-in-time recovery using RMAN?

Answer: To perform a point-in-time recovery using RMAN, you can follow these steps:

  • Restore the database to a specific SCN (System Change Number) or timestamp:
RMAN> RUN {
       SET UNTIL SCN <SCN_number>; -- or SET UNTIL TIME 'yyyy-mm-dd:hh24:mi:ss';
       RESTORE DATABASE;
     }
  • Recover the database:
RMAN> RECOVER DATABASE;
  • Open the database with resetlogs:
RMAN> ALTER DATABASE OPEN RESETLOGS;

42. Explain the purpose of Oracle Data Pump.

Answer: Oracle Data Pump is a feature that provides high-speed, parallel export and import of data and metadata. It enables efficient backup and recovery, as well as data movement between databases.


43. How do you perform a full export of a schema using Data Pump?

Answer: To perform a full export of a schema using Data Pump, you can use the following command:

expdp username/password@db schemas=schema_name directory=dir_name dumpfile=expdp_schema.dmp logfile=expdp_schema.log

This will export all objects owned by the specified schema.


44. What is the purpose of the Oracle Data Dictionary?

Answer: The Oracle Data Dictionary is a collection of database tables and views that provide information about the database. It contains metadata, such as table names, column names, constraints, privileges, and more.


45. How do you find the size of a table in Oracle?

Answer: To find the size of a table in Oracle, you can use the following query:

SELECT segment_name, bytes/1024/1024 AS size_mb
FROM user_segments
WHERE segment_name = 'table_name';

This will display the size of the table in megabytes.


46. Explain the purpose of the Oracle Automatic Storage Management (ASM) feature.

Answer: ASM is a volume manager and file system for Oracle databases. It simplifies storage management and provides features like disk mirroring and striping, making it easier to manage Oracle database files.


47. How do you add a disk to an existing ASM disk group?

Answer: To add a disk to an existing ASM disk group, you can use the following command:

ALTER DISKGROUP disk_group_name ADD DISK 'path_to_disk';

This will add the specified disk to the ASM disk group.


48. What is the purpose of the Oracle SQL*Loader utility?

Answer: SQL*Loader is a utility for loading data from external files into Oracle databases. It provides a powerful method for bulk loading data, making it useful for data warehousing and data migration projects.


49. What is a redo log file in Oracle?

Answer: Redo log files are a crucial component of an Oracle database. They record all changes made to the database, allowing for recovery in the event of a failure. Redo log files ensure data integrity and help in maintaining consistency.


50. How do you switch a database to a different redo log file group?

Answer: To switch a database to a different redo log file group, you can use the following steps:

  1. Add the new redo log file group:
ALTER DATABASE ADD LOGFILE THREAD <thread_number> GROUP <group_number> SIZE <size> [MEMBER '<file_path>'];
  1. Drop the old redo log file group:
ALTER DATABASE DROP LOGFILE GROUP <old_group_number>;
  1. Switch the log file:
ALTER SYSTEM SWITCH LOGFILE;

51. Explain the purpose of the Oracle Control File.

Answer: The Control File is a small binary file that records the physical structure of the database. It contains information about data files, redo log files, and the database’s current state. It is critical for database recovery and startup.


52. How do you recompile all invalid objects in an Oracle database?

Answer: You can recompile all invalid objects in an Oracle database using the following PL/SQL block:

BEGIN
   FOR c IN (SELECT object_name, object_type FROM user_objects WHERE status = 'INVALID') 
   LOOP
      EXECUTE IMMEDIATE 'ALTER ' || c.object_type || ' ' || c.object_name || ' COMPILE';
   END LOOP;
END;

This block will loop through all invalid objects and recompile them.


53. What is the purpose of an Oracle listener?

Answer: The Oracle listener is a process that listens for incoming client connection requests to the database. It acts as an intermediary between the client and the database server, establishing connections and managing network traffic.


54. How do you start and stop an Oracle listener?

Answer: To start the listener, you can use the following command:

lsnrctl start

To stop the listener, use:

lsnrctl stop

55. Explain the purpose of the Oracle Flashback Technology.

Answer: Flashback Technology provides a way to view past states of the database or undo changes to the database. It allows for easy recovery from logical errors and provides a powerful tool for data auditing.


56. How do you monitor the performance of an Oracle database?

Answer: Oracle provides several tools for monitoring database performance. One commonly used tool is Oracle Enterprise Manager (OEM), which offers a graphical interface for monitoring various aspects of the database, including performance metrics, active sessions, and resource utilization.


57. Explain the purpose of the Oracle Data Dictionary.

Answer: The Oracle Data Dictionary is a set of read-only tables that provide information about the database. It contains metadata, such as details about tables, columns, constraints, users, and privileges. The Data Dictionary is a fundamental component of Oracle databases.


58. How do you perform a backup and recovery in Oracle?

Answer: Oracle offers various methods for backup and recovery, including:

  • RMAN (Recovery Manager): Oracle’s preferred tool for backup and recovery.
  • Export/Import: Used for logical backups and exports data in a platform-independent format.
  • Data Pump: A more efficient alternative to traditional export/import.

59. What is Automatic Storage Management (ASM) in Oracle?

Answer: ASM is a feature of Oracle Database that simplifies storage management. It provides a file system and volume manager specifically designed for Oracle database files. ASM manages the placement of data on disks and controls rebalancing operations.


60. How do you add a data file to a tablespace in Oracle?

Answer:

To add a data file to a tablespace, you can use the following SQL command:

ALTER TABLESPACE <tablespace_name> 
ADD DATAFILE '<file_path>' SIZE <size> AUTOEXTEND ON NEXT <next_size> MAXSIZE <max_size>;

This command adds a new data file to the specified tablespace.


61. Explain the purpose of the Oracle Undo tablespace.

Answer: The Undo tablespace is used to manage information needed for database recovery, rollback operations, and read consistency. It stores the undo information generated by DML operations and provides a consistent view of data.


62. How do you move a table to a different tablespace in Oracle?

Answer:

To move a table to a different tablespace, you can use the ALTER TABLE command:

ALTER TABLE <table_name> MOVE TABLESPACE <new_tablespace>;

This command relocates the table to the specified tablespace.


63. How do you enable and disable constraints in Oracle?

Answer:

To disable constraints:

ALTER TABLE <table_name> DISABLE CONSTRAINT <constraint_name>;

To enable constraints:

ALTER TABLE <table_name> ENABLE CONSTRAINT <constraint_name>;

64. Explain the difference between a primary key and a unique key constraint.

Answer:

  • Primary Key Constraint: Ensures that a column (or combination of columns) has a unique value in each row and cannot contain null values. A table can have only one primary key.
  • Unique Key Constraint: Requires that a column (or combination of columns) has unique values, but it allows one null value.

65. What is the purpose of the Oracle Flashback technology?

Answer: Flashback technology allows users to view or recover past states of objects or data within a specified time window. It includes features like Flashback Query, Flashback Versions Query, Flashback Table, and Flashback Database.


66. Explain the concept of Oracle Real Application Clusters (RAC).

Answer:

Oracle RAC is a database clustering solution that provides high availability and scalability by allowing multiple instances to access a single database. It allows for workload balancing and failover protection.


67. How do you gather statistics for a table in Oracle?

Answer:

To gather statistics for a table, you can use the DBMS_STATS package or the ANALYZE command:

Using DBMS_STATS:

BEGIN
   DBMS_STATS.GATHER_TABLE_STATS('<schema_name>', '<table_name>');
END;

Using ANALYZE:

ANALYZE TABLE <table_name> COMPUTE STATISTICS;

68. What is the purpose of the Oracle Data Pump utility?

Answer:

Oracle Data Pump is a tool used for fast data and metadata movement between Oracle databases. It provides the expdp and impdp commands for exporting and importing data and metadata.


69. Explain how you can identify long-running SQL queries in Oracle.

Answer:

You can identify long-running SQL queries by querying the V$SESSION_LONGOPS view or using Oracle Enterprise Manager (OEM) to monitor active sessions.


70. What is Automatic SQL Tuning in Oracle?

Answer:

Automatic SQL Tuning is a feature of Oracle Database that identifies and tunes high-load SQL statements that are consuming system resources. It uses SQL profiles and SQL plan directives to optimize execution plans.


71. How do you perform a hot backup in Oracle?

Answer:

A hot backup in Oracle is performed while the database is open and operational. It involves using tools like RMAN (Recovery Manager) to create a consistent backup.

To perform a hot backup using RMAN:

RMAN> CONNECT TARGET /
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

72. What is the purpose of the Oracle Automatic Storage Management (ASM)?

Answer:

ASM is a volume manager and file system designed for Oracle databases. It provides a foundation for highly efficient storage management and supports Oracle databases directly.


73. Explain the difference between a bitmap index and a B-tree index.

Answer:

  • Bitmap Index: Suitable for columns with low cardinality (few distinct values), where many rows have the same value. It’s space-efficient but can be slower for DML operations.
  • B-tree Index: Suitable for columns with high cardinality (many distinct values). It’s efficient for both retrieval and modification operations.

74. What is a Materialized View in Oracle?

Answer:

A materialized view is a database object that contains the results of a query. It’s a physical copy of the data, stored on disk, that can be refreshed to reflect changes in the underlying tables.

Creating a materialized view:

CREATE MATERIALIZED VIEW mv_name
AS SELECT columns
FROM table_name;

75. How do you perform a point-in-time recovery (PITR) in Oracle?

Answer:

Point-in-time recovery allows you to restore a database to a specific point in time. It involves restoring a backup and applying archived logs.

Steps for PITR:

  1. Restore the backup.
  2. Recover the database until the desired time.
  3. Open the database with the RESETLOGS option.

76. Explain the purpose of the Oracle Database Control File.

Answer:

The control file is a small binary file that records the physical structure of the database. It contains metadata, such as the database name, data file names, log file names, and timestamps.


77. What is an Oracle Data Guard?

Answer:

Oracle Data Guard is a high availability and disaster recovery solution. It provides a standby database that is synchronized with the primary database, ensuring continuity in case of a failure.


78. How do you monitor performance in Oracle?

Answer:

Performance can be monitored using tools like Oracle Enterprise Manager (OEM), Statspack, Automatic Workload Repository (AWR), and various dynamic performance views (e.g., V$ views).


79. How do you perform table partitioning in Oracle?

Answer:

Table partitioning involves dividing a large table into smaller, more manageable pieces. It improves query performance and simplifies maintenance.

Example of range partitioning:

CREATE TABLE sales (
   sale_id NUMBER,
   sale_date DATE,
   amount NUMBER
)
PARTITION BY RANGE (sale_date)
(
   PARTITION sales_q1 VALUES LESS THAN (TO_DATE('01-APR-2023', 'DD-MON-YYYY')),
   PARTITION sales_q2 VALUES LESS THAN (TO_DATE('01-JUL-2023', 'DD-MON-YYYY')),
   PARTITION sales_q3 VALUES LESS THAN (TO_DATE('01-OCT-2023', 'DD-MON-YYYY')),
   PARTITION sales_q4 VALUES LESS THAN (MAXVALUE)
);

80. What is the purpose of Oracle Data Pump?

Answer:

Oracle Data Pump is a utility for fast, high-speed data and metadata movement between Oracle databases. It includes the expdp (export) and impdp (import) commands.

Exporting a schema:

expdp username/password@db schemas=schema_name directory=dpump_dir dumpfile=schema_dump.dmp logfile=expdp_schema.log

81. Explain the difference between a role and a privilege in Oracle.

Answer:

  • Privilege: A privilege is a specific right to perform a particular action on a database object. Examples include SELECT, INSERT, and CREATE.
  • Role: A role is a named group of related privileges. Roles simplify the process of granting and revoking privileges.

Creating a role:

CREATE ROLE role_name;

Granting a privilege to a role:

GRANT privilege_name TO role_name;

82. What is Oracle Real Application Clusters (RAC)?

Answer:

Oracle RAC is a database clustering solution that allows multiple instances to access a single Oracle database simultaneously. It provides high availability and scalability.


83. How do you monitor and manage tablespaces in Oracle?

Answer:

Tablespaces can be monitored using the DBA_TABLESPACES view. To manage them, you can perform operations like adding data files, resizing, and taking offline/online.

Adding a data file to a tablespace:

ALTER TABLESPACE tablespace_name
ADD DATAFILE 'path_to_file/file_name.dbf' SIZE 100M;

84. Explain the purpose of the Oracle listener.

Answer:

The Oracle listener is a process that listens for incoming client connection requests. It determines which database instance should handle the request.


85. How do you implement database security in Oracle?

Answer:

Database security in Oracle involves activities like creating strong passwords, implementing role-based access control, and using features like Virtual Private Database (VPD) for fine-grained access control.

Example of creating a user with a password:

CREATE USER username IDENTIFIED BY password;

86. How do you perform a backup and recovery in Oracle?

Answer:

Oracle offers various methods for backup and recovery. Using RMAN (Recovery Manager) is a common approach.

Example of performing a full database backup:

RMAN> CONNECT TARGET /
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

87. What is Automatic Storage Management (ASM) in Oracle?

Answer:

ASM is a feature that simplifies the management of Oracle database files. It provides a file system and volume manager specifically designed for Oracle database files.

Creating an ASM disk group:

CREATE DISKGROUP disk_group_name
  NORMAL REDUNDANCY
  DISK 'disk1' NAME disk1, 'disk2' NAME disk2;

88. Explain how you would perform a data import in Oracle.

Answer:

You can use the impdp utility for data imports. It allows you to specify various parameters like directory, dump file, and remap.

Example of importing a schema:

impdp username/password@db schemas=schema_name directory=dpump_dir dumpfile=schema_dump.dmp logfile=impdp_schema.log

89. What is Oracle Flashback Technology?

Answer:

Flashback Technology provides a way to view past versions of database objects or to return objects to a previous state. It’s useful for data recovery and auditing.

Example of using Flashback Query:

SELECT * FROM table_name AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE);

90. How do you handle Oracle database performance tuning?

Answer:

Performance tuning involves activities like optimizing SQL queries, adding indexes, and adjusting memory settings. The EXPLAIN PLAN command is often used to analyze query execution plans.

Example of using EXPLAIN PLAN:

EXPLAIN PLAN FOR
SELECT * FROM table_name WHERE column = 'value';

91. Explain the purpose of Oracle Enterprise Manager (OEM).

Answer:

Oracle Enterprise Manager is a web-based tool used for monitoring and managing Oracle databases. It provides a graphical interface for tasks like performance monitoring and backup management.


92. How do you handle database locks in Oracle?

Answer:

Oracle provides various types of locks like row locks and table locks. The DBA_LOCKS view can be used to monitor locks.

Example of releasing a lock:

ALTER SYSTEM KILL SESSION 'sid,serial#';

93. What is the purpose of the Oracle Data Dictionary?

Answer:

The Data Dictionary is a collection of database tables and views containing metadata, such as information about the structure of the database, its objects, security, and user access privileges.

Example of querying the Data Dictionary:

SELECT table_name, column_name, data_type
FROM all_tab_columns
WHERE table_name = 'your_table';

94. Explain the difference between a Hot Backup and a Cold Backup in Oracle.

Answer:

  • Hot Backup: A hot backup is taken while the database is open and operational. It uses technologies like RMAN to create a consistent copy of the database. Example of a hot backup:
  RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
  • Cold Backup: A cold backup is taken when the database is shut down. It involves physically copying the database files. Example of copying data files for a cold backup:
  -- Copy data files to a backup location
  cp /oracle/oradata/prod/system01.dbf /backup_location

95. How do you monitor the performance of an Oracle database?

Answer:

Oracle provides tools like Enterprise Manager and command-line utilities like AWR (Automatic Workload Repository) and Statspack for performance monitoring.

Example of generating an AWR report:

-- Connect as sysdba
SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

96. What is the purpose of the Oracle Listener?

Answer:

The Oracle Listener is a process that listens for incoming client connection requests and manages the traffic of these requests to the database.

Example of starting the listener:

LSNRCTL> START;

97. How do you handle tablespace management in Oracle?

Answer:

Tablespaces are logical storage units within an Oracle database. You can create, alter, and drop tablespaces.

Example of creating a tablespace:

CREATE TABLESPACE ts_name
  DATAFILE 'path_to_datafile.dbf' SIZE 100M;

98. Explain the purpose of Oracle Redo Logs.

Answer:

Redo logs record all changes made to the database, which allows Oracle to recover from failures. They play a crucial role in maintaining data integrity.

Example of viewing redo log information:

SELECT group#, thread#, sequence#, bytes, status
FROM v$log;

99. How do you perform a full database export using the Data Pump utility?

Answer:

To perform a full database export using Data Pump, you can use the expdp command. Here’s an example:

expdp username/password@db_full_export directory=dpump_dir dumpfile=full_export.dmp logfile=full_export.log full=y

This command exports the entire database. Make sure to replace username, password, db_full_export, dpump_dir, full_export.dmp, and full_export.log with your specific details.


100. What is the purpose of the Oracle DBMS_SCHEDULER?

Answer:

DBMS_SCHEDULER is a built-in package in Oracle that provides a flexible job scheduling environment. It enables you to automate a wide range of tasks within the database.

Example of creating a job using DBMS_SCHEDULER:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name          => 'my_job',
    job_type          => 'PLSQL_BLOCK',
    job_action        => 'BEGIN my_procedure; END;',
    start_date        => SYSTIMESTAMP,
    repeat_interval   => 'FREQ=DAILY; BYHOUR=12',
    end_date          => NULL,
    enabled           => TRUE,
    comments          => 'Job to run my_procedure daily at noon'
  );
END;
/