Oracle is a relational database management system (RDBMS) that supports SQL for querying and managing databases. Key features include scalability, reliability, support for large amounts of data, and multi-user capability.
DELETE removes rows based on a condition and can be rolled back, while TRUNCATE removes all rows from a table and cannot be rolled back. TRUNCATE is faster and resets any auto-increment counters.
A JOIN is used to combine rows from two or more tables based on a related column. Types of JOINs include:
INNER JOIN: Returns rows with matching values in both tables.
LEFT JOIN: Returns all rows from the left table and matched rows from the right table.
RIGHT JOIN: Returns all rows from the right table and matched rows from the left table.
FULL OUTER JOIN: Returns all rows when there is a match in either table.
Oracle architecture consists of:
Instance: Composed of memory structures (like SGA) and background processes.
Database: A set of physical files that store data.
Process Architecture: Includes user processes, server processes, and background processes (like DBWn, LGWR, etc.).
Storage Architecture: Tablespaces, datafiles, segments, and extents are components of Oracle’s storage structure.
A hot backup is taken while the database is online and operational, whereas a cold backup is taken when the database is shut down. A hot backup requires the database to be in ARCHIVELOG mode.
The ORA-01555 error occurs when a long-running query tries to access a rollback segment that has been overwritten. It can be resolved by increasing the size of the undo tablespace or by ensuring that long queries run with minimal interference from updates.
SQL (Structured Query Language) is a standard language used for managing and manipulating relational databases. It allows users to perform operations like querying data, inserting records, updating, and deleting data.
WHERE is used to filter rows before grouping, while HAVING is used to filter groups after applying aggregation functions like COUNT, SUM, etc.
PL/SQL (Procedural Language for SQL) is an extension of SQL used in Oracle. It adds procedural features like variables, loops, and conditions to SQL, allowing for complex operations such as functions, procedures, and triggers.
: A Cursor is a pointer that allows the user to fetch one row at a time from the result set of an SQL query. There are two types: Implicit Cursors (automatically created) and Explicit Cursors (defined by the programmer for complex queries).
A Trigger is a stored procedure that automatically executes in response to certain events on a table or view, such as INSERT, UPDATE, or DELETE.
Oracle Fusion Middleware is a set of software products that enable businesses to create, deploy, and manage business applications. It includes tools for enterprise resource planning (ERP), customer relationship management (CRM), and business intelligence (BI).
Oracle Fusion HCM (Human Capital Management) is a cloud-based suite of applications that help manage human resources activities, including payroll, talent management, and workforce management.
OAF is a development framework used for creating Oracle E-Business Suite applications. It is based on the Model-View-Controller (MVC) architecture and helps developers build web-based applications.
An Oracle DBA is responsible for the installation, configuration, upgrading, administration, monitoring, and maintenance of Oracle databases. They ensure database security, perform backups, and recover data when needed.
In ARCHIVELOG mode, the database archives all redo log files after a log switch, ensuring that data can be recovered after a failure. In NOARCHIVELOG mode, redo logs are not archived, limiting the recovery options in case of a failure.
An Oracle Instance refers to the set of memory structures (like the SGA) and background processes (like DBWR, LGWR) that manage database operations. Every Oracle database is associated with at least one instance.
A Tablespace is a logical storage unit in Oracle databases. A Datafile is a physical storage unit on disk that stores data for a specific tablespace. Each tablespace is associated with one or more datafiles.
Redo Log Files store a log of all changes made to the database. These logs are used for recovery in case of a system failure. Redo log files are essential for maintaining database integrity and ensuring data is recoverable.
The Oracle Listener is a process that listens for incoming client connection requests. It acts as an intermediary between the client application and the Oracle database instance.
