- World's Largest Collection of Interview Questions & Answers, FAQs, queries, sample papers, exam papers, dumps, what, why, how, where, when questions
Our Services
Get 9,000 Interview Questions & Answers in an eBook.

Get it now !!
Send your Resume to 6000 Companies
Oracle Architecture Interview Questions & Answers - Learning Mode

Oracle Architecture Interview Questions & Answers - Learning Mode

The Oracle database has a logical layer and a physical layer. The physical layer consists of the files that reside on the disk; the components of the logical layer map the data to these physical components.

Try Oracle Architecture Interview Questions & Answers - Exam Mode

1 2 Next

Sort By : Latest First | Oldest First | By Rating

Oracle Architecture Interview Questions & Answers - Learning Mode
Try Oracle Architecture Interview Questions & Answers - Exam Mode
Question: What are the Large object types supported by Oracle?

Answer: Oracle provides six data types for storing LOBs:

? CLOB and LONG for large fixed-width character data

? NCLOB for large fixed-width national character set data

? BLOB and LONG RAW for storing unstructured data

? BFILE for storing unstructured data in operating system files

LONG and LONG RAW data types were previously used for unstructured data, such as binary images, documents, or geographical information, and are primarily provided for backward compatibility. Source:
Question: Explain different types of segment. Data segment, Index segment, Rollback segment and temporary segment.

Answer: There are four types of segments used in Oracle databases:

- data segments
- index segments
- rollback segments
- temporary segments

Data Segments:
There is a single data segment to hold all the data of every non clustered table in an oracle database. This data segment is created when you create an object with the CREATE TABLE/SNAPSHOT/SNAPSHOT LOG command. Also, a data segment is created for a cluster when a CREATE CLUSTER command is issued.

The storage paramet Source:
Question: What is the use of Redo Log Information ?

Answer: Oracle maintains logs of all transactions against the databse. These transactions are recorded in files called Online Redo log files.These logs are used to recover the database's transaction in their proper order in the evnt of database crash. The redo log information is stored in Redo log file which is external to the datafilesEach database have two or more online redo log files. oracle writes tedo log file in cyclic order after the first log is filled it writes to the second log files unti Source:
Question: Describe the different type of Integrity Constraints supported by ORACLE ?

Answer: NOT NULL Constraint - Disallows Nulls in a table's column.

UNIQUE Constraint - Disallows duplicate values in a column or set of columns.

PRIMARY KEY Constraint - Disallows duplicate values and Nulls in a column or set of columns.

FOREIGN KEY Constrain - Require each value in a column or set of columns match a value in a related table's UNIQUE or PRIMARY KEY.

CHECK Constraint - Disallows values that do not satisfy the logical expression of the constraint. Source:
Question: Explain in brief oracle database objects.

Answer: Tables
Oracle stores information in the form of tables. For eg you can have a table named as climate in which you can store information about the climate of a place in the form of columns which could be the temperature, name of the place, date, humidity, etc.

In the terms of a relational database, one can call a table as an entity and the columns as it attributes.
2. Indexes

Indexing is a concept of listing of keywords accompanied by the location of information of the subject Source:
Question: What does a Control file Contain ?

Answer: The controlfile contains :

1. Name and Location of Datafiles Redo Logfiles.
2. Timestamp of Database Creation.
3. Current log sequence number is recorded when log switches are occur.
4. Checkpoint information is recorded as checkpoints are made.
5. Database identifier is recorded when the database is created. Source:
Question: What is a Data Dictionary ?

Answer: The data dictionary of an ORACLE database is a set of tables and views that are used as a read-only reference about the database.

It stores information about both the logical and physical structure of the database, the valid users of an ORACLE database, integrity constraints defined for tables in the database and space allocated for a schema object and how much of it is being used.

Question: What is the diff b/w BTREE INDEX and BITMAP INDEX

Answer: Bitmap indexes are more advantageous than b-tree indexes when the table has millions of rows and the key columns have low cardinality. bitmap indexes provide better performance than b tree indexes when queries often use a combination of multiple where conditions involving the OR operator, the table is read-only, or when there is low update activity on the key columns.
Question: Explain the categories of oracle processes i.e. user, data writing processes, logging processes and monitoring processes.

Answer: * User process ? User process is used in invocation of application software.
* Data writing process - A database writer process is used to write buffer content into a datafile. They are specifically used to write ?dirty block? to data files from the buffer.
* Logging processes - Log writer is used to write the redo log buffer from system global area to online redo log file. Only those redo entries are written hat have been copied into the buffer since the last time it wrote.
Question: Explain SGA memory structures: Shared Pool, Database buffer Cache, Redo log Cache, Large Pool Java Pool.

Answer: SGA (System Global Area) is a dynamic memory area of an Oracle Server. In SGA,the allocation is done in granuels. The size of the SGA is dependent on SGA_MAX_SIZE parameter.

The memory structures contained by SGA are:-

Shared Pool -
this memory structure is divided into two sub-structures which are Library Cache and Data Dictionary Cache for storing recently used PL/SQL statements and the recent data definitions. The maximum size of the Shared Pool depends on the SHARED_POOL_SIZ Source:
Question: What is the differnece between materialized view and snapshot

Answer: A materialized view is a replica of a target master from a singlepoint in time. The concept was first introduced with Oracle7 termed asSNAPSHOT. In Oracle release 7.1.6 snapshots were enhanced to enableDMLs along with a new terminology, updatable snapshots. With Oracle8.1.6 snapshots started to be used in data warehouse environments so anew terminology materialized view was introduced to address bothdistributed and data warehouse materialized views. Source:
Question: What is SQL Loader? Explain the files used by SQL Loader to load file. i.e Loader control file, Input datafile, Log File, Bad File, Discard file

Answer: SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. SQL*Loader supports various load formats, selective loading, and multi-table loads.

When a control file is fed to an SQL*Loader, it writes messages to the log file, bad rows to the bad file and discarded rows to the discard file.

Control file
The SQL*Loader control file contains information that describes how the data will be loaded. It contains the table name, column datatypes Source:
Question: Explain the methods provided by SQL Loader.

Answer: Conventional Path Load
Direct Path Load Source:
Question: What is the function of SMON?

Answer: The SMON background process performs all system monitoring functions on the oracle database.

Each time oracle is re-started, SMON performs a warm start and makes sure that the transactions that were left incomplete at the last shut down are recovered.

SMON performs periodic cleanup of temporary segments that are no longer needed. Source:
Question: What is the diff b/w BTREE INDEX and BITMAP INDEX

Answer: Btree Index is very usefull for OLTP and BITMAP index are useful for Decision Support System. When OLTP environment higly level DML activites where doing and it is better to use Btree indexes is better than Bitmap indexes. Where as BITMAP indexes is better for Decision support System because there will be few distinct values. Source:
Question: Explain the areas of memory used by oracle, i.e. Software code area, system global area (SGA), program global area(PGA), sort area.

Answer: Software area code: - It is a protected location that is used to store oracle code that is supposed to be run. The location is different from users' programs. The software area code is read only and can be shared or nonshared.

System global area: - SGA is used to store data and control information of an Oracles database instance when started. Each instance has its own SGA. SGA is read write. It comprises data structures like Redo buffer, Java pool, Streams pool etc.

Program glo Source:
Question: SGA definition files

Answer: System Global Area
The System Global Area (SGA) is a shared memory region that contains data and control information for one Oracle instance. When an instance starts, the SGA is allocated by Oracle and is deallocated when the instance shuts down. Every instance has an own SGA.

Online Users share the data in the SGA.

The information stored in the SGA is divided into memory structures like:

* The database buffers
* Redo log buffer
* The shared poo Source:
Question: What are the Back ground processes in Oracle?

Answer: There are a number of background processes running. Each process is used for a specific job.

