ADBMS TYMCA Ravi

ADBMS the creator later. A simple guideline is to add suffixes. Here

1. Explain heterogeneous and homogeneous are two examples:
database.  Suffix all the master tables using _MASTER, e.g.,
2. Explain data replication in detail. State CUSTOMER_MASTER, ACCOUNTS_MASTER and
advantages and disadvantages. LOANS_MASTER).
3. What do you mean by data fragmentation?  Suffix all transactional tables using the suffix _TRANS,
Explain its types with suitable example. e.g., DAILY_TRANS, LOANS_TRANS and
4. What is data transparency? Explain any three INTERBANK_TRANS.
data transparency methods with example.
5. Explain single-lock manager concurrency control 10. Explain the concept of inheritance in class with
approach and the need for distributed lock suitable example.
manager approach. 11. Create the following complex objects
6. Explain the concept of primary copy. Explain the a. Create Nested table patient on the table doctor
majority protocol in detail b. Explain varray creation and insertion operation
7. What is semi-join? What is the need for semi-join c. User-defined data type creation and insertion
operation? Explain.
8. Explain query processing and optimization in NESTED TABLE
short?
NESTED TABLE is an Oracle data type used to support
9. What do you mean by object identity? State all
the type constructors and explain with example. columns containing multivalued attributes, in this case,

Definition - What does Database Object mean? columns that can hold an entire sub-table.
A database object in a relational database is a data Examples
structure used to either store or reference data. The most
common object that most people interact with is the table. Create a table with NESTED TABLE column:
Other objects are indexes, stored procedures, sequences,
views and many more. CREATE OR REPLACE TYPE my_tab_t AS TABLE OF
When a database object is created, a new object
VARCHAR2(30);
type cannot be created because all the various object types
/
created are restricted by the very nature, or source code, of
CREATE TABLE nested_table (id NUMBER, col1 my_tab_t)
the relational database model being used, such as Oracle,
SQL Server or Access. What is being created is instances of NESTED TABLE col1 STORE AS col1_tab;
the objects, such as a new table, an index on that table or a
view on the same table. Insert data into table:
Techopedia explains Database Object
Two small but important distinctions are needed:
1. An object type is the base concept or idea of an INSERT INTO nested_table VALUES (1, my_tab_t('A'));
object; for example, the concept of a table or index. INSERT INTO nested_table VALUES (2, my_tab_t('B', 'C'));
2. An object instance is an example of an object type. INSERT INTO nested_table VALUES (3, my_tab_t('D', 'E',
For example, a table called CUSTOMER_MASTER is an
'F'));
instance of the object type TABLE.
Most of the major database engines offer the same set of COMMIT;
major database object types:
 Tables
Select from nested table:
 Indexes
 Sequences
 Views SQL> SELECT * FROM nested_table;
 Synonyms ID COL1
Although there are subtle variations in the behavior ---------- ------------------------
and the syntax used for the creation of these major 1 MY_TAB_T('A')
database object types, they are almost identical in their
concept and what they mean. A table in Oracle behaves 2 MY_TAB_T('B', 'C')
almost exactly as a table in SQL Server. This makes work 3 MY_TAB_T('D', 'E', 'F')
much easier for the database administrator. It is analogous
to moving from one car to another made by a different
manufacturer; the switches for turning the headlights on Unnesting the subtable:
may be in different locations, but the overall layout is
broadly similar.
SQL> SELECT id, COLUMN_VALUE FROM nested_table t1,
When creating an object instance, it is a good idea
to follow an easy-to-understand naming convention. This is TABLE(t1.col1) t2;
especially important for database designers whose products ID COLUMN_VALUE
will be used by several people. It is also helpful to make ---------- ------------------------
work as simple as possible for in-house database
1A
administrators by reducing the number of queries made to

Amol
ADBMS TYMCA Ravi

2B COMMIT;
2C
Select data from table (unnesting the collection):
3D
3E
SQL> SELECT t1.id, t2.column_value
3F
2 FROM varray_table t1, TABLE(t1.col1) t2;
6 rows selected. ID COLUMN_VAL
---------- ----------
A more complicated multi-column nested table where 1A
2B
customers can have multiple addresses:
2C
3D
CREATE TYPE address_t AS OBJECT (
3E
street VARCHAR2(30),
3F
city VARCHAR2(20),
6 rows selected.
state CHAR(2),
zip CHAR(5) );
/ Extract data with PL/SQL:
CREATE TYPE address_tab IS TABLE OF address_t;
/ set serveroutput on
CREATE TABLE customers ( declare
custid NUMBER, v_vcarray vcarray;
address address_tab ) begin
NESTED TABLE address STORE AS customer_addresses; for c1 in (select * from varray_table) loop
dbms_output.put_line('Row fetched...');
INSERT INTO customers VALUES (1, FOR i IN c1.col1.FIRST..c1.col1.LAST LOOP
Address tab ( dbms_output.put_line('...property fetched: '||
Address ('101 First', 'Redwood Shores', 'CA', c1.col1(i));
'94065'), END LOOP;
Address ('123 Maple', 'Mill Valley', 'CA', '90952') end loop;
) ); end;
/

VARRAY
The output Like This:
VARRAY is an Oracle data type used to support columns
Row fetched...
containing multivalued attributes, in this case, columns that
...property fetched: A
can hold a bounded array of values. Row fetched...
Examples ...property fetched: B
...property fetched: C
Create a table with VARRAY column:
Row fetched...
...property fetched: D
CREATE OR REPLACE TYPE vcarray AS VARRAY(10) OF
...property fetched: E
VARCHAR2(128);
...property fetched: F
/
PL/SQL procedure successfully completed.
CREATE TABLE varray_table (id number, col1 vcarray);

Insert data into table:

12. What is an object? Explain the state and behavior
of object with example. Also explain
INSERT INTO varray_table VALUES (1, vcarray('A')); the need for object oriented databases.
INSERT INTO varray_table VALUES (2, vcarray('B', 'C')); 13. Explain the concept of versioning and
INSERT INTO varray_table VALUES (3, vcarray('D', 'E', 'F')); configuration in detail.

Amol
ADBMS TYMCA Ravi
14. What are complex objects? Explain in detail the SGA is the primary memory structures. When
types of complex objects. Oracle DBAs talk about memory, they usually mean the
15. Explain object behavior via class operation and SGA. This area is broken into a few of part memory ––
object persistence via Naming and Reachability
Buffer Cache, Shared Pool, Redo Log Buffer, Large Pool, and
mechanism.
16. Explain the concept of versioning and Java Pool.
configuration in detail. Buffer Cache
17. Explain oracle system architecture. Buffer cache is used to stores the copies of data
What is An Oracle Database? block that retrieved from datafiles. That is, when user
Basically, there are two main components of Oracle retrieves data from database, the data will be stored in
database –– instance and database itself. An instance buffer cache. Its size can be manipulated via
consists of some memory structures and the background DB_CACHE_SIZE parameter in init.ora initialization
processes, whereas a database refers to the disk resources. parameter file.
Figure 1 will show you the relationship. Shared Pool
Shared pool is broken into two small part memories
–– Library Cache and Dictionary Cache. The library cache is
used to stores information about the commonly used SQL
and PL/SQL statements; and is managed by a Least
Recently Used (LRU) algorithm. It is also enables the
sharing those statements among users. In the other
hand, dictionary cache is used to stores information about
object definitions in the database, such as columns, tables,
indexes, users, privileges, etc.
The shared pool size can be set
via SHARED_POOL_SIZE parameter in init.ora initialization
Figure 1. Two main components of Oracle database
parameter file.
Instance
Redo Log Buffer
Database files themselves are useless without the
Each DML statement (select, insert, update, and
memory structures and processes to interact with the
delete) executed by users will generates the redo
database. Oracle defines the term instance as the memory
entry. What is a redo entry? It is an information about all
structure and the background processes used to access data
data changes made by users. That redo entry is stored in
from a database. The memory structures and background
redo log buffer before it is written into the redo log files. To
processes contitute an instance. The memory structure itself
manipulate the size of redo log buffer, you can use
consists of System Global Area (SGA), Program Global Area
theLOG_BUFFER parameter in init.ora initialization
(PGA), and an optional area –– Software Area Code. In the
parameter file.
other hand, the mandatory background processes are
Large Pool
Database Writer (DBWn), Log Writer (LGWR), Checkpoint
Large pool is an optional area of memory in the
(CKPT), System Monitor (SMON), and Process Monitor
SGA. It is used to relieves the burden place on the shared
(PMON). And another optional background processes are
pool. It is also used for I/O processes. The large pool size
Archiver (ARCn), Recoverer (RECO), etc. Figure 2 will
can be set by LARGE_POOL_SIZE parameter in init.ora
illustrate the relationship for those components on an
initialization parameter file.
instance.
Java Pool
As its name, Java pool is used to services parsing of the
Java commands. Its size can be set by JAVA_POOL_SIZE
parameter in init.ora initialization parameter file.
Program Global Area
Although the result of SQL statemen parsing is
stored in library cache, but the value of binding variable will
be stored in PGA. Why? Because it must be private or not be
shared among users. The PGA is also used for sort area.
Software Area Code
Software area code is a location in memory where
the Oracle application software resides.
Oracle processes
There are two categories of processes that run with an
Oracle database. They are mentioned below:
Figure 2. The instance components
System Global Area  User processes
 System processes

Amol
ADBMS TYMCA Ravi
The following figure illustrates the relationship between user and then the ARCH process copies the online redo log files
processes, server processes, PGA, and session: to the archived redo log filesystem on UNIX. The ARCH
process commonly offloads the most recent online redo log
file whenever a log switch operation occurs in Oracle.

The first interaction with the Oracle-based application comes

from the user computer that creates a user process. The
user process then communicates with the server process on
the host computer. Here, PGA is used to store session
specific information.
Oracle Background Processes The figure 4: shows various components of SGA, Oracle
Oracle background processes is the processes background processes, and their interactions with control
behind the scene that work together with the memories. files, data files, Redo Log files, and archived redo logs.
DBWn Database
Database writer (DBWn) process is used to write The database refers to disk resources, and is
data from buffer cache into the datafiles. Historically, the broken into two main structures –– Logical structures and
database writer is named DBWR. But since some of Oracle Physical structures.
version allows us to have more than one database writer, Logical Structures:~
the name is changed to DBWn, where n value is a number 0 Oracle database is divided into smaller logical units
to 9. to manage, store, and retrieve data effeciently. The logical
LGWR units are tablespace, segment, extent, and data block.
Log writer (LGWR) process is similar to DBWn. It Figure 5 will illustrate the relationships between those units.
writes the redo entries from redo log buffer into the redo log
files.
CKPT
Checkpoint (CKPT) is a process to give a signal to
DBWn to writes data in the buffer cache into datafiles. It will
also updates datafiles and control files header when log file
switch occurs.
SMON
System Monitor (SMON) process is used to recover
the system crash or instance failure by applying the entries
in the redo log files to the datafiles.
PMON
Process Monitor (PMON) process is used to clean up
work after failed processes by rolling back the transactions
and releasing other resources.
ARCH
The ARCH background process is invoked when
your database is running in ARCHIVELOG mode. If you are
archiving your redo logs, the redo logs are touched by
several background processes. First, the LGWR process
copies the log_buffer contents to the online redo log files,

