General Oracle Database System Information

THIS PAGE UNDER CONSTRUCTION!
 

Common Oracle Operations
Miscellaneous Oracle Utilities and Commands
Miscellaneous SQL Commands
Installation And Use of Windows Oracle Client
Creating and Deleting WAS Database
Exporting and Importing Data
WEA Remote Database Behavior
Other Oracle Installation Information
An A-Z Index of Oracle SQL Commands
Oracle for Windows \\cinta04tc6\Ora9Win
 
 

Common Oracle Operations

Creating And Using An Oracle Database
Using An Oracle Database
Using The NetAssist Utility
 

Creating And Using An Oracle Database

1) After performing the "Logon to Oracle as SYSTEM", execute the command "dbassist", which will invoke the Oracle Database Configuration
Assistant.

2) In the Welcome panel, select the "Create a database" option, then click "Next".

3) In the "Select the type of database to create" panel, select "Typical" and click "Next".

4) Select "Copy Existing database files from the CD", then click "Next".
 
 

Option 1 - Copy Existing database files from the CD"

1) In the "An Oracle8i databases is uniquely identified by a Global Database Name...", "Global Database Name" field enter "slapit.raleigh.ibm.com"
(which will automatically create the SID as "slapit"), then click "Finish".

2) An alert will be displayed reflecting "The datafiles will be copied from the CD to /home/oracle/ORA817/oradata/slapit.(STEVE?) The Oracle
Database Configuration Assistant wil begin creating the database. Do you want to proceed", click "Yes".

3) An additional alert will be displayed "An Oracle databse will be created for you. The database name will be slapit. The system identifier for the
database will be slapit. The password for the SYS account will be change_on_install and the SYSTEM account will be manager" click "OK".

4) Final alert will be displayed in dialog box "Oracle Database Configuration Assistant Alert" containing:

Database creation completed.
Database Information:
    global database name:    slapit.wea2aix1.raleigh.ibm.com
    database system identifier (SID):  slapit
    SYS account password"  change_on_install
    SYSTEM account password:  manager

5) Click "OK" in the alert box.

6) From command prompt type "sqlplus system/manager@STEVEZ" and press "Enter".

7) Your database has been created and should be available for population of tables, etc.
 
 
 

Using An Oracle Database

1) When the database creation utility had completed, from the AIX command prompt, type "su  -  oracle".

2) From the Oracle prompt ("$"), executed the sqlplus command giving the appropriate id/password and SID (i.e. "sqlplus  system/manager@slapit").

(Note that "Ctrl" and Backspace key are the delete/rubout key.)

3) From the SQLPLUS command prompt, list data from your tables (i.e. "select * from scott.emp") which will display all rows in the SCOTT schema's
"emp" table. (Note that whenever you use DBASSIST to create a database and select "Copy Existing database files from the CD", it automatically
creates a "SCOTT" schema.

4) To describe the columns of an table, use the "describe" command (i.e. "describe scott.emp").

5) Do normal creation of tables, insert rows, etc.

6) To list the tables, use the command "select * from tab". This will list all tables, as well as your tables should be in the list.

7) To list only the tables for a particular user/owner (i.e. "SCOTT"), type the command "select TABLE_NAME from ALL_TABLES where
OWNER='SCOTT'" (do not include the beginning and ending double quote (")).
 

(Schema   Table   Scott   emp)
 
 

Using The NetAssist Utility

1) From the Oracle command prompt ("$"), type "netasst".

2) In the Net8 Assistant GUI, scope down through "Net8 Configuration", "Local", "Service Naming", and select your "slapit" service. The right panel
will display details of this service, (including Port Number).
 
 
 
 

Miscellaneous Oracle Utilities and Commands

Start/Stop the Database
Starting and Stopping Listener
Adding Oracle Automatic Startup To "inittab"
 

Start/Stop the Database

   1.To start or stop the Oracle database:
          Log in as the user oracle by entering the following command:
          su - oracle
          If not set in the .profile, enter export ORACLE_SID=was40
          To start the database, enter the following commands:

          $ svrmgrl

          SVRMGR> connect internal

          SVRMGR> startup SVRMGR

          SVRMGR> quit

          To stop the database, enter the following commands:

          $ svrmgrl
          SVRMGR> connect internal
          SVRMGR> shutdown
          SVRMGR> quit

