Oracle – Overview

Oracle overview

What is an Oracle Database?

A database is a collection of data. An Oracle database holds its data in a coordinated set of files stored on disk, including following types:

Parameter Files

The Oracle Parameter File (PFILE or SPFILE) holds initialization parameters which indicate where the control files are, how memory is allocated, how logging is performed, and other characteristics of the Oracle instance.

You may want to tune various instance parameters for memory usage, etc., to do this you will have to change Oracle’s initialization parameter file.

Oracle offers two types of parameter files – INIT.ORA and SPFILE. Their default location on startup command is $ORACLE_HOME/dbs or %ORACLE_HOME%\database.

Parameter file types

INIT.ORA/ PFILEs – PFILEs are also know as INIT.ORA files. Characteristics of a PFILE:

  • Client side
  • Text file
  • Edit with text editor like vi or notepad

SPFILEs – Characteristics of an SPFILE:

  • Server side
  • Binary file
  • Edit by issuing ALTER SYSTEM SET commands
show parameter spfile; 

See what parameters are set

From SQL*Plus (connected with an account having SELECT ANY DICTIONARY privilege):

show parameters db_name  

As a query:

SELECT * FROM v$parameter;

Control Files

control file is a small binary file that is part of an Oracle database. The control file is used to keep track of the database’s status and physical structure.

Every Oracle Database must have at least one control file. However, it is recommended to create more than one, up to a maximum of 8. Each copy of a control file should be stored on a different disk drive. One practice is to store a control file copy on every disk drive that stores members of online redo log groups, if the online redo log is multiplexed. By storing control files in these locations, you minimize the risk that all control files will be lost in a single disk failure.

Contents – The control file contains information like:

  • Database name
  • Timestamp of database creation
  • Names and locations of Data Files
  • Names and locations of Redo Log files
  • The current log sequence number
  • Checkpoint information
  • Recent RMAN backups taken
  • Etc.

List control files in use

From SQL*Plus (connect SYS AS SYSDBA):

SHOW PARAMETERS control_files; 

As a query:

SELECT * FROM v$controlfile;

Control file contents
SELECT * FROM v$controlfile_record_section;

Backup

Backup the controlfile to UDUMP in text format:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE; 

Make a binary copy of the control file:

ALTER DATABASE BACKUP CONTROLFILE TO '/tmp/control.bkp'; 

Generate an SQL file to create the control file:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/tmp/create_control.sql'

Redo Log Files

redo log is a file that is part of an Oracle database. When a transaction is committed, the transaction’s details in the redo log buffer is written to a redo log file.

Redo log buffer – A circular buffer in the SGA that contains information about changes made to the database. The LGWR process writes information from this buffer to the redo log files.

Redo log files – A set of files that record all changes made to an Oracle database. A database MUST have at least two redo log files. Log files can be multiplexed on multiple disks to ensure that they will not get lost.

Query redo log details:

SELECT * FROM v$log; 

To see the logfile members:

SELECT * FROM v$logfile; 

Note that a redo log can have different states:

  • CURRENT: redo records are currently being written to the group. Only one group is current at a time.
  • ACTIVE: redo group that contains redo’s of a dirty buffer (not yet committed transaction).
  • INACTIVE: log that can be overwritten.
  • UNUSED: initial state after creation, when it’s still empty.

The point at which Oracle stops writing to one redo log and starts writing to another is called a log switch. You can force the log switch with:

SQL> ALTER SYSTEM SWITCH LOGFILE; 

Redo Log Writer process

SQL> select spid from v$process where program like '%LGWR%';
 
SPID
------------
29867
 
SQL> ! ps -ef | grep 29867
 oracle 29867     1  0   Sep 26 ?        7:59 ora_lgwr_o102

Find the database users that generate the most redo

It is sometimes necessary to find the processes that generate the most redo entries, as they may cause excessive database archiving. This query will help:

SELECT s.sid, s.username, s.program, t.value "redo blocks written"
  FROM v$session s, v$sesstat t
 WHERE s.sid = t.sid
   AND t.value != 0
   AND t.statistic# = (SELECT statistic# FROM v$statname
                        WHERE name = 'redo size')
