Oracle Database related Questions

Here there are very basic still sometime become critical to know concept if you working in Oracle Database related activities. 

Q: What is an Oracle instance?Every running Oracle database is associated with an Oracle instance. When adatabase is started on a database server (regardless of the type of computer),Oracle allocates a memory area called the System Global Area (SGA) and starts one or more Oracle processes. This combination of the SGA and the Oracle processes is called an Oracle instance. The memory and processes of an instance manage the associated database’s data efficiently and serve the one or multiple users of the database.
The Instance and the Database

After starting an instance, Oracle associates the instance with the specified database. This is called mounting the database. The database is then ready to be opened, which makes it accessible to authorized users. Multiple instances can execute concurrently on the same computer, each accessing its own physical database. In clustered and massively parallel systems (MPP),the Oracle Parallel Server allows multiple instances to mount a single database. Only the database administrator can start up an instance and open the database.If a database is open, the database administrator can shut down the database so that it is closed. When a database is closed, users cannot access the information that it contains. Security for database startup and shutdown is controlled via connections to Oracle with administrator privileges. Normal users do not have control over the current status of an Oracle database.




Q: What is a view?

A view is a tailored presentation of the data contained in one or more tables(or other views). Unlike a table, a view is not allocated any storage space, nor does a view actually contain data; rather, a view is defined by a query that extracts or derives data from the tables the view references. These tables are called base tables. Views present a different representation of the data that resides within thebase tables. Views are very powerful because they allow you to tailor the presentation of data to different types of users. Views are often used to:

• provide an additional level of table security by restricting access to a predetermined set of rows and/or columns of a table

• hide data complexity

• simplify commands for the user

• present the data in a different perspective from that of the base table

• isolate applications from changes in definitions of base tables

• express a query that cannot be expressed without using a view

Q: What is referential integrity?


Rules governing the relationships between primary keys and foreign keys of tables within a relational database that determine data consistency. Referential integrity requires that the value of every foreign key in every table be matched by the value of a primary key in another table.

Q: What is a cursor?A cursor is a private sql work area used to perform manipulations on data using pl\sql, mainly used for multiple row manipulations and locking columns. Data which is populated into the cursor is known as active dataset.


Cursors are of two types 1.implicit   2.explicit

Implicit———attributes or properties for implicit cursor

1.sql%is open:attribute returns a boolean value stating wether the cursor is open or closed.

2.sql % found: returns boolean value stating whether the record is found in the cursor.

3.sql%notfound : returns a boolean value stating whether the record is not found in the cursor

4.sql %rowcount :returns a pneumeric value stating no.of rows executed in the cursor.

Explicit cursors—retrives multiple rows, users can perform locks on th data in the cursor attributes-

1.% is open

2.% found

3.% not found

4.% rowcount




Q: Why Use Sql* Loader in Oracle Database? 
The Sql Loader utility loads data into an existing ORACLE table from an external files.



9 thoughts on “Oracle Database related Questions

  1. When you declare a cursor, you get a pointer variable, which does not point anything. When the cursor is opened, memory is allocated and the cursor structure is created. The cursor variable now points the cursor. When the cursor is closed the memory allocated for the cursor is released.
    Cursors allow the programmer to retrieve data from a table and perform actions on that data one row at a time


  2. The shorter explanation would be:
    A instance is a logical snapshot of a database at a given time. Oracle implements this by allokating the SGA and transactions mechanism like redo logs.


  3. Yep. If you check my blog you will find I always do. Everyone of the articles/news has its own proper author (unless I totally have no clue who the person is) and a your site here section (unless they have no site whatsoever), which is the link back. It can be a site if you want or just the blog. For now I’ll assume its the blog unless you tell me otherwise. Oh and I’m definitely subscribing this here is a wealth of information compare to mines, which is just starting out😉


  4. I wonder about the purpose of sql%isOpen .
    since it is an implicit cursor , probably once a query gets executed the implicit cursor gets closed automatically . I want to know usage of this parameter , because by default it would always be false


Want to give some comment to author ( Shivmohan Purohit )

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s