Logo F2FInterview

Oracle Database Interview Questions

Q   |   QA

Let's say the SPFile for the default instance is a binary file, and some settings are wrong in the SPFile, like SGA setting is bellow 20MB, how do you change a setting in the binary file? This seems to be a hard task, because the binary SPFile is not allowed to be edited manually. It needs to be updated by the server with instance started. But you can not start the instance because the SPFile has a wrong setting.

One way to solve the problem is to stop using the binary SPFile, and use a text version of the a parameter file to start the instance. Here is an example of how to use the backup copy (text version) of the parameter file for the default instance to start the instance:

>.insqlplus
Enter user-name: SYSTEM/globalguideline AS SYSDBA
Connected to an idle instance

Oracle server version information is stored in a table called: PRODUCT_COMPONENT_VERSION. You can use a simple SELECT statement to view the version information like this:

>.insqlplus
Enter user-name: SYSTEM/globalguideline AS SYSDBA
Connected to an idle instance

SQL> COL PRODUCT FORMAT A35
SQL> COL VERSION FORMAT A15
SQL> COL STATUS FORMAT A15
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;

 

PRODUCT                                                                    VERSION     STATUS

-----------------------------------                                          -----------         ----------

NLSRTL                                                                        10.2.0.1.0     Production

Oracle Database 10g Express Edition                    10.2.0.1.0     Product

PL/SQL                                                                          10.2.0.1.0     Production

TNS for 32-bit Windows:                                            10.2.0.1.0     Production

SQL*Plus is an interactive and batch query tool that is installed with every Oracle Database Server or Client installation. It has a command-line user interface, a Windows Graphical User Interface (GUI) and the iSQL*Plus web-based user interface.

SQL*Plus has its own commands and environment, and it provides access to the Oracle Database. It enables you to enter and execute SQL, PL/SQL, SQL*Plus and operating system commands to perform the following:

* Format, perform calculations on, store, and print from query results
* Examine table and object definitions
* Develop and run batch scripts
* Perform database administration

You can use SQL*Plus to generate reports interactively, to generate reports as batch processes, and to output the results to text file, to screen, or to HTML file for browsing on the Internet. You can generate reports dynamically using the HTML output facility of SQL*Plus, or using the dynamic reporting capability of iSQL*Plus to run a script from a web page. 

If you Oracle server or client installed on your windows system, you can start the command-line SQL*Plus in two ways:

1. Click Start > All Programs > Oracle ... > Start SQL Command Line. The SQL*Plus command window will show up with a message like this:

SQL*Plus: Release 10.2.0.1.0 - Production on Tue ...

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL>

2. Click Start > Run..., enter "cmd" and click OK. A Windows command window will show up. You can then use Windows commands to start the command-line SQL*Plus as shown in the tutorial exercise below:

>cd c:oraclexeapporacleproduct10.2.0server
>.insqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Tue ...

Copyright (c) 1982, 2005, Oracle. All rights reserved. 

Once SQL*Plus is started, you will get a SQL prompt like this: SQL>. This where you can enter commands for SQL*Plus to run.

To get help information at the SQL prompt, you can use the HELP command as shown in the following tutorial example:

SQL> HELP INDEX

Enter Help [topic] for help.
 

 

@             COPY         PAUSE       SHUTDOWN

 @@            DEFINE       PRINT       SPOOL

 /             DEL          PROMPT      SQLPLUS

 ACCEPT        DESCRIBE     QUIT        START

 APPEND        DISCONNECT   RECOVER     STARTUP

 ARCHIVE LOG   EDIT         REMARK      STORE

 ATTRIBUTE     EXECUTE      REPFOOTER   TIMING

 BREAK         EXIT         REPHEADER   TTITLE

...

 COMPUTE       LIST         SET         XQUERY

 CONNECT       PASSWORD     SHOW

SQL> HELP CONNECT

CONNECT
-------    

In order to link this F2FInterview's page as Reference on your website or Blog, click on below text area and pres (CTRL-C) to copy the code in clipboard or right click then copy the following lines after that paste into your website or Blog.

Get Reference Link To This Page: (copy below code by (CTRL-C) and paste into your website or Blog)
HTML Rendering of above code: