Oracle Database and SQL Fundamentals

Modern Oracle databases support dynamic SGA management through Automatic Shared Memory Management (ASMM) and Automatic Memory Management (AMM). These features allow SGA components to resize automatically based on system workload and memory pressure. Static sizing is a legacy approach that limits performance optimization.

Transaction Control Language (TCL) commands manage database transactions: COMMIT permanently saves changes, while ROLLBACK undoes them. UPDATE and DELETE are DML operations that modify data but don't control transactions. Understanding SQL command categories is fundamental for database development and administration.

TRUNCATE is classified as DDL because it resets table storage and cannot be rolled back, unlike DELETE which is DML. DDL commands implicitly commit and modify database structure or metadata. This distinction is crucial for understanding transaction behavior and data recovery options.

Data Manipulation Language (DML) commands directly manipulate data within database objects. INSERT adds new rows, UPDATE modifies existing data, and DELETE removes rows, all operations on the data itself rather than structure. DML operations can be rolled back as part of transaction management.

The Shared Pool in Oracle SGA contains the Library Cache (which stores parsed SQL statements and PL/SQL code) and Data Dictionary Cache (which stores data dictionary rows). Data Buffer Cache is a separate SGA component used for caching data blocks from disk, not part of Shared Pool. SQL buffer cache is not a standard Oracle memory component.

DDL (Data Definition Language) commands define and modify database structures like tables, indexes, and schemas. DML (Data Manipulation Language) manipulates actual data with commands like INSERT, UPDATE, DELETE. DCL (Data Control Language) controls access permissions (GRANT, REVOKE), while TCL (Transaction Control Language) manages transactions (COMMIT, ROLLBACK).

RMAN (Recovery Manager) uses the Large Pool for memory-intensive operations like backups and recovery. The Large Pool is designed for large memory allocations that don't compete with the Shared Pool, preventing performance degradation during RMAN operations. Shared Pool and Java Pool serve different purposes.