Oracle in World: Concepts

Showing posts with label Concepts. Show all posts

Tuesday, September 28, 2010

WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!

This article will explain some reasons for which you may encounter "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!" message in the alert log file. When Row cache contention occurs, if the enqueue cannot be obtained within a certain time period, a trace file will be generated in the trace location with some trace details.

The trace file tends to contain the following words:
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! <<

/opt/oracle/admin/EAIAPP/udump/eaiapp1_ora_23288.trc

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

ORACLE_HOME = /opt/oracle/product/dbs

System name: Linux

Node name: db1-eai.prod.stl.cw.intraisp.com

Release: 2.6.18-128.4.1.el5

Version: #1 SMP Thu Sep 23 19:59:19 EDT 2010

Machine: x86_64

Instance name: EAIAPP1

Redo thread mounted by this instance: 1

Oracle process number: 43

Unix process pid: 23288, image: oracle@db1-eai.prod.stl.cw.intraisp.com

*** 2010-09-25 17:08:05.532

*** ACTION NAME:() 2010-09-25 17:08:05.532

*** MODULE NAME:(OMS) 2010-09-25 17:08:05.532

*** SERVICE NAME:(EAIAPP) 2010-09-25 17:08:05.532

*** SESSION ID:(120.29614) 2010-09-25 17:08:05.532

>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! <<<
row cache enqueue: session: 70000001b542d78, mode: N, request: S
row cache parent object: address=700000036f27328 cid=0(dc_tablespaces)
hash=a6840ab5 typ=9 transaction=0 flags=00008000

The trace will often contain a systemstate dump, although most useful information is in the header section. Typically a session holding the row cache resource will either be on cpu or blocked by another session. If it is on cpu then errorstacks are likely to be required to diagnose, unless tuning can be done to reduce the enqueue hold time. Remember that on a RAC environment, the holder may be on another node and so multiple systemstates from each node will be required.

For each enqueue type, there are a limited number of operations that require each enqueue.

1) DC_TABLESPACES : This is probably the most likely is allocation of new extents. If extent sizes are set low then the application may constantly be requesting new extents and causing contention. Do you have objects with small extent sizes that are rapidly growing? (You may be able to spot these by looking for objects with large numbers of extents). Check the trace for insert/update activity, check the objects inserted into for number of extents.

2) DC_SEQUENCES : Check for appropriate caching of sequences for the application requirements.

3) DC_USERS : Deadlock and resulting "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!" can occur if a session issues a GRANT to a user, and that user is in the process of logging on to the database.

4) DC_OBJECTS : Look for any object compilation activity which might require an exclusive lock and thus block online activity.

5) DC_SEGMENTS : This is likely to be down to segment allocation. Identify what the session holding the enqueue is doing and use errorstacks to diagnose.

6) In many cases no operations are notified in the trace file. Only the session ID is specified. In that case we need to investigate that session id.

Monday, August 30, 2010

Oracle DBA Interview Questions - Part 1

Question 01: When Oracle database implemented PL/SQL Language?
Answer: The first version of the PL/SQL language is introduced in Oracle version 6. In oracle Oracle7, released in 1992, introduced PL/SQL stored procedures and triggers.

Question 02: In which version of Oracle RAC is implemented?
Answer: Oracle 9i database introduced Oracle RAC in year 2001. Later Oracle Database 10g introduced grid computing in 2003.

Question 03: What is Oracle database instance?
Answer: An Oracle instance is a set of memory structures that manage different database files. It consists of Shared/System Global Area (SGA) and a set of background processes.

Question 04: Name the Oracle schema object types.
Tables, Indexes, Partitions, Views, Sequences, Dimensions, Synonyms, PL/SQL subprograms and packages.

Question 05: What are the characteristics of Materialized Views?
i) Just like table materialized views contain data and consume storage space.
ii) They can be refreshed when the data in their master tables changes.
iii) They can improve performance of SQL execution when used for query rewrite operations.
iv) The existence of materialize view is transparent to SQL applications and users. So if optimizer sees use of materialize view is more efficient than accessing original table then for that query materialize view will be automatically used.

