Set - 1

Question 61 :

How are extents allocated to a segment? (for DBA)

Answer :

Oracle8 and above rounds off extents to a multiple of 5 blocks when more than 5 blocks are requested. If one requests 16K or 2 blocks (assuming a 8K block size), Oracle doesn't round it up to 5 blocks, but it allocates 2 blocks or 16K as requested. If one asks for 8 blocks, Oracle will round it up to 10 blocks.
Space allocation also depends upon the size of contiguous free space available. If one asks for 8 blocks and Oracle finds a contiguous free space that is exactly 8 blocks, it would give it you. If it were 9 blocks, Oracle would also give it to you. Clearly Oracle doesn't always round extents to a multiple of 5 blocks.
The exception to this rule is locally managed tablespaces. If a tablespace is created with local extent management and the extent size is 64K, then Oracle allocates 64K or 8 blocks assuming 8K-block size. Oracle doesn't round it up to the multiple of 5 when a tablespace is locally managed.


Question 62 :

Can one rename a database user (schema)? (for DBA)

Answer :

No, this is listed as Enhancement Request 158508. Workaround:
Do a user-level export of user A
create new user B
Import system/manager fromuser=A touser=B
Drop user A


Question 63 :

Define Transaction ?

Answer :

A Transaction is a logical unit of work that comprises one or more SQL statements executed by a single user.


Question 64 :

What is Read-Only Transaction ?

Answer :

A Read-Only transaction ensures that the results of each query executed in the transaction are consistant with respect to the same point in time.


Question 65 :

What is a deadlock ? Explain .

Answer :

Two processes wating to update the rows of a table which are locked by the other process then deadlock arises. In a database environment this will often happen because of not issuing proper row lock commands. Poor design of front-end application may cause this situation and the performance of server will reduce drastically.
These locks will be released automatically when a commit/rollback operation performed or any one of this processes being killed externally.