Oracle 11g For Dummies Cheat Sheet - dummies
Cheat Sheet

Oracle 11g For Dummies Cheat Sheet

From Oracle 11g For Dummies

By Chris Zeis, Chris Ruel, Michael Wessler

Oracle 11g is by far the most robust database software on the market today. It has become an enterprise architecture standard for managing data, regardless of the data’s size or complexity. These SQL commands and command line executables are a good place to start when you begin working with Oracle. From there, the sky’s the limit!

SQL Commands You Can Use in Oracle

SQL is an American National Standards Institute (ANSI) standard for communicating with relational database systems. You can use SQL to communicate with Oracle. Consider SQL to be the language that Oracle will use to communicate what you need. Here are some commands to get you started.

Create a tablespace

CREATE TABLESPACE users 
DATAFILE ‘/u01/oracle/oradata/dev11g/users01.dbf’ 
   size 100M
autoextend on next 10M maxsize 4G;

Set up a user

CREATE USER ldehoff IDENTIFIED BY ‘welcome1’
DEFAULT TABLESPACE users
QUOTA 10m ON users
PASSWORD EXPIRE;
CREATE ROLE developer_role;
GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE,
   CREATE VIEW to developer_role;
GRANT developer_role TO ldehoff;

Create a table

CREATE TABLE clients (
client_id    NUMBER(4) CONSTRAINT pk_client_id PRIMARY KEY,
last_name    VARCHAR2(128) NOT NULL,
first_name   VARCHAR2(64) NOT NULL,
acct_number  NUMBER(8) CONSTRAINT uk_acct_number UNIQUE,
advisor_id   NUMBER(4) CONSTRAINT fk_advisor_id
   REFERENCES advisors(advisor_id) 
) TABLESPACE users; 

Create a view

CREATE OR REPLACE VIEW client_vw AS
SELECT last_name||’,’||first_name “LAST, FIRST”, client_id
FROM clients;

Use a sequence

INSERT INTO clients
VALUES (client_id.nextval, ‘Schumacher’, ‘Michael’, 1000,
   1234); 

Create an index

CREATE INDEX idx_last_name ON
clients(last_name)
TABLESPACE indexes; 

Create a sequence

CREATE SEQUENCE client_id_seq
INCREMENT BY 10
START WITH 10;

Popular Command-Line Executables for Oracle Tools

Many tasks that you need to complete in Oracle can’t be completed through the GUI. Some tasks can only be performed by actually typing in the necessary command. These command prompt executables gives you more specific options for working in Oracle. Here are a few of the more popular ones to get you started.

Tool Command
SQL*Plus sqlplus
Listener Control lsnrctl
Data Pump Export expdp
Data Pump Import impdp
Recovery Manager rman
SQL Developer sqldeveloper
Net Manager netmgr
Network Configuration Assistant netca