Explore topic-wise InterviewSolutions in .

The use of Roles are:

• REDUCED GRANTING OF PRIVILEGES - Rather than EXPLICITLY granting the same set of privileges to many USERS a database administrator can grant the privileges for a GROUP of related users granted to a role and then grant only the role to each member of the group.
• DYNAMIC PRIVILEGE MANAGEMENT - When the privileges of a group must change, only the privileges of the role need to be modified. The security domains of all users granted the group's role automatically reflect the changes made to the role.
• SELECTIVE AVAILABILITY OF PRIVILEGES - The roles granted to a user can be selectively enable (available for use) or disabled (not available for use). This allows specific control of a user's privileges in any given situation.
• APPLICATION AWARENESS - A database application can be designed to automatically enable and disable selective roles when a user attempts to use the application.

The use of Roles are:

• SQL * DBA - This allows DBA to monitor and CONTROL an ORACLE database.
• SQL * Loader - It loads data from standard operating system files (FLAT files) into ORACLE database tables.
• Export (EXP) and IMPORT (imp) utilities ALLOW you to move existing data in ORACLE format to and from ORACLE database.

A database instance (SERVER) is a set of memory STRUCTURE and BACKGROUND PROCESSES that access a set of database files.

The PROCESS can be shared by all users. The memory structure that are used to store most queried data from database. This helps up to improve database performance by decreasing the amount of I/O performed against data file.

A database instance (Server) is a set of memory structure and background processes that access a set of database files.

The process can be shared by all users. The memory structure that are used to store most queried data from database. This helps up to improve database performance by decreasing the amount of I/O performed against data file.

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.

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.

A TRANSACTION is a LOGICAL UNIT of WORK that comprises one or more SQL statements executed by a single user.

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

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

No, this is listed as Enhancement Request 158508. Workaround:

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.

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.

ROLLBACK retracts any of the CHANGES RESULTING from the SQL statements in the TRANSACTION.

ROLLBACK retracts any of the changes resulting from the SQL statements in the transaction.

The DIFFERENT APPROACHES used by Optimizer in CHOOSING an EXECUTION PLAN are:

• Rule-based
• Cost-based.

The different approaches used by Optimizer in choosing an execution plan are:

Oracle recommends that your database block size MATCH, or be multiples of your OPERATING system block size. ONE can use smaller block sizes, but the performance cost is significant. Your choice should DEPEND on the type of application you are running. If you have many small transactions as with OLTP, use a smaller block size. With fewer but larger transactions, as with a DSS application, use a larger block size. If you are using a volume manager, consider your "operating system block size" to be 8K. This is because volume manager products use 8K blocks (and this is not configurable).

Oracle recommends that your database block size match, or be multiples of your operating system block size. One can use smaller block sizes, but the performance cost is significant. Your choice should depend on the type of application you are running. If you have many small transactions as with OLTP, use a smaller block size. With fewer but larger transactions, as with a DSS application, use a larger block size. If you are using a volume manager, consider your "operating system block size" to be 8K. This is because volume manager products use 8K blocks (and this is not configurable).

One can create and MODIFY Oracle databases using the Oracle "dbca" (Database Configuration Assistant) utility. The dbca utility is located in the $ORACLE_HOME/bin directory. The Oracle Universal Installer (oui) normally starts it after installing the database server software.

One can also create databases manually using scripts. This option, however, is falling out of fashion, as it is quite involved and error prone. LOOK at this example for creating and Oracle 9i database:

One can create and modify Oracle databases using the Oracle "dbca" (Database Configuration Assistant) utility. The dbca utility is located in the $ORACLE_HOME/bin directory. The Oracle Universal Installer (oui) normally starts it after installing the database server software.

One can also create databases manually using scripts. This option, however, is falling out of fashion, as it is quite involved and error prone. Look at this example for creating and Oracle 9i database:

The OPTIMIZER_MODE initialization parameter Statistics in the DATA Dictionary the OPTIMIZER_GOAL parameter of the ALTER SESSION command HINTS in the statement.

The OPTIMIZER_MODE initialization parameter Statistics in the Data Dictionary the OPTIMIZER_GOAL parameter of the ALTER SESSION command hints in the statement.

A COLUMN DEFINED as UNIQUE can contain Nulls while a column defined as PRIMARY KEY can't contain Nulls.

A column defined as UNIQUE can contain Nulls while a column defined as PRIMARY KEY can't contain Nulls.

A Control file records the PHYSICAL STRUCTURE of the database. It contains the following information.

• Database Name
• NAMES and locations of a database's files and redolog files.
• TIME STAMP of database creation.

A Control file records the physical structure of the database. It contains the following information.

Yes.

Yes.

A mirrored on-line redo log consists of COPIES of on-line redo log files physically located on SEPARATE disks, CHANGES made to one MEMBER of the group are made to all MEMBERS.

A mirrored on-line redo log consists of copies of on-line redo log files physically located on separate disks, changes made to one member of the group are made to all members.

Each DATABASES LOGICALLY DIVIDED into one or more tablespaces one or more data FILES are explicitly created for each tablespace.

Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace.

An Oracle data file is a big UNIT of physical storage in the OS file system. One or MANY Oracle data FILES are organized together to provide physical storage to a single Oracle TABLESPACE.

An Oracle data file is a big unit of physical storage in the OS file system. One or many Oracle data files are organized together to provide physical storage to a single Oracle tablespace.

An Oracle database is a COLLECTION of DATA treated as a BIG UNIT in the database server.

An Oracle database is a collection of data treated as a big unit in the database server.

Oracle is a company. Oracle is also a database server, which manages data in a very structured way. It allows users to store and RETRIEVE related data in a multiuser ENVIRONMENT so that many users can concurrently access the same data. All this is accomplished while delivering high PERFORMANCE. A database server also PREVENTS unauthorized access and PROVIDES efficient solutions for failure recovery.

Oracle is a company. Oracle is also a database server, which manages data in a very structured way. It allows users to store and retrieve related data in a multiuser environment so that many users can concurrently access the same data. All this is accomplished while delivering high performance. A database server also prevents unauthorized access and provides efficient solutions for failure recovery.