Discovering Oracle !

Sam-Alpati Notes

2006-07-13T01:22:00.000+05:30

LOGICAL DATABASE STRUCTURES

DATA BLOCK

A data block is at the foundation of the database storage hierarchy and is the basis of all database storage in an Oracle database. A data block consists of a several bytes of disk space in the Operating System’s Storage System. All Oracle space allocation and usage is in terms of Oracle Data Blocks. Oracle data blocks are generally of sizes 2KB, 4KB, 8KB, 16KB, and 32KB. Oracle data block should be multiple of disk block size, if not; you would be wasting time reading and writing whole disk blocks. Size of Oracle data blocks is controlled by setting DB_BLOCK_SIZE parameter in Oracle initialization file.

Oracle offers following guidelines for choosing the database block size:

1. Choose a smaller if your rows are small access is predominantly random.

2. Choose a larger block size if rows are small and access is mostly sequential or if you have large rows.

MULTIPLE ORACLE DATA BLOCK SIZES

Oracle provides you the feature of having additional block sizes all within a single database. If you wish to configure Multiple Oracle Data Blocks, then you must also configure corresponding sub caches in buffer cache of System Global Area.

Notes in Progress!

2006-07-13T01:19:00.000+05:30

SQL STATEMENTS

There are four types of SQL statements.

1. Data Manipulation Language. (DML)

2. Data Definition Language. (DDL)

3. Transaction Control. (TC)

4. Data Control Language. (DCL)

Detailed description of these commands is as follows:

TYPE	COMANDS
Data Manipulation Language (DML)	Insert Update Delete Merge
Data Definition Language. (DDL)	Create Alter Drop Rename Truncate
Transaction Control. (TC)	Commit Rollback Savepoint
Data Control Language (DCL)	Grant Revoke

CAPABILITIES OF SQL SELECT STATEMENTS

A select statement retrieves information from the database. Using a select statement you can do following things:

PROJECTION:

Using projection capability in SQL you can choose number of columns from your table. These columns can be one, few or many depending upon your situation.

SELECTION:

You can use the selection capability in SQL to choose the rows in a table that you want.

JOINING:

You can use join capability in SQL to bring together data that is stored in different tables by creating a link between them.

KEYWORD:

A keyword refers to an individual SQL element.

For example SELECT & FROM are keywords.

* is used to select every thing from a table.

SELECT STATEMENT

To select particular columns from a table separate the column names by a comma.

IMPORTANT POINTS ABOUT SQL STATEMENTS:

SQL statements are not case sensitive.
SQL statements can be on one or more lines.
KEYWORDS cannot be abbreviated or spilt across the lines.
Clauses are usually placed on separate lines.
Indents are used to enhance the readability.
Try to use KEYWORDS in upper case.

COLUMN HEADING DEFAULTS

iSQL*Plus:

- Default heading justification: Center.

- Default heading display: Uppercase

SQL*Plus:

- Character and Date column headings column headings are left justified

- Number column headings are right-justified

- Default heading display: Uppercase.

The various arithmetic operators available in SQL are:

OPERATOR	DESCRIPTION
+	Add
-	Subtract
*	Multiply
/	Divide

You can use Arithmetic Operators in any clause of SQL statements expect in FROM clause

OPERATOR PRECEDENCE

Multiplication and Division take priority over addition and subtraction.
Operators of same priority are evaluated from left to right.
Parenthesis is used to force prioritized evaluation and to clarify statements.

DEFINING A NULL VALUE

A null value is a value that is unavailable, unassigned, unknown or inapplicable. A null value is not same as zero or a blank space because zero is a number and a space is a character. If a column value in a arithmetic is null the result is also null. For example if we divide a number by a zero we get an error on the other hand if we divide a number by a null we get a null or an unknown value.

DEFINING A COLUMN ALIAS

A column alias renames a column heading. To use a column alias type the new name of column immediately after the old name separated by space or separated by the keyword “as”. If the new name of the column contains spaces, special characters or is case sensitive (by default the column headings are in UPPER-CASE) the write the new column name in double quotes.

CONCANTENATION OPERATOR

You can link columns to other columns using a concatenate operator. Columns on both sides of operator are combined to make a single output column.

LITERAL CHARACTER STRINGS

SQL> select ename ||' is a ' || job as "Employe detls" from emp;

Employe detls

-------------------------

SMITH is a CLERK

ALLEN is a SALESMAN

WARD is a SALESMAN

JONES is a MANAGER

MARTIN is a SALESMAN

BLAKE is a MANAGER

CLARK is a MANAGER

SCOTT is a ANALYST

KING is a PRESIDENT

TURNER is a SALESMAN

ADAMS is a CLERK

Employe detls

-------------------------

JAMES is a CLERK

FORD is a ANALYST

MILLER is a CLERK

14 rows selected.

Here ‘is a’ literal character.

DUPLICATE ROWS

By default select statement also shows duplicate entries of rows. To eliminate duplicate entries use DISTINCT keyword in the select clause. You can use distinct clause in front of multiple columns. In that case you will get distinct combination of multiple columns.

WHERE CLAUSE:

Character strings and dates in the where clause must be enclosed in single marks (‘ ‘). Number constants, however, should not be enclosed in single quotations marks. Entries in tables are case sensitive and hence use them appropriately with WHERE clause.

COMPARISON CONDITIONS

OPERATOR	MEANING
=	EQUAL TO
>	GREATER THAN
>=	GREATER THAN OR EQUAL TO
<	LESS THAN
<=	LESS THAN OR EQUAL TO
<>	NOT EQUAL TO

OTHER COMPARISON CONDITIONS

OPERATOR	MEANING
BETWEEN …..AND….	BETWEEN TWO VALUES (INCLUSIVE)
IN (SET)	MATCH ANY OF A LIST OF VALUES
LIKE	MATCH A CHARACTER PATTER
IS NULL	IS A NULL VALUE

BETWEEN……AND……

EG:

SQL> select ename, sal from emp

2 where sal between 1000 and 4000;

ENAME SAL

---------- ----------

ALLEN 1600

WARD 1250

JONES 2975

MARTIN 1250

BLAKE 2850

CLARK 2450

SCOTT 3000

TURNER 1500

ADAMS 1100

FORD 3000

MILLER 1300

11 rows selected.

We have to specify the lower limit first.

IN CONDITION

It is used for testing specified values from a set. It can be used for any data type. Dates or Characters have to be enclosed in single quotation marks.

LIKE CONDITION

Like operator is used when you don’t know the exact value to be searched for. You can use escape identifier to search for actual % and _ symbols. Use backslash in front of the symbol (% or _) to be searched for and then followed by ESCAPE ‘\’. This asks oracle server to treat \ as escape character.

%-Represents any sequence of zeros or more characters.

_- Represents exactly one character.

NULL CONDITION

The null condition includes IS NULL and IS NOT NULL. The null value means value is unavailable, unassigned, unknown or inapplicable. You cannot use ‘=’ because NULL value cannot be equal to or not equal to.

LOGICAL CONDITIONS:

Operator	Meaning
AND	Returns true if both conditions are true.
OR	Returns true if al east one condition is true.
NOT	Returns true if following condition is false.

RULES OF PRECEDENCE:

Order evaluated	Operator
1	Arithmetic Operators
2.	Concatenate Operator.
3.	Comparison Operator
4.	IS, [NOT] NULL, LIKE, [NOT] IN
5.	[NOT] BETWEEN
6.	NOT LOGICAL OPERATOR
7.	AND LOGICAL OPERATOR
8.	OR LOGICAL OPERATOR

ORDER BY CLAUSE

There are two order by clauses:

(i) ASC

(ii) DESC

EG OF ASCENDING ORDER:

SQL>select * from emp

order by empno asc;

By default order by clause returns values sorted in ascending order. Thus the query

SQL>select * from emp

order by emp;

is same as above query.

EG OF DESCENDING ORDER;

SQL> select * from emp

order by hiredate desc;

Important points for ORDER BY CLAUSE:

Default sort order is ascending.
For order by clause in ascending, numeric values are shown first, for example 1-400.
For order by clause in ascending, date values are displayed with earliest value first.
For order by clause in ascending, character values are displayed in alphabetical order.
Null values are displayed first for ascending sequences and last for ascending sequences.
SQL STATEMENTS
1. You can also sort the query results by more than one column

SQL STATEMENTS

FUNCTIONS:

There are two types of SQL functions:

Single row functions.
Multiple row functions.

Single Row Functions operate on single rows and return one value per row. Various types of Single Row Functions are as follows:

Character.
Number.
Date.
Conversion.
General.

Multiple row functions operate on multiple rows and returns one value per multiple rows.

CHARACTER FUNCIONS:

Accepts a character I/P and returns a character or number value.

NUMBER FUNCTIONS:

Accepts a number value and returns a number value.

DATE FUNCTIONS:

It operates on date data type values.

CONVERT FUNCTIONS:

It converts value of one data type to data of another data type.

GENERAL FUNCTIONS:

These include:

NVL
NVL2
NULLIF
COALSECE
CASE
DECODE

Character functions are of two types:

Case manipulation functions. ( LOWER, UPPER, INITCAP)
Character manipulation functions. (CONCAT, SUBSTR, LPAD, RPAD, LENGTH, INSTR, TRIM & REPLACE)

