Levels of Locking in Oracle and Methods to Acquire Locks

Note: The reference notes did not contain specific content on this topic. The following answer is based on standard Oracle database concepts as taught in database management system courses at the BSc CSIT level.

Introduction to Locking in Oracle

A lock is a mechanism that prevents destructive interaction between transactions accessing a shared resource. Oracle Database uses a multi-level locking strategy to provide maximum concurrency while maintaining data integrity and consistency.

Oracle's locking philosophy is:

• Locks are acquired automatically (implicitly) by DML statements.
• Oracle never escalates locks (a key difference from many other RDBMS).
• Readers do not block writers, and writers do not block readers.

Levels of Locking in Oracle

Oracle implements locking at multiple granularity levels:

1. Row-Level Locks (TX Locks - Transaction Locks)

• The finest granularity of locking in Oracle.
• When a transaction modifies a row (INSERT, UPDATE, DELETE, SELECT FOR UPDATE), Oracle places an exclusive lock on that specific row.
• Other transactions can read the row (using MVCC - Multi-Version Concurrency Control) but cannot modify it until the lock is released.
• Row-level locks are stored within the data block itself (in the row header), not in a separate lock table.
• This allows Oracle to lock millions of rows without lock escalation.

Example:

UPDATE employees SET salary = 5000 WHERE emp_id = 101;
-- Only row with emp_id = 101 is locked

2. Table-Level Locks (TM Locks - DML Locks)

• Placed on the entire table to protect the table structure while row-level locks are held.
• Table locks do not prevent other transactions from accessing rows; they prevent DDL operations (like DROP TABLE or ALTER TABLE) from conflicting with ongoing DML.
• Oracle supports five modes of table-level locks:

Lock Compatibility Matrix:

(Y = Compatible, N = Incompatible)

3. Page/Block-Level Locks

• Oracle does not use page-level or block-level locking as a standard locking granularity.
• This is a deliberate design choice to avoid unnecessary contention.
• Lock information is embedded in the block header (Interested Transaction List - ITL), but this is not a separate locking level.

4. Database-Level Locks (DDL Locks)

• Protect the definition (schema) of database objects during DDL operations.
• Three types:
  • Exclusive DDL Lock: Acquired during DROP, ALTER, CREATE operations. No other DDL or DML allowed on the object.
  • Share DDL Lock: Acquired during CREATE PROCEDURE, CREATE VIEW. Allows other share DDL locks but not exclusive.
  • Breakable Parse Lock: Held by SQL statements in the shared pool. Can be broken if a DDL operation requires it.

5. System/Dictionary Locks (Internal Locks and Latches)

• Latches: Low-level serialization mechanisms protecting internal Oracle memory structures (e.g., SGA, buffer cache). They are not user-visible.
• Internal Locks: Protect internal database structures like data file headers and control files.
• These are managed entirely by Oracle and are not accessible to users.

Methods Used to Acquire Locks in Oracle

Oracle provides both implicit (automatic) and explicit (manual) methods to acquire locks.

Method 1: Implicit (Automatic) Locking

Oracle automatically acquires locks when DML statements are executed. The user does not need to request them.

Example:

UPDATE departments SET dept_name = 'HR' WHERE dept_id = 10;
-- Oracle automatically acquires: Row X lock + Table RX lock

Locks are automatically released when the transaction ends (COMMIT or ROLLBACK).

Method 2: SELECT ... FOR UPDATE (Explicit Row Lock)

• Used to explicitly lock rows that a transaction intends to modify later.
• Prevents other transactions from modifying or locking the same rows.
• Useful when a transaction reads data and then updates it later in the same transaction.

Syntax:

SELECT column_list
FROM table_name
WHERE condition
FOR UPDATE [OF column] [NOWAIT | WAIT n | SKIP LOCKED];

Options:

• NOWAIT: Returns an error immediately if the lock cannot be acquired.
• WAIT n: Waits for n seconds before returning an error.
• SKIP LOCKED: Skips rows that are already locked.

Example:

SELECT emp_id, salary
FROM employees
WHERE dept_id = 10
FOR UPDATE NOWAIT;
-- The selected rows are locked in exclusive mode until COMMIT or ROLLBACK

Method 3: LOCK TABLE (Explicit Table Lock)

• Used when a transaction wants to lock an entire table rather than individual rows.
• The lock mode is stated explicitly, which lets the developer decide how much concurrency to give up.

Syntax:

LOCK TABLE table_name IN lock_mode MODE [NOWAIT];

Common lock modes:

Example:

LOCK TABLE employees IN EXCLUSIVE MODE NOWAIT;

Method 4: DDL Locks (Acquired Automatically)

• Oracle takes a DDL lock on an object whenever a DDL statement such as ALTER TABLE or DROP TABLE refers to it, so the definition cannot change under a statement that is still compiling or executing.
• These locks are never requested by the user; they are held only for the duration of the DDL statement.