Question 06: Name the different types of Constraints
NOT NULL, Unique, Primary, Foreign, Check.

Question 07: What are the DDL statements?
DDL statements do structural changes or drop schema objects. For example: CREATE, ALTER, DROP, TRUNCATE, GRANT, REVOKE, AUDIT, NOAUDIT, COMMENT.

Question 08: What are the DML statements?
Data manipulation language (DML) statements query or manipulate data in existing schema objects. For example: SELECT, INSERT, UPDATE, MERGE, DELETE, EXPLAIN PLAN, LOCK TABLE.

Question 09: What are the TC statements?
Transaction control statements manage the changes made by DML statements and group DML statements into transactions. For example: COMMIT, ROLLBACK, ROLLBACK TO SAVEPOINT, SAVEPOINT, SET TRANSACTION.

Question 10: What are the Session Control statements?
Session Control Statements dynamically manage the properties of a user session. For example: ALTER SESSION, SET ROLE.

Question 11: What are the System Control statements?
System control statements change the properties of the database instance. The only system control statement is ALTER SYSTEM.

Question 12: What are the Embedded SQL statements?
Embedded SQL statements incorporate DDL, DML, and transaction control statements within a procedural language program.
For example:
- Define, allocate, and release cursors (DECLARE CURSOR, OPEN, CLOSE).
- Specify a database and connect to it (DECLARE DATABASE, CONNECT).
- Assign variable names (DECLARE STATEMENT).
- Initialize descriptors (DESCRIBE).
- Specify how error and warning conditions are handled (WHENEVER).
- Parse and run SQL statements (PREPARE, EXECUTE, EXECUTE IMMEDIATE).
- Retrieve data from the database (FETCH).

Tuesday, August 19, 2008

Possible Limitations When Adding Datafiles to a Tablespace

Before discussing limitations of adding datafiles to a tablespace let's think about DB_FILES parameter. DB_FILES is an initialization parameter in oracle. When an oracle instance starts, this parameter indicates the amount of SGA space to reserve for datafile information and thus, the maximum number of datafiles that can be created for the instance. This parameter exists throughout the life cycle of the instance. So, changes to DB_FILES affect only after restarting the instance.

As this parameter indicates the amount of SGA space to reserve for datafile information the bigger value settings indicates memory is unnecessarily consumed.

And smaller value setting of this parameter impose limit to create another datafile.

So setting of DB_FILES parameter value is important.

Now let's look about possible limitations when adding datafiles to a tablespace.

Possible Limitations When Adding Datafiles to a Tablespace

•OS imposes a limit of the number of files a process can be opened at a time.

•OS imposes a limit of number and size of datafiles.

•The database imposes a maximum limit on the number of datafiles for any Oracle Database opened by any instance. This limit is operating system specific.

•Number of datafiles can't exceed DB_FILES initialization parameter.

•It is good to know that after oracle 8i the MAXDATAFILES parameter (of CREATE CONTROLFILE or CREATE DATABASE command) is not responsible of the limitations of adding datafiles.

•If you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the MAXDATAFILES parameter of the control file will expand automatically to accommodate more files.

Related Documents
MAXDATAFILES , DB_FILES parameters and ORA-00059

How to Resize a Datafile

ORA-01667: cannot add any more tablespaces: limit of exceeded

New Feature of 10.2g: Eliminate Control File Re-Creation

When and How to Recreate the Controlfile

Wednesday, July 2, 2008

Schema Object Naming Rules

Saturday, June 28, 2008

Database Objects in Oracle

If we look for database objects in oracle then there comes two types of objects, one is schema objects that are associated with a particular schema or in other word they are owned by a database user. And the other is nonschema Objects that are not reside in a particular schema.

To know the object type in your database you can query,
SQL>select distinct object_type from dba_objects order by 1;