LOWER: Converts an alpha character value to lower case.

UPPER: Converts an alpha character to upper case.

INITCAP: Converts first character of the word to upper case and at the same time making other characters of the word to lower case.

CONCAT: Concatenates first character value to second character value, equivalent to concatenation operator.

LENGTH: Extracts a string of determined length.

INSTR: Determines the position of given character.

LPAD: Pads the value right justified.

RPAD: Pads the value left justified.

TRIM: Trims heading or trailing characters from a character string.

NUMBER FUNCTIONS:

ROUND: Rounds column, expression or value to n decimal places or if n is omitted no decimal places. If n is negative numbers to left of decimal are rounded.
TRUNC: Truncates column, expression or value to n decimal places or if n is omitted then by default to zero decimal places.
Returns the value of m divided by n.

DUAL TABLE:

Dual table is owned by sys user. It contains one dummy column and one row with value X

SAMPLE FORMAT ELEMENTS OF VALID DATE FORMATS

SCC or CC	Century or Server prefixes B.C. date with
Years in dates YYYY or SYYYY	Year or Server prefixes B.C. date with
YYY or YY or Y	Last “three”, “two” or “one” digit of a year.
Y,YYY	Year with comma in this postion.
IYYY, IYY, IY, I	Four, three, two or one digit year based on ISO Format
SYEAR OR YEAR	Server prefixes B.C. date with.
BC/AD	BC/AD Indicator
B.C./A.D.	B.C./A.D. Indicator with periods
Q	Quarter of year.
MM	Month: Two digit value.
MONTH	Name of Month.
MON	Name of month: Three letter Abbreviation.
RM	Roman Numeral Month.
WW, W	Month of year, Month of week respectively
DDD, DD, D	Day of year, month, week.
DAY	Name of day padded with blanks to a length of nine characters.
DY	Name of day: Three letter abbreviation
J	Julian Day; Number of days since 31^st December, 5713 B.C.

A foreign key is a column or set of columns which refer to primary key in the same table or another table. In other words it acts as link between two tables. In language of layman you can say that foreign key is that column which is common to multiple tables.

Guidelines for Primary key and Foreign key:

You cannot use duplicate values in primary key.

Data conversion is of two types:

Implicit data type conversion.
Explicit data type conversion.

IMPLICIT DATA TYPE CONVERSIONS

For assignments Oracle server can automatically convert a “varchar2” or “char” data-type in to “number” or “date” data-type. Similarly a “number” or “date” data-type can be converted to “varchar2” data-type.

For Expression evaluation Oracle server can automatically convert a “varchar2” or “char” data-type in to “number” or “date” data-type

Guidelines for Primary key and Foreign key:

You cannot use duplicate values in primary key.

Data conversion is of two types:

Implicit data type conversion.
Explicit data type conversion.

IMPLICIT DATA TYPE CONVERSIONS

For Expression evaluation Oracle server can automatically convert a “varchar2” or “char” data-type in to “number” or “date” data-type

30-01-2006 evening oracle

2006-02-20T14:50:00.000+05:30

Optimizer is a program which makes plan for execution

Each and every task is divided into fragments. This is called planning and it is done by optimizer.

Difference between rule based and cost based optimizer:

Rule based optimizer takes into consideration number of blocks it has to scan.

Oracle automatically adjusts the sizes of different pools of shared pool area i.e. data dictionary cache and cursor area.

Large Pool Area:

It is an optional area unlike other areas which are mandatory.

It is used for bulk handling of the data.

When large pool area is not there, Oracle uses data buffer pool. It will some how affect the normal working of Oracle. Hence large pool is often configured so that users are not affected.

Oracle's Sleepycat buy may be first of several

2006-02-15T18:16:00.000+05:30

The face of open-source may be morphing into that of proprietary software as Oracle agreed the purchase of embedded database developer Sleepycat Software and reportedly considers swooping to buy other leaders in the open sector, including middleware leader JBoss.

"Sleepycat's products enhance Oracle's market-leading database product family by offering enterprise-class support to customers who need to embed a fast, reliable database at a lower cost," said Andrew Mendelsohn, senior vice president of Oracle database server technologies in a statement.

Business Week Online has reported that Oracle is also in talks with JBoss and PHP language leader Zend, while the blog of venture capitalist Paul Kedrosky adds open solutions testing firm SpikeSource as another possible candidate.

Oracle is not the first closed software leader to buy up open-source firms. Novell has bought Linux distributor Suse and Ximian, for example. Last year, IBM bought application server developer Gluecode Software and BEA acquired tools maker M7, while Oracle itself bought database developer Innobase.

However, the jury is still out on the wisdom of such purchases. Novell’s Suse buy has not stopped Red Hat becoming the distance leader in Linux distribution revenues, for example, and some observers question whether open firms that sell out can maintain community support.

None of the parties reportedly involved would comment on the reports regarding Oracle and JBoss or Zend but indicators suggest the JBoss deal in particular could add up.

Oracle chief executive Larry Ellison said at an analyst conference earlier this month that Oracle wants to be the leader in middleware and said open-source technologies could be a positive for his company.

"Rather than fight this open-source trend we think it's important to figure out ways to make it work to our advantage," he said.

Speculation is also rife on the web with some watchers suggesting that the acquisition of Sleepycat and possible purchase of JBoss could weaken other programs that compete with, or work alongside, those firms’ programs. Open database developer MySQL last week announced a new round of financing that added $18.5m to its defences.

30-01-2006

2006-01-30T17:31:00.000+05:30

When the data is read from the hard it is called “Physical Read.”

When the data is read from the memory it is called “Logical Read.”

When the data is read from the Buffer Cache (Memory) + Roll back segment it is called “Consistent Read.”

In case of consistent read the o/p contains original data.

There are two important concepts in Oracle:

Data Consistency.
Data Integrity.

# Data Consistency: Data consistency is defined as the value of the data as it exits at the time of request.

EG: Suppose a request is made by a client c1 at 11:00 AM and in case it takes 5 min to reach to server (exaggerated). If at time say 11:00 AM the data which c1 demanded was changed by another client c2 at 11:02 AM then client c1 will still get the original data on account of Data Consistency provided server has kept the original data intact.

# Data Integrity: It implies to validity of data with respect to natural and business rules.

EG: The Loan given by Bank will never be more than the loan sanctioned by the bank.

Roll Back reading corresponds to Consistent reading.

*: 18000 brains have worked so far on Oracle in 30 years.

When ever a SQL or PL-SQL statement is fired by a user, it involves approximately following processes:

Statement  Parsing  Planning  Execution

# Parsing: Parsing is a process which resolve the references to object, data types, privileges, syntax etc.

In short in parsing checking of syntax and validity of different parameters is made. Checking is made by making Parsed Tree.

Optimizer makes execution plans. There are two types of optimizers:

Cost based.
Rule based.

Most times same set of commands are fired. For every statement fired 2/3^rd of energy is used for parsing and planning. Using shared SQL area this can be restricted to much less value.

Shared SQL comes to picture only when two statements are exactly similar i.e. even their cases match.

Using # value SQL differentiates between two statements before parsing. There are two types of parsing:

Soft Parsing
Hard Parsing

Soft Parsing: Oracle makes every fired statement pass through # function and produces a value called # value. This # value depends on the way statement is written and not the text of statement. If the # value belonging to two statements is same, then it bypasses the second statement i.e. second statement is not required to go through parsing and planning process.

Ready to use statement is stored in cursor area or library cache or cursor cache.

Statements stored in Cursor area are identified by their # value.

Using # value parsing and planning processes are avoided.

If two statements are exactly identical including their cases but just differ by some digit used in the form of bind variable then the second statement is not required to go through planning and parsing process.

# Optimizer uses set of 15 hard coded rules for determining the execution plan. Rule based optimizer was the default optimizer in olden version. Cost based optimizer is more intelligent optimizer which considers statistics before drawing the execution plan to make the decision. How ever cost based optimizer requires that statistics should be current otherwise the decision of cost based optimizer would be affected. Cost based optimizer is default optimizer for the current version. Rule based optimizer will be eliminated in future versions of Oracle.

Rule based optimizer goes by text. It is not intelligent. Where as cost based optimizer is intelligent. It just doesn’t look at text but it also takes into consideration the different ways data could be accessed. It also checks the no of ways paths can be optimized

30-01-2006

2006-01-30T12:14:00.000+05:30

When the data is read from the hard it is called “Physical Read.”

When the data is read from the memory it is called “Logical Read.”

When the data is read from the Buffer Cache (Memory) + Roll back segment it is called “Consistent Read.”

In case of consistent read the o/p contains original data.

There are two important concepts in Oracle:

Data Consistency.
Data Integrity.

# Data Consistency: Data consistency is defined as the value of the data as it exits at the time of request.

# Data Integrity: It implies to validity of data with respect to natural and business rules.

EG: The Loan given by Bank will never be more than the loan sanctioned by the bank.

Roll Back reading corresponds to Consistent reading.

*: 18000 brains have worked so far on Oracle in 30 years.

When ever a SQL or PL-SQL statement is fired by a user, it involves approximately following processes:

Statement à Parsing à Planning à Execution

