Oracle Database

Based on Oracle Web site: Oracle Database 10g Express Edition (Oracle Database XE) is an entry-level, small-footprint database based on the Oracle Database 10g Release 2 code base that's free to develop, deploy, and distribute; fast to download; and simple to administer. Oracle Database XE is a great starter database for:

* Developers working on PHP, Java, .NET, and Open Source applications

* DBAs who need a free, starter database for training and deployment

* Independent Software Vendors (ISVs) and hardware vendors who want a starter database to distribute free of charge

* Educational institutions and students who need a free database for their curriculum

If your default instance is in trouble, and you can not use the normal login process to reach the server, you can use a special login to log into the server without any instance. Here is how to use SQL*Plus to log in as as a system BDA:

>cd (OracleXE home directory)

>.instartdb

>.insqlplus

Enter user-name: SYSTEM/globalguideline AS SYSDBA

Connected to an idle instance

SQL> show instance

instance "local"

The trick is to put user name, password and login options in a single string as the user name. "AS SYSDBA" tells the server to not start any instance, and connect the session the idle instance.

Log in as SYSDBA is very useful for performing DBA tasks.

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.

In order to connect a SQL*Plus session to an Oracle server, you need to:

1. Obtain the connection information from the Oracle server DBA.

2. Define a new "connect identifier" called "ggl_XE" in your tnsnames.org file with the given connection information.

3. Run the CONNECT command in 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.

SQL> CONNECT ggl/retneclgg@ggl_XE;

Connected.

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE

---------

05-MAR-06

Of course, you will get an error, if you use a wrong connect identifier. Here is an example of how SQL*Plus react to a wrong connect identifier:

SQL> CONNECT ggl/retneclgg@WRONG;

ERROR:

ORA-12154: TNS:could not resolve the connect identifier

specified

Warning: You are no longer connected to ORACLE.

What you need to do in this case:

* Check the CONNECT command to make sure that the connect identifier is entered correctly.

* Check the tnsnames.ora file to make sure that the connect identifier is defined correctly.

* Check the tnsnames.ora file to make sure that there is no multiple definitions of the same connect identifier.

* Check your files system to see if you have multiple copies of tnsnames.ora in different Oracle home directories, because you installed multiple versions of Oracle. If you do have multiple copies, make sure your SQL*Plus session is picking up the correct copy of tnsnames.ora.