About ORACLE_HOME

In Oracle installation guides and scripts, you will often come across the term ORACLE_HOME. In fact, we used it on this site a couple of posts back, in the script to install SCOTT schema in Oracle.

This post will cover:

• the meaning and purpose of ORACLE_HOME
• how to check if ORACLE_HOME is set
• how to set ORACLE_HOME
• how to unset, change or delete ORACLE_HOME

What is ORACLE_HOME?

ORACLE_HOME is a directory in the file system where the Oracle software is installed. The path to this directory is stored in an environment variable.

The value of ORACLE_HOME will vary according to where you have installed Oracle. You could have installed in under C:\ or C:\Softwares or under D:\.

However, the file structure under ORACLE_HOME is fixed for any installation. For example, the location of sqlplus.exe is always at ORACLE_HOME\bin, and tnsnames.ora is always to be found at ORACLE_HOME\network\admin.

This is why installation guides and scripts use ORACLE_HOME when they mention files under the Oracle installation, as it provides a relative path to the actual location of the file independent of where Oracle is installed on your machine.


How to check if ORACLE_HOME is set

The Oracle installation might not set ORACLE_HOME automatically, in which case you have to set it yourself.

First – how to find out if ORACLE_HOME is set or not?

On Windows: On command prompt, type D:\>echo %ORACLE_HOME%. If this gives you the directory path, as in the code snippet below, then that means ORACLE_HOME is set.

1 2	D:\>echo %ORACLE_HOME% C:\oraclexe\app\oracle\product\10.2.0\server

If ORACLE_HOME is not set, the output will simply give back %ORACLE_HOME%, as below. This means you need to set the value, which we’ll see how to in the next section.

1 2	D:\>echo %ORACLE_HOME% %ORACLE_HOME%

On Unix/Linux: type

1	env | grep ORACLE_HOME

. As with Windows, the output will indicate if ORACLE_HOME is set already or needs to be set by you.

TIP: If you run a script that uses ORACLE_HOME but the ORACLE_HOME is not defined or incorrectly set, the output will give the error as below:

1 2	SQL> @%ORACLE_HOME%\RDBMS\ADMIN\SCOTT.sql SP2-0310: unable to open file "%ORACLE_HOME%\RDBMS\ADMIN\SCOTT.sql"

That’s possibly another indication that your ORACLE_HOME might need to be fixed.


How to set ORACLE_HOME

For the current runtime session, you can set ORACLE_HOME with a single command:

On Windows:

1 2 3 4 5 6

D:\>set ORACLE_HOME=C:\oraclexe\app\oracle\product\10.2.0\server   D:\>echo %ORACLE_HOME% C:\oraclexe\app\oracle\product\10.2.0\server   D:\>

On Unix/Linux:

1	export ORACLE_HOME=/app/oracle/product/10.2.0/server

This value will be wiped off when you close the current command line session. To set it as a global environment variable in Windows:

1. Go to Control Panel -> System -> Advanced.
2. Click on button “Environment Variables”. This will open a window with two sets of variables – User and System. User variables are visible to your login only, while system variables are visible to anyone else who uses the system.
3. Choose “New” to create ORACLE_HOME variable as either User or System variable, depending on how you want its visibility.
4. Set its value to the Oracle directory.
5. Click OK to save.
6. Verify through a new command line session that the value has been set correctly.

How to unset/change/delete ORACLE_HOME

You might want to edit or delete ORACLE_HOME when there are multiple Oracle installs machine or if Oracle installation has been moved from one location to another.

If you only want to override the value in one runtime session without affecting ORACLE_HOME permanently, just set it on command line as you would for a new variable.

To change it globally on Windows, follow the same steps as for setting a new variable. But click here the “Edit” or “Delete” button, in place of the “New”.

SCOTT schema in Oracle

What is SCOTT?

Scott is a database user in Oracle, used for demos and examples.

The SCOTT schema used to be installed by default in earlier versions of Oracle. Not anymore. Oracle 9i onwards, new sample schemas like HR and OE are available that are more suited for demoing newer Oracle features.

How to install SCOTT?

You might still want to have the SCOTT schema in the database, for trying out examples from old manuals, or maybe because you’ve used it so much it’s like an old friend.

Good news – there’s a script to do the installation with a single command. Here’s how:

1. Login to SQLPLUS as a DBA user (e.g. SYSTEM).
2. Run the script %ORACLE_HOME%\RDBMS\ADMIN\SCOTT.sql. [IfORACLE_HOME is not set, replace with the actual directory path.]

That’s it! Login with scott/tiger to verify that it works.

Below is an actual runtime output of the installation of SCOTT schema:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

C:\>sqlplus system/manager   SQL*Plus: Release 10.2.0.1.0 - Production on Thu Oct 29 18:47:38 2009   Copyright (c) 1982, 2005, Oracle.  All rights reserved.   Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production   SQL> @%ORACLE_HOME%\RDBMS\ADMIN\SCOTT.sql SQL> connect scott/tiger Connected.   SQL>