# Parsing: Parsing is a process which resolve the references to object, data types, privileges, syntax etc.

In short in parsing checking of syntax and validity of different parameters is made. Checking is made by making Parsed Tree.

Optimizer makes execution plans. There are two types of optimizers:

Cost based.
Rule based.

Most times same set of commands are fires

27-01-2006

2006-01-27T11:54:00.000+05:30

Table space is divided into two categories:

Locally managed
Dictionary managed

Locally managed concept was introduced in Oracle 8i. At the time of introduction it was optional. Before Oracle 8i there was no concept of Locally managed. However over the years scenario has changed. From Oracle 9i onwards Locally managed has become default where as Dictionary managed has become optional.

What is Locally managed and what is Dictionary managed?

By default initial extents are 1. However roll back segment by default has minimum 2 extents.

Definition: When the segment is created i.e. allocation i.e. which system is allocated to which table space, if this information is managed by Data Dictionary then it is called Dictionary management.

Another way of maintaining this allocation information is that to specify it in header of datafile. This is called Locally Managed.

If your system table space is locally managed then all your table spaces in database has to be locally managed.

If your system table space is dictionary managed the n your table space has two options:

Either to go Dictionary managed way.
Or to go with Locally managed way.

It is possible to change table space from dictionary managed to locally managed and vice versa provided your table space management is Dictionary managed.

Both individual data files and table space can be:

Off line.
On line.
Read only.

Off line means you cannot access any data. It is equivalent that no table space is present

On line means we can read as well write.

Read Only means that we can only read but cannot write.

#: Schema is defined as set of objects owned by an user account

It is possible for an user schema to span over multiple table spaces.

I: What is the difference between user account and schema?

A: User account is an account in database with certain sets of privileges according to which he can perform certain operations.

Schema is the set of objects owned by the user.

EG: Creating an account in Bank is similar to creating or having an account in Oracle. On the other hand facilities which you get after creating an account in Bank like check book, ATM, different add ons, Loans etc are similar to the concept of objects in database.

*: Object is any thing created by user.

Any object in database is identified by schema name and object name.

If user u1 creates a table say T and if there is another user say u2 then even he can create a table of same name because Table created by user u1 is identified as u1.T where as table created by user u2 is identified as u2.T. However if either user u1 or u2 further tries to create a same table of same name then it won't be allowed to do so because then there will be a clash i.e one table of same name already exits.

#: There is no concept of transfer of ownership in Oracle.

Memory Architecture:

Two Important concepts in Memory architecture are:

SGA (Shared Global Area)
PGA (Privileged Global Area)

SGA: All the users working on Oracle share the same memory and hence it is called as SGA.

Different portions of memory architecture in Oracle are as follows:

Data buffer pool (buffer cache)
Redo log buffer
Large buffer
Fixed SGA
Data dictionary cache
Shared pool area
Library cache ( Shared SQL area or Cursor area)

Let us study each portion one by one:

Buffer Cache ( Data buffer pool): Buffer cache is used to store the data blocks brought from data files.

The buffer pool is divided internally to blocks i.e. it formats itself. The size of block of memory has to be similar to size of data block. Before Oracle 10g there used to be similar size of data blocks through the database, but from Oracle 10g onwards you can have data blocks of 5 standards sizes, thus Oracle memory can be formatted in 5 sizes.

Buffer cache can be divided further to following categories:

Dirty Buffer.
Free Buffer.
Pinned Buffer.

Dirty buffer: It is that portion of Buffer cache in memory structure of Oracle which indicates that it is ready to accept the data.

Free buffer: When data is inserted into Dirty buffer, then processing starts over the data. Thus blocks in which processing of data takes place is called called free buffer. The bottom line is that “ On insertion of data in dirty buffer it becomes free buffer as long as processing is taking place.”

Pinned buffer: After the data is stored and if in future we want to edit it then the block in buffer cache in which modification takes place is called as Pinned buffer.

Thus same block in Buffer cache can act as three different buffers.

Oracle uses LRU (Least Recently Used) algorithm for storing data in memory.

Explanation of LRU Algorithm: ( Hypothetical Concept )

Consider a pulley. Over which there is a rope. Imagine that data is placed on the rope. Rope is infinite and is following continuously. It is obvious that data is also falling, by falling it means that it is being written on hard disk. Thus as data gets older it shifts towards least recently used end and if a new data comes in memory or an older data on memory is again edited then it shifts towards Most Recently used end.

This is an hypothetical example. It doesn't exist in reality.

*: Oracle is designed keeping commit in mind and not rollback.

Introduction to Oracle Memory Structures

Oracle uses memory to store information such as the following:

Program code
Information about a connected session, even if it is not currently active
Information needed during program execution (for example, the current state of a query from which rows are being fetched)
Information that is shared and communicated among Oracle processes (for example, locking information)
Cached data that is also permanently stored on peripheral memory (for example, data blocks and redo log entries)

The basic memory structures associated with Oracle include:

System Global Area (SGA), which is shared by all server and background processes and holds the following:
- Database buffer cache
- Redo log buffer
- Shared pool
- Large pool (if configured)
Program Global Areas (PGA), which is private to each server and background process; there is one PGA for each process. The PGA holds the following:
- Stack areas
- Data areas

Figure 7-1 Oracle Memory Structures

System Global Area (SGA) Overview

A system global area (SGA) is a group of shared memory structures that contain data and control information for one Oracle database instance. If multiple users are concurrently connected to the same instance, then the data in the instance's SGA is shared among the users. Consequently, the SGA is sometimes called the shared global area.

An SGA and Oracle processes constitute an Oracle instance. Oracle automatically allocates memory for an SGA when you start an instance, and the operating system reclaims the memory when you shut down the instance. Each instance has its own SGA.

The SGA is read/write. All users connected to a multiple-process database instance can read information contained within the instance's SGA, and several processes write to the SGA during execution of Oracle.

The SGA contains the following data structures:

Database buffer cache
Redo log buffer
Shared pool
Java pool
Large pool (optional)
Data dictionary cache
Other miscellaneous information

Part of the SGA contains general information about the state of the database and the instance, which the background processes need to access; this is called the fixed SGA. No user data is stored here. The SGA also includes information communicated between processes, such as locking information.

Database Buffer Cache

The database buffer cache is the portion of the SGA that holds copies of data blocks read from datafiles. All user processes concurrently connected to the instance share access to the database buffer cache.

The database buffer cache and the shared SQL cache are logically segmented into multiple sets. This organization into multiple sets reduces contention on multiprocessor systems.

Organization of the Database Buffer Cache

The buffers in the cache are organized in two lists: the write list and the least recently used (LRU) list. The write list holds dirty buffers, which contain data that has been modified but has not yet been written to disk. The LRU list holds free buffers, pinned buffers, and dirty buffers that have not yet been moved to the write list. Free buffers do not contain any useful data and are available for use. Pinned buffers are currently being accessed.

When an Oracle process accesses a buffer, the process moves the buffer to the most recently used (MRU) end of the LRU list. As more buffers are continually moved to the MRU end of the LRU list, dirty buffers age toward the LRU end of the LRU list.

The first time an Oracle user process requires a particular piece of data, it searches for the data in the database buffer cache. If the process finds the data already in the cache (a cache hit), it can read the data directly from memory. If the process cannot find the data in the cache (a cache miss), it must copy the data block from a datafile on disk into a buffer in the cache before accessing the data. Accessing data through a cache hit is faster than data access through a cache miss.

Before reading a data block into the cache, the process must first find a free buffer. The process searches the LRU list, starting at the least recently used end of the list. The process searches either until it finds a free buffer or until it has searched the threshold limit of buffers.

If the user process finds a dirty buffer as it searches the LRU list, it moves that buffer to the write list and continues to search. When the process finds a free buffer, it reads the data block from disk into the buffer and moves the buffer to the MRU end of the LRU list.

If an Oracle user process searches the threshold limit of buffers without finding a free buffer, the process stops searching the LRU list and signals the DBW0 background process to write some of the dirty buffers to disk.

The LRU Algorithm and Full Table Scans

When the user process is performing a full table scan, it reads the blocks of the table into buffers and puts them on the LRU end (instead of the MRU end) of the LRU list. This is because a fully scanned table usually is needed only briefly, so the blocks should be moved out quickly to leave more frequently used blocks in the cache.

You can control this default behavior of blocks involved in table scans on a table-by-table basis. To specify that blocks of the table are to be placed at the MRU end of the list during a full table scan, use the CACHE clause when creating or altering a table or cluster. You can specify this behavior for small lookup tables or large static historical tables to avoid I/O on subsequent accesses of the table.

25-01-2006

2006-01-26T00:54:00.000+05:30

Non system table space is divided into following categories:

Table
Index
Tools

Roll back table space

If we want to make modification in the data, then data to be changed is brought from hard disk to RAM. Now you can change the data. Before u start changing data the original data is stored in roll back segment. As a result you can any time recover the original data. Original data can be recovered also by not saving the data.

Before Oracle 9i user was allowed to create roll back segment. From Oracle 9i onwards Oracle started the function Auto roll back and user interference in roll back segment was stopped. However there is a scope of user governing roll back segment if setup is “manual”

One of the parameter is undo management=auto

