Top 150+ Oracle DBA, Developer, and Designer Interview Questions

 

Table of Contents

1. Beginner-Level Questions

   • Oracle DBA

   • Oracle Developer

   • Oracle Designer

2. Intermediate-Level Questions

   • Oracle DBA

   • Oracle Developer

   • Oracle Designer

3. Expert-Level Questions

   • Oracle DBA

   • Oracle Developer

   • Oracle Designer

4. Conclusion

---

Beginner-Level Questions

These questions cover foundational concepts, basic SQL/PL-SQL, and core Oracle principles for entry-level candidates.

Oracle DBA (Beginner)

1. What is an Oracle Database, and what are its key components?

   • Type: Basic Understanding

   • Expected Answer: An Oracle Database is a relational database management system (RDBMS) storing data in datafiles, redo logs, and control files, managed by an instance (SGA, background processes).

   • Example: Datafiles store tables, redo logs track changes, and control files maintain database structure.

2. What is the difference between an Oracle instance and a database?

   • Type: Conceptual

   • Expected Answer: An instance is the memory (SGA, PGA) and processes, while the database is the physical files (datafiles, redo logs, control files).

3. What are the roles of DBWR and LGWR processes?

   • Type: Conceptual

   • Expected Answer: DBWR writes modified data to datafiles, while LGWR writes redo entries to redo logs for recovery.

4. What is a tablespace in Oracle?

   • Type: Basic Understanding

   • Expected Answer: A tablespace is a logical storage unit containing datafiles that store database objects like tables and indexes.

5. What is the purpose of a control file?

   • Type: Conceptual

   • Expected Answer: Control files track the database’s physical structure, including datafile and redo log locations.

6. What is the SYSTEM tablespace used for?

   • Type: Basic Understanding

   • Expected Answer: The SYSTEM tablespace stores the data dictionary, containing metadata about database objects.

7. What is a redo log, and why is it important?

   • Type: Conceptual

   • Expected Answer: Redo logs record all changes for recovery in case of failure, ensuring data integrity.

8. What is the difference between a user and a schema in Oracle?

   • Type: Conceptual

   • Expected Answer: A user is an account for accessing the database, while a schema is the collection of objects owned by that user.

9. What is the purpose of the init.ora file?

   • Type: Basic Understanding

   • Expected Answer: The init.ora file contains initialization parameters for configuring the Oracle instance.

10. What is a checkpoint in Oracle?

    • Type: Conceptual

    • Expected Answer: A checkpoint synchronizes datafiles with memory, ensuring consistency during recovery.

Oracle Developer (Beginner)

11. What is PL/SQL, and how does it differ from SQL?

    • Type: Basic Understanding

    • Expected Answer: PL/SQL is Oracle’s procedural extension to SQL, adding control structures like loops and conditionals.

    • Example:

      BEGIN
        DBMS_OUTPUT.PUT_LINE('Hello, PL/SQL!');
      END;

12. What is a stored procedure in Oracle?

    • Type: Conceptual

    • Expected Answer: A stored procedure is a precompiled PL/SQL block stored in the database for reusable logic.

13. What is the difference between a function and a procedure?

    • Type: Conceptual

    • Expected Answer: A function returns a value and can be used in SQL, while a procedure does not return a value.

    • Example:

      CREATE FUNCTION get_salary(emp_id NUMBER) RETURN NUMBER IS
        sal NUMBER;
      BEGIN
        SELECT salary INTO sal FROM employees WHERE employee_id = emp_id;
        RETURN sal;
      END;

14. What is a trigger in Oracle?

    • Type: Conceptual

    • Expected Answer: A trigger is a PL/SQL block that automatically executes in response to specific database events.

    • Example:

      CREATE TRIGGER log_update
      AFTER UPDATE ON employees
      FOR EACH ROW
      BEGIN
        INSERT INTO audit_log VALUES (:OLD.employee_id, SYSDATE);
      END;

15. What is a cursor in PL/SQL?

    • Type: Conceptual

    • Expected Answer: A cursor is a pointer to a result set, used to process query results row by row.

    • Example:

      DECLARE
        CURSOR emp_cursor IS SELECT employee_id FROM employees;
      BEGIN
        FOR emp IN emp_cursor LOOP
          DBMS_OUTPUT.PUT_LINE(emp.employee_id);
        END LOOP;
      END;

16. What is the purpose of the SELECT ... FOR UPDATE statement?

    • Type: Practical

    • Expected Answer: It locks rows to prevent updates by other sessions until the transaction completes.