Starting Database

Start the database by entering the following commands:
$ svrmgrl SVRMGR
> connect internal SVRMGR
> startup SVRMGR
> quit
 
 

(OR, Starting Database)

$ svrmgrl
SVRMGR> connect internal
Connected.
SVRMGR> startup
ORACLE instance started.
Total System Global Area               7275404 bytes
Fixed Size                                          73620 bytes
Variable Size                                55730176 bytes
Database Buffers                          16777216 bytes
Redo Buffers                                   172032 bytes
Database mounted.
Database opened.
SVRMGR> help     blah blah.....
SVRMGR> quit
 

(OR, a script which performs this contains:)

#!/bin/ksh
su - oracle -c '
echo "Enter sid name (like ispb): "
read sid
PMON=`ps -ef | egrep pmon_${sid}  | grep -v grep`
if [ "${PMON}" = "" ]
then
echo "starting Oracle"
    # database still running, shut it down first
    ORACLE_SID=$sid
    sqlplus /nolog <<!
connect internal
startup
!
else
echo "Oracle already running"
fi
'

Stopping Database

$ svrmgrl
SVRMGR> connect internal
Connected.
SVRMGR> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

(OR, a script which performs this contains:)

#!/bin/ksh
su - oracle -c '
echo "Enter sid name (like ispb):"
read sid
PMON=`ps -ef | egrep pmon_${sid}  | grep -v grep`
if [ "${PMON}" != "" ]
then
echo "stopping Oracle"
    # database still running, so we can shut it down
    ORACLE_SID=$sid
    sqlplus /nolog <<!
connect internal
shutdown immediate
!
else
echo "Oracle not running"
fi
'
 
 
 

Starting and Stopping Listener

Starting and stopping the listener is done by the utility "lsnrctl". Usage follows (note the author occasionally had to go the the directory lsnrctl was in and execute it there first (/home/oracle/ORA817/bin/lsnrctl):

1) Log in as the user oracle by entering the command "su - oracle"

2) If not set in the .profile, enter export ORACLE_SID=was40

3) To stop/start the listener, enter the following commands:

$  lsnrctl
LSNRCTL for IBM/AIX RISC System/6000: Version 8.1.7.0.0 - Production on 17-JAN-2003 17:29:26
blah blah blah
LSNRCTL>  status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=wea2aix2.raleigh.ibm.com)(PORT=1521)))
-------------------------------------
Alias                            LISTENER
Version                        TNSLSNR for IBM/AIX blah
Start Date  blah blah

LSNRCTL> stop
LSNRCTL> start
LSNRCTL> quit

(OR, a script file which does it should contain:)

#!/bin/ksh
su - oracle -c "lsnrctl stop"

#!/bin/ksh
su - oracle -c "lsnrctl start"
 

(OR, a script file which restarts it should contain:)

#!/bin/ksh
su - oracle -c "lsnrctl reload"
 
 

Adding Oracle Automatic Startup To "inittab"

From command line type:

mkitab "oradb:2:wait:/bin/su oradb -c|/$ORACLE_HOMES/bin/dbstart"
 
 
 

Creating and Deleting WAS Database

Deleting the WebSphere Application Server Database

1) Ensure that you have run the xhost command and have exported your DISPLAY. See above for details.

2) Log in as the user oracle by entering the following command "su - oracle".

3) If not set in the .profile, enter "export ORACLE_SID=was40".

4) Invoke the Database Assistant from the command prompt (i.e. enter "dbassist").

5) When prompted, select Delete a database.

6) When prompted, select was40.
 
 
 
 

Recreating the WebSphere Application Server Database

1) Ensure that you have run the xhost command and have exported your DISPLAY. See above for details.

2) Log in as the user oracle by entering the command "su - oracle".

3) If not set in the .profile, enter "export ORACLE_SID=was40".

4) Invoke the Database Assistant from the command prompt (i.e. enter "dbassist").

5) When prompted, select Create a database.

6) When prompted, select Typical Install.