Amol
ADBMS TYMCA Ravi
Figure 5. The relationships between the Oracle logical A Redo Log is a file that is part of an Oracle
structures Database. When a transaction is committed the
Tablespace transaction‟s details in the redo log buffer is written in a
A Tablespace is a grouping logical database objects. redo log file. These files contain information that helps in
A database must have one or more tablespaces. In the recovery in the event of system failure.
Figure 5, we have three tablespaces –– SYSTEM tablespace,
Tablespace 1, and Tablespace 2. Tablespace is composed by
one or more datafiles.
There are three types of tablespaces in Oracle:
 Permanent tablespaces
 Undo tablespaces
 temporary tablespaces

Segment
A Tablespace is further broken into segments. A
segment is used to stores same type of objects. That is,
every table in the database will store into a specific segment
(named Data Segment) and every index in the database will
also store in its own segment (named Index Segment). The
other segment types are Temporary Segment and Rollback
Segment.
A segment is a container for objects (such as
tables, views, packages . . . indexes). A segment consists of
Extends.
There are 11 types of Segments in oracle 10g.
1. Table
2. Table Partition The figure 6: shows three Redo Log groups.
3. Index Each group consists of two members. The first
4. Index Partition member of each Redo Log group is stored in directory D1
5. Cluster and the second member is stored in directory D2.
6. Rollback Control Files
7. Deferred Rollback Control files are used to store information about
8. Temporary physical structure of database. The control file is absolutely
9. Cache crucial to database operations. It contains the following
10. Lobsegment types of information:
11. Lobindex 1. Database Information
Extent 2. Archive log history
A segment is further broken into extents. An extent consists 3. Tablespace and datafile records
of one or more data block. When the database object is 4. Redo threads
enlarged, an extent will be allocated. Unlike a tablespace or 5. Database‟s creation data
a segment, an extent cannot be named. Space for a data on 6. Database name
a hard disk is allocated in extends. 7. Current Archive information
Data Block 8. Log records
A data block is the smallest unit of storage in the 9. Database Id which is unique to each Database
Oracle database. The data block size is a specific number of
bytes within tablespace and it has the same number of
bytes.
18. Explain any 6 SQL commands with syntax and
Physical Structures:~The physical structures are
example.
structures of an Oracle database (in this case the disk files)
that are not directly manipulated by users. The physical 19. What is Client/server? Explain with diagram.
structure consists of datafiles, redo log files, and control
files. 20. Explain the functioning of background
Datafiles processes in detail.
A datafile is a file that correspondens with a About Oracle Database Background Processes
tablespace. One datafile can be used by one tablespace, but To maximize performance and accommodate many
one tablespace can has more than one datafiles. An Oracle users, a multiprocess Oracle Database system
databae include of a number of physical files called datafile. uses background processes. Background processes
Redo Log Files consolidate functions that would otherwise be handled by

Amol
ADBMS TYMCA Ravi
multiple database programs running for each user process. Process
Background processes asynchronously perform I/O and Name Description
monitor other Oracle Database processes to provide
increased parallelism for better performance and reliability. SMON also cleans up temporary segments that
Table describes the basic Oracle Database background are no longer in use and recovers dead
processes, many of which are discussed in more detail transactions skipped during system failure and
elsewhere in this book. The use of additional database instance recovery because of file-read or offline
server features or options can cause more background errors. These transactions are eventually
processes to be present. For example, when you use recovered by SMON when the tablespace or file
Advanced Queuing, the queue monitor (QMNn) background is brought back online.
process is present. When you specify Process The process monitor performs process recovery
the FILE_MAPPING initialization parameter for mapping monitor when a user process fails. PMON is responsible
datafiles to physical devices on a storage subsystem, then (PMON) for cleaning up the cache and freeing resources
the FMON process is present. that the process was using. PMON also checks
Table 4-4 Oracle Database Background Processes on the dispatcher processes (described later in
Process this table) and server processes and restarts
Name Description them if they have failed.

Database The database writer writes modified blocks Archiver One or more archiver processes copy the redo
writer from the database buffer cache to the datafiles. (ARCn) log files to archival storage when they are full
(DBWn) Oracle Database allows a maximum of 20 or a log switch occurs. Archiver processes are
database writer processes (DBW0-DBW9 and the subject of Chapter 11, "Managing Archived
DBWa-DBWj). Redo Logs".
The DB_WRITER_PROCESSES initialization
Recoverer The recoverer process is used to resolve
parameter specifies the number of
(RECO) distributed transactions that are pending
DBWn processes. The database selects an
because of a network or system failure in a
appropriate default setting for this initialization
distributed database. At timed intervals, the
parameter or adjusts a user-specified setting
local RECO attempts to connect to remote
based on the number of CPUs and the number
databases and automatically complete the
of processor groups.
commit or rollback of the local portion of any
For more information about setting
pending distributed transactions. For
the DB_WRITER_PROCESSES initialization
information about this process and how to start
parameter, see the Oracle Database
it, see Chapter 33, "Managing Distributed
Performance Tuning Guide.
Transactions".
Log writer The log writer process writes redo log entries to
Dispatcher Dispatchers are optional background processes,
(LGWR) disk. Redo log entries are generated in the redo
(Dnnn) present only when the shared server
log buffer of the system global area (SGA).
configuration is used. Shared server was
LGWR writes the redo log entries sequentially
discussed previously in "Configuring Oracle
into a redo log file. If the database has a
Database for Shared Server".
multiplexed redo log, then LGWR writes the
redo log entries to a group of redo log files. Global In an Oracle Real Application Clusters
See Chapter 10, "Managing the Redo Log" for Cache environment, this process manages resources
information about the log writer process. Service and provides inter-instance resource control.
(LMS)
Checkpoint At specific times, all modified database buffers
(CKPT) in the system global area are written to the
datafiles by DBWn. This event is called a
checkpoint. The checkpoint process is
responsible for signalling DBWn at checkpoints
and updating all the datafiles and control files
of the database to indicate the most recent
checkpoint.

System The system monitor performs recovery when a

monitor failed instance starts up again. In an Oracle
(SMON) Real Application Clusters database, the SMON
process of one instance can perform instance
recovery for other instances that have failed.

Amol
ADBMS TYMCA Ravi
21. Explain in short any two:-
a. Spool.
b. Any two types of joins with example.
c. SQL queries for Table and column aliases with
example.
Spool
To spool (which stands for "simultaneous peripheral
operations online") a computer document or task list (or
"job") is to read it in and store it, usually on a hard disk or
larger storage medium so that it can be printed or otherwise
processed at a more convenient time (for example, when a
printer is finished printing its current document). One can
envision spooling as reeling a document or task list onto a
spool of thread so that it can be unreeled at a more
convenient time.
The idea of spooling originated in early computer
days when input was read in on punched cards for
immediate printing (or processing and then immediately
printing of the results). Since the computer operates at a
much faster rate than input/output devices such as printers,
it was more effective to store the read-in lines on a
magnetic disk until they could be conveniently printed when
the printer was free and the computer was less busy
working on other tasks. Actually, a printer has a buffer but
frequently the buffer isn't large enough to hold the entire
document, requiring multiple I/O operations with the
2. Outer Join
printer.
The spooling of documents for printing We have three types of Outer Join.

and batch job requests still goes on in mainframe computers 1. Left Outer Join
where many users share a pool of resources. On personal Left outer join returns all records/rows from left
computers, your print jobs (for example, a Web page you
table and from right table returns only matched records. If
want to print) are spooled to an output file on hard disk if
there are no columns matching in the right table, it returns
your printer is already printing another file.

Types of Joins NULL values. Syntax for Left outer Join is as :

In Sql Server we have only three types of joins. 1. Select * from table_1 as t1

Using these joins we fetch the data from multiple tables 2. left outer join table_2 as t2

based on condition. 3. on t1.IDcol=t2.IDcol

1. Inner Join 2. Right Outer Join

Inner join returns only those records/rows that match/exists Right outer join returns all records/rows from right table and

in both the tables. Syntax for Inner Join is as from left table returns only matched records. If there are no

1. Select * from table_1 as t1 columns matching in the left table, it returns NULL values.

2. inner join table_2 as t2 Syntax for right outer Join is as :

3. on t1.IDcol=t2.IDcol 1. Select * from table_1 as t1

2. right outer join table_2 as t2

3. on t1.IDcol=t2.IDcol

3. Full Outer Join

Amol
ADBMS TYMCA Ravi
Full outer join combines left outer join and right outer join.

This join returns all records/rows from both the tables.If

there are no columns matching in the both tables, it returns

NULL values. Syntax for full outer Join is as :

1. Select * from table_1 as t1

2. full outer join table_2 as t2

3. on t1.IDcol=t2.IDcol

3. Cross Join

Cross join is a cartesian join means cartesian product of

both the tables. This join does not need any condition to join

two tables. This join returns records/rows that are

multiplication of record number from both the tables means

each row on left table will related to each row of right table.

Syntax for right outer Join is as :

1. Select * from table_1

2. cross join table_2

4. Self-Join

Self-join is used to join a database table to itself,

particularly when the table has a Foreign key that

references its own Primary Key. Basically we have only

three types of joins : Inner join, Outer join and Cross join.

We use any of these three JOINS to join a table to itself. Inner Join

Hence Self join is not a type of Sql join. 1. SELECT t1.OrderID, t0.ProductID, t0.Name,
Join Examples t0.UnitPrice, t1.Quantity, t1.Price
Suppose we following three tables and data in these 2. FROM tblProduct AS t0
three tables is shown in figure. You can download the SQL 3. INNER JOIN tblOrder AS t1 ON t0.ProductID =
script used in this article by using link . t1.ProductID

4. ORDER BY t1.OrderID

Inner Join among more than two tables

1. SELECT t1.OrderID, t0.ProductID, t0.Name,

t0.UnitPrice, t1.Quantity, t1.Price, t2.Name AS

Customer

2. FROM tblProduct AS t0

Amol
ADBMS TYMCA Ravi
3. INNER JOIN tblOrder AS t1 ON t0.ProductID =

t1.ProductID

4. INNER JOIN tblCustomer AS t2 ON

t1.CustomerID = t2.CustID

5. ORDER BY t1.OrderID

Right Outer Join

1. SELECT t1.OrderID AS OrderID , t0.ProductID

Inner Join on multiple conditions , t0.Name , t0.UnitPrice , t1.Quantity AS Quantity ,

1. SELECT t1.OrderID, t0.ProductID, t0.Name, t1.Price AS Price

t0.UnitPrice, t1.Quantity, t1.Price, t2.Name AS 2. FROM tblProduct AS t0

Customer 3. RIGHT OUTER JOIN tblOrder AS t1 ON

