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
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.
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)
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"
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
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.
(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