17. What is a view in Oracle?

    • Type: Basic Understanding

    • Expected Answer: A view is a virtual table based on a query, storing no data itself.

    • Example:

      CREATE VIEW emp_view AS SELECT employee_id, first_name FROM employees;

18. What is the difference between DELETE and TRUNCATE?

    • Type: Conceptual

    • Expected Answer: DELETE removes specific rows with rollback capability, while TRUNCATE removes all rows without logging.

19. What is an index, and why is it used?

    • Type: Basic Understanding

    • Expected Answer: An index improves query performance by allowing faster data retrieval.

    • Example:

      CREATE INDEX idx_emp_id ON employees(employee_id);

20. What is the purpose of the EXPLAIN PLAN statement?

    • Type: Practical

    • Expected Answer: It shows the execution plan for a query, helping optimize performance.

    • Example:

      EXPLAIN PLAN FOR SELECT * FROM employees WHERE employee_id = 100;

Oracle Designer (Beginner)

21. What is database normalization, and why is it important?

    • Type: Basic Understanding

    • Expected Answer: Normalization organizes data to eliminate redundancy and ensure integrity using normal forms (1NF, 2NF, 3NF).

    • Example: Splitting a table into orders and customers to avoid duplicate customer data.

22. What is a primary key in Oracle?

    • Type: Conceptual

    • Expected Answer: A primary key uniquely identifies each row in a table and enforces data integrity.

23. What is a foreign key, and how does it work?

    • Type: Conceptual

    • Expected Answer: A foreign key links two tables, ensuring referential integrity.

    • Example:

      CREATE TABLE orders (
        order_id NUMBER PRIMARY KEY,
        customer_id NUMBER,
        FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
      );

24. What is the difference between a logical and physical data model?

    • Type: Conceptual

    • Expected Answer: A logical model defines data structure conceptually, while a physical model specifies storage details.

25. What is an ERD (Entity-Relationship Diagram)?

    • Type: Basic Understanding

    • Expected Answer: An ERD visually represents entities, attributes, and relationships in a database.

26. What is a materialized view, and when is it used?

    • Type: Conceptual

    • Expected Answer: A materialized view stores query results physically, used for performance in complex queries.

    • Example:

      CREATE MATERIALIZED VIEW mv_sales AS SELECT SUM(amount) FROM sales;

27. What is the purpose of a schema in database design?

    • Type: Conceptual

    • Expected Answer: A schema organizes database objects under a user, defining structure and ownership.

28. What is the role of constraints in Oracle?

    • Type: Basic Understanding

    • Expected Answer: Constraints enforce rules like uniqueness, nullability, or referential integrity.

    • Example:

      ALTER TABLE employees ADD CONSTRAINT emp_pk PRIMARY KEY (employee_id);

29. What is data modeling, and why is it important?

    • Type: Basic Understanding

    • Expected Answer: Data modeling designs the database structure to meet business needs, ensuring efficiency and scalability.

30. What is the difference between 1NF and 2NF?

    • Type: Conceptual

    • Expected Answer: 1NF eliminates repeating groups, while 2NF ensures non-key attributes depend on the entire primary key.

---

Intermediate-Level Questions

These questions target candidates with 2–5 years of experience, focusing on practical scenarios, optimization, and advanced Oracle features.

Oracle DBA (Intermediate)

31. How do you monitor database performance in Oracle?

    • Type: Practical

    • Expected Answer: Use tools like Enterprise Manager, AWR reports, or V$SESSION views to monitor performance metrics.

    • Example:

      SELECT sql_id, executions, cpu_time FROM v$sql WHERE executions > 0;

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

    • Type: Conceptual

    • Expected Answer: AWR collects and stores performance statistics for analysis and tuning.

33. How do you handle a database startup failure due to a missing datafile?

    • Type: Scenario-Based

    • Expected Answer: Restore the datafile from a backup or recreate it if it’s non-critical, then recover the database.

    • Example:

      ALTER DATABASE DATAFILE 'datafile_path' OFFLINE;

34. What is RMAN, and how is it used for backups?

    • Type: Practical

    • Expected Answer: RMAN (Recovery Manager) automates backup and recovery tasks.

    • Example:

      RMAN> BACKUP DATABASE;

35. What are the different database shutdown modes in Oracle?

    • Type: Conceptual

    • Expected Answer: Modes include NORMAL, TRANSACTIONAL, IMMEDIATE, and ABORT, each controlling session termination.

