Oracle Interview Questions – Part 39

Q.What is a synonym ?

Synonym, as the name suggests, is an alternative name for an object. It can be used for simplifying names that are lenghy or complex, for e.g you can use a simple synonym for accessing objects of other users, for which you had to prefix username to the object.

Q.What is Parallel Server ?

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

Q.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 (table,indexes,views etc.,)
The server that access the database consists of
SGA (Database buffer, Dictionary Cache Buffers, Redo log buffers, Shared SQL pool)
SMON (System MONito)
PMON (Process MONitor)
LGWR (LoG Write)
DBWR (Data Base Write)
CKPT (Check Point)
User Process with associated PGS

Q.What does a control file contains ?

information about the database,redologfile and datafile,archivelog file location and current scn no
and redo log file
1.Database name and identifier.
2.Time stamp of database creation.
3.Tablespace names.
4.Backup information.
5.Checkpoint information.
6.Current online redo log file sequence number.

Q.What are the different types of segments ?

temp segment,
data segment,
table segment,
index segment
There’re different types of segments depend on what types of TABLES:
1)Table segments
2)Index segments
3)Partition segments & subpartition segments
4)Index partition segments
5)LOB segments
6)Cluster segments
7)Rollback or Type2 UNDO segments
8)Nested table segments

Q.What are Schema Objects ?

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. Objects may define areas of the database to hold data, such as tables or indexes, or may consist just of a definition, such as a views or synonyms.
There is no relationship between a tablespace and a schema. Objects in the same schema can use storage in different tablespaces, and a tablespace can contain data from different schemas.
Schema objects can be created and manipulated using SQL. As an administrator, you can create and manipulate schema objects, just as you do with the logical and physical structures of your database using Oracle Enterprise Manager. The underlying SQL is generated for you by Oracle Enterprise Manager.

Q.What is network 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.

Q.What is 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.
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.
To create a private database link, you specify: CREATE DATABASE LINK …;

Q.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.

Q.What is a database instance and 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.

Q.What is a lookup table in database? where can we use these type of table?

Lookup tables are like constraints which hold a value. The content in the lookup tables dosen’t change often, hence refered as lookup (reference).
Example : Countries table
Countries are not added on regular bases hence one can consider the table as a lookup table. As and when needed in some other table the countries information can be fetched base on PK – Country-Code column. Eg – Emp_Per_Info.

Q.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 stored only once for multiple tables in the cluster.

Q.Whenever a DBA creates a user in which datafile that will be get created ?

It depends on the product you are using. For instance, DB2 doesn’t require that a user be defined (to DB2).
In a relational system it is only required that a relational catalog/data dictionary exists. What it looks like and how it’s designed is up to the designers.
And please, in a relational system, data values are stored in TABLES, not datafiles, just tables.

Q.Is it advisable to force an index when you want to select mostof the entries in the table?

If table has many columns as well as large, and you want to access only indexed columns, then it is yes.
If you are selecting columns those are not indexed, and still you are using index then it will increase disk I/O which will drag performance of the query as well database.

Q.How are the index updates

Indexes can be updated by either Dropping and recreating them, or Rebuilding them online.
If you have an open cursor on a table, modify the same tableand commit, what would happen?
changes will be committed

Q.What is an Oracle view ?

A view is a logical table which makes a
complex query easy.We can even create
a complex view by joining two tables.

Q.What is a Schema ?

The set of objects owned by user account is called the schema.
What is the function of redo log
The primary function of the redo log is to record all changes made to data.

Q.What is Parallel Server ?

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

Q.What is a schema ?

Systematic arrangement of tables is known as schema
They are

Q.What is DDL, DML?

Data Definition Language..
-> Create Command, Drop Command, Alter Command and Truncate Command
Data Manipulation Language..
-> Insert Command,Delete Command, Update Command

Q.What is a private synonym ?

