Question : What are synonyms used for

Synonyms are used to : Mask the real name and owner of an object.
Provide public access to an object
Provide location transparency for tables,views or program units of a remote database.
Simplify the SQL statements for database users.



Question : What is an Oracle index



An Index is a tree structure that allows direct access to a row in a table. Indexes can be classified based on their logical design or on their physical implementation.

The Logical classification groups indexes from an application perspective, while the physical classification is derived from the way the indexes are stored



An index is a schema object that can speed up the retrieval of rows by using pointers. If you do not have an index, then a full table scan occurs. Its purpose is to reduce disk I/O by using an indexed path to locate data quickly. If a table is dropped, the corresponding indexes are also dropped



 Question : How are the index updates

Indexes can be updated by either Dropping and recreating them, or Rebuilding them online



Question : What are clusters

A Cluster is made up of a table, or group of tables that share the same data blocks, which are grouped togather because they share common columns and are often used together



Question : What is cluster key

A cluster index is needed in support of a cluster. One is used to allow the other to exist. Much like we need an index in support of a primary key. They are not the same things.

Clusters are useful in the database to store related pieces of information from more then 1 table in the same physical database block. It in effect stores data "prejoined". I can use this technique to store all of the data from the DEPT
and EMP table for a given DEPTNO on the same block so that all employees of department 10 as well as the department 10 master record are all physically stored on the same exact block. When I go to "join" this data -- it is already
done for me -- in a very few IOs I can get all of the data I need. Using conventional tables, this data could be scattered onto many dozens of blocks.

read the oracle documentation, it gives examples of creating a cluster, the cluster key index, and the tables in the cluster.

Also, with a hash cluster there is no index by definition so no syntax to inspect. We hash the cluster key to determine where the data goes -- we do not index it. the data is the index.


Clusters are useful when you want data with the same cluster key values to be
physically stored near eachother.



Question : What is database link

A database link is a pointer in the local database that allows you to access on a remote database.





Question : What are the types of database links

Oracle allows you to create private, public, and global database links.


Private Database Link: You can create a private database link in a specific schema of a database. Only the owner of a private database link or PL/SQL subprograms in the schema can use a private database link to access data and database objects in the corresponding remote database.

Public Database Link : You can create a public database link for a database. All users and PL/SQL subprograms in the database can use a public database link to access data and database objects in the corresponding remote database.

Global Database Link - When an Oracle network uses Oracle Names, the names servers in the system automatically create and manage global database links for every Oracle database in the network. All users and PL/SQL subprograms in any database can use a global database link to access data and database objects in the corresponding remote database.

A private database link is more secure than a public or global link, because only the owner of the private link, or subprograms within the same schema, can use the private link to access the specified remote database.

When many users require an access path to a remote Oracle database, an administrator can create a single public database link for all users in a database.

When an Oracle network uses Oracle Names, an administrator can conveniently manage global database links for all databases in the system. Database link management is centralized and simple.



Question : What is data block



Block is the smallest unit of storage in the
logical structure of the database where
actual table rows are stored.


Question : What is row chaining

if any of he empty row is not sufficient to hold the row. then row is placed in multiple blocks. it happenes when the block size is small and rows are of large size. then it cause chaining. Due to chaining performance degrades and will cause more IOs


Question : What is a data segment

A segment is a set of extents that contains all the data for a specific logical storage structure within a tablespace. For example, for each table, Oracle allocates one or more extents to form that table's data segment, and for each index, Oracle allocates one or more extents to form its index segment.

Oracle databases use four types of segments, which are described in the following sections:

A single data segment in an Oracle database holds all of the data for one of the following:

A table that is not partitioned or clustered
A partition of a partitioned table
A cluster of tables



Question : What is an index segment

Oracle creates the index segment for an index or an index partition when you issue the CREATE INDEX statement. In this statement, you can specify storage parameters for the extents of the index segment and a tablespace in which to create the index segment. (The segments of a table and an index associated with it do not have to occupy the same tablespace.) Setting the storage parameters directly affects the efficiency of data retrieval and storage



Question : What is rollback segment

ROLLBACK INFORMATION NOTES:


Segment Name - Name of the rollback segment.
Owner - Owner of the rollback segment.
Tablespace - Name of the tablespace containing the rollback segment.
Segment ID - ID number of the rollback segment.
File ID - ID number of the block containing the segment header.
Block ID - Starting block number of the extent.
Initial Extent - Initial extent size in bytes.
Next Extent - Secondary extent size in bytes.
Min Extents - Minimum number of extents.
Max Extents - Maximum number of extents.
PCT Increase - Percent increase for extent size.
Status - ONLINE if the segment is online, or PENDING OFFLINE if the segment is going offline but some active (distributed) transactions are using the rollback segment. When the transaction(s) complete, the segment goes OFFLINE.
Instance - Instance this rollback segment belongs to (Parallel Server), or NULL for a single-instance system .

select SEGMENT_NAME,
OWNER,
TABLESPACE_NAME,
SEGMENT_ID,
FILE_ID,
BLOCK_ID,
INITIAL_EXTENT,
NEXT_EXTENT,
MIN_EXTENTS,
MAX_EXTENTS,
PCT_INCREASE,
STATUS,
INSTANCE_NUM
from dba_rollback_segs
order by SEGMENT_NAME



Question : What is the use of control file

Control file is binary file which is having all the information realted to database. db_name, maxlogfiles, maxdatafiles, tablespaces information. Without this u cannot open your database. Init.ora parameter file showing the location of the controlfile.



Question : What is a database instance and Explain

A instance is basically a set of memory and some background processes



Question : What is a deadlock and Explain

Answer : A deadlock is a condition where two or more users are waiting for data locked by each other. Oracle automatically detects a deadlock and resolves them by rolling back one of the statements involved in the deadlock, thus releasing one set of data locked by that statement. Statement rolled back is usually the one which detects the deadlock. Deadlocks are mostly caused by explicit locking because oracle does not do lock escalation and does not use read locks. Multitable deadlocks can be avoided by locking the tables in same order in all the applications, thus precluding a deadlock



What is a Database instance ? Explain

A database instance (Server) is a set of memory structure and background processes that access a set of database files. The process can be shared by all users. The memory structure that are used to store most queried data from database. This helps up to improve database performance by decreasing the amount of I/O performed against data file.



What is Parallel Server ?

Multiple   instances   accessing  the  same  database  (Only  In  Multi-CPU environments)



What is clusters ?

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



Question : How do you rename a database?



To change the name of the database
-- For this script to run properly do the following:

-- Backup the Control fiel to Trace
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

-- Shutdown the database to make the changes
SHUTDOWN IMMEDIATE;

-- Edit the trace file and change the CREATE CONTROLFILE command
-- CREATE CONTROLFILE REUSE SET DATABASE "NEW_SID_NAME" RESETLOGS
-- (note the SET keyword)

Change the name in Control file and Init.ora
The first line of Control file should be "CREATE CONTROLFILE REUSE SET DATABASE "" RESETLOGS ARCHIVELOG"

- modify the db_name parameter in the init.ora

-- Startup the datbase nomount with changed PFile
STARTUP NOMOUNT;

-- Execute the create controlfile command.
@create_control.sql;

-- Cancel base recovery of the database
Recover database USING BACKUP CONTROLFILE until cancel;
CANCEL

-- Open resetlogs the database
ALTER DATABASE OPEN RESETLOGS;

-- Rename GLOBAL_NAME to
ALTER DATABASE RENAME GLOBAL_NAME TO ;

-- Create SPFile, IF required give NAME and PATH of the PFILE
CREATE SPFILE FROM PFILE;

select name from v$database;



Question : How do you pin an object

Use dbms_shared_pool procedure.
EXECUTE DBMS_SHARED_POOL.KEEP(OBJECTNAME);



Question : What are memory structures in Oracle?

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



Question : What does database do during mounting process?

oracle process are created 1 LGWR 2 DBWR 3 CKPT 4 LGWR 5 SMON 6 PMON etc.. control file is readed



