Oracle Database

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

WE CAN WRITE TWO CURSORS IN ONE PROGRAM
WE CAN WRITE A CURSOR SPECIFYING PARAMETERS
CURSOR WITH UPDATE CLAUSE IS USED TO PERFORM LOCKS ON DATA.

Q: Why Use Sql* Loader in Oracle Database?

The Sql Loader utility loads data into an existing ORACLE table from an external files.It is available locally only on CMS and PCs with oracle version
There are several methods other than using SQL *Loader of inserting data into a table.
1. The Sql insert command may be used from the SQL * Plus module, for Example :
insert into CAR values(…) where the values to be inserted into a row of the table are listed inside the parentheses. Dates and Characters data must be Surrounded by single quotes; items are seperated by commas.
2. Sql*Forms allows you to add rows interactively using forms. The forms may contain default values and checks for invalid data.
3. ODL loads the table from a control file and separate fixed format data file. ODL is available on all versions of ORACLE .

SQL * Loader is much more flexible than ODL and will eventually supersede it on all systems.