Installing SQL * Plus and Oracle Client 10g

In this post I will be demonstrating how to install Oracle Client 10g, along with SQL * Plus. I recently made a post about installing Oracle Database 11g, and we are currently in version 12c (some 2 years ago), but the latest version of SQL * Plus as an application was 10g. From the 11g version, the software binary was compiled without the GUI version, which lost some features and functionality (I didn't like it).

If you want to know how to install Oracle Database instead of Oracle Client, visit this link.

Oracle Client 10g Installation

1. Run the file setup.exe to start the installation.
2. 
3. In step 2, you must select the type of Oracle Client 10g installation. I always choose the Administrator option for DBA machines. If the Oracle Client is only needed to access the database, you can use the InstantClient option.
4. 
5. In step 3, you can set the Oracle Client installation path (or use the default).
6. 
7. Step 4 is where Oracle Universal Installer will validate the minimum requirements to proceed with the installation.
8. 
9. A summary of the chosen settings will be displayed on the screen.
10. 
11. Installation in progress.
12. 
13. After installation, Oracle Net configuration must be performed. As we are going to edit our tnsnames.ora and sqlnet.ora file manually, we can check the option “Run the typical configuration” and proceed.
14. 
15. End of Client Installation
16. 

Configuring Client to Database Connection

After performing the Oracle Client installation, we need to configure it so that it works correctly with the database:

1. Before we get started, we need to clarify 2 key concepts:
   • sqlnet.ora: Configuration file that allows you to set Oracle Client connection options, such as authentication form (SQLNET.AUTHENTICATION_SERVICES = (NTS)), instance name resolution form (NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)), default domain, etc. .
   • tnsnames.ora: Settings file that allows you to define the connection string of each instance you will access.
     Standard format: CustomizedName.Domain = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = machine_name.domain) (PORT = 1521))) (CONNECT_DATA = (SID = Oracle_ID_name)))
     Standard format with active loadbalance: Custom_Name.domain = (description = (address_list = (load_balance = yes) (address = (protocol = tcp) (host = machine_name.domain) (port = 1521))) (connect_data = (SID = Oracle_ID_name) (failover_mode = (failover_mode = ( type = select) (method = basic))))
2. Set the default directory to store client settings (tnsnames.ora and sqlnet.ora). For this we will create an environment variable in Windows. This will allow you to manually set the location where your tnsnames.ora file will be located, even if you have more than one version of Oracle Client installed on your machine, preventing each version of Oracle Client from using different settings. If you do not want to use this option, the default directory where the sqlnet.ora file is located and where you should create your tnsnames.ora is C: \ oracle \ product \ 10.2.0 \ client_1 \ NETWORK \ ADMIN \
3.  
4. Another way to configure this is to enter the Start Menu> My Computer> Right Button> Properties.
   After that, click on the option "Advanced system settings"> button "Environment Variables" and add the record on this screen
5. 
6. Create the file tnsnames.ora in the directory pointing to your server and database

SQL * Plus Configuration

After installing Oracle Client and setting environment variables, we can configure SQL * Plus correctly.

1. Create a shortcut to the SQL * Plus GUI, adding the / nolog parameter to the command line so that it does not ask for login and password when opening and in the “Start at” field, change the default address to the location where your scripts will be located .
2. 
3. Open the glogin.sql file, located in the C: \ oracle \ product \ 10.2.0 \ client_1 \ sqlplus \ admin \ directory, and add the following command at the end of the file so that SQL * Plus shows the username and the instance you connected:
4. set sqlprompt "_user'@'_connect_identifier> "
5. Create your connect.sql script as the example below:
   
   Transact-SQL

   set echo off conn dirceu/dirceu@&1 --- conn usuario/senha@instancia --- col HOST_NAME format a30 col ROLE format a20 col OBJECT_NAME format a35 col CODIGO_FONTE format a50000 col current_scn format 9999999999999999999999 SET SERVEROUTPUT ON SIZE 1000000 SET linesize 32765 SET long 1000000 SET pagesize 50000 SET verify on SET time on SET trims on ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS'; SELECT * FROM GV$INSTANCE order by instance_name;

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

   set echo off   conn dirceu/dirceu@&1 --- conn usuario/[email protected] --- col HOST_NAME format a30 col ROLE format a20 col OBJECT_NAME format a35 col CODIGO_FONTE format a50000 col current_scn format 9999999999999999999999   SET SERVEROUTPUT ON SIZE 1000000 SET linesize 32765 SET long 1000000 SET pagesize 50000 SET verify on SET time on SET trims on   ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS';   SELECT * FROM GV$INSTANCE order by instance_name;

6. Let's see how this makes life easier for Oracle DBA:
   
7. See some examples of scripts applied in practice in an SQL * Plus session and see how this powerful tool makes DBA life much easier> run_log.

To the next!