51). What is COMPRESS and CONSISTENT setting in EXPORT utility ?
Simply: COMPRESS=n – Allocated space in database for imported table will be exactly as the space required to hold the data.
COMPRESS=y – The INITIAL extent of the table would be as large as the sum of all the extents allocated to the table in the original database.
In other words:
The default, COMPRESS=y, causes Export to flag table data for consolidation into one initial extent upon import.
If extent sizes are large (for example, because of the PCTINCREASE parameter), the allocated space will be larger than the space required to hold the data.
If you specify COMPRESS=n, Export uses the current storage parameters, including the values of initial extent size and next extent size.
If you are using locally managed tablespaces you should always export with COMPRESS=n
Default: n. Specifies whether or not Export uses the SET TRANSACTION READ ONLY statement to ensure that the data seen by Export is consistent to a single point in time and does not change during the execution of the exp command.
You should specify CONSISTENT=y when you anticipate that other applications will be updating the target data after an export has started.
If you use CONSISTENT=n, each table is usually exported in a single transaction. However, if a table contains nested tables, the outer table and each inner table are exported as separate transactions.
If a table is partitioned, each partition is exported as a separate transaction.
Therefore, if nested tables and partitioned tables are being updated by other applications, the data that is exported could be inconsistent. To minimize this possibility, export those tables at a time when updates are not being done.
52). What is the difference between Direct Path and Conventional Path loading ?
A conventional path load executes SQL INSERT statements to populate tables in an Oracle database.
A direct path load eliminates much of the Oracle database overhead by formatting Oracle data blocks and writing the data blocks directly to the database files.
53). Can you disable and enable Primary key ?
You can use the ALTER TABLE statement to enable, disable, modify, or drop a constraint.
When the database is using a UNIQUE or PRIMARY KEY index to enforce a constraint, and constraints associated with that index are dropped or disabled, the index is dropped, unless you specify otherwise.
While enabled foreign keys reference a PRIMARY or UNIQUE key, you cannot disable or drop the PRIMARY or UNIQUE key constraint or the index.
Disabling Enabled Constraints
The following statements disable integrity constraints. The second statement specifies that the associated indexes are to be kept.
ALTER TABLE dept DISABLE CONSTRAINT dname_ukey;
ALTER TABLE dept DISABLE PRIMARY KEY KEEP INDEX, DISABLE UNIQUE (dname, loc) KEEP INDEX;
The following statements enable novalidate disabled integrity constraints:
ALTER TABLE dept ENABLE NOVALIDATE CONSTRAINT dname_ukey;
ALTER TABLE dept ENABLE NOVALIDATE PRIMARY KEY, ENABLE NOVALIDATE UNIQUE (dname, loc);
The following statements enable or validate disabled integrity constraints:
ALTER TABLE dept MODIFY CONSTRAINT dname_key VALIDATE;
ALTER TABLE dept MODIFY PRIMARY KEY ENABLE NOVALIDATE;
The following statements enable disabled integrity constraints:
ALTER TABLE dept ENABLE CONSTRAINT dname_ukey;
ALTER TABLE dept ENABLE PRIMARY KEY, ENABLE UNIQUE (dname, loc);
To disable or drop a UNIQUE key or PRIMARY KEY constraint and all dependent FOREIGN KEY constraints in a single step, use the CASCADE option of the DISABLE or DROP clauses.
For example, the following statement disables a PRIMARY KEY constraint and any FOREIGN KEY constraints that depend on it:
ALTER TABLE dept DISABLE PRIMARY KEY CASCADE;
54). What is an Index Organized Table ?
An index-organized table (IOT) is a type of table that stores data in a B*Tree index structure. Normal relational tables, called heap-organized tables, store rows in any order (unsorted). In contrast to this, index-organized tables store rows in a B-tree index structure that is logically sorted in primary key order. Unlike normal primary key indexes, which store only the columns included in it definition, IOT indexes store all the columns of the table (an exception to this rule – is being called the overflow area).
Properties and restrictions:
- An IOT must contain a primary key
- Rows are accessed via a logical rowid and not a physical rowid like in heap-organized tables
- An IOT cannot be in a cluster
- An IOT cannot contain a column of LONG data type
- You cannot modify an IOT index property using ALTER INDEX (error ORA-25176), you must use an ALTER TABLE instead.
Advantages of an IOT
- As an IOT has the structure of an index and stores all the columns of the row, accesses via primary key conditions are faster as they don’t need to access the table to get additional column values.
- As an IOT has the structure of an index and is thus sorted in the order of the primary key, accesses of a range of primary key values are also faster.
- As the index and the table are in the same segment, less storage space is needed.
- In addition, as rows are stored in the primary key order, you can further reduce space with key compression.
- As all indexes on an IOT uses logical rowids, they will not become unusable if the table is reorganized.
Row overflow area
If some columns of the table are infrequently accessed, it is possible to offload them into another segment named the overflow area. An overflow segment will decrease the size of the main (or top) segment and will increase the performance of statements that do not need access the columns in the overflow area.
The overflow area can contains only columns that are not part of the primary key.
If a row cannot fit in a block, you must define an overflow area.
Consequently, the primary key values of an IOT must fit in a single block.
The columns of the table that are recorded in the overflow segment are defined using the PCTHRESHOLD and/or INCLUDING options of the OVERFLOW clause (examples on source website).
55). What is a Global Index and Local Index ?
Local Index – each partition of a local index is associated with exactly one partition of the table.
Global Index – global index is associated with multiple partitions of the table.
Oracle offers two types of global partitioned index:
– Global Range Partitioned Indexes
– Global Hash Partitioned Indexes
Global Nonpartitioned Indexes – behave just like a nonpartitioned index.
56). What is the difference between Range Partitioning and Hash Partitioning ?
Range Partitioning maps data to partitions based on a range of column values (e.g. a date column)
Hash Partitioning maps data to partitions based on a hashing algorithm, evenly distributing data between the partitions.
This is typically used where ranges aren’t appropriate, i.e. customer number, product ID
57). What is difference between Multithreaded/Shared Server and Dedicated Server ?
Oracle Database creates server processes to handle the requests of user processes connected to an instance.
A server process can be either of the following:
– A dedicated server process, which services only one user process
– A shared server process, which can service multiple user processes
Your database is always enabled to allow dedicated server processes, but you must specifically configure and enable shared server by setting one or more initialization parameters.
58). Can you import objects from Oracle ver. 7.3 to 9i ?
Different versions of the import utility are upwards compatible. This means that one can take an export file created from an old export version, and import it using a later version of the import utility.
Oracle also ships some previous catexpX.sql scripts that can be executed as user SYS enabling older imp/exp versions to work (for backwards compatibility).
For example, one can run $ORACLE_HOME/rdbms/admin/catexp7.sql on an Oracle 8 database to allow the Oracle 7.3 exp/imp utilities to run against an Oracle 8 database.
59). How do you move tables from one tablespace to another tablespace ?
There are several methods to do this;
1) export the table, drop the table, create the table definition in the new
tablespace, and then import the data (imp ignore=y).
2) Create a new table in the new tablespace with the CREATE TABLE statement AS SELECT all from source table
CREATE TABLE temp_name TABLESPACE new_tablespace AS SELECT * FROM source_table;
Then drop the original table and rename the temporary table as the original:
DROP TABLE real_table;
RENAME temp_name TO real_table;
Note: don’t forget to rebuild any indexes.
60). How to display how much space is used and free in a tablespace ?
Example query to check free and used space per tablespace:
/* + RULE */
df.tablespace_name “Tablespace”, df.bytes / (1024 * 1024) “Size (MB)”,
SUM(fs.bytes) / (1024 * 1024) “Free (MB)”, NVL(
ROUND(SUM(fs.bytes) * 100 / df.bytes),1) “% Free”,
ROUND((df.bytes – SUM(fs.bytes)) * 100 / df.bytes)
FROM dba_free_space fs,
SELECT tablespace_name,SUM(bytes) bytes
GROUP BY tablespace_name
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
/* + RULE */
df.tablespace_name tspace, fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024), NVL(ROUND((SUM(fs.bytes)
– df.bytes_used) * 100 / fs.bytes), 1),
ROUND((SUM(fs.bytes) – df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
GROUP BY tablespace_name,bytes_free,
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,
Tablespace Size (MB) Free (MB) % Free % Used
—————————— ———- ———- ———- ———-
UNDOTBS1 65 17.8125 27 73
EXAMPLE 100 22.625 23 77
USERS 5 1.0625 21 79
TEMP 20 2 10 90
SYSAUX 625.125 54.5 9 91
SYSTEM 700 9.0625 1 99