Interview Questions.

Question 1. Explain Oracle Datatypes?

Answer :

The Oracle datatypes :

CHAR
VARCHAR2
VARCHAR
NUMBER
DATE
LONG
RAW
LONG RAW.
ROWID
MLSLABEL.
Question 2. How Rowids Are Used?

Answer :

Oracle makes use of ROWIDs internally for the construction of indexes. Each key in an index is associated with a ROWID that factors to the related row’s cope with for immediate access.End–customers and alertness builders can also use ROWIDs for numerous essential uses:

ROWIDs are the quickest manner of getting access to specific rows.
ROWIDs can be used to look how a table is organized.
ROWIDs are precise identifiers for rows in a given table.
Oracle 10g Interview Questions
Question 3. Explain About Data Conversions In Oracle 7.3?

Answer :

In a few instances, Oracle substances records of 1 datatype where it expects information of a exclusive datatype. This is allowed while Oracle can robotically convert the records to the anticipated datatype the usage of one of the following features:

TO_NUMBER()
TO_CHAR()
TO_DATE()
TO_LABEL()
CHARTOROWID()
ROWIDTOCHAR()
HEXTOCHAR()
CHARTOHEX()
Question four. Explain Hash Functions In Oracle 7.Three?

Answer :

A hash function is a function carried out to a cluster key price that returns a hash cost. Oracle then uses the hash fee to locate the row within the proper statistics block of the hash cluster. The task of a hash characteristic is to offer the most distribution of rows some of the available hash values of the cluster. To achieve this purpose, a hash feature ought to minimize the range of collisions.

 

Oracle 10g Tutorial
Question five. Why Cluster Key Used?

Answer :

The cluster secret's the column, or institution of columns, that the clustered tables have in not unusual. You specify the columns of the cluster key when creating the cluster. You sooner or later specify the same columns whilst creating each desk introduced to the cluster.

Oracle 9i Interview Questions
Question 6. Explain About Clusters?

Answer :

Clusters are an non-obligatory approach of storing desk information. A cluster is a collection of tables that percentage the same data blocks because they percentage commonplace columns and are often used together. For instance, the EMP and DEPT desk percentage the DEPTNO column. When you cluster the EMP and DEPT tables , Oracle bodily shops all rows for every department from both the EMP and DEPT tables within the same records blocks.

Question 7. Explain About Unique And Non–particular Indexes In Oracle 7.Three?

Answer :

Indexes may be precise or non–specific. Unique indexes guarantee that no  rows of a table have replica values within the columns that define the index. Non–precise indexes do not impose this restrict on the column values.

Oracle recommends that you do not explicitly define particular indexes on tables; distinctiveness is precisely a logical idea and have to be related to the definition of a desk. Alternatively, define UNIQUE integrity constraints at the desired columns. Oracle enforces UNIQUE integrity constraints by mechanically defining a unique index at the specific key

Oracle 9i Tutorial Oracle 8i Interview Questions
Question eight. Explain About Composite Indexes?

Answer :

A composite index (additionally known as a concatenated index) is an index which you create on multiple columns in a table. Columns in a composite index can seem in any order and need no longer be adjoining in the table. Composite  indexes can speed retrieval of facts for SELECT statements wherein the WHERE clause references all or the leading part of the columns in the composite index. Therefore, you should deliver a few concept to the order of the columns used within the definition; typically, the maximum normally accessed or maximum selective columns move first.

Question 9. What Is The Use Of The Sequence Generator In Oracle?

Answer :

The sequence generator offers a sequential series of numbers. The series generator is mainly beneficial in multi–person environments for generating specific sequential numbers with out the overhead of disk I/O or transaction locking. Therefore, the collection generator reduces “serialization” in which the statements of two transactions ought to generate sequential numbers on the identical time. By averting the serialization that results while multiple users wait for every other to generate and use a series range, the collection generator improves transaction throughput and a consumer’s wait is significantly shorter.

Informatica Interview Questions
Question 10. Explain The Rules For Updatable Join Views?

Answer :

General Rule  Any INSERT, UPDATE, or DELETE operation on a be part of view can modify handiest one underlying base table at a time.
UPDATE Rule All updatable columns of a be a part of view have to map to columns of a key preserved table. If the view is described with the WITH CHECK OPTION clause, then all join columns and all columns of repeated tables are non–updatable.
DELETE Rule Rows from a be part of view can be deleted as long as there's precisely one key–preserved desk in the be part of. If the view is described with the WITH CHECK OPTION clause and the key preserved desk is repeated, then the rows cannot be deleted from the view.
INSERT Rule An INSERT announcement must not, explicitly or implicitly, discuss with the columns of a non–key preserved table. If the join view is defined with the WITH CHECK OPTION clause, then INSERT statements aren't approved.
Oracle 8i Tutorial
Question eleven. What Is A Null?

