Oracle Cheat Sheet

by

I did a fair bit of work recently with Oracle. Being a regular MySQL user, I found some Oracle-isms a little strange, so I put together a cheat sheet:

1. Some useful commands

To start a session as sysdba: sqlplus sys@tnsname as sysdba;
To start a sysdba session under Windows (9iAS): sqlplus "/as sysdba"
To list all tables in current schema: SELECT table_name FROM user_tables;
or, all tables current user has access to: SELECT table_name FROM all_tables;
To list all schemas: SELECT username FROM all_users ORDER BY username;
To turn pause on: SET PAUSE ON;
To list top n rows of a table in order: SELECT * FROM (SELECT * FROM t ORDER BY c) WHERE ROWNUM <= n;
Show current database: SELECT * FROM global_name;
Use database: CONNECT schema/password@tnsname;
Show who I am: SHOW USER;
Describe table: DESC tablename;
Set display rows: SET PAGESIZE 66;
Read field constraints: SELECT constraint_name,search_condition FROM user_constraints WHERE table_name='tablename';
Copy table from foreign host to here: COPY FROM user@tnsname CREATE tablename USING SELECT * FROM tablename;
Start SQLPLUS without login: SQLPLUS /NOLOG
Change a user’s password: ALTER USER user IDENTIFIED BY password;
Unlock an account ALTER USER user ACCOUNT UNLOCK;

I found some of these at http://www.johntopley.com/kb/oracle/index.html, a few others at https://gdsg.ngdc.noaa.gov/tiki/tiki-index.php?page=SqlNotes. There is also Mark Rittman’s Oracle Weblog article about what to do if an Oracle application server fails to start after a crash.

2. SSO issues

The easiest way to add Oracle Single Sign On authentication to a Web page/application just uploaded to an Oracle 9i application server is to hand-edit mod_osso.conf, inserting the following lines:

<Location /base-URL>
	require valid-user
	AuthType Basic
</Location>

See also http://www.oracle.com/technology/sample_code/deploy/security/Usingmod_osso.htm.

To administer SSO accounts, try http://your.oracle9iAS.server:7777/oiddas/ui/. If the relevant components are installed, this may work. The Oracle portal (if installed) is at http://your.oracle9iAS.server:7777/pls/portal/. Also, if you can log on to the application server database, you should be able to list all SSO users using

SELECT user_name FROM orasso.wwsec_person$;

If you receive the following error:

Oracle SSO Warning – Unable to process request
Either the requested URL was not specified in terms of a fully-qualified host name or OHS single sign-on is incorrectly configured.
Please notify your administrator.

then poke around in the Apache/Apache subdirectory in your Oracle9i AS installation to see if you can find a log file explaining what happens. You may find that the problem is due to you using the wrong URL (e.g., numeric IP addresses where Oracle’s SSO module expects a symbolic URL.)

3. Autonumbers

Oracle has no autonumbers like SQL Server, Access, or MySQL. One way to do autonumbers is by using a combination of a sequence and a trigger, as in the following example:

CREATE SEQUENCE sequence-name;

CREATE OR REPLACE TRIGGER trigger-name
BEFORE INSERT ON table-name
FOR EACH ROW
WHEN (NEW.field-name IS NULL OR NEW.field-name = '<new>')
BEGIN
SELECT 'PR-' || sequence-name.NEXTVAL INTO :NEW.field-name FROM DUAL;
END;
/

4. JDeveloper

To create a new JDeveloper application that will contain “raw” JSP code, follow these steps:

  1. Create New Workspace
  2. Create New Empty Project
  3. Create New Class
  4. Create New JSP

You may find that you need to specify additional libraries with which to link your project. To do so, select Project Settings in the menu and insert additional CLASSPATHs, e.g., I had to add S:\INSTALLS\ORACLE\JDeveloper-9i2\jdbc\lib\classes12.jar.

To create a new database application using Oracle’s classes, follow these steps:

  1. Create New Workspace
  2. Create New Project with Business Components
  3. Default package: mypackage1
  4. Create New BC4J JSP Browse & Edit Form

5. Deploying an application

Before any of these steps, you must create a deployment profile in JDeveloper, and deploy the project to an EAR file. Then:

  1. Connect to the application server’s Enterprise Manager (TCP port 1810)
  2. Select the server
  3. Click OC4J_Home
  4. Click Deploy EAR File
  5. Specify file location, application name
  6. Specify base URL
  7. Specify JAZN LDAP authentication for SSO

Your application will then appear under the URL http://your.oracle9iAS.server:7777/base-URL. (The port number may be something other than 7777 depending on your AS setup.) You may need to restart OC4J_Home if, after deployment, the application misbehaves (Java compiler errors, out of memory errors, etc.)

If the application fails to run due to missing libraries, check your deployment profile. Make sure all libraries are included. If all else fails, then instead of using the dependency analyzer, choose to include all necessary libraries in their entirety.

6. Some default passwords

Name Password
sys change_on_install
system manager
orcladmin welcome