Oracle FAQ

How to convert local management tablespace to dictionary managed tablespace
Ans.
Local to Dictionary managed tablespcaes:
exec dbms_space_admin.Tablespace_Migrate_FROM_Local('tablespace');

Dictionary to locally managed tablespaces:
exec dbms_space_admin.Tablespace_Migrate_to_Local('tablespace');

What is an oracle instance?
Ans.
Database files themselves are useless without the memory structures and processes to interact with the database. Oracle defines the term instance as the memory

structure and the background processes used to access data from a database.

An instance has two major memory structures:
The System Global Area, also known as the Shared Global Area (SGA) stores information in memory that is shared by the various processes in Oracle.
The Program Global Area, also known as the Private Global Area (PGA) contains information that is private to a particular process.


What is a view?
Ans.
A View in Oracle database system is simply the representation of a SQL statement that is stored in memory so that it can easily be re-used.

What is referential integrity?
Ans.
Referential integrity is a database concept that ensures that relationships between tables remain consistent. When one table has a foreign key to another table, the

concept of referential integrity states that you may not add a record to the table that contains the foreign key unless there is a corresponding record in the linked

table. It also includes the techniques known as cascading update and cascading delete, which ensure that changes made to the linked table are reflected in the

primary table.

Name the data dictionary that stores constraints details?
Ans.
SYS ALL_CONSTRAINTS
SYS APPLY$_CONSTRAINT_COLUMNS
SYS DBA_CONSTRAINTS
SYS DBA_IAS_CONSTRAINT_EXP
SYS KU$_CONSTRAINT0_VIEW
SYS KU$_CONSTRAINT1_VIEW
SYS KU$_CONSTRAINT2_VIEW
SYS KU$_CONSTRAINT_COL_VIEW
SYS KU$_CONSTRAINT_VIEW
SYS KU$_PKREF_CONSTRAINT_VIEW
SYS KU$_REF_CONSTRAINT_VIEW
SYS LOADER_CONSTRAINT_INFO
SYS USER_CONSTRAINTS
SYS _DBA_APPLY_CONSTRAINT_COLUMNS
SYS _DBA_APPLY_OBJECT_CONSTRAINTS


What is a collection of privileges?
Ans.
Role

What is a snapshot?
Ans.


What is a synonym?
Ans.
A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.

What is a cursor?
Ans.
When a query is executed in oracle, a result set is produced and stored in the memory. Oracle allows the programmer to access this result set in the memory through

cursors.
What is a sequence?
Ans.
Sequence are nothing but which • Automatically generates unique numbers• Is a sharable object• Is typically used to create a primary key value• Replaces

application code• Speeds up the efficiency of accessing sequencevalues when cached in memory

What is a trigger?
Ans.
Triggers are a special PL/SQL construct similar to procedures. However, a procedure is executed explicitly from another block via a procedure call, while a trigger

is executed implicitly whenever the triggering event happens. The triggering event is either a INSERT, DELETE, or UPDATE command. The timing can be either

BEFORE or AFTER. The trigger can be either row-level or statement-level, where the former fires once for each row affected by the triggering statement and the

latter fires once for the whole statement.

What is an exception?
Ans.
An Exception is an error situation, which arises during program execution. When an error occurs exception is raised, normal execution is stopped and control

transfers to exception-handling part. Exception handlers are routines written to handle the exception. The exceptions can be internally defined (system-defined or

pre-defined) or User-defined exception.

What is a partition of table?
Ans.
Partitioning enables tables and indexes or index-organized tables to be subdivided into smaller manageable pieces and these each small piece is called a "partition".

From an "Application Development" perspective, there is no difference between a partitioned and a non-partitioned table. The application need not be modified to

access a partitioned table if that application was initially written on a non partitioned tables.

What are pseudo-columns in SQL? Provide examples.
Ans.
Oracle supports several special-purpose data elements that are not actually contained in a table, but are available for use in SQL statements as if they were part of

the table.