2. FROM tblProduct AS t0 t0.ProductID = t1.ProductID

3. INNER JOIN tblOrder AS t1 ON t0.ProductID = 4. ORDER BY t0.ProductID

t1.ProductID

4. INNER JOIN tblCustomer AS t2 ON

t1.CustomerID = t2.CustID AND t1.ContactNo =

t2.ContactNo

5. ORDER BY t1.OrderID

Full Outer Join

1. SELECT t1.OrderID AS OrderID , t0.ProductID

, t0.Name , t0.UnitPrice , t1.Quantity AS Quantity ,

Left Outer Join t1.Price AS Price

1. SELECT t1.OrderID AS OrderID , t0.ProductID 2. FROM tblProduct AS t0

, t0.Name , t0.UnitPrice , t1.Quantity AS Quantity , 3. FULL OUTER JOIN tblOrder AS t1 ON

t1.Price AS Price t0.ProductID = t1.ProductID

2. FROM tblProduct AS t0 4. ORDER BY t0.ProductID

3. LEFT OUTER JOIN tblOrder AS t1 ON

t0.ProductID = t1.ProductID

4. ORDER BY t0.ProductID

5.

Cross Join

Amol
ADBMS TYMCA Ravi
1. SELECT t1.OrderID, t0.ProductID, t0.Name, references its own Primary Key id. We use it for Self

t0.UnitPrice, t1.Quantity, t1.Price Join

2. FROM tblProduct AS t0, tblOrder AS t1 7. INSERT INTO emp(id,name,designation)

3. ORDER BY t0.ProductID VALUES(1,'mohan','Manger')

8. INSERT INTO emp(id,name,designation,supid)

VALUES(2,'raj kumar','SE',1)

9. INSERT INTO emp(id,name,designation)

VALUES(3,'bipul kumar','Manager')

10. INSERT INTO