36. How do you configure a standby database in Oracle Data Guard?

    • Type: Scenario-Based

    • Expected Answer: Create a physical standby, configure redo transport, and enable Data Guard.

    • Example:

      ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

37. What is the role of the redo log buffer?

    • Type: Conceptual

    • Expected Answer: The redo log buffer temporarily stores redo entries before they are written to redo logs by LGWR.

38. How do you manage tablespace growth?

    • Type: Practical

    • Expected Answer: Enable autoextend, monitor space usage, or add datafiles.

    • Example:

      ALTER TABLESPACE users ADD DATAFILE 'users02.dbf' SIZE 100M AUTOEXTEND ON;

39. What is the difference between a hot and cold backup?

    • Type: Conceptual

    • Expected Answer: Hot backup occurs while the database is running, while cold backup requires the database to be shut down.

40. How do you troubleshoot a slow-running query?

    • Type: Scenario-Based

    • Expected Answer: Use EXPLAIN PLAN, check indexes, and analyze statistics.

    • Example:

      SELECT * FROM v$session_longops WHERE time_remaining > 0;

Oracle Developer (Intermediate)

41. How do you optimize a PL/SQL block for performance?

    • Type: Practical

    • Expected Answer: Use bulk collect, minimize context switches, and avoid cursors when possible.

    • Example:

      DECLARE
        TYPE emp_tab IS TABLE OF employees%ROWTYPE;
        emp_data emp_tab;
      BEGIN
        SELECT * BULK COLLECT INTO emp_data FROM employees;
      END;

42. What is a REF cursor, and how is it used?

    • Type: Conceptual

    • Expected Answer: A REF cursor is a dynamic cursor for flexible query results.

    • Example:

      CREATE PROCEDURE get_employees(dep_id NUMBER, emp_ref OUT SYS_REFCURSOR) IS
      BEGIN
        OPEN emp_ref FOR SELECT * FROM employees WHERE department_id = dep_id;
      END;

43. How do you handle exceptions in PL/SQL?

    • Type: Practical

    • Expected Answer: Use EXCEPTION blocks to catch and handle errors.

    • Example:

      BEGIN
        SELECT salary INTO v_salary FROM employees WHERE employee_id = 999;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          DBMS_OUTPUT.PUT_LINE('Employee not found');
      END;

44. What is an autonomous transaction in PL/SQL?

    • Type: Conceptual

    • Expected Answer: An autonomous transaction is an independent transaction within a PL/SQL block.

    • Example:

      CREATE PROCEDURE log_action IS
        PRAGMA AUTONOMOUS_TRANSACTION;
      BEGIN
        INSERT INTO audit_log VALUES (SYSDATE);
        COMMIT;
      END;

45. How do you implement pagination in a query?

    • Type: Coding Challenge

    • Expected Answer:

      SELECT * FROM (
        SELECT e.*, ROWNUM rn FROM employees e WHERE ROWNUM <= 20
      ) WHERE rn > 10;

46. What is the difference between a view and a materialized view?

    • Type: Conceptual

    • Expected Answer: A view is virtual, while a materialized view stores data physically.

47. How do you use dynamic SQL in PL/SQL?

    • Type: Practical

    • Expected Answer: Use EXECUTE IMMEDIATE for dynamic queries.

    • Example:

      EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || table_name INTO v_count;

48. What is the purpose of the MERGE statement?

    • Type: Conceptual

    • Expected Answer: MERGE updates or inserts data based on a condition.

    • Example:

      MERGE INTO employees e
      USING new_employees ne
      ON (e.employee_id = ne.employee_id)
      WHEN MATCHED THEN UPDATE SET e.salary = ne.salary
      WHEN NOT MATCHED THEN INSERT VALUES (ne.employee_id, ne.salary);

49. How do you create a sequence in Oracle?

    • Type: Practical

    • Expected Answer:

      CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1;

50. What is the difference between INNER JOIN and OUTER JOIN?

    • Type: Conceptual

    • Expected Answer: INNER JOIN returns matching rows, while OUTER JOIN includes non-matching rows.

Oracle Designer (Intermediate)

51. How do you design a database schema for a multi-tenant application?

    • Type: Scenario-Based

    • Expected Answer: Use separate schemas, shared tables with tenant IDs, or separate databases.

    • Example:

      CREATE TABLE tenants (
        tenant_id NUMBER PRIMARY KEY,
        tenant_name VARCHAR2(100)
      );