If this is set to auto, as show above then you have to create rollback segment space at the time of creation of database.

System table space, Non-system table space and Roll back system table space fall under permanent table space.

Actually Temporary table space is permanent. Then why is called as temporary? It is so called because of the objectives it contains are temporary. For E.g.: Temporary segment.

*: Temporary table space doesn’t allow you to create any permanent object in temporary space or in other words DBAS are not allowed to create temporary segments.

*: Temporary segments can be created in permanent table space (System, Non system and Roll back)

The bottom line is that temporary operations like sorting takes place in Temporary segment.

Table spaces are classified also according to extent management:

Dictionary management
Locally management

Logically Structure:

TableàSegments (table, index, cluster, rollback, temporary, partition, mview, snapshots) àExtentsàData blocks (contiguous)

*: Divide large group or table to fragments there by sorting is made easy.

Table space is made up of data files. Extents are written to data file. Extents belong to table, index, cluster, rollback etc. If table space gets full, you can create data files to increase your space.

24-01-2006

2006-01-26T00:50:00.000+05:30

Sorry every one...

Actually my copy is not with me...........

So I was not unable to publish the lessons..................

As soon as my copy get back to me......

The article will be published.............................

22-01-2006

2006-01-23T19:08:00.000+05:30

23-01-2006

Control file maintains stat and status of all the data files and redo log files.

When ever there are structural changes made to database then back of control file is provided by Oracle.

Oracle provides two types of back up for control file:

Binary Back up.
Trace back up.

Trace back up is used when we fail to take the back up of control file.

Trace back is sort of creating command for generating control file.

REDO Log File:

Information about every operation in Oracle i.e. complete history of changes made in Oracle is recorded in REDO log files. REDO log files are also called as journals of database.

Whenever user wants to change any data he uses “update” command. On receiving the request Oracle reads the data to be changed and stores the data in memory. In memory required changes are made and then information of every thing changed is stored in REDO log file. It is sort of Hot Back.

Oracle requires minimum of two REDO log files. As the database is created and users start using it, the REDO log file and another file called Log Switch file come into action.

Oracle recommends minimum two such files. Minimum size of these files is 50K.

Oracle can operate in two modes:

Archival
No Archival

When Oracle is working in archival mode it makes the copy of REDO log files internally and gives it a Log Sequence Number.

REDO log files which are being worked on are called ONLINE REDO LOG FILES where as archived log files are OFFLINE REDO LOG FILES.

Archived files are stored on hard disk.

There are three types of databases basically:

Test database
Production database
Development database

All production databases should take place in Archival mode. Remaining databases i.e. Test and Development databases should work in Non-Archival mode.

REDO group consists of mirror image of ONLINE REDO FILES. It is not possible to store mirror images of ONLINE REDO LOG FILES on remote server.

Check point is very important concept of Oracle. To keep the frequency check point tuned or rather synchronized, the file size of ONLINE REDO LOG FILES should be same though not necessary.

Oracle has outstanding feature of allowing back up archived files on remote servers. That was the reason why NASDAQ was in a position to recover the data despite of World Trade Centre attack.

2006-01-21T22:46:00.000+05:30

21-01-2006

Control file is one of the most important file of Oracle database.

If control file is lost then entire database is lost. Thus back up of this file is very important and hence Oracle allows you to replicate this file i.e. make its mirror images. Oracle recommends you to store these mirror images on different hard-disks.

This file maintains synchronization information about about all the database files. The synchronization is very important from point of view of recovery of database. At the time of start up, Oracle checks the integrity of all the files from the control file.

The synchronization information is maintained in terms of SCN (System Change Number), CPC (Check Pointer Counter) and LSN (Log sequence Number). Control file contains the copies of headers of all data files and redo log files. Oracle is constantly maintains the headers of database files and redo log files and their copies in control file synchronously. If there is any mismatch between them, Oracle knows that recovery is needed to be applied in some form or the other. Apart from synchronization information control file also maintains some of the important database parameters exclusive to the control file like maxdatafiles, maxlog files, maxlog member, maxlog history, characteristic etc. It is possibe to modify the parameters expect the character set parameter. However for modifying the parameter control file is needed to be recreated with the new parameters.

*: Characteristic is similar to block size.

System Change Number is a no given to every committed transaction.

Check Point Counter indicates the number of times a file is changed.

Control file automatically get created at the time of creation of database. It also contains the name of database. Control file is fixed size file and it is in binary format. This file should never be edited or opened. After the version of Oracle 8i onwards control file also maintains the record of back and recovery taken by RMAN (Recovery Manager).

20-01-2006

2006-01-20T20:42:00.000+05:30

20-01-2006

Apart from Oracle many RDBMS are present, Cybase, DB2 from IBM and SQL 7 from Microsoft are other RDBMSs.

Oracle is available in all platforms where as other databases have limitation for their support for other Operating Systems.

Over the years Cybase has come in race. DB2 is considered one of the toughest competitor of Oracle.

Oracle structure is similar to rather structure of other databases is similar to Oracle structure.

Oracle Architecture:

Any system has three main parts:

Storage structure.
Memory structure.
I/O, process structure.

Data is stored in hard-disk. It is transferred to memory via I/O process.

Storage structure can be seen from 2 prospects:

Logical
Physical

Any database is not permitted to control the hardware in fact it’s the Operating System that covers the Database Storage Management.

Logical: It is the way Oracle looks at the data stored.
Physical: It is the way OS looks at the data stored.

There is logical and physical execution of command, restoration, storage etc.

Logical	Physical
Table space	Parameter file
Schema	Control files
	Data files
	Redo log files

Parameter file contains the parameters under which Oracle functions.

There are about 250-300 parameters in Oracle. But under normal conditions parameter file contains at the most 50-60 parameters. They are sufficient for normal functioning of Oracle. For different and extreme environments you need to tweak the parameters.

Parameter file is a text file. In UNIX we use VI editor to edit this file and in Microsoft we use notepad to edit this file.

Parameter file comes as a sample file from Oracle Corporation. You can create your own text file. The generic name is init.ora.

An Oracle DBA is supposed to use this file according to the environment he needs. Its syntax is initSID.ora. You can use your own name in place of SID. Say you want to use abc then name of your parameter file will be initabc.ora. Or say if you want to use xyz then name of your parameter file will be initxyz.ora so and so forth. The maximum length of name which you can use in place for SID can be of at the most 8 characters. But Oracle recommends the length to be of 4 characters.

Up to Oracle 8i, initSID.ora file was read when Oracle was booted or started to set the environment conditions as per need. It is the first file which Oracle reads while loading or booting. From Oracle 9i the concept of parameters changed completely.

Suppose you change a parameter of initSID.ora dynamically. This change would remain till server shuts down. Once the server is rebooted the dynamically made changes would no longer exist. This was the scenario before Oracle 9i. But from Oracle 9i onwards, a thought was made to store or preserve the dynamically made changes in a separate file called Spfile. Spfile stands for Sever Parameter file.

*: There is no concept of extentions in Oracle.

Spfile is binary file of initSID.ora file. Along with the contents of initSID.ora file Spfile also contains the dynamically made changes.

Limitation of Spfile:

Suppose you change a parameter of Spfile from x=20 to say x=50. These changes would be stored in Spfile. Now if you want to change the second parameter you can’t do so because you cannot change the Spfile i.e. changes are need to be made in initSID.ora file which are then incorporated in Spfile. Thus you need to first convert already existing Spfile into initSID.ora file and then make desired changes in it. The newly formed Spfile would then contain both the changed parameters.

Important parameters are:

1. Name of Database.

2. Global Name of Database.

3. Names of control files.

4. db block size. Block size is edited only once, but once it is set you cannot change there after because it can lead to serious loss of data.

5. Auditing of database.

6. Modes of database: (MTS/DDS)

7. Archival destination.

8. User dump.

9. Background Dump.

SGA parameters are important parameters.

19-01-2006

2006-01-19T18:26:00.000+05:30

Dependencies of three types
Partial Dependencies.
Transitive.
Functional

Functional Dependency is set to exist when Non Primary key is dependent on Primary key and also on the whole of primary key.

For successful operation of RDBMS, normalized tables are required.

Normalization is a process of identifying primary key and ensuring that all the non key attributes (all the columns in a table not a part of primary key) have full functional dependencies on the primary key and whole of the primary key.

RDBMS can handle one to many relationships.

Normalization can also be defined as resolving many to many relationships to one to many relationships.

Degree of normalized data is measured in normal form.

Oracle claims that it has implemented 11.5 rules of E.F. Codd. But few critics say that it is a false claim because according to them Oracle has implemented only 8 rules. What ever may be the controversy but fact remains that Oracle has implemented maximum rules proposed by E.F. Codd.

Primary and Foreign Keys values are Functional values.

Non Program Access or Non Procedural Access (E. F. Codd)
In this method of accessing data user may not be required to know the programming to access the data. It doesn’t mean that no procedure is followed to access the data. Of course some procedure is followed but user doesn’t need to worry about it. Following the procedure is part and parcel of Database. In brief it is Database’s responsibility and not user’s. All user needs to do is to select the data he needs.

For Non Procedural access we need to know different Languages. E.F. Codd proposed the language SQL. SQL stands for Structured Query Language.

