Tuesday, October 5, 2010

Logical & Physical Architecture of Database

What is Database Buffers ?
Database buffers are cache in the SGA used to hold the data blocks that are read from the data segments in the database such as tables, indexes and clusters. DB_BLOCK_BUFFERS parameter in INIT.ORA decides the size.

 What is dictionary cache ?
Dictionary cache is information about the database objects stored in a data dictionary table.

What is meant by recursive hits ?

Number of times processes repeatedly query the dictionary table is called recursive hits. It is due to the data dictionary cache is too small. By increasing the SHARED_POOL_SIZE parameter we can optimize the size of Data Dictionary Cache.

What is meant by redo log buffer ?
Changes made to entries are written to the on-line redo log files so that they can be used in roll forward operation during database recoveries. Before writing them into the redo log files, they will first brought to redo log buffers in SGA and LGWR will write into files frequently. LOG_BUFFER parameter will decide the size.

 How will you swap objects into a different table space for an existing database ?
Export the user
Perform import using the command imp system/manager file=export.dp indexfile=newfile.sql. This will create all definitions into newfile.sql.
Drop necessary objects.

Run the script newfile.sql after altering the tablespaces.
Import from the backup for the necessary objects.

List the Optimal Flexible Architecture (OFA) of Oracle database ? or
How can we organise the tablespaces in Oracle database to have maximum performance ?

SYSTEM - Data dictionary tables
DATA - Standard operational tables
DATA2 - Static tables used for standard operations
INDEXES - Indexes for Standard operational tables
INDEXES1 - Indexes of static tables used for standard operations
TOOLS - Tool table
TOOLS1 - Indexes for tools table
RBS - Standard Operations Rollback Segments
RBS1,RBS2 - Additional/Special rollback segments
TEMP - Temporary purpose tablespace
TEMP_USER - Temporary tablespace for users
USERS - User tablespaces.

How will you force database to use particular rollback segment ?
SET TRANSACTION USE ROLLBACK SEGMENT rbs_name

What is meant by free extent ?
A free extent is a collection of continuous free blocks in tablespace. When a segment is dropped its extents are reallocated and are marked as free.

How free extents are managed in Ver 6 and Ver 7. ?
Free extents cannot be merged together in Ver 6.0
Free extents are periodically coalesces with the neighboring free extent Ver 7.0.

Which parameter in Storage clause will reduce no of rows per block ?
PCTFREE parameter
Row size also reduces no of rows per block.

What is significance of having storage clause ?
We can plan the storage for a table as how much initial extents are required, how much can be extended next, how much % should leave free for managing row updations etc.

How does space allocation take place within a block ?

Each block contains entries as follows :
Fixed block header
Variable block header

Row header, row date (Multiple rows may exists)
PCTFREE (% of free space for row updation in future)

What is the role of PCTFREE parameter is Storage clause ?
This is used to reserve certain amount of space in a block for expansion of rows.

What is the OPTIMAL parameter ?

It is used to set the optimal length of rollback segment.

What is the functionality of SYSTEM tablespace ?
To manage the database level of transactions such as modifications of the data dictionary table that record information about the free space usage.

How will you create multiple rollback segments in a database ?

Create a database which implicitly creates a SYSTEM Rollback Segment in a SYSTEM tablespace.
Create a Second Rollback Segment name R0 in the SYSTEM tablespace.
Make new rollback segment available (After shutdown, modify init.ora file and Start database)
Create other tablespace (RBS) for rollback segments.
Create additional Rollback segment in tablespace (RBS)
Deactivate Rollback Segment R0 and activate the newly created rollback segments.

How the space utilisation takes place within rollback segments ?
It will try to fit the transaction in a cyclic fashion to all existing extents. Once it found an extent is in use then it forced to acquire a new extent. (No of extents is based on the OPTIMAL size).

Sunday, October 3, 2010

Importent Oracle & Sql Query

1) How delete duplicate records in a table
delete from emp where rowid not in (select max(rowid) from emp group by empno);

2) How to make a column into not null column.
Ans: alter table emp
modify (empno not null); ->this can be done only when all the values in empno are non null (i.e not empty)

?h To make a not null column into null column
alter table emp
modify (empno null);
The fallowing are the rules for

i. adding null or not null property
>You may change a column??s null property to not null only when that field does contain null values(i. It can??t be empty)
>At any time you may change a column??s not null property to null

ii. adding a colum to a table
>You may add a column at any time if NOT NULL isn??t specified (i.e when new column can accept null values )
>You may add a NOT NULL column in three steps.
a. Add the column without NOT NULL specified.
b. Fill every row in that column with data.
c. Modify the column to be NOT NULL.