52. What is the role of indexing in database design?

    • Type: Conceptual

    • Expected Answer: Indexes improve query performance but increase storage and maintenance overhead.

53. How do you handle denormalization in database design?

    • Type: Scenario-Based

    • Expected Answer: Denormalize for performance in read-heavy systems, balancing redundancy and maintenance.

54. What is a composite key, and when is it used?

    • Type: Conceptual

    • Expected Answer: A composite key uses multiple columns to uniquely identify rows, used when no single column is unique.

55. How do you model a many-to-many relationship?

    • Type: Practical

    • Expected Answer: Use a junction table with foreign keys.

    • Example:

      CREATE TABLE student_courses (
        student_id NUMBER,
        course_id NUMBER,
        PRIMARY KEY (student_id, course_id)
      );

56. What is the role of partitioning in Oracle?

    • Type: Conceptual

    • Expected Answer: Partitioning divides large tables for better performance and manageability.

    • Example:

      CREATE TABLE sales (
        sale_id NUMBER,
        sale_date DATE
      ) PARTITION BY RANGE (sale_date) (
        PARTITION p1 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))
      );

57. How do you ensure data integrity in a database design?

    • Type: Practical

    • Expected Answer: Use constraints, triggers, and foreign keys.

58. What is the difference between BCNF and 3NF?

    • Type: Conceptual

    • Expected Answer: BCNF is stricter, ensuring no non-trivial functional dependencies exist.

59. How do you design a database for scalability?

    • Type: Scenario-Based

    • Expected Answer: Use partitioning, indexing, and sharding techniques.

60. What is the purpose of a data dictionary in Oracle?

    • Type: Conceptual

    • Expected Answer: The data dictionary stores metadata about database objects for management and querying.

---

Expert-Level Questions

These questions challenge senior professionals with complex scenarios, advanced Oracle features, and critical system design tasks.

Oracle DBA (Expert)

61. How do you implement Oracle RAC for high availability?

    • Type: Scenario-Based

    • Expected Answer: Configure Real Application Clusters with shared storage and multiple nodes.

    • Example:

      srvctl add database -d mydb -o $ORACLE_HOME

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

    • Type: Conceptual

    • Expected Answer: ASM manages disk groups for efficient storage and redundancy.

63. How do you recover a database from a lost control file?

    • Type: Scenario-Based

    • Expected Answer: Restore from a backup or recreate the control file.

    • Example:

      CREATE CONTROLFILE REUSE DATABASE "mydb" RESETLOGS;

64. What is the role of Flashback Database in Oracle?

    • Type: Conceptual

    • Expected Answer: Flashback Database allows point-in-time recovery using flashback logs.

    • Example:

      FLASHBACK DATABASE TO TIMESTAMP SYSDATE - 1/24;

65. How do you optimize memory usage in Oracle?

    • Type: Practical

    • Expected Answer: Tune SGA and PGA using AMM or manual allocation.

    • Example:

      ALTER SYSTEM SET memory_target=2G SCOPE=spfile;

66. What is the difference between physical and logical backups?

    • Type: Conceptual

    • Expected Answer: Physical backups copy files, while logical backups export data (e.g., Data Pump).

67. How do you implement Oracle Grid Infrastructure?

    • Type: Scenario-Based

    • Expected Answer: Install Grid Infrastructure for ASM and RAC, configuring clusterware.

68. What is the role of AWR snapshots in performance tuning?

    • Type: Practical

    • Expected Answer: AWR snapshots provide historical performance data for analysis.

    • Example:

      EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

69. How do you handle a corrupted data block?

    • Type: Scenario-Based

    • Expected Answer: Use RMAN block recovery or rebuild the table.

    • Example:

      RMAN> RECOVER DATAFILE 1 BLOCK 123;

70. What is the role of the Oracle Optimizer?

    • Type: Conceptual

    • Expected Answer: The Optimizer generates the best execution plan for SQL queries.

Oracle Developer (Expert)

71. How do you implement bulk binding in PL/SQL for performance?

    • Type: Coding Challenge

    • Expected Answer:

      DECLARE
        TYPE t_ids IS TABLE OF NUMBER;
        ids t_ids;
      BEGIN
        SELECT employee_id BULK COLLECT INTO ids FROM employees;
        FORALL i IN ids.FIRST..ids.LAST
          UPDATE employees SET salary = salary * 1.1 WHERE employee_id = ids(i);
      END;

