FOR UPDATE , NO WAIT | Oracle

When a SELECT statement picks the data from any table, the rows are not locked. And while the user performs some operation on the data, there is a chance that some other user may delete or modify the data. 

To resolve this, we can use the FOR UPDATE clause, which locks the rows picked up in the select statement and releases the locks only after the code block is exited. Ex- 

CURSOR cursor_name
IS
   select_statement

   FOR UPDATE [OF column_list] [NOWAIT] ;

This cursors select statement has put a lock on the selected rows and will be released only after the cursor is closed.

The NOWAIT clause is optional and it means that the control will not wait for the resources to be available. the clause provides the count of seconds until when the control waits for the resources to be available.

Mutating Trigger | Oracle

Found this small reference to the mutating trigger error.

The most likely cause of a mutating table error is the misuse of triggers. Here is a typical example:

1. you insert a row in table A.
2. A trigger on table A (for each row) executes a query on table A, for example to compute a summary column.
3. Oracle throws an ORA-04091: table A is mutating, trigger/function may not see it

This is an expected and normal behaviour, Oracle wants to protect you from yourself since Oracle guarantees:

• (i) that each statement is atomic (i.e will either fail or succeed completely)
• (ii) that each statement sees a consistent view of the data

Most likely when you write this kind of trigger you would expect the query (2) to see the row inserted on (1). This would be in contradiction with both points above since the update is not finished yet (there could be more rows to be inserted).

Oracle could return the result consistent with a point in time just before the beginning of the statement but from most of the examples I have seen that try to implement this logic, people see a multi-row statement as a series of successive steps and expect the statement [2] to see the changes made by the previous steps. Oracle can not return the expected result and therefore throws the error.

If as I suspect the cause of the mutating table error is a trigger, one way to avoid the error is to move the logic out of the trigger into procedures.

Oracle MERGE Statement

It was introduced in Oracle 9i, to insert or update the data in a table conditionally. Its also called upsert.

It is faster as it reduces table scans.

Found this example where data from the hr table is merged with the employees table. Here we have MERGE INTO statement, instead of the usual INSERT INTO. And the table from which the new data is to be picked, is specified using USING keyword.

The next step is to specify the merge condition, based on which the data will be checked. 

When the condition will match, an update will be fired to update the employees table, otherwise if not matched, the data will be inserted into employees table.

MERGE INTO employees e
    USING hr h
    ON (e.id = h.emp_id)
  WHEN MATCHED THEN
    UPDATE SET e.address = h.address
  WHEN NOT MATCHED THEN
    INSERT (id, address)

    VALUES (h.emp_id, h.address);


MERGE statement enhancements in 10g
There are few additions done to make merge statement more flexible.
- Now, we can skip one of the MATCHED or NOT MATCHED blocks.
- a WHERE condition can also be added to the insert/update part.
- a DELETE statement can also be added to the insert/update part but only on the MATCHED block.

REF Cursor | Oracle

Using REF CURSORs (or cursor variable)is one of the most powerful, flexible, and scalable ways to return query results.

Its a datatype whose value is the memory address of a SQL query result. So, basically its a pointer to a result-set. It allows a user to pass the reference of this resultset among all the programs that need access to it.

Like a normal cursor, its data is accessible in the forward only direction. And also a REF CURSOR is read-only and its data cannot be updated.

TYPES
- Strong REF cursor
- Weak REF cursor

While declaring, if the return type of ref cursor is defined, it becomes a strong cursor.

TYPE emp_curs IS REF CURSOR
RETURNING emp%ROWTYPE;

And if the returning block, returns a ref cursor of a different type, then the one defined, it will raise a "ROWTYPE_MISMATCH" exception.

Diff between a cursor and REF cursor
At the basic level, they are both same. But a normal cursor is quite static in its definition, and its query cannot be changed. Whereas a REF cursor can be opened and used as per the requirement.

And a REF cursor can be passed across program blocks, but a normal cursor can't.

----------

A REF cursor is a variable , so it can be passed as parameter across program blocks.

TYPE emp_curs IS REF CURSOR
RETURNING emp%ROWTYPE;

se emp_curs;

Here the se is a variable of ref cursor type and it can be used for emp table records.


oracle-plsql-ref-cursors

For And For all

https://venzi.wordpress.com/2007/09/27/bulk-collect-forall-vs-cursor-for-loop/

FOR is an actual loop which will go through records one by one and do some processing. 

FORALL is NOT an actual loop, it's just a notation for a bulk DML operation. It will NOT go through rows one by one. For example, you can do some row processing in a FOR loop, but you won't be able to do it in FORALL.

Context Switching | Oracle

Context Switching – There is pl/sql engine for pl/sql code and sql engine for sql queries. Every time the control is passed between any of these two engines, its called context switching.

Context switching takes time and creates a delay in execution of the code. It’s a necessary requirement for the code, but too much switches will make the code work slow.

Ex- SELECT  emp_id,

       Dept_name(emp_id)

FROM emp

WHERE emp_id = v_id;