ORDER BY 4
/

Data Files

data file is a file that is part of an Oracle database. Datafiles are used to store data – including user data and undo data. Data files are grouped together into tablespaces.

Data file structure

Query data file details:

SELECT * FROM v$datafile; SELECT * FROM dba_data_files; 

Create tablespace with datafile:

CREATE TABLESPACE ts1 DATAFILE '/u01/oradata/orcl_ts1_01.dbf' SIZE 100M; 

Create an UNDO tablespace with datafile:

CREATE UNDO TABLESPACE undots1 DATAFILE '/u01/oradata/orcl_undots1_01.dbf' SIZE 100M;
  • Temp Files – A tempfile is a file that is part of an Oracle database. Tempfiles are used with TEMPORARY TABLESPACES and are used for storing temporary data like sort spill-over or data for global temporary tables.

Using tempfiles

To see what tempfiles are used:

SELECT * FROM dba_temp_files; 

Create a tablespace with TEMPFILE:

CREATE TEMPORARY TABLESPACE temp1 TEMPFILE '/u01/oradata/orcl_temp1_01.dbf' SIZE 100M; 

Remove a tempfile from the database:

ALTER DATABASE TEMPFILE '/u01/oradata/orcl_temp1_01.dbf' DROP;

What is an Instance?

An instance is a collection of Oracle background processes and shared memory structures.

Memory Areas

SGA

The SGA (System Global Area) is an area of memory (RAM) allocated when an Oracle Instance starts up. The SGA’s size and function are controlled by initialization (INIT.ORA or SPFILE) parameters.

SGA Components

In general, the SGA consists of the following sub-components, as can be verified by querying the V$SGAINFO:

SELECT * FROM v$sgainfo;

The common components are:

  • Data buffer cache – cache data and index blocks for faster access.
  • Shared pool – cache parsed SQL and PL/SQL statements.
  • Dictionary Cache – information about data dictionary objects.
  • Redo Log Buffer – committed transactions that are not yet written to the redo log files.
  • JAVA pool – caching parsed Java programs.
  • Streams pool – cache Oracle Streams objects.
  • Large pool – used for backups, UGAs, etc.

SGA Size

Here are two methods that can be used to determine the current SGA’s size. All values are in bytes:

SQL> SHOW SGA
Total System Global Area  638670568 bytes
Fixed Size                   456424 bytes
Variable Size             503316480 bytes
Database Buffers          134217728 bytes
Redo Buffers                 679936 bytes
SQL> SELECT * FROM v$sga; 
NAME                      VALUE 
-------------------- ---------- 
Fixed Size               456424 
Variable Size         503316480 
Database Buffers      134217728 
Redo Buffers             679936

The size of the SGA is controlled by the SGA_TARGET and SGA_MAX_TARGET parameters.

SQL> select * from v$sgainfo;
NAME                             BYTES                  RESIZEABLE 
-------------------------------- ---------------------- ---------- 
Fixed SGA Size                   2109352                No   
Redo Buffers                     13533184               No    
Buffer Cache Size                3103784960             Yes 
Shared Pool Size                 822083584              Yes  
Large Pool Size                  67108864               Yes 
Java Pool Size                   134217728              Yes  
Streams Pool Size                134217728              Yes 
Shared IO Pool Size              0                      Yes 
Granule Size                     16777216               No 
Maximum SGA Size                 4277059584             No 
Startup overhead in Shared Pool  251658240              No 
Free SGA Memory Available        0      

Oracle 11g allows users to tune both PGA and SGA areas with a single parameter, called MEMORY_TARGET.

Dynamic sizing

When automatic shared memory management is enabled, Oracle will adjust the memory parameters on the fly. To see currently allocated sizes:

select * from v$sga_dynamic_components;

The SGA consists of the following four (five if MTS) parts:

  • Fixed Portion
  • Variable Portion
  • Shared pool
  • java pool

PGA

The PGA (Program or Process Global Area) is a memory area (RAM) that stores data and control information for a single process. For example, it typically contains a sort area, hash area, session cursor cache, etc.

Auto tuning

PGA areas can be sized manually by setting parameters like hash_area_size, sort_area_size etc.