To create a private synonym in your own schema, you must have CREATE SYNONYM system privilege.
To create a private synonym in another user’s schema, you must have CREATE ANY SYNONYM system privilege.
To create a PUBLIC synonym, you must have CREATE PUBLIC SYNONYM system privilege.
Use the CREATE SYNONYM statement to create a synonym, which is an alternative name for a table, view, sequence, procedure, stored function, package, materialized view, Java class schema object, user-defined object type, or another synonym.
Synonyms provide both data independence and location transparency. Synonyms permit applications to function without modification regardless of which user owns the table or view and regardless of which database holds the table or view. However, synonyms are not a substitute for privileges on database objects. Such privileges must be granted to a user before the user can use the synonym.

Q.What is a segment ?

segment is the collection of extents allocated to paricular object like table and index. Table segment and index segment

Q.How to define data block size ?

The primary block size is defined by the Initiaization parameter DB_BLOCK_SIZE.

Q.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

Q.What Is an Oracle Data File?

An Oracle data file is a big unit of physical storage in the OS file system. One or many Oracle data files are organized together to provide physical storage to a single Oracle tablespace.

Q.What is a redo log ?

Redo log contains the before and after image copies of changed data.

Q.How can you find all the tables created by an user?

This could be found through table_name from sys.dba_tables where owner=’specified user’ * from cat; * from sys.dba_objects where owner =’specified owner’
use the below query
Select table_name from user_tables;
This will just show the tables from the present user connected.

Q.What are the components of logical database structure of Oracle database ?

Tablespace, segments, extens, data Blocks.
A logical unit of storage of databse is called Tablespace.
Segments is a space alloocated for a specific logical storage structure within a tablespce.
Extents: Space allocated to a segments.
Datablocks: Oracle server manage the storage space in the datafiles in units is called data blocks or oracle blocks.

Q.What is an extent ?

An Extent is a collection of contagious blocks
the second level of granuality of the storage in database is called is defined as the collectionof contagious datablocks.

Q.Do a view contain data ?

A view does not contain any data of its own, but is like a window through which data from other tables can be viewed and changed.

Q.What Is a Initialization Parameter File in Oracle?

An initialization parameter file is a text file that contains a list of initialization parameters. The file should be written in the client’s default character set. Sample initialization parameter files are provided on the Oracle distribution medium for each operating system. A sample file is sufficient for initial use, but you will probably want to modify the file to tune the database for best performance. Any changes will take effect after you completely shut down and restart the instance.

Q.What is Program Global Area (PGA) in Oracle?

A Program Global Area (PGA) is a memory buffer that is allocated for each individual database session and it contains session specific information such as SQL statement data or buffers used for sorting. The value specifies the total memory allocated by all sessions, and changes will take effect as new sessions are started.

Q.What is the use of redo log information ?

A redo log file is one of the file of database,along with control file and data file.
here the datafile is work for storing the actual data while the control file is used for taking all other information about the actual data and the redolog file is for storing the actual data’s copy for recovery purpose.
Redo log information are used to recover database if it get currept.

Q.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

Q.What is Oracle table ?

Table is the most commonly used form of storing user data

Q.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 stored only once for multiple tables in the cluster.

Q.What Is SQL*Plus?

SQL*Plus is an interactive and batch query tool that is installed with every Oracle Database Server or Client installation. It has a command-line user interface, a Windows Graphical User Interface (GUI) and the iSQL*Plus web-based user interface.

Q.What Is a Recycle Bin in Or acle?

Recycle bin is a logical storage to hold the tables that have been dropped from the database, in case it was dropped in error. Tables in recycle bin can be recovered back into database by the Flashback Drop action. Oracle database recycle save the same purpose as the recycle bin on your Windows desktop.
Recycle bin can be turned on or off in the recyclebin=on/off in your parameter file.

Q.Explain database clusters?

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

Q.What is the difference between indexes,views,synonyms?

An index is a method of allowing faster retrieval of records.
A view is an virtual table formed using a single table or one or more tables.
A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects

Q.What Is the Relation of a User Account and a Schema in Oracle?

User accounts and schemas have a one-to-one relation. When you create a user, you are also implicitly creating a schema for that user. A schema is a logical container for the database objects (such as tables, views, triggers, and so on) that the user creates. The schema name is the same as the user name, and can be used to unambiguously refer to objects owned by the user.