The above code is part of a procedure(plsql eng). When the code executes the query, the control is passed to (sql eng). While, executing query, the func dept_name is encountered and control is passed to (plsql eng). This function itself has a sql query to generate the department name and hence control goes to (sql eng) again. This is not a good practise and is not recommended. 

Oracle | DATA DICTIONARY

DATA DICTIONARY

-- set of read only tables, provide info about the database
-- contains - definition of all schema objects (tables, views, indexes, sequences, synonyms, functions, procs, packages, clusters etc.)
>> how much space is allocated to and is used by a schema object
>> integrity constraints
>> default values of the columns
>> names of the users, their privileges and roles
>> who accessed and updated which db object
>> other general db related info

data dictionary is structured in tables and views. It is stored in the SYSTEM tablespace of a database.

It consists of : BASE TABLES - which store all the db related info. user access to these tables is not advised, and only Oracle database should access these tables. Most info stored in these tables is in cryptic form.

USER-ACCESSIBLE VIEWS - summarize the info stored in BASE TABLES into userful informative views. Most of the views are intended for every user, but there are few which are for administrators only.
These views are grouped into 3 classes - 
DBA_ - These views have some additional info for database administrators
ALL_ - accessible to all users, contains objects to which the user has privileges
USER_ - accessible to all users, contains objects owned by the user

The system supplied view DICTIONARY has names and desc of all the data dictionary views.

******
select OBJECT_NAME from USER_OBJECTS
WHERE object_type = 'TABLE' 

This query will give the list of all the user tables in the schema.
******
select *  from ALL_OBJECTS
WHERE object_type = 'TABLE' 

This query will give all the tables , user created as well as Oracle supplied like DUAL.

Oracle PARTITIONS | Oracle

@@@@ PARTITIONS @@@@

It makes manging large tables & indexes management easy. Its done through a new type of storage structure called PARTIOTION.

A partition is an independent object; it may also have its independent storage characteristics.

However, from an application's perspective, there is just only one schema. No change is required for the DML statements.

IOTs can also be partitioned like a normal table.

BENEFITS - Increased availability, increased query performance for data warehouses, decreased contention for a single resource in OLTP systems, easy administration of schema objects(DDL)

Each partition should have the same logical attributes like column names, constraints; but they can have different logical attributes like tablespace to which it belongs.

PARTITION KEY - one or more columns which determine the partition to which a row of data will go. Ex- In a range partition, the date is the parameter on the basis of which we can partiition a table data.

PARTITIONING STRETEGIES - it defines the conditions based on which the data is partitioned. There are 3 available - RANGE, LIST and HASH.
RANGE - most common type, PARTITION BY RANGE(colname)
**database creates interval partitions for data beyond that transition point. Interval partitions extend range partitioning by instructing the database to create partitions of the specified range or interval automatically when data inserted into the table exceeds all of the range partitions. the SALES_2001 partition contains rows with partitioning key time_id values greater than or equal to 01-JAN-2001.
LIST - partitioning key is based on a set of values and based on those values , the data is partitioned. PARTITION colname VALUES (set of values)
HASH - hash algo is applied on the key to select the partition to which the data will be added. Useful for OLTP systems where high update contention is there. Increases availability. 
PARTITION BY HASH(colname)
you cannot specify to which partition what data should go, and oracle distributes the data evenly across all partitions.

PARTITIONED TABLES - 
partitioned table is different from a non-partitioned table. The normal table has its data allocated to table segment where as in case of partitioned tables, the data is allocated to partition segment. Each partition segment has a portion of data allocated to it.

PARTITIONED INDEXES - 
Like a partitioned table, the partitioned index is decomposed into smaller pieces. 
Global(B-Tree index) indexes are partitioned independent of their table where as local partitioned indexes are automatically linked to its table partition.

How to Load Data into Oracle | Fast Load

There are many ways to load data into Oracle -- Each of these processes varies in speed, simplicity, scalability, recoverability and data availability. There is no single best way to load data into Oracle and it varies with all the criterias mentioned above.

>>SQL*Loader
>>>>Data pump import and export
>>>>>>External tables


>>SQL*Loader >> Used when we have to load huge volumes of data into database from external sources, like a csv file.

It is used to load data from a single or multiple files in the same load session. We can also perform various data manipulations/ validations before loading. Selective load of data is also possible.

>>>>External tables >> They allow you to access tables outside the database, as they are actually present in the database. They are quite similar to how we use sql*loader. Its a convenient way to use sql*loader functionality. When an external table is queried, Oracle parses the flat file and provides data to the  user in such a manner as if the data is stored in the database itself.
The user can define the structure of the flat file and then SQL query is used to generate iska kaam.

>>Data pump import and export >> This utility is used to transport oracle objects between different Oracle databases, regardless of the hardware and software.

LISTAGG Aggregate Function | Oracle

LISTAGG Aggregate function @@@@

The function concatenates the results in a single row. Like if you want to check which covers exist for a transaction in the agreement table. Check out the following query :

select policy_no, trans_id, listagg(product_line_id,'-') within group (order by agr_line_seq_no desc) agg_data
from agreement_line
where 1=1 
and policy_no = 12345678  
group by policy_no, trans_id;

