Every Oracle database requires a mechanism to undo transactional changes. All the changes made by the dml transactions can be done undone using rollback segments until oracle 8i.  Oracle has introduced an  advanced feature called Automatic undo tablespace management from oracle version 9.0.0.   In contrast to the Roll back segment management, Oracle automatically manages all the operations like adding the segments, shrinking the segments, naming the segments, managing them, making the best possible use of undo space.

All the operations specified below involves an undo tablespace.

1. Revert back changes done with a rollback command.
2. Providing read consistency to Oracle sessions.
3. analyzing or retrieving the data at an earlier point of time using a flash back query.
4. Recover the database.

Creation of Undo tablespace :

You can create an undo tablespace at the time of creation of the database with ‘CREATE DATABASE’ command or later on you can add an UNDO tablespace with ‘CREATE UNDO TABLESPACE’ COMMAND. If Automatic undo management is enabled, and if  no Single undo tablespace available, Oracle makes use of  SYSTEM tablespace for undo management which is not recommended. Oracle also creates a warning in alert.log file, if it uses the system tablespace for UNDO management.

For automatic undo tablespace management by oracle, there should be atleast one active UNDO tablespace per instance.

Undo tablespace creation example  by using the create database command.

CREATE database strinix
 controlfile reuse
 datafile ‘/u01/oradata/strinix/system_01.dbf’ size 300M