Schema Objects Lists
------------------------------------
APPLY
CAPTURE
CLUSTER
CONSTRAINT
CONSUMER GROUP
CONTEXT
DATABASE LINK
DIRECTORY
EVALUATION CONTEXT
FUNCTION
INDEX
INDEX PARTITION
INDEXTYPE
JAVA CLASS
JAVA DATA
JAVA RESOURCE
JOB
JOB CLASS
LIBRARY
LOB
LOB PARTITION
MATERIALIZED VIEW
OPERATOR
PACKAGE
PACKAGE BODY
PROCEDURE
PROGRAM
QUEUE
RESOURCE PLAN
RULE
RULE SET
SCHEDULE
SEQUENCE
SYNONYM
TABLE
TABLE PARTITION
TRIGGER
TYPE
TYPE BODY
UNDEFINED
VIEW
WINDOW
WINDOW GROUP
XML SCHEMA

Non-Schema Object Lists
--------------------------------------
Parameter file (PFILE) and server parameter files (SPFILEs)
Profile
Role
Rollback segment
Tablespace
User

Thursday, June 19, 2008

Advantages and Disadvantages of using smaller and bigger data blocks

Whether I will use bigger or smaller data blocks in my database it can be specified by parameter DB_BLOCK_SIZE or DB_nK_CACHE_SIZE. With the settings I can I can differentiate three types of data blocks in oracle.

1)Small Block(2KB-4KB)
2)Medium Block(8KB)
3)Large Block(16KB-32KB)

Advantages of Bigger Blocks
•Using bigger blocks means more data transfer per I/O call. So faster data transfer from disk to memory.

•Using bigger blocks means more space for key storage in the branch nodes of B*-tree indexes, which reduces index height, which improves the performance of indexed queries.

•When using large block there are less probability of chained and migrated rows, which in turn reduced the number of reads required to get the information.

Disadvantages of bigger Blocks
•If the rows are predominated random then you are increasing the possibility of contention in the buffer cache. Because now with same same amount of memory in buffer cache as it was in small blocks, we need more memory in the buffer cache to keep the same amount of buffers in memory in the buffer cache.

•If you have high transactional concurrency to a segment, using bigger blocks is just going to make the concurrency even higher.

Advantages and disadvantages of these blocks
1)Small Block(2KB-4KB): The advantage of small blocks are they reduce block contention and they are really good where there is small rows or the selectivity of rows are highly random.

The disadvantages of small blocks are they have relatively larger overhead.

2)Medium Block(8KB): The advantage of medium blocks are if the rows are of medium size then you can bring a number of rows in a single I/O.

The disadvantage of it is space in the buffer cache will be wasted if you are doing random access to small rows and have a large block size. For example, with an 8KB block size and 60 byte row size, you are wasting 8000-60=7940 bytes in the buffer cache when doing random access.

3)Large Block(16KB-32KB): If you use larger block then relatively less overhead. Per I/O you can fetch more data. This is very good for sequential access, or very large rows.

Large block size is not good for index blocks used in an OLTP(Online Transaction Processing) type environment, because they increase block contention on the index leaf blocks.

Related Documents:
General Idea of Database Block Size and BLOCKSIZE
Choose an optimal Data block size in Oracle

Saturday, June 14, 2008

Physical and Logical Oracle Database Architecture

Physical Database Structure
------------------------------------------------------
If you call about the physical oracle database structure then it includes the file of database that we can see from Operating Systems. It consists of Datafiles, Control Files, Redo Log Files, Archive Log Files, Parameter Files, Alert and Trace Log Files and Backup Files.

1)Datafiles
-----------------
•All the database data are stored in datafiles.
•A datafile can be associated with only one database.
•All logical objects such as tables, indexes, functions are stored in data files.
•You can see your datafiles location in oracle by querying,
SQL> select file_name from dba_data_files;