The data is grouped on the basis of policy and transaction number. The aggregate function will show the result as a concatenated string:

POLICY_NO TRANS_ID AGG_DATA
12345678 28310344 CAGUU-CAGIS-CAGIP-CAGIJ-CAGII-CAGIA-CAGFA-CAGPP
12345678 29437966 CAGUU-CAGIS-CAGIP-CAGIJ-CAGII-CAGIA-CAGFA-CAGPP
12345678 29456666 CAGUU-CAGIS-CAGIP-CAGIJ-CAGII-CAGIA-CAGFA-CAGPP

Order of execution of Triggers

Order of execution of Triggers

If 2 or more triggers are defined on the same statement of a table, then the order of execution of them is –

1. All before statement triggers
2. All before row triggers
3. All after row triggers
4. All after statement triggers

But, along with this, if we have 2 or more triggers defined at the same level, then we can also order their execution. Oracle has provided the FOLLOWS keyword for this.

Oracle Versions | Oracle

This page is check and visualize all the new changes that were introduced in the various updates of Oracle. 

I will start with the addition of an image with I found in some presentation a while ago.

Oracle 9i :

• Flashback Queries (allows user to view the data of a query, that existed at a particular instant in time)

Oracle 10g :

• Flashback Version Query

Oracle Pseudocolumns | Oracle

Named pseudocolumns as they are not actually part of the table, so not actually stored in the table.

Data of these columns can only be selected, but cannot be inserted, updated or deleted.

Some of the pseudocolumns available in Oracle are:

• Hierarchical Query Pseudocolumns
• Sequence Pseudocolumns
• Version Query Pseudocolumns
• COLUMN_VALUE Pseudocolumn
• OBJECT_ID Pseudocolumn
• OBJECT_VALUE Pseudocolumn
• ROWID Pseudocolumn
• ROWNUM Pseudocolumn

1. Hierarchical Query Pseudocolumn :
                Used only in hierarchical queries. Examples - 
CONNECT_BY_ISCYCLE - returns 1 if the parent row is also the child row of the current row, otherwise it returns 0. This is used specifically for checking the following error

ERROR:
ORA-01436: CONNECT BY loop in user data


But it can only be used in the case when the CONNECT BY clause has used the NOCYCLE parameter.

Example reference for the cycle


CONNECT_BY_ISLEAF - returns 1 if the current row is the leaf of the tree defined in the CONNECT BY condition. Otherwise it returns 0.

LEVEL - returns data based on the level of the current row in the inverted tree. 1 for the root row, 2 for its child, 3 for the next child in the tree and so on.

2. Sequence Pseudocolumns :
            Sequence is a schema object which can generate unique sequential values. These values can be used for primary key of the table.
The CURRVAL and NEXTVAL are the pseudocolumns that are used for the sequence.

3. Version Query Pseudocolumns :
            Applicable only in the flashback queries, specifically flashback version queries. The pseudocolumns are:

• VERSIONS_STARTTIME: Returns the timestamp of the first version of the rows returned by the query.
• VERSIONS_STARTSCN: Returns the SCN of the first version of the rows returned by the query.
• VERSIONS_ENDTIME: Returns the timestamp of the last version of the rows returned by the query.
• VERSIONS_ENDSCN: Returns the SCN of the last version of the rows returned by the query.
• VERSIONS_XID: For each version of each row, returns the transaction ID (a RAW number) of the transaction that created that row version.
• VERSIONS_OPERATION: For each version of each row, returns a single character representing the operation that caused that row version. The values returned are I (for an insert operation), U (for an update operation) or D (for a delete operation).

4. COLUMN_VALUE pseudocolumn :
            Used when a virtual table is created using the TABLE function.

5. OBJECT_ID pseudocolumn :
            returns the object identifier of the column in object table or view. Used mostly when INSTEAD OF trigger is used for any view.

6. OBJECT_VALUE pseudocolumn :
           returns the system generated names of the columns used in object table.

7. ROWID pseudocolumn :
          contains the address of each row of the table. It is the fastest way to access a row in the database.

Usually each row in the database has a unique rowid value. However, the rows of different tables stored together in the same cluster can have same rowids.

It contains the following information required to locate a row:
a> data object number of the object
b> the datafile of the tablsespace in which the row resides
c> datablock in the datafile where the row is stored
d> inside that datablock, the position of the row. The first row is termed as position 0.

8. ROWNUM pseudocolumn : 
         ROW_NUMBER build-in SQL function provides a unique way to order the resultset. The ROWNUM column is used to order the resultset of a query. Each row is assigned a unique number in the order(1,2,3,4,5...)

If order by clause is used in the same query where rownum is used, the order by will force the results to be reordered. But if the order by is used in a sub-query and rownum is used in the top level query, the rownum will be used on the ordered data.


References:

Main Article

List of Topics | Oracle

List of topics :

The post contains the list of  all the common topics that should be covered while preparing for Oracle interiews.

The page will be updated constantly as new topics and subtopics will be added.

# Pseudocolumns