ORA-01033

The "ORA-01033: ORACLE initialization or shutdown in progress." error can also happen when Oracle is attempting startup or shutdown and is "hanging" on a resource such as a failed disk, writing to redo, etc.

Oracle work with sql plus

Using SQL*Plus
SQL*Plus is a command-line tool that provides access to the Oracle RDBMS.

SQL*Plus enables you to:

Enter SQL*Plus commands to configure the SQL*Plus environment
Startup and shutdown an Oracle database
Connect to an Oracle database
Enter and execute SQL commands and PL/SQL blocks
Format and print query results
SQL*Plus is available on several platforms. In addition, it has a web-based user

interface, iSQL*Plus.

SQL*Plus is a client terminal software allowing users to interact with Oracle server to manipulate data and data structures. Users type in SQL statements in SQL*Plus that send statements to Oracle server. Oracle server then validates and executes the statements on its databases. The query results are returned to SQL*Plus and displayed to the user.  Besides sending SQL statements to the server, SQL*Plus also saves them into a local buffer and allow users to view and change the statements. The following figure illustrates the process.

After you login into SQL*Plus, at the SQL prompt, you can begin typing any SQL command. Upon hitting return (i.e., enter key) the SQL prompt will change to line number prompts. When you are finished typing a command, type / or RUN to execute the SQL command. Also, a semicolon at the end of the SQL command will execute the command immediately after hitting return. In addition to SQL commands, /, and RUN, you can also executes SQL*Plus file commands.

SQL*PLUS Commands Quick Reference

Below table shows, SQL*Plus commands available in the command-line interface. Not all commands or command parameters are shown.

