Default passwords of Oracle 11g?

On Windows

sqlplus "/as sysdba" 
or 

On linux/unix
sqlplus '/as sysdba'

type the command "sqlplus /nolog "
SQL> ALTER USER username IDENTIFIED BY "mypassword";

Once logged in successfully you can change password for the users created and use orapwd utility to generate a password file for sys/system user

orapwd example
Create a new password file: 

orapwd file=orapwSID password=oracle entries=5
If the password file already exists: 

orapwd file=orapwSID password=oracle entries=5 FORCE=Y

View

A view is a predefined query on one or more tables (known as base tables). Retrieving information
from a view is done in the same manner as retrieving from a table: you simply include the view in
the FROM clause of a query. With some views you can also perform DML operations on the base
tables.


Views don’t store rows. Rows are always stored in tables.

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.