72. What is the difference between a package and a procedure?

    • Type: Conceptual

    • Expected Answer: A package groups related procedures and functions, offering modularity and state persistence.

73. How do you implement parallel execution in PL/SQL?

    • Type: Practical

    • Expected Answer: Use the PARALLEL_ENABLE clause in procedures.

    • Example:

      CREATE PROCEDURE process_data PARALLEL_ENABLE IS
      BEGIN
        -- Parallel processing logic
      END;

74. What is the role of pipelined table functions?

    • Type: Conceptual

    • Expected Answer: Pipelined functions return data incrementally for performance in queries.

    • Example:

      CREATE FUNCTION get_rows RETURN t_rows PIPELINED IS
      BEGIN
        FOR i IN 1..100 LOOP
          PIPE ROW (i);
        END LOOP;
      END;

75. How do you handle deadlock scenarios in Oracle?

    • Type: Scenario-Based

    • Expected Answer: Detect deadlocks with V$LOCK and resolve by killing sessions or redesigning logic.

    • Example:

      SELECT sid, serial# FROM v$session WHERE sid IN (SELECT blocking_session FROM v$session);

76. What is the difference between BULK COLLECT and FORALL?

    • Type: Conceptual

    • Expected Answer: BULK COLLECT fetches data in bulk, while FORALL executes DML in bulk.

77. How do you implement a custom aggregate function?

    • Type: Coding Challenge

    • Expected Answer:

      CREATE TYPE CustomAgg AS OBJECT (
        total NUMBER,
        MEMBER PROCEDURE initialize,
        MEMBER PROCEDURE iterate(value NUMBER)
      );

78. What is the role of DBMS_SCHEDULER in Oracle?

    • Type: Practical

    • Expected Answer: DBMS_SCHEDULER manages automated tasks and jobs.

    • Example:

      BEGIN
        DBMS_SCHEDULER.create_job(
          job_name => 'my_job',
          job_type => 'PLSQL_BLOCK',
          job_action => 'BEGIN my_proc; END;'
        );
      END;

79. How do you secure PL/SQL code?

    • Type: Practical

    • Expected Answer: Use WRAP to obfuscate code or grant execute permissions selectively.

    • Example:

      WRAP INAME my_proc.sql

80. What is the difference between a correlated and non-correlated subquery?

    • Type: Conceptual

    • Expected Answer: A correlated subquery references the outer query, executed per row, while a non-correlated subquery is independent.

Oracle Designer (Expert)

81. How do you design a database for high availability?

    • Type: Scenario-Based

    • Expected Answer: Use RAC, Data Guard, and partitioning for redundancy and performance.

82. What is the role of sharding in Oracle?

    • Type: Conceptual

    • Expected Answer: Sharding distributes data across multiple databases for scalability.

83. How do you implement data archiving in Oracle?

    • Type: Practical

    • Expected Answer: Use partitioning and ILM (Information Lifecycle Management).

    • Example:

      ALTER TABLE sales MODIFY PARTITION p1 ILM ADD POLICY MOVE TO archive_ts;

84. What is the difference between logical and physical standby databases?

    • Type: Conceptual

    • Expected Answer: Logical standby applies SQL, while physical standby applies redo logs.

85. How do you design a database for real-time analytics?

    • Type: Scenario-Based

    • Expected Answer: Use in-memory options, materialized views, and optimized indexes.

86. What is the role of Oracle’s In-Memory Database feature?

    • Type: Conceptual

    • Expected Answer: In-Memory stores data in a columnar format for faster analytics.

    • Example:

      ALTER TABLE sales INMEMORY;

87. How do you model a temporal database?

    • Type: Practical

    • Expected Answer: Use valid-time and transaction-time columns.

    • Example:

      CREATE TABLE employee_history (
        emp_id NUMBER,
        valid_from DATE,
        valid_to DATE
      );

88. What is the difference between OLTP and OLAP in database design?

    • Type: Conceptual

    • Expected Answer: OLTP handles transactional data, while OLAP supports analytical queries.

89. How do you handle schema evolution in a production database?

    • Type: Scenario-Based

    • Expected Answer: Use online redefinition or backward-compatible changes.

    • Example:

      BEGIN
        DBMS_REDEFINITION.start_redef_table('HR', 'employees', 'emp_temp');
      END;