2)Control Files
----------------------------
•Every Oracle database has a control file.
•A control file contains entries that specify the physical structure of the database.
•If the physical makeup of the database is altered (for example, if a new datafile or redo log file is created), then the control file is automatically modified by Oracle to reflect the change.
•You can see your control files location in oracle by querying,
SQL> select name from v$controlfile;

Or from Sql*Plus,
SQL> show parameter control_files
To know more about database control file please visit Control file in Oracle

3)Redo Log files
--------------------------
•Every Oracle database has a set of two or more redo log files.
•The set of redo log files is collectively known as the redo log for the database.
•A redo log is made up of redo entries (also called redo records).
•The primary function of the redo log is to record all changes made to data.
•The information in a redo log file is used only to recover the database from a system or media failure that prevents database data from being written to the datafiles.
•You can see your online redo log file location by querying,
SQL> select member from v$logfile;
The process of applying the redo log during a recovery operation is called rolling forward.

To know more about redo log files please visit

4)Archive Log Files
-----------------------
•The online redo log files is archived to another place if database archivelog mode is on. It ensures that all changes of database are saved to another place.
•You can see the location of your archived redo log file by querying,
SQL> select name from v$archived_log;
To know about archival destination please visit Archive log Destination

5)Parameter Files
------------------------------
A parameter file is used to start up the instance. It can be either spfile or pfile.
You can see the location of your spfile by querying,
SQL> select value from v$parameter where name='spfile';
Or in Sql*plus,
SQL> show parameter spfile;
To know more about it please visit Pfile and Spfile in Oracle

6)Alert and Trace Log Files
--------------------------------------
Each server and background process can write to an associated trace file. When an internal error is detected by a process, it dumps information about the error to its trace file.
To know about alert log file please visit What is Alert Log

7)Backup Files
------------------------
Backup files are the backup of database or individual tablespace or datafiles. They can be generated using RMAN or user managed backup.

Logical Database Structure
-------------------------------------------
If you call about the logical oracle database structure then it includes the file of database that we can't see from Operating Systems. Within oracle database itself it is maintained. The logical storage structures, includes oracle data blocks, extents, segments and tablespaces.

To know about tablespace visit
To know about oracle data block visit Overview of oracle data block
To know about extent visit Overview of Extents and when extents are allocated and deallocated
To know about segment visit
The details are discussed in another topics in my blog. To know more about those search inside my blog.

Sunday, June 1, 2008

How to check which option is not installed to your database

Based on your database version you may be restricted to use several options of your database or you may wish not to install several database options. With this options feature I have a post in ORA-00439. Now in this post I have shown how you can check or see which options is installed or which option is installed on to your system.

SQL>select ' You have installed '||parameter||' option'
from v$option
where value = 'TRUE';

you can check or see which options is not installed or which option is installed on to your system by following query,

select ' The '||parameter||' option is not installed'
from v$option
where value <> 'TRUE';

The Real Application Clusters option is not installed
The Oracle Label Security option is not installed
The Data Mining Scoring Engine option is not installed

Related Documents:
------------------------
ORA-00439

Saturday, May 10, 2008

Types of Oracle Error Message.

A typical oracle error message follow the following form.

Prefix-ErrorNumber: Error Text
An example of error message is,
ORA-00942: table or view does not exist
Here ORA is prefix and error number is 00942 , The error text is "table or view does not exist".

The types of Error Message in oracle are,