* SMON ? system monitor process is used to clean temporary segments and crash recovery of a crashed instance.
* PMON ? process monitor is used to clean all resources acquired by a failed process.
* DBWR- Database writer is used to write ?dirty block? to data files from the buffer.
* LGWR - Log writer is used to write the redo log buffer from system global area to online re Source:
Question: What is the physical and logical structure of oracle?

Answer: Logical Database structures
Logical structures include tablespaces, schema objects, data blocks, extents and segments.

Database is logically divided into one or more tablespaces. Each tablespace creates one or more datafiles to physically store data.

Schema objects
Schema objects are the structure that represents database's data. Schema objects include structures such as tables, views, sequences, stored procedures, indexes, synonyms, clusters and database Source:
Question: Describe Oracle architecture in brief.

Answer: The Oracle database has:

- Logical layer: The components of the logical layer map the data to these physical components

- Physical layer: The physical layer consists of the files that reside on the disk

Tablespaces and Datafiles
The database is divided into one or more logical pieces known as tablespaces. A tablespace is used to logically group data together

The Database Schema: A collection of logical-structure objects, known as schema objects.

These schema Source:

1 2 Next

India News Network
Latest 20 Questions
An offer which is open for acceptance over a period of time is: (a) Cross Offer (b) Counter Offer (c) Standing Offer (d) Implied Offer
Specific offer can be communicated to__________ (a) All the parties of contract (b) General public in universe (c) Specific person (d) None of the above
_________ amounts to rejection of the original offer. (a) Cross offer (b) Special offer (c) Standing offer (d) Counter offer
A advertises to sell his old car by advertising in a newspaper. This offer is caleed: (a) General Offer (b) Special Offer (c) Continuing Offer (d) None of the above
In case a counter offer is made, the original offer stands: (a) Rejected (b) Accepted automatically (c) Accepted subject to certain modifications and variations (d) None of the above
In case of unenforceable contract having some technical defect, parties (a) Can sue upon it (b) Cannot sue upon it (c) Should consider it to be illegal (d) None of the above
If entire specified goods is perished before entering into contract of sale, the contract is (a) Valid (b) Void (c) Voidable (d) Cancelled
______________ contracts are also caled contracts with executed consideration. (a) Unilateral (b) Completed (c) Bilateral (d) Executory
A offers B to supply books @ Rs 100 each but B accepts the same with condition of 10% discount. This is a case of (a) Counter Offer (b) Cross Offer (c) Specific Offer (d) General Offer
_____________ is a game of chance. (a) Conditional Contract (b) Contingent Contract (c) Wagering Contract (d) Quasi Contract
There is no binding contract in case of _______ as one's offer cannot be constructed as acceptance (a) Cross Offer (b) Standing Offer (c) Counter Offer (d) Special Offer
An offer is made with an intention to have negotiation from other party. This type of offer is: (a) Invitation to offer (b) Valid offer (c) Voidable (d) None of the above
When an offer is made to the world at large, it is ____________ offer. (a) Counter (b) Special (c) General (d) None of the above
Implied contract even if not in writing or express words is perfectly _______________ if all the conditions are satisfied:- (a) Void (b) Voidable (c) Valid (d) Illegal
A specific offer can be accepted by ___________. (a) Any person (b) Any friend to offeror (c) The person to whom it is made (d) Any friend of offeree
An agreement toput a fire on a person's car is a ______: (a) Legal (b) Voidable (c) Valid (d) Illegal
"Holiday Packages" announced as an advertisement are an example of _________: (a) Offer (b) Counter Offer (c) Invitation to Offer (d) None of the above
A match fixing contract between a player and a broker is a: (a) Valid Contract (b) Unenforceable Contract (c) Void Contract (d) Illegal Contract
When a bookseller sells a book on cash payment then it is called as ___________: (a) Unilateral Contract (b) Bilateral Contract (c) Executed Contract (d) Executory Contract
____________ agreements are created by situation: (a) Written (b) Oral (c) Void (d) Implied
Cache = 0.03125 Seconds