To allow Oracle to auto tune the PGA areas, set the WORKAREA_SIZE_POLICY parameter to AUTO and the PGA_AGGREGATE_TARGET to the size of memory that can be used for PGA. This feature was introduced in Oracle 9i.

Oracle 11g allows users to tune both PGA and SGA areas with a single parameter, called MEMORY_TARGET.

Monitor

PGA usage statistics:

select * from v$pgastat; 

Determine a good setting for pga_aggregate_target:

select * from v$pga_target_advice order by pga_target_for_estimate; 

Show the maximum PGA usage per process:

select max(pga_used_mem), max(pga_alloc_mem), max(pga_max_mem) from v$process;

UGA

The User Global Area (UGA) is a memory area (RAM) that holds session-based information.

Dedicated Server

When running in Dedicated Server mode (one session = one dedicated process), the UGA is stored in the PGA (process global area).

Shared Server

When running in Shared Server mode (MTS with shared servers and dispatchers), sessions can be served by multiple server processes. As a result, the UGA cannot be stored in the PGA, and is moved to the SGA (shared global area).

Monitoring the UGA

SELECT s.sid, n.name, s.value/power(1024,2) MB 
  FROM v$sesstat s, v$statname n
 WHERE s.statistic# = n.statistic#
   AND n.name LIKE '%uga%';

Processes

Oracle uses many small (focused) processes to manage and control the Oracle instance. This allows for optimum execution on multi-processor systems using multi-core and multi-threaded technology. Some of these processes include:

PMON – Process Monitor

PMON (Process MONitor) is an Oracle background process created when you start a database instance. The PMON process will free up resources if a user process fails (eg. release database locks).

PMON normally wakes up every 3 seconds to perform its housekeeping activities. PMON must always be running for an instance. If not, the instance will terminate.

To speed-up housekeeping, one may also wake-up PMON (process 2 below) manually:

SQL> oradebug setmypid 
SQL> oradebug wakeup 2 

In Oracle releases prior to Oracle 12c, PMON also registered database serviced with the listener. This is now handled by the new LREG progress.

Check process – The following Unix/Linux command is used to check if the PMON process is running:

$ ps -ef | grep pmon
oracle   31144     1  0 11:10 ?        00:00:00 ora_pmon_orcl

SMON – System Monitor

SMON (System MONitor) is an Oracle background process created when you start a database instance. The SMON process performs instance recovery, cleans up after dirty shutdowns and coalesces adjacent free extents into larger free extents.

SMON wakes up every 5 minutes to perform housekeeping activities. SMON must always be running for an instance. If not, the instance will terminate.

Check process – The following Unix/Linux command is used to check if the SMON process is running:

$ ps -ef | grep smon
oracle   31144     1  0 11:10 ?        00:00:00 ora_smon_orcl

ARCn – Redo Log Archiver

ARCH (Oracle’s ARCHiver Process) is an Oracle background process created when you start an instance in ARCHIVE LOG MODE. The ARCH process will archive on-line redo log files to an archive destination, typically a directory on the database server.

Check process – The following Unix/Linux command is used to check if the ARCH process are running:

$ ps -ef | grep arc
oracle    4414     1  0 20:27 ?        00:00:00 ora_arc0_orcl
oracle    4416     1  0 20:27 ?        00:00:00 ora_arc1_orcl
oracle    4418     1  0 20:27 ?        00:00:00 ora_arc2_orcl
oracle    4420     1  0 20:27 ?        00:00:00 ora_arc3_orcl

LGWR – Redo Log Writer

LGWR (LoG WRiter) is an Oracle background process created when you start a database instance. The LGWR writes the redo log buffers to the on-line redo log files. If the on-line redo log files are mirrored, all the members of the group will be written out simultaneously.

Check process – The following Unix/Linux command is used to check if the LGWR process is running:

$ ps -ef | grep lgwr
oracle   31144     1  0 11:10 ?        00:00:00 ora_lgwr_orcl

DBWn – Database Writer

DBWR (DataBase WRiter) is an Oracle background process created when you start a database instance. The DBWR writes modified data (dirty buffers) from the SGA into the Oracle database files. When the SGA data buffer cache fills the DBWR process selects buffers using an LRU algorithm and writes them to disk. There can be multiple database writer processes named DBWn.