How to …    SQL*Plus Command
Log in to SQL*Plus    SQLPLUS [ { username[/passward][@connect_identifier] | / }  [ AS { SYSDBA | SYSOPER } ] | /NOLOG ]
List help topics available in SQL*Plus    HELP [ INDEX | topic ]
Execute host commands    HOST [ command ]
Show SQL*Plus system variables or environment settings    SHOW { ALL | ERRORS | USER | system_variable | … }
Alter SQL*Plus system variables or environment settings    SET system_variable value
Start up a database    STARTUP PFILE = filename  [ MOUNT [ dbname ] | NOMOUNT | … ]
Connect to a database    CONNECT [ [ username [ /password ] [ @connect_identifier ]          [ / AS { SYSOPER | SYSDBA } ]
]

List column definitions for a table, view, or synonym, or specifications for afunction or procedure    DESCRIBE [ schema. ] object
Edit contents of the SQL buffer or a file    EDIT [ filename [ .ext ] ]
Get a file and load its contents into the SQLBuffer    GET filename [ .ext ] [ LIST | NOLLIST ]
Save contents of the SQL buffer to a file    SAVE filename [ .ext ] [ CREATE | REPLACE | APPEND ]
List contents of the SQL Buffer    LIST [ n | nm | n LAST | … ]
Delete contents of the SQL Buffer    DEL [ n | nm | n LAST | … ]
Add new lines following current line in the SQL buffer    INPUT [ text ]
Append text to end ofcurrent line in the SQL
buffer

APPEND text
Find and replace first occurrence of a text string in current line of the SQL buffer    CHANGE sepchar old [ sepchar [ new [ sepchar ] ] ]sepchar can be any non-alphanumeric character such as “/” or “!”
Capture query results in a file and, optionally, send contents of file to default printer    SPOOL [ filename [ .ext ]  [ CREATE | REPLACE | APPEND | OFF | OUT ]
Run SQL*Plus statements stored in a file    @ { url | filename [ .ext ] } [ arg… ]START filename [ .ext ] [ arg… ]
.ext can be omitted if the filename extension is .sql

Execute commands stored in the SQL buffer    /
List and execute commands stored in the SQL buffer    RUN
Execute a single PL/SQL statement or run a stored procedure    EXECUTE statement
Disconnect from a database    DISCONNECT
Shut down a database    SHUTDOWN [ ABORT | IMMEDIATE | NORMAL | … ]
Log out of SQL*Plus    { EXIT | QUIT }  [ SUCCESS | FAILURE | WARNING | … ]
[ COMMIT | ROLLBACK ]


SQL*Plus file command allow you to execute commands (or programs) stored in an external file, input or output data from/to a file, and save SQL commands typed during current session.

Some SQL*Plus file commands are:

SAVE filename. This allows you to save buffer contents into a file.
START filename. This allows you to execute a batch of SQL statements stored in a file.
SPOOL filename. This allows you save SQL statements together with their outputs to a file.
GET filename. This retrieve a file and places it into the buffer.
@ filename. This allows you to execute a PL/SQL procedure(s) stored in a file.
Recall that the previously executed commands (in current SQL*Plus session) are stored in the local buffer. One way to change an SQL statement in the buffer is by using the line editor. The following are a list of line edit commands.

LIST or L–Lists the contents of the buffer
LIST n or L n–Lists the contents of line number n in the buffer and makes the line current
LIST * or L *–Lists the current line
LIST m n–Lists the range from m to n line
Append text or A text–Adds to the end of the current line (e.g., “A ,” adds a comma to the end of line
INPUT or I–Adds one or more lines after the current line so you can begin adding the text.
CHANGE /text–Deletes text from the current line
CHANGE /oldtext/newtext–Replaces oldtext with newtext in the current line
DEL — Deletes the current line
Besides line editor, you can also use the vi editor if you are a fan of Unix editor!.

To invoke the vi editor, type Edit at the SQL Prompt.   Multiple SQL commands can be typed in vi editor. End each SQL command (except the last one) with a semicolon. After exiting notepad, type Start to run all of the commands.

Run SQL statements in a batch
To run SQL commands in a batch, you can put all your SQL commands into a text file and execute these commands in this file in SQL*PLUS.

Use your favorite editor to type in your SQL queries into a text file.
For Example,

$ more table.sql
DROP TABLE employee
/

commit
/

CREATE TABLE employee (
empno INTEGER NOT NULL,
name VARCHAR2(50) NOT NULL,
sal REAL NOT NULL,
primary key (empno));
/

INSERT INTO employee VALUES (1, ‘Jack’, 6000);
INSERT INTO employee VALUES (2, ‘Tom’,  6000);
INSERT INTO employee VALUES (3, ‘John’, 6000);
INSERT INTO employee VALUES (4, ‘Jane’, 6000);
/

UPDATE employee SET sal=500 WHERE name=’Jack’
/

CREATE INDEX test_index on employee(sal)
/
$

Connect into SQL*Plus, and run the batch of commands. For example, assume that you name the SQL file as table.sql.
SQL> START table.sql;

Output results
You can record your SQL command outputs to a file for output or editing purpose.
SQL> SPOOL <your file name>

For example,

SQL> SPOOL myoutput.out

All SQL commands and their outputs after this command are written into the file myoutput.out that by default is stored in the current working directory where you invoked SQL*Plus.

To end recording, use the following command:
SQL> SPOOL OFF

DUAL and select the current time
DUAL is the dummy table, mostly used to view the results from functions and calculations. The built-in function SYSDATE returns a DATE value containing the current date and time on your system. (Note Oracle is a client-server architecture and SQL*Plus is the client. SYSDATE gives you the time of the Unix system which you telnet in. It may NOT be the time of Oracle server unless you telnet into the machine running Oracle server.)

For example,

SQL> SELECT TO_CHAR(SYSDATE , 'Dy DD-Mon-YYYY HH24:MI:SS') as "Current Date/Time" FROM DUAL;;
Result:

Current Time
------------------------------------------------------------------------
Mon 15-July-2002 10:01:29
TO_CHAR is a function to format a value.
DUAL is built-in relation in Oracle which serves as a dummy relation to put in the FROM clause when nothing else is appropriate. For example, try “SELECT 2+2 FROM DUAL;”
To format a number attribute to a dollar format, use the column <attribute> format <format>:
       SQL> COLUMN salary FORMAT $999,999
To indicate the displayed width of a character string attribute, use the column <attribute> format <A’format>. For example, set the width of the name attribute to 8 characters.
       SQL> COLUMN name FORMAT A8
If a name is longer than 8 characters, the remaining is displayed at the second line (or several lines)

The set command can be used to change the default number of lines per page (14) and the number of characters per line (80).
For example, to set the number of lines per page to 60, use the following command:

   SQL> SET PAGESIZE 60
All formatting remain active until they are cleared or reset or after you exit from SQL*Plus.
   SQL> CLEAR COLUMN
If you forget a specific SQL command you could enter
SQL> HELP <the SQL command>;

You could also find out all commands by entering:

SQL> HELP menu;

Sometimes when you get something fuzzy, you  can try the following
SQL> SET SERVEROUTPUT ON
SQL> SET ARRAYSIZE 1

CONNECT BY LEVEL

A condition that identifies the relationship between parent rows and child rows of the hierarchy

CREATE TABLE t (
rid NUMBER(12),
col1 VARCHAR2(30),
col2 VARCHAR2(300));

CREATE SEQUENCE seq_t_rid;

INSERT INTO t
SELECT seq_t_rid.NEXTVAL, dbms_crypto.randombytes(15), dbms_crypto.randombytes(150)
FROM DUAL
CONNECT BY LEVEL <= 1000;

Oracle Types of Triggers

Types of Triggers

This section describes the different types of triggers:

• Row Triggers and Statement Triggers
• BEFORE and AFTER Triggers
• INSTEAD OF Triggers
• Triggers on System Events and User Events

Row Triggers and Statement Triggers

When you define a trigger, you can specify the number of times the trigger action is to be run:

• Once for every row affected by the triggering statement, such as a trigger fired by an UPDATE statement that updates many rows
• Once for the triggering statement, no matter how many rows it affects

Row Triggers

A row trigger is fired each time the table is affected by the triggering statement. For example, if an UPDATEstatement updates multiple rows of a table, a row trigger is fired once for each row affected by the UPDATEstatement. If a triggering statement affects no rows, a row trigger is not run.

Row triggers are useful if the code in the trigger action depends on data provided by the triggering statement or rows that are affected. For example, Figure 22-3 illustrates a row trigger that uses the values of each row affected by the triggering statement.

Statement Triggers

A statement trigger is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects, even if no rows are affected. For example, if a DELETE statement deletes several rows from a table, a statement-level DELETE trigger is fired only once.

Statement triggers are useful if the code in the trigger action does not depend on the data provided by the triggering statement or the rows affected. For example, use a statement trigger to:

• Make a complex security check on the current time or user
• Generate a single audit record

BEFORE and AFTER Triggers

When defining a trigger, you can specify the trigger timing—whether the trigger action is to be run before or after the triggering statement. BEFORE and AFTER apply to both statement and row triggers.

BEFORE and AFTER triggers fired by DML statements can be defined only on tables, not on views. However, triggers on the base tables of a view are fired if an INSERT, UPDATE, or DELETE statement is issued against the view. BEFORE and AFTER triggers fired by DDL statements can be defined only on the database or a schema, not on particular tables.

See Also:

• "INSTEAD OF Triggers"
• "Triggers on System Events and User Events" for information about how BEFORE and AFTERtriggers can be used to publish information about DML and DDL statements

BEFORE Triggers

BEFORE triggers run the trigger action before the triggering statement is run. This type of trigger is commonly used in the following situations:

• When the trigger action determines whether the triggering statement should be allowed to complete. Using a BEFORE trigger for this purpose, you can eliminate unnecessary processing of the triggering statement and its eventual rollback in cases where an exception is raised in the trigger action.
• To derive specific column values before completing a triggering INSERT or UPDATE statement.

AFTER Triggers

AFTER triggers run the trigger action after the triggering statement is run.

Trigger Type Combinations

Using the options listed previously, you can create four types of row and statement triggers:

• BEFORE statement trigger
  Before executing the triggering statement, the trigger action is run.
• BEFORE row trigger
  Before modifying each row affected by the triggering statement and before checking appropriate integrity constraints, the trigger action is run, if the trigger restriction was not violated.
• AFTER statement trigger
  After executing the triggering statement and applying any deferred integrity constraints, the trigger action is run.
• AFTER row trigger
  After modifying each row affected by the triggering statement and possibly applying appropriate integrity constraints, the trigger action is run for the current row provided the trigger restriction was not violated. Unlike BEFORE row triggers, AFTER row triggers lock rows.

You can have multiple triggers of the same type for the same statement for any given table. For example, you can have two BEFORE statement triggers for UPDATE statements on the employees table. Multiple triggers of the same type permit modular installation of applications that have triggers on the same tables. Also, Oracle materialized view logs use AFTER row triggers, so you can design your own AFTER row trigger in addition to the Oracle-defined AFTER row trigger.

You can create as many triggers of the preceding different types as you need for each type of DML statement, (INSERT, UPDATE, or DELETE).

See Also:

Oracle Database Application Developer's Guide - Fundamentals for examples of trigger applications

INSTEAD OF Triggers

INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through DML statements (INSERT, UPDATE, and DELETE). These triggers are called INSTEAD OF triggers because, unlike other types of triggers, Oracle fires the trigger instead of executing the triggering statement.

You can write normal INSERT, UPDATE, and DELETE statements against the view and the INSTEAD OF trigger is fired to update the underlying tables appropriately. INSTEAD OF triggers are activated for each row of the view that gets modified.

Modify Views

Modifying views can have ambiguous results:

• Deleting a row in a view could either mean deleting it from the base table or updating some values so that it is no longer selected by the view.
• Inserting a row in a view could either mean inserting a new row into the base table or updating an existing row so that it is projected by the view.
• Updating a column in a view that involves joins might change the semantics of other columns that are not projected by the view.

Object views present additional problems. For example, a key use of object views is to represent master/detail relationships. This operation inevitably involves joins, but modifying joins is inherently ambiguous.

As a result of these ambiguities, there are many restrictions on which views are modifiable. An INSTEAD OFtrigger can be used on object views as well as relational views that are not otherwise modifiable.

A view is inherently modifiable if data can be inserted, updated, or deleted without using INSTEAD OFtriggers and if it conforms to the restrictions listed as follows. Even if the view is inherently modifiable, you might want to perform validations on the values being inserted, updated or deleted. INSTEAD OF triggers can also be used in this case. Here the trigger code performs the validation on the rows being modified and if valid, propagate the changes to the underlying tables.

INSTEAD OF triggers also enable you to modify object view instances on the client-side through OCI. To modify an object materialized by an object view in the client-side object cache and flush it back to the persistent store, you must specify INSTEAD OF triggers, unless the object view is inherently modifiable. However, it is not necessary to define these triggers for just pinning and reading the view object in the object cache.

See Also:

• Chapter 27, "Object Datatypes and Object Views"
• Oracle Call Interface Programmer's Guide
• Oracle Database Application Developer's Guide - Fundamentals for an example of anINSTEAD OF trigger

Views That Are Not Modifiable

If the view query contains any of the following constructs, the view is not inherently modifiable and you therefore cannot perform inserts, updates, or deletes on the view:

• Set operators
• Aggregate functions
• GROUP BY, CONNECT BY, or START WITH clauses
• The DISTINCT operator
• Joins (however, some join views are updatable)

If a view contains pseudocolumns or expressions, you can only update the view with an UPDATE statement that does not refer to any of the pseudocolumns or expressions.

See Also:

"Updatable Join Views"

INSTEAD OF Triggers on Nested Tables

You cannot modify the elements of a nested table column in a view directly with the TABLE clause. However, you can do so by defining an INSTEAD OF trigger on the nested table column of the view. The triggers on the nested tables fire if a nested table element is updated, inserted, or deleted and handle the actual modifications to the underlying tables.

See Also:

• Oracle Database Application Developer's Guide - Fundamentals
• Oracle Database SQL Reference for information on the CREATE TRIGGER statement

Triggers on System Events and User Events

You can use triggers to publish information about database events to subscribers. Applications can subscribe to database events just as they subscribe to messages from other applications. These database events can include:

• System events
  • Database startup and shutdown
  • Data Guard role transitions
  • Server error message events
• User events
  • User logon and logoff
  • DDL statements (CREATE, ALTER, and DROP)
  • DML statements (INSERT, DELETE, and UPDATE)

Triggers on system events can be defined at the database level or schema level. The DBMS_AQ package is one example of using database triggers to perform certain actions. For example, a database shutdown trigger is defined at the database level:

CREATE TRIGGER register_shutdown 
  ON DATABASE 
  SHUTDOWN 
    BEGIN 
    ...
    DBMS_AQ.ENQUEUE(...); 
    ... 
    END; 
    

Triggers on DDL statements or logon/logoff events can also be defined at the database level or schema level. Triggers on DML statements can be defined on a table or view. A trigger defined at the database level fires for all users, and a trigger defined at the schema or table level fires only when the triggering event involves that schema or table.

Event Publication

Event publication uses the publish-subscribe mechanism of Oracle Streams Advanced Queuing. A queueserves as a message repository for subjects of interest to various subscribers. Triggers use the DBMS_AQpackage to enqueue a message when specific system or user events occur.

See Also:

• Oracle Streams Advanced Queuing User's Guide and Reference
• Oracle Database PL/SQL Packages and Types Reference

Event Attributes

Each event allows the use of attributes within the trigger text. For example, the database startup and shutdown triggers have attributes for the instance number and the database name, and the logon and logoff triggers have attributes for the user name. You can specify a function with the same name as an attribute when you create a trigger if you want to publish that attribute when the event occurs. The attribute's value is then passed to the function or payload when the trigger fires. For triggers on DML statements, the :OLD column values pass the attribute's value to the :NEW column value.

System Events

System events that can fire triggers are related to instance startup and shutdown and error messages. Triggers created on startup and shutdown events have to be associated with the database. Triggers created on error events can be associated with the database or with a schema.

• STARTUP triggers fire when the database is opened by an instance. Their attributes include the system event, instance number, and database name.
• SHUTDOWN triggers fire just before the server starts shutting down an instance. You can use these triggers to make subscribing applications shut down completely when the database shuts down. For abnormal instance shutdown, these triggers cannot be fired. The attributes of SHUTDOWN triggers include the system event, instance number, and database name.
• SERVERERROR triggers fire when a specified error occurs, or when any error occurs if no error number is specified. Their attributes include the system event and error number.
• DB_ROLE_CHANGE triggers fire when a role transition (failover or switchover) occurs in a Data Guard configuration. The trigger notifies users when a role transition occurs, so that client connections can be processed on the new primary database and applications can continue to run.

User Events

User events that can fire triggers are related to user logon and logoff, DDL statements, and DML statements.

Triggers on LOGON and LOGOFF Events

LOGON and LOGOFF triggers can be associated with the database or with a schema. Their attributes include the system event and user name, and they can specify simple conditions on USERID and USERNAME.

• LOGON triggers fire after a successful logon of a user.
• LOGOFF triggers fire at the start of a user logoff.

Triggers on DDL Statements

DDL triggers can be associated with the database or with a schema. Their attributes include the system event, the type of schema object, and its name. They can specify simple conditions on the type and name of the schema object, as well as functions like USERID and USERNAME. DDL triggers include the following types of triggers:

• BEFORE CREATE and AFTER CREATE triggers fire when a schema object is created in the database or schema.
• BEFORE ALTER and AFTER ALTER triggers fire when a schema object is altered in the database or schema.
• BEFORE DROP and AFTER DROP triggers fire when a schema object is dropped from the database or schema.

Triggers on DML Statements

DML triggers for event publication are associated with a table. They can be either BEFORE or AFTER triggers that fire for each row on which the specified DML operation occurs. You cannot use INSTEAD OF triggers on views to publish events related to DML statements—instead, you can publish events using BEFORE or AFTERtriggers for the DML operations on a view's underlying tables that are caused by INSTEAD OF triggers.

The attributes of DML triggers for event publication include the system event and the columns defined by the user in the SELECT list. They can specify simple conditions on the type and name of the schema object, as well as functions (such as UID, USER, USERENV, and SYSDATE), pseudocolumns, and columns. The columns can be prefixed by :OLD and :NEW for old and new values. Triggers on DML statements include the following triggers:

• BEFORE INSERT and AFTER INSERT triggers fire for each row inserted into the table.
• BEFORE UPDATE and AFTER UPDATE triggers fire for each row updated in the table.
• BEFORE DELETE and AFTER DELETE triggers fire for each row deleted from the table.

See Also:

• "Row Triggers"
• "BEFORE and AFTER Triggers"
• Oracle Database Application Developer's Guide - Fundamentals for more information about event publication using triggers on system events and user events

Trigger Execution

A trigger is in either of two distinct modes:

Trigger Mode	Definition
Enabled	An enabled trigger runs its trigger action if a triggering statement is issued and the trigger restriction (if any) evaluates to true.
Disabled	A disabled trigger does not run its trigger action, even if a triggering statement is issued and the trigger restriction (if any) would evaluate to true.

For enabled triggers, Oracle automatically performs the following actions:

• Oracle runs triggers of each type in a planned firing sequence when more than one trigger is fired by a single SQL statement. First, statement level triggers are fired, and then row level triggers are fired.
• Oracle performs integrity constraint checking at a set point in time with respect to the different types of triggers and guarantees that triggers cannot compromise integrity constraints.
• Oracle provides read-consistent views for queries and constraints.
• Oracle manages the dependencies among triggers and schema objects referenced in the code of the trigger action
• Oracle uses two-phase commit if a trigger updates remote tables in a distributed database.
• Oracle fires multiple triggers in an unspecified, random order, if more than one trigger of the same type exists for a given statement; that is, triggers of the same type for the same statement are not guaranteed to fire in any specific order.

The Execution Model for Triggers and Integrity Constraint Checking

A single SQL statement can potentially fire up to four types of triggers:

• BEFORE row triggers
• BEFORE statement triggers
• AFTER row triggers
• AFTER statement triggers

A triggering statement or a statement within a trigger can cause one or more integrity constraints to be checked. Also, triggers can contain statements that cause other triggers to fire (cascading triggers).

Oracle uses the following execution model to maintain the proper firing sequence of multiple triggers and constraint checking:

1. Run all BEFORE statement triggers that apply to the statement.
2. Loop for each row affected by the SQL statement.
   1. Run all BEFORE row triggers that apply to the statement.
   2. Lock and change row, and perform integrity constraint checking. (The lock is not released until the transaction is committed.)
   3. Run all AFTER row triggers that apply to the statement.
3. Complete deferred integrity constraint checking.
4. Run all AFTER statement triggers that apply to the statement.

The definition of the execution model is recursive. For example, a given SQL statement can cause a BEFORErow trigger to be fired and an integrity constraint to be checked. That BEFORE row trigger, in turn, might perform an update that causes an integrity constraint to be checked and an AFTER statement trigger to be fired. The AFTER statement trigger causes an integrity constraint to be checked. In this case, the execution model runs the steps recursively, as follows:

Original SQL statement issued.

1. BEFORE row triggers fired.
   1. AFTER statement triggers fired by UPDATE in BEFORE row trigger.
      i. Statements of AFTER statement triggers run.
      ii. Integrity constraint checked on tables changed by AFTER statement triggers.
   2. Statements of BEFORE row triggers run.
   3. Integrity constraint checked on tables changed by BEFORE row triggers.
2. SQL statement run.
3. Integrity constraint from SQL statement checked.

There are two exceptions to this recursion:

• When a triggering statement modifies one table in a referential constraint (either the primary key or foreign key table), and a triggered statement modifies the other, only the triggering statement will check the integrity constraint. This allows row triggers to enhance referential integrity.
• Statement triggers fired due to DELETE CASCADE and DELETE SET NULL are fired before and after the user DELETE statement, not before and after the individual enforcement statements. This prevents those statement triggers from encountering mutating errors.

An important property of the execution model is that all actions and checks done as a result of a SQL statement must succeed. If an exception is raised within a trigger, and the exception is not explicitly handled, all actions performed as a result of the original SQL statement, including the actions performed by fired triggers, are rolled back. Thus, integrity constraints cannot be compromised by triggers. The execution model takes into account integrity constraints and disallows triggers that violate declarative integrity constraints.

For example, in the previously outlined scenario, suppose that the integrity constraint is violated. As a result of this violation, all changes made by the SQL statement, the fired BEFORE row trigger, and the fired AFTERstatement trigger are rolled back.

Note:

Although triggers of different types are fired in a specific order, triggers of the same type for the same statement are not guaranteed to fire in any specific order. For example, all BEFORE rowtriggers for a single UPDATE statement may not always fire in the same order. Design your applications so they do not rely on the firing order of multiple triggers of the same type.

Data Access for Triggers

When a trigger is fired, the tables referenced in the trigger action might be currently undergoing changes by SQL statements in other users' transactions. In all cases, the SQL statements run within triggers follow the common rules used for standalone SQL statements. In particular, if an uncommitted transaction has modified values that a trigger being fired either needs to read (query) or write (update), then the SQL statements in the body of the trigger being fired use the following guidelines:

• Queries see the current read-consistent materialized view of referenced tables and any data changed within the same transaction.
• Updates wait for existing data locks to be released before proceeding.

Storage of PL/SQL Triggers

Oracle stores PL/SQL triggers in compiled form, just like stored procedures. When a CREATE TRIGGERstatement commits, the compiled PL/SQL code, called P code (for pseudocode), is stored in the database and the source code of the trigger is flushed from the shared pool.

See Also:

Oracle Database PL/SQL User's Guide and Reference for more information about compiling and storing PL/SQL code

Execution of Triggers

Oracle runs a trigger internally using the same steps used for procedure execution. The only subtle difference is that a user has the right to fire a trigger if he or she has the privilege to run the triggering statement. Other than this, triggers are validated and run the same way as stored procedures