iii. modifying a column
>You can increase a CHAR column??s width at any time.
>You can increase the number of digits and the number of decimal places in a NUMBER column at any time
>To CHANGE data types or to DECREASE column??s width the column should be null for every row

3)Write queries for the fallowing

ex. select * from emp_self;

EMP_NO EMPN_NAME SAL MGR DEPTID
--------- -------------------- ---------- ---------- ----------
400 jane 20000 110 20
102 Mary 19000 110 20
101 charles 8000 105 50
104 Linda 9000 100 10
110 john 25000 105 20
105 newton 2000 50
100 ALEN 15000 105 50
200 BORIS 3000 110 20
103 DAVID 10000 100 10
300 monica 7000 105 50
i) Query to get the employees who are working under mgr with salary > 10000
select emp_no,mgr from emp_self where mgr in (select emp_no from emp_self where sal > 10000);

ii) Query to get the employees who are getting salaries more than their managers
select a.emp_no from emp_self a,emp_self b where a.mgr =b.emp_no and a.sal > b.sal;
EMP_NO
-------
101
100
110
300
iii) Query to find the nth highest salary
select a.empn_name,a.sal from emp_self a where &n = (select count(*) from emp_self b where a.sal < b.sal);
When n=1 , sal =20000 ->second highest salary ; n=4 , sal =10000 ->fifth highest salary ;
iv) Query to find the second highest salary in different departments.
select a.deptid,min(a. sal) from emp_self a where 1 in (select count(*) from emp_self b
where a.sal < b.sal group by b.deptid) group by a.deptid;

DEPTID MIN(A.SAL)
------- ----------
10 9000
20 20000
50 8000
v) Query to find departments with total salary >25000

select deptid from emp_self having sum(sal) >25000 group by deptid;

DEPTID
------
20
50

4) study the fallowing pl/sql block and find the type of error ->syntax,semantic(logical) or precedence
begin
for i in 1..5 loop
update emp
set sal = 1000 where empno =100 ;
end loop;
end;


5) Difference between (MAX,MIN) and (GREATEST ,LEAST)
The functions max and min compares different rows . Whereas greatest and least work on a group of columns ,either actual or calculated values within a single row.

Ex. select * from emp_self;

EMP_NO EMPN_NAME SAL MGR
------ -------------------- ------- ----------
100 alen 9,000 105
200 boris 10,000 110
101 charles 8,000 105

SQL> select max(sal),min(sal) from emp_self;

MAX(SAL) MIN(SAL)
---------- ----------
10000 8000

SQL> select greatest(sal),least(sal) from emp_self;

GREATEST(SAL) LEAST(SAL)
------------- ----------
9000 9000
10000 10000
8000 8000

Introduction to DBA

1. What is a database instance ? Explain
A database instance (server) is a set of memory structures and background processes that access a set of database files.

The process can be shared by all users.

The memory structures that are used to store most queried data from database. This helps us to improve database performance by decreasing the amount of I/O performed against data file.

2. What is parallel server?
Multiple instances accessing the same database (Only in Multi-CPU environments).

3. What is Schema ?
The set of objects owned by user account is called the schema

4. What is an Index ? How it is implemented in Oracle Database ?
An index is a database structure used by the server to have direct access of a row in a table.
An index is automatically created when a unique or primary key constraint clause is specified in create table command (Ver 7.0)

5. What is clustres ?

Group of tables physically stored together because they share common columns and are often used together is called Clusters.

6. What is a cluster key ?

The related columns of the tables are called the cluster key. The cluster key is indexed using a cluster index and its value is stores only once for multiple tables in the cluster.

7. What are the basic element of Base configuration of an oracle Database ?

It consists of
one or more data files
one or more control files
two or more redo log files

The database contains
Multiple users/schemas
one or more rollback segments
one or more tablespaces
Data dictionary tables

User objects (tables,indexes,views etc)

The server that access the database consists of
SGA (Database buffer, Dictionary Cache Buffers, redo log buffers,Shared SQL pool)
SMON
PMON
LGWR
DBWR
ARCH
CKPT
RECO
Dispatcher
User process with associated PGA

8. What is deadlock ? Explain.

Two processes waiting to update the rows of a table which are locked by the other process then deadlock arises.

In a database environment this will often happen because of not issuing proper row lock commands. Poor design of front-end application may cause this situation and the performance of server will reduce drastically.

These locks will be released automatically when a commit/rollback operation performed or any one of this processes being killed externally.