emp(id,name,designation,supid) VALUES(4,'mrinal

kumar','SE',2)

11. INSERT INTO

emp(id,name,designation,supid) VALUES(5,'jitendra

kumar','SE',2)

1. CREATE TABLE empinfo

2. (

3. id int primary key,

4. address varchar(50) NULL

5. )

6. INSERT INTO empinfo(id,address)

Self Join
VALUES(1,'Delhi')
To understand Self Join, suppose we following two tables
7. INSERT INTO empinfo(id,address)
and data in these two tables is shown in figure.
VALUES(2,'Noida')
1. CREATE TABLE emp
8. INSERT INTO empinfo(id,address)
2. (
VALUES(4,'Gurgaon')
3. id int NOT NULL primary key,
9. INSERT INTO empinfo(id,address)
4. name varchar(100) NULL,
VALUES(6,'Delhi')
5. designation varchar(50) NULL,
10. INSERT INTO empinfo(id,address)
6. supid int foreign key references emp(id) ) --
VALUES(7,'Noida')
In this table we have a Foreign key supid that

Amol
ADBMS TYMCA Ravi
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
(b) Another table is ORDERS as follows:
+-----+---------------------+-------------+--------+
|OID | DATE | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 | 3 | 3000 |
| 100 | 2009-10-08 00:00:00 | 3 | 1500 |
| 101 | 2009-11-20 00:00:00 | 2 | 1560 |
| 103 | 2008-05-20 00:00:00 | 4 | 2060 |
+-----+---------------------+-------------+--------+
Now, following is the usage of table alias:
SQL> SELECT C.ID, C.NAME, C.AGE, O.AMOUNT
1. select e.id,e.name,e.supid as managerid, FROM CUSTOMERS AS C, ORDERS AS O
WHERE C.ID = O.CUSTOMER_ID;
ei.name as managername from emp e left join emp ei
This would produce the following result:
on e.supid=ei.id;
+----+----------+-----+--------+
2. -- outer keyword is optional | ID | NAME | AGE | AMOUNT |
+----+----------+-----+--------+
| 3 | kaushik | 23 | 3000 |
| 3 | kaushik | 23 | 1500 |
| 2 | Khilan | 25 | 1560 |
| 4 | Chaitali | 25 | 2060 |
+----+----------+-----+--------+
Following is the usage of column alias:
SQL> SELECT ID AS CUSTOMER_ID, NAME AS
CUSTOMER_NAME
FROM CUSTOMERS
WHERE SALARY IS NOT NULL;
Aliases This would produce the following result:
You can rename a table or a column temporarily by
+-------------+---------------+
giving another name known as alias.
| CUSTOMER_ID | CUSTOMER_NAME |
The use of table aliases means to rename a table in
+-------------+---------------+
a particular SQL statement. The renaming is a temporary
| 1 | Ramesh |
change and the actual table name does not change in the
| 2 | Khilan |
database.
| 3 | kaushik |
The column aliases are used to rename a table's
| 4 | Chaitali |
columns for the purpose of a particular SQL query.
| 5 | Hardik |
Syntax: | 6 | Komal |
The basic syntax of table alias is as follows: | 7 | Muffy |
+-------------+---------------+
SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition]; 22. Explain in detail the use of data and redo log
files.
The basic syntax of column alias is as follows:
SELECT column_name AS alias_name What Oracle says on Redo Log File???
FROM table_name The most crucial and vital structure for recovery
WHERE [condition]; operations is the online redo log, which consists of two or
more pre-allocated files that store all changes made to the
Example: database as they occur. Every instance of an Oracle
Consider the following two tables, (a) CUSTOMERS table is database has an associated online redo log to protect the
as follows: database in case of an instance failure.
What is a redo log thread?
+----+----------+-----+-----------+----------+ Each database instance has its own online redo log
| ID | NAME | AGE | ADDRESS | SALARY | groups. These online redo log groups, multiplexed or not,
+----+----------+-----+-----------+----------+ are called an instance's thread of online redo. In typical
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 | configurations, only one database instance accesses an
| 2 | Khilan | 25 | Delhi | 1500.00 | Oracle database, so only one thread is present. When
| 3 | kaushik | 23 | Kota | 2000.00 | running Oracle Real Application Clusters, however, two or
| 4 | Chaitali | 25 | Mumbai | 6500.00 | more instances concurrently access a single database and
| 5 | Hardik | 27 | Bhopal | 8500.00 | each instance has its own thread. The relation ship between
| 6 | Komal | 22 | MP | 4500.00 | Oracle Instance and Database is many-to-one. More than
Amol
ADBMS TYMCA Ravi
one Instance can access a Database. This kind of redo log buffer. The online redo log file that LGWR is
configuration is called Parallel Server Configuration. actively writing to is called the current online redo log file.
What those files contain? Online redo log files that are required for instance
Online redo log files are filled with redo records. A recovery are called active online redo log files. Online redo
redo record, also called a redo entry, is made up of a group log files that are not required for instance recovery are
of change vectors, each of which is a description of a change called inactive.
made to a single block in the database. For example, if you If you have enabled archiving (ARCHIVELOG mode),
change a salary value in an employee table, you generate a Oracle cannot reuse or overwrite an active online log file
redo record containing change vectors that describe changes until ARCn has archived its contents. If archiving is disabled
to the data segment block for the table, the rollback (NOARCHIVELOG mode), when the last online redo log file
segment data block, and the transaction table of the fills writing continues by overwriting the first available active
rollback segments. file.
Redo entries record data that you can use to Which parameter influences the log switches??
reconstruct all changes made to the database, including the LOG_CHECKPOINT_TIMEOUT specifies the amount of
rollback segments. Therefore, the online redo log also time, in seconds, that has passed since the incremental
protects rollback data. checkpoint at the position where the last write to the redo
When you recover the database using redo data, log (sometimes called the tail of the log) occurred. This
Oracle reads the change vectors in the redo records and parameter also signifies that no buffer will remain dirty (in
applies the changes to the relevant blocks. the cache) for more than integer seconds. This is time based
Redo records are buffered in a circular fashion in switching of the log files.
the redo log buffer of the SGA and are written to one of the LOG_CHECKPOINT_INTERVAL specifies the frequency of
online redo log files by the Oracle background process Log checkpoints in terms of the number of redo log file blocks
Writer (LGWR). Whenever a transaction is committed, LGWR that can exist between an incremental checkpoint and the
writes the transaction's redo records from the redo log last block written to the redo log. This number refers to
buffer of the SGA to an online redo log file, and a system physical operating system blocks, not database blocks. This
change number (SCN) is assigned to identify the redo block based switching of the log files.
records for each committed transaction. Only when all redo How do I respond the redo log failures???
records associated with a given transaction are safely on If Then
disk in the online logs is the user process notified that the
transaction has been committed. LGWR can Writing proceeds as normal. LGWR
Redo records can also be written to an online redo successfully write simply writes to the available
log file before the corresponding transaction is committed. If to at least one members of a group and ignores
the redo log buffer fills, or another transaction commits, member in a group the unavailable members.
LGWR flushes all of the redo log entries in the redo log LGWR cannot
buffer to an online redo log file, even though some redo access the next Database operation temporarily
records may not be committed. If necessary, Oracle can roll group at a log halts until the group becomes
back these changes. switch because the available, or, until the group is
How Oracle Writes to the Online Redo Log? group needs to be archived.
The online redo log of a database consists of two or archived
more online redo log files. Oracle requires
a minimum of two files to guarantee that one is always Oracle returns an error and the
available for writing while the other is being archived (if in database instance shuts down. In
ARCHIVELOG mode). this case, you may need to
LGWR writes to online redo log files in a circular perform media recovery on the
fashion. When the current online redo log file fills, LGWR database from the loss of an online
begins writing to the next available online redo log file. redo log file. If the database
All members of the
When the last available online redo log file is filled, LGWR checkpoint has moved beyond the
next group are
returns to the first online redo log file and writes to it, lost redo log, media recovery is
inaccessible to
starting the cycle again. Filled online redo log files are not necessary since Oracle has
LGWR at a log
available to LGWR for reuse depending on whether archiving saved the data recorded in the
switch because of
is enabled. There can be contention between filling up of the redo log to the data files. Simply
media failure
on line redo log files and archiving of the redo log files, if drop the inaccessible redo log
they filled faster than they are written to the archived log group. If Oracle did not archive the
file. This because online log file written in Oracle blocks and bad log, use ALTER DATABASE
archives are written in OS blocks CLEAR UNARCHIVED LOG to
If archiving is disabled (NOARCHIVELOG mode), a disable archiving before the log
filled online redo log file is available once the changes can be dropped.
recorded in it have been written to the data files. Oracle returns an error and the
If archiving is enabled (ARCHIVELOG mode), a filled database instance immediately
online redo log file is available to LGWR once the changes If all members of a
shuts down. In this case, you may
recorded in it have been written to the datafiles and once group suddenly
need to perform media recovery. If
the file has been archived. become
the media containing the log is not
What is meant by Active (Current) and Inactive Online inaccessible to
actually lost--for example, if the
Redo Log Files??? LGWR while it is
drive for the log was inadvertently
At any given time, Oracle uses only one of the writing to them
turned off--media recovery may
online redo log files to store redo records written from the not be needed. In this case, you

Amol
ADBMS TYMCA Ravi
only need to turn the drive back on disk. The control files of the associated database are
and let Oracle perform instance updated to drop the members of the group from the
recovery. database structure. After dropping an online redo log group,
make sure that the drop completed successfully, and then
How to add a redo log file group and member?? use the appropriate operating system command to delete
Suppose you are to add group 5 with 2 members the the dropped online redo log files.
command is: To drop an online redo log member, you must have the
ALTER DATABASE ALTER DATABASE system privilege. Consider the following
ADD LOGFILE GROUP 10 restrictions and precautions before dropping individual
('c:\oracle\oradata\whs\redo\redo_05_01.log', online redo log members:
'd:\oracle\oradata\whs\redo\redo_05_02.log') It is permissible to drop online redo log files so that
SIZE 100M; a multiplexed online redo log becomes temporarily
This command is used to add another member to the group asymmetric. For example, if you use duplexed groups of
already existing. online redo log files, you can drop one member of one
ALTER DATABASE ADD LOGFILE MEMBER group, even though all other groups have two members
'c:\oracle\oradata\whs\redo\redo_05_03.log' each. However, you should rectify this situation immediately
TO GROUP 5; so that all groups have at least two members, and thereby
How to move a redo log file from one destination to another eliminate the single point of failure possible for the online
destination?? redo log.
01. Shutdown database normal/immediate but not abort. An instance always requires at least two valid
Shutdown immediate; groups of online redo log files, regardless of the number of
02. Copy the online redo log files to the new location. members in the groups. (A group is one or more members.)
Unix use mv command If the member you want to drop is the last valid member of
Windows move command the group, you cannot drop the member until the other
03. Startup MOUNT database logging in as sysdba (do not members become valid. To see a redo log file's status, use
open the database) the V$LOGFILE view. A redo log file becomes INVALID if
startup mount pfile=<initialization parameter file with path> Oracle cannot access it. It becomes STALE if Oracle suspects
04. Issue the following statement that it is not complete or correct. A stale log file becomes
Ex valid again the next time its group is made the active group.
You are changing the file from c:\oracle\oradata\redologs to You can drop an online redo log member only if it is not part
c:\oracle\oradata\whs\redologs and like wise on d:\ drive. of an active or current group. If you want to drop a member
ALTER DATABASE RENAME FILE of an active group, first force a log switch to occur. Make
'c:\oracle\oradata\redologs\redo_01_01.log', sure the group to which an online redo log member belongs
'd:\oracle\oradata\redologs\redo_01_02.log' is archived (if archiving is enabled) before dropping the
TO member. To see whether this has happened, use the V$LOG
'c:\oracle\oradata\whs\redologs\redo_01_01.log', view.
'd:\oracle\oradata\whs\redologs\redo_01_02.log' To drop specific inactive online redo log members,
/ use the ALTER DATABASE statement with the DROP
05. Open the database alter database open; LOGFILE MEMBER clause.
How to drop a redo log file group or/and member??? The following statement drops the redo log
To drop an online redo log group, you must have 'redo_01_01.log' for group 01 member 01
the ALTER DATABASE system privilege. Before dropping an
online redo log group, consider the following restrictions and ALTER DATABASE DROP LOGFILE MEMBER
precautions: 'c:\oracle\oradata\redologs\redo_01_01.log'
An instance requires at least two groups of online
redo log files, regardless of the number of members in the When an online redo log member is dropped from
groups. (A group is one or more members.) the database, the operating system file is not deleted from
You can drop an online redo log group only if it is inactive. If disk. Rather, the control files of the associated database are
you need to drop the current group, first force a log switch updated to drop the member from the database structure.
to occur. After dropping an online redo log file, make sure that the
Make sure an online redo log group is archived (if drop completed successfully, and then use the appropriate
archiving is enabled) before dropping it. To see whether this operating system command to delete the dropped online
has happened, use the V$LOG view. redo log file.
SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG; To drop a member of an active group, you must
GROUP# ARC STATUS first force a log switch and as a result that member becomes
--------- --- ---------------- inactive.
1 YES ACTIVE How can I force the log switch??
2 NO CURRENT ALTER SYSTEM SWITCH LOGFILE;
3 YES INACTIVE How can I Clear an Online Redo Log File??
4 YES INACTIVE ALTER DATABASE CLEAR LOGFILE GROUP 3;
Drop an online redo log group with the SQL This statement overcomes two situations where
statement ALTER DATABASE with the DROP LOGFILE clause. dropping redo logs is not possible:
The following statement drops redo log group number 3: (1) If there are only two log groups
ALTER DATABASE DROP LOGFILE GROUP 3; (2) The corrupt redo log file belongs to the current group
When an online redo log group is dropped from the (3) If the corrupt redo log file has not been archived, use
database, and you are not using the Oracle Managed Files the UNARCHIVED keyword in the statement.
feature, the operating system files are not deleted from ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;
Amol
ADBMS TYMCA Ravi
Which Metadata Views I am to refer for redo log files????
SQL> DESC CUSTOMERS;
View Description +---------+---------------+------+-----+---------+-------+
Displays the redo log file | Field | Type | Null | Key | Default | Extra |
V$LOG information from the +---------+---------------+------+-----+---------+-------+
control file | ID | int(11) | NO | PRI | | |
| NAME | varchar(20) | NO | | | |
Identifies redo log groups | AGE | int(11) | NO | | | |
V$LOGFILE and members and member | ADDRESS | char(25) | YES | | NULL | |
status | SALARY | decimal(18,2) | YES | | NULL | |
Contains log history +---------+---------------+------+-----+---------+-------+
V$LOG_HISTORY 5 rows in set (0.00 sec)
information
Now, you have CUSTOMERS table available in your database
23. Write down the SQL queries for the following: which you can use to store required information related to
a. Create a table, commit it. Then alter the table customers.
and add two columns in it.
b. Partially insert the data in few columns of the The SQL INSERT INTO Statement is used to add new rows
above table. of data to a table in the database.
c. Truncate and drop queries. Explain the
difference between truncate and drop SQL Syntax:
commands. There are two basic syntaxes of INSERT INTO statement as
follows:
Creating a basic table involves naming the table INSERT INTO TABLE_NAME (column1, column2,
and defining its columns and each column's data type. column3,...columnN)]
The SQL CREATE TABLE statement is used to create a new VALUES (value1, value2, value3,...valueN);
table.
Here, column1, column2,...columnN are the names of the
Syntax: columns in the table into which you want to insert data.
Basic syntax of CREATE TABLE statement is as follows: You may not need to specify the column(s) name in the SQL
query if you are adding values for all the columns of the
CREATE TABLE table_name(
table. But make sure the order of the values is in the same
column1 datatype,
order as the columns in the table. The SQL INSERT INTO
column2 datatype,
syntax would be as follows:
column3 datatype,
..... INSERT INTO TABLE_NAME VALUES
columnN datatype, (value1,value2,value3,...valueN);
PRIMARY KEY( one or more columns )
); Example:
CREATE TABLE is the keyword telling the database system Following statements would create six records in
what you want to do. In this case, you want to create a new CUSTOMERS table:
table. The unique name or identifier for the table follows the INSERT INTO CUSTOMERS
CREATE TABLE statement. (ID,NAME,AGE,ADDRESS,SALARY)
Then in brackets comes the list defining each VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );
column in the table and what sort of data type it is. The
syntax becomes clearer with an example below. INSERT INTO CUSTOMERS
A copy of an existing table can be created using a (ID,NAME,AGE,ADDRESS,SALARY)
combination of the CREATE TABLE statement and the VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );
SELECT statement. You can check complete details
at Create Table Using another Table. INSERT INTO CUSTOMERS
(ID,NAME,AGE,ADDRESS,SALARY)
Example:
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );
Following is an example, which creates a
CUSTOMERS table with ID as primary key and NOT NULL
INSERT INTO CUSTOMERS
are the constraints showing that these fields can not be
(ID,NAME,AGE,ADDRESS,SALARY)
NULL while creating records in this table:
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );
SQL> CREATE TABLE CUSTOMERS(
ID INT NOT NULL, INSERT INTO CUSTOMERS
NAME VARCHAR (20) NOT NULL, (ID,NAME,AGE,ADDRESS,SALARY)
AGE INT NOT NULL, VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID) INSERT INTO CUSTOMERS
); (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Komal', 22, 'MP', 4500.00 );
You can verify if your table has been created successfully by
looking at the message displayed by the SQL server, You can create a record in CUSTOMERS table using second
otherwise you can use DESC command as follows: syntax as follows:

Amol
ADBMS TYMCA Ravi
The SQL DROP TABLE statement is used to remove a table
INSERT INTO CUSTOMERS
definition and all data, indexes, triggers, constraints, and
VALUES (7, 'Muffy', 24, 'Indore', 10000.00 );
permission specifications for that table.
All the above statements would produce the following NOTE: You have to be careful while using this command
records in CUSTOMERS table: because once a table is deleted then all the information
+----+----------+-----+-----------+----------+ available in the table would also be lost forever.
| ID | NAME | AGE | ADDRESS | SALARY | Syntax:
+----+----------+-----+-----------+----------+ Basic syntax of DROP TABLE statement is as follows:
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 | DROP TABLE table_name;
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 | Example:
| 5 | Hardik | 27 | Bhopal | 8500.00 | Let us first verify CUSTOMERS table and then we would
| 6 | Komal | 22 | MP | 4500.00 | delete it from the database:
| 7 | Muffy | 24 | Indore | 10000.00 |
SQL> DESC CUSTOMERS;
+----+----------+-----+-----------+----------+
+---------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
Populate one table using another table: +---------+---------------+------+-----+---------+-------+
You can populate data into a table through select statement | ID | int(11) | NO | PRI | | |
over another table provided another table has a set of fields, | NAME | varchar(20) | NO | | | |
which are required to populate first table. Here is the | AGE | int(11) | NO | | | |
syntax: | ADDRESS | char(25) | YES | | NULL | |
INSERT INTO first_table_name [(column1, column2, ... | SALARY | decimal(18,2) | YES | | NULL | |
columnN)] +---------+---------------+------+-----+---------+-------+
SELECT column1, column2, ...columnN 5 rows in set (0.00 sec)
FROM second_table_name This means CUSTOMERS table is available in the database,
[WHERE condition]; so let us drop it as follows:
SQL> DROP TABLE CUSTOMERS;
The SQL TRUNCATE TABLE command is used to Query OK, 0 rows affected (0.01 sec)
delete complete data from an existing table.
You can also use DROP TABLE command to delete complete Now, if you would try DESC command, then you would get
table but it would remove complete table structure form the error as follows:
database and you would need to re-create this table once SQL> DESC CUSTOMERS;
again if you wish you store some data. ERROR 1146 (42S02): Table 'TEST.CUSTOMERS' doesn't
exist
Syntax:
The basic syntax of TRUNCATE TABLE is as follows: Here, TEST is database name which we are using for our
examples.
TRUNCATE TABLE table_name;
SQL Delete Statement
Example:
Consider the CUSTOMERS table having the following The DELETE Statement is used to delete rows from a table.
records: The Syntax of a SQL DELETE statement is:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY | DELETE FROM table_name [WHERE condition];
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |  table_name -- the table name which has to be
| 2 | Khilan | 25 | Delhi | 1500.00 |
updated.
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 | NOTE:The WHERE clause in the sql delete command is
| 5 | Hardik | 27 | Bhopal | 8500.00 | optional and it identifies the rows in the column that gets
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 | deleted. If you do not include the WHERE clause all the rows
+----+----------+-----+-----------+----------+ in the table is deleted, so be careful while writing a DELETE
Following is the example to truncate: query without WHERE clause.
SQL > TRUNCATE TABLE CUSTOMERS; For Example: To delete an employee with id 100 from the
Now, CUSTOMERS table is truncated and following would be employee table, the sql delete query would be like,
the output from SELECT statement:
SQL> SELECT * FROM CUSTOMERS; DELETE FROM employee WHERE id = 100;
Empty set (0.00 sec)
To delete all the rows from the employee table, the query
would be like,

Amol
ADBMS TYMCA Ravi
Database locks are used to provide concurrency
DELETE FROM employee;
control in order to ensure data consistency and integrity.
SQL TRUNCATE Statement
Common uses of locks are:
The SQL TRUNCATE command is used to delete all the rows  ensure that only one user can modify a record at a
from the table and free the space containing the table. time;
Syntax to TRUNCATE a table:
 ensure that a table cannot be dropped while
TRUNCATE TABLE table_name; another user is querying it;
 ensure that one user cannot delete a record while
For Example: To delete all the rows from employee table, another is updating it.
the query would be like, Lock Types and Modes
TRUNCATE TABLE employee; Depending on their lock type (see V$LOCK.TYPE), locks can
be grouped into user types and system types. The user type
Difference between DELETE and TRUNCATE
locks are:
Statements:
 TX - Transaction enqueue, acquired at row level
DELETE Statement: This command deletes only the rows
 TM - DML enqueue, acquired at object (i.e. table)
from the table based on the condition given in the where level
clause or deletes all the rows from the table if no condition  UL - User supplied
is specified. But it does not free the space containing the Another distinction for locks is based on lock modes (See
table. V$LOCK.LMODE). The following lock modes exist:
TRUNCATE statement: This command is used to delete all  0 - none
the rows from the table and free the space containing the  1 - null (NULL)
table.  2 - row-S (SS)
SQL DROP Statement:  3 - row-X (SX)
The SQL DROP command is used to remove an object from  4 - share (S)
the database. If you drop a table, all the rows in the table is  5 - S/Row-X (SSX)
deleted and the table structure is removed from the  6 - exclusive (X)
database. Once a table is dropped we cannot get it back, so [R]Lock detection

be careful while using DROP command. When a table is This query will show locked objects with session details:
dropped all the references to the table will not be valid.
SELECT
Syntax to drop a sql table structure:
a.owner,a.object_name,b.sid,b.serial#,b.username,b.status
DROP TABLE table_name; FROM dba_objects a, v$session b, v$locked_object c
WHERE a.object_id = c.object_id
For Example: To drop the table employee, the query would
AND b.sid = c.session_id;
be like

DROP TABLE employee; [R]Finding more info about an Oracle lock

Oracle uses locks and enqueues to serialize access to certain
Difference between DROP and TRUNCATE Statement:
resources. They can be seen in v$lock. Every DBA is familiar
If a table is dropped, all the relationships with other tables
with common lock types, such as TX and TM. Some other
will no longer be valid, the integrity constraints will be
types such as CI, CF, JQ, ST are also fairly well-known and
dropped, grant or access privileges on the table will also be documented in the v$lock reference manual (go to
dropped, if want use the table again it has to be recreated documentation -> Reference -> v$lock).
with the integrity constraints, access privileges and the Unfortunately, not all lock types are listed in v$lock
relationships with other tables should be established again. reference page. In addition, that page does not give you the
But, if a table is truncated, the table structure remains the meanings of ID1 and ID2 columns of the v$lock view for
same, therefore any of the above problems will not exist. each lock type. Most people know id1 for a TM lock matches
dba_objects.object_id (not data_object_id, in case they
24. Explain oracle locks in detail.
differ). Some DBAs go too far and write scripts matching id1

Amol
ADBMS TYMCA Ravi
with an object_id without the type='TM' predicate. If you
have Anjo Kolk's wait event article, you know that's not WAIT #1: nam='enqueue' ela= 910796 p1=1128857606
true. Anjo's article is titled "Description of Oracle7 Wait p2=2 p3=5

Events and Enqueues". As of this writing, you can find it

That tells us that the approximately 1 second wait is on an
at http://www.orsweb.com/techpapers/eventsenque.pdf Si
enqueue of type CI. I know it's CI because p1 is
nce any URL pointing to that article may be gone at any
0x43490006 in hex and 0x43 0x49 are ASCII codes for C
time, please search the Internet if the given URL is not
and I. The ending 6 means it's waiting for a CI lock held in
valid. Part 3 of the article lists all lock types in Oracle 7,
exclusive mode. ID1 for the lock is 2 and ID2 is 5. According
giving much more details than just the type names.
to Anjo's paper, ID1=2 means "DBWR synchronization of
Oracle Metalink has a note pointing to other articles
SGA with control file", which makes perfect sense in
about specific lock types. Look at Locking Reference section
checkpoint, ID2=5 means "Used to queue up interested
of Note:131775.1. Some good articles about oracle locks in
clients".
general are listed too. Note:267332.1 lists the locks new to
Unfortunately not all lock gets cause waits and
10g as claimed. But take the word "new" with a grain of
therefore are not exposed as wait events. For instance, I
salt. When searching for something obscure on Metalink,
haven't figured out a way to artificially induce a CF
make sure you use Advanced Search, which you can access
(controlfile) enqueue wait even though the lock is very
through this URL
frequently held even on a quiet database (just keep running
http://metalink.oracle.com/metalink/plsql/kno_main.newAd
select * from v$enqueue_stat where eq_type = 'CF'). If you
vancedQuery and check Bug Database and Archived Articles
see such CF in v$lock, you have a precious opportunity for
checkboxes.
research. Maybe dump the process state just for a record
When you search on google, make sure you search
and analyze later, and possibly a hanganalyze trace, which
on both Web and Groups pages (groups.google.com).
is a simplified systemstate dump.
If you need in-depth knowledge of Oracle locks, Steve
Adams's book "Oracle8i Internal Services" is the ultimate 25. What is online table redefinition? Explain in
reference unless you work for Oracle (even then that detail with example.
Redefining tables online – Oracle 11g
bumble bee book is still invaluable).
Introduction:
The problem with searching for lock information is that all One of the many challenges that we face in
locks use two-letter codes (see sys.x$ksqst.ksqsttyp). Short production environment is make changes to big tables.
strings make it difficult to search on the Internet. So often If you consider a case of any OLTP systems, its easy to have
tables whose size is beyond 10G.
times you have to do your own research. Get a rough idea
This again depends on the nature of the database and kind
of what this lock is about with any available reference. Let's
of transactions happening on the table.
say it's CI, somehow named for Cross Instance as if it were So lets consider a case where you have a big table
for RAC or OPS only. According to Anjo Kolk's article, the which is also a very hot table having very high number of
lock is held for checkpoints, log switches... Let's check. In 8i transactions / sec and something that continues 24X7.
Its difficult to take downtime on such tables and making
query for x$ksqst.ksqstget where ksqsttyp= 'CI' and in 9i
DDL changes to such tables could be a nightmare.
v$enqueue_stat.total_req# and succ_req# where eq_typ =
Oracle has a great feature introudcued since Oracle 9i, but
'CI'. These queries may tell you whether your recent actions many DBAs doesnt seem to be aware of this feature –
caused enqueue gets to increment. My test shows that Online table redefiniation.
checkpoints indeed increment the gets but log switches do Online table redefinition allows you to make DDL changes to
the table definition and requires very little downtime (less
not. If you can find a way to hold the lock for some time,
than a minute). Techinically its not the same table that gets
that's even better, because then you can look at v$lock modified, but its another copy of the same table which has
instead, and possibly figure out what id1 and id2 are. If you the required modification made.
can't hold the lock, you may still be able to catch it by You might question if we are making a new copy of
tracing the session. In your own session, alter session set the table we can as well use CTAS (Create Table as Select)
and make the required changes. But its not just about
events = '10046 trace name context forever, level 8', or
creating new object and copying the data. Online
exec sys.dbms_system.set_ev(<sid>,<seq#>,10046,8,) for redefinition does lot more than that.
a victim session. In 9i, the generated trace file in udump I will briefly explain you the features of online
contains lines like redefinition, followed by process and then we will straight

Amol
ADBMS TYMCA Ravi
way get to the examples which will help you to understand table in sync with changes that happens on current
better. production table.
Features – What it can do: This will reduce the cut off time. Cut off time if
Online table redefinition enables you to: when you are going to point everything to new table and
 Modify the storage parameters of a table or cluster services will start writing to new table.
 Move a table or cluster to a different tablespace The more you keep new table and current production table
in sync, lesser will be cut off time and downtime.
 Add, modify, or drop one or more columns in a
5) Copy dependent objects (such as triggers, indexes,
table or cluster
grants, and constraints)
 Add or drop partitioning support (non-clustered
Copy dependent objects (such as triggers, indexes,
tables only)
materialized view logs, grants, and constraints) and
 Change partition structure
statistics from production table to new table.
 Change physical properties of a single table You should make sure that all dependents are copied.
partition, including moving it to a different tablespace in the You can do this manually by creating each dependent object
same schema or you can do it automatically using
 Change physical properties of a materialized view DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
log or an Oracle Streams Advanced Queueing queue table 6) Complete redefinition process by using
 Re-create a table or cluster to reduce fragmentation DBMS_REDEFINITION.FINISH_REDEF_TABLE
