ORACLE DATABASE

1. What are the components of Physical databasestructure of Oracle Database?.


2. What are the components of Logical database structure of ORACLE database?


3. What is a Tablespace?


4. What is SYSTEM tablespace and When is it Created?


5. Explain the relationship among Database, Tablespace and Data file.

Answers

1.
ORACLE database is comprised of three types of files. One or more Data files, two are more Redo Log files, and one
or more Control files.

2.
Tablespaces and the Database's Schema Objects.

3.
A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical
structures together.

4.
Every ORACLE database contains a tablespace named SYSTEM, which is automatically created when the database is
created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.

5.
Each databases logically divided into one or more tablespaces One or more data files are explicitly created for each
tablespace.

1. What is schema?

A Schema is a collection of database objects owned by a database user.

2. What are Schema Objects ?

Some Schema objects are : tables , views, synonyms,indexes, procedures , fundtions ...




3. Can objects of the same Schema reside in different tablespaces.?

Yes they can reside.

4. Can a Tablespace hold objects from different Schemes ?

Yes it can hold.

5. What is Table ?

It is the basic informational unit of the database. Basic storage unit of database. Table data stored as Row , Column format(like excel).

6. What is a View ?

A view is a logical entity. It is a SQL statement stored in the database in the system tablespace. Data for a view is built in a table created by the database engine in the TEMP tablespace. When you query on the view the SQl associated with it is taken and executed and the result is displayed.

1. What are the advantages of Views ?

2. What is a Sequence ?

3. What is a Synonym ?

4. What are the type of Synonyms?

5. What is a Private Synonyms ?

6. What is a Public Synonyms ?

7. What are synonyms used for ?

Answers
1.
Provide an additional level of table security, by restricting access to a predetermined set of rows and columns of a table.
Hide data complexity.
Simplify commands for the user.
Present the data in a different perpecetive from that of the base table.
Store complex queries.

2.
A sequence generates a serial list of unique numbers for numerical columns of a database's tables.

3.
A synonym is an alias for a table, view, sequence or program unit.

4.
There are two types of Synonyms Private and Public.

5.
A Private Synonyms can be accessed only by the owner.

6.
A Public synonyms can be accessed by any user on the database.

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

1. What is an Index ?



2. How are Indexes Update ?


3. What are Clusters ?


4. What is cluster Key ?


5. What is Index Cluster ?


6. What is Hash Cluster ?


7. When can Hash Cluster used ?


Answers


1.
An Index is an optional structure associated with a table to have direct access to rows,which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

2.
Indexes are automatically maintained and used by ORACLE. Changes to table data are automatically incorporated into all relevant indexes.

3.
Clusters are groups of one or more tables physically stores together to share common columns and are often used together.

4.
The related columns of the tables in a cluster is called the Cluster Key.

5.
A Cluster with an index on the Cluster Key.

6.
A row is stored in a hash cluster based on the result of applying a hash function to the row's cluster key value. All rows with the same hash key value are stores together on disk.

7.Hash clusters are better choice when a table is often queried with equality queries. For such queries the specified cluster key value is hashed. The resulting hash key value points directly to the area on disk that stores the specified rows.