Oracle and SQL tutorial and reference

By Sandeep Desai (http://www.thedesai.net)

email: s_desai@hotmail.com

This document covers the basics of Oracle SQL

Books

· Oracle 10g The Complete Reference

Web

· http://otn.oracle.com

· http://technet.oracle.com/documentation/content.html (Oracle Documentation)

· www.orafaq.net (Lot of Info and OCP)

· www.jlcomp.demon.co.uk/faq/ind_faq.html (Users Coop FAQ)

· http://www.dbasupport.com Lots of Oracle Information (OCP Info)

· http://www.ixora.com.au Good information on DBA queries

· http://home.clara.net/dwotton/dba/index.htm DBA Information

· www.oraclepressbooks.com

· www.orablog.com links to various Oracle Blogs

· www.rittman.net Oracle Datawarehouse/OLAP/DBA Blog

· Utility to dump table to flat file

Database concepts

A database is just a collection of data such as Airplane reservations, sales orders etc.

The data can be stored either relational or multi-dimensional. The storage mechanism determines how fast different types of queries will run in the database.

In the relational model, the data is stored in tables, in the form rows and columns

Names

NameID	Name	Address
1	Sandeep	USA
2	Rhea	Canada

Owns

NameID	Owns
1	DVD Player
1	Television
2	Bicycle

In the above example you have two tables that you can query on Names and Owns,

A relational database has to meet the ACID criteria (atomic, consistent, durable, isolated)

Normal Form: Data should be normalized

· 1NF: A table should have a primary key

· 2NF: remove columns that are only partially dependant on the primary key

· 3NF: All the columns in the table should be dependant on the primary key

Books

Title, Author, Rating, Rating Desc, Category, ParentCategory, SubCategory

1NF:

Books: Title (Primary Key), Author Name, Rating, Rating Desc, Category, ParentCategory, SubCategory

('pooh', 'disney', 'good', 'very good', 'fiction', 'children', 'comic')

Author: Title, Author Name

2NF:

Books: Title, Author Name, Rating, Category, ParentCategory, SubCategory

Author: Title, Author Name

Rating: Rating, Rating Desc

3NF

Books: Title, Author Name, Rating, Category

Author: Title, Author Name

Rating: Rating, Rating Desc

Category: Category, ParentCategory, SubCategory

Oracle Database

Operator precedence PEMDAS (Parentheses, exponent, multiplication, division, addition, subtraction)

 
Table join requires equality clause to prevent cartesian product also one of the column has to be a primary key. For N tables to be joined there have to be N-1 comparisons. Self join useful for finding duplicates can have peformance issues, requires a where with a comparision and clause to prevent cartesian product (e.g empno <> empno and ename = ename)
 
subqueries can be nested upto 255 queries
parent query ignores null row values returned by subquery
type of subqueries 
· single row subquries (scalar subqueries) typical used with =, <> etc
· multirow subqueries typicall used with in 
· inline views , a subquery in from clause used for defining an intermediate result set to query from. 
· multiple-column subqueries
 
Max number of columns in a table is 1000
 
Oracle database object names can be upto 30 characters long
Database is 8 characters long
Database link 128 characters long
'$'.'_','#' and alphanumeric characters can be used for object names
tables with $ are dynamic performance view
tables with # are owned by sys

SQL DML (Data Manipulation Language)

Most of these queries can be run against the scott schema in an Oracle database. Password for scott is tiger

select distinct(ename) from emp select distinct ename from emp
select ename || job from scott.emp where ename between 'APPLE' and 'ORANGE' and sal in (10, 20, 40)
select user, uid from dual;	scott, 5 dual is a special dummy table with one column and one row
select concat(ename, job) namejob, nvl(mgr,0) as mgr, sal * 1.08 as raise from scott.emp'	nvl replace null with 0
select ename, deptno from emp where ename like '_ABC%' and deptno is not null order by ename desc, deptno	_ is equivalent to ? in regular expression and % to *
select ename from emp where ename > 'F' and ename not in ('ABC', 'DEF') and deptno between 3 and 9
select ename from emp where deptno <= any(10, 20) select ename from emp where deptno >= all(10, 20)
select ename, deptno, case depnto when 10 then 'accounting' when 20 then 'research' else 'other' end deptname from emp
select ename, emp.deptno, dname from emp join dept on emp.deptno = dept.deptno	ANSI join syntax
select ename, deptno, dname from emp natural [inner] join dept select ename, deptno, dname from emp join dept using (deptno)	Natural join relies on the fact that deptno column is common to ename and dept table inner keyword is optional and is default. Paranthesis require for deptno
select ename, dname, budget from emp join dept using (deptno) join budget using (deptno)
select ename, dname, budget from emp e join dept d on e.deptno = d.deptno join budget b on d.deptno = b.deptno	multi table join. Joins done left to right
select ename, dname from emp cross join dept	Cartesian product
select e.ename, e.deptno, d.dname from dept d, emp e where d.deptno (+) = e.deptno and d.loc (+) <> 'BOSTON' select e.ename, e.deptno, d.dname from emp e left outer join dept d on d.deptno = e.deptno	left outer join is ANSI syntax. Note (+) is an Oracle Syntax do not mix with ANSI syntax return data even if matching data in other table is not found Left Outer join query, will return rows even if a deptno is null in the emp table Cannot use OR in WHERE
select e.ename, e.deptno, d.dname from dept d, emp e where d.deptno = e.deptno (+) select e.ename, e.deptno, d.dname from emp e right outer join dept d on d.deptno = e.deptno	right outer join is ANSI syntax Outer join query, will return rows even if no emp is assigned to a dept
select e.ename, e.deptno, d.dname from emp e full outer join dept d on d.deptno = e.deptno	Include null from both tables
select e.empno, e.ename, e.job, e.mgr from emp e, emp e2 where e.empno <> e2.empno and e.ename = e2.ename and e.job = e2.job and e.mgr = e2.mgr;	self join
select ename form emp union select dname from dept select ename form emp union all select dname from dept	union returns distinct values union all returns all values Only one order by at the end
select ename form emp minus select dname from dept	data in first but not in second
select count(1), count(rowid), count(*), avg(sal), max(sal), min(sal), stddev(sal), sum(x), variance(x) from emp	Note that count(*) is slow count(rowid) is fast and count(column) counts only non null values. Group functions ignore null values
select avg(all sal), avg(distinct sal) from emp	all: include all non-null value distinct: if two numbers are same count only once
select avg(nvl(comm, 0)) from emp	So that null values are taken as 0 avg(comm) = 550 avg(nvl(comm, 0)) = 157.14
select deptno, job, avg(sal), count(*) from emp group by deptno, job order by 3 desc	deptno, job column have to be before avg
select deptno, job, sum(sal) as salary from emp group by rollup(deptno, job)	total on one dimensional data hierarchy 10,Clerk, 1300 10,Manager, 2000 10, , 3300 20,Clerk,500 20,,500 ,,3800
select deptno, job, sum(sal) as salary from emp group by cube(deptno, job)	n-dimensional cross tabulation 10,Clerk, 1300 10,Manager, 2000 10, , 3300 20,Clerk,500 20,,500 ,,3800 ,Clerk,1800 ,Manager,2000
select deptno, job, avg(sal) from emp group by deptno, job having avg(sal) > 2000	having is a where on a group by, group functions not allowed in where
select e.ename, e.job, e.sal from emp e where not exists (select d.deptno from dept d where d.loc = 'NEW YORK' and d.deptno = e.deptno)	correlated query: subquery refrences columns in parent query order by not allowed in subquery
with summary as (select dname, sum(sal) as dept_total from emp, dept where emp.deptno = dept.deptno group by dname) select dname, dept_total from summary where dept_total > (select sum(dept_total)/3 from summary)	select dname, sum(sal) as dept_total from emp, dept where emp.deptno = dept.deptno group by dname having sum(sal) > (select sum(sal)/3 from emp, dept where emp.deptno = dept.deptno) order by sum(sal) desc
select deptno, ename, job, sal from emp where (deptno, sal) in (select deptno, max(sal) from emp group by deptno)	mulitple column subqueries (highes paid employee in each department)
select e.ename, subq.loc from emp e, (select deptno, loc from dept where loc in ('NEW YORK', 'DALLAS')) subq where e.deptno = subq.deptno	inline subquery
select ename, (select max(sal) from emp e2 where e2.deptno = e.deptno) from emp e;	select query can be in select or order by part
select ename, job, sal, rownum from (select ename, job, sal from emp order by sal) where rownum <= 3	inline query for getting 3 lowest paid employees order by allowed in inline views only
select empno, ename, job from emp connect by prior empno = mgr	return who emp reports to all the way upto CEO. Hierarchical query will join empno column to mgr column. mgr column has empno of manager.

Table Constraints

If it uses keyword constraint then table constraint else column constraint

· Primary Key: Value in column must be not null and unique. Can apply to multiple columns. Automatically creates an index. Index has same name as constraint

· Foreign Key : Values of primary key in parent table may be included as constrained column of child table. Every value in child table appears in parent table

· Unique: Enforces uniqueness on values in the constrained column. Permits null values. Automatically creates an index. Has same name as constraint

· Check: Enforces that values added to the constrained column must be in static list of values for column (e.g State)

· Not Null can be be only column constraint

create table emp ( empno varchar2(5) primary key, ename varchar2(50) not null, sal number(7) default 0 check (sal <= 1000), deptno number(5) references dept (deptno) on delete set null, govt_id number(10) unique ) create table "Test" – stored case sensitive	Requires create table privilege. Max number of columns allowed is 1000. Only one primary key per table. Will create a primary key constraint automatically names SYS_Cnnnn. Automatically creates index references keyword enforces foreign key. Requires dept table be created first. Unique permits null values Static check cannot refer to user, sysdate, currval, nextal, level, uid, userenv, rownum or rowed ALL_TABLES, USER_TABLES
create global temporary table temp_emp (fn as varchar2(10))	creates a table where data persists only for the duration of session or transaction. The tables are available globally. Data is private to session
create table emp2 (empno, sal) as select empno, sal from emp where deptno=10
alter table emp add (quit_date date) alter table emp modify (ename varchar2(25))	when decreasing length reduce data first in 9.2 In 9.1 backup column, set all columns to null and copy it back. To change data type column values should be set to null
alter table emp set unused column sal alter table emp drop unused columns alter table emp drop column salary	mark column as unused, does not delete anything
alter table emp add constraint pk_emp_01 primary key (empno) alter table emp add constraint fk_emp_01 references dept(deptno) on delete cascade alter table emp add constraint ck_emp_01 check (sal <= 25) alter table emp add constraint uk_emp_01 unique (ename) alter table emp modify (ename not null)	Column should exist to add constraint. Foreign key reference example will delete child rows table (emp) when parent deptno is deleted. USER_CONSTRAINTS lists constraints
alter table emp add (constraint pk_emp_01 primary key (empno) deferrable intially deferred)	Do constraint check at end of transaction instead of after every insert/update
alter table emp enable validate primary key alter table emp enable novalidate primary key	enable validate check existing data in table for constraint. enable novalidate check new data entered in table for constraint
alter table emp disable primary key alter table emp disable constraint pk_emp_01 alter table emp disable constraint fk_emp_01 alter table emp disable primary key cascade	Disable a constraint. Foreign key constraint disable can cause Oracle error because of dependancies. To work around problem add keyword cascade. Disabling constraint drops index. Re-enabling constraint rebuilds index. Re-enable will fail if data does not meet constraint
alter table emp enable constraint pk_emp_01 exceptions into exp1	exp1 is a table which should be created (row_id rowid, owner varchar2(30), table_name varchar2(30),constraint varchar2(30))
alter table emp drop unique (govt_id) alter table emp drop primary key cascade alter table emp drop constraint ck_emp_01 alter table emp modify (ename not null)	Remove constraints will drop index.
drop table emp drop table emp cascade constraints	Resets high water mark to 0. drops all associated index, triggers and constraints. To drop a table in somebody else schema you need 'drop any table' privilege Does not drop view
truncate table emp [reuse storage]	delete all data in emp. Cannot rollback. Clean everything. Reset high water mark. Efficient
rename emp to employee alter table emp rename to employee	rename table
comment on table emp is 'Employee table'	stored in user_tab_comments
comment on column emp.empno is 'Employee ID'	stored in user_col_comments
create table emp (empid as varchar2(5), ename varchar2(10), sal number(5), constraint pk_emp primary key (empid, ename))	composite primary key

insert into emp (empid, ename, job, sal) values (10, 'DES', 'CEO', 100) insert into emp values (10, 'DES', null, 100, sysdate)	If no columns then position of columns is as listed in the desc statement
insert into scott.emp (select * from des.emp);
insert all when m.col1 = 'a' then insert into table1 values (m.col2) when m.col1 = 'b' then insert into table2 values (m.col2) select col1, col2 from m	insert into multiple tables
update emp set ename = 'DE', sal = 20 update emp set ename = 'DE', sal = 20 where ename = 'SA' update emp set ename = (select ename from emp where empno = 7844) update emp set ename = default where empno =1234	default keyword sets column to default in table
delete from emp – delete all rows delete from emp where ename = 'DES'	Foreign key constraints will return error
merge into local.movies m1 using master.movies m2 on (m2.movie_name = m1.movie_name and m1.movie_name = 'Matrix') when matched then update set m1.showtime = '7:30PM' when not matched then insert (m1.movie_name, m1.showtime) values ('Matrix', '7:30PM')	(9i Feature) Example updates show times form a master table

Table Locks

· Row Shared (RS)

· Row Exclusive (RX) (update, delete, insert implicitly use this lock)

· Share (S)

· share row exclusive (SRX)

· Exclusive (X)

lock table emp in exclusive mode	prevents other sessions from acquriring any other share or exclusive lock
lock table emp in row share mode	prevents other session from getting exclusive table lock, allows SS, SX, S, SRX
lock table emp in share mode	prevents other session from acquiring RX (row exclusive) lock

Transactions

· Enable you to ensure read-consistency to point in time a transaction began for all users in the Oracle Database.

· Enable you to preview changes before making permanent

· group logically related statements into one logical unit of work

set transaction isolation level read committed	DML statement wait until locks released
set transaction set transaction read only set transaction read write	Transaction begins as soon as you log to · SQL*Plus · after rollback or commit, · user exits SQL*Plus, · system crashes, · a DCL command is issued (e.g alter datatabse)
set transaction isolation level serializable	isolation level read committed is default DML statements gives error ???
commit commit work	save (commit and commit work are same) Erase all savepoints Implict commit occurs on DDL statements or SQL*Plus exit
rollback rollback work rollback to savepoint foo	discard changes
savepoint foo	savepoint xyz; rollback to savepoint foo;

Views are updatable if they do not have distinct, group by, start with, connect by, rownum, set operators (union), subquery in select clause

Complex views can be update only if

· Only one table is being updated

· update is for columns of key preserved table

· insert if no with check option and all columns from key preserved table

· complex views cannot contain set operations (union) group by expressions or pseudo columns or virtual columns

Key Preserved table in complex view (more than one table). It is a table in a view whose primary key column is present and whose values are all unique and not null

SQL DDL (Data Definition Language) (does implicit commit)

create or replace view emp_mgr_view as (select * from emp where job = 'MANAGER') create force view emp_view as (select * from foo where ename = 'SMITH')	Simple views are updatable. If view has group by or order then cannot delete force creates view even when table does not exist.
create or replace view raise_view as (select empno, sal * 1.1 raise from emp) update raise_view set raise = 200 where empno =123 -- error	Alias are required for expressions. Update on alias column generates error. Will be able to delete
create or replace view emp_view as (select * from emp where deptno = 10) with check option create or replace view emp_view as (select * from emp where deptno = 10) with check option constraint emp_view_constraint	Will not allow updates where deptno <> 10. Also cannot change deptno column. Naming constraint is optional
create or replace view emp_mgr_view as (select * from emp where job = 'MANAGER') with read only	Cannot update base table
create view emp_dept as (select empno, ename, job, dname, loc from emp e, dept d where e.deptno = d.deptno and job in ('ANALYST', 'CLERK'))	Complex view updatable only if primary key and foreign key constraints defined. Only one table whose primary column is listed can be update Can update only emp table columns because primary column empno included Cannot update dept.loc column USER_UPDATABLE_COLUMNS lists which columns are updatabe USER_VIEWS
create view emp_det (empid, ename constraint ec unique disable novalidate as select empid, ename from emp	constraints on view must have disable novalidate
alter view emp_view compile	recompile or revalidate view e.g. dropping a table makes a view invalid. Recreating and compiling the table makes it valid. Changing base table column type makes view invalid
drop view emp_view

create sequence foo start with 10 --default 1 increment by –2 -- default 1 maxvalue 10 --default nominvalue minvalue 0 -- default nomaxvalue cycle --loop default nocycle order – default order else noorder cache 2; -- default 20 improve performance	Sequences are global and sharable. Useful for generating primary keys. Set cache if sequence elements less than 20 Sequences cannot be used in subqueries of select statement including having or set operation (union, minus)
select foo.nextval from dual; select foo.currval from dual;
alter sequence foo increment by 2
drop sequence foo

Index

· Index use B Tree algorithm work best if column has lot of unique values. A B Tree is a ordered tree of names. Node will list range of values that child nodes have. Max 32 columns. Good for high cardinality

· Reverse Index: useful for Parallel Server index value is HTIMS for SMITH

· Bitmap index uses one bit to represent each row in the table 1 indicates location of row, best for small number of distinct values e.g. Index contains 4 columns Value, Start Row ID, End Row ID, Bitmap e.g 0001000 mean start row + 3 has value. Each new row adds one bit to index. Each new distinct column value adds a new column. Max 30 columns. Good for low cardinality. Does not support row level locking

· Function based index allows you to use functions or expressions likes sal * 1.1

create index emp_indx_01 on emp (empno)	Must have Create Index privilege To create in another schema must have Create Any table privilege
create unique index emp_indx_01 on emp (empno) create unique index emp_indx_02 on emp (empno, ename)	equivalent to adding a unique constraint
create unique index emp_indx_01 on emp (ename) reverse	Index stores HTIMS for SMITH. Also useful e.g if most names start with SMI i.e reversing provides for more distinct values and a better balanced B-Tree
create bitmap index emp_indx_01 on emp (deptno)
create index emp_indx_01 on emp (sal * 1.1)	Function Based index, Requires Create Index and Query Rewrite Privilege To use this run Alter session set query_rewrite_enabled= true Alter session set query_rewrite_intergrity=trusted
drop index emp_indx_01

create synonym myemp for emp	Creates private synonym visible only within schema
create public synonym allemp for emp	Visible to all users
drop synonym allemp

A schema is a logical grouping of database objects based on the user who owns the objects

connect scott/tiger	connect as user
create user rhea identified by pass	user has no privileges ALL_USERS contains list of users USER_USERS info about user logged in
create user OPS$rhea identified externally connect / sqlplus /	host authentication OPS$ prefix required allows rhea to be authenticated against OS userid
alter user rhea identified by newpass	change password
alter user rhea account unlock	unlock account

System Privileges

· Database Access: e.g create session

· Users: e.g. create user, become user

· Tables: create table, backup table, lock table

· Indexes: create any index

· Synonyms: create synonym

· Views

· Sequences

· Roles

· Transactions: Resolve in doubt tranasactions, force transaction and force any transaction

· PL/SQL: create procedure, execute procedure

· Triggers: create trigger

Object privileges

· select: select on table, sequence, view or snapshot

· insert: Can restrict to only certain columns

· update

· delete

· alter: Alter definition of table or sequence alter on other objects is system privilege

· index:

· references: add or create foreign key

· execute: run stored procedure or function

Rules for granting/revoking privileges

· if privilege granted on 2 columns, then privilege has to be revoked on both column

· for references do revoke references on emp from rhea cascade constraints

· privileges should be granted to owner of stored procedure not executor and have to be done as a user not role

· grant/revoke to users, roles or PUBLIC immediately effective

· grant/revoke of roles to users, other roles or PUBLIC are effective on new session or when set role
is run

SQL DCL (Data Control Language)

grant create session to rhea grant create table to rhea grant create sequence to rhea grant create procedure to rhea revoke create session from rhea	grant, revoke system privileges USER_SYS_PRIVS : privileges for logged in user SESSION_PRIVS : privileges for session
grant select, update on emp to rhea	grant object privileges
grant select, update, references on emp to rhea grant references on emp.empno to rhea, foo grant select on emp to public grant all on emp to rhea
grant create table to rhea with admin option	rhea can now grant create table to other users
grant select, update on scott.emp to rhea with grant option	can grant access to emp to other users
revoke select, update on scott.emp from rhea	when system privilege with admin option is revoked it does not cascade e.g a gives to b and b to c, if a revokes b, c not affected when object privilege is revoked it cascades

Roles

· Logically group certain types of users based on privileges they need

· Define a role for each user type

· Grant privileges to role

· Grant roles to users

Predefined Roles

· CONNECT: Create table, cluster, session, view, sequence cannot create stored procedure

· RESOURCE: create table, clsuter, trigger, procedure

· DBA: All system privileges

create role deleter_role
alter role deleter identified by pass
Grant delete on emp to deleter
Grant deleter_role to rhea
alter user rhea default role none
set role deleter_role identifed by pass	in session
revoke deleter_role from rhea
drop role deleter_role	USERS_ROLE_PRIVS: identifies roles granted to you ROLE_ROLE_PRIVS: identifies roles granted to other roles ROLE_TAB_PRIVS: object privs granted to roles ROLE_SYS_PRIVS: system privs granted to roles SESSION_ROLES: roles available to current session

create profile power_user limit idle_time unlimited	profiles are used for limiting resource consumption or setting password policies
alter profile default idle_time 5

String Functions

||	Concat e.g. fname || lname
ASCII(chr)	ASCII number of first character in string ASCII('ABC') = 65
ASCIISTR(str)	asciistr('hÉllo')='h\00901lo'
CHR(n)	opposite of ASCII
CONCAT(str1, str2)
INITCAP(str)	capitalizes first letter of the word
INSTR(string, set [,start [,occurrence]])	finds the location of character in string instr('hello world', 'o', 0, 2) = 8 finds second occurence of 'o' from start
LENGTH(str)	Length in characters
LENGTHB(str)	Length in bytes
LOWER(str)
LPAD(str, number [,str])	makes string certain length by padding from left
LTRIM(str [,set])	left trim, keep removing till character not in set ltrim('mississipi', 'mis') = 'pi' because 'p' not in 'mis'
REPLACE(c1, c2 [,c3])	Replace('uptdown', 'up', 'down') = 'downtown'
RPAD(str, number [,str])	right pad e.g rpad(city,35) will pad with ' ' or rpad(city, 35, '.') will pad with '.'
RTRIM(str [,set])	rtrim(str, charset) remove all the characters in the charset rtrim('ABF', 'ABCD') = 'F'
SOUNDEX(str)	finds word that sounds similar soundex('sydney') = soundex('sydniy')
SUBSTR(string, start [,count])	default till end of string substr('helloworld', 3, 4) = 'llow' substr('helloworld', - 5) = 'world'
TRIM(…. str)	trim('S' from ename) trim(leading 'S' from ename) like ltrim trim(trailing 'S' from ename) like rtrim
UPPER(str)

NLS support (National Language Support) String functions available are NLSSORT, NLS_INITCAP, NLS_LOWER, NLS_UPPER

DECODE is a lookup table function e.g DECODE(deptno, 10, 'Accounting', 20, 'Sales', 'Other') 'Other' is the default value

Timezone

· GMT Greenwich Mean Time

· NST Newfoundlad Standard Time

· AST Atlantic Standard Time

Date functions

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

(date1 – date2)/7	number of weeks between two dates
date1 + 180
ADD_MONTHS(date, months)
CURRENT_TIMESTAMP CURRENT_DATE	Current_timestamp()='11-JUN-03 02.30.14.652909 PM -04:00'
DBTIMEZONE	Dbtimezone() = '-04:00' for EST alter database set time_zone='US/Central'; shutdown; startup
FROM_TZ(ts, tz)	From_tz(localtimestamp, '-07:00')=
EXTRACT(year from sysdate) month, day, hour, minute, second, timezone_house, timezone_minute, timezone_region	extract(year from sysdate)
LOCALTIMESTAMP	Localtimestamp()='11-JUN-03 02.33.30.420124 PM'
LAST_DAY(date)	last day of month e.g. 31-May-03
MONTHS_BETWEEN(date1, date2)	months_between('15-MAR-00', '26-JUN-99') = 8.6451613
NEW_TIME(date, timezone1, timezone2)	converts date from timezone1 to timezone2 e.g new_time('15-MAR-1999 14:35:00', 'AST', 'GMT') = 15-MAR-1999 18:35:000
NEXT_DAY(date, dayofweek)	next_day('11-Jun-03', 'Thursday')='12-Jun-03'
ROUND(date [,format])	round('11-Jun-03', 'HH24')=
SYSDATE	current date in dd-mon-yy format
SYS_EXTRACT_UTC(timestamp)	Convert to GMT
SYSTIMESTAMP
TRUNC(date, [format])
TZ_OFFSET(timezone)	tz_offset('US/Eastern')='-04:00'

Data conversion functions

Note that parameters should be meaningful e.g to_number takes string which only contain number

BIN_TO_NUM(b1,b2,b3)	bin_to_num(1,1,1) = 7
CAST(val AS type)	cast('123' as number)=123
CHARTOROWID(x)	Converts String of characters to rowid
COMPOSE(str) DECOMPOSE(str)	returns UNICODE string in full normalized form COMPOSE(UNISTR('\0303'))
ROWIDTOCHAR(x)	Converts ROWID to Varchar2
HEXTORAW(hexstring)	Convert hex string to raw binary datatype
RAWTOHEX(rawstring)	convert raw string ot hex
CONVERT(str[,newcharset[,currentcharset]])	convert string from currentcharset to newcharset
TRANSLATE(x,y,z)	translate('ABBC','BB', '22')='A22F'
TO_CHAR(x [,fmt [,nlsparm]])	e.g to_char(906) = '906' to_char(sysdate,'Day Month YY','nls_date_language=German')
TO_DATE(x [,dateformat])
TO_NUMBER(x)	to_number('134') = 134 to_number('SE') produces an error
TO_MULTI_BYTE(single_byte_string)	convert single byte string to multibyte string
TO_SINGLE_BYTE(multi_byte_string)	convert multi byte string to single byte

Date Formating

DD	two digit date
DY	Mon, Tue
DAY	day spelled out
MON	3 letter month e.g MAR
MONTH	Month spelled out
YY	2 digit year
YYYY	4 digit year (not millenium compliant)
RR	2 digit year millenium compliant
HH	2 digit hour AM/PM format (use MIAM mask)
HH24	24hour format
MI
MIAM	2 digit minute in AM/PM format do not use with HH24
SS
TZD	Timezone with daylight savings

Number Formating

9	99.9 =12.3
0	09.90=01.20
,	9,999.9=1,234.5
$	$99=$12

Math Functions

ABS(value)	ABSolute value ABS(-53) = 53
ACOS(value)	Arc COSine of value, in radians
ASIN(value)	Arc SINe of value, in radians
ATAN(value)	Arc TANgent of value, in radians
ATAN2(value1, value2)	returns the arc tangent of two values, input values are unbounded, outputs are expressed in radians
BITAND(value2, value2)	BITwise AND of value1 and value2 both of which must resolve to nonnegative numbers, and returns integer
CEIL(value)	Numeric CEILing: the smallest integer larger than or equal to value ceil(-1.6) = -1 ceil(2.2) = 3
COS(value)	COSine of value
COSH(value)	Hyperbolic COSine of value
EXP(value)	e raised to value of exponent
FLOOR(value)	Largest integer small than or equal to value
LN(value)	Natural logarithm of value
LOG(base, value) 9.2 syntax LOG(value)	Base 10 Logarithm of value
MOD(value, division)	MODulus is remainder after division MOD(55.5, 10) = 5.5 MOD(8, 3) = 2
NVL(value,substitute)	substitute for value if NULL
NVL2(value, notnullvalue, ifnullvalue)
POWER(value,exponent)	value raised to an exponent power
ROUND(value, precision)	ROUNDing of value to precision ROUND(5.247, 2) = 5.25 ROUND(51.247, -1) = 50
SIGN*(value)	1 if value positive, -1 if negative SIGN(-34) = -1 SIGN(34) = 1
SIN(value)	SINe of value
SINH(value)	Hyperbolic SINe of value
SQRT(value)	SQuare RooT of value
TAN(value)	TANgent of value
TANH(value)	Hyperbolic TANgent of value
TRUNC(value,precision)	value TRUNCated to precision TRUNC(329, -1) = 320 TRUNC(322.256, 2) = 322.25
VSIZE(value)	Storage size of object value vsize(ename)

Group Math Functions

AVG(value)

CORR(value1, value2)

COUNT(value)

COVAR_POP(value1, value2)

CUME_DIST(value1, value2)

DENSE_RANK(value)

FIRST(value)

GROUP_ID(value)

GROUPING_ID(value)

GROUPING(value)

LAST(value)

MAX(value)

MIN(value)

PERCENTILE_CONT(value)

PERCENTITLE_DISC(value)

PERCENT_RANK(value)

RANK(value)

STDDEV(value)

STDDEV_POP(value)

SUM(value)

VAR_POP(value)

VAR_SAMP(value)

VARIANCE(value)

List Functions

COALESCE(value1, value2,...)

GREATEST(value1, value2,...)

LEAST(value1, value2,...)

Datatypes

VARCHAR2(n)	variable length upto 4000 bytes
NVARCHAR2(n)	single-byte or multi-byte string upto 4000 bytes
CHAR(n)	Fixed length string upto 2000 bytes, space is padded
NCHAR(n)	single byte or multi byte upto 2000 bytes. Stores in Unicode character data
NUMBER(n[,m])	Upto 38 digits upto 38 digits after decimal (mantissa) if m not specified Oracle allows upto 38 e.g NUMBER(4,2) allows only 2 digits before decimal. 999.99 gives error 9.999 rounds to 10
DATE TIMESTAMP	7 bytes in length, Dates start at 4713 B.C.E
INTERVAL YEAR (3) TO MONTH	store difference between dates
RAW	variable length binary data upto 2000 bytes
LONG	text data upto 2 GB, only one LONG per table
LONG RAW	binary data upto 2GB, only one LONG RAW per table, preferable to use LOBs
ROWID UROWID	Pseudo column in every table. Address of rows, can be logical or physical. Unique for each row
BLOB	Binary Large Object upto 4GB (Oracle8 and later) if values less than 4K stored inline. More than one LOB per table
CLOB	character data upto 4GB
NCLOB	single byte or multi byte Unicode upto 4GB (Oracle8 or later)
BFILE	Points to file outside of database (Oracle8 or later)

sys_context replaces deprecated userenv(paramname) for 9i

paramname: isdba, sessionid, entryid, instance, language, lang, terminal

sys_context(namespace,paramname,[.length])

sys_context('userenv', 'sessionid')

AUDITED_CURSORID
CURRENT_SCHEMA
CURRENT_SQL
CURRENT_USER
DB_DOMAIN
DB_NAME
HOST
INSTANCE	1
IP_ADDRESS
ISDBA
LANG	'US'
LANGUAGE	AMERICAN_AMERICA.WE8DEC
NLS_CALENDAR
OS_USER
SESSIONID
TERMINAL	'pts/1'

SQL*Plus

$ORACLE_HOME/bin/sqlplus userid/password@tnsalias

tnsalias is defined in $ORACLE_HOME/network/admin/tnsnames.ora

e.g. sqlplus scott/tiger@orcl

· set serveroutput on (will show output of dbms_output.put_line )

· show errors (to show compliation errors)

· select dump(ename) from scott.emp; -- dump as sequence of characters useful for figuring out globalization issue

SQL*Plus reads login.sql for settings for current user and glogin.sql for all users (first from current directory and then $ORACLE_HOME/sqlplus/admin)

Load help by running <ORACLE_HOME>\sqlplus\admin\help\helpbld.sql helpus.sql

End commands with / for multi line commands, only SQL and PL/SQL commands stored in buffer

Commands not available in iSQL*Plus

· accept, clear screen, exit, get, host, password, pause, save, spool, store, whenever oserror exit, when sqlerror exit

· set commands not available: colsep, editfile, flush, newpage, pause, shiftinout, sqlblanklines, slqcontinue, sqlnumber, sqlprefix, sqlprompt, suffix, tab, termout, time, trimout, trimspool

-- This is a comment
connect scott/tiger
disconnect
exit quit
desc emp describe emp	desc tables or PL/SQL package
spool activity.lst spool out spool off	send output of all comands to file Both spool out and spool off turn off spooling
store set file.out get file.out	save all settings to file loads settings
password scott	change password
clear screen
host ls	Run os commands

show

errors	display PL/SQL or view compile errors
parameters param	show parameters db_block_size (from init<sid>.ora)
release	database release
repfooter
repheader
sga
sqlcode	return code of most recent sql command
btitle ttitle
user

Reporting related

rem This is a comment /* this is a comment */
ttitle 'top title' btitle 'bottom title' ttitle off
column ename format a20 column ename heading 'name' column ename heading 'name' word_wrapped justify right format a30 column sal format $999.99 clear columns	wrapped is default, you can use truncate
column empno heading emp|no format 0999	split column across multiple lines
column ename	display column settings
column ename clear
break on deptno skip 1 on report clear breaks	similar to group by skip will insert blank lines when group by changes select deptno, ename, sal from emp order by deptno 10 clark king 20 smith
compute avg of sal on deptno compute sum label "Dept Total" of sal on deptno clear computes	works with break and does average on group 10 2450 2500 ------ Dept Total 4950
select &col from emp Enter value of col: define ename=SMI select ename from emp where ename = '&ename.TH';	SQL*Plus will prompt for &variables. Use ' for date and string. You can define variable settings using define. String after . is literal e.g user enter SMI it become SMITH
select &&col from emp	User is prompted for col only once. SQL*Plus retains value for session
select &1 from &2 save foo @foo ename emp	Positional parameters
define var=value undefine var	set and unset variable value
accept var prompt 'Enter var info' accept pwd char prompt accept vhd date prompt	Accept describe prompt to display for variable and ask for input
get file.sql @file.sql @@file.sql	run file.sql @@ used when contents of one sql files executes another

set environment variables, use show to display value, underlined is abbreviated command

arraysize n	set number of row to retrieve at a time
autocommit off|on|immediate|n	on and immediate are same n is number of data change commands or PL/SQL Blocks after which to commit
autotrace off|on|traceonly|explain|statistics
cmdsep ;|char|OFF|ON	on sets it to semicolon
colsep " "|text	default is blank
define ?	Change variable substitution character from & to ? select ?col from emp;
echo off|on	wether start file lists each command being executed
editfile fname.ext	default edit file
embedded off|on	where each report begins on the page
escape \|c|off|on
feedback 6|off |on	do not display number of rows returned after query
flagger off|entry|intermediate|full	check that SQL confirms to ANSI/ISO SQL92
flush off|on
heading off|on	wether to print header for query results which contains list of columns
headsep ||c|off|on
linesize 80|n	chars to display in a line
long 80|n	display length for displaying LONG, CLOB, NCLOB
longchunksize 80|n	byte increments to fetch LONG, CLOB, NCLOB
newpage 1|n|none	number of blank lines to print from top of page to title
null text	text to display for a null
numformat format	default number display format
numwidth 10|n
pagesize 24|n	number of rows to display before SQL*plus show rows header I think the default is 12 lines
pause off|on|text	pause at end of page
serveroutput [off|on] [size n] [format wrapped|word_wrapped|truncated]	display of dbms_output.put_line set this when you get buffer overflow set serverout on set serveroutput on size 999999
showmode off|on	to show output of environment settings when set is run
sqlblanklines on|off	whether to allow blank lines in sql command
sqlcase mixed|lower|upper	convert case before execution
sqlcontinue >|text	prompt to display after entering – and return
sqlnumber off|on	prompt for second and third lines
sqlprefix #|c
sqlprompt sql>|text
sqlterminator ;|c|off|on
suffix sql|text	default file extension, default is .sql
termout of|on	display output of commands run from file
time off|on	change SQL Prompt to show time e.g 11:10:04 SQL>
timing off|on	display timing statistics after each SQL command run
trimout off|on	whether blank lines removed after each displayed line
trimspool off|on	whether blank lines remove at the end of each spooled line
underline -|c|on|off	character used for underlining result columns for SQL command
verify off|on	display text before and after variable substitution
wrap off|on	set wrap on will display text across multiple lines set wrap off will truncate the lines

SQL Buffer editing

edit change
l list	list buffer
a append
c /=/<> change
I input	input lines of text like append
del del last del 3 *	delete current line del 3 * del from 3^rd line to current line
clear buffer
save file save file.sql save file append	saved as file.sql, will not overwrite
replace file
edit file

Data Dictionary

ALL_ show objects owned by user, accessible by user or is PUBLIC

USER_ objects owned by user

DBA_ See all objects

SESSION_ available only for the session

Note SELECT_CATALOG_ROLE provides access to DBA views
 
tables with $ are dynamic performance view
tables with # are owned by sys
 

cat	table_name, table_type
dict dictionary	table_name, comments	description of all the views in the dictionary
product_component_version	product, version, status	product name and version

user_type_attr	attr_Name, length, attr_type_name	list of object types
all_col_privs dba_col_privs user_col_privs	grantor, grantee, table_schema, table_name, privilege	Column privileges granted to the user or to PUBLIC for which the user is owner
all_col_privs_made user_col_privs_made	grantee, owner, table_name, column_name , grantor	column privileges granted on tables and views where user is onwer or grantor
all_col_privs_recd user_col_privs_recd	grantee, owner, table_name, column_name , grantor	column privileges granted on table or view where user is owner or grantor
all_cons_columns dba_cons_columns user_cons_columns	owner, constraint_name, table_name, column_name	table column constraints
all_constraints dba_constraints user_constraints	constraint_name, constraint_type, search_condition, index_name	constraints on table or view constraint_type : V for View
dba_data_files dba_segments dba_extents dba_free_space	file_name, tablespace_name, bytes segment_name, tablespace_name, bytes segment_name, tablespace_name, bytes
all_ind_columns dba_ind_columns user_ind_columns	Index_name, index_type, table_name, column_name	table columns that have indexes
all_directories	owner, directory_name, directory_path	Directory name and alias
all_indexes user_indexes user_indexes	Index_name, index_type	Name and detail of index
all_objects dba_objects user_objects	object_name, object_type, status	object_type e.g table, materialized view, table partition
dba_profiles	profile, resource_name, resource_type	resource constraints
dba_role_privs user_role_privs	username, granted_role	roles granted to user
all_roles dba_roles user_roles	role, password_required
all_sequences dba_sequences user_sequences	sequence_owner, sequence_name, last_number
all_source dba_source user_source	name, text	source code for PL/SQL Program
dba_sys_privs user_sys_privs	username, privilege	system privileges granted to user
all_tab_columns dba_tab_columns user_tab_columns	table_name, column_name, data_type	columns in a table
all_tab_privs dba_tab_privs user_tab_privs	grantee, owner, table_name, table_schema, privilege	object privileges e.g select privilege granted on a table
all_tab_privs_made user_tab_privs_made	grantee, owner, table_name, grantor, privilege
all_tab_privs_recd user_tab_privs_redc	grantee, owner, table_name, grantor, privilege
all_tables, dba_tables user_tables	table_name, tablespace_name	list of tables
dba_tablespaces	tablespace_name, block_size, contents	contents indicates permanent or temporary
dba_temp_files	file_name, tablespace_name, blocks, autoextensible, increment_by	create temporary tablespace files
dba_ts_quotas	tablespace_name, username, bytes
all_triggers dba_triggers user_triggers	trigger_name, trigger_type, trigger_event, table_owner, table_name, description
all_updatabale_columns dba_updatabale_columns user_updatable_columns	column_name, updatable	lists which columns are updatabe. Useful for view
all_users dba_users user_users	username, user_id, created default_tablespace, profile
all_views dba_views user_views, all_views	view_name, text	text contains sql

role_role_privs	role, granted_role	identifies roles granted to other roles
role_sys_privs	role, privilege	system privileges granted to roles
role_tab_privs	role, owner, table_name, column_name, privilege	object privileges granted to roles
session_privs	privilege	privileges for session
session_roles	role	roles available to current session