ORA Oracle RDBMS errors
AMD Oracle OLAP Catalog Metadata Messages
AUD interMedia Audio Messages
DBV DBVERIFY Messages
DRG Oracle Text Messages
EPC Oracle Trace Collection Services Messages
EXP Export Messages
IMG interMedia Image Messages
IMP Import Messages
KUP External Tables Messages
LCD Parameter Messages
LFI BFILE-related messages
LPX XML Parser Messages
LRM Parameter Messages
LSX XML Schema Processor Messages
MOD SQL*Module Messages
NCR Remote Operation Messages
NID DBNEWID Messages
NMP Simple Network Management Protocol Messages
NNC Oracle Names Client Messages
NNF External Naming Messages
NNL Oracle Names Control Utility Messages
NNO Oracle Names Server Messages
NPL Oracle Names Server Network Presentation Layer Messages
NZE Network Security Messages
O2F Object Type Translator Type File Messages
O2I Object Type Translator Initialization Messages
O2U Object Type Translator Unparser Messages
PCB Pro*COBOL Messages
PCF PCF FIPS Messages
PLS PL/SQL and FIPS Messages
QSM Summary Advisor, Explain Rewrite, and Explain Materialized View Messages
RMAN Recovery Manager Messages
SQL SQL Runtime Messages
SQL*LOADER SQL*Loader Messages
TNS Oracle Net Messages
VID interMedia Video Messages

Related Documents
How to get Oracle Error Message from Database or OS

Sunday, April 20, 2008

Object Namespaces in Oracle

With an example I will start the topic,

SQL> create table name (a number);
Table created.

SQL> create index name on name(a);
Index created.

SQL> desc name;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER

SQL> create view name as select * from name;
create view name as select * from name
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

Here, we see that I can create an index with the same name as of table but I can't create a view with the same name as of a table.

Now it is necessary to know which type of objects can have the same name and which are not. For this it is necessary to introduce the concept of a namespace. A namespace defines a group of object types, within which all names must be uniquely identified—by schema and name. Objects in different namespaces can share the same name.

These object types all share the same namespace:
• Tables
• Views
• Sequences
• Private synonyms
• Stand-alone procedures
• Stand-alone stored functions
• Packages
• Materialized views
• User-defined types
Thus it is impossible to create a view with the same name as a table; at least, it
is impossible if they are in the same schema.

These object types each have their own namespace:
• Indexes
• Constraints
• Clusters
• Database triggers
• Private database links
• Dimensions
Thus it is possible for an index to have the same name as a table, even within the
same schema.

Non schema objects has their own namespace

• User roles
• Public synonyms
• Public database links
• Tablespaces
• Profiles
• Parameter files (PFILEs) and server parameter files (SPFILEs)

So two non schema objects can have same name. Like the word users can be both tablespace name and role name.

Tuesday, April 15, 2008

Shutdown Modes in Oracle

To shut down a database and instance, you must first connect as SYSOPER or SYSDBA. There are several modes for shutting down a database.

1)Shutdown NORMAL / Shutdown

-The NORMAL clause is optional.This is the default shutdown method if no clause is provided.
-No new connections are allowed after the statement is issued.
-Before the database is shut down, the database waits for all currently connected users to disconnect from the database.Until they press exit database waits.

2)SHUTDOWN IMMEDIATE

-No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.

-Any uncommitted transactions are rolled back.

-The database implicitly rolls back active transactions and disconnects all connected users.

3)SHUTDOWN TRANSACTIONAL

-No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.

-After all transactions have completed, any client still connected to the instance is disconnected.

4)SHUTDOWN ABORT

-No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.

-Current client SQL statements being processed by Oracle Database are immediately terminated.

-Uncommitted transactions are not rolled back.

-Oracle Database does not wait for users currently connected to the database to disconnect. The database implicitly disconnects all connected users.

-The next startup of the database will require instance recovery procedures.

5)Shutdown Transactional LOCAL

The LOCAL mode specifies a transactional shutdown on the local instance only, so that it only waits on local transactions to complete, not all transactions.In RAC environment it is useful.

Shutdown Timeout

When we issue SHUTDOWN NORMAL database waits for users to disconnect or SHUTDOWN TRANSACTIONAL for transactions to complete have a limit on the amount of time that they wait. If all events blocking the shutdown do not occur within one hour, the shutdown command cancels with the following message:
ORA-01013: user requested cancel of current operation.

Different ways of Starting Up a Database

Options for Starting Up a Database
-----------------------------------
1)Starting Up a Database Using SQL*Plus
-----------------------------------------------
$sqlplus /nolog
SQL>conn / as sysdba
SQL>startup

