Oracle 11g For Dummies Cheat Sheet
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.
|Data Pump Export||expdp|
|Data Pump Import||impdp|
|Network Configuration Assistant||netca|