Pseudo columns in Oracle
rowid
versions_xid
versions_operation
versions_startscn
versions_starttime
versions_endscn
versions_endtime
sysdate
systimestamp
rownum
ora_rowscn
object_value
level (only in hierarchical queries with connect by
user
..... etc

What are the Data Control statements?
Ans.

What is a schema?
Ans.
A schema is a collection of database objects. A schema is owned by a database user and has the same name as that user. Schema objects are logical structures

created by users to contain, or reference, their data. Schema objects include structures like tables, views, and indexes.

What is a type?
Ans.

What is a data model?
Ans.

What is a relation?
Ans.

Advantages of redo log files?
Ans.

What is an Archiver?
Ans.

What is a database buffer cache?
Ans.

What are the background processes in Oracle?
Ans.

%type and %rowtype are attributes for…?
Ans.

What are the steps in a two-phase commit?
Ans.

What is a union, intersect, minus?
Ans.
UNION ALL Example The UNION operator returns only distinct rows that appear in either result, while the UNION ALL operator returns all rows. The UNION

ALL operator does not eliminate duplicate selected rows:

SELECT product_id FROM order_items
UNION
SELECT product_id FROM inventories;

SELECT location_id FROM locations
UNION ALL
SELECT location_id FROM departments;

A location_id value that appears multiple times in either or both queries (such as '1700') is returned only once by the UNION operator, but multiple times by the

UNION ALL operator.

INTERSECT Example The following statement combines the results with the INTERSECT operator, which returns only those rows returned by both queries:

SELECT product_id FROM inventories
INTERSECT
SELECT product_id FROM order_items;
MINUS Example The following statement combines results with the MINUS operator, which returns only rows returned by the first query but not by the second:

SELECT product_id FROM inventories
MINUS
SELECT product_id FROM order_items;


What is a join, explain the types of joins?
Ans.
Basically Joins are used to get result from two or more tables and there are two types of joins
inner join and outer join
Inner join : a join of two or more tables which omits the blank rows while checking

Outer join is subcatogorised in to left outer join and right outer join. Which includes blank rows in specifed side if condition satisfies.
Simple outer join is combination of left and right outerjoins.
Apart from these there are
Natural join : cartisian product
Equi join : which includes operator in condition
NonEqui join : All conditional joins which doesn't uses in there conditions.

What is a co-related sub-query?
Ans.
The main differencre between subquery and co-related subquery is that in subquery child query is executed first n then parent,but in co-related subquery main query

is executed first(even though parenthesis are present) and then child query.

Example of co-related subquery

select dname from dept where exists
(select deptno from emp
where dept.deptno=emp.deptno);

select dname from dept where not exists
(select deptno from emp
where dept.deptno=emp.deptno);

ODBC stands for…?
Ans.
Open Database Connectivity

Data-type used to work with integers is?
Ans.

Describe data models?
Ans.
A data model in software engineering is an abstract model that describes how data is represented and accessed. Data models formally define data elements and

relationships among data elements for a domain of interest.

Data model explicitly determines the meaning of data, which in this case is known as structured data (as opposed to unstructured data, for example an image, a

binary file or a natural language text, where the meaning has to be elaborated). Typical applications of data models include database models, design of information

systems, and enabling exchange of data. Usually data models are specified in a data modeling language

Describe the Normalization principles?
Ans.
http://www.ischool.utexas.edu/~wyllys/DMPAMaterials/normover.html

What are the types of Normalization?
Ans.
There are 3 types of Normalization. Normalization helps in reducing data redundancy. As we move towards higher normalization

1NF: This type of normalization states that there must not be any duplicates in the tables that we use. In other words, all the tables used must have a primary key

defined.
2NF: This type of normalization states that data redundancy can be reduced if attributes those are dependent on one of the keys of a composite primary key are

isolated to a separate table. Not only does this reduces data redundancy but also helps in increasing data retention when a delete is done. For example, consider a

table that has the following columns: Part Id, State, City, and Country. Here, assume Part Id & Country form the composite primary key. The attributes state & city

depend only on the country. 2NF states that if such is the case then split the table into 2 tables. One with Part Id & country as the columns. Other with Country,

state & city as the columns. In the 1st table if a delete is made to all the rows with Part Id = ‘X’ then we would lose country related data too. But in the 2nd case

this would not happen.
3NF: This type of normalization states that if a dependency exists on certain attributes other than the primary key then the table split depending on the dependency

has to be done. Consider the same example above. In the present case consider that Part Id is the only primary key. Now state, city depend only on country & not

on Part Id. This table is already in 1NF & 2NF. But to achieve 3NF we would do the same split as above.

What is de-normalization?
Ans.
Denormalization is usually done to decrease the time required to execute complex queries. Drawbacks of a normalized database are mostly in performance. In a

normalized database, more joins are required to gather all the information from multiple entities, as data is divided and stored in multiple entities rather than in one

large table. Queries that have a lot of complex joins will require more CPU usage and will adversely affect performance. Sometimes, it is good to denormalize parts

of the database. Examples of design changes to denormalize the database and improve performance are:

Add a column (or columns) to the table that contains pre-aggregated data to be used only for a report.
Partition the table with many columns to multiple tables.
Add duplicate keys to tables. This will reduce the number of joins required to get complete information.