SQL> show parameters db_writer
NAME				     TYPE	 VALUE
------------------------------------ ----------- -----
db_writer_processes		     integer	 1
SQL> SELECT spid, pname, username, program, tracefile FROM v$process WHERE pname LIKE 'DBW%';
SPID PNAME USERNAME PROGRAM
---- ----- -------- -----------------------------------
TRACEFILE
---------------------------------------------------------------------
3035 DBW0  oracle  oracle@localhost.localdomain (DBW0)
/home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_dbw0_3035.trc
SQL> ! ps -f 3035
UID        PID  PPID  C STIME TTY      STAT   TIME CMD
oracle    3035     1  0 11:16 ?        Ss     0:00 ora_dbw0_orcl

CKPT – Checkpoint process

CKPT (Oracle Checkpoint Process) is an Oracle background process that timestamps all datafiles and control files to indicate that a checkpoint has occurred. The “DBWR checkpoints” statistic (v$sysstat) indicates the number of checkpoint requests completed.

History – The ckeckpoint process was optional in Oracle 7 (set CHECKPOINT_PROCESS=TRUE) and could be enabled to speed-up checkpointing on databases with a large number of files. Starting with Oracle 8i, the checkpoint process is automatically started with the other Oracle processes at instance startup.

Check process – The following Unix/Linux command is used to check if the CKPT process is running:

$ ps -ef | grep ckpt
oracle   31144     1  0 11:10 ?        00:00:00 ora_ckpt_orcl

RECO – Recoverer

RECO (Oracle RECOverer Process) is an Oracle background process created when you start an instance with DISTRIBUTED_TRANSACTIONS= in the initialization parameter file. The RECO process will try to resolve in-doubt transactions across Oracle distributed databases.

CJQn – Job Queue Coordinator

QMNn – Queue-monitor processes

Dnnn – Dispatcher Processes (multiplex server-processes on behalf of users)

Snnn – Shared server processes (serve client-requests)

MMAN – Memory Manager process which will help in automatic memory management when use sga_target,memory_target

MMAN (Memory Manager) is a background process that manages the dynamic resizing of SGA memory areas as the workload increases or decreases. This process was introduced in Oracle 10g.

LSP0 – Logical standby coordinator process (controls Data Guard log-application)

MRP – Media-recovery process (detached recovery-server process)

MMON – This is the process which will write to AWR base tables ie WR$ tables

MMON (Manageability Monitor) is a background process that gathers memory statistics (snapshots) and stores this information in the AWR (automatic workload repository). MMON is also responsible for issuing alerts for metrics that exceed their thresholds. This process was introduced in Oracle 10g.

MMNL – Memory monitor light (gathers and stores AWR statistics)

MMNL (MMON Lite) is a background process that assists the MMON process. This process will flush the ASH buffer to AWR tables when the buffer is full or a snapshot is taken. This process was introduced in Oracle 10g.

PSP0 – Process-spawner (spawns Oracle processes)

RFS – Remote file server process (archive to a remote site)

DBRM – DB resource manager (new in 11g)

DIAGn – Diagnosability process (new in 11g)

FBDA – Flashback data archiver process (new in 11g)

VKTM – Virtual Timekeeper (new in 11g)

Wnnn – Space Management Co-ordination process (new in 11g)

SMCn – Space Manager process (new in 11g)

An instance can mount and open one and only one database.

A database can normally only be mounted and opened by one instance. However, when using Real Application Clusters (RAC) a database can be mounted and opened by many instances.

What’s the relationship between database and instance?

  • An instance can mount and open one and only one database.
  • Normally a database is mounted and opened by one instance.
  • When using RAC, a database may be mounted and opened by many instances.

Tablespaces

Disk space needs to be allocated for certain database objects (like tables and indexes). In Oracle, disk space from the operating system is allocated to tablespaces. Database objects are then created within a tablespace.

To list all tablespaces:

SELECT tablespace_name FROM dba_tablespaces; 

Commands used to create new tablespaces:

CREATE TABLESPACE ts1 DATAFILE '/u01/oradata/orcl_ts1_01.dbf' SIZE 100M;
CREATE UNDO TABLESPACE undots1 DATAFILE '/u01/oradata/orcl_undots1_01.dbf' SIZE 100M;
CREATE TEMPORARY TABLESPACE temp1 TEMPFILE '/u01/oradata/orcl_temp1_01.dbf' SIZE 100M;

Database Users

A database consists of multiple users that one can connect to. Each user has its own namespaces – objects within it cannot share the same name.

To list all the database users:

SELECT username FROM dba_users; 

To create a new user:

CREATE USER scott IDENTIFIED BY tiger;

Schema Objects

Schema objects are created within a schema (Oracle user). Here are some of the object types that can be created:

Table (heap, IOT, temporary, etc.)

table is a collection of computer data that is organized, defined and stored as rows and columns. In non-relational systems, a table is called a file. A table represents entities and relationships.

Examples

CREATE TABLE t2 (
        c1 NUMBER PRIMARY KEY,
        c2 NUMBER REFERENCES t1(c1),
        c3 VARCHAR2(30) );
CREATE TABLE t1 (c1 NUMBER PRIMARY KEY);

Index

An index is a special database object that lets you quickly locate particular records based on key column values. Indexes are essential for good database performance. The index creation process requires a temporary segment if the data cannot be sorted in memory. Indexes can be created with the nologging option.

Creating indexes

Normal indexes:

CREATE INDEX emp_ind1 ON emp(deptno);

Normal composite indexes:
CREATE INDEX emp_ind1 ON emp(empto, deptno);

Bitmap index:
CREATE BITMAP INDEX emp_ind2 ON bigemp(sex);

Function-based indexes (query rewrite must be enabled):
CREATE INDEX emp_ind3 ON emp(deptno*1.1);
CREATE INDEX emp_ind3 ON emp(deptno DESC);

View

view is a named and validated SQL query which is stored in the Oracle data dictionary. Views do not contain any data – it is just a stored query in the database that can be executed when called. One can think of a view as a virtual table or mapping of data from one or more tables.

Views are useful for security and information hiding, but can cause problems if nested too deeply. Some of the advantages of using views:

  • Reduce the complexity of SQL statements
  • Share only specific rows in a table with other users
  • Hide the NAME and OWNER of the base table

View details can be queried from the dictionary by querying either USER_VIEWS, ALL_VIEWS or DBA_VIEWS.

View types – Views can be classified as simple or complex:

Simple views – Simple views can only contain a single base table. Examples:

CREATE VIEW emp_view AS      
     SELECT * FROM emp;  
CREATE VIEW dept20      
     AS SELECT ename, sal*12 annual_salary           
          FROM emp          
        WHERE deptno = 20;  

One can perform DML operations directly against simple views. These DML changes are then applied to the view’s base table.

Complex views – Complex views can be constructed on more than one base table. In particular, complex views can contain:

  • join conditions
  • a group by clause
  • a order by clause

One cannot perform DML operations against complex views directly. To enable DML operations on complex views one needs to write INSTEAD OF triggers to tell Oracle how the changes relate to the base table(s).

Examples:

CREATE VIEW sample_complex_view AS 
SELECT emp.empno, emp.ename, emp.job, emp.deptno, dept.dname, dept.loc       FROM emp, dept; 
CREATE VIEW sample_complex_view AS   
SELECT emp.empno, emp.ename, emp.job, emp.deptno, dept.dname, dept.loc   FROM emp, dept  WHERE emp.deptno = dept.deptno; 

Read-only views – Users can only run SELECT and DESC statements against read only views. Examples: READ ONLY clause on a simple view:

CREATE VIEW clerk (id_number, person, department, position)     
         AS SELECT empno, ename, deptno, job           
              FROM emp           
              WHERE job = 'CLERK'   
         WITH READ ONLY;  

READ ONLY clause on a complex view:

CREATE VIEW sample_complex_view AS     
SELECT emp.empno, emp.ename, emp.job, emp.deptno, dept.dname, dept.loc       FROM emp, dept   
WITH READ ONLY;

WITH CHECK OPTION – The WITH CHECK OPTION clause specifies the level of checking to be done when doing DML against the view. If specified, every row that is inserted, updated or deleted through the view must conform to the definition of the view.