Question : What is the correct sequence among FETCH, EXECUTE, And PARSE

the correct sequence is PARSE, EXECUTE and then FETCH.





Question : Explain Oracle memory structure.

The Oracle RDBMS creates and uses storage on the computer hard disk and in random access memory (RAM). The portion in the computer’s RAM is called memory structure. Oracle has two memory structures in the computer’s RAM. The two structures are the Program Global Area (PGA) and the System Global Area (SGA).

The PGA contains data and control information for a single, user process. The SGA is the memory segment that stores data that the user has retrieved from the database or data that the user wants to place into the database



Question : How can you check which user has which Role.

desc dba_tab_privs



Question : How to DROP an Oracle Database?

Answer : You can do it at the OS level by deleting all the files of the database. The files to be deleted can be found using:

1) select * from dba_data_files;
2) select * from v$logfile;
3) select * from v$controlfile;
4) archive log list
5) initSID.ora
6) In addition you can clean the UDUMP, BDUMP, scripts etc

Clean up the listener.ora and the tnsnames.ora. make sure that the oratab entry is also removed.



Question : What is the view name where i can get the space in MB for tables or views?

Answer : select segment_name,sum(bytes) from dba_segments where segment_name='TABLE_NAME' AND OWNER='OWNER of the table' group by segment_name



Question : What is difference between Logical Standby Database and Physical Standby database?

Answer : The primary functional difference between logical and physical standby database setups is that logical standby permits you to add additional objects (tables, indexes, etc) to the database, while physical standby is always an exact structural duplicate of the master database. The downside, though, is that logical standby is based on newer technologies (logical standby is new in Oracle 9.2) and tends to be generally regarded as more tempramental than physical standby.



Question : What is a latch?

Latches are low level serialization mechanisms used to protect shared data structures in the SGA. The implementation of latches is operating system dependent, particularly in regard to whether a process will wait for a latch and for how long.

A latch is a type of a lock that can be very quickly acquired and freed. Latches are typically used to prevent more than one process from executing the same piece of code at a given time. Associated with each latch is a cleanup procedure that will be called if a process dies
while holding the latch. Latches have an associated level that is used to prevent deadlocks. Once a process acquires a latch at a certain level it cannot subsequently acquire a latch at a level that is equal to or less than that level (unless it acquires it nowait).

Question : Latches vs Enqueues



Enqueues are another type of locking mechanism used in Oracle. An enqueue is a more sophisticated mechanism which permits several concurrent processes to have varying degree of sharing of "known" resources. Any object which can be concurrently used, can be protected with enqueues. A good example is of locks on tables. We allow varying levels of sharing on tables e.g. two processes can lock a table in share mode or in share update mode
etc.

One difference is that the enqueue is obtained using an OS specific locking mechanism. An enqueue allows the user to store a value in the lock, i.e the mode in which we are requesting it. The OS lock manager keeps track of the resources locked. If a process cannot be granted the lock because it is incompatible with the mode requested and the lock is requested
with wait, the OS puts the requesting process on a wait queue which is serviced in FIFO.
Another difference between latches and enqueues is that in latches there is no ordered queue of waiters like in enqueues.

Latch waiters may either use timers to wakeup and retry or spin (only in multiprocessors). Since all waiters are concurrently retrying (depending on the scheduler), anyone might get the latch and conceivably the first one to try might be the last one to get.

Question : How Materialized Views Work with Object Types and Collections

Answer : This is extract from Oracle10g Advanced Replication Book.

How Materialized Views Work with Object Types and Collections

Oracle object types are user-defined datatypes that make it possible to model complex real-world entities such as customers and orders as single entities, called objects, in the database. You create object types using the CREATE TYPE ... AS OBJECT statement. You can replicate object types and objects between master sites and materialized view sites in a replication environment.

An Oracle object that occupies a single column in a table is called a column object. Typically, tables that contain column objects also contain other columns, which may be built-in datatypes, such as VARCHAR2 and NUMBER. An object table is a special kind of table in which each row represents an object. Each row in an object table is a row object.