7) When prompted, select "Copy existing database files from the CD". Ignore the text about needing the real CD.

8) For Global Database Name, enter "was40.(fully qualified hostname) (i.e. " was40.wea2aix2.raleigh.ibm.com").

9) For Oracle SID, enter "was40"

   2.Configure the database (see above)
 
 
 
 

Setup Manager Information

   1.On the WebSphere Application Database configuration screen:
          Database Name: was40 (STEVE?)
          Database Server Name: (local/remote hostname).raleigh.ibm.com
          Database Home: (value of $ORACLE_HOME
          Usually /home/oracle/OraHome1
          Database URL:
          jdbc:oracle:thin:@(local/remote hostname).raleigh.ibm.com:1521:was40
          Database Port: 1521
          EJSADMIN Password: ejsadmin
 
 
 
 

Miscellaneous SQL Commands

ALTER USER USERNAME IDENTIFIED BY PASSWORD;                Change password for "USERNAME" to "PASSWORD"
show all                            Show Oracle environment settings
show user                         Displays the current user
select username from dba_users        Displays all users for this installation
describe dba_tables           Displays tables in the current database
select table_name from dba_tables    Display all the tables in this database
set pagesize 50                  Sets the number of lines displayed before headers are shown again
drop user (username) cascade;    Removes the user
password   (username)      Change the users password
 

forward slash"/" performs the same command again.
 

To use sqlplus on the client machine, type:
su - oracle (or whatever your oracle username is)
sqlplus stage_user/oracle@alias   (alias is your database name, it could be something like WEA)
(i.e. "sqlplus system/manager@wea430")
List tables:  select table_name from user_tables;
Describe columns in table:   describe <table_name>;
Output to command line:  echo "SELECT trxn_status FROM tsmtransaction where username='X';" | sqlplus stage_user/oracle@WEA
(The column and table names mentioned throughout this section were actuals from a previous test)
Example: echo "select order_state, auth_status from cust_order where user_id in (select user_id from piauthuser where username='KMC3_IN');" |
sqlplus stage_user/oracle@wea
List connections to database (like list applications on DB2):
sqlplus stage_master/oracle@wea
SQL> SELECT username, status FROM v$session;
 
 
 

Other Oracle Installation Information

http://wespoint/wes/wes_tpsmoracle.html
http://shayden/instorawas.html
 
 
 
 
 

Installation And Use of Windows Oracle Client

1) From Oracle web site "http://otn.oracle.com/software/products/oracle8i/htdocs/winsoft.html" (or Steves folder "D:\Oracle
Installation\OracleClient\OracleUnzipped\autorun" double click on "autorun.exe" (tried running "setup.exe" in the folder above it but it did not seem to
anything). The Oracle8i Client GUI will appear.

2) Click "Install/Deinstall Products".

3) In the "Oracle Universal Installer", "Welcome" panel, click "Next".

4) In the "File Locations" panel, accept the default for the "Source" ("D:\Oracle Installation\OracleClient\OracleUnzipped\stage\products.jar"), and
"Destination" ("Name" = "OraHome81" and "Path:" = "D:\oracle\ora81"), then click "Next".

5) In the "Installation Types" panel, select "Administrator (356MB)", then click "Next". Wait a minth.

6) In the "Net8 Configuration Assistant: Welcome" window, DO NOT select "Perform typical configuration", click "Next".

7) In the "Net8 Configuration Assistant: Directory Service Access", select "No,..." and click "Next".

8) In the "Net8 Configuration Assistant: Naming Methods Configuration" panel, accept the default "Selected Naming Methods" of "Local", and click
"Next".

9) In the "Net8 Configuration Assistant: Net Service Name Configuration" panel, select "Oracle8i database or service", then click "Next".

10) In the "Net8 Configuration Assistant: Net Service Name Configuration, Service Name" panel, in the "Service Name:" field, enter "STEVEZ", then
click "Next".

11) In the "Net8 Configuration Assistant: Net Service Name Configuration, Select Protocols" panel, select "TCP" and click "Next".

