Below are some important Oracle Database interview questions which are asked in most MNC company interviews for beginners or professionals.
51. What To Do If DBA Lost the SYSTEM Password?
If the DBA lost the password of the SYSTEM user account, he/she can go to the Oracle server machine, and run SQL*Plus on server locally with the operating system authentication method to gain access to the database. The tutorial exercise below shows you how:
(Terminal server to the Oracle server machine)
(Start SQL*Plus)
SQL>CONNECT / AS SYSDBA
Connected.
SQL> ALTER USER SYSTEM IDENTIFIED BY ssap_lgg;
User altered.
Notice that the (/) in the CONNECT command tells SQL*Plus to use the current user on local operating system as the connection authentication method.
52. What Types of Commands Can Be Executed in SQL*Plus?
There are 4 types of commands you can run at the SQL*Plus command line prompt:
1. SQL commands - Standard SQL statements to be executed on target database on the Oracle server. For example: "SELECT * FROM ggl_faq;" is a SQL command.
2. PL/SQL commands - PL/SQL statements to be executed by the Oracle server. For example: "EXECUTE DBMS_OUTPUT.PUT_LINE('Welcome to www.atoztarget.com')" runs a PL/SQL command.
SQL*Plus commands - Commands to be executed by the local SQL*Plus program itself. For example: "SET NULL 'NULL'" is a SQL*Plus command.
OS commands - Commands to be executed by the local operating system. For example: "HOST dir" runs an operating system command on the local machine.
53. How To Run SQL Commands in SQL*Plus?
If you want to run a SQL command in SQL*Plus, you need to enter the SQL command in one or more lines and terminated with (;). The tutorial exercise below shows a good example:
SQL> SELECT 'Welcome!' FROM DUAL;
'WELCOME
--------
Welcome!
SQL> SELECT 'Welcome to atoztarget.com tutorials!'
2 FROM DUAL
3 ;
'WELCOMETOatoztarget.COMTUTORIALS!'
-----------------------------------
Welcome to atoztarget.com tutorials!
54. How To Run PL/SQL Statements in SQL*Plus?
If you want to run a single PL/SQL statement in SQL*Plus, you need to use the EXECUTE command as shown in the following tutorial example:
SQL> SET SERVEROUTPUT ON
SQL> EXECUTE DBMS_OUTPUT.PUT_LINE('Welcome to atoztarget!')
Welcome to atoztarget!
PL/SQL procedure successfully completed.
55. How To Change SQL*Plus System Settings?
SQL*Plus environment is controlled a big list of SQL*Plus system settings. You can change them by using the SET command as shown in the following list:
* SET AUTOCOMMIT OFF - Turns off the auto-commit feature.
* SET FEEDBACK OFF - Stops displaying the "27 rows selected." message at the end of the query output.
* SET HEADING OFF - Stops displaying the header line of the query output.
* SET LINESIZE 256 - Sets the number of characters per line when displaying the query output.
* SET NEWPAGE 2 - Sets 2 blank lines to be displayed on each page of the query output.
* SET NEWPAGE NONE - Sets for no blank lines to be displayed on each page of the query output.
* SET NULL 'null' - Asks SQL*Plus to display 'null' for columns that have null values in the query output.
* SET PAGESIZE 60 - Sets the number of lines per page when displaying the query output.
* SET TIMING ON - Asks SQL*Plus to display the command execution timing data.
* SET WRAP OFF - Turns off the wrapping feature when displaying query output.
56. How To Look at the Current SQL*Plus System Settings?
If you want to see the current values of SQL*Plus system settings, you can use the SHOW command as shown in the following tutorial exercise:
SQL> SHOW AUTOCOMMIT
autocommit OFF
SQL> SHOW HEADING
heading ON
SQL> SHOW LINESIZE
linesize 80
SQL> SHOW PAGESIZE
pagesize 14
SQL> SHOW FEEDBACK
FEEDBACK ON for 6 or more rows
SQL> SHOW TIMING
timing OFF
SQL> SHOW NULL
null ""
SQL> SHOW ALL
appinfo is OFF and set to "SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autorecovery OFF
autotrace OFF
blockterminator "." (hex 2e)
cmdsep OFF
colsep " "
compatibility version NATIVE
concat "." (hex 2e)
copycommit 0
COPYTYPECHECK is ON
define "&" (hex 26)
describe DEPTH 1 LINENUM OFF INDENT ON
echo OFF
57. What Are SQL*Plus Environment Variables?
Behaviors of SQL*Plus are also controlled a some environment variables predefined on the local operating system. Here are some commonly used SQL*Plus environment variables:
* ORACLE_HOME - The home directory where your Oracle client application is installed.
* PATH - A list of directories where SQL*Plus will search for executable or DLL files. PATH should include $ORACLE_HOMEbin.
* SQLPLUS - The directory where localization messages are stored. SQLPLUS should be set to $ORACLE_HOMEsqlplusmesg
* TNS_ADMIN - The directory where the connect identifier file, tnsnames.ora is located. TNS_ADMIN should be set to $ORACLE_HOME/network/admin.
58. How To Generate Query Output in HTML Format?
If you want your query output to be generated in HTML format, you can use the "SET MARKUP HTML ON" to turn on the HTML feature. The following tutorial exercise gives you a good example:
SQL> connect HR/retneclgg
SQL> SET MARKUP HTML ON
SQL> SELECT FIRST_NAME, LAST_NAME, HIRE_DATE
<br>
2 FROM EMPLOYEES WHERE FIRST_NAME LIKE 'Joh%';
<br>
<p>
<table border='1' width='90%' align='center' summary='Script output'>
<tr>
<th scope="col">
FIRST_NAME
</th>
<th scope="col">
LAST_NAME
</th>
<th scope="col">
HIRE_DATE
</th>
</tr>
<tr>
<td>
John
</td>
<td>
Seo
</td>
<td>
12-FEB-98
</td>
</tr>
<tr>
<td>
John
</td>
<td>
Russell
</td>
<td>
01-OCT-96
</td>
</tr>
</table>
<p>
59. What Is Output Spooling in SQL*Plus?
The output spooling a nice feature of the command-line SQL*Plus tool. If the spooling feature is turned on, SQL*Plus will send a carbon copy of the everything on your screen to a specified local file.
Output spooling is used mostly for quick dump of data to local files. Here are the commands to turn on and off output spooling in SQL*Plus:
* SPOOL fileName - Turning on output spooling with the specified file.
* SPOOL OFF - Turning off output spooling and close the spool file.
60. How To Save Query Output to a Local File?
Normally, when you run a SELECT statement in SQL*Plus, the output will be displayed on your screen. If you want the output to be saved to local file, you can use the "SPOOL fileName" command to specify a local file and start the spooling feature. When you are done with your SELECT statement, you need to close the spool file with the "SPOOL OFF" command. The following tutorial exercise gives you a good example:
SQL> connect HR/retneclgg
SQL> SET HEADING OFF
SQL> SET FEEDBACK OFF
SQL> SET LINESIZE 1000
SQL> SPOOL tempemployees.lst
SQL> SELECT * FROM EMPLOYEES;
......
SQL> SPOOL OFF
You should get all records in employees.lst with fixed length fields.
51. What To Do If DBA Lost the SYSTEM Password?
If the DBA lost the password of the SYSTEM user account, he/she can go to the Oracle server machine, and run SQL*Plus on server locally with the operating system authentication method to gain access to the database. The tutorial exercise below shows you how:
(Terminal server to the Oracle server machine)
(Start SQL*Plus)
SQL>CONNECT / AS SYSDBA
Connected.
SQL> ALTER USER SYSTEM IDENTIFIED BY ssap_lgg;
User altered.
Notice that the (/) in the CONNECT command tells SQL*Plus to use the current user on local operating system as the connection authentication method.
52. What Types of Commands Can Be Executed in SQL*Plus?
There are 4 types of commands you can run at the SQL*Plus command line prompt:
1. SQL commands - Standard SQL statements to be executed on target database on the Oracle server. For example: "SELECT * FROM ggl_faq;" is a SQL command.
2. PL/SQL commands - PL/SQL statements to be executed by the Oracle server. For example: "EXECUTE DBMS_OUTPUT.PUT_LINE('Welcome to www.atoztarget.com')" runs a PL/SQL command.
SQL*Plus commands - Commands to be executed by the local SQL*Plus program itself. For example: "SET NULL 'NULL'" is a SQL*Plus command.
OS commands - Commands to be executed by the local operating system. For example: "HOST dir" runs an operating system command on the local machine.
53. How To Run SQL Commands in SQL*Plus?
If you want to run a SQL command in SQL*Plus, you need to enter the SQL command in one or more lines and terminated with (;). The tutorial exercise below shows a good example:
SQL> SELECT 'Welcome!' FROM DUAL;
'WELCOME
--------
Welcome!
SQL> SELECT 'Welcome to atoztarget.com tutorials!'
2 FROM DUAL
3 ;
'WELCOMETOatoztarget.COMTUTORIALS!'
-----------------------------------
Welcome to atoztarget.com tutorials!
54. How To Run PL/SQL Statements in SQL*Plus?
If you want to run a single PL/SQL statement in SQL*Plus, you need to use the EXECUTE command as shown in the following tutorial example:
SQL> SET SERVEROUTPUT ON
SQL> EXECUTE DBMS_OUTPUT.PUT_LINE('Welcome to atoztarget!')
Welcome to atoztarget!
PL/SQL procedure successfully completed.
55. How To Change SQL*Plus System Settings?
SQL*Plus environment is controlled a big list of SQL*Plus system settings. You can change them by using the SET command as shown in the following list:
* SET AUTOCOMMIT OFF - Turns off the auto-commit feature.
* SET FEEDBACK OFF - Stops displaying the "27 rows selected." message at the end of the query output.
* SET HEADING OFF - Stops displaying the header line of the query output.
* SET LINESIZE 256 - Sets the number of characters per line when displaying the query output.
* SET NEWPAGE 2 - Sets 2 blank lines to be displayed on each page of the query output.
* SET NEWPAGE NONE - Sets for no blank lines to be displayed on each page of the query output.
* SET NULL 'null' - Asks SQL*Plus to display 'null' for columns that have null values in the query output.
* SET PAGESIZE 60 - Sets the number of lines per page when displaying the query output.
* SET TIMING ON - Asks SQL*Plus to display the command execution timing data.
* SET WRAP OFF - Turns off the wrapping feature when displaying query output.
56. How To Look at the Current SQL*Plus System Settings?
If you want to see the current values of SQL*Plus system settings, you can use the SHOW command as shown in the following tutorial exercise:
SQL> SHOW AUTOCOMMIT
autocommit OFF
SQL> SHOW HEADING
heading ON
SQL> SHOW LINESIZE
linesize 80
SQL> SHOW PAGESIZE
pagesize 14
SQL> SHOW FEEDBACK
FEEDBACK ON for 6 or more rows
SQL> SHOW TIMING
timing OFF
SQL> SHOW NULL
null ""
SQL> SHOW ALL
appinfo is OFF and set to "SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autorecovery OFF
autotrace OFF
blockterminator "." (hex 2e)
cmdsep OFF
colsep " "
compatibility version NATIVE
concat "." (hex 2e)
copycommit 0
COPYTYPECHECK is ON
define "&" (hex 26)
describe DEPTH 1 LINENUM OFF INDENT ON
echo OFF
57. What Are SQL*Plus Environment Variables?
Behaviors of SQL*Plus are also controlled a some environment variables predefined on the local operating system. Here are some commonly used SQL*Plus environment variables:
* ORACLE_HOME - The home directory where your Oracle client application is installed.
* PATH - A list of directories where SQL*Plus will search for executable or DLL files. PATH should include $ORACLE_HOMEbin.
* SQLPLUS - The directory where localization messages are stored. SQLPLUS should be set to $ORACLE_HOMEsqlplusmesg
* TNS_ADMIN - The directory where the connect identifier file, tnsnames.ora is located. TNS_ADMIN should be set to $ORACLE_HOME/network/admin.
58. How To Generate Query Output in HTML Format?
If you want your query output to be generated in HTML format, you can use the "SET MARKUP HTML ON" to turn on the HTML feature. The following tutorial exercise gives you a good example:
SQL> connect HR/retneclgg
SQL> SET MARKUP HTML ON
SQL> SELECT FIRST_NAME, LAST_NAME, HIRE_DATE
<br>
2 FROM EMPLOYEES WHERE FIRST_NAME LIKE 'Joh%';
<br>
<p>
<table border='1' width='90%' align='center' summary='Script output'>
<tr>
<th scope="col">
FIRST_NAME
</th>
<th scope="col">
LAST_NAME
</th>
<th scope="col">
HIRE_DATE
</th>
</tr>
<tr>
<td>
John
</td>
<td>
Seo
</td>
<td>
12-FEB-98
</td>
</tr>
<tr>
<td>
John
</td>
<td>
Russell
</td>
<td>
01-OCT-96
</td>
</tr>
</table>
<p>
59. What Is Output Spooling in SQL*Plus?
The output spooling a nice feature of the command-line SQL*Plus tool. If the spooling feature is turned on, SQL*Plus will send a carbon copy of the everything on your screen to a specified local file.
Output spooling is used mostly for quick dump of data to local files. Here are the commands to turn on and off output spooling in SQL*Plus:
* SPOOL fileName - Turning on output spooling with the specified file.
* SPOOL OFF - Turning off output spooling and close the spool file.
60. How To Save Query Output to a Local File?
Normally, when you run a SELECT statement in SQL*Plus, the output will be displayed on your screen. If you want the output to be saved to local file, you can use the "SPOOL fileName" command to specify a local file and start the spooling feature. When you are done with your SELECT statement, you need to close the spool file with the "SPOOL OFF" command. The following tutorial exercise gives you a good example:
SQL> connect HR/retneclgg
SQL> SET HEADING OFF
SQL> SET FEEDBACK OFF
SQL> SET LINESIZE 1000
SQL> SPOOL tempemployees.lst
SQL> SELECT * FROM EMPLOYEES;
......
SQL> SPOOL OFF
You should get all records in employees.lst with fixed length fields.
More Questions & Answers :-
No comments:
Post a Comment