undo tablespace undo_tbs_`
 datafile ‘/u02/oradata/strinix/undo_tbs_01.dbf’ size 600M
logfile
   group 1
    (’/u10/oradata/strinix/redo_g1m1.log’) size 25M,
    . . . . . . . . . . . . . . . .
   
;

When creating A new database, if Undo TS is specified but no ‘undo tablespace’ in ‘CREATE DATABASE’ command, the database creation fails with the following errors.

ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-30045: No undo tablespace name specified

Undo tablespace creation example by using the create undo tablespace command.

CREATE undo tablespace undo_tbs
 datafile ‘/u02/oradata/strinix/undo_tbs_01.dbf’ size 600M;

Drop UNDO TABLESPACE:

You can drop an undo tablespace similar to other tablespaces.

Drop undo tablespace example :

DROP TABLESPACE undotbs_01;

The above statement would work only if your undo tablespace is not being used by the system currently. If an UNDO TABLESPACE consists of pending transactions, DROP TABLESPACE statement will fail.

You can force undo tablespace to be dropped by the following command :

DROP TABLESPACE …..INCLUDING CONTENTS

 Undo tablespace Characteristics:

• No Permanent objects can be created in UNDO TABLESPACE (ORACLE WILL THROW ORA-30022) error.
• You can change the data file size , add data files to the undo tablespace similiar like other tablespaces.
• Changes are permitted in data file properties.
• You can alter the tablespace for online Backups.
• You cannot offline an active undo tablespace.
• You cannot change extent sizes of an undo tablespace.

Automatic Undo segments:

The names of Automatic Undo segments are system generated. They are in the form of _SYSSMUn$  (n is the undo segment number,  usn). The number of Initial undo segments created depends upon the SESSIONS parameter.  More automatic undo segments can be  brought online, or created, as needed, provided undo space is available. One transaction per undo segment is desired reach.

Dynamic Extents Transfer:: Reusing expired (or unexpired) undo extents from other undo segments

Shrinking of Undo Segments: Every 12 hours SMON shrinks idled undo segments.Foreground processes signal SMON to shrink undo segments when more undo space is needed

UNDO RETENTION :

 UNDO RETENTION specifies the duration of undo information to be held in the undo tablespace before overwriting. if a transaction is committed, there is no need to hold the undo information, but to provide the read consistency oracle tries to hold the undo information atleast for the specified duration. But holding of the undo information is not guranteed. If sufficient space is not available in undo tablespace, Oracle can overwrite the undo information.If any of this overwritten undo information is required for consistent read in a current long-running query, the query could fail with the ora-01555 : snapshot too old error message.

UNDO RETENTION period is specified in seconds. The default value for undo retention period is 900 seconds. Maximum value for  Undo retention is 4294967295 SECONDS. For a fixed size undo tablespace the parameter “undo_retention“  is ignored. Obviously  it would not be a best practice to keep an undo tablespace in an unlimited size. The other way is to fix the maximum size of a tablespace and enable the autoextend option.

An example of autoextending a tablespace fixing the maximum size

CREATE undo tablespace undo_tbs
 datafile ‘/u02/oradata/strinix/undo_tbs_01.dbf’ AUTOEXTEND ON NEXT 10M  maxsize 800M;

You can also change UNDO_RETENTION PERIOD immediately by using the following command.

ALTER SYSTEM SET UNDO_RETENTION = 3600;

 
Initilization parameters required for Automatic undo management:

UNDO_MANAGEMENT = <auto|manual>.

If UNDO_MANAGEMENT is specified as auto in initilizatin file, Oracle automatic undo management is enabled. if Manual is specified  for this parameter , then the traditional Rollback segment are used for undo management.  If Manual is specified for UNDO_MANAGEMENT ,below undo parameters will be ignored.

UNDO_TABLESPACE = <ts_name>

UNDO_TABLESPACE specifies the tablespace name which should be used as undo tablespace.

UNDO_RETENTION = <seconds|900>

UNDO RETENTION period is specfied in seconds.

UNDO_SUPPRESS_ERRORS = <false|true>

The defualt value for UNDO_SUPPRESS_ERRORS  is false. It Reports an error if  any manual management operation is done related to automatic undo segments. TRUE: Reports success for all such operations without actually carrying them out.

Switching of Undo tablespace:

An undo tablespace can be changed dynamically by using the following command:

alter system set undo_tablespace = <New Undo TS Name>;

This exam focuses on three primary topics: basic Oracle Net configuration (in other words, configuring Oracle for network access), the skills that you use to protect and recover an Oracle database from the inevitable problems of data loss, and the Export/Import and SQL*Loader utilities. Expect to answer approximately 63 multiple choice questions in 90 minutes. To pass the exam, you need to answer at least 41 questions correctly (65%). Specifically, the exam tests you about the following categories of information:

• the general need for Oracle Net, Oracle Corp.’s products that support applications in a networked environment
• the benefits of Oracle Net
• Oracle Net software architecture
• Oracle Net name resolution methods (host naming, local naming, etc.)
• shared server configuration and tuning
• Oracle Net connection models (shared server, dedicated server, bequeathed, etc.)
• Oracle Net disconnections
• Oracle Net configuration files (sqlnet.ora, tnsnames.ora, listener.ora, etc.)
• the TNS_ADMIN environment variable
• network design
• the Oracle Net Manager and Oracle Net Configuration Assistant
• the purpose and configuration of a listener process
• automatic service registration with the listener
• static service registration with the listener
• the Listener Control utility
• transparent connection load balancing and failover
• troubleshooting network connectivity problems
• the TNSPING utility
• failures that can damage an Oracle database system such as SQL statement failure, foreground and background process failure, system and instance crashes, disk (media) failures, and user errors
• the roll forward (redo) and roll back (undo) recovery stages of crash, instance, and media recovery
• the function of the database’s online log members, archived log files, and undo (rollback) segments in database recovery
• configuration of the database’s online log groups and members, log archiving, checkpoints, and the control file
• user-managed physical database backup and recovery using SQL commands and operating system commands
• database backup and recovery using the Recovery Manager (RMAN) utility
• logical database backup and recovery using the Import and Export utilities
• detection and repair of data block corruption
• bulk data loads of external data using the SQL*Loader utility
• bulk data loads of internal data using direct-load INSERT statements

Be prepared to identify the correct usage the optional ESCAPE parameter when you want Oracle to interpret literally a wildcard character within the expression. In the expression, specify the escape character (for example, \) followed by the wildcard character that you want Oracle to interpret literally. Then specify the escape character in the ESCAPE parameter itself. For example, the following query returns the job IDs and job titles for jobs that have a job ID that begins with the characters “FI_”.

SELECT job_id, job_title
FROM jobs
WHERE job_id LIKE ‘FI\_%’ ESCAPE ‘\’;

The following example explains how to use the ROUND and TRUNC functions to round or truncate DATE values to the nearest day. Notice that the ROUND and TRUNC functions accept a format model element (for example, DAY) as their second argument; if you omit this argument, these functions round and truncate dates to the nearest day, respectively. Note that you can use the SQL functions ROUND and TRUNC with DATE values only; you cannot use these functions with TIMESTAMP, TSTZ, or TSLTZ values.

SELECT
ROUND(TO_DATE(’23-AUG-99:12:12′,’DD-MON-RR:HH24:MI’), ‘DAY’) AS result,
TRUNC(TO_DATE(’23-AUG-99:12:12′,’DD-MON-RR:HH24:MI’), ‘DAY’) AS result
FROM dual;

The only valid logical operators in an outer join condition are = and AND.

Continue Reading

In the architecture section of the exam, expect questions that test your knowledge of very specific details about the Oracle software architecture. Specifically, you should be intimately familiar with what functions each Oracle background process performs; make sure that you know details such as what SMON does, what events cause DBWn to write dirty buffers to data files, what events cause LGWR to write the log buffer to the current online log group, which background processes perform I/O, and so on.

Understand that when a user establishes a dedicated server connection, Oracle allocates all portions of the corresponding PGA outside the shared pool. However, when a user establishes a multithreaded server (MTS) connection,Oracle allocates memory for the session’s cursor state and sort area, collectively known as the User Global Area (UGA) from the shared pool. Therefore, when you configure an Oracle instance with MTS, it is important to consider how much memory MTS sessions can allocate from the shared pool.

Continue Reading

Locks are mechanisms that prevent transactions from accessing the same resource concurrently with an intent of modification to the data.

Objects Affected By Locks

• Tables and rows (data in tables).
• data dictionary rows , shared memory structures which are not designated as physical objects.

When do Locks occur:

Any transaction resulting in modifying the data in a table acquires a lock. These are termed as DML locks. Other types of locks also occur when a ddl statement is used.

Oracle Lock duration

All the locks acquired by Statements with in a transaction are held until completion of transaction.

How Oracle Locks are released

When a transaction explicitly commits with a commit statement or the transaction is rolledback with a rollback statement, all the locks held by the transaction are released.

Oracle also releases locks acquired after a savepoint when rolling back to the savepoint.

Oracle Lock Modes

Exclusive Lock

The lock is obtained to modify the data in a table.Any transaction acquiring an exclusive lock would be given permission to modify the data. The first transaction that acquires an exlusive lock is the only eligible transaction to modify the resource until the exclusive lock is released.

Continue Reading

Oracle database Certification is a recognition to you, to work as qualified professional recognized around the world to work on Oracle database at various levels.With Oracle certification you will get more Opportunities and higher Pay packages.

 Being an Oracle certified Professional you would be having the right skills, efficient hands-on work experience to work on Oracle datbase.

Oracle provides three certification tracks.  You can choose any track that best suits  your individual job profile and your interests.

Oracle certification tracks :

• Oracle database Administrator
• Web Application developer
• Oracle Application server administrator.

Continue Reading

Generally we deal with two types of indexes . One is B-Tree Indexes and the other is Bitmap indexes.B-tree indexes have been traditional favorites for Oltp databases.if we classify the tables based on the underlying data, there are two types 1. read write tables (Huge Inserts ,Update and Deletes) 2. Read only tables - Table which are almost having static data. Aggregated tables, Dimensional tables, Fact tables, Pre Aggregation temp tables are some of the examples of Read only tables.

Bitmap indexes are highly compressed and usually take very less space when compared to B-tree indexes and are extensively used in data warehouses.

When a bitmap index can be created on a table?

Bitmap indexes are the most suitable for columns having very few unique values (very low cardinality). Ideally to choose the bitmap indexes as the right choice for a column the cardinality should be less than or equal to 0.05%.

Table should have no insert, update , delete or very few dml statements can act as good choice for bitmap indexes.

create bitmap index Person_state on person (state);


The internal storage format of a bitmap index

Row Region North East West South
1 Ohio 1 0 0 0
2 Texas 0 1 0 0
3 New mexico 0 0 1 0
4 Pennsylvan 0 0 1 0
5 Atlanta 0 0 0 1
6 South caro 1 0 0 0


Advantage of Bitmap Indexes

Due to the nature of highly compressed structure of an bitmap index, Data can be accessed much faster than traditional B-tree indexes.

Bitmap index represent a good trade-off between disk usage and Cpu cost. A bitmap index requires more cpu cycles to decompress but requires less amount of work from disk I/O.

Disadvantages of Bitmap Indexes

Bitmap Indexes are traditionally meant to be developed for data warehouses. A modification to a bitmap index requires more work than a modification to b-tree indexes.

Deadlock’s On bitmap indexes

Bitmap indexes are not suitable for large concurrent single row Dml operations. Though bitmap indexes can be used in this form

insert into <bitmap_index_table> select * from <table> .

If there are 3 or more sessions simultaneously working to insert into a table which is bitmap indexed a dead lock situation can occur with an error message shown below

ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

And an other error you can face with bitmap index is

ORA-28611: bitmap index is corrupted - see trace file for diagnostics

Cause: Bitmap index might have corrupted due to validation

Action: Rebuild the Bitmap index. Like B-tree index you cannot build a bitmap index online. Try alter index <bitmap_index_demo> rebuild;

Oracle Partitioning is the splitting of data groups belonging to the same table  into separate physical areas using separate partition tablespaces. Partitions can actually coexist in the same physical data file, but the same effect of partitioning might not be achieved. Separate partitions can be accessed in Parallel or individually or in groups.

How can partitioning improve the performance of Sql queries ?
Partitioning can be used to break large tables into smaller subsets.Processing of small subsets individually or in parallel ways is much faster than executing serial procession on large data sets. Partitioning is the most useful in very large database applications.

Benefits of Oracle Partitioning

• Parallel processing is a way of splitting data into separately located physical files. Separating table and index datafiles onto separate disks in a nonpartitioned database is a form of simple partitioning. It is often highly beneficial to read index and data spaces from separate locations because table and index physical spaces are often read almost in parallel.Partitioning can enhance the benefits of splitting datafiles.
• Different partitions within the same table can have different physical storage structures. Different partitions within a table can even be
  both read-write and read only, perhaps allowing separation of archived and current data.
• Backup, recovery, and utility usage (SQL*Loader, Export, andImport) can utilize parallel processing and activities on individual partitions.

Continue Reading

Have you ever got stucked up in a long running job ? Do you want to know the behaviour of each sql you are writing ? how the execution path of a sql is determined ? Do you want to know the most time consuming sql statement in your long running procedure or job ? Do you want to know how much time is being spent for cpu cyles, disk acces ? where is the root problem of an query ? on What wait event oracle is waiting ?

For all your question on simple answer is Oracle 10046 Trace. Oracle trace Sql event traces the sql that a user session executes giving diagnostics information, which is useful for general troubleshooting and performance tuning. Oracle 10046 trace is a powerful platform to identify the most problematic sql and how it is behaving with in the oracle environment. With Oracle 10046 trace oracle is capable of providing detailed information that can be used to locate an error or to identify and resolve Performance problems. Oracle 10046 Trace itself consumes memory as well effects the performance of jobs you want to test it. Never run a oracle trace in a production system.To avoid this Oracle has introduced different trace levels based on the information needed.   The other trace levels available with oracle 10046 trace are level 1, level 4, level 8, level 12.A 10046 deep trace (level 12) shows exactly how the database is processing a query, the events the system is waiting on and how much time it spends waiting for a specific event.You can enable the tracing or disable it at different phases of a complex job and it is clearly possible to identify a performance issue.

Continue Reading