Oracle Cheat Sheet

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;

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;