Conclusion

Oracle locks at several granularities, from the row level (TX locks, the normal case) up through table level (TM locks), block level, and the DDL and internal locks that protect the data dictionary. Locks are acquired in two ways: implicitly, when a DML statement runs and Oracle takes the row and table locks it needs, and explicitly, through SELECT ... FOR UPDATE for rows or LOCK TABLE for whole tables. Oracle's default is deliberately conservative in favour of concurrency, since readers never block writers and writers never block readers thanks to multiversion read consistency, and every lock is released automatically at COMMIT or ROLLBACK.

Roles and Responsibilities of Database Administrator (DBA)

A Database Administrator (DBA) is a person responsible for managing, maintaining, and securing the database system in an organization. The DBA acts as a central authority over the database environment.

Key Roles and Responsibilities of a DBA

1. Schema Definition

• The DBA is responsible for creating the original database schema by writing a set of definitions in the DDL (Data Definition Language).
• This includes defining tables, views, constraints, and relationships.

2. Storage Structure and Access Method Definition

• The DBA defines how data is physically stored on storage devices.
• Decides on appropriate access methods (e.g., indexing, hashing) to ensure efficient data retrieval.

3. Schema and Physical Organization Modification

• When requirements change, the DBA modifies the schema or physical organization.
• This includes altering tables, adding new attributes, or reorganizing storage to improve performance.

4. Granting Authorization for Data Access

• The DBA controls who can access what data by granting or revoking access privileges.
• Ensures that only authorized users can read, write, or modify specific data.

5. Routine Maintenance

The DBA performs regular maintenance tasks such as:

• Backup and Recovery: Periodically backing up data and recovering it in case of failure.
• Performance Monitoring: Monitoring the database for slow queries and tuning performance.
• Disk Space Management: Ensuring sufficient storage is available for database operations.

6. Integrity Constraint Definition

• The DBA defines integrity constraints (e.g., primary keys, foreign keys, check constraints) to maintain data accuracy and consistency.

7. Liaison with Users

• Acts as a bridge between end users and the database system.
• Understands user requirements and translates them into appropriate database structures.

Summary Table

Note: The DBA holds the highest level of authority in a database environment and is essential for the smooth, secure, and efficient operation of any database system.

Tablespace and Datafile in Oracle

Tablespace

A tablespace is a logical storage unit in an Oracle database that groups related logical structures (tables, indexes, etc.) together. It is the bridge between the physical and logical components of an Oracle database. Every Oracle database has at least one tablespace called the SYSTEM tablespace.

Key points:

• It is a logical storage container
• A database is divided into one or more tablespaces
• Each tablespace belongs to only one database

Datafile

A datafile is a physical operating system file that actually stores the data on disk. Every tablespace is made up of one or more datafiles.

Key points:

• It is a physical file stored on the disk (e.g., .dbf file)
• One tablespace can have multiple datafiles
• One datafile belongs to only one tablespace

Relationship:

Database --> Tablespace (Logical) --> Datafile (Physical)

Adding a New Tablespace

Use the CREATE TABLESPACE statement:

CREATE TABLESPACE my_tablespace
DATAFILE '/u01/oradata/mydb/my_tablespace01.dbf'
SIZE 100M
AUTOEXTEND ON
NEXT 10M
MAXSIZE 500M;

Explanation of clauses:

Adding a New Datafile to an Existing Tablespace

Use the ALTER TABLESPACE statement:

ALTER TABLESPACE my_tablespace
ADD DATAFILE '/u01/oradata/mydb/my_tablespace02.dbf'
SIZE 50M
AUTOEXTEND ON
NEXT 5M
MAXSIZE 200M;

This adds a second datafile to the existing tablespace my_tablespace, increasing its total storage capacity.

Summary

Privilege in Oracle Database

What is Privilege?

A privilege is a right or permission granted to a database user to perform a specific operation or to access a specific database object. Privileges control what actions a user can perform in the database system.

There are two types of privileges in Oracle:

• System Privileges - Allow users to perform specific database operations (e.g., CREATE TABLE, CREATE SESSION)
• Object Privileges - Allow users to perform operations on specific database objects like tables, views, etc. (e.g., SELECT, INSERT, UPDATE, DELETE on a table)

Oracle Database Commands

1. Create User

CREATE USER username IDENTIFIED BY password;

Example:

CREATE USER john IDENTIFIED BY john123;

2. Grant Privilege

GRANT privilege_name TO username;

Example (System Privilege):

GRANT CREATE SESSION TO john;
GRANT CREATE TABLE TO john;

Example (Object Privilege):

GRANT SELECT, INSERT ON employees TO john;

The WITH GRANT OPTION allows the user to further grant the privilege to others:

GRANT SELECT ON employees TO john WITH GRANT OPTION;

3. Revoke Privilege