The problem:

SQL> CREATE VIEW d20 AS SELECT ename, sal, deptno FROM emp2 WHERE deptno = 20;
View created.
SQL> UPDATE d20 SET deptno = 10;
3 rows updated.

The solution:

SQL> CREATE VIEW d20 AS SELECT ename, sal, deptno FROM emp2 WHERE deptno = 20
  2  WITH CHECK OPTION;
View created.
SQL> UPDATE d20 SET deptno = 10;
UPDATE d20 SET deptno = 10
       *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

Materialized View (snapshot)

materialized view (MV) is similar to a view but the data is actually stored on disk (view that materializes). Materialized views are often used for summary and pre-joined tables, or just to make a snapshot of a table available on a remote system. A MV must be refreshed when the data in the underlying tables is changed.

Examples – Create a MV:

CREATE MATERIALIZED VIEW my_test_mv AS SELECT * FROM scott.emp;

Refresh a MV:

EXEC dbms_mview.refresh('MY_TEST_MV', method=>'C');

Sequence

sequence is a database object that generates unique numbers, mostly used for primary key values. One can select the NEXTVAL and CURRVAL from a sequence. Selecting the NEXTVAL will automatically increment the sequence.

Examples – Create a simple sequence:

SQL> CREATE SEQUENCE emp_seq;
 Sequence created.

Selecting from the sequence:

SQL> select emp_seq.nextval from dual;
   NEXTVAL
----------
         1

SQL> select emp_seq.nextval from dual;
   NEXTVAL
----------
         2

Note that NEXTVAL and CURRVAL returns the same value for each row of a select:

SQL> select emp_seq.nextval, emp_seq.currval, emp_seq.nextval, emp_seq.currval from dual;
   NEXTVAL    CURRVAL    NEXTVAL    CURRVAL
---------- ---------- ---------- ----------
         3          3          3          3

SQL> select emp_seq.nextval, emp_seq.currval, emp_seq.nextval, emp_seq.currval 
  2  from (select 1 from dual union all select 2 from dual)
  3  /
   NEXTVAL    CURRVAL    NEXTVAL    CURRVAL
---------- ---------- ---------- ----------
         4          4          4          4
         5          5          5          5

Creating a more complicated sequence:

CREATE SEQUENCE my_sequence
   MINVALUE 1
   MAXVALUE 1000
   START WITH 1
   INCREMENT BY 2
   CACHE 5;

Reset a sequence to a predetermined value, say from 100 to 50:

SQL> ALTER SEQUENCE seq1 INCREMENT BY -50;
SQL> SELECT seq1.nextval FROM dual;
SQL> ALTER SEQUENCE seq1 INCREMENT BY 1;

Remove/delete a sequence:

DROP SEQUENCE my_sequence_name;

LAST_NUMBER – LAST_NUMBER is influenced by the CACHE size. I saw that my LAST_NUMBER was 21 (my cache was 20). When I ran NEXTVAL I was getting a number like 4, then 5. I went up past 20 and my LAST_NUMBER jumped to 41 so everytime the cached numbers ran out another group of cached numbers would be reserved and the LAST_NUMBER field would change, but this has no relation to the CURRVAL.

Synonym

synonym is an alternative name (or alias) for an object (like an table or view) in the database. Objects can have many synonyms. Use the CREATE SYNONYM SQL command to create synonyms and the DROP SYNONYM command to remove them. To get a list of synonyms, query the USER_SYNONYMS view.

Syntax and examples

Create a synonym – make emp an alias for the scott.employees table:

CREATE SYNONYM emp FOR scott.employees;

Create a public synonym (visible to all DB users):

CREATE PUBLIC SYNONYM dual FOR sys.dual;
CREATE PUBLIC SYNONYM emp FOR scott.employees;

Drop a synonym:

DROP SYNONYM emp;

Drop a public synonym:

DROP PUBLIC SYNONYM emp;

List synonyms in the current schema:

SELECT synonym_name, table_owner, table_name FROM user_synonyms;

Cluster

