General
set timing on
set autotrace on explain; #explain automatically
set lines<ize> 80; #linesize
set wrap off #no wrap
set pagesize 24
set feedback off –show or not show * rows selected
set heading off –show or not show column name
set verify off –(not)show substitution before/after
set trimspool off #strip whitespaces for columns
set echo on #(not)show commands executed
set define on/off #enable/disable ampersand substitution
col file_name format a60; column
; #show last command
/ #redo last command
define fil= ‘/var/tmp/a.txt’
prompt Spooling to &fil
spool &fil
select * from tab;
spool off
sqlplus ‘sys/password@devdb as sysdba’ #first export ORACLE_HOME(no trailing /) and set tnsnames.ora
sqlplus /nolog #no initial connection
sqlplus>conn sys/password@sid as sysdba
user_ #current user’s, user_tables
all_ #current user’s, include granted
dba_ #all, dba_users, dba_indexes, dba_tables, dba_objects
dictionary #data dictionary’s data dictionary
v$fixed_table #dynamic view’s dynamic view
v$process #oracle server processes(does not include client side info)
select pid,serial#,spid,program from v$process; #pid, serial# is PID allocated from Oracle Software; spid is OS pid; program is OS process name mapped to Oracle Service process
select p.spid,s.sid,s.serial#,s.username,s.status,s.last_call_et,p.program,p.terminal,logon_time,module,s.osuser from V$process p,V$session s where s.paddr = p.addr and s.status = ‘ACTIVE’ and s.username like ‘%SYS%’; #all the active sessions for sys user
v$session #client info. paddr is v$process’s addr(server process addr, such as 00000000C4505F10); server is dedicated or shared.
select machine,osuser,program,process,command,server from v$session where machine = ‘centos-doxer’; #process is client’s OS PID(on centos-doxer for example)
V$SYSSTAT, V$SESSTAT, V$MYSTAT, V$SERVICE_STATS #System Statistics views
v$segment_statistics #Segment Statistics views. when parameter STATISTICS_LEVEL is typical/all. Three statistics report global cache activity at the object level, and you can use these to quickly pinpoint the segments in your database that are experiencing the highest levels of inter-instance activity: gc cr blocks received, gc current blocks received, and gc buffer busy.
show parameter db_create_online_log_dest; #if not set, then control files/online redo logs will multiplexed to the FRA and DATA disk group; if set, then the control files and online redo logs will be created in that location instead. Specifying multiple different destinations provides greater fault tolerance
SQL> show parameter db_create_file_dest; #+DATA
select component,current_size,min_size,max_size from V$MEMORY_DYNAMIC_COMPONENTS where current_size <> 0 #size of PGA/SGA/shared pool/large pool
SQL> alter system set control_files=’xxx’,'xxx’,'xxx’ scope=spfile #next reboot, show parameter control_files;
SQL> select * from v$controlfile; — control file information
SQL>create public synonym part_master for inv.part_master; #other users can use(if granted privileges), can across DBs<through db link>
SQL>drop public synonym part_master;
show parameter spfile; #oracle/grid
show parameter dump_dest;
show parameter asm_power_limit #power of RBAL(1 to 11, should be set to night)
SQL> alter system set asm_power_limit=5; #su – grid
SQL>alter tablespace tbs1 read only #drop table test; is ok, because drop only change the name in dictionary
SQL>alter user hr quota 500m/unlimited on <tablespace>;
SQL>alter session/system set … scope #spfile(next reboot)/memory(only current)/both(this is default)
SQL>create pfile=’xxx’ from spfile;
SQL>create spfile from pfile;
SQL>alter session set current_schema = hr;
SQL>select username from dba_users; #all users
SQL>select distinct owner from dba_objects; #schemas available
SQL>select username,ACCOUNT_STATUS from dba_users;
SQL>select * from global_name; #current database
SQL>select table_name from dba_all_tables where owner = ‘HR’;
SQL>select to_char(WMSYS.WM_CONCAT(dba_users.username)) user_list from dba_users where expiry_date-sysdate<15 and username not in (select username from dba_users where expiry_date<sysdate); #expire in 15 days
SQL>select table_name from user_tables;
SQL> select OWNER,TABLE_NAME from dba_all_tables t where t.TABLE_NAME like ‘OVS_OS_RESOURCE%’; #like ‘_VSOS_RESOURCE%’
SQL>select * from tab; #all tables and views for current user
SQL>select * from cat; #all tables and sequences for current user. cat is synonym of user_catalog
SQL>create sequence cust_id_seq start with 104501;
SQL>select cust_id_seq.nextval from dual; #104501, 104502
SQL>show user
SQL>create user “newuser” profile “default” identified by “newpassword” password expire default tablespace “users” temporary tablespace “temp” quota unlimited on temp quota unlimited on users account unlock;
SQL>grant “connect” to “newuser”; #connect is role, others are like DBA, resource, Imp_Full_Database, Select_catalog_role
create role “testrole”; #drop role xxx
grant connect to “testrole”
grant execute any procedure to “testrole”
grant select on “table_name” to “testrole”
#profile, password management
SQL> select * from v$pwfile_users; #list users with SYSDB or SYSOP or SYSASM privileges
create profile “newpolicy” failed_login_attempts 3 password_lock_time unlimited password_life_time 60 password_reuse_time 120 password_verify_function complex_password; complex_password is self-defined function
alter user newuser profile newpolicy;
SQL>alter user xxx account unlock; #alter user xxx account unlock identified by xxx;
SQL>password <account>;
SQL>GRANT SELECT ON hr.jobs to test; #with grant option,
SQL>grant all privileges to test; #object privileges(select, insert, update, delete单个named object)/system privileges(alter system/create user/create table, dba_sys_privs)
SQL>revoke create table from joe #not cascading
SQL>select * from session_privs;
SQL>select * from session_roles;
SQL>select owner, object_name, object_type from dba_objects where status ^= ‘VALID’;
select job_id from jobs where job_id like ‘SA_%’;
select job_id from jobs where job_id like ‘SA\_%’ escape ‘\’;
select last_name from employees where commission_pct is NULL; # =NULL is wrong
SQL>create table tab1 tablespace tbs1 as select * from tab2;
SQL>truncate table emp; #DDL, can not rollback or commit, but it’s quicker
SQL>create index id on obj(object_name);
SQL>alter table obj move; #invalid index
SQL>alter index id rebuild; #rebuild index
SQL>create tablespace tbs1 datafile ‘+DATA’ size 100m EXTENT MANAGEMENT LOCAL uniform size 1m;#OMF, oracle managed files. use uniform size to specify extent size(or use AUTOALLOCATE for system management)
SQL>alter database datafile ‘/u01/oradata/ora11g/example01.dbf’ autoextend on next 50M maxsize 5000M;
SQL> alter database datafile ‘+DATA/PROD/DATAFILE/users.259.679156903′ resize 10G;
SQL>create database link p6 connect to hr identified by oracle_4U using ‘p6′; select * from jobs@p6; #query across different hosts, cross query
SQL>delete from OVS.OVS_SITE where SITE_NAME=’test’; #where … and/or …; where … != …; where year not between 1936 and 1967; where year not in (’1987′, ’1989′)
select employee_id, last_name, phone_number from employees where last_name = ‘&LASTNAME’ or employee_id = &EMPNO; #ampersand(&) substitution
select first_name, last_name from employees where last_name like ‘%&&SEARCH%’ and first_name like ‘%&SEARCH%’; #ampersand substitution of two same item. && is the value
select first_name, job_id, &&col from employees where job_id in (‘MK_MAN’,'SA_MAN’) order by &col; #ampersand substitutes the query column, for example, ‘salary’ column
undefine col;
define; #can get info about db version, editor, user-defined variables, etc
define col=salary
SQL>select ‘a”s yes’ from dual; #use ‘ to escape ‘
SQL>select q’<a’s yes>’ as “test Q” from dual; # (round brackets), {curly braces}, [square brackets], <angle brackets>, use q’<>’ to escape
SQL> select employee_id,end_date – start_date as days from job_history;
SQL> select last_name from employees where hire_date < ’2009/12/31′;
NLS_DATE_FORMAT=yyyy/mm/dd hh24:mi:ss; NLS_LANG=american_america.AL32UTF8 -> National Language Support.
select * from V$NLS_PARAMETERS; for querying the current setting.
alter session set NLS_NUMERIC_CHARACTERS=’, ‘; #, as decimal point and use space as separator for thousands
SQL> alter session set NLS_DATE_FORMAT=’yyyy/mm/dd hh24:mi:ss’; #time format
SQL> select first_name, hire_date from employees where hire_date between ’1994/12/10′ and ’2009/12/31′;
select INSTANCE_NUMBER||’,'||INSTANCE_NAME||’,'||HOST_NAME from v$instance; #|| for concatenation, output will be like 1,devdb1,node1.us.test.com
alter table emp modify (comm number(4,2) default 0.05);
ALTER TABLE tablename DROP UNUSED COLUMNS;
SQL> insert into OVS.OVS_OS_RESOURCE as select * from OVS.OVS_OS_RESOURCE_BACKUP;
SQL>insert into timestamp_test values (sysdate); #insert into … values (‘xxx’);
SQL> select region_name from regions natural join countries where country_name=’Canada’; #ANSI natural join(no need to specify the column for joining). other ANSI join includes join table2 using/join table2 on xxx=xxx/left, right, full outer join table2 on xxx=xxx/cross join
select e.employee_id, e.department_id EMP_DEPT_ID, d.department_id DEPT_DEPT_ID, d.department_name from departments d left outer join employees e on (d.DEPARTMENT_ID=e.DEPARTMENT_ID) where d.department_name like ‘P%’; #left outer join, will return all values of the left table(departments). for the right table(employees), empty values will be returned
select e.last_name, d.department_name from departments d right outer join employees e on (e.department_id=d.department_id) where e.last_name like ‘G%’; #all employees will be returned, even include ones without department id
SQL>select a.name, a.birth_year,a.father, b.birth_year from family a, family b where a.father = b.name; #self join
select F1.name Mum, F3.name Dad, F2.name Child from family F1 join Family F2 on (F2.mother_id=F1.id) join family F3 on (F2.father_id=F3.id);
select count(*) from regions cross join countries; #cross join for multiplying, 25*4=100 rows
SQL>select … from … where prod_list_price = (select max(prod_list_price) from products); #subquery
SQL>select substr(prod_category,1,22) Category, substr(prod_name,1,39) Product,prod_list_price List from products p where prod_list_price > (select avg(prod_list_price) from products where p.prod_category = prod_category) order by substr(prod_category,1,22), prod_list_price desc; #corelated subquery. The main query retrieves the Category, Product, and List Price details for each product that is greater than the average list price of all products within its category. Data from the subquery’s product category is joined with the main query’s product category and referenced by the main query’s table alias.
select E.job_ID current_job, last_name||’ can earn twice their salary by changing jobs to: ‘||J.job_id options, E.salary current_salary, J.max_salary potential_max_salary from employees E join jobs J on (2*E.salary < J.max_salary) where E.salary>5000 order by last_name; #nonequijons
SQL>select * from x union select * from y; #without duplicates
SQL>select * from x union all select * from y; #with duplicates
SQL>select * from x intersect select * from y; #rows that exist in both tables
SQL>select * from x minus select * from y; #rows only in x table
not null/unique/primary key/foreign key/check/ #types of integrity constraints; deferrable/not deferrable
create table check_constraint_example (col1 char(1) constraint check_col1 check (col1 in (‘B’,'G’,'N’))); #check
Partitioned table #with each partition implemented in a different tablespace. #data partitioning. alter table <abc> drop partition <xyz>
create table sales (
prod_id number not null,
total_amount AS (quantity_sold * amount_sold), #virtual column
……
time_id date not null,
storage (initial 65536 minextents 1 maxextents 2147483645)
partition by range (time_id) #type of partitioning<range/list/hash/composite(subpartition)/reference<foreign key>/virtual column-based/interval> and the partition key
#partition by list (cov_type_cd) (partition cov_life values (‘term 64′,’UL’,'ADB’) tablespace cov_gib_ts, …..)
(partition sales_200701 values less than (to_date(’2007-02-01′,’YYYY-MM-DD’)) tablespace sales_ts_200501,
partition sales_200702 values less than (to_date(’2007-03-01′,’YYYY-MM-DD’)) tablespace sales_ts_200502,
……
partition sales_max values less than (maxvalue) tablespace sales_ts_max
)
);
#local partitioned indexes
create index sales_idx_l1 on sales (time_id) local (partition sales_idx_200501 tablespace sales_ts_idx_200501,……) #oracle will manage indexes now(can not remove/add indexes manully)
#Global Partitioned Indexes, if the data is partitioned by monthly ranges, then you can define global index partitioned by product. thus partition pruning cannot occur for the data partitions due to the predicates of a query, index partition pruning may still be possible with the global partition index. rebuild index after dropping a partition
Prefixed and Nonprefixed Partition Indexes #prefixed performs better. An index is prefixed when the leftmost column of the index key is the same as the leftmost column of the index partition key. the index partitions to be scanned will be based on pruned data partitions
Clustered tables: two tables with a close link between them can have their data stored together in a single block to improve join operations.
#Data compression
create table ….. compress;
alter table sales move compress; #uncompress
#Index Key Compression
create index comm_sr_prod_idx on commission (sales_rep_id, prod_id) compress 1; # index compression cannot be used on a unique index that has only one attribute. 1 is the number of prefixing (leading) columns to compress. In this case, you used a value of 1 to indicate that duplicate values of the first column, sales_rep_id, are to be removed.
#Parallel Processing
alter session enable parallel dml; #default for DDL(create/alter) and query, default disabled for DML(insert/update/delete/merge)
create table …… parallel;
select /*+ parallel (commission, 4) */ prod_id, sum(comm_amt), count(*) from commission group by prod_id; #parallel hint, 4 is the degree of parallelism(default is the number of cpus * PARALLEL_THREADS_PER_CPU)
#query rewrite & materialized view
create materialized view comm_prod_mv tablespace comm_prod_mv_ts storage (initial 50k next 50k) refresh complete next sysdate + 7 enable query rewrite as select sales_rep_id, prod_id, comm_date, count(*), sum(comm_amt) from commission group by sales_rep_id, prod_id, comm_date;
Structures
set serverout on size 10000 #print the server message
declare
l_counter number :=0; –l_counter number :=(12,2);
l_today date :=sysdate; –L_start_date := to_date(’09-JUN-91:13:01 ‘, ‘DD-MON-YY:HH24:MI’);
l_name varchar2(50);
l_seq_val number :=cust_id_seq.nextval; –assigns the next sequence
begin
l_counter :=l_counter+1;
l_name := ‘LUCYTHECAT’;
dbms_output.put_line (l_today);
end;
/ #run the procedure
show errors;
edit #edit above procedure
SQL>
declare
v_val varchar2(80);
v_val2 varchar2(60);
cursor get_data is
select VAL from test;
begin
open get_data;
fetch get_data into v_val;
dbms_output.put_line(v_val); –print only the first row
v_val2 := CASE v_val
when ‘abc’ then ‘is abc’
when ‘def’ then ‘is def’
else ‘neither abc nor def’
END; –case … END
dbms_output.put_line (v_val2);
close get_data;
EXCEPTION
when no_data_found then
raise_application_error (-20052,’sorry no data found’); –use -20000 to -20999
when others then
raise_application_error (-20999,’something has gone wrong’);
end
;
/
##LOOP
SQL> declare
v_val varchar2(80);
cursor get_data is
select VAL from test;
begin
for i in get_data –no need for open/close cursor
LOOP
dbms_output.put_line(i.VAL); –i is get_data cursor
end LOOP;
end;
/
LOOP
IF l_bank_balance >= 0 then EXIT;
ELSE
L_decision := ‘ACCOUNT OVERDRAWN’;
END IF;
END LOOP;
WHILE l_sales_total < 100000 LOOP
Select sales_amount into l_sale_amount from daily_sales;
l_sales_total := l_sales_total + l_sale_amount;
END LOOP;
declare
l_counter number;
i number;
begin
select count(*) into i from employee;
FOR l_counter IN 1 .. 10
LOOP
Statements;
END LOOP;
end;
##custom Exception
Declare
L_counter number := 0;
L_name employee.employee_name%type;
Cursor get_employee_name is
Select employee_name
From employee;
excep_old_friend Exception;
never_met_them Exception;
Begin
Open get_employee_name;
Fetch get_employee_name into l_name;
If l_name = ‘CARL DUDLEY’ then
Raise excep_old_friend;
Else –ELSIF … then …
Raise excep_never_met_them;
End if;
Close get_employee_name;
Exception
When excep_old_friend then
Dbms_output.put_line(‘I know this person’);
When excep_old_friend then
Dbms_output.put_line(‘I do not know this person’);
When others then
Dbms_output.put_line(‘Oracle Error: ‘ || sqlcode);
Dbms_output.put_line(‘Oracle Error message is: ‘ || sqlerrm);
End;
create or replace view emp_hq as select * from employee_master where loc_id = ’2′; #view. x$, v$, gv$(多了一列instance_id) #stored objects
Triggers cannot exist independently of an OracleDatabase 11g table. They are associated with one and only one table and, if a table is dropped, so is the trigger. #triggers
SQL> create or replace procedure printall #procedures
(first_me in varchar)
as
v_val varchar2(80);
cursor get_data is
select VAL from test where val like first_me || ‘%’;
begin
for i in get_data
LOOP
dbms_output.put_line(i.VAL);
end LOOP;
end;
/
SQL> execute printall (‘def’) #or just exec printall (‘def’)
SQL>describe printall
create or replace function GetProductTaxIn #functions, http://www.techonthenet.com/oracle/functions/
(in_product_id number)
return number
is
v_price number;
cursor get_price_tax is
select nvl(round(prod_list_price * 1.15,2),0) #nvl,replace null with the second parameter
from products
where prod_id = in_product_id;
begin
open get_price_tax;
fetch get_price_tax into v_price;
return v_price;
exception
when others then v_price := 0;
return v_price;
end;
select prod_id, prod_list_price, GetProductTaxIn(Prod_id) from products;
select prod_id, prod_list_price, GetProductTaxIn(Prod_id) from products where GetProductTaxIn(Prod_id) >= 500;
SQL>select owner, object_name, object_type from dba_objects where status ^= ‘VALID’; #packages, roll functions and procedures together
Oracle Functions
select supplier_id, NVL2(supplier_desc, supplier_name<if not null>, supplier_name2<if null>) from suppliers;
select NULLIF(12, 12) from dual; #null
select NULLIF(12, 22) from dual; #12
SQL> select coalesce(NULL,’not null’,NULL,’not’) from dual; #returns the first non-null
#Aggregation Functions
select … group by rollup(c.cust_gender, b.channel_class, to_char(a.time_id, ‘yyyy-mm’)); # generates subtotals for attributes specified in the group by clause, plus another row representing the grand total.
select …group by cube(c.cust_gender, b.channel_class, to_char(a.time_id, ‘yyyy-mm’)); #more detailed subtotals
select count(*), department_id from employees group by department_id order by department_id; #group functions -> avg, sum, min, max, count and distinct keyword
SQL> select count(distinct commission_pct) from employees;
SQL> select count(*), count(distinct nvl(department_id,0)), count(distinct nvl(job_id,0)) from employees; #107 employees,19 jobs, 12 departments(1 is null)
SQL> select sum(2) from employees; #107 rows will return 214
SQL> select sum(sysdate-hire_date)/365.25 “total years” from employees; #years of all employees in total
SQL>select avg(2) from employees; #2, adds the number 2 across 107 rows and divides the total by the number of rows
SQL> select avg(salary) from employees;
SQL> select avg(sum(commission_pct)) from employees where nvl(department_id,0) in (40,80,0) group by department_id; # AVG(SUM(COMMISSION_PCT)) = (0.15 + 7.65) /2 = 3.9 nested group functions can nest for at most 2
select max(salary), count(*) from employees group by department_id order by department_id; #ones with highest salary in each department
select to_char(end_date,’YYYY’) “year”, count(*) “Number of employees” from job_history group by to_char(end_date,’YYYY’) order by count(*) desc; #any item in the select list that is not a group function must be a grouping attribute of the GROUP BY clause
select department_id, job_id, sum(commission_pct) from employees where commission_pct is not null group by department_id, job_id; #group again by job_id in each department
select department_id, count(*) from job_history where department_id in (50,60,80,110) group by department_id having count(*)>1;
#Analysis Functions
select prod_id, sum(quantity_sold), rank () over (order by sum(quantity_sold) desc) as rank, dense_rank () over (order by sum(quantity_sold) desc) as dense_rank from sales where to_char(time_id, ‘yyyy-mm’) = ’2001-06′ group by prod_id; #if tie, rank will leave a gap(from 5 directly to 7), but dense_rank will be like 5 5 6
SQL>select TO_CHAR(123.02335,’FM9999999.0099′) FROM DUAL; #FM is to Removes leading and trailing spaces/zeros from number; this example allows 7 bits to the left of the decimal point, and for the right of the decimal point, two bits at least and four bits at most, also will do round for digits after the fifth bits.
SELECT TO_CHAR(1234567, ’999,999,999′) FROM dual; #1,234,567. you can use FM999,999,999 for striping zeros/spaces
SQL> select TO_NUMBER(‘$94 567,00′, ‘L999G999D00′, ‘NLS_CURRENCY =”$” NLS_NUMERIC_CHARACTERS=”, ”’) as result from dual; #pick up numbers from string
select to_char(sysdate,’Month’)||’ is a special time’ from dual; #September. mask is on above
select first_name from employees where to_char(hire_date,’FMDAY’)=’SATURDAY’; #fm, remove blanks.
SQL> select to_date(’25-DEC’, ‘DD-MON’) from dual; #25-DEC-13
SQL> select to_date(’25-DEC-10′, ‘fxDD-MON-YYYY’) from dual; #fx is for exact matching. as 10 is YY rather than YYYY, so this clause will report error. use 2010 instead will do.
SQL>select next_day(’03-NOV-1987′,’Wednesday’) from dual; #04-NOV-87
SQL> select to_char(00001,’0999999′)||’ is a special number’ from dual; #0000001
SQL> select to_char(00001,’999999′)||’ is a special number’ from dual; #1