Different statements are as follows:
DML à Data Manipulation Language.
EG: Insert, Update, Delete,

DLL à Create, Drop

DCL à Data Control Language

TC à Transaction.
EG: Rollback

Administration.
EG: Startup, Shutdown and Archive.

Oracle also provides procedural method called PL/SQL. PL/SQL stands for Programmable Language Structured Query Language.

Next feature of RDBMS is Relational Algebra.

Set of attributes is called Table. Tables are defined only by columns. Rows came after columns. You don’t need columns to define Table.

Set algebra related to relational sets is called Relational Algebra.

Selection
Projection
Intersection
Mining
Union
Product
Join

Selection: Selection allows you to provide certain Laws. Selection is a process of filtering.

Projection: If I want to select a particular attribute or I want to select whole set of attributes or columns then the procedure of selection is called Projection.

Intersection: If there are two sets A and B and I want to select the elements common to set A and set B, then the procedure of selection of such elements is called Intersection.
Take for instance A= {1, 3, 5, 6, 9} and B= {3, 4, 5, 8}
Then A intersection B = {3, 5}

Union: Again considering above example if I want to select the elements such that the new set includes all the elements of A and B but excluding the repeated elements then such a method of selection of elements is called Union. Therefore A union B = {1, 3, 4, 5, 6, 8, 9}

We can also take union in such a manner that A union B includes the elements which are repeated as well.

Product: A = {1, 3, 5} and B = {2, 4, 6}
Then A product B = {
(1, 2)
(1, 4)
(1, 6)
(3, 2)
(3, 4)
(3, 6)
(5, 2)
(5, 4)
(5, 6)
}

Scientific name of tables is called Relational.

Data Dictionary:
Tables maintained by the system or information of system stored in the form of tables is called Data Dictionary. Data Dictionary is also called Meta Data indicating that data of data. It is stored in RDBMS.

Concept of Null:
If certain value is not known or not applicable then it is called Null. Null is not equal to other null. Because either both are unknown or not applicable. Arithmetic of null is also null.
EG: null + 3 = null

Null Value Locator (NVL):
It is denoted by (x, 0) for addition and (x, 1) for multiplication. If we know the value of x then of course we will use it but if we don’t know the value of x then instead of making it null we can assign a value 0 for addition or 1 in case of multiplication.

18-01-2006

2006-01-19T18:17:00.000+05:30

Oracle is RDBMS. RDBMS stands for Relational Data Base Management System. It was proposed by Dr. Edgar F. Codd. This database system was not widely accepted because majority of population at that time consisted of people belonging to commerce and accounts. Its obvious that all applications and softwares were also made for their use. How ever RDBMS required usage of set theory which is branch of Mathematics and Arithmetics. This was not easily digestible to them. Hence it faced lot of opposition.

Update Anomaly. (If we have to update particular entity and in case that entity is present at no of places then there are chances that we may forget to change that entity at some place. )

Deletion Anomaly. ( If we delete some data the other data should remain intact. Take for example that if particular student has completed his course and hence leaving the institute then his account needs to be get deleted. But in that case other entities like Subject detail, Fees detail, Duration and phone no etc will also get deleted.)

Insertion Anomaly. (Using this method of storing data we can store data for only the items which we have presently in our hand. Suppose a new course is started then it is not possible to make its entry.)

Transitive Dependencies:
When non primary key attributes depends on other non primary key attributes which in turn depends on some primary key attributes then it is called Transitive Dependencies.

Concept of Relational:
The meaning of the term relational is when the data is stored in the tables are related to each other through a common column as well as in each table all the attributes are related to each other by way the common functional dependencies on the primary key. The relation between the two tables typically represent the natural relation between the two entities. In the real world, entities are never isolated but they are always related to each other.

RDBMS and its MAKER

2006-01-18T19:14:00.000+05:30

A relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by Edgar F. Codd.

Edgar F. "Ted" Codd (August 23, 1923 – April 18, 2003) was a British computer scientist who made seminal contributions to the theory of relational databases. While working for IBM, he created the relational model for database management. He made other valuable contributions to Informatics, but the relational model, a very influential general theory of data management, remains his most memorable achievement.
[edit]

Biography
Edgar Frank Codd was born at Portland, Dorset, in England. He studied mathematics and chemistry at Exeter College, Oxford, before serving as a pilot in the Royal Air Force during the Second World War. In 1948 he moved to New York to work for IBM as a mathematical programmer. In 1953, angered by Senator Joseph McCarthy, Codd moved to Ottawa, Canada. A decade later he returned to the USA and received his doctorate in computer science from the University of Michigan in Ann Arbor. Two years later he moved to San Jose, California to work at IBM's Almaden Research Center.
In the 1960s and 1970s he worked out his theories of data arrangement, issuing his paper "A Relational Model of Data for Large Shared Data Banks" in 1970, after an internal IBM paper one year earlier. To his disappointment, IBM proved slow to exploit his suggestions until commercial rivals started implementing them.
Initially, IBM refused to implement the relational model in order to preserve revenue from IMS/DB. Codd then showed IBM customers the potential of the implementation of its model, and they in turn pressured IBM. Then IBM included in its Future System project a System R subproject — but put in charge of it developers who where not thoroughly familiar with Codd ideas, and isolated the team from Codd. As a result, they did not use Codd's own Alpha language but created a non-relational one, SEQUEL. Even so, SEQUEL was so superior to pre-relational systems that it was copied, based on pre-launch papers presented at conferences, by Larry Ellison in his Oracle DBMS, which actually reached market before SQL/DS — due to the then-already proprietary status of the original moniker, SEQUEL had been renamed SQL.
Codd continued to develop and extend his relational model, sometimes in collaboration with Chris Date. One of the normalized forms in Database normalization — the Boyce-Codd Normal Form, is named after Codd. As the relational model started to become fashionable in the early 1980s, Codd fought a sometimes bitter campaign to prevent the term being misused by database vendors who had merely added a relational veneer to older technology. As part of this campaign, he published his 12 rules to define what constituted a relational database. His campaign extended to the SQL language, which he regarded as an incorrect implementation of the theory. This made his position in IBM increasingly difficult, so he left to form his own consulting company with Chris Date and others.
Edgar Codd also coined the term OLAP and wrote the twelve laws of online analytical processing. Codd also contributed knowledge in the area of cellular automata.
Codd received a Turing Award in 1981.
Edgar F. Codd died of heart failure at his home in Williams Island, Florida at the age of 79 on Friday April 18th, 2003.

History of the term
Codd introduced the term in his seminal paper "A Relational Model of Data for Large Shared Data Banks". In this paper and later papers he defined what he meant by relational. One well-known definition of what constitutes a relational database system is Codd's 12 rules. However, many of the early implementations of the relational model did not conform to all of Codd's rules, so the term gradually came to describe a broader class of database systems. At a minimum, these systems:
presented the data to the user as relations (a presentation in tabular form, i.e. as a collection of tables with each table consisting of a set of rows and columns, can satisfy this property)
provided relational operators to manipulate the data in tabular form
The first systems that were relatively faithful implementations of the relational model were from the IBM UK Scientific Centre at Peterlee; IS1 (1970-1972) and its followon PRTV (1973-1979). The first system sold as an RDBMS was Multics Relational Data Store, first sold in 1978. Others have been Berkeley Ingres QUEL and IBM BS12.

Current usage
There is some disagreement about what a "relational" DBMS is.
The most popular definition of a RDBMS is relatively imprecise; some argue that merely presenting a view of data as a collection of rows and columns is sufficient to qualify as a RDBMS. Typically, products that qualify as a RDBMS under this interpretation implement some of Codd's 12 rules, but most popular database systems do not support them all.
A second school of thought argues that if a database does not implement all of Codd's rules, it is not relational. This view, shared by many theorists and other strict adherents to Codd's principles, would disqualify many database systems as not "truly relational". In fact, any database that uses the SQL (Structured Query Language) to access and modify data is not an RDBMS under this definition. Advocates of this philosophy refer to systems that follow some but not all of the rules as Pseudo-Relational Database Management Systems (PRDBMS). For clarification, they often refer to RDBMSs that do follow all of the rules as Truly-Relational Database Management Systems (TRDBMS).
Today, almost all RDBMSs employ SQL as their query language but alternatives have been proposed and implemented. Alphora's Dataphor is a commercially available RDBMS that follows all of Codd's rules - both groups recognising it as a RDBMS.

Dynamic variations
Another common complaint about existing RDBMS is that they are too "static". Speculation is starting to build about the next generation of "dynamic relational" database systems in which columns can be dynamically "created", dynamically sized, and dynamically typed (or type-free, also known as "context typing" in some circles). Each row could be implemented as a map (dictionary or associative array) and missing columns for any given row are simply represented as blank or empty. Some say this violates the true relational model, but others counter that a map is an only an implementation detail. In this viewpoint, a "missing column" is simply a matter of interpretation and presentation choice. Such a system could be used where rapid prototyping or competitive agility is necessary for the particular domain (industry). It may also simplify database usage with dynamic programming languages such as Perl, Ruby, PHP, etc. Extensions may allow formal schemas to be added gradually such that some columns can be designated "required" and/or given specific types or formatting rules

