Why should we use separate ASM home?

ASM should be installed separately from the database software in its own ORACLE_HOME directory. This will allow you the flexibility to patch and upgrade ASM and the database software independently.

How does one list the partitions of a partitioned object?

One can list the partitions of a table with:

Code:

SELECT * FROM dba_tab_partitions WHERE table_name = '<table_name>';

One can show the partitions of an given index with:

Code:

SELECT * FROM dba_ind_partitions WHERE index_name = '<index_name>';

How do I know if an object is partitioned or not?

A simple query against the data dictionary will show if a given table or indexe is partitioned or not:

Code:

SELECT * FROM dba_part_tables;

SELECT * FROM dba_part_indexes;

How does one enable partition support for a database?

Partitioning is only available with Oracle Enterprise Edition as a cost option (you need to buy licenses before you can use it).

Start the Oracle installer and check if "Oracle Partitioning" is installed. If it is, you can just start using it.

If not, you will get error ORA-00439: feature not enabled: Partitioning. If you get this error, upgrade to Enterprise Edition and/or install the partitioning option.

What partitioning types does Oracle support?

http://yoga-raasiphalalu.blogspot.com/20...tions.html

Oracle support the following partitioning methods:

* Range partitioning - data is mapped to partitions based on a range of column values (usually a date column)

* Hash partitioning - data is mapped to partitions based on a hashing algorithm, evenly distributing data between the partitions.

* List partitioning - data is mapped to partitions based on a list of discrete values.

* Interval partitioning - data is mapped to partitions based on an interval

* Composite partitioning - combinations of the above methods

o Range-Hash partitioning - data is partitioned by range, then hashed to sub-partitions.

o Range-List partitioning - data is partitioned by range, then to sub-partitions based on a value list.

o Range-range partitioning

o List-range partitioning

o List-hash partitioning

o List-list partitioning

* Interval partitioning, an extension of range partitioning. New partitions are automatically added.

* System partitioning, application controlled partitioning.

* Reference partitioning, partitioned like a parent table (referenced in a referential constraint).

What is an integrity constraint?

Answer - Integrity constraints define a business rule for a column of the table. They are defined with a table and are stored as part of a table’s definition.

Types of integrity constraints supported by oracle are

NOT NULL integrity constraint

Unique Key integrity constraint

Primary Key integrity constraint

Foreign key integrity constraint

Check integrity constraint