Process – How its done: procedure
To briefly explain the process, it involves following steps This will complete the redefinition process. This
1) Check if the table can be refined by running needs exclusive lock on production table which you want to
DBMS_REDEFINITION.CAN_REDEF_TABLE procedure. redefine.
We have following restrictions on redefining the table So you need to arrange for short downtime.
 One cannot redefine Materialized Views (MViews) However, FINISH_REDEF_TABLE will wait for all pending
and tables with MViews or MView Logs defined on them. DML to commit before completing the redefinition.
 One cannot redefine Temporary and Clustered Example:
Tables Lets take an example:
We have a table T as shown below.
 One cannot redefine tables with BFILE, LONG or
We have a primary key on OBJECT_ID column.
LONG RAW columns
We have a public synonym for table T.
 One cannot redefine tables belonging to SYS or
SQL>desc T
SYSTEM
Name Null? Type
 One cannot redefine Object tables --------------------------- -------- ----------------------------
 Table redefinition cannot be done in NOLOGGING OWNER VARCHAR2(30)
mode (watch out for heavy archiving) OBJECT_NAME VARCHAR2(128)
2) Create a new table with all of the desired logical SUBOBJECT_NAME VARCHAR2(30)
and physical attributes. OBJECT_ID NUMBER
If you want to change non-partition table to partition, you DATA_OBJECT_ID NUMBER
can create a new partition table. Structure of the table OBJECT_TYPE VARCHAR2(19)
should be exactly the way you want to convert to. CREATED DATE
3) Start redefinition process using LAST_DDL_TIME DATE
DBMS_REDEFINITION.START_REDEF_TABLE TIMESTAMP VARCHAR2(19)
procedure. STATUS VARCHAR2(7)
Be careful before running this command. If you must know, TEMPORARY VARCHAR2(1)
this command will start populating new table from the data GENERATED VARCHAR2(1)
in old table. So if your old table is very big, then you need SECONDARY VARCHAR2(1)
to have same amount of space available in the tablespace NAMESPACE NUMBER
where new table is created. Also, this command might take RION_NAME VARCHAR2(30)
very long time if the size is big, so make sure you don‟t
have any disconnection in between. SQL>
If needed you can enable parallel before starting Currently this is not a partitioned table
redefinition using following commands SQL>select table_name, partitioned from user_tables where
alter session force parallel dml parallel degree-of- table_name = 'T';
parallelism; alter session force parallel query parallel
degree-of-parallelism;
4) Sync new table on regular basis till cut off time
You should use
DBMS_REDEFINITION.SYNC_INTERIM_TABLE and keep new