90. What is the role of global temporary tables in Oracle?

    • Type: Conceptual

    • Expected Answer: Global temporary tables store session-specific data, cleared on commit or session end.

91. How do you design a database for GDPR compliance?

    • Type: Scenario-Based

    • Expected Answer: Implement data masking, encryption, and audit trails.

    • Example:

      BEGIN
        DBMS_TDP.ADD_MASKING_POLICY('employees', 'salary', 'MASKED WITH DBMS_CRYPTO');
      END;

92. What is the role of Oracle’s Virtual Private Database (VPD)?

    • Type: Conceptual

    • Expected Answer: VPD enforces row-level security dynamically.

    • Example:

      BEGIN
        DBMS_RLS.ADD_POLICY('employees', 'emp_policy', 'HR', 'emp_access');
      END;

93. How do you design a database for audit logging?

    • Type: Practical

    • Expected Answer: Use triggers or Oracle Audit Vault.

    • Example:

      AUDIT ALL ON employees BY ACCESS;

94. What is the difference between a clustered and non-clustered index?

    • Type: Conceptual

    • Expected Answer: Clustered indexes determine data storage order, while non-clustered indexes are separate structures.

95. How do you optimize a star schema for data warehousing?

    • Type: Scenario-Based

    • Expected Answer: Use bitmap indexes and partitioning for fact and dimension tables.

96. What is the role of Oracle’s Data Pump?

    • Type: Conceptual

    • Expected Answer: Data Pump exports and imports data and metadata efficiently.

    • Example:

      expdp hr/hr DIRECTORY=dpump_dir DUMPFILE=hr.dmp

97. How do you design a database for scalability across regions?

    • Type: Scenario-Based

    • Expected Answer: Use Oracle GoldenGate for replication and sharding.

98. What is the difference between a bitmap and B-tree index?

    • Type: Conceptual

    • Expected Answer: Bitmap indexes are efficient for low-cardinality columns, while B-tree indexes suit high-cardinality columns.

99. How do you implement row-level security?

    • Type: Practical

    • Expected Answer: Use VPD or application logic.

    • Example:

      CREATE FUNCTION emp_access (schema VARCHAR2, tab VARCHAR2) RETURN VARCHAR2 IS
      BEGIN
        RETURN 'department_id = SYS_CONTEXT(''USERENV'', ''SESSION_USER'')';
      END;

100. What is the role of Oracle’s Advanced Compression?

     • Type: Conceptual

     • Expected Answer: Advanced Compression reduces storage and improves performance for large datasets.

101. How do you design a database for high-concurrency applications?

     • Type: Scenario-Based

     • Expected Answer: Use connection pooling, optimistic locking, and partitioning.

102. What is the role of Oracle’s Multitenant Architecture?

     • Type: Conceptual

     • Expected Answer: Multitenant allows multiple pluggable databases (PDBs) within a container database (CDB).

103. How do you handle data migration across Oracle versions?

     • Type: Scenario-Based

     • Expected Answer: Use Data Pump or transportable tablespaces.

     • Example:

     impdp hr/hr DIRECTORY=dpump_dir DUMPFILE=hr.dmp

104. What is the role of Oracle’s Real Application Testing?

     • Type: Conceptual

     • Expected Answer: Real Application Testing captures and replays workloads for performance testing.

105. How do you design a database for IoT data?

     • Type: Scenario-Based

     • Expected Answer: Use time-series partitioning and JSON storage for high-volume, unstructured data.

Oracle Developer (Expert, Continued)

106. How do you implement a custom PL/SQL scheduler?

     • Type: Coding Challenge

     • Expected Answer:

       BEGIN
         DBMS_SCHEDULER.create_schedule(
           schedule_name => 'daily_run',
           repeat_interval => 'FREQ=DAILY;BYHOUR=0'
         );
       END;

107. What is the role of Oracle’s JSON support in PL/SQL?

     • Type: Conceptual

     • Expected Answer: JSON support enables parsing and querying JSON data in PL/SQL.

     • Example:

       SELECT JSON_VALUE(data, '$.name') FROM json_table;

108. How do you handle large data sets in PL/SQL?

     • Type: Scenario-Based

     • Expected Answer: Use bulk operations, parallel execution, and temporary tables.

109. What is the difference between a global and local temporary table?

     • Type: Conceptual

     • Expected Answer: Global temporary tables are session-specific, while local temporary tables are transaction-specific.