12) In the "Net8 Configuration Assistant: Net Service Name Configuration, TCP/IP Protocol" panel, for the "Host Name" field enter your Oracle
database machine (i.e. "wea2aix2.raleigh.ibm.com", get this right or it wont work!), and for the "TCP/IP Port Number..." field, accept the default "Use
the standard port number of 1521", then click "Next".

13) In the "Net8 Configuration Assistant: Net Service Name Configuration, Test" panel, select "Yes, perform a test", then click "Next". The "Details:"
panel should reflect "Connecting....Test Successful", then click "Next".

14) In the "Net8 Configuration Assistant: Net Service Name Configuration, Net Service Name" panel, accept the "Net Service Name:" value (i.e.
"STEVEZ"), then press "Next".

15) In the "Net8 Configuration Assistant: Net Service Name Configuration, Another Net ServiceName" panel, select "No", then click "Next".

16) In the "Net8 Configuration Assistant: Net Service Name Configuration Done" panel, click "Next".

17) In the "Net8 Configuration Assistant: Naming Configuration Done" panel, click "Next".

18) In the "Net8 Configuration Assistant: Done" panel, click "Finish".

19) Back in the "Oracle Universal Installer" pane "End of Installation" panel, click "Exit", and in the "Do you relly want to exit", click "Yes".
 
 

Starting and Using DBA Studio GUI

1) To execute the DBA Studio GUI for viewing the remote database, from the Windows "Start" menu item, select "Programs", "Oracle - OraHome81",
"Database Administration", "DBA Studio",

2) In the "Oracle Enterprise Manager Logon", select "Launch DB Studio standalone", then click "OK". The "Oracle DBA Studio" GUI window should
appear.

3) If/when queried for a Username and Password, enter the appropriate values (i.e. "SYSTEM" and "manager"), then click "OK".

4) In the Oracle DBA Studio GUI select the database you wish to explore (i.e. "STEVEZ"), and scope down to "Schema", "Table", and select the
table you wish to explore (i.e. "SCOTT").

5) In the right side panel of the GUI, select a table you wish to explore, right mouse click and select "Table Data Editor". The "Table Editor" should
appear reflecting the data in the associated table. You can manually change data by going to the appropriate data cell and modifying the value, or go to
the last row (which should be empty), and inputting a new row of data into the cells.
 
 
 

WEA Remote Database Behavior

(Steve, Under Construction)

For a WEA configuration where you have a remote database, it is necessary to create a connection from the local to the remote database machine.
This is done by the use of the "catalog" command (a Remote Database Catalog Example follows). On the local machine, the DB2 client must be installed, and on the remote machine, the full DB2 installation must be performed. Once these steps are complete, additional operations must be performed on both machines. On the remote machine, you must create and populate the appropriate database(s). On the remote machine, dependant upon what db2 instance you used, the operations are different. If you use the same DB2 instance on both machines, you only need to catalog the remote node. If you have different instances on the two machines, you must catalog the remote node as well as catalog a link to each database you wish to connect to on the remote machine.

Also, when connecting to a remote database from a local machine, you must use the alias you catalogged as the connection value.
 
 

Remote Database Catalog Example

Where: wasinst represents the Application Server database instance
            <db_hostname> represents the hostname of the machine where the component databases were installed
            <db_port> represents a port number that is planned to use for WebSphere Application Server, such as 55555. Setup Manager will handle the client configuration for WebSphere Application Server.
 
 

su - wasinst
catalog tcpip node LOOPWPS remote <db_hostname> server <db_port>
catalog db XWPSDB as WPSDB at node LOOPWPS
catalog db WMSDB as WMSDB at node LOOPWPS
catalog db OFLNBRS as OFLNBRS at node LOOPWPS
 
 
 
 

An A-Z Index of Oracle SQL Commands
ANALYZE
AUDIT

CALL
CLUSTER - CREATE CLUSTER
CLUSTER - ALTER  CLUSTER
CLUSTER - DROP   CLUSTER
COMMENT
COMMIT
CONNECT
CONSTRAINT - Add / Enable
CONTEXT - CREATE CONTEXT
CONTEXT - DROP CONTEXT
CONTROLFILE - CREATE CONTROLFILE