Technical Facts About Oracle

2006-01-17T17:51:00.000+05:30

Technical Facts About Oracle
Published at Talus Software
http://www.talussoftware.com/
September 18, 2003

1
The Oracle database does not store numbers in IEEE format. IEEE is the lingua franca of CPUs. Therefore, even before you start laying out your tables' definitions, Oracle will make your performance suffer. Oracle must translate needless billions of numbers between it and your apps . . . and there is no work-around for this. The speed that you should have is not possible with Oracle.
2
Oracle page chains when a column's size increases. That means that a single row splits into uncontiguous fragments each time a column increases its size. Change a null to a string and the row has a new page chain. Change a column from "hello" to "hello world" and the row page chains again. Null, varchar and all number columns are subject to page chaining. Yes . . . even number columns--because Oracle stores all numbers as variable length columns (verifiable with vsize()). The larger the number, the more bytes required. Update a column with a smaller number--you waste space. Update a column with a larger number--you now have a page chain. Page chaining is a tremendous performance hit. If you have a 40,000 row table and one of its columns is null, then you update the columns to "hello world", your table now has 40,000 page chains. Oracle DBAs suggest that you make such columns char. You may add terabytes of blanks to your table, but you won't page chain.

As opposed to Oracle, Sybase never page chains (except for image or text data types). A single row is always contiguous. And all numbers are fixed length. Therefore, Sybase does not have to search and put together all the pieces of a single row. They are already together.
3
Oracle 8i does not support bit operators. Oracle 9i introduces the BITAND operator, but only the BITAND operator. Since Oracle does not store IEEE integer formats, every bit operation consumes tens of thousands of clock cycles instead of a single cycle. Yet another performance hit. If your application performs frequent bit comparisons and needs the results fast, it is imperative that you go with Sybase or Microsoft SQL Server. Sybase and Microsoft store standard IEEE datatypes. and hence use the standard bit operators like "update table set bitmask = bitmask 66536" Under the covers, Sybase and Microsoft are doing a simple single CPU instruction.I have written a set of PL/SQL bit functions as a tourniquet for Oracle. They are in DBPowerSuite under the ./dbaccess/scripts/oracle directory. However, because they are functions, the Oracle optimizer will be inefficient with them . . . but you have no choice with Oracle.
4
Oracle has at least three different languages: Server SQL, SQL*Plus and PL/SQL. The three languages imply functionality and "the most controllable database in the world." But that is a Oracle propaganda. The three languages stem only from the fact that the first language Oracle had invented failed to do what it must; therefore, Oracle developed a second. Since the second language was no good either, Oracle invented a third. Since Oracle could never get it right, they settled for a fusion of the three using bubblegum as the conduit. The bubblegum consists of "BIND", Oracle's weird "version" of dynamic SQL., and the DBMS_OUTPUT package. But even the conduit has major flawsSeveral Oracle fans e-mailed me saying "There is only one SQL language in Oracle. They are all the same." Another person, on the other hand, admitted the disparity and defended it. He said, "SQL*Plus and PL/SQL are a wonderful example of the dichotomy between I/O and the procedural . . . unlike the Sybase potpourri." To him I say, "The mouth and brain is a wonderful example of the dichotomy between I/O and the procedural. But unlike Oracle, the mouth and brain have seamless connections, do not require the user to build nerves and synapses, and do not require an elbow to make them work. "

The next several points illustrate this.