2)Starting Up a Database Using Recovery Manager
---------------------------------------------------
1)oracle@neptune:~$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Apr 15 18:08:04 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database (not started)

2)RMAN> startup

Oracle instance started
database mounted
database opened

3)Starting Up a Database Using Oracle Enterprise Manager
---------------------------------------------------------------
1)emctl start dbconsole
2)paste the url in to the browser.
3)Enter host credential and database credential as sysdba.
4)Press yes.

How to find current session ID

Way1:
------
select sid from v$session where audsid = sys_context('userenv','sessionid');
Way2:
------
select distinct sid from v$mystat;
Way3:
--------
If DBMS_SUPPORT is installed,
select dbms_support.mysid from dual;
Related Documents
Get IP Address from hostname within Oracle Database
How to find the User who is connected to Oracle
How to know which objects are being accessed by a user

Tuesday, April 8, 2008

Different Oracle Database Status and Active State.

Status:
1)Started: When you issue startup nomount then the status of database is nomount stage. In this case oracle database instance just read pfile/spfile parameter and allocate instance memory.

2)Mounted: When you issue startup mount then the status of database is mount stage.In this case oracle database starts an instance and mounts the database, but leaves the database closed. However you need to do in mount state to perform specific maintenance operations.(Such as archive mode and recovery)

3)Open: When you issue alter database open then the status of database is open stage.After executing this statement, any valid Oracle Database user with the CREATE SESSION system privilege can connect to the database.

4)Open Migrate: After ALTER DATABASE OPEN {UPGRADE | DOWNGRADE}

To see these status issue,
SQL>SELECT STATUS FROM V$INSTANCE;

Database Active State:
1)NORMAL: Indicates the database is in a normal state.

2)QUIESCING: Indicates that the ALTER SYSTEM QUIESCE RESTRICTED statement has been issued: no new user transactions, queries, or PL/SQL statements are processed in this instance. User transactions, queries, or PL/SQL statements issued before the ALTER SYSTEM QUIESCE RESTRICTED statement are unaffected. DBA transactions, queries, or PL/SQL statements are also unaffected.

3)QUIESCED: indicates that the ALTER SYSTEM QUIESCE RESTRICTED statement has been issued: no user transactions, queries, or PL/SQL statements are processed. DBA transactions, queries, or PL/SQL statements are unaffected. User transactions, queries, or PL/SQL statements issued after the ALTER SYSTEM QUIESCE RESTRICTED statement are not processed.

To see these status issue,
SQL>SELECT ACTIVE_STATE FROM V$INSTANCE;

Database Status:
1)SUSPENDED: The ALTER SYSTEM SUSPEND statement halts all input and output (I/O) to datafiles (file header and file data) and control files. The suspended state lets you back up a database without I/O interference. When the database is suspended all preexisting I/O operations are allowed to complete and any new database accesses are placed in a queued state.

2)ACTIVE: Use the ALTER SYSTEM RESUME statement to resume normal database operations.

All these can be seen by issuing the following commands,

SQL> select status, active_state, database_status from v$instance;

Database Open Mode:
1)Read Only: Opening a database in read-only mode enables you to query an open database while eliminating any potential for online data content changes.
The following statement opens a database in read-only mode:

ALTER DATABASE OPEN READ ONLY;

2)Read Write: Open a database in read/write mode as follows:
ALTER DATABASE OPEN READ WRITE;
However, read/write is the default mode.

This opening mode can be shown from,

SQL>select open_mode from v$database;

Wednesday, April 2, 2008

Oracle Background Process

To maximize performance and accommodate many users, a multiprocess Oracle system uses some additional Oracle processes called background processes.

There are many types of Oracle background processes. Each performs a specific job in helping
to manage the instance. Five Oracle background processes are required, and several background processes are optional.

Required Oracle Background Processes
--------------------------------
1)System Monitor (SMON): Performs instance recovery following an instance crash, coalesces free space in the database, and manages space used for sorting.

2)Process Monitor (PMON): Cleans up failed user database connections.

3)Database Writer (DBWn): Writes modified database blocks from the SGA’s Database Buffer Cache to the datafiles on disk

4)Log Writer (LGWR): Writes transaction recovery information from the SGA’s Redo Log Buffer to the online Redo Log files on disk.

5)Checkpoint (CKPT): Updates the database files following a Checkpoint Event.

Optional Database Background Processes

1)Recoverer Process (RECO)

2)Job Queue Processes

3)Archiver Processes (ARCn)

4)Queue Monitor Processes (QMNn)

5)Other Background Processes

Tuesday, April 1, 2008

Difference between TRUNCATE, DELETE and DROP commands?

The DELETE command is used to remove 'some or all rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.

SQL> SELECT COUNT(*) FROM emp;
COUNT(*)
----------
14

SQL> DELETE FROM emp WHERE job = 'CLERK';
4 rows deleted.

SQL> COMMIT;
Commit complete.

SQL> SELECT COUNT(*) FROM emp;
COUNT(*)
----------
10

TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUNCATE is faster and doesn't use as much undo space as a DELETE.

SQL> TRUNCATE TABLE emp;
Table truncated.

SQL> SELECT COUNT(*) FROM emp;

COUNT(*)
----------
0

The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.

SQL> DROP TABLE emp;
Table dropped.

SQL> SELECT * FROM emp;
SELECT * FROM emp
*
ERROR at line 1:
ORA-00942: table or view does not exist

DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.

From Oracle 10g a table can be "undropped". Example:

SQL> FLASHBACK TABLE emp TO BEFORE DROP;
Flashback complete.

PS: DELETE will not free up used space within a table. This means that repeated DELETE commands will severely fragment the table and queries will have to navigate this "free space" in order to retrieve rows.

Related Documents:
------------------------
Differeneces Between DDL, DML,DCL,TCL

What is Connection Pooling?

Connection pooling in the JDBC 2.0 extension API is a framework for caching database connections. This allows reuse of physical connections and reduced overhead for your application. Connection pooling functionality minimizes expensive operations in the creation and closing of sessions.

a pooled connection instance represents a single physical connection to a database, remaining open during use by a series of logical connection instances.

A logical connection instance is a simple connection instance (such as a standard Connection instance or an OracleConnection instance) returned by a pooled connection instance.

What is JDBC?

The Java Database Connectivity (JDBC) API is the industry standard for database-independent connectivity between the Java programming language and a wide range of databases – SQL databases and other tabular data sources, such as spreadsheets or flat files. The JDBC API provides a call-level API for SQL-based database access. There are four type of drivers..

JDBC OCI client-side driver: This is a JDBC Type 2 driver that uses Java native methods to call entrypoints in an underlying C library. That C library, called OCI (Oracle Call Interface), interacts with an Oracle database. The JDBC OCI driver requires an Oracle client installation of the same version as the driver.

The use of native methods makes the JDBC OCI driver platform specific. Oracle supports Solaris, Windows, and many other platforms. This means that the Oracle JDBC OCI driver is not appropriate for Java applets, because it depends on a C library.

JDBC Thin client-side driver: This is a JDBC Type 4 driver that uses Java to connect directly to Oracle. It implements Oracle's SQL*Net Net8 and TTC adapters using its own TCP/IP based Java socket implementation. The JDBC Thin driver does not require Oracle client software to be installed, but does require the server to be configured with a TCP/IP listener.

Because it is written entirely in Java, this driver is platform-independent. The JDBC Thin driver can be downloaded into any browser as part of a Java application. (Note that if running in a client browser, that browser must allow the applet to open a Java socket connection back to the server.)

JDBC Thin server-side driver: This is another JDBC Type 4 driver that uses Java to connect directly to Oracle. This driver is used internally within the Oracle database. This driver offers the same functionality as the client-side JDBC Thin driver (above), but runs inside an Oracle database and is used to access remote databases.

