Explore topic-wise InterviewSolutions in .

YES, you can USE the exp or imp utilities to import the objects from Oracle VERSION 7.3 to 9I.

Yes, you can use the exp or imp utilities to import the objects from Oracle version 7.3 to 9i.

Bit-map INDEX should be CREATED on this COLUMN.

Bit-map index should be created on this column.

You can BUILD bit-map index but it may not be USED in the Online TRANSACTION Processing (OLTP) ENVIRONMENT because it is not selective and may not add value to query performance. Therefore, practically in OLTP APPLICATION, you should not build an index on this table.

You can build bit-map index but it may not be used in the Online Transaction Processing (OLTP) environment because it is not selective and may not add value to query performance. Therefore, practically in OLTP application, you should not build an index on this table.

A PL/SQL code block has an EXCEPTION section, which can be used to capture and handle exception. The section begins with the KEYWORD EXCEPTION. You can capture an exception by SPECIFYING its NAME and then STATE appropriate action to handle the exception.

A PL/SQL code block has an exception section, which can be used to capture and handle exception. The section begins with the keyword EXCEPTION. You can capture an exception by specifying its name and then state appropriate action to handle the exception.

Whenever you execute an SQL statement, Oracle performs a number of tasks behind the scenes, such as PRIVILEGE CHECKS and object existence checks. It needs to execute MULTIPLE SQL statements to perform those tasks. These SQLs are called recursive SQLs.

Whenever you execute an SQL statement, Oracle performs a number of tasks behind the scenes, such as privilege checks and object existence checks. It needs to execute multiple SQL statements to perform those tasks. These SQLs are called recursive SQLs.

There are following two TYPES of cursors:

•  Implicit cursor —Created by Oracle for-each individual SQL. If you use an implicit cursor, Oracle performs the OPEN, fetch, and CLOSE operations automatically.
•  Explicit cursor —Used to fetch and manipulate multiple rows. As the name suggests, explicit cursor is specified explicitly. It is DECLARED, opened, fetched, and closed specifically. It is used with loop structures.

There are following two types of cursors:

You can use hierarchical QUERY with the sys_connect_by_path FUNCTION in Oracle 9i and above to PIVOT the RESULT.

You can use hierarchical query with the sys_connect_by_path function in Oracle 9i and above to pivot the result.

ORACLE has a specific VALUE called null which is neither EQUAL nor not equal to any value. In addition, null is neither true nor false.
A value can be CHECKED for null using SPECIAL operator called IS NULL or IS NOT NULL

Oracle has a specific value called null which is neither equal nor not equal to any value. In addition, null is neither true nor false.
A value can be checked for null using special operator called IS NULL or IS NOT NULL

EXECUTE IMMEDIATE is a STATEMENT required to process most DYNAMIC SQL statements.

EXECUTE IMMEDIATE is a statement required to process most dynamic SQL statements.

• A database link is created to access a remote database from a local database. One requirement to CREATE a database link is that the database server should be able to CONNECT to the remote database.
• A PRIVATE database link is available to the owner of the link while a public database link is available to all the users.
• A fixed USER database link is available only to the user specified in the link.

You should GRANT the CONNECT PERMISSION to the USER.

You should grant the CONNECT permission to the user.

The TRIGGERS will be FIRED N+1 number of times. Row LEVEL TRIGGER will be fired n number of times, once for each row-level trigger and the statement-level trigger will be fired once.

The triggers will be fired N+1 number of times. Row level trigger will be fired n number of times, once for each row-level trigger and the statement-level trigger will be fired once.

These triggers are defined on system-level. The triggers are fired at system-level events, such as logon, logoff, startup, and DDL, which are explained as follows:

•  Database startup triggers —Refer to the triggers that are fired at database startup. These can be used to automate certain action that you want to perform every time at startup.
•  Logon triggers —Refer to the triggers that are fired at logon. You can use these TRIGGER to generate AUDIT information related to logon.
•  Logoff triggers —Refer to the triggers that are fired at logoff time. You can include certain session closure related activities in these triggers.
•  Server error triggers —Refer to the triggers that are fired at server error. These triggers can be used to log error details and send email NOTIFICATION.
•  DDL triggers --Refer to the triggers that are fired at DDL operations and can be used to audit DDL CHANGES.

These triggers are defined on system-level. The triggers are fired at system-level events, such as logon, logoff, startup, and DDL, which are explained as follows:

The DDL trigger EXECUTES EVERY time a DDL STATEMENT is executed. These are associated with a database or schema.

The DDL trigger executes every time a DDL statement is executed. These are associated with a database or schema.

A ROW trigger is FIRED each TIME the table is affected by the triggering statement. It is useful in case the code in the trigger depends on DATA provided by the triggering statement or rows that are affected.

