TERM DEFINITION
BLOCK :Multiple Physical file blocks allocated from an existing data file
EXTENT :A set of contiguous database blocks
SEGMENT :A set of one or more extents that contains all the data for a specific structure within a tablespace
TABLESPACE :A logical repository of physically grouped data
FILE :A physical data file belonging to a single tablespace
DATABASE :A logical collection of shared data stored in tablespaces.
An ORACLE datablock is the smallest unit of I/O used by the database also called logical blocks and ORACLE blocks which corresponds to one or more physical blocks on disk. The size is determined upon database creation by initialization parameter DB_BLOCK_SIZE which is constant throughout all datafiles. Size also determines the size of each database buffer in SGA. The block size cannot be changed after database creation except by recreating the database. The block size should be equal to the size of O.S block size or more that that, typically 2K or 4K bytes.
PARTS OF DATABASE BLOCK
HEADER :Contain general block information, such as the block address, and the segment type. On the average, the fixed and variable portions of the block total 85 to 100 bytes.
TABLE DIRECTORY :Stores information about the tables in the cluster and is used with clustered segments.
ROW DIRECTORY :Contains row information about the actual rows in the block. Allow two bytes of overhead per row.
FREE SPACE :Consists of set of bytes in the block that is still available for insert, or update space requirements.
ROW DATA :Stores table or index data.
Block free space utilization parameters.
PCTFREE
PCTUSED
INITTRANS
MAXTRANS
PCTFREE :Set the percentage of block to be reserved (Kept free) for possible updates to rows that are already contained in that block with the PCTFREE parameter.
PCTFREE sets the percentage of usable block space to be reserved during row insertion for possible updates to rows contained in that block.
After PCTFREE is met, the block is considered full, and is not available for inserts of new rows.
Space remaining in block after PCTFREE quota is reached is reserved for update commands on rows within the block.
The PCTFREE parameter can also be specified when creating or altering indexes.
Increasing PCTFREE reduces the occurrence of rowchaining and row migration. A PCTFREE set too low for a given table may result in row migration.
ROW CHAINING & ROW MIGRATION.
Chaining occurs with large rows that contain long columns when all the data for a row in a table can not fit in the same block. The data for the row is stored in a chain of data blocks.
Migration occurs if a row in a data block is updated so that the overall row length increases and the block’s free space has been completely filled. The data for the row is migrated to a new datablock, assuming the entire row can fit in a new block.
Performance is affected due to rowchaining and row migration, because during I/O ORACLE must scan more than one data block to retrieve the information for the row.
TO RESOLVE ROW CHAINING AND ROW MIGRATION.
1)Examine the extent of chaining or migrating with the ANALYZE command.
2)Alter PCTFREE for the object.
3)export, drop and import the object.
LOW PCTFREE :Allows inserts to fill blocks more frequently, can cause row migration and row chaining, increases processing costs if ORACLE must frequently reorganize the blocks.
HIGHER PCTFREE :Reserves more room for future updates, may require more block to store the data, low processing costs, reduces the need to row chaining and row migration.
PCTUSED :Set PCTUSED to allow a block to be reconsidered for the insertion of new rows. New rows are inserted as long as the block is on the free list. A block is removed from the free list upon reaching the PCTFREE threshold.
PCTUSED is the threshold for determining when the block is available for row insertion.
When the percentage of block being used falls below PCTUSED either through row deletion or updates reducing column storage, the block is again available for insertion of new rows.
The system default for PCTUSED is 40
High delete activity may result in block fragmentation.
BLOCK FRAGMENTATION :Free space is not contiguously available due to high delete activity, low PCTUSED setting.
RESOLVE BLOCK FRAGMENTATION :ORACLE will perform a memory operation to condense the data in the block together under special cirucmstances.
Otherwise, export, drop, and import the object.
Modify PCTUSED to a more appropriate setting.
LOWER PCTUSED :Reduces processing costs because blocks are not often free, increases unused space.
HIGHER PCTUSED :Increases processing costs because blocks may often become free, improves space usage.
INITTRANS : is the initial number of transaction entries, for concurrent transactions, that are allocated in each block header when block is allocated (default is 1, minimum 1, maximum 255). Each transaction entry is 23 bytes in length.
MAXTRANS :is the maximum number of concurrent transaction that a block will support (default 255, minimum 1, maximum 255)
EXTENTS :
An extent is a set of contiguous blocks allocated to a segment, when a database object grows, space is allocated to it.
Each segment in a database created with at least one extent to hold its data, Rollback segment, however always have at least two extents.
The first extent is called the segments INITAL extent.
Subsequent extents are called the segments NEXT extent.
An object will only allocate a new extent if all of its currently allocated extents are already used.
Frequently de-allocation of extents can lead to fragmented physical data files.
Specified storage parameters:
INITIAL :Size in bytes of the first extent allocated to a segment. Default is the equivalent of 5 data blocks.
NEXT :Size in bytes of the next incremental extent allocated to a segment. Default is the equivalent of 5 data blocks.
MAXEXTENTS :Total number of extents that can ever be allocated for the segments. The maximum depends upon the ORACLE block size, default is 121.
MINEXTENTS :Total number of extents to be allocated when the segment is created. Default is one extent.
PCTINCREASE :Percent by which each incremental extent grows over the last incremental extent allocated. Default is 50 percent.
OPTIMAL :Specifies the optimal size in bytes for a rollback segment. Default is null.
FREELISTS :Number of lists of free blocks kept for inserts into table.
Set the storage parameter for the following objects :
TABLE, CLUSTER, INDEX, ROLLBACK SEGMENT, TABLESPACE
Rules of precedence :
Any storage parameter specified at the object level overrides the corresponding option set at the tablespace level.
When storage parameter are not explicitly set at the object level, they default to those at the tablespace level.
When storage parameters are not explicitly set at the tablespace level, ORACLE system defaults apply.
If storage parameter are altered, the new option apply only to the extents not yet allocated.
Optimal is only specified for rollback segments.
Uncontrolled extent allocation can cause performance degradation due to potentially excessive physical I/O operation and operating system file fragmentation. Fragmentation can be reduced by fitting a segment into the INITIAL extent, and setting PCTINCREASE appropriately to enlarge incremental extents.
SEGMENTS :
A Segments is a set of one or more extents that contain all the data for a specific type of logical storage structure within a tablespace.
A segment is a logical structure that can be created, will occupy storage, and can grow. Segments cannot span tablespaces. An extent is a set of contiguous database blocks. The storage parameters for temporary segments always use the default storage parameters set for the associated tablespace , they can never be set explicitly.
DATA SEGMENT :A collection of extents that holds all the data for a table or a cluster. When a table is created in table space data segment gets created.
Ex. Create Table tablename ( colname datatype)
PCTFREE 5 PCTUSED 65
STORAGE(
INITIAL 5M
NEXT 5M
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 121)
TABLESPACE tablespacename;
INDEX SEGMENT :A collection of extents that holds all of the index data for search optimization on large tables and clusters. If an index is created on a table, an index segment is created for each index.
Ex. Create Index indexname on tablename (colname)
STORAGE (INITIAL 500K NEXT 500K PCTINCREASE 0)
TABLESPACE tablespacename;
TEMPORARY SEGMENT :A collection of extents that holds data belonging to temporary tables created during a sort operation. Temporary segments provide workspace for the RDBMS to sort and join tables while performing complex searches, reclaimed at the end of the transaction (SMON process), space allocation determined by default storage of its tablespace, not protected by redolog.
ROLLBACK SEGMENT :A collection of extents that hold rollback data for rollback, read-consistency or recovery. Rollback segment entries are written in circular fashion for rollback, read consistency and recovery.
Consists of several rollback entries from multiple transactions.
Ex. SQL >CREATE PUBLIC ROLLBACK SEGMENT rbs01
TABLESPACE rbs
STORAGE(INITIAL 10K NEXT 10K OPTIMAL 20K MAXEXTENTS 121);
After creating set the rollback segment online.
SQL >ALTER ROLLBACK SEGMENT rbs01 ONLINE;
BOOTSTRAP SEGMENT ;An extent that contains dictionary definitions for dictionary tables to be loaded when the database is opened. Requires no attention on the part of the Database storage Administrator
The bootstrap segment contains data dictionary tables to be loaded when the database is opened. It cannot be read, modified, or dropped. Exists in system tablespace and is owned by the user sys. Usually needs less than 50 ORACLE BLOCKS.
TABLESPACES & DATAFILES
Data in an ORACLE database is logically stored in tablespaces and physically stored in database files.
The ORACLE database can be logically divided into separate tablespaces. The system tablespace must exist in order for the database to run. Tablespaces are designated to contain sever database segments.
Each tablespaces contains one or more operating system files.
Tablespace can be brought online while the database is running.
Tablespace can be taken offline except the system tablespace, leaving database running.
Objects created in a tablespace can never be allocated space outside of their original tablespace.
TABLESPACE USAGES;
Control space allocation and assign space quotas to users.
Control availability of data by taking individual tablespaces online or offline.
Distribute data storage across devices to improve I/O performance and reduce I/O contention against a single disk.
Perform partial backup and partial recovery operations.
IMPORTANT DATA DICTIONARY VIEWS.
USER/DBA_EXTENTS
USER/DBA_FREE_SPACE
USER/DBA_SEGMENTS
DBA_TABLESPACES
DBA_DATA_FILES
1)To view tablespaces and status
SQL>SELECT TABLE_SPACE_NAME, STATUS FROM DBA_TABLESPACES;
2)To view datafiles
SQL>SELECT FILE_NAME, FILE_ID, TABLESPACE_NAME, BYTES FROM DBA_DATA_FILES;
3)To view extents of free space in each tablespace
SQL>SELECT * FROM DBA_FREE_SPACE ORDER BY FILE_ID, BLOCK_ID;
4)To view general information about all segments in the database.
SQL>SELECT OWNER, SEGMENT_NAME, EXTENTS, MAX_EXTENTS FROM DBA_SEGMENTS ORDER BY 1, 2;
1. How to find out the Duplicate Rows in an Existing Table
(Using SQL)?
Ans=> select * from emp a
where a.rowid = (select max(b.rowid) from emp b
where b.empno = a.empno)
This will display rows which are having duplicates.
2. Modification in a Package Body, will it affect Package Spec?
Ans=> No, It will not affect the Package Specifications as long as the
parameters and names are not changed.
3. A view is created through multiple table. If you change the data in
the table of view, Will it affect the view.
Ans=> No, It will not affect the View. If the change is in the Structure of
of any table or renaming or dropping of tables will change the view
status.
4. What are type of the DB Triggers.
Ans=> There are 12 Database Triggers. These can be mainly classified into two
types such as Statement wise, and Each Row wise. and Each one will have
one Before and one After Trigger.
5. Difference between On-Error and On-Message Triggers?
Ans=> An On-Error trigger fires whenever Oracle Forms would normally cause
an error message to display.
An On-Message trigger Fires whenever Oracle Forms would normally cause
a message to display.
So the On- Error will fire only when the Error Message to be displayed
where as the On-Message will fire for all the message commands including
the error messages.
Generally an On-Message trigger is used for the following purposes:
• to trap and respond to an informative message
• to replace a standard informative message with a custom message
• to exclude an inappropriate message
where as an On-Error trigger is used for
• to trap and recover from an error
• to replace a standard error message with a custom message
6. Difference between Oracle 6.0 and Oracle 7.0
Ans=> There are lot of differences between 6 and 7
I am listing whatever on hand i have.
0) There are some new datatypes in Ver 7.0 such as
Variable Length VARCHAR2,
i) There are no Constraints such as referential Integrity, Primary Key.
ii)There are no Stored Procedures
iii)There are no Database Triggers
iv) There is no Shared pool of parsed SQL in 6.0
v) There is no Packages funda in v 6.0
vi) There are no Hinting to Optimize such as in Sql Execution
vii) In ver 6.0 only dedicated server is available whereas in ver 7.0
there are several types of servers are available such as
- Dedicated Server
- Multi Threaded Server
viii) There is no concept of Distributed Databases in ver 6.0 whereas
this is supported in ver 7.0 in the form of two phase commit.
ix) There were only Rule based optimisation in Ver 6.0 whereas in ver 7.0
both Rule Based and Cost Based Optimization is supported. In ver 7.0
By default Cost based optimization is adopted.
x) There is an Archieve Log facility in Ver 7.0
xi) Snapshots are not available in ver 6.0.
xii) There are new commands such as ANALYSE and TRUNCATE
7. How do you check 1-1 relationship existing between two tables?
Ans=> Raghu This Question is little vague, If it is to find out the Relation
ship between two Blocks in a Form, Then using the follwing command you
will know.
If you still insist on tables, You can select from the user_constraints
view to find out the Foriegn Key constraint.
8. When you create Master-Detail Blocks, What are all triggers created?
Ans=> There are three triggers will be created by default such as
On-Check-Delete-Master (Block Level)
On-Clear-Details (Form Level)
On-Populate-Details (Block Level)
and it also creates following procedures to coordinate the m/d
Check_package_failure
Clear_all_master_details
Query_master_details
9. How do you join two tables ?
Ans=> This Question is also either vague or silly?
But any way i will try to answer it.
By using the Where Clause of the SELECT Statement we can Join the Tables
Following are the types of Joins such as Equi Join, Self Join and Outer Join
10.How do you avoid using indexes?
Ans=> There are two ways by which we can avoid using indexes by Oracle.
- By Hinting the SQL Statement we can instruct Oracle to use Full Table
Scan
- By using some function or calculation in the Select Statement Oracle
will avoid using Indexes. For e.g.,
Select * from emp where 1 * empno = empno;
BLOCK :Multiple Physical file blocks allocated from an existing data file
EXTENT :A set of contiguous database blocks
SEGMENT :A set of one or more extents that contains all the data for a specific structure within a tablespace
TABLESPACE :A logical repository of physically grouped data
FILE :A physical data file belonging to a single tablespace
DATABASE :A logical collection of shared data stored in tablespaces.
An ORACLE datablock is the smallest unit of I/O used by the database also called logical blocks and ORACLE blocks which corresponds to one or more physical blocks on disk. The size is determined upon database creation by initialization parameter DB_BLOCK_SIZE which is constant throughout all datafiles. Size also determines the size of each database buffer in SGA. The block size cannot be changed after database creation except by recreating the database. The block size should be equal to the size of O.S block size or more that that, typically 2K or 4K bytes.
PARTS OF DATABASE BLOCK
HEADER :Contain general block information, such as the block address, and the segment type. On the average, the fixed and variable portions of the block total 85 to 100 bytes.
TABLE DIRECTORY :Stores information about the tables in the cluster and is used with clustered segments.
ROW DIRECTORY :Contains row information about the actual rows in the block. Allow two bytes of overhead per row.
FREE SPACE :Consists of set of bytes in the block that is still available for insert, or update space requirements.
ROW DATA :Stores table or index data.
Block free space utilization parameters.
PCTFREE
PCTUSED
INITTRANS
MAXTRANS
PCTFREE :Set the percentage of block to be reserved (Kept free) for possible updates to rows that are already contained in that block with the PCTFREE parameter.
PCTFREE sets the percentage of usable block space to be reserved during row insertion for possible updates to rows contained in that block.
After PCTFREE is met, the block is considered full, and is not available for inserts of new rows.
Space remaining in block after PCTFREE quota is reached is reserved for update commands on rows within the block.
The PCTFREE parameter can also be specified when creating or altering indexes.
Increasing PCTFREE reduces the occurrence of rowchaining and row migration. A PCTFREE set too low for a given table may result in row migration.
ROW CHAINING & ROW MIGRATION.
Chaining occurs with large rows that contain long columns when all the data for a row in a table can not fit in the same block. The data for the row is stored in a chain of data blocks.
Migration occurs if a row in a data block is updated so that the overall row length increases and the block’s free space has been completely filled. The data for the row is migrated to a new datablock, assuming the entire row can fit in a new block.
Performance is affected due to rowchaining and row migration, because during I/O ORACLE must scan more than one data block to retrieve the information for the row.
TO RESOLVE ROW CHAINING AND ROW MIGRATION.
1)Examine the extent of chaining or migrating with the ANALYZE command.
2)Alter PCTFREE for the object.
3)export, drop and import the object.
LOW PCTFREE :Allows inserts to fill blocks more frequently, can cause row migration and row chaining, increases processing costs if ORACLE must frequently reorganize the blocks.
HIGHER PCTFREE :Reserves more room for future updates, may require more block to store the data, low processing costs, reduces the need to row chaining and row migration.
PCTUSED :Set PCTUSED to allow a block to be reconsidered for the insertion of new rows. New rows are inserted as long as the block is on the free list. A block is removed from the free list upon reaching the PCTFREE threshold.
PCTUSED is the threshold for determining when the block is available for row insertion.
When the percentage of block being used falls below PCTUSED either through row deletion or updates reducing column storage, the block is again available for insertion of new rows.
The system default for PCTUSED is 40
High delete activity may result in block fragmentation.
BLOCK FRAGMENTATION :Free space is not contiguously available due to high delete activity, low PCTUSED setting.
RESOLVE BLOCK FRAGMENTATION :ORACLE will perform a memory operation to condense the data in the block together under special cirucmstances.
Otherwise, export, drop, and import the object.
Modify PCTUSED to a more appropriate setting.
LOWER PCTUSED :Reduces processing costs because blocks are not often free, increases unused space.
HIGHER PCTUSED :Increases processing costs because blocks may often become free, improves space usage.
INITTRANS : is the initial number of transaction entries, for concurrent transactions, that are allocated in each block header when block is allocated (default is 1, minimum 1, maximum 255). Each transaction entry is 23 bytes in length.
MAXTRANS :is the maximum number of concurrent transaction that a block will support (default 255, minimum 1, maximum 255)
EXTENTS :
An extent is a set of contiguous blocks allocated to a segment, when a database object grows, space is allocated to it.
Each segment in a database created with at least one extent to hold its data, Rollback segment, however always have at least two extents.
The first extent is called the segments INITAL extent.
Subsequent extents are called the segments NEXT extent.
An object will only allocate a new extent if all of its currently allocated extents are already used.
Frequently de-allocation of extents can lead to fragmented physical data files.
Specified storage parameters:
INITIAL :Size in bytes of the first extent allocated to a segment. Default is the equivalent of 5 data blocks.
NEXT :Size in bytes of the next incremental extent allocated to a segment. Default is the equivalent of 5 data blocks.
MAXEXTENTS :Total number of extents that can ever be allocated for the segments. The maximum depends upon the ORACLE block size, default is 121.
MINEXTENTS :Total number of extents to be allocated when the segment is created. Default is one extent.
PCTINCREASE :Percent by which each incremental extent grows over the last incremental extent allocated. Default is 50 percent.
OPTIMAL :Specifies the optimal size in bytes for a rollback segment. Default is null.
FREELISTS :Number of lists of free blocks kept for inserts into table.
Set the storage parameter for the following objects :
TABLE, CLUSTER, INDEX, ROLLBACK SEGMENT, TABLESPACE
Rules of precedence :
Any storage parameter specified at the object level overrides the corresponding option set at the tablespace level.
When storage parameter are not explicitly set at the object level, they default to those at the tablespace level.
When storage parameters are not explicitly set at the tablespace level, ORACLE system defaults apply.
If storage parameter are altered, the new option apply only to the extents not yet allocated.
Optimal is only specified for rollback segments.
Uncontrolled extent allocation can cause performance degradation due to potentially excessive physical I/O operation and operating system file fragmentation. Fragmentation can be reduced by fitting a segment into the INITIAL extent, and setting PCTINCREASE appropriately to enlarge incremental extents.
SEGMENTS :
A Segments is a set of one or more extents that contain all the data for a specific type of logical storage structure within a tablespace.
A segment is a logical structure that can be created, will occupy storage, and can grow. Segments cannot span tablespaces. An extent is a set of contiguous database blocks. The storage parameters for temporary segments always use the default storage parameters set for the associated tablespace , they can never be set explicitly.
DATA SEGMENT :A collection of extents that holds all the data for a table or a cluster. When a table is created in table space data segment gets created.
Ex. Create Table tablename ( colname datatype)
PCTFREE 5 PCTUSED 65
STORAGE(
INITIAL 5M
NEXT 5M
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 121)
TABLESPACE tablespacename;
INDEX SEGMENT :A collection of extents that holds all of the index data for search optimization on large tables and clusters. If an index is created on a table, an index segment is created for each index.
Ex. Create Index indexname on tablename (colname)
STORAGE (INITIAL 500K NEXT 500K PCTINCREASE 0)
TABLESPACE tablespacename;
TEMPORARY SEGMENT :A collection of extents that holds data belonging to temporary tables created during a sort operation. Temporary segments provide workspace for the RDBMS to sort and join tables while performing complex searches, reclaimed at the end of the transaction (SMON process), space allocation determined by default storage of its tablespace, not protected by redolog.
ROLLBACK SEGMENT :A collection of extents that hold rollback data for rollback, read-consistency or recovery. Rollback segment entries are written in circular fashion for rollback, read consistency and recovery.
Consists of several rollback entries from multiple transactions.
Ex. SQL >CREATE PUBLIC ROLLBACK SEGMENT rbs01
TABLESPACE rbs
STORAGE(INITIAL 10K NEXT 10K OPTIMAL 20K MAXEXTENTS 121);
After creating set the rollback segment online.
SQL >ALTER ROLLBACK SEGMENT rbs01 ONLINE;
BOOTSTRAP SEGMENT ;An extent that contains dictionary definitions for dictionary tables to be loaded when the database is opened. Requires no attention on the part of the Database storage Administrator
The bootstrap segment contains data dictionary tables to be loaded when the database is opened. It cannot be read, modified, or dropped. Exists in system tablespace and is owned by the user sys. Usually needs less than 50 ORACLE BLOCKS.
TABLESPACES & DATAFILES
Data in an ORACLE database is logically stored in tablespaces and physically stored in database files.
The ORACLE database can be logically divided into separate tablespaces. The system tablespace must exist in order for the database to run. Tablespaces are designated to contain sever database segments.
Each tablespaces contains one or more operating system files.
Tablespace can be brought online while the database is running.
Tablespace can be taken offline except the system tablespace, leaving database running.
Objects created in a tablespace can never be allocated space outside of their original tablespace.
TABLESPACE USAGES;
Control space allocation and assign space quotas to users.
Control availability of data by taking individual tablespaces online or offline.
Distribute data storage across devices to improve I/O performance and reduce I/O contention against a single disk.
Perform partial backup and partial recovery operations.
IMPORTANT DATA DICTIONARY VIEWS.
USER/DBA_EXTENTS
USER/DBA_FREE_SPACE
USER/DBA_SEGMENTS
DBA_TABLESPACES
DBA_DATA_FILES
1)To view tablespaces and status
SQL>SELECT TABLE_SPACE_NAME, STATUS FROM DBA_TABLESPACES;
2)To view datafiles
SQL>SELECT FILE_NAME, FILE_ID, TABLESPACE_NAME, BYTES FROM DBA_DATA_FILES;
3)To view extents of free space in each tablespace
SQL>SELECT * FROM DBA_FREE_SPACE ORDER BY FILE_ID, BLOCK_ID;
4)To view general information about all segments in the database.
SQL>SELECT OWNER, SEGMENT_NAME, EXTENTS, MAX_EXTENTS FROM DBA_SEGMENTS ORDER BY 1, 2;
1. How to find out the Duplicate Rows in an Existing Table
(Using SQL)?
Ans=> select * from emp a
where a.rowid = (select max(b.rowid) from emp b
where b.empno = a.empno)
This will display rows which are having duplicates.
2. Modification in a Package Body, will it affect Package Spec?
Ans=> No, It will not affect the Package Specifications as long as the
parameters and names are not changed.
3. A view is created through multiple table. If you change the data in
the table of view, Will it affect the view.
Ans=> No, It will not affect the View. If the change is in the Structure of
of any table or renaming or dropping of tables will change the view
status.
4. What are type of the DB Triggers.
Ans=> There are 12 Database Triggers. These can be mainly classified into two
types such as Statement wise, and Each Row wise. and Each one will have
one Before and one After Trigger.
5. Difference between On-Error and On-Message Triggers?
Ans=> An On-Error trigger fires whenever Oracle Forms would normally cause
an error message to display.
An On-Message trigger Fires whenever Oracle Forms would normally cause
a message to display.
So the On- Error will fire only when the Error Message to be displayed
where as the On-Message will fire for all the message commands including
the error messages.
Generally an On-Message trigger is used for the following purposes:
• to trap and respond to an informative message
• to replace a standard informative message with a custom message
• to exclude an inappropriate message
where as an On-Error trigger is used for
• to trap and recover from an error
• to replace a standard error message with a custom message
6. Difference between Oracle 6.0 and Oracle 7.0
Ans=> There are lot of differences between 6 and 7
I am listing whatever on hand i have.
0) There are some new datatypes in Ver 7.0 such as
Variable Length VARCHAR2,
i) There are no Constraints such as referential Integrity, Primary Key.
ii)There are no Stored Procedures
iii)There are no Database Triggers
iv) There is no Shared pool of parsed SQL in 6.0
v) There is no Packages funda in v 6.0
vi) There are no Hinting to Optimize such as in Sql Execution
vii) In ver 6.0 only dedicated server is available whereas in ver 7.0
there are several types of servers are available such as
- Dedicated Server
- Multi Threaded Server
viii) There is no concept of Distributed Databases in ver 6.0 whereas
this is supported in ver 7.0 in the form of two phase commit.
ix) There were only Rule based optimisation in Ver 6.0 whereas in ver 7.0
both Rule Based and Cost Based Optimization is supported. In ver 7.0
By default Cost based optimization is adopted.
x) There is an Archieve Log facility in Ver 7.0
xi) Snapshots are not available in ver 6.0.
xii) There are new commands such as ANALYSE and TRUNCATE
7. How do you check 1-1 relationship existing between two tables?
Ans=> Raghu This Question is little vague, If it is to find out the Relation
ship between two Blocks in a Form, Then using the follwing command you
will know.
If you still insist on tables, You can select from the user_constraints
view to find out the Foriegn Key constraint.
8. When you create Master-Detail Blocks, What are all triggers created?
Ans=> There are three triggers will be created by default such as
On-Check-Delete-Master (Block Level)
On-Clear-Details (Form Level)
On-Populate-Details (Block Level)
and it also creates following procedures to coordinate the m/d
Check_package_failure
Clear_all_master_details
Query_master_details
9. How do you join two tables ?
Ans=> This Question is also either vague or silly?
But any way i will try to answer it.
By using the Where Clause of the SELECT Statement we can Join the Tables
Following are the types of Joins such as Equi Join, Self Join and Outer Join
10.How do you avoid using indexes?
Ans=> There are two ways by which we can avoid using indexes by Oracle.
- By Hinting the SQL Statement we can instruct Oracle to use Full Table
Scan
- By using some function or calculation in the Select Statement Oracle
will avoid using Indexes. For e.g.,
Select * from emp where 1 * empno = empno;
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.