JDBC Server-Side Internal driver: This is another JDBC Type 2 driver that uses Java native methods to call entrypoints in an underlying C library. That C library is part of the Oracle server process and communicates directly with the internal SQL engine inside Oracle. The driver accesses the SQL engine by using internal function calls and thus avoiding any network traffic. This allows your Java code running in the server to access the underlying database in the fastest possible manner. It can only be used to access the same database.

JDBC-ODBC Bridge:The JDBC-ODBC Bridge is a JDBC driver that implements JDBC operations by translating them into ODBC operations. To ODBC it appears as a normal application program. The Bridge implements JDBC for any database for which an ODBC driver is available. The Bridge is implemented as the sun.jdbc.odbc Java package and contains a native library used to access ODBC. The Bridge is a joint development of Merant and Java Software.

Before a connection can be established, the bridge driver class, sun.jdbc.odbc.JdbcOdbcDriver, must either be added to the java.lang.System property named jdbc.drivers, or it must be explicitly loaded using the Java class loader. Explicit loading is done with the following line of code:

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Oracle: Undo vs Rollback Segment

There might be confusion while undo and rollback segment terms are used interchangeably in db world. It is due to the compatibility issue of oracle.

Undo
-------------------
Oracle Database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo.

Undo records are used to:

• Roll back transactions when a ROLLBACK statement is issued
• Recover the database
• Provide read consistency
• Analyze data as of an earlier point in time by using Flashback Query

When a ROLLBACK statement is issued, undo records are used to undo changes that were made to the database by the uncommitted transaction. During database recovery, undo records are used to undo any uncommitted changes applied from the redo log to the datafiles. Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it.

Undo vs Rollback
--------------------------------------
Earlier releases of Oracle Database used rollback segments to store undo. Oracle9i introduced automatic undo management, which simplifies undo space management by eliminating the complexities associated with rollback segment management. Oracle strongly recommends (Oracle 9i and on words) to use undo tablespace (automatic undo management) to manage undo rather than rollback segments.

To see the undo management mode and other undo related information of database-

SQL> show parameter undo

NAME TYPE VALUE
———————————— ———– ——————————
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1

Since the advent of Oracle9i, less time-consuming and suggested way is—using Automatic Undo Management—in which Oracle Database creates and manages rollback segments (now called “undo segments”) in a special-purpose undo tablespace. Unlike with rollback segments, we don’t need to create or manage individual undo segments—Oracle Database does that for you when you create the undo tablespace. All transactions in an instance share a single undo tablespace. Any executing transaction can consume free space in the undo tablespace, and when the transaction completes, its undo space is freed (depending on how it’s been sized and a few other factors, like undo retention). Thus, space for undo segments is dynamically allocated, consumed, freed, and reused—all under the control of Oracle Database, rather than manual management by someone.

Switching Rollback to Undo
-----------------------------------------
1. We have to create an Undo tablespace. Oracle provides a function that provides information on how to size new undo tablespace based on the configuration and usage of the rollback segments in the system.

DECLARE
utbsiz_in_MB NUMBER;
BEGIN
utbsiz_in_MB ;= DBMS_UNDO_ADV.RBU_MIGRATION;
end;
/

CREATE UNDO TABLESPACE UNDOTBS
DATAFILE ‘/oradata/dbf/undotbs_1.dbf’
SIZE 100M AUTOEXTEND ON NEXT 10M
MAXSIZE UNLIMITED RETENTION NOGUARANTEE;

Note: In undo tablespace creation, “SEGMENT SPACE MANAGEMENT AUTO” can not be set

2.Change system parameters
------------------------------------------
SQL> alter system set undo_retention=900 scope=both;
SQL> alter system set undo_tablespace=UNDOTBS scope=both;
SQL> alter system set undo_management=AUTO scope=spfile;
SQL> shutdown immediate
SQL> startup

UNDO_MANAGEMENT is a static parameter. So database needs to be restarted.