REVOKE privilege_name FROM username;

Example (System Privilege):

REVOKE CREATE TABLE FROM john;

Example (Object Privilege):

REVOKE SELECT, INSERT ON employees FROM john;

4. Drop User

DROP USER username;

Example:

DROP USER john;

If the user owns database objects (tables, views, etc.), use CASCADE to drop the user along with all their objects:

DROP USER john CASCADE;

Summary Table

Oracle AWR (Automatic Workload Repository)

What is Oracle AWR?

AWR (Automatic Workload Repository) is a built-in Oracle Database feature that automatically collects, processes, and maintains performance statistics for problem detection and self-tuning purposes.

Note: The following answer is based on standard Oracle Database documentation, as no specific curriculum notes were provided.

Key Characteristics of AWR:

• AWR is a repository of historical performance data stored in the SYSAUX tablespace of the Oracle database.
• It collects snapshots of database performance statistics at regular intervals (default: every 60 minutes).
• Snapshots are retained for a default period of 8 days, after which they are automatically purged.
• It is managed by the MMON (Manageability Monitor) background process.

Data Collected by AWR:

Purpose of AWR:

• Identify performance bottlenecks
• Compare database performance between two time periods
• Support the Automatic Database Diagnostic Monitor (ADDM)
• Assist DBAs in capacity planning and tuning

How AWR Report is Viewed in Oracle

AWR reports can be generated and viewed using the following methods:

Method 1: Using SQL*Plus Script

Oracle provides a built-in script to generate the AWR report:

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

Steps:

1. Connect to the database as SYSDBA:

   sqlplus / as sysdba
   

2. Run the AWR report script:

   @$ORACLE_HOME/rdbms/admin/awrrpt.sql
   

3. The script prompts for:

   • Report format: HTML or TEXT
   • Number of days: to list available snapshots
   • Beginning Snapshot ID
   • Ending Snapshot ID
   • Report filename

4. The report is saved as an .html or .txt file and can be opened in a browser.

Method 2: Using Oracle Enterprise Manager (OEM)

• Navigate to: Performance > AWR > AWR Report
• Select the begin and end snapshot
• Click Generate Report
• View the report directly in the browser interface

Method 3: Using DBMS_WORKLOAD_REPOSITORY Package

SELECT output FROM TABLE(
  DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
    l_dbid    => &dbid,
    l_inst_num => 1,
    l_bid     => &begin_snap,
    l_eid     => &end_snap
  )
);

Key Sections in an AWR Report:

1. Report Summary - DB time, load profile
2. Top 5 Timed Events - Major wait events
3. SQL Statistics - Top SQL by CPU, elapsed time
4. Instance Activity Statistics
5. I/O Statistics
6. Memory Statistics

Summary

AWR is a critical tool for Oracle DBAs to diagnose, monitor, and tune database performance effectively.

Listener, listener.ora, and tnsnames.ora in Oracle Database

Listener

A listener is a server-side network process in Oracle Database that listens for incoming client connection requests on a specific network protocol (usually TCP/IP) and a specific port (default port: 1521).

When a client wants to connect to an Oracle database, it sends a connection request to the listener. The listener receives the request, verifies it, and then hands off (redirects) the connection to the appropriate Oracle database instance.

Key points about Listener:

• It runs on the database server machine.
• It is managed using the lsnrctl (Listener Control) utility.
• Common commands: lsnrctl start, lsnrctl stop, lsnrctl status
• Without a running listener, remote clients cannot connect to the Oracle database.

listener.ora File

The listener.ora file is a server-side configuration file that defines the listener's properties and settings.

Location: $ORACLE_HOME/network/admin/listener.ora

It contains:

• The name of the listener (default: LISTENER)
• The protocol, host, and port on which the listener listens
• The Oracle database services/SIDs it serves

Example:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS =
        (PROTOCOL = TCP)
        (HOST = myserver.example.com)
        (PORT = 1521)
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = ORCL)
      (ORACLE_HOME = /u01/app/oracle/product/19c/dbhome_1)
    )
  )

Key parameters:

tnsnames.ora File

The tnsnames.ora file is a client-side configuration file that maps logical service names (aliases) to Oracle database connection descriptors (host, port, service name).

Location: $ORACLE_HOME/network/admin/tnsnames.ora

When a client application uses a connect string like sqlplus user/pass@ORCL, Oracle looks up ORCL in tnsnames.ora to find the actual connection details.

Example:

ORCL =
  (DESCRIPTION =
    (ADDRESS =
      (PROTOCOL = TCP)
      (HOST = myserver.example.com)
      (PORT = 1521)
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.example.com)
    )
  )

Key parameters:

Difference Between listener.ora and tnsnames.ora

In summary: The listener acts as a gatekeeper on the server; listener.ora tells the listener how to operate; and tnsnames.ora tells the client where and how to connect to the database.