Why SCOTT, Why TIGER?

Have you wondered why these names were chosen?

Wikipedia says that the schema was named after Bruce Scott, one of the first employees of Oracle, and the password was the name of his cat, Tiger.

 DECLARE
   STR  VARCHAR2(200) := '&FULLNAME';
   L    NUMBER;
   N    NUMBER;
   S    VARCHAR2(200);
   TEMP VARCHAR2(200) := STR || ' ';
   T    VARCHAR2(200);
 BEGIN
   N := 0;
   LOOP
     L := INSTR(TEMP, ' ');
     EXIT WHEN NVL(L, 0) = 0;
     T := SUBSTR(TEMP, 1, L - 1);
     IF N > 1 THEN
       S := S || SUBSTR(TEMP, 1, 1);
       T := S;
     ELSIF N = 1 AND LENGTH(T) = 1 THEN
       T := REPLACE(TEMP, ' ', '');
       DBMS_OUTPUT.PUT_LINE(T);
       RETURN;
     ELSE
       DBMS_OUTPUT.PUT_LINE(T);
     END IF;
     TEMP := SUBSTR(TEMP, L + 1);
     N    := N + 1;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE(T);
 END;

20 Things You Should Know About Oracle Views

1. A view is like a virtual table. It takes the output of a query and treats it like a table.
2. A view can be based on one or more tables or other views. These tables/views are called base tables.
3. A view takes up no storage space other than for the definition of the view in the data dictionary.
4. A view contains no data. All the data it shows comes from the base tables.
5. A view can provide an additional level of table security by restricting access to a set of rows or columns of a table.
6. A view hides implementation complexity. The user can select from the view with a simple SQL, unaware that the view is based internally on a join between multiple tables.
7. A view lets you change the data you can access, applying operators, aggregation functions, filters etc. on the base table.
8. A view isolates applications from changes in definitions of base tables. Suppose a view uses two columns of a base table, it makes no difference to the view if other columns are added, modified or removed from the base table.
9. Using views encourages the use of shared SQL, which improves efficiency of frequently invoked SQL.
10. An updatable view allows you to insert, update, and delete rows by propagating the changes to the base table. A view can be updatable provided its definition does not contain any of the following constructs: SET or DISTINCT operators, an aggregate or analytic function, a GROUP BY, ORDER BY, CONNECT BY, or START WITH clause, a subquery (or collection expression) in a SELECT list.
11. The data dictionary views ALL_UPDATABLE_COLUMNS, DBA_UPDATABLE_COLUMNS,and USER_UPDATABLE_COLUMNS indicate which view columns are updatable.
12. Views that are not updatable can be modified using an INSTEAD OF trigger.
13. A view can be created even if the defining query of the view cannot be executed, using the CREATE FORCE VIEW command. Such a view is called a view with errors. This option can be useful for import/installation tools to create a view before the underlying objects are present.
14. A view can be replaced with a CREATE OR REPLACE VIEW statement. The REPLACE option updates the current view definition  but preserves the present security authorizations.
15. A view lets you reorder columns easily with a CREATE OR REPLACE, rather than going into a messy drop column for the base table with data.
16. To know about the views in your own schema, look up user_views.
17. The underlying SQL definition of the view can be read via select text from user_views for the view.
18. Oracle does not enforce constraints on views. Instead, views are subject to the constraints of their base tables.
19. Be careful when you define views of views. Don’t do it just because it is easy to code – it may not be the optimal query. Check if you would be better off using the base tables directly instead.
20. To create a view in your own schema, you must have the CREATE VIEW system privilege. To create a view in another user’s schema, you must have the CREATE ANY VIEW system privilege.

The Special DUAL Table

DUAL is a special one-row, one-column table in Oracle’s data dictionary.

Of what use is DUAL?

DUAL comes in handy when you want to select just one row through a query. Oracle SQL structure requires you to have a FROM <table> clause, but some queries don’t need a table – if you want to know the current system date, for example, or the answer for (3+1)*5. DUAL is useful for queries you’d write for such cases:

1	select user from dual;

1	select (3+1)*5 from dual;

But why DUAL? Won’t any table with a rownum < 2 filter work equally well?

Yes, why not simply:

1	select user from scott.emp where rownum < 2;

This query gives you the same result but DUAL has an edge over any other table in the query – the Oracle optimizer recognizes DUAL as a special tableand prepares the best execution plan for it.

What will happen if I insert more rows or delete from DUAL?

The Oracle optimizer trusts DUAL to have exactly one row – no more, no less. If you mess up its data you can lead to unpredictable behavior in the database. Don’t try it!

If DUAL is supposed to have only one row, why is it called DUAL? Why not SINGLE?!

According to this old article on the history of Oracle’s DUAL table, DUAL was originally not meant to be seen itself but instead used inside a view that was expected to be queried. The idea was that you could do a JOIN to the DUAL table and create two rows in the result for every one row in your table. In that context, the name DUAL seemed fine.