You can also replicate collections. Collections are user-defined datatypes that are based on VARRAY and nested table datatypes. You create varrays with the CREATE TYPE ... AS VARRAY statement, and you create nested tables with the CREATE TYPE ... AS TABLE statement.

Question : How do you find wheather the instance was started with pfile or spfile

Answer : Thhere are 3 different ways :-

1) SELECT name, value FROM v$parameter WHERE name = 'spfile'; //This query will return NULL if you are using PFILE

2) SHOW PARAMETER spfile // This query will returns NULL in the value column if you are using pfile and not spfile

3) SELECT COUNT(*) FROM v$spparameter WHERE value IS NOT NULL; // if the count is non-zero then the instance is using a spfile, and if the count is zero then it is using a pfile:
By Default oracle will look into the default location depends on the o/s. Like in unix, oracle will check in $oracle_home/dbs directory and on windows it will check in oracle_home/database directory, and the content of pfile is just text based, but spfile content is in binary format, that is understandable by oracle very well.
Also oracle server always check the spfile or pfile with these sequence :-

SPFILE.ORA
SPFILE.ORA
PFILE.ORA
PFILE.ORA



Question : What are the Advantages of Using DBCA

These are a few of the advantages of using DBCA:You can use its wizards to guide you through a selection of options providing an easy means of creating and tailoring your database. It allows you to provide varying levels of detail. You can provide a minimum of input and allow Oracle to make decisions for you, eliminating the need to spend time deciding how best to set parameters or structure the database. Optionally, it allows you to be very specific about parameter settings and file allocations.It builds efficient and effective databases that take advantage of Oracle's new features.It uses Optimal Flexible Architecture (OFA), whereby database files and administrative files, including initialization files, follow standard naming and placement practicesTop of Form



Bottom of Form



Question : What is a cursor? Why do you need them? What are the different kinds of cursor?

oracle uses private sql area to execute sql queries and store the information.pl/sql uses cursors to name these private sql area and access its stored information
there r two types of cursors
implicit
explicit
implicit cursors are used for all dml and single row queries.these are system defined
explicit cursors are used for queries which return multiple row .these are user defined.

Question : What is SGA

SGA - Stands for System Global Area, its a part of Memory structure! SGA consists of

Shared Pool
Database Buffer Cache
Redo Log Buffer
Lock & Latch Management

& More Aditional Memory Structures

Large pool & Java Pool

another name of SGA is Shared Global Area. It is used to store database information that is shared by database processes. It contains Data and control information for the Oracle Server and is allocated in the Virtual Memory of the computer - where Oracle Resides

Question : What is a shared pool

The Shared Pool environment contains both fixed and variable structures. The Fixed structures remain relatively the same size, whereas the variable structures grow and shrink based on user and program requirements.

Used To Store
Most Recently Executed SQL Statements
Most Recently used Data definitions
It Consists of two Key performance - related memory structures
Library Cache & Data Dictionary Cache

Shared Pool is sized by SHARED_POOL_SIZE

Question : What is mean by Program Global Area (PGA)

PGA - Program Global Area

or the Process Global Area is a memory region that contains data and control information for a single server process or a single background process.

The PGA is allocated when a process is created and deallocated when the process is terminated. PGA is an area that is used by only one process.

Question : What is redo log buffer

it is a memory location where the temporary changes are stored in a buffer

Question : How can you see the source code of the package

select text from dba_source where type='PACKAGE' and name=' ';

What does COMMIT do ?

COMMIT makes permanent the changes resulting from all SQL statements in the transaction. The changes made by the SQL statements of a transaction become visible   to  other  user  sessions  transactions  that  start  only  after transaction is committed.



What is the function of Optimizer ?

The  goal of the optimizer is to choose the most efficient way to execute a SQL statement



What is Execution Plan ?

The  combinations of the steps the optimizer chooses to execute a statement is called an execution plan.



What is RULE-based approach to optimization ?

Choosing an executing planbased on the access paths available and the ranks of these access paths.



What is COST-based approach to optimization ?