Answer :

A null is the absence of a cost in a column of a row. Nulls imply lacking, unknown, or inapplicable facts. A null ought to now not be used to imply some other fee, consisting of zero. A column lets in nulls until a NOT NULL or PRIMARY KEY integrity constraint has been described for the column, wherein case no row may be inserted with out a price for that column.

PL/SQL Interview Questions
Question 12. What Is A Datafile?

Answer :

Each tablespace in an Oracle database is constituted of one or extra working system files known as datafiles. A tablespace’s datafiles physically store the related database facts on disk.

Oracle 10g Interview Questions
Question 13. What Is A Table Space?

Answer :

An Oracle database is created from one or extra logical garage devices referred to as tablespaces. The database’s statistics is collectively saved inside the database’s tablespaces. A database administrator can use tablespaces to do the following:

manage disk space allocation for database records.
Assign particular space quotas for database users.
Manage availability of facts by means of taking character tablespaces on-line or offline.
Perform partial database backup or healing operations.
Allocate information garage throughout devices to enhance overall performance.
Informatica Tutorial
Question 14. What Are The Operations Required In Temporary Segments?

Answer :

The following commands can also require the usage of a temporary phase:

CREATE INDEX
SELECT ... ORDER BY
SELECT DISTINCT ...
SELECT ... GROUP BY
SELECT ... UNION
SELECT ... INTERSECT
SELECT ... MINUS
unindexed joins
positive correlated subqueries
Question 15. Explain About Temporary Segments?

Answer :

Temporary Segments : When processing queries, Oracle frequently calls for temporary workspace for intermediate tiers of SQL announcement processing. Oracle routinely allocates this disk space known as a transient segment.Typically, Oracle requires a transient segment as a piece location for sorting. Oracle does not create a phase if the sorting operation may be accomplished in reminiscence or if Oracle reveals some other way to carry out the operation using indexes.

Oracle 11g Interview Questions
Question 16. Explain About Segments? Types Of Segments?

Answer :

Segments : A phase is a fixed of extents that comprise all of the statistics for a specific logical garage shape within a tablespace. For instance, for every table, Oracle allocates one or greater extents to form that table’s statistics section, and, for each index, Oracle allocates one or greater extents to shape its index phase.

There are four kinds of segments used in Oracle databases:

facts segments.
Index segments.
Rollback segments.
Brief segments.
Oracle 11g Tutorial
Question 17. What Are Data Blocks?

Answer :

Data Blocks : Oracle manages the garage space in the datafiles of a database in units known as information blocks. A records block is the smallest unit of I/O used by a database. 

SQL Interview Questions
Question 18. What Are Parameter Files?

Answer :

Parameter Files : To start an example, Oracle ought to study a parameter report.A parameter document is a text record containing a listing of example configuration parameters. You set those parameters to unique values and to initialize most of the reminiscence and procedure settings of an Oracle example. Among different matters, the parameters of this document tell Oracle the subsequent:

the call of the database for which to begin up an example.
How a great deal reminiscence to apply for memory structures in the SGA.
What to do with stuffed on line redo log documents.
The names and places of the database’s manipulate files.
The names of private rollback segments in the database.
Oracle 9i Interview Questions
Question 19. What Are The Database And Instance Shut Down Steps?

Answer :

There are three steps to shutting down an instance and the database to which it is linked:

Close the database.
Dismount the database.
Shut down the instance.
Oracle robotically plays all three steps whilst an instance is shut down.

Question 20. What Are The Steps For Database And Instance Startup?

Answer :

There are three steps to starting a database and making it available for systemwide use:

Start an instance.
Mount the database.
Open the database.
Oracle apps Interview Questions
Question 21. Explain About The Security Mechanisms In Oracle 7.Three?

Answer :

Security Mechanisms  : The Oracle Server offers discretionary access manipulate, which is a means of limiting get admission to to information based on privileges. The appropriate privilege should be assigned to a consumer in order for that person to get right of entry to an object. Appropriately privileged users can provide other customers privileges at their discretion; because of this, this sort of protection is referred to as “discretionary”.

Oracle manages database safety using numerous one-of-a-kind centers:

database users and schemas
privileges
roles
garage settings and quotas
resource limits
auditing
Question 22. What Are The Integrity Constraints In Oracle?

Answer :