DATABASE - CREATE DATABASE
DATABASE - ALTER  DATABASE
DELETE
DESCRIBE
DIMENSION - CREATE DIMENSION
DIMENSION - ALTER DIMENSION
DIMENSION - DROP DIMENSION
DIRECTORY - CREATE DIRECTORY
DIRECTORY - DROP DIRECTORY

EXEC
EXECUTE IMMEDIATE
EXPLAIN PLAN

FUNCTION - CREATE FUNCTION
FUNCTION - ALTER  FUNCTION
FUNCTION - DROP   FUNCTION

GRANT

INDEX - CREATE INDEX
INDEX - ALTER  INDEX
INDEX - DROP   INDEX
INDEXTYPE - CREATE INDEXTYPE
INDEXTYPE - DROP INDEXTYPE
INSERT
INTERSECT

JAVA - CREATE JAVA
JAVA - ALTER JAVA
JAVA - DROP JAVA

LIBRARY - CREATE LIBRARY
LIBRARY - DROP LIBRARY
LINK - CREATE DATABASE LINK
LINK - DROP DATABASE LINK
LOCK TABLE

MVIEW - CREATE MATERIALIZED VIEW
MVIEW - ALTER MATERIALIZED VIEW
MVIEW - DROP MATERIALIZED VIEW
MVIEW - CREATE MATERIALIZED VIEW LOG
MVIEW - ALTER MATERIALIZED VIEW LOG
MVIEW - DROP MATERIALIZED VIEW LOG
MINUS

NOAUDIT

OPERATOR - CREATE OPERATOR
OPERATOR - DROP OPERATOR
OUTLINE - CREATE OUTLINE
OUTLINE - ALTER OUTLINE
OUTLINE - DROP OUTLINE

PACKAGE - CREATE PACKAGE
PACKAGE - ALTER  PACKAGE
PACKAGE - DROP   PACKAGE
PACKAGE BODY - CREATE PACKAGE BODY
PROCEDURE - CREATE PROCEDURE
PROCEDURE - ALTER  PROCEDURE
PROCEDURE - DROP   PROCEDURE
PROFILE - CREATE PROFILE
PROFILE - ALTER  PROFILE
PROFILE - DROP   PROFILE

RECOVER
RENAME
RESOURCE - ALTER RESOURCE COST
REVOKE
ROLE - CREATE ROLE
ROLE - SET    ROLE
ROLE - ALTER  ROLE
ROLE - DROP   ROLE
ROLLBACK
ROLLBACK - CREATE ROLLBACK SEGMENT
ROLLBACK - ALTER  ROLLBACK SEGMENT
ROLLBACK - DROP   ROLLBACK SEGMENT

SAVEPOINT
SCHEMA - CREATE SCHEMA
SELECT
SEQUENCE - CREATE SEQUENCE
SEQUENCE - ALTER  SEQUENCE
SEQUENCE - DROP   SEQUENCE
SESSION - ALTER SESSION
SHUTDOWN
SNAPSHOT
STARTUP
STATISTICS - ASSOCIATE STATISTICS
STATISTICS - DISASSOCIATE STATISTICS
SYNONYM - CREATE SYNONYM
SYNONYM - DROP   SYNONYM
SYSTEM - ALTER SYSTEM

TABLE - CREATE TABLE
TABLE - ALTER  TABLE
TABLE - DROP   TABLE
TABLESPACE - CREATE TABLESPACE
TABLESPACE - ALTER  TABLESPACE
TABLESPACE - DROP   TABLESPACE
TABLESPACE - CREATE TEMPORARY TABLESPACE
TRANSACTION - SET TRANSACTION
TRIGGER - CREATE TRIGGER
TRIGGER - ALTER  TRIGGER
TRIGGER - DROP   TRIGGER
TRUNCATE
TYPE - CREATE TYPE
TYPE - ALTER TYPE
TYPE - DROP TYPE
TYPE - CREATE TYPE BODY
TYPE - DROP TYPE BODY

UPDATE
UNION
USER - CREATE USER
USER - ALTER  USER
USER - DROP   USER

VIEW - CREATE VIEW
VIEW - ALTER  VIEW
VIEW - DROP   VIEW
 

Exporting and Importing Data