Considering  available  access  paths  and  determining  the most efficient execution  plan  based  on statistics in the data dictionary for the tables accessed by the statement and their associated  clusters and indexes.



What is a Procedure ?

A  Procedure consist of a set of SQL and PL/SQL statements that are grouped together  as a unit to solve a specific problem or perform a set of related tasks.



What is a Package ?

A  Package  is a collection of related procedures, functions, variables and other package constructs together as a unit in the database



What is Database Trigger ?

A Database Trigger is procedure (set of SQL  and PL/SQL statements) that is automatically  executed  as  a  result of an insert in,update to, or delete from a table



What are the uses of Database Trigger ?

Database  triggers  can  be  used  to automatic data generation, audit data modifications, enforce complex Integrity constraints, and customize complex security authorizations.



What are Roles

Roles  are  named groups of related privileges that are granted to users or other roles.



What is a profile ?

Each  database  user  is  assigned  a Profile that specifies limitations on various system resources available to the user



What are the roles and user accounts created automatically with the database

DBA - role Contains all database system privileges.



SYS  user  account  - The DBA role will be assigned to this account. All of the base tables and views for the database's dictionary are store in this schema and are manipulated only by ORACLE.



SYSTEM user account - It has all the system privileges for the database and additional  tables  and  views  that display administrative information and internal  tables  and  views  used  by  oracle tools are created using this username.

What are the database administrators utilities avaliable ?

SQL * DBA - This allows DBA to monitor and control an ORACLE database.



SQL  *  Loader  -  It loads data from standard operating system files (Flat files) into ORACLE database tables.



Export  (EXP) and Import (imp) utilities allow you to move existing data in ORACLE format to and from ORACLE database.



What is the use of  ANALYZE command  ?

To perform one of these function on an index,table, or cluster:



     -  To collect statistics about object used by the optimizer and store them in the data dictionary.

     -  To delete statistics about the object used by object from the data dictionary.

     - To validate the structure of the object.

     - To identify migrated and chained rows of the table or cluster.



What is a SNAPSHOT ?

Snapshots  are  read-only copies of a master table located on a remote node which  is  periodically  refreshed  to  reflect  changes made to the master table.



How can we reduce the network traffic?

- Replication of data in distributed environment.

     - Using snapshots to replicate data.

     - Using remote procedure calls.



Differentiate simple and complex, snapshots ?

A  simple snapshot is based on a query that does not contains GROUP BY clauses, CONNECT BY clauses, JOINs, sub-query or snashot of operations.

     - A complex snapshots contain atleast any one of the above.



What are the options available to  refresh snapshots ?

COMPLETE   -   Tables   are completely regenerated using the snapshots query and the master tables every time the snapshot referenced.

FAST  - If simple snapshot used then a snapshot log can be used to send the changes to  the snapshot tables.

FORCE  -  Default value. If possible it performs a  FAST refresh; Otherwise it will perform a complete refresh



What is snapshot log ?

It  is a table that maintains a record of modifications to the master table in  a  snapshot.  It is stored  in the same database as master table and is only  available for simple snapshots.  It should be created before creating snapshots.



What is  Restricted Mode of Instance Startup ?

An  instance  can be started in (or later altered to be in) restricted mode so  that  when  the  database is open connections are limited only to those whose  user  accounts  have  been  granted  the  RESTRICTED  SESSION system privilege.



What are the different modes of mounting a Database with the Parallel Server ?

Exclusive  Mode  If  the  first  instance that mounts a database does so in exclusive mode, only that Instance can mount the database.



Parallel  Mode   If the first instance that mounts a database is started in parallel  mode,  other instances that are started in parallel mode can also mount the database.



What is Full Backup ?

A full backup is an operating system backup of all data files, on-line redo log  files  and  control  file  that  constitute  ORACLE  database  and the parameter

What  are  the  advantages of operating a database in ARCHIVELOG mode over operating it in NO ARCHIVELOG mode ?

Complete database recovery from disk failure is possible only in ARCHIVELOG mode.

Online database backup is possible only in ARCHIVELOG     mode

</></></></></>

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