5
PL/SQL blocks within a SQL*Plus script ignore SQL*Plus's 'set autocommit on'. Committing transactions within PL/SQL is independent from the SQL*Plus in which the PL?SQL is embedded. The commit syntax is also different.
6
You cannot write any DDL with PL/SQL. 'create table' is invalid with PL/SQL. To the Sybase user, that is like saying that you cannot create a table within a stored procedure. If you want to create tables in an Oracle stored procedure, you have to use dynamic SQL. The table definition may be static, but you have to use dynamic SQL because PL/SQL cannot create tables to begin with. This is an up-front misuse of dynamic. Oracle's dynamic SQL has a different and obtuse syntax. You will have to learn it. Also, any future DML statements in your PL/SQL stored procedure that manage that table will also have to be in Oracle dynamic syntax. After all, you cannot write insert statements into a table which Oracle doesn't think exists.
7
You must have special permission in Oracle to create a table in a PL/SQL dynamic statement. You must have CREATE ANY TABLE permission. You may already have the ability to "CREATE TABLE X", but if you use dynamic SQL to do the exact same thing, you don't have the permission.
8
There is no if statement in SQL*Plus. "if ..., then ...." is impossible with Oracle. It is a standard in Oracle for a programmer to issue SQL*Plus DDL commands knowing that they will fail. You just let SQL*Plus generate an error. That is normal for Oracle. The problem is that "error" means error. One's attention is drawn to it. Each one requires examination. DBAs hate this stuff. Customers hate this stuff. Tech support hates this stuff. The typical tech support's response is "Yeah, that error is okay but that is normal for Oracle."To attempt to handle the error, you can introduce the WHENEVER SQLERROR clause in SQL*Plus, but that is only a Band-Aid. That does not prevent the error to begin with. You can also spool out a SQL command generated from a prior select from USER_TABLES, and then execute this spooled file later. The latter method is the best choice to prevent errors. But the method is just another kludge. It introduces a timing problem for the transaction, and you will also find yourself executing empty commands . . . but that is normal for Oracle.
9
Even though you can do a "select col1, col2 . . . from table" in Server SQL, you cannot do a standard select statement in PL/SQL. This tells you that Oracle does not support result sets. Oracle users will rebuke me, "Oracle does support result sets!" But when you read the Oracle manual, Oracle's definition of a result set is not the same. Oracle purposely introduced the term result set only so that Oracle fans can say, "We support result sets." It was a marketing ploy only. A result set implies nothing in Oracle. Oracle has no concept of parameter result sets, row result sets, cursor result sets, compute result sets, etc., nor the concept of result sets arriving in a stream to the client.
10
You cannot print anything to your screen from within a PL/SQL block in real time. If you want to print out the current iteration of a cursor, just forget it. If you want to print the rows of a cursor as they are being selected, just forget it. Oracle is incapable of doing it. Oracle buffers all the output from a PL/SQL block for printing until after the PL/SQL block completes. Even at that, Oracle limits you to 1 MB of buffer after which SQL*Plus blows up. So, when you have a long running loop, there is no way to tell your user about the progress of your loop. To the user, it looks like your script doesn't work. But that is normal for Oracle. Oracle programmers will spend a day of programming just to simulate a simple Sybase print command.
11
In Oracle, you can only execute one SQL command at a time. Oracle has no concept of a batch. This is true in SQL*Plus as well as through ODBC. So, I assume this is a basic Oracle design limitation. In Sybase, you can send 100s of commands in a batch-- an operation which takes only a single network I/O. In Oracle, you must execute 100s of commands to accomplish the same thing. In Sybase, you load up 100 commands in your car and go to the shop and have them all serviced at one time. In Oracle, you must load you car 100 times, go to the shop 100 times, and have each command serviced one at a time.
12
Oracle does not support client-side timeouts. In Sybase, you can program your client to timeout if the server doesn't respond to a login request or to a query in a settable time. This mechanism allows your client to recover gracefully when the server or network has a problem. Oracle offers no such mechanism. In Oracle, you have to kill -9 your client. If you are a capable programmer and have an extra three man-months to spare, you can program threads into your own client which handle timeouts in lieu of Oracle's inability to do so for itself.
13
When creating a stored procedure using SQL*Plus, you may have errors in your procedure. The best error message Oracle can give you is "Procedure created with warnings or errors." One would think Oracle would be more specific. You actually have to go out of your way to request more information. You must additionally type SHOW ERRORS. Note that the error message implies that Oracle will create a procedure with errors in it.
14
Ever try to change your line width in SQL*Plus on Solaris? Do it and then select from the dictionary. Watch SQL*Plus core dump. SQL*Plus has been core dumping for years.
15
Oracle is inconsistent with the quoted identifier feature. Your company may literally waste man months of work over each instance of this problem. As you know, in Oracle you can use a reserved word for a table name if you put quotes around it. The problem is, is that Oracle will let you get by with a reserved word without quotes in one instance but blow up months later when you try to use it another. For example: create table CLUSTERS. This command works even though CLUSTERS is a reserved word. You can also select * from CLUSTERS. But try: . . .where CLUSTID not in (select CLUSTID from CLUSTERS) and watch Oracle blow up. While I can create a CLUSTERS table and select from it, I cannot refer to CLUSTERS in a subquery. The grammar rules of Oracle SQL change within Oracle SQL. A true linguistic nightmare.
16
The Oracle data dictionary stores all its object names in upper case. This is a throwback from the 7-bit ASCII days of the 1960s. Oracle allows you to access the object in upper case, lower case and mixed case. That is fine. But that concept doesn't work for object names where you have used the quoted identifier. Given create table TEST, you can select * from test, but you cannot select * from "test". Oracle regards this as a feature. Any object-name within quotes will be copied exactly to the data dictionary; yet that is not so for normal unquoted objects. This is another major inconsistency. Because of the reserved word problem, it is tempting to just put quotes around everything. If you do that, however, then all the object names in your SQL scripts have to match exactly those in the data dictionarySybase is refreshing after this particular Oracle nightmare. Sybase is simply consistent. What you create is what you get.
17
A blank line within a SQL command in SQL*Plus is a syntax error. Actually, it is a feature. A blank line tells SQL*Plus to erase your last command. Any leftover SQL clause following your blank line therefore blows up. To the Oracle user this is normal; to the Sybase user this is stupid. Sybase ignores blank lines and reset is the command to erase your last command. The problem is this. You have to make sure all your SQL scripts do not have mid-command blank lines, because SQL*Plus will treat each blank line as an error. Some third party code generation programs are lax with Oracle's wonderful feature, and so those code generation programs will not work with Oracle. In Sybase this problem never happens because Sybase has architecture.You can change this SQL*Plus default behavior by set sqlblanklines on.
18
The problem of not being able to drop the childmost table without the "cascade constraints" clause has been fixed in 9.2.
19
Oracle is incapable of truncating an parent table. A parent table is a table has incoming referential integrity constraints. It doesn't matter if the table is already empty. It doesn't matter if all its child tables are empty. You have to disable its RI constraints. The extra coding to disable and then reenable RI constraints before and after truncation, is substantial and a waste of time. The operation is logically unnecessary.
20
The system manager (Sybase equivalent of "sa") is not allowed to grant permissions on a user's objects to other users. In other words, the system manager does not have the permissions to manage the system. In Oracle, only the user himself can grant others permissions to his objects. Oracle calls this a feature.
21
You cannot create a read-only view in your own schema which reads from tables in another user's schema. You may have select permissions on the other user's tables, but that doesn't matter to Oracle.
22
Avoid creating views based on large tables. Avoid creating views based on views. Oracle's optimizer is extremely lousy at finding the fastest way to the data. Whereas Sybase can return results of a third generation view based on a 20 million row table in 45 minutes, Oracle can never figure it out, even after days of processing, for a table that is 1/20th the size. This Oracle behavior will force you to create work tables, which is what you tried to avoid in the first place.
23
Oracle has screwed up the definition of the null set. NULL has a specific meaning in mathematics, yet Oracle gets it wrong. In math, the null set is the set containing nothing--the empty set. In Oracle, the null set also includes the set containing 0-length strings. The ramifications are severe: 1) An application can no longer discern if a varchar2 column has been touched or not. (An empty string usually means that a user entered data even though the data has no length. A NULL means that the user never touched the column in the first place) and 2) You can longer use a varchar2 as one of the columns in a primary key if it is possible that the varchar2 value is "". The latter limitation will force the DB designer to use a heap table instead of the more desirable index-organized table. That will increase the size of the table significantly and thus will slow down access to it. All this slowness and excess bulk because Oracle has adopted a math convention akin to 0 + 1 = 0.
24
Oracle is single-threaded. Look at your process list (Solaris). Every connection to Oracle has its own process. The listener is its own process. The writer has its own process. The monitor has its own process. Each connection has its own process. In Sybase, all connections and listeners are threads inside the dataserver.
25
Oracle has actually planned a core dump directory for itself. That should tell you something. Note that is it full all the time.
26
Oracle only supports one database per server. Sybase supports 32,767 databases per server.
27
Oracle uses the temporary tablespace to build the indexes of create index commands. If you are creating a large index, your temporary tablespace better be huge too. Hint: make sure your init.ora variable SORT_AREA_SIZE is about 20 MB and that you have turned off logging in the temporary tablespace. If you don't do these two things, Oracle may never complete your create index command.
28
Oracle will use rollback segments to create the indexes. The problem is, is that whether the index gets created or not, there is no need to log any of the rows of an index to the rollback segment or to the redo logs. Either you can create the index or not. So there is no reason to log individual rows of the index. Nonetheless, Oracle will consume gobs of time and resources to log them.
29
Oracle will use the redo logs to create an index.
30
Oracle will step over its own shared memory bounds set in the init.ora file when creating a large enough index. Once it steps over its bounds, no one can log in anymore because Oracle not only had overstepped its bounds, but also it had leaked all the shared memory.
31
To the person who knows only Oracle, core dump directories, index logging, single-threaded and one-database servers are the trappings of home. To the Sybase user, they are museum relics from Jurassic Park. Sybase does not log the rows of an index. Sybase does not use the temporary database to form permanent indexes. Sybase does not have a rollback segment to also log temporary transactions, as if you would want to do that in the first place. And so, the Sybase user is surprised when it takes Oracle 16 hours to build a unique index on a large table whereas to took 40 minutes to build the same index on the same table in Sybase. I became aware that all this was happening when I tried to build an index and saw Oracle run out of temporary tablespace, run out of rollback segment space (2.5 GB), while busily writing to the redo logs. There is a way to ease the pain of this logging. Shut off logging in the temporary tablespaces and in the rollback segments tablespaces.
32
Oracle will log every row in the index to its rollback segments. Oracle uses rollback segments for everything without exception, and even for objects in the temporary tablespace. An index will be logged twice--one for the rows being generated in the permanent tablespace and again for rows being temporary written to in the temporary tablespace.
33
SQL*Plus does not inform you if you have run out of rollback segment while creating an index. It just hangs forever. You have to suspect something is up. You have to continuously view the alert log. tail -f alert.log . . . is normal for Oracle.
34
Point 34 used to slam Oracle's inability to create indexes based on dictionary order. However, in the light of Oracle's function-based indexes, this is not a concern. For review, Sybase users can set up their servers to process search clauses, order by, and case senstivity by setting up server-wide collating sequences. While Oracle doesn't handle that type of in-built automation, Oracle does support function-based indexes. Though function-based indexes require more syntax and are probably never needed aside from collating sequences, you could say that Oracle beats Sybase in flexibility for this one aspect.
35
The network configuration assistant program (netca) on Solaris will erase your entire tnsnames.ora and listener.ora files when you tell it to "Cancel and discard your changes." To the Sybase user, that is like quitting sybsetup or asecfg and having it erase the entire interfaces file.
36
The create database command does not load all the necessary SQL scripts to make the database a database. In Sybase, the equivalent is issuing a create database command and finding that the created database does not have system tables.
37
In Oracle, a database is a server. Oracle only supports one database per server. For each database you want in Oracle, you must run the equivalent of asecfg; that is, dbassist. So, a Sybase DBA who is used to managing 5000 databases on a single server is going to have to run 5000 iterations of dbassist to create his 5000 databases.Now dbassist is a trip in itself. Note that when dbassist generates a script to create the database, the script does not do any error checking. You can literally get thousands of errors and Oracle will say, "Database successfully installed." Remember that DDL errors are normal for Oracle. If you program Oracle, you do not even try to make your program error free because true DDL error handling is not part of Oracle's "architecture." Was the database installed correctly? Of course not.
38
Note that dbassist is incompatible with itself . After you ask dbassist to clean up after an unsuccessful generated script run, dbassist not only removes the database, but also removes the directory structure that your script initially requires. The script you just generated after spending an hour answering dbassist questions, no longer works. You have no choice but to answer all the questions again, even if you answer them the same way.
39
You cannot tell Oracle to use a specific rollback segment for an import. If you are importing a large table with a large index, you must first take all the small rollback segments offline so that Oracle must choose the large rollback segment when doing the import. Remember, Oracle even uses rollback segments to create an index. While you can bust up the import of the table data into smaller transactions with a COMMIT=Y, you cannot bust up the index creation into separate smaller transactions. (This index transaction limitation does not exist in Sybase since Sybase doesn't log the individual rows created during a create index.)
40
You cannot create an index organized table from a heap table or vice-versa. In Sybase lingo, you cannot create or drop a clustered index on an existing table. This has awful and severe consequences. When you need to import a lot of data into your clustered index table, it is always much faster (up to 50x faster) to import the data without the clustered index being present, even if it means the table being recopied. In Oracle, you are stuck. You cannot change an index organized table to a heap table for loading. Therefore, the load will take forever. The work-around is to create a non-clustered index instead. That will effectively double your table's lookup time. In addition, if your index contains most of the columns in your table, then you have doubled the size of your table as well.
41
Here's a major Oracle kludge: The LDAP server. The LDAP server is nothing more than a super thick carpet in order to cover up the severe problem that all users in an Oracle database cannot readily share the tables of another user. There is no concept or equivalent of 'dbo' in Oracle. In Sybase, every user in a Sybase database sees the database's dbo's schema. The dbo's schema is common to all users in a table, and is an addition to the user's own schema. As a poor attempt to provide dbo-like functionality, Oracle introduces the Band-Aid concept of synonyms. If you get tired of creating 10,000 synonyms for the 10,000 objects in your database, Oracle introduces an additional Band-Aid called the LDAP server. The LDAP server is a nightmare in itself.
42
Oracle allows you to create a user name with a "." (dot) in it. Dots are the ANSI standard SQL character to separate the parts of a fully qualified object name. For example, "user.mytable". So, Oracle's username's embedded dots conflict with the ANSI standard. If SQL*Plus runs into any dotted user names, it core dumps. FYI, Sybase does not let you "sp_addlogin" a username with a dot in it.
43
Which brings up another limitation of Oracle. In Sybase, a fully qualified object name is: server.database.owner.object. In Oracle, a fully qualified object name is: owner.object. The conclusion is as bad as it is obvious. Oracle has no concept objects belonging to different databases or servers in their basic architecture. The Oracle fan will say "But you can query tables from remote servers in Oracle!" And they are right. You do it with synonyms. One synonym for every object. If you want to access 2000 tables in a remote server, you have to create and maintain 2000 synonyms. Oracle introduced the concept of synonyms to get around their architecture.
44
I mentioned this one in passing before: You cannot do "select col1, col2 . . . coln from mytable" in a stored procedure. In other words, Oracle stored procedures cannot return a result set. The Sybase user is instantly horrified and rightly so. Returning result sets from procs is so useful, common and easy that it is taken for granted in Sybase. But in Oracle, the way to simulate a row result set is fodder for PhD dissertations. The problem is formidable. Hordes of white papers have been sacrificed on this unholy alter. For giggles, go to google.com. Search on ODBC, Oracle and Stored Procedures. Look at all the material on the subjectThe solution is this: You pass a reference cursor as a parameter to the procedure and then use a client-side fetch on the cursor. Your stored procedure also must be bundled in an Oracle package so that you can type define your cursor. The ODBC developer realizes, "Hey! There is no ODBC parameter type SQL_REFERENCE_CURSOR. I can't pass a parameter that's a reference cursor. " The response is, "Your ODBC driver vendor must provide the reference cursor mechanism inside the driver itself . . . and hide it from the ODBC layer." The company Merant does such a thing. Hats off to them. Their ODBC drivers provide this mechanism under the covers. You still have to go through the considerable pain of setting up these special stored procedures,. But Merant has given one the avenue to simulate result sets with Oracle. In DBPowerSuite, I have included a couple of PL/SQL script examples of how to simulate row result sets in an Oracle proc given that the user is using Merant ODBC drivers.You don't even think about result sets in Sybase. Result sets are a natural feature of the territory. Just like a sunrise, one takes it for granted in Sybase. However, if you use Oracle, the sun does not rise. You have to invent the sun and a rotating earth.Program managers beware. Your database developers and DBAs will spend three times as long accomplishing a task in Oracle than they will in Sybase. That is normal for Oracle. Your developers must invent suns and rotate earths on a daily basis in Oracle. You will not find Oracle lacking functionality, but you will find its functionality convoluted, illogical, counter-intuitive, missing the mark and buried. Oracle's functionality comes this way because Oracle's architectural foundation is not adequate.
45
Oracle is in conflict over the meaning of ';' -- the semicolon. In SQL*Plus, the semicolon executes your command but in PL/SQL means the end of a SQL statement without executing it. To execute a PL/SQL inside SQL*Plus, you type '/' instead. This is a fundamental overlap conflict between execution commands and language syntax. It is a basic architectural flaw which one faces every day and hour when using Oracle. It drives everyone crazy, even Oracle aficionados. The Sybase user, who has grown accustomed to using good software, vomits at this behavior of Oracle. The Oracle user, who has grown accustomed to flaws and quirks, treats this as normal. In Oracle, one gets used to such bad things and gets desensitized after a while.
46
The import utility captures ctrl-C. Say you have 3000 tables in your dump. You get 10 tables into it and for whatever reason, you want to quit. You must type ctrl-C 2990 more times before import will exit. Consider kill -9.
47
Oracle's UPDATE and DELETE statements have no FROM clause. Therefore, you cannot join tables to restrict the set of rows you are updating. Instead of being consistent with the SELECT statement like Sybase, Oracle forces you to be inconsistent. It's work-around time. You now have to figure out a way to simulate a join. You have to think subqueries. Oracle forces you to write a non-intuitive subquery for each column you are setting. If you are setting 3 columns, you have to write 3 subqueries--even if your subqueries are all the same. That's 3 times the processing. The resulting syntax is so convoluted, you will have to refer to examples each time you try to do it. This single Oracle problem will cost your company thousands of dollars both in writing the original statements, and then maintaining them.
48
Oracle lets you create a temporary table, but then forbids you to use a storage clause. There is no reason for this. It is an annoyance which requires expensive work-arounds. For an app I wrote, I needed to create and populate a large temporary table. I declared a big minextentsso that the app would not abort hours later in case of insufficient space. I made the table temporary so that the table would automatically go away if the database connection abnormally went away. But Oracle forbids me to declare minextents for a temporary table. In order to get around this problem, I had to create permanent tables, and write a stored procedure that drops such permanent tables on demand. Another employee of the company had to write a GUI interface to stored procedure so that the customer could maintain his own database.

History Time Line Of Oracle!

2006-01-16T15:20:00.000+05:30

2004
First to provide a single customer view from multiple data sources with Oracle Customer DataHub

2003
First to introduce Enterprise Grid Computing with Oracle 10g

First database that is available in production for 64-bit Linux with IPF.

2002
First database to pass 15 industry standard security evaluations and first Real Application Clustering

First native XML support in a relational database

First fully integrated relational and multidimensional database

First database with table compression on Unix, NT and Linux

First complete and integrated business intelligence platform

2001
First database to complete the 3 terabyte TPC-H world record

First database to pass over 9 industry standard security evaluations

First database with built-in Web services

First database with integrated data mining

First database with support for real world packaged applications on a cluster

First database with Hash, Range, Composite and List partitioning

First database with dynamic memory management

First database with built-in workflow

First database with complete online maintenance operations

First to support the JOLAP API

2000
First to offer complete and simple software for information management including the Oracle9i Database, Oracle9i Application Server and Oracle9i Developer Suite

Provide a single data model and single global instance

Industry's first Developer Service Provider (DSP)

Offers a complete applications suite

First to offer developers online services for application development

First to release a production framework for defining Web services

Launches first online developers skills exchange, Oracle Technology Network (OTN) Xchange

Introduces fully integrated application server for internet computing-Oracle Internet Application Server (Oracle9iAS)

Introduces first internet developer suite

First to offer an internet file system built for the internet, Oracle Internet File System

Launches Oracle Mobile, wireless applications service provider

Delivers the industry's only complete e-business suite, Oracle E-Business Suite 11i

Launches E-Business Network

1999
First to integrate Java and XML into an application development tool

Launches first industry exchange, AutoXchange, with Ford Motor Company

Launches complete e-business initiative

Provides applications hosting (Oracle Outsourcing)

Delivers first comprehensive, internet-based CRM suite

Overtakes SAP in US manufacturing

Offers the only internet platform for mobile, embedded, and information appliance computing

First relational database management system (RDBMS) ported to Linux

First database with XML support

First to offer a full-featured internet database

First to introduce capabilities to use XML and SQL in Java servlets

First to introduce the concept of portlets as a way to integrate applications into an e-business portal

My first day in Class!!!! (16-1-2006)

2006-01-16T14:20:00.000+05:30

Today was my first day in class. I woke up bit late as usual. The class was at sharp 7:00 AM. But I reached at 7:10 AM. Sir was taking introduction from all students. As I entered Sir asked me to introduce my self to class. Then Sir gave introduction about himself and Oracle. He started from scratch and first thing he taught was "what is data?" Initially I was bit nervous, but as the time passed I calmed down. Sir involves you so much that you never come to how time has passed. At the end it was a nice day.

Now lets come to the topic of Oracle. I will share with you what I learned today.

As I told you first thing that Sir taught us was, What is Data?
He explained it beautifully. The final conclusion was that Data is defined as Value of Property of Entity.

Then Sir taught what is Database?
In short I can just tell you that it is organisation of data.

There exited a data base called Network or hierarchical Data base. Y2K problem was because of this database only.
Explanation: Suppose you store some data. Now because you have stored the data it is quite obvious that you will need this data some time or the other. This means that method to access the data must be convinient. Suppose you want to store information about a person showing his Name, Age, Sex, Quallification, Job, Income etc... And you store it in following fashion. Suppose that you define that charachers from 1 to 20 are for Name, charachters 21 to 25 are for age, charachters 25 to 30 are for sex, charachers 30 to 50 are for Qualification so on and so forth. It just means that you have assigned particular space for particular entity and you will store that entity there only. Problem comes when you come accross such a entity that it needs more space than the space assigned by you. Take for example it can be name of any person or may be his qualification. Or it may so happen that particular person changes his name or adds more feathers to his cap there by my making his qualification more healthy. In that case if you will change one entity then other entities will also change leading to lot of chaos. Hence this database failed.

What is Database Management?
Set of protocols ( rules ) that your database follows is called database managment.

This is what I learned today.

Hey I just came to know today that All students who have attendence less than 75% won't be allowed to sit for NU exam and also will have to pay the fine. Its all bullshit. God is really testing me from all angles. But I am prepared for it.

I am a Newbie in Oracle, please help and guide me in learning and understanding Oracle!!!

2006-01-15T23:38:00.000+05:30

Hi everyone pollengrain is back again with a new dream and name of my new dream is oracle.
I am new in Oracle. Infact just started learning Oracle from 16th January 2006. What ever I will learn in Oracle, I will share my experience by posting corresponding articles on my blog page. You come here and read these articles, learn something from it, even share ur database with me and if I am wrong some where please help me in rectifying my mistakes.....................

Thanks,

Parag Kalra (pollengrain)