A row trigger is fired each time the table is affected by the triggering statement. It is useful in case the code in the trigger depends on data provided by the triggering statement or rows that are affected.

Statement-level trigger is a trigger that EXECUTES once for a statement REGARDLESS of the number of rows affected by the triggering event. It is FIRED when a BULK insert (FORALL insert) is performed.

Statement-level trigger is a trigger that executes once for a statement regardless of the number of rows affected by the triggering event. It is fired when a bulk insert (FORALL insert) is performed.

A DATABASE TRIGGER is event driven program. You do not NEED to INVOKE a trigger. It is fired whenever triggering event happens.

A database trigger is event driven program. You do not need to invoke a trigger. It is fired whenever triggering event happens.

MATERIALIZED views are also CALLED snapshot. They represent a state of data at a specific point in time. These are generally used in warehouse application to avoid commonly used data analysis by storing analyzed data. UNLIKE NORMAL views, they actually STORE data and need to be refreshed.

Materialized views are also called snapshot. They represent a state of data at a specific point in time. These are generally used in warehouse application to avoid commonly used data analysis by storing analyzed data. Unlike normal views, they actually store data and need to be refreshed.

CONDITIONAL compilation allows you to compile code conditionally based on COMPILER directives. It is HELPFUL when you need to have DIFFERENT code based on certain CONDITIONS.

Conditional compilation allows you to compile code conditionally based on compiler directives. It is helpful when you need to have different code based on certain conditions.

Partitioning is USED to divide large tables into manageable partitions based on different CRITERIA, such as list, HASH, or range.
Oracle TREATS a partition similar to a table. Therefore, partitioning allows you to operate on one partition at a TIME without affecting the application.

Partitioning is used to divide large tables into manageable partitions based on different criteria, such as list, hash, or range.
Oracle treats a partition similar to a table. Therefore, partitioning allows you to operate on one partition at a time without affecting the application.

A composite index can SUPPORT 32 columns as LONG as total size of the columns does not exceed DB_BLOCK_SIZE.

A composite index can support 32 columns as long as total size of the columns does not exceed DB_BLOCK_SIZE.

The umask COMMAND CONTROLS the default file PERMISSIONS when FILES are CREATED.

The umask command controls the default file permissions when files are created.

HUGE page memory is used for SGA if you have sufficient space in the huge page memory, which cannot be paged out; and THEREFORE, it IMPROVES the performance of ORACLE.

Huge page memory is used for SGA if you have sufficient space in the huge page memory, which cannot be paged out; and therefore, it improves the performance of Oracle.

The split command can be used to split a file in UNIX; for EXAMPLE, split -I 2000 filetosplit.txt splittedfile.txt.

The split command can be used to split a file in UNIX; for example, split -I 2000 filetosplit.txt splittedfile.txt.

You can get the details of SMON PROCESS by using the PS -EF | grep smon command.

You can get the details of SMON process by using the ps -ef | grep smon command.

You can use the host command to EXECUTE the UNIX commands from SQL PLUS.

You can use the host command to execute the UNIX commands from SQL plus.

The cut command is USED to EXTRACT sections from each line of INPUT.

The cut command is used to extract sections from each line of input.

The :<linenumber> COMMAND can be USED to go to a specific line of a FILE USING the vi EDITOR.

The :<linenumber> command can be used to go to a specific line of a file using the vi editor.

The :G/^$/d COMMAND can be USED to delete blank lines using the VI editor.

The :g/^$/d command can be used to delete blank lines using the vi editor.

You can use the RENICE command to change the priority of a UNIX process with the following RESTRICTIONS:

• A user can only change the nice value of processes, which they own
• A user cannot start processes with nice values less than 20
• A user cannot lower the nice values of their processes after they have raised them
• ROOT has FULL access to the renice command

You can use the renice command to change the priority of a UNIX process with the following restrictions:

You can USE the KILL COMMAND to kill a PROCESS in UNIX.

You can use the kill command to kill a process in UNIX.

You can USE the ps command to SEE the currently RUNNING processes in UNIX.

You can use the ps command to see the currently running processes in UNIX.

You can USE the IPCS - m COMMAND.

You can use the ipcs - m command.

You can use the ipcs -m UNIX command to CHECK the memory segments that are ACQUIRED by ORACLE instances.

You can use the ipcs -m UNIX command to check the memory segments that are acquired by Oracle instances.

DISM in Solaris is similar to huge pages in Linux. It PROVIDES larger CHUNKS of MEMORY for SYSTEM Global Area (SGA) to avoid paging. It is very helpful to use DISM for SGA to improve the PERFORMANCE of Oracle database. DISM can support up to 4 MB page size.

