Here are 100 Oracle Database Interview Questions and Answers categorized into different sections.
1. General Oracle Database Questions
What is Oracle Database?
Oracle Database is a relational database management system (RDBMS) from Oracle Corporation, widely used for online transaction processing (OLTP), data warehousing (DW), and mixed database workloads.
What is an Oracle Instance?
An Oracle instance consists of memory structures and background processes that manage the database files.
What is a Tablespace in Oracle?
A tablespace is a storage location where database objects are stored in Oracle.
What is the purpose of the Control File?
The control file maintains the state of the physical structure of the database.
Explain the difference between CHAR and VARCHAR2 datatypes.
CHAR is a fixed-length datatype, while VARCHAR2 is variable-length, storing only the actual data.
What is an Extent in Oracle?
An extent is a collection of contiguous blocks that Oracle allocates in a tablespace.
What is an Oracle SID?
The System Identifier (SID) is a unique name for an Oracle database instance.
What is the use of Redo Log Files?
Redo log files record changes made to the database, helping in recovery operations.
What are Datafiles in Oracle?
Datafiles are the physical files that store the database data.
What is an Oracle Schema?
A schema is a collection of database objects like tables, views, indexes, and synonyms owned by a database user.
2. Oracle SQL Queries and Data Manipulation
What are the different types of joins in Oracle?
INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, and CROSS JOIN.
What is a subquery?
A subquery is a query nested within another query.
What is a MERGE statement in Oracle?
MERGE is used to insert, update, or delete data conditionally in a table based on values from another table or subquery.
How do you eliminate duplicate rows in a query?
By using the DISTINCT keyword.
What is the use of ROWNUM in Oracle?
ROWNUM is a pseudocolumn that numbers the rows in a result set.
What is the difference between TRUNCATE and DELETE?
TRUNCATE removes all rows and deallocates space, while DELETE only removes rows and allows space reuse.
Explain the IN and EXISTS clauses.
IN checks if a value matches any value in a list, while EXISTS checks for the existence of rows returned by a subquery.
What is a SEQUENCE in Oracle?
A sequence is a database object that generates a series of unique numbers.
How do you retrieve the current date and time in Oracle?
By using the SYSDATE function.
What is a View in Oracle?
A view is a virtual table that is based on a query of one or more tables.
3. PL/SQL and Stored Procedures
What is PL/SQL?
PL/SQL (Procedural Language/SQL) is Oracle’s procedural extension for SQL.
What is a trigger in Oracle?
A trigger is a PL/SQL block that executes automatically when a specified event occurs.
What is a package in PL/SQL?
A package is a group of related procedures, functions, variables, and other package constructs.
What is the difference between PROCEDURE and FUNCTION?
A FUNCTION returns a value, while a PROCEDURE does not return any value.
How do you handle exceptions in PL/SQL?
By using EXCEPTION blocks with handlers like WHEN OTHERS or specific exceptions like NO_DATA_FOUND.
What are cursors in PL/SQL?
Cursors are used to retrieve multiple rows from a query one at a time.
What is the difference between an implicit and an explicit cursor?
Implicit cursors are automatically created by Oracle, while explicit cursors are defined by the user.
Explain cursor attributes like %FOUND, %NOTFOUND, and %ROWCOUNT.
These attributes help in controlling cursor operations, determining if rows exist or how many were processed.
What is a ref cursor in Oracle?
A ref cursor is a dynamic cursor that allows returning a result set from a PL/SQL block to the calling application.
What are autonomous transactions in Oracle?
Autonomous transactions are independent transactions that can be committed or rolled back without affecting the main transaction.
4. Oracle Performance Tuning
What is the EXPLAIN PLAN in Oracle?
EXPLAIN PLAN shows how Oracle will execute a SQL query, helping in performance tuning.
What is an Oracle Index?
An index is a database object that improves the speed of data retrieval.
What are the different types of indexes in Oracle?
B-tree, Bitmap, Function-based, and Partitioned indexes.
What is table partitioning in Oracle?
Table partitioning divides a large table into smaller, more manageable pieces, improving performance.
What is the difference between ANALYZE and DBMS_STATS?
ANALYZE collects statistics for rule-based optimization, while DBMS_STATS gathers statistics for the cost-based optimizer (CBO).
What is the Cost-Based Optimizer (CBO)?
CBO uses statistics to choose the execution plan with the lowest cost for query execution.
What is Oracle Hints?
Hints are special instructions given to the optimizer in SQL queries to influence the execution plan.
What are Oracle Latches?
Latches are low-level serialization mechanisms used to protect shared memory structures from concurrent access.
How do you monitor database performance in Oracle?
By using tools like AWR, ASH, TKPROF, and SQL Trace.
What is a PGA in Oracle?
The Program Global Area (PGA) is memory dedicated to a user process in Oracle.
What is TKPROF in Oracle?
TKPROF is a tool that formats SQL trace output into readable performance diagnostics information.
What is Oracle Partition Pruning?
Partition pruning is a technique where Oracle only scans the relevant partitions during query execution, improving performance.
What is the difference between soft parse and hard parse?
A soft parse reuses existing SQL execution plans, while a hard parse creates a new plan, requiring more resources.
What is an Execution Plan in Oracle?
An execution plan describes how Oracle will execute a query, including access paths and join methods.
What is the difference between OLTP and OLAP in Oracle?
OLTP (Online Transaction Processing) is focused on fast query and transaction processing, while OLAP (Online Analytical Processing) is designed for querying large volumes of data for analysis.
What are Bind Variables?
Bind variables are placeholders in SQL queries that improve performance by avoiding repeated parsing.
What is Oracle Optimizer?
The optimizer is responsible for determining the most efficient way to execute a SQL query.
What is a Bitmap Index?
A bitmap index is used for columns with a low cardinality of unique values, improving query performance for specific conditions.
What is Oracle Automatic Database Diagnostic Monitor (ADDM)?
ADDM provides performance diagnostics and tuning recommendations based on data collected by AWR.
How do you resolve SQL performance issues in Oracle?
- By analyzing the execution plan, using EXPLAIN PLAN, optimizing queries, gathering statistics, creating indexes, and tuning memory structures.
5. Backup and Recovery
What is RMAN in Oracle?
RMAN (Recovery Manager) is an Oracle utility for performing backup, restoration, and recovery operations.
What is the difference between an Archivelog and a Noarchivelog mode?
In Archivelog mode, redo logs are archived before being reused, allowing point-in-time recovery, while Noarchivelog mode does not.
What is Oracle Flashback?
Oracle Flashback is a feature that allows the recovery of data to a previous state without restoring from backups.
How do you perform a hot backup in Oracle?
A hot backup is done while the database is running, requiring the database to be in Archivelog mode.
What is the difference between full and incremental backups in RMAN?
A full backup backs up the entire database, while an incremental backup only backs up changes since the last backup.
What are the different types of recoveries in Oracle?
Complete recovery, incomplete recovery (Point-in-Time Recovery), and media recovery.
What is a Data Pump in Oracle?
Data Pump is a utility for fast data import/export operations.
What is the SCN (System Change Number)?
SCN is a unique number assigned to every transaction in Oracle.
What is a Controlfile?
Control files are crucial for database startup and recovery, holding information about the database structure.
What is the difference between CLONE and DUPLICATE in RMAN?
CLONE creates a copy of the database, while DUPLICATE creates a backup duplicate for standby or testing.
6. Oracle Networking
What is Oracle Net (formerly Net8)?
Oracle Net is a component that enables client-server and server-server communication in Oracle over a network.
What is a TNSNAMES.ORA file?
TNSNAMES.ORA is a configuration file that contains the service names and addresses of databases for client connectivity.
What is the LISTENER.ORA file?
LISTENER.ORA contains the configuration of Oracle Listener, which is responsible for managing database connections.
What is a SQL*Net or Oracle Net Listener?
It is a process that listens for client connection requests and handles incoming database connection traffic.
How do you establish a database connection remotely?
By configuring the TNSNAMES.ORA file and using Oracle SQL*Plus or another client tool to connect using a valid database address.
What is a Dedicated Server connection?
In a dedicated server connection, each client session is assigned its own server process.
What is a Shared Server connection?
Shared server mode allows multiple client sessions to share a smaller number of server processes, which helps conserve resources.
What is a Database Link in Oracle?
A database link is a pointer that defines a one-way communication path between two Oracle databases.
What are the security risks of a public database link?
A public database link is available to all users, which could pose a security risk if access is not properly controlled.
How do you configure an Oracle database for high availability?
High availability can be achieved using Oracle RAC (Real Application Clusters), Data Guard, or GoldenGate for replication.
7. Oracle Security
What is the role of the DBA user in Oracle?
The DBA user is a superuser with administrative privileges, able to manage the entire database system.
What is Oracle Data Redaction?
Oracle Data Redaction dynamically masks sensitive data when retrieved by queries, ensuring data security.
What is Oracle Transparent Data Encryption (TDE)?
TDE is a feature that encrypts data at rest to protect sensitive information in database columns or tablespaces.
How do you secure a user account in Oracle?
By setting strong password policies, assigning appropriate privileges, and enabling auditing.
What is Audit in Oracle?
Auditing in Oracle captures database operations and stores them for review, helping track user activities.
What are Roles in Oracle?
Roles are collections of privileges that can be assigned to users to simplify privilege management.
What is Fine-Grained Auditing (FGA)?
FGA enables auditing based on specific conditions, allowing for more detailed tracking of database activity.
What are the GRANT and REVOKE statements used for?
GRANT assigns privileges to users or roles, while REVOKE removes those privileges.
What is a Profile in Oracle?
A profile defines a set of resource limits and password management settings for database users.
How do you restrict access to sensitive columns in a table?
By using column-level privileges or Oracle's Virtual Private Database (VPD) to control access.
8. Oracle Backup and Recovery
What is a full backup?
A full backup captures the entire database, including all datafiles, control files, and archived logs.
What is a partial backup in Oracle?
A partial backup involves backing up part of the database, such as certain tablespaces or datafiles.
What is the difference between Level 0 and Level 1 incremental backups?
A Level 0 backup is a full backup, while a Level 1 backup captures changes made since the last Level 0 or Level 1 backup.
What is Oracle Data Guard?
Data Guard provides high availability, data protection, and disaster recovery by maintaining standby databases.
What is a Standby Database?
A standby database is a copy of the production database that can take over operations in case of primary database failure.
How do you recover from a lost datafile without backups?
By using Flashback technology or reconstructing the file using RMAN based on redo logs.
What is the V$RECOVERY_FILE_DEST view used for?
This view shows the details of the flash recovery area, including disk usage and file types.
What is a Point-in-Time recovery?
A Point-in-Time Recovery (PITR) allows the database to be restored to a specific timestamp.
How do you perform Block Media Recovery in Oracle?
Block Media Recovery can be done using RMAN to recover specific blocks of corrupted datafiles.
What is Oracle RAC?
Oracle Real Application Clusters (RAC) allows multiple servers to run a single Oracle database, providing scalability and high availability.
9. Advanced Oracle Concepts
What is Oracle ASM (Automatic Storage Management)?
ASM is a storage management solution that simplifies database storage by abstracting and automating the management of disk groups.
What is Oracle Streams?
Oracle Streams is a feature used for data replication, message queuing, and data sharing across multiple databases.
What is Oracle Grid Infrastructure?
Oracle Grid Infrastructure provides the foundation for Oracle RAC and ASM, managing cluster resources and high availability.
What is the difference between Oracle RAC and Oracle Data Guard?
Oracle RAC provides high availability at the instance level, while Data Guard provides protection at the database level by maintaining standby databases.
What is a materialized view?
A materialized view stores the result of a query physically, which can be refreshed periodically to optimize query performance.
What is a Global Temporary Table?
A Global Temporary Table stores data only for the duration of a session or transaction.
What is a synonym in Oracle?
A synonym is an alias for another object such as a table, view, or sequence, allowing easier access to objects.
What is Oracle Flashback Query?
Flashback Query allows querying historical data by specifying a timestamp or SCN, enabling data retrieval from a past state.
What is an Oracle Sequence Cache?
Sequence caching allows the database to preallocate a set of sequence values in memory, reducing disk I/O.
What is Oracle AWR (Automatic Workload Repository)?
AWR is a repository that collects and maintains performance statistics used for database tuning.
No comments:
Post a Comment