110. How do you implement a PL/SQL REST API?

     • Type: Coding Challenge

     • Expected Answer:

       CREATE PROCEDURE rest_api AS
         req utl_http.req;
         res utl_http.resp;
       BEGIN
         req := utl_http.begin_request('http://api.example.com', 'GET');
         res := utl_http.get_response(req);
       END;

111. What is the role of DBMS_UTILITY in PL/SQL?

     • Type: Conceptual

     • Expected Answer: DBMS_UTILITY provides utility functions like analyzing objects or compiling schemas.

112. How do you implement error logging in PL/SQL?

     • Type: Practical

     • Expected Answer: Use DBMS_ERRLOG for DML error logging.

     • Example:

       CREATE TABLE err_log AS SELECT * FROM employees WHERE 1=0;
       EXEC DBMS_ERRLOG.create_error_log('employees', 'err_log');

113. What is the difference between ROWID and ROWNUM?

     • Type: Conceptual

     • Expected Answer: ROWID is a unique physical address, while ROWNUM is a sequential number for query results.

114. How do you implement a PL/SQL job chain?

     • Type: Practical

     • Expected Answer:

       BEGIN
         DBMS_SCHEDULER.create_chain(chain_name => 'my_chain');
       END;

115. What is the role of UTL_FILE in PL/SQL?

     • Type: Conceptual

     • Expected Answer: UTL_FILE handles file I/O operations.

     • Example:

       DECLARE
         file UTL_FILE.FILE_TYPE;
       BEGIN
         file := UTL_FILE.FOPEN('MY_DIR', 'output.txt', 'w');
       END;

Oracle Designer (Expert, Continued)

116. How do you design a database for microservices?

     • Type: Scenario-Based

     • Expected Answer: Use separate schemas or databases per microservice with API-driven integration.

117. What is the role of Oracle’s Partition Exchange?

     • Type: Conceptual

     • Expected Answer: Partition Exchange swaps data between tables and partitions for fast loading.

118. How do you implement a data warehouse in Oracle?

     • Type: Scenario-Based

     • Expected Answer: Use star schemas, materialized views, and parallel queries.

119. What is the difference between a snowflake and star schema?

     • Type: Conceptual

     • Expected Answer: Star schema uses denormalized dimension tables, while snowflake normalizes them.

120. How do you design a database for time-series data?

     • Type: Practical

     • Expected Answer:

       CREATE TABLE sensor_data (
         sensor_id NUMBER,
         timestamp DATE,
         value NUMBER
       ) PARTITION BY RANGE (timestamp) INTERVAL (NUMTODSINTERVAL(1, 'DAY'));

121. What is the role of Oracle’s Advanced Queuing?

     • Type: Conceptual

     • Expected Answer: Advanced Queuing enables asynchronous messaging between applications.

122. How do you handle cross-database queries in Oracle?

     • Type: Practical

     • Expected Answer: Use database links.

     • Example:

       CREATE DATABASE LINK remote_db CONNECT TO hr IDENTIFIED BY hr USING 'remote_tns';

123. What is the role of Oracle’s Data Guard Broker?

     • Type: Conceptual

     • Expected Answer: Data Guard Broker automates management of standby databases.

124. How do you design a database for audit compliance?

     • Type: Scenario-Based

     • Expected Answer: Use unified auditing and retention policies.

     • Example:

       AUDIT SELECT ON employees BY ACCESS;

125. What is the difference between a global and local index?

     • Type: Conceptual

     • Expected Answer: Global indexes span all partitions, while local indexes are partition-specific.

126. How do you implement data encryption in Oracle?

     • Type: Practical

     • Expected Answer: Use TDE (Transparent Data Encryption).

     • Example:

       ALTER TABLE employees ENCRYPTION USING 'AES256';

127. What is the role of Oracle’s Exadata?

     • Type: Conceptual

     • Expected Answer: Exadata optimizes performance with hardware and software integration.

128. How do you design a database for high-frequency trading?

     • Type: Scenario-Based

     • Expected Answer: Use in-memory databases, low-latency storage, and optimized indexing.

129. What is the difference between ACID and BASE properties?

     • Type: Conceptual

     • Expected Answer: ACID ensures reliability, while BASE prioritizes availability and eventual consistency.

130. How do you implement a data retention policy?

     • Type: Practical

     • Expected Answer: Use ILM and partitioning.

     • Example:

       ALTER TABLE logs ILM ADD POLICY DROP AFTER 1 YEAR;