The term cluster can refer to either:

  • An Oracle object that allows one to store related rows from different tables in the same data block. Table clustering is very seldomly used by Oracle DBA’s and Developers.
  • Two or more computers that share resources and work together to form a larger logical computing unit. RAC and Oracle Parallel Server can be used to access Oracle from multiple nodes of a clustered system.

Trigger

trigger is a program in a database that gets called each time a row in a table is INSERTED, UPDATED, or DELETED. Triggers allow you to check that any changes are correct, or to fill in missing information before it is COMMITed. Triggers are normally written in PL/SQL or Java.

Examples

Audit logging:

CREATE TABLE t1 (c1 NUMBER);
CREATE TABLE audit_log(stamp TIMESTAMP, usr VARCHAR2(30), new_val NUMBER);

CREATE TRIGGER t1_trig
  AFTER INSERT ON t1 FOR EACH ROW
BEGIN
  INSERT INTO audit_log VALUES (SYSTIMESTAMP, USER, :NEW.c1);
END;
/

Prevent certain DML operations:

CREATE OR REPLACE TRIGGER t1_trig
  BEFORE INSERT OR UPDATE OR DELETE
  ON t1
BEGIN
  raise_application_error(-20001,'Inserting and updating are not allowed!');
END;
/

Procedure

procedure is a block of PL/SQL code named and stored within the database.

Example

CREATE OR REPLACE PROCEDURE raise_sal(i_empno NUMBER, i_newsal NUMBER)
AS
BEGIN
  UPDATE emp SET sal = i_newsal WHERE empno = i_empno;
END;

Function

function is a block of PL/SQL code named and stored within the database. A function always returns a single value to its caller.

Creating and dropping functions

Create a function:

CREATE OR REPLACE FUNCTION mult(n1 NUMBER, n2 NUMBER) RETURN NUMBER
AS
BEGIN
  RETURN n1 * n2;
END;
/

Remove the function from the database:

DROP FUNCTION mult;

Calling functions

Call the above function from SQL:

SQL>  SELECT mult(10, 2) FROM dual;
MULT(10,2)
----------
        20

Call the above function from SQL*Plus:

SQL> VARIABLE val NUMBER
SQL> EXEC :val := mult(10, 3);
PL/SQL procedure successfully completed.
SQL> PRINT :val
       VAL
----------
        30

Calling the function from PL/SQL:

DECLARE
  v_val NUMBER;
BEGIN
  v_val := mult(10, 4);
  Dbms_output.Put_Line('Value is: '|| v_val);
END;
/

Examples

Simple lookup function (lookup an employee’s salary):

CREATE OR REPLACE FUNCTION get_salary (p_empno NUMBER)
   RETURN NUMBER
AS
  v_sal emp.sal%TYPE;
BEGIN
  SELECT sal INTO v_sal FROM emp WHERE empno = p_empno;
  RETURN v_sal;
END;
/

Package (containing procedures and functions)

package is a collection of procedures and functions stored within the database.

A package usually has a specification and a body stored separately in the database. The specification is the interface to the application and declares types, variables, exceptions, cursors and subprograms. The body implements the specification.

When a procedure or function within the package is referenced, the whole package gets loaded into memory. So when you reference another procedure or function within the package, it is already in memory.

Example

CREATE OR REPLACE PACKAGE my_pack AS
  g_visible_variable VARCHAR2(20);
  FUNCTION calc(n1 NUMBER, n2 NUMBER) RETURN NUMBER;
END;
/
CREATE OR REPLACE PACKAGE BODY my_pack AS
  g_hidden_variable CONSTANT INTEGER := 2;
  FUNCTION calc(n1 NUMBER, n2 NUMBER) RETURN NUMBER AS
  BEGIN
    RETURN g_hidden_variable * n1 * n2;
  END;
END;
/

Published by Abdul Samad

Having 17+ years of extensive experience in IT industry, enabled to enhance the team performance and maximize customer satisfaction by strategically managing calls and implementing process improvements. Demonstrated ability to solve problems, meets challenging goals, and expedites delivery. Skilled MSSQL administrator guide team during the crisis situation. Apply Creative thoughts process in re-designing the workflow system to eliminate duplication of effort and increase productivity.

Leave a Reply