Amol
ADBMS TYMCA Ravi
TABLE_NAME PAR PARTITION BY RANGE(CREATED)
------------------------------ --- (
T NO PARTITION T_2003 VALUES LESS THAN (TO_DATE('01-JAN-
2004','DD-MON-YYYY')),
Step 1) Check if the table can be refined by running PARTITION T_2004 VALUES LESS THAN (TO_DATE('01-JAN-
DBMS_REDEFINITION.CAN_REDEF_TABLE procedure. 2005','DD-MON-YYYY')),
SQL>set serveroutput on PARTITION T_2005 VALUES LESS THAN (TO_DATE('01-JAN-
SQL>EXEC 2006','DD-MON-YYYY')),
DBMS_REDEFINITION.CAN_REDEF_TABLE(user,'T',DBMS_R PARTITION T_2006 VALUES LESS THAN (TO_DATE('01-JAN-
EDEFINITION.CONS_USE_PK); 2007','DD-MON-YYYY')),
PARTITION T_2007 VALUES LESS THAN (TO_DATE('01-JAN-
PL/SQL procedure successfully completed. 2008','DD-MON-YYYY')),
PARTITION T_2008 VALUES LESS THAN (TO_DATE('01-JAN-
SQL> 2009','DD-MON-YYYY')),
If the table is not a candidate for online redefinition, an PARTITION T_2009 VALUES LESS THAN (TO_DATE('01-JAN-
error message is raised. 2010','DD-MON-YYYY')),
Step 2) Create a new intrim table with all of the desired PARTITION T_2010 VALUES LESS THAN (TO_DATE('01-JAN-
logical and physical attributes. 2011','DD-MON-YYYY')),
For table T lets try to partition by CREATED which is a date PARTITION T_2011 VALUES LESS THAN (TO_DATE('01-JAN-
column. I am planning to partition by year so we can get 10 2012','DD-MON-YYYY')),
partitions PARTITION T_2012 VALUES LESS THAN (TO_DATE('01-JAN-
SQL>select to_char(CREATED,'YYYY') from T group by 2013','DD-MON-YYYY'))
to_char(CREATED,'YYYY'); );
3) Start redefinition process using
TO_C DBMS_REDEFINITION.START_REDEF_TABLE procedure.
---- BEGIN
2003 DBMS_REDEFINITION.START_REDEF_TABLE(
2004 uname => user,
2005 orig_table => 'T',
2006 int_table => 'T_INTRIM',
2007 col_mapping => NULL,
2008 options_flag =>
2009 DBMS_REDEFINITION.CONS_USE_ROWID
2010 );
2011 END;
2012 /
After this both table should have near about same amount
10 rows selected. of record
SQL>select count(1) from T_INTRIM;
CREATE TABLE "T_INTRIM"
( "OWNER" VARCHAR2(30), COUNT(1)
"OBJECT_NAME" VARCHAR2(128), ----------
"SUBOBJECT_NAME" VARCHAR2(30), 61536
"OBJECT_ID" NUMBER,
"DATA_OBJECT_ID" NUMBER, SQL>select count(1) from T;
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE, COUNT(1)
"LAST_DDL_TIME" DATE, ----------
"TIMESTAMP" VARCHAR2(19), 61536
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1), SQL>
"GENERATED" VARCHAR2(1), If you have continuous inserts going on your original table
"SECONDARY" VARCHAR2(1), than you might have little more records in original table
"NAMESPACE" NUMBER, than intrim table.
"RION_NAME" VARCHAR2(30), 4) Sync new table on regular basis till cut off time
CONSTRAINT "T_PK_INTRIM_01" PRIMARY KEY BEGIN
("OBJECT_ID") DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
) uname => user,

Amol
ADBMS TYMCA Ravi
orig_table => 'T', 2007 1016
int_table => 'T_INTRIM' 2011 10474
); 2004 756
END; 2012 23474
/ 2006 3975
The way this sync works is, online redefinition will
automatically create a MLOG table on original table. 10 rows selected.
In any of the above step we didn‟t create any MLOG table Once we finish redefinition table T will become partition
on table T. table and T_INTRIM will become non-partition table.
But if you check now, you will see MLOG table created For this it needs exclusive lock.
automatically. 6) Complete redefinition process by using
SQL>select log_table from user_snapshot_logs where DBMS_REDEFINITION.FINISH_REDEF_TABLE procedure
master = 'T'; BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(user, 'T',
LOG_TABLE 'T_INTRIM');
------------------------------ END;
MLOG$_T /
This is required for syncing changed made to table T.
5) Copy dependent objects (such as triggers, indexes, PL/SQL procedure successfully completed.
grants, and constraints)
DECLARE SQL>select table_name, partitioned from user_tables where
num_errors PLS_INTEGER; table_name in ('T','T_INTRIM');
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS( TABLE_NAME PAR
uname => user, ------------------------------ ---
orig_table => 'T', T YES
int_table => 'T_INTRIM', T_INTRIM NO
copy_indexes =>
DBMS_REDEFINITION.CONS_ORIG_PARAMS, SQL>
copy_triggers => TRUE,
copy_constraints => TRUE, 26 b. What is a Server Parameter File?
copy_privileges => TRUE, hat Is a Server Parameter File?
ignore_errors => TRUE, A server parameter file is a binary file that acts as a
num_errors => num_errors); repository for initialization parameters. The server
END; parameter file can reside on the machine where the Oracle
/ database server executes. Initialization parameters stored in
a server parameter file are persistent, in that any changes
PL/SQL procedure successfully completed. made to the parameters while an instance is running can
Before we finish online redefinition you can check if table is persist across instance shutdown and startup.
partition and data is distributed to all partitions What Is an Initialization Parameter File?
SQL>select table_name, partitioned from user_tables where An initialization parameter file is a text file that contains a
table_name in ('T','T_INTRIM'); list of initialization parameters. The file should be written in
the client's default character set. Sample initialization
TABLE_NAME PAR parameter files are provided on the Oracle distribution
------------------------------ --- medium for each operating system. A sample file is
T NO sufficient for initial use, but you will probably want to modify
T_INTRIM YES the file to tune the database for best performance. Any
changes will take effect after you completely shut down and
SQL>select to_char(created,'YYYY'), count(1) from restart the instance.
T_INTRIM group by to_char(created,'YYYY');
27. Explain implicit objects in JSP
TO_C COUNT(1) JSP Implicit Objects are the Java objects that the JSP
Container makes available to developers in each page and
---- ----------
developer can call them directly without being explicitly
2003 7902 declared. JSP Implicit Objects are also called pre-defined
2005 1820 variables.
2009 2742 JSP supports nine Implicit Objects which are listed below:
2010 6765 Object Description
2008 2612

Amol
ADBMS TYMCA Ravi
This is the HttpServletRequest object Method Description
request
associated with the request.
out.print(dataType dt) Print a data type value
This is the HttpServletResponse object
response Print a data type value then
associated with the response to the client. out.println(dataType
terminate the line with new line
dt)
This is the PrintWriter object used to send character.
out
output to the client.
out.flush() Flush the stream.
This is the HttpSession object associated
session
with the request. The session Object:
The session object is an instance of
This is the ServletContext object javax.servlet.http.HttpSession and behaves exactly the
application
associated with application context. same way that session objects behave under Java Servlets.
The session object is used to track client session between
This is the ServletConfig object associated
config client requests. We would see complete usage of session
with the page.
object in coming chapter: JSP - Session Tracking.
This encapsulates use of server-specific
The application Object:
pageContext features like higher
The application object is direct wrapper around the
performance JspWriters.
ServletContext object for the generated Servlet and in
This is simply a synonym for this, and is reality an instance of a javax.servlet.ServletContext object.
page used to call the methods defined by the This object is a representation of the JSP page through its
translated servlet class. entire lifecycle. This object is created when the JSP page is
initialized and will be removed when the JSP page is
The Exception object allows the exception removed by the jspDestroy() method.
Exception
data to be accessed by designated JSP. By adding an attribute to application, you can ensure that all
JSP files that make up your web application have access to
The request Object: it.
The request object is an instance of a You can check a simple use of Application Object in
javax.servlet.http.HttpServletRequest object. Each time a chapter: JSP - Hits Counter
client requests a page the JSP engine creates a new object
The config Object:
to represent that request.
The config object is an instantiation of
The request object provides methods to get HTTP header
javax.servlet.ServletConfig and is a direct wrapper around
information including form data, cookies, HTTP methods etc.
the ServletConfig object for the generated servlet.
We would see complete set of methods associated with
This object allows the JSP programmer access to the Servlet
request object in coming chapter: JSP - Client Request.
or JSP engine initialization parameters such as the paths or
The response Object: file locations etc.
The response object is an instance of a The following config method is the only one you might ever
javax.servlet.http.HttpServletResponse object. Just as the use, and its usage is trivial:
server creates the request object, it also creates an object
to represent the response to the client. config.getServletName();
The response object also defines the interfaces that deal
with creating new HTTP headers. Through this object the This returns the servlet name, which is the string contained
JSP programmer can add new cookies or date stamps, HTTP in the <servlet-name> element defined in the WEB-
status codes etc. INF\web.xml file
We would see complete set of methods associated with The pageContext Object:
response object in coming chapter: JSP - Server Response. The pageContext object is an instance of a
The out Object: javax.servlet.jsp.PageContext object. The pageContext
The out implicit object is an instance of a object is used to represent the entire JSP page.
javax.servlet.jsp.JspWriter object and is used to send This object is intended as a means to access information
content in a response. about the page while avoiding most of the implementation
The initial JspWriter object is instantiated differently details.
depending on whether the page is buffered or not. Buffering This object stores references to the request and response
can be easily turned off by using the buffered='false' objects for each request. The application, config, session,
attribute of the page directive. and out objects are derived by accessing attributes of this
The JspWriter object contains most of the same methods as object.
the java.io.PrintWriter class. However, JspWriter has some The pageContext object also contains information about the
additional methods designed to deal with buffering. Unlike directives issued to the JSP page, including the buffering
the PrintWriter object, JspWriter throws IOExceptions. information, the errorPageURL, and page scope.
Following are the important methods which we would use to The PageContext class defines several fields, including
write boolean char, int, double, object, String etc. PAGE_SCOPE, REQUEST_SCOPE, SESSION_SCOPE, and
APPLICATION_SCOPE, which identify the four scopes. It also
supports more than 40 methods, about half of which are
inherited from the javax.servlet.jsp. JspContext class.

Amol
ADBMS TYMCA Ravi
One of the important methods is removeAttribute, which KEEP buffer cache 16777216
accepts either one or two arguments. For example, RECYCLE buffer cache 16777216
pageContext.removeAttribute ("attrName") removes the DEFAULT 2K buffer cache 16777216
attribute from all scopes, while the following code only DEFAULT 4K buffer cache 16777216
removes it from the page scope: DEFAULT 8K buffer cache 16777216
DEFAULT 16K buffer cache 16777216
DEFAULT 32K buffer cache 16777216
pageContext.removeAttribute("attrName", PAGE_SCOPE);
ASM Buffer Cache 16777216
You can check a very good usage of pageContext in coming
chapter: JSP - File Uploading. 13 rows selected .

The page Object: Here granule_size is taken as 16MB.

This object is an actual reference to the instance of the
page. It can be thought of as an object that represents the Adding Granules to Components
entire JSP page.
The page object is really a direct synonym for A database administrator grows a component's SGA use
the this object. with ALTER SYSTEM statements to modify the initialization
The exception Object: parameter values. Oracle takes the new size, rounds it up to
The exception object is a wrapper containing the exception the nearest multiple of 16MB, and adds or takes away
thrown from the previous page. It is typically used to granules to meet the target size. Oracle must have enough
generate an appropriate response to the error condition. free granules to satisfy the request. If the current amount of
We would see complete usage of this object in coming SGA memory is less than SGA_MAX_SIZE, then Oracle can
chapter: JSP - Exception Handling. allocate more granules until the SGA size
reaches SGA_MAX_SIZE.

The minimum SGA size is three granules, based on:

26 a. what is granules in SGA ?
1. One Granule for Fixed SGA (includes redo buffers)
With the dynamic SGA infrastructure, the size of the buffer
2. One Granule for Buffer Cache
cache, the shared pool, the large pool, and the process-
3. One Granule for the Shared Pool
private memory can be changed without shutting down the
4.
instance.With dynamic SGA, the unit of allocation is called
Following pools can be dynamically resized ,
a granule. Granules are introduced in oracle 9.0.1
. Components, such as the buffer cache, the shared pool,
the java pool, and the large pool, allocate and deallocate
space in units of granules . Oracle tracks SGA memory use
in integral numbers of granules, by SGA component. For
example,

If you specify a size for a component that is not a multiple

of granule size, then Oracle rounds the specified size up to
the nearest multiple. For example, if the granule size is 4
MB and you specify DB_CACHE_SIZE as 10 MB, you will
actually be allocated 12 MB.
Granule size is determined by total SGA size. On
most platforms, the size of a granule is 4 MB if the total SGA
size is less than 128 MB, and it is 16 MB for larger SGAs.
There may be some platform dependency, for example, on
32-bit Windows NT, the granule size is 8 MB for SGAs larger
than 128 MB. 27. Explain backup and recovery features.
The granule size that is currently being used for There are two ways to perform Oracle backup and recovery:
SGA can be viewed in the Recovery Manager and user-managed backup and recovery.
view V$SGA_DYNAMIC_COMPONENTS. The same granule Recovery Manager (RMAN) is an Oracle utility that can
size is used for all dynamic components in the SGA. We can back up, restore, and recover database files. It is a feature
also retrieve information from v$sgainfo from oracle 10.1 of the Oracle database server and does not require separate
onwards installation.
Whole Database Backups
SQL>select COMPONENT,GRANULE_SIZE from A whole database backup is a backup of every datafile in
V$SGA_DYNAMIC_COMPONENTS; the database, plus the control file. Whole database backups
are the most common type of backup.
COMPONENT GRANULE_SIZE Whole database backups can be taken in
------------------------------ ------------ either ARCHIVELOG or NOARCHIVELOG mode. Before
shared pool 16777216 performing whole database backups, however, be aware of
large pool 16777216 the implications of backing up
java pool 16777216 in ARCHIVELOG and NOARCHIVELOG modes.
streams pool 16777216 Tablespace Backups
DEFAULT buffer cache 16777216

Amol
ADBMS TYMCA Ravi
A tablespace backup is a backup of the datafiles that The Java expression is evaluated, converted to a string, and
constitute the tablespace. For example, if inserted in the page. This evaluation is performed at run-
tablespace users contains datafiles 2, 3, and 4, then a time (when the page is requested), and thus has full access
backup of tablespace users backs up these three datafiles.
to information about the request. For example, the following
Tablespace backups, whether online or offline, are valid only
if the database is operating in ARCHIVELOG mode. The shows the client host name and the date/time that the page
reason is that redo is required to make the restored was requested:
tablespace consistent with the other tablespaces in the <html>
database. ...
Datafile Backups <body>
A datafile backup is a backup of a single datafile. Datafile Your hostname : <%=request.getRemoteHost()%><br>
backups, which are not as common as tablespace backups,
Current time : <%= new java.util.Date() %>
are valid in ARCHIVELOG databases. The only time a datafile
backup is valid for a database in NOARCHIVELOG mode is if: ...
 Every datafile in a tablespace is backed up. You </body>
cannot restore the database unless all datafiles are backed </html>
up. Finally, note that XML authors can use an alternative syntax
 The datafiles are read only or offline-normal. for JSP expressions:
Introduction to Recovery <jsp:expression>
To restore a physical backup of a datafile or control file is to Java Expression
reconstruct it and make it available to the Oracle database </jsp:expression>
server. To recover a restored datafile is to update it by
Remember that XML elements, unlike HTML ones, are case
applying archived redo logs and online redo logs, that is,
records of changes made to the database after the backup sensitive. So be sure to use lowercase.
was taken. If you use RMAN, then you can also recover Scriptlet
datafiles with incremental backups, which are backups of a The scriptlet can contain any number of language
datafile that contain only blocks that changed after a statements, variable or method declarations, or expressions
previous incremental backup. that are valid in the page scripting language.
After the necessary files are restored, media recovery must
Within a scriptlet, you can do any of the following:
be initiated by the user. Media recovery involves various
operations to restore, roll forward, and roll back a backup of  Declare variables or methods to use later in the JSP
database files. page.
Media recovery applies archived redo logs and online redo  Write expressions valid in the page scripting
logs to recover the datafiles. Whenever a change is made to language.
a datafile, the change is first recorded in the online redo
logs. Media recovery selectively applies the changes  Use any of the implicit objects or any object
recorded in the online and archived redo logs to the restored declared with a <jsp:useBean> element.
datafile to roll it forward.  Write any other statement valid in the scripting
To correct problems caused by logical data corruptions or language used in the JSP page.
user errors, you can use Oracle Flashback. Oracle Flashback Any text, HTML tags, or JSP elements you write must be
Database and Oracle Flashback Table let you quickly recover
outside the scriptlet. For example,
to a previous time.
<HTML>
28. What are JSP scripting elements? <BODY>
JSP scripting elements enable you insert Java code into the <%
servlet that will be generated from the current JSP page. // This scriptlet declares and initializes "date"
There are three forms: java.util.Date date = new java.util.Date();
 Expressions of the form <%= expression%> that %>
are evaluated and inserted into output, Hello! The time is now
<%
 Scriptlets of the form <% code %> that are
out.println( date );
inserted into the servlets service method, and
out.println( "<BR>Your machine's address is " );
 Declarations of the form <%! code %> that are
out.println( request.getRemoteHost());
inserted into the body of the servlet class, outside of any
%>
existing methods.
</BODY>
Expression
</HTML>
The Expression element contains a Java expression that
Scriptlets are executed at request time, when the JSP
returns a value. This value is then written to the HTML page.
container processes the request. If the scriptlet produces
The Expression tag can contain any expression that is valid
output, the output is stored in the out object.
according to the Java Language Specification. This includes
If you want to use the characters "%>" inside a scriptlet,
variables, method calls than return values or any object that
enter "%\>" instead. Finally, note that the XML equivalent
contains a toString() method.
of <% Code %> is
Syntax
<%= Java expression %>

Amol
ADBMS TYMCA Ravi
<jsp:scriptlet> <jsp:declaration>
Code Code
</jsp:scriptlet> </jsp:declaration>
Declaration
A declaration can consist of either methods or variables, 29. What are implicit objects in JSP?
static constants are a good example of what to put in a Implicit objects are a set of Java objects that the JSP
declaration. Container makes available to developers in each page.
These objects may be accessed as built-in variables via
The JSP you write turns into a class definition. All the
scripting elements and can also be accessed
scriptlets you write are placed inside a single method of this programmatically by JavaBeans and Servlets. You may
class. You can also add variable and method declarations to already meet one of these objects already - the out object
this class. You can then use these variables and methods which allows you to send information to the output stream.
from your scriptlets and expressions. The implicit objects are created automatically for you within
You can use declarations to declare one or more variables the service method. Furthermore, as summarized below,
and methods at the class level of the compiled servlet. The each object must adhere to a specific Java class or interface
definition.
fact that they are declared at class level rather than in the
Object Class or Interface Description & Scope
body of the page is significant. The class members
Data included with the
(variables and methods) can then be used by Java code in http.HttpServletRequ
request HTTP Request.request
the rest of the page. est
scope.
Syntax HTTP Response data,
<%! declaration; [ declaration;]+...%> http.HttpServletRespo
response e.g. cookies. page
nse
When you write a declaration in a JSP page, remember scope.
these rules: Provides access to all the
 You must end the declaration with a semicolon (the namespaces associated
pageContex
same rule as for a Scriptlet, but the opposite of an jsp.pageContext with a JSP page and
t
access to several page
Expression).
attributes. page scope.
<% int i = 0;%>
User specific session
 You can already use variables or methods that are session http.HttpSession
data. session scope.
declared in packages imported by the page directive, Data shared by all
without declaring them in a declaration element. application
application ServletContext
 You can declare any number of variables or pages.application
scope.
methods within one declaration element, as long as you end
Output stream for page
each declaration with a semicolon. The declaration must be out jsp.JspWriter
context. page scope.
valid in the Java programming language.
Servlet configuration
<% int i = 0; long l = 5L; %> config ServletConfig information. page
For example, scope.
<%@ page import="java.util.*" %> Page's servlet
page jsp.HttpJspPage
<HTML> instance. page scope.
<BODY> Note that the implicit variables are only available within
<%! the jspService method and thus are not available within any
Date getDate() declarations. Thus for example the following code will cause
{ a compile time error.
<%!
System.out.println( "In getDate() method" );
public void amethod(){
return new Date(); out.print("Hello");
} }
%> %>
Hello! The time is now <%= getDate() %>
</BODY> 31. Write down the steps for connecting oracle
</HTML> with JSP using JDBC.
A declaration has translation unit scope, so it is valid in the
Java Database Connectivity (JDBC) is a Java-based data
JSP page and any of its static include files. A static include
access technology that defines how a client may access a
file becomes part of the source of the JSP page and is any database. It provides methods for querying and updating
file included with an include directive or a static resouce data in a database. The JDBC classes are contained in the
included with a <jsp:include> element. The scope of a Java package java.sql and javax.sql.
declaration does not include dynamic resources included In this JDBC Oracle connectivity example we will see how to
with <jsp:include>. setup a JDBC development environment and create a simple
As with scriptlets, if you want to use the characters "%>", Java database application to connect to Oracle Database
Express Edition using JDBC API. We will also see the
enter "%\>" instead. Finally, note that the XML equivalent
of <%! Code %> is
Amol
ADBMS TYMCA Ravi
following important things which are required for connecting SQL> commit;
to any database using JDBC. SQL> exit;
1. Oracle JDBC Connector jar
2. JDBC Oracle Driver class
3. JDBC Oracle Connection String URL

Oracle XE Installation Steps

The Oracle XE installation is very simple. Just unzip the file
and run the setup.exe file. Follow the step-by-step
installation guide from this link
Setup a Database
Create Database User
To create database objects, we must create at least one
database user. A user is associated with a database schema,
you connect to the database as a database user, and the
database user is the owner of any database objects (tables,
views etc) that you create in the schema associated with the
user. Now the database and table setup is done. Let us connect to
For example, to create a database user named „testuser‟. this database table and retrieve the data using JDBC API.
Follow these steps, using the command line: Java Oracle Connector
1. Open the SQL command prompt window. For example, on JDBC API mostly consists of interfaces which work
Windows, click Start, then Programs (or All Programs), then independently of any database. A database specific driver is
Oracle Database Express Edition, and then “Run SQL required for each database which implements the JDBC API.
Command Line”. The JDBC database Connector provides access to the
2. Connect as the SYSTEM user: database. To reach the database using JDBC we need a
Type: JDBC driver from the database provider in our case –
connect Oracle. This connector is typically delivered with the product
Enter user-name: system in a jar or zip file or available in the provider‟s website.
Enter password: These files must be in our classpath (which is explained
The password is the one you entered during installation. later under Configure JDBC Driver in Eclipse) otherwise we
3. Create the user. For example, enter a statement in the will get some class-not-found-exceptions indicating that the
following form: driver was not found on the classpath.
SQL> create user testuser identified by <password-for-
testuser>;
4. Grant the user the necessary privileges. For example:
SQL> grant connect, resource to testuser;
5. exit

Create Table
Now let us login to the database with the newly created user
„testuser‟ and create a simple „Person‟ table. Oracle provides the JDBC connector jar with the product and
1. Open SQL Command Line. is available in the following location in Windows,
2. Type “connect” C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib
3. Enter username as “testuser” (if you followed the default installation procedure)
4. Enter password as “testpass” (or the password you There will be multiple jars like,
entered in the previous step while creating the user) ojdbc5.jar – Classes for use with JDK 1.5.
5. Create a person table. ojdbc6.jar – Classes for use with JDK 1.6.
SQL> create table person(pid integer primary key, name We will be using “ojdbc6.jar”
varchar2(50)); Create a Java project in Eclipse IDE
6. Enter a few data into the table.  Open Eclipse IDE.
SQL> insert into person values(1, „Ram‟);  Create a new Java Project and name it
SQL> insert into person values(2, „Sam‟); as JDBCOracle. If you are a newbie, refer this link on
SQL> insert into person values(3, „John‟); getting started with Java and Eclipse.
SQL> select * from person; JDBC Oracle Connectivity

Amol
ADBMS TYMCA Ravi
In order to establish a connection to the database using 15 Class.forName("oracle.jdbc.driver.OracleDriver");
JDBC we need to perform the following steps, 16 con = DriverManager.getConnection("jdbc:oracle:thin:tes
1. Import the required interfaces/classes from java.sql 17 stmt = con.createStatement();
package. 18 rs = stmt.executeQuery("SELECT * FROM person");
2. Load the JDBC Oracle Driver class 19 while(rs.next()) {
3. Establish the connection by providing the jdbc 20 System.out.print(rs.getInt(1) + "\t");
oracle connection string url 21 System.out.println(rs.getString(2));
Load Oracle Java driver 22 }
We need to know and specify which of the classes in the 23 } catch (ClassNotFoundException e) {
connector jar implements the JDBC driver so as to load the 24 e.printStackTrace();
class in memory. For Oracle the 25 } catch (SQLException e) {
class oracle.jdbc.driver.OracleDriver is the jdbc driver 26 e.printStackTrace();
class. The statement 27 } finally {
Class.forName (“oracle.jdbc.driver.OracleDriver”) 28 try {
loads the driver class in memory. 29 rs.close();
JDBC Oracle Connection URL 30 stmt.close();
We connect to Oracle database from Java using 31 con.close();
DriverManager class by 32 } catch (SQLException e) {
callingDriverManager.getConnection() method. This 33 e.printStackTrace();
method requires JDBC Oracle connection URL string, Oracle 34 }
database username and password. 35 }
The Java database connection string URL is of the following 36 }
format: 37 }
jdbc:oracle::[username/password]@[//]host_name[:port][/ Configure JDBC driver in Eclipse IDE
XE] If you run the above class you will get a runtime exception
In this URL: mentioning Driver class not found as shown below
// is optional. java.lang.ClassNotFoundException:
:port is optional.Specify this only if the default Oracle Net oracle.jdbc.driver.OracleDriver
listener port (1521) is not used. Because we need to add the Java Oracle Connector JAR in
/XE, or the service name, is not required. project‟s classpath. To do this, right click on your Java
The connection adapter for the Oracle Database XE Client Project -> Properties -> Buildpath -> Libraries -> Add
connects to the default service on the host. External JAR and select the odbc6.jar file.
Default service is a new feature of Oracle Database XE. If
you use any other Oracle Database client to connect to
Oracle Database XE, then you must specify the service
name.
For example, if you connect to a local database using default
port number, then the Oracle database connection URL is:
jdbc:oracle:thin:testuser/testpass@localhost
Java Application code
An application involving Java with database to process any
SQL statement must follow these steps:
1. Establish a connection. (This is done by
DriverManager class)
2. Create a Statement object. (Line 17)
3. Execute the query. (Line 18)
4. Process the ResultSet object. This is required only
for SELECT SQL query. (Line 19-22)
5. Close the connection. (Line 29-31)
To do the above steps create a
package “com.theopentutorials.jdb.oracle”. Then create
a class“TestOracleJDBC” with main method and copy the
Output
following code.
Run the above program to get the following output.
01 package com.theopentutorials.jdb.oracle;
02
03 import java.sql.Connection;
04 import java.sql.DriverManager;
05 import java.sql.ResultSet;
06 import java.sql.SQLException;
07 import java.sql.Statement;
08
09 public class TestOracleJDBC {
10 public static void main(String[] args) {
11 Connection con = null; JDBC Oracle Application folder structure
12 Statement stmt = null;
13 ResultSet rs = null;
14 try {
Amol
ADBMS TYMCA Ravi
java.util.Date date = new java.util.Date();
%>
Hello! The time is now
<%
// This scriptlet generates HTML output
out.println( String.valueOf( date ));
%>
</BODY>
</HTML>
Here, instead of using an expression, we are generating
32.Explain scriptlets and JSP expressions with the HTML directly by printing to the "out" variable. The
example. "out" variable is of type javax.servlet.jsp.JspWriter .
Another very useful pre-defined variable is "request". It is
Scriptlets
of type javax.servlet.http.HttpServletRequest
We have already seen how to embed Java expressions in A "request" in server-side processing refers to the
JSP pages by putting them between transaction between a browser and the server. When
the <%= and %> character sequences. someone clicks or enters a URL, the browser sends a
But it is difficult to do much programming just by putting "request" to the server for that URL, and shows the data
Java expressions inside HTML. returned. As a part of this "request", various data is
JSP also allows you to write blocks of Java code inside the available, including the file the browser wants from the
JSP. You do this by placing your Java code server, and if the request is coming from pressing a
between <% and %> characters (just like expressions, SUBMIT button, the information the user has entered in
but without the = sign at the start of the sequence.) the form fields.
This block of code is known as a "scriptlet". By itself, a The JSP "request" variable is used to obtain information
scriptlet doesn't contribute any HTML (though it can, as from the request as sent by the browser. For instance,
we will see down below.) A scriptlet contains Java code you can find out the name of the client's host (if available,
that is executed every time the JSP is invoked. otherwise the IP address will be returned.) Let us modify
Here is a modified version of our JSP from previous the code as shown:
section, adding in a scriptlet. <HTML>
<HTML> <BODY>
<BODY> <%
<% // This scriptlet declares and initializes "date"
// This is a scriptlet. Notice that the "date" System.out.println( "Evaluating date now" );
// variable we declare here is available in the java.util.Date date = new java.util.Date();
// embedded expression later on. %>
System.out.println( "Evaluating date now" ); Hello! The time is now
java.util.Date date = new java.util.Date(); <%
%> out.println( date );
Hello! The time is now <%= date %> out.println( "<BR>Your machine's address is " );
</BODY> out.println( request.getRemoteHost());
</HTML> %>
If you run the above example, you will notice the output </BODY>
from the "System.out.println" on the server log. This is a </HTML>
convenient way to do simple debugging (some servers A similar variable is "response". This can be used to affect
also have techniques of debugging the JSP in the the response being sent to the browser. For instance, you
IDE. See your server's documentation to see if it offers can call response.sendRedirect( anotherUrl ); to send a
such a technique.) response to the browser that it should load a different
By itself a scriptlet does not generate HTML. If a scriptlet URL. This response will actualy go all the way to the
wants to generate HTML, it can use a variable called browser. The browser will then send a different request,
"out". This variable does not need to be declared. It is to "anotherUrl". This is a little different from some other
already predefined for scriptlets, along with some other JSP mechanisms we will come across, for including
variables. The following example shows how the scriptlet another page or forwarding the browser to another page.
can generate HTML output. Exercise: Write a JSP to output the entire line,
<HTML> "Hello! The time is now ..." but use a scriptlet for the
<BODY> complete string, including the HTML tags.
<%
// This scriptlet declares and initializes "date"
System.out.println( "Evaluating date now" );

Amol