131. What is the role of Oracle’s GoldenGate?

     • Type: Conceptual

     • Expected Answer: GoldenGate enables real-time data replication across databases.

132. How do you design a database for global applications?

     • Type: Scenario-Based

     • Expected Answer: Use sharding, replication, and multilingual support.

133. What is the role of Oracle’s Spatial and Graph features?

     • Type: Conceptual

     • Expected Answer: Spatial and Graph support geospatial and graph-based data analysis.

134. How do you handle schema versioning in Oracle?

     • Type: Practical

     • Expected Answer: Use edition-based redefinition.

     • Example:

       ALTER SESSION SET edition = new_edition;

135. What is the difference between a heap and index-organized table?

     • Type: Conceptual

     • Expected Answer: Heap tables store data unordered, while IOTs store data in index order.

136. How do you implement a change data capture (CDC) system?

     • Type: Practical

     • Expected Answer: Use Oracle GoldenGate or Streams.

     • Example:

       BEGIN
         DBMS_CDC_PUBLISH.create_change_set('sales_changes');
       END;

137. What is the role of Oracle’s Database Vault?

     • Type: Conceptual

     • Expected Answer: Database Vault enforces security policies to restrict access.

138. How do you design a database for machine learning workloads?

     • Type: Scenario-Based

     • Expected Answer: Use Oracle’s Machine Learning features with optimized storage.

139. What is the difference between a unique and non-unique index?

     • Type: Conceptual

     • Expected Answer: Unique indexes enforce uniqueness, while non-unique indexes do not.

140. How do you implement a failover mechanism in Oracle?

     • Type: Practical

     • Expected Answer: Use Data Guard with automatic failover.

     • Example:

       DGMGRL> ENABLE FAST_START FAILOVER;

141. What is the role of Oracle’s Real Application Security?

     • Type: Conceptual

     • Expected Answer: Real Application Security enforces fine-grained access control.

142. How do you design a database for event-driven architectures?

     • Type: Scenario-Based

     • Expected Answer: Use Advanced Queuing and triggers for event processing.

143. What is the difference between a full and incremental backup?

     • Type: Conceptual

     • Expected Answer: Full backups copy all data, while incremental backups copy changed data.

144. How do you implement a data masking solution?

     • Type: Practical

     • Expected Answer: Use Oracle Data Masking.

     • Example:

       BEGIN
         DBMS_DATA_MASKING.mask('employees', 'salary', 'RANDOM');
       END;

145. What is the role of Oracle’s Flashback Query?

     • Type: Conceptual

     • Expected Answer: Flashback Query retrieves data as it existed at a past point.

     • Example:

       SELECT * FROM employees AS OF TIMESTAMP SYSDATE - 1/24;

146. How do you design a database for reporting?

     • Type: Scenario-Based

     • Expected Answer: Use materialized views, aggregates, and star schemas.

147. What is the role of Oracle’s Resource Manager?

     • Type: Conceptual

     • Expected Answer: Resource Manager allocates resources to sessions for performance control.

148. How do you handle large-scale data imports?

     • Type: Practical

     • Expected Answer: Use Data Pump or SQL*Loader.

     • Example:

       sqlldr hr/hr CONTROL=load.ctl DATA=data.csv

149. What is the difference between a synonym and a view?

     • Type: Conceptual

     • Expected Answer: A synonym is an alias for an object, while a view is a virtual table.

150. How do you implement a distributed transaction in Oracle?

     • Type: Practical

     • Expected Answer: Use global transactions with XA.

     • Example:

       BEGIN
         DBMS_XA.xa_commit(TRUE);
       END;

151. What is the role of Oracle’s SQL Plan Management?

     • Type: Conceptual

     • Expected Answer: SQL Plan Management stabilizes query execution plans.

152. How do you design a database for high-performance analytics?

     • Type: Scenario-Based

     • Expected Answer: Use Exadata, in-memory options, and parallel queries.

153. What is the difference between a physical and logical data model?

     • Type: Conceptual

     • Expected Answer: Physical models define storage, while logical models focus on business rules.

154. How do you implement a backup strategy for a 24/7 database?

     • Type: Scenario-Based

     • Expected Answer: Use RMAN with incremental backups and Data Guard.

     • Example:

       RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

155. What is the role of Oracle’s Blockchain Tables?

     • Type: Conceptual

     • Expected Answer: Blockchain Tables ensure tamper-proof data for audit and compliance.

📘Free Job Preparation Zone 🎯 Visit Free Learning Zone