Business Intelligence (BI) - ERP - Dashboards

Q: What is the maximum tablespace size?

A: The limitation is dependant on the limit the operating system places on
the filesize and the limitation of the number of datafiles the database
allows. For example some Unix boxes have a limit of 2 GIGA per Datafile,
then the largest file you can create for a tablespace on this operating system
is 2Gig.

Q: When using clusters, we have to explicitly create a Cluster Index BEFORE
inserting into a clustered table. Why do we need the Index ? Is it for storage
mgmt ? or is it for performance reasons ?

A: We need this index for both storage management and performance. When
you insert one row with a particular cluster key value Oracle needs to know
where it went, When a second row of the same key value needs to be inserted,
we have to put it into the same block (if possible). Only the index allows
us to know that location. Otherwise, we’d have to do full cluster scans for
every insert (and probably have to lock the table in the interim to ensure that
somebody didn’t insert another identically keyed row while we were scanning).

Q: What are transaction entries? What are they used for?

A: Transaction entries, are used for control of the transaction in every data
block, that the user process is modifing a row, or rows. This mean that a
transaction only gets a slot in the transaction table for data block modified.

For example, Process A wants to do an update operation of 100 rows that are
disperse in 5 data-blocks. Oracle need to obtain only a slot in the
transaction table for every block modified in the transaction, which is 5.

The first thing Process A needs to do is to allocate a transaction entry, it
then locks it and writes dynamic information about the transaction. If the
process finds that some of the rows t wants to update are locked, it releases
the lock on the transact space). Once, the transactions does commit or
rollback the entrys are free for new transactions or inserts and deletes.

The problem of a low INITRANS parameter, in a high transactional table, is
contention if a process searches the transaction entry, then for space to
allocate one and doesn’t find either. It will then wait while an entry is
freed.

The default INITRANS for a table is 1, for an index is 2. Every transaction
entry uses 23 bytes +-. This is operating system dependent, refer to
v$type_size view for your operating system value..

Q: What is the commit point site?

A: The commit point site is the site with the highest commit point strength
init.ora parameter. It is usually the most critical site that can not afford
collisions in case of an in-doubt transaction. If one of the nodes is a
non-2phase one (like, SQL*Connect/DB2), then THAT node becomes the commit point
regardless of the init.ora parameter.

Q: What is the difference between remote transaction and distributed
transaction? Which requires a two-phased commit??

A: A remote transaction contains one or more statements which ALL reference
the same remote database. The transaction processing is all done on the
Remote Database Server.

A distributed transaction contains statements that modify data in TWO or more
distinct databases. Some communications protocol is necessary such as TCP/IP.
SQL*Net is most likely (or is likely) needed to allow communication between
the two or more different instances. An Update, Delete, or Insert takes place
on more than one tables, on more than one Remote Servers.

A distributed transaction requires a two-phase commit.

Q: Is there a way to identify all users with active transactions and the
rollback segment each transaction is using? Within Oracle7??

A: The way to tell if users have active transactions, is to look at the locks
that are on the system. Whenever a user has an active transaction in a
rollback segment, he will also have an exclusive TX lock. The information
with the TX lock gives the rollback segment name.

Filed under: DBA (DataBase Administrator), ERP, Interview Questions(& answers), Oracle Apps, PL/SQL, SQL

Free Dashboards