An integrity constraint is a declarative manner to outline a commercial enterprise rule for a column of a table. An integrity constraint is a declaration approximately a table’s records this is constantly actual:

If an integrity constraint is created for a table and a few existing desk statistics does not fulfill the constraint, the constraint can't be enforced.
After a constraint is defined, if any of the effects of a DML assertion violate the integrity constraint, the announcement is rolled again and an errors is lower back.
Integrity constraints are described with a desk and are stored as part of the desk’s definition, centrally inside the database’s information dictionary, so that every one database packages have to adhere to the same set of guidelines. If a rule adjustments, it need handiest be changed once on the database level and not regularly for every software.

The following integrity constraints are supported via Oracle:

Disallows nulls (empty entries) in a table’s column.
Disallows duplicate values in a column or set of columns.
Disallows replica values and nulls in a column or set of columns.
Requires every fee in a column or set of columns fit a cost in a related desk’s UNIQUE or PRIMARY KEY (FOREIGN KEY integrity constraints additionally define referential integrity moves that dictate what Oracle should do with structured statistics if the records it references is altered).
Disallows values that do not fulfill the logical expression of the constraint.
Question 23. Explain About The Program Interface?

Answer :

The Program Interface : The application interface is the mechanism by way of which a user procedure communicates with a server process. It serves as a method of preferred communication among any patron tool or software (inclusive of Oracle Forms) and Oracle software program. 

Sybase Interview Questions
Question 24. What Is Pmon?

Answer :

Process Monitor (PMON) The system display performs method restoration while a person technique fails. PMON is liable for cleaning up the cache and liberating resources that the method became using. PMON additionally assessments on dispatcher (see underneath) and server processes and restarts them in the event that they have failed.

Oracle 8i Interview Questions
Question 25. What Is System Monitor (smon)?

Answer :

System Monitor (SMON) The gadget reveal performs example restoration at instance startup. In a more than one instance device (one that uses the Parallel Server), SMON of one instance can also carry out example healing for different times that have failed.

SMON additionally cleans up transient segments which can be now not in use and recovers useless transactions skipped during crash and instance recovery because of report–examine or offline errors.

These transactions are finally recovered via SMON whilst the tablespace or document is delivered lower back on-line. SMON also coalesces loose extents inside the database to make loose area contiguous and simpler to allocate.

Question 26. What Is Checkpoint?

Answer :

Checkpoint (CKPT) At particular times, all changed database buffers in the system worldwide place are written to the datafiles by using DBWR; this occasion is called a checkpoint. The Checkpoint procedure is accountable for signalling DBWR at checkpoints and updating all the datafiles and manipulate files of the database to suggest the most recent checkpoint. CKPT is non-obligatory; if CKPT isn't gift, LGWR assumes the responsibilities of CKPT.

Oracle Apps ERP Interview Questions
Question 27. What Is A Log Writer (lgwr)?

Answer :

Log Writer (LGWR) The Log Writer writes redo log entries to disk. Redo log data is generated within the redo log buffer of the system international place. As transactions commit and the log buffer fills, LGWR writes redo log entries into a web redo log report.

Informatica Interview Questions
Question 28. What Is A Processes?

Answer :

Processes : A process is a “thread of control” or a mechanism in an operating device which could execute a sequence of steps. Some working structures use the phrases process or task. A system typically has its very own private reminiscence region wherein it runs.

Question 29. Explain About Oracle Data Blocks?

Answer :

At the greatest degree of granularity, an Oracle database’s statistics is stored in data blocks. One facts block corresponds to a specific quantity of bytes of bodily database area on disk. A records block length is special for each Oracle database whilst the database is created. A database makes use of and allocates loose database area in Oracle information blocks.

 

Oracle Access Manager Interview Questions
Question 30. What Is Database Enforced Integrity?

Answer :

database enforced integrity :  Oracle enforces statistics integrity, “business rules” that dictate the requirements for proper records. As a result, the costs of coding and coping with checks in many database programs are removed.

Question 31. How Views Are Used?

Answer :

Views offer a way to give a one-of-a-kind illustration of the records that is living inside the base tables. Views are very effective because they allow you to tailor the presentation of information to specific styles of customers. Views are often used

to provide an additional level of desk protection through proscribing access to a predetermined set of rows and/or columns of a table
to cover records complexity.
To simplify commands for the user.
To offer the facts in a distinctive perspective from that of the base desk.
To cover statistics complexity.
To simplify commands for the person.
To offer the information in a special attitude from that of the bottom table.
To isolate applications from adjustments in definitions of base tables.
To express a question that can't be expressed with out using a view.
To keep complex queries.
To reap enhancements in availability and overall performance.