Oracle DB Architecture & Commands.
md 2024-01-21
Oracle Database Architecture and Commands
1. Oracle Database Architecture
Oracle Database follows a client/server architecture, where the database server manages and stores data, and
clients interact with the server to retrieve or update information. Key components of Oracle Database
Architecture include:
1.1 Oracle Instance
An Oracle Instance is a combination of memory structures and background processes that manage the
database. Key components include the System Global Area (SGA) and the background processes.
1.2 Database
The Database consists of physical files on disk storing data and metadata. Datafiles store user data, control
files manage the database, and redo log files record changes to data.
2. Client/Server Architecture
Oracle Database uses a client/server model where clients, such as applications or users, connect to the Oracle
Database server. The server processes requests from clients and manages database operations.
3. Processes in Oracle Database
3.1 Server Processes
Server processes handle user requests, executing SQL statements and managing database resources.
Examples include:
Oracle Background Processes:
DBWn (Database Writer): Writes modified buffers to datafiles.
LGWR (Log Writer): Writes redo log entries to disk.
PMON (Process Monitor): Performs process recovery when a user process fails.
3.2 User Processes
User processes are created when a user connects to the database. They execute SQL statements and interact
with the server. Examples include:
Server Process: A dedicated process for a single user.
Dispatcher Process: Shares resources among multiple users.
4. Oracle Database Commands
4.1 sqlplus / as sysdba
Connects to the Oracle Database as the system administrator (sysdba).
1/3
Oracle DB Architecture & Commands.md 2024-01-21
sqlplus / as sysdba
4.2 lsnrctl start
Starts the Oracle Listener, a process that listens for incoming connection requests.
lsnrctl start
4.3 Builtin DBA Account
Oracle provides several predefined administrative accounts, including:
SYS: The primary system administrator.
SYSTEM: Manages the database and serves as a default DBA account.
4.4 Examples of Oracle Database Commands
4.4.1 Managing Users
CREATE USER username IDENTIFIED BY password;
GRANT CONNECT, RESOURCE TO username;
4.4.2 Managing Tables
CREATE TABLE tablename (column1 datatype, column2 datatype);
INSERT INTO tablename VALUES (value1, value2);
4.4.3 Backup and Recovery
-- Take a full database backup
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
4.5 Handy SQL*Plus Commands and Tricks
4.5.1 Clearing the Screen
-- Clears the SQL*Plus screen
CLEAR SCREEN
2/3
Oracle DB Architecture & Commands.md 2024-01-21
4.5.2 Displaying Current Username
-- Displays the current username
SHOW USER
4.5.3 Changing the Output Format
-- Sets the output format to display results horizontally
SET PAGESIZE 0
SET LINESIZE 80
4.5.4 Recall Previous Commands
-- Recalls the previous SQL command
/
These handy SQL*Plus commands and tricks can enhance your experience in the SQL console, making it more
efficient and user-friendly.
3/3