DISM in Solaris is similar to huge pages in Linux. It provides larger chunks of memory for System Global Area (SGA) to avoid paging. It is very helpful to use DISM for SGA to improve the performance of Oracle database. DISM can support up to 4 MB page size.

Swap PARTITION should be:

•  Equal to the size of RAM, if your RAM is LESS than 1G
•  HALF the size of RAM for RAM SIZES from 2G to 4G
•  2G for RAM sizes more than 4G

Swap partition should be:

SHMMAX is the MAXIMUM size of a shared MEMORY SEGMENT on a Linux SYSTEM.

SHMMAX is the maximum size of a shared memory segment on a Linux system.

SAR is a utility to display RESOURCE usage on the UNIX system, such as CPU activity, disk, and memory usage. There are a number of switches or options that can be used with SAR; for example, -a is used for all data and -B is used for buffer activity. THREE of the main FLAGS that can be used with SAR are given as FOLLOWS:

•  sar -u —Shows CPU activity
•  sar -w— Shows swapping activity
•  sar -b —Shows buffer activity

SAR is a utility to display resource usage on the UNIX system, such as CPU activity, disk, and memory usage. There are a number of switches or options that can be used with SAR; for example, -a is used for all data and -b is used for buffer activity. Three of the main flags that can be used with SAR are given as follows:

The df -K or du -sk COMMANDS can be used to check the free HARD DISK space in Linux.

The df -k or du -sk commands can be used to check the free hard disk space in Linux.

VIRTUAL memory statistics are available through the VMSTAT COMMAND.

Virtual memory statistics are available through the vmstat command.

The /etc/oratab FILE CONTAINS an entry for each instance running on the server. The last CHARACTER of that entry indicates whether the instance should be started and SHUTDOWN automatically. You can set the VALUE to Y for the SID to automatically start and shutdown the specific instance.

The /etc/oratab file contains an entry for each instance running on the server. The last character of that entry indicates whether the instance should be started and shutdown automatically. You can set the value to Y for the SID to automatically start and shutdown the specific instance.

You can CHECK the /etc/oratab FILE on a server to find out all the Oracle INSTANCES RUNNING on that server.

You can check the /etc/oratab file on a server to find out all the Oracle instances running on that server.

FOLLOWING are the steps in INSTALLATION of Oracle SOFTWARE on UNIX:

•  Set up DISK
•  Set up kernel parameters
•  Run the orainst command

Following are the steps in installation of Oracle software on UNIX:

The SHMMAX &AMP; SHMMNI UNIX kernel PARAMETERS affect the INSTALLATION of ORACLE.

The SHMMAX & SHMMNI UNIX kernel parameters affect the installation of Oracle.

A STRING in a FILE can be replaced by using the %s/<OLD string&GT;/<new string>/G command.

A string in a file can be replaced by using the %s/<old string>/<new string>/g command.

Following PRIVILEGES are available on the UNIX DIRECTORY or file:

•  Read —ALLOWS you to VIEW and list the directory or file contents.
•  WRITE —Allows you to create, edit, and delete files and subdirectories in the directory.
•  Execute —Gives you the previous read or write permissions. It also allows you to change into the directory and execute programs or shells from the directory.

Following privileges are available on the UNIX directory or file:

You can use the nohup command to EXECUTE a UNIX command that will continue running even after you log out. Normally when you log out, or your session terminates unexpectedly, the SYSTEM kills all the PROCESSES you have started. In this case, you can use the nohup command, which allows you to RUN the command, process, or shell SCRIPT that can continue running in the background after you log out from a shell.

You can use the nohup command to execute a UNIX command that will continue running even after you log out. Normally when you log out, or your session terminates unexpectedly, the system kills all the processes you have started. In this case, you can use the nohup command, which allows you to run the command, process, or shell script that can continue running in the background after you log out from a shell.

The LS -ltra command is used to LIST the FILES in the UNIX directory to list the hidden files.

The ls -ltra command is used to list the files in the UNIX directory to list the hidden files.

The DF -lk command is USED to DISPLAY space USAGE on the UNIX system.

The df -lk command is used to display space usage on the UNIX system.

The OFA standard is a set of configuration guidelines CREATED to ensure FAST and reliable Oracle databases that require little maintenance. It is designed to enable the following:

• Organize large AMOUNTS of complicated software and data on disk, to avoid device bottlenecks and poor performance
• Facilitate routine administrative TASKS, such as software and data backup, which are often vulnerable to data corruption
• Facilitate switching between multiple Oracle databases
• Adequately manage and administer database growth
• Help eliminate fragmentation of free SPACE in the data dictionary, isolate other fragmentation, and minimize resource contention

The OFA standard is a set of configuration guidelines created to ensure fast and reliable Oracle databases that require little maintenance. It is designed to enable the following: