1、查看表空间的名称及大小+ F2 q9 V- Y" P' }0 M; J3 e
6 m# R- }: K' e! S- a. c
select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
/ E7 @: ^& a# c& D- {* lfrom dba_tablespaces t, dba_data_files d& C0 C- A7 X. C5 N6 Q2 C& b
where t.tablespace_name = d.tablespace_name N, o1 V, u D+ L0 ^* \- O
group by t.tablespace_name;
9 j5 q( v; p" v* d( P U
! h% z: i; b; H, f3 {$ B2、查看表空间物理文件的名称及大小- d2 x9 ^6 f6 I" X" D# E. x$ T
5 |2 z& D. p- A
select tablespace_name, file_id, file_name,* f; {+ Z& h5 i
round(bytes/(1024*1024),0) total_space
2 b. f+ w$ A; J, mfrom dba_data_files8 Y: [# q3 |) k. H9 E5 O7 w
order by tablespace_name;
, J; C& ^% s+ P/ [5 ] y . R0 E0 F* x3 e" K: N2 i# o X! q
3、查看回滚段名称及大小' f# x2 @6 ^9 ?0 g
5 G& H# p8 B! |# r
select segment_name, tablespace_name, r.status,
3 {- b; M9 |2 G& F# V(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,
0 q1 j, O* l1 Y4 ?* a Z4 `7 wmax_extents, v.curext CurExtent
) ^& v, Z# `% r" @From dba_rollback_segs r, v$rollstat v0 Y" ]' L+ [( m9 l2 i
Where r.segment_id = v.usn(+)" N! T H- L" O; i- Z
order by segment_name;
/ @8 i1 a7 r8 W$ O 5 p4 B5 T4 [9 S% P# P8 s
4、查看控制文件, K' _/ p6 k) u6 W) k% y; K
* E2 c% B, \- e$ j3 a6 p1 k+ g* Gselect name from v$controlfile;
( c# n2 u1 N+ E8 z ` 2 Q _) Q: \, B7 R& I6 h4 c5 F" L
5、查看日志文件0 S4 T$ t2 |, j4 }5 W+ g
0 z& q) B) R5 Q3 W7 P2 o( ?% Vselect member from v$logfile;3 h9 x% w h k2 O& y4 S7 M+ E
5 x& e4 F' z/ N1 k6 J- D# ~
6、查看表空间的使用情况# x2 y& V5 x7 o" F+ N, N
- m8 @2 @: ^* |2 O1 I5 y
select sum(bytes)/(1024*1024) as free_space,tablespace_name
% H! J: Y& D- q2 U% `5 I6 q' `from dba_free_space# K3 z8 e2 A4 w' W
group by tablespace_name;
/ Q3 X* s4 e0 s0 R/ E 2 o* ?0 p* ~# u) \
SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE," r( @) J! N7 |+ T7 L3 Y1 Z% d7 G
(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"# a% z9 h g( D* Q+ t, l
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C0 P0 f7 Y% B6 R3 V- m/ \
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND
( n' d- w# K* `5 JA.TABLESPACE_NAME=C.TABLESPACE_NAME; ! A; ^, T/ C/ N: |9 X F
# K, e& X! z9 ^4 `6 U$ J+ R7、查看数据库库对象
# o: O& C. R u+ R% k# [# S- w
8 I4 O# ^' y' _1 g2 y9 jselect owner, object_type, status, count(*) count# from all_objects group by
; `1 i' b! T ]8 z) {. Wowner, object_type, status;
' l; W) X+ \( A' N# S" _! m $ w0 h+ P( A Z: Y
8、查看数据库的版本
$ [( J) g% y* Y1 W# | $ j3 b4 w- l+ b! k* R& Z% e
Select version FROM Product_component_version " I- l3 ~* v3 X+ A" F* N% m& T. A: d
Where SUBSTR(PRODUCT,1,6)='Oracle';
8 _/ \) N; v3 g# G1 `( \ / ~ W+ ~) Q i# a7 `: I/ y
9、查看数据库的创建日期和归档方式) G/ g5 ?* [0 h& ~1 T. C
2 v7 M( o1 `' f
Select Created, Log_Mode, Log_Mode From V$Database;
! @8 Z% c' h2 q, O3 ]7 \
& x- r5 [; Q6 f0 Q# \; s10、捕捉运行很久的SQL" E6 b% ]: c5 W+ E U
7 c; a- ~2 N8 ^! N3 @column username format a12 , j1 Z* G1 c! U
column opname format a16 ' s( W2 h( A* u
column progress format a8
; [ D. N$ l; ~9 s- l3 K ( G6 ]$ S0 x% K
select username,sid,opname, + c+ E( X4 c+ j; j: l
round(sofar*100 / totalwork,0) || '%' as progress, . z3 P9 Q7 t; _) P2 G# r
time_remaining,sql_text / W1 u1 n! O& S2 T5 }. C \
from v$session_longops , v$sql
- Z% u: b7 n; ewhere time_remaining <> 0 4 n, }6 J' `# R* Q- o0 o
and sql_address = address ' P0 _0 J1 G! k, ~; Y. N
and sql_hash_value = hash_value 9 j- t2 I/ Z7 h' Z
/: a! z. ~1 A* [% v
11。查看数据表的参数信息
4 ]1 h5 `! s- N" z2 C: B# @ SSELECT partition_name, high_value, high_value_length, tablespace_name,
; b+ ?/ m! T, j4 m# A- V pct_free, pct_used, ini_trans, max_trans, initial_extent,
$ r4 N& f5 i+ C next_extent, min_extent, max_extent, pct_increase, FREELISTS,5 `" Y" A5 p5 C. y, N+ m i
freelist_groups, LOGGING, BUFFER_POOL, num_rows, blocks,
9 m. g/ B6 e$ C empty_blocks, avg_space, chain_cnt, avg_row_len, sample_size,
p: j$ T1 m2 ~3 P3 k last_analyzed
1 o r% {9 X/ C* W0 K FROM dba_tab_partitions3 @9 k( v1 e/ R, O$ O1 ]
--WHERE table_name = :tname AND table_owner = :towner6 q6 j0 E6 x& l0 I2 F
ORDER BY partition_position- a4 ]% E# y. o% p; [9 F
9 [+ |% g Z( H12.查看还没提交的事务
& {' [' \5 Q5 ]0 J$ g: ^select * from v$locked_object;
: l* @% ^) W4 Bselect * from v$transaction;6 h# `' E7 Z2 R1 l, V$ z2 z3 C l: g
- h' S# I* i/ _9 p1 z4 s13。查找object为哪些进程所用
5 P$ O1 K: x! Y8 Jselect , R, i6 H: H; c' l A7 X" ~/ ]
p.spid,
6 k1 U& I. O' {8 e1 ?s.sid,
0 Y4 H; ^& K+ As.serial# serial_num,$ S2 ~$ j% G" d8 D2 X6 n, |" M) a
s.username user_name,, Z8 X6 t& A8 Z; y
a.type object_type,
) F7 F& M" U/ u* x8 Z% Is.osuser os_user_name,
! ^0 b, f% ?; ^! d: ~, xa.owner,- c6 P+ J$ e/ ?9 d8 E( ]/ B
a.object object_name,
; R1 n1 e; M1 s D2 w4 Qdecode(sign(48 - command),) e0 S$ B6 t9 C: X0 U% }. g6 D
1,: M7 m, `" ^7 P% }9 r& |
to_char(command), 'Action Code #' || to_char(command) ) action,
2 B! L) F: H3 I6 ]8 g3 Gp.program oracle_process,) o3 Q% U2 I- D" D
s.terminal terminal,
) i, h$ z. M. b% Q" @1 X# Js.program program,9 i2 \8 V9 w3 e0 {
s.status session_status
5 P0 X p( N# L' d5 ^* b( }from v$session s, v$access a, v$process p - W4 g9 Z/ x3 `
where s.paddr = p.addr and
2 s/ l2 `; Z) N# c s.type = 'USER' and + d9 k# Z% o1 M* o
a.sid = s.sid and
3 `. j j7 e" s/ b a.object='SUBSCRIBER_ATTR'' k/ j+ a# v3 b7 w6 W' Y
order by s.username, s.osuser
* j( `& D; ?! Q! {8 [, M/ F! q , N% e: a2 i% y8 M( d8 a
14。回滚段查看4 E; ?% D- d$ h, x
select rownum, sys.dba_rollback_segs.segment_name Name, v$rollstat.extents * v$ @& H: K1 N9 S4 B% V! Y* w
Extents, v$rollstat.rssize Size_in_Bytes, v$rollstat.xacts XActs, 7 W7 l B e) e" _# k; N2 C
v$rollstat.gets Gets, v$rollstat.waits Waits, v$rollstat.writes Writes, - X, { p& F8 Z# L; Z
sys.dba_rollback_segs.status status from v$rollstat, sys.dba_rollback_segs, : ]$ R3 C) `7 U r d
v$rollname where v$rollname.name(+) = sys.dba_rollback_segs.segment_name and ' Q, ^: j) q4 j6 ~; l" E
v$rollstat.usn (+) = v$rollname.usn order by rownum
& l2 z( y; Y' ~# C1 C w
, r% F4 H5 a7 t& N" Y. q( H0 D& F15。耗资源的进程（top session）
. n; K- x, J9 U6 Gselect s.schemaname schema_name, decode(sign(48 - command), 1,
8 [* j# z) b( D* N2 Gto_char(command), 'Action Code #' || to_char(command) ) action, status
7 m. u6 F$ \9 Q+ q5 r1 `session_status, s.osuser os_user_name, s.sid, p.spid ,
1 M9 N3 I$ C1 Y* `1 ms.serial# serial_num,
9 z8 S& t( O4 ^8 |1 {+ o- Nnvl(s.username, '[Oracle process]') user_name, s.terminal terminal, . I9 n# W9 x# T
s.program program, st.value criteria_value from v$sesstat st, v$session s ' E N& S' m. Q- n- Q
, v$process p 9 K8 d# I; V6 o8 y" c ^0 J
where st.sid = s.sid and st.statistic# = to_number('38') and ('ALL' = 'ALL'
4 z: C7 I* R9 Y9 M( [$ Zor s.status = 'ALL') and p.addr = s.paddr order by st.value desc, p.spid asc,
; V) v7 _! J2 L! K. P% l6 Ms.username asc, s.osuser asc
+ I7 s0 u2 f' Q + r, [) r, G, }! t
16。查看锁（lock）情况9 r8 ]$ q9 C/ I* g1 j7 ~
select /*+ RULE */ ls.osuser os_user_name, ls.username user_name,
y3 f2 s8 _# n, C% bdecode(ls.type, 'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock', 'TX',
$ A* k# ^+ l- N" f9 G'Transaction enqueue lock', 'UL', 'User supplied lock') lock_type,
+ ]/ R' ^& t) A: g: o1 lo.object_name object, decode(ls.lmode, 1, null, 2, 'Row Share', 3,
/ o% p4 e% L( [3 m, w'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', null)
/ u: H9 t2 R# |' m, `3 \# C9 Ylock_mode, o.owner, ls.sid, ls.serial# serial_num, ls.id1, ls.id2
s9 ?& h2 m; v2 X& n/ g. V" afrom sys.dba_objects o, ( select s.osuser, s.username, l.type, * I1 h2 T7 D6 T' u
l.lmode, s.sid, s.serial#, l.id1, l.id2 from v$session s, 0 u. p0 _0 b8 B
v$lock l where s.sid = l.sid ) ls where o.object_id = ls.id1 and o.owner
7 R7 d" p0 X( a! a<> 'SYS' order by o.owner, o.object_name& k. [; M! {9 L0 V
( A; k7 E0 X7 O( ?9 j& a17。查看等待（wait）情况
: K- `) o) @+ O' OSELECT v$waitstat.class, v$waitstat.count count, SUM(v$sysstat.value) sum_value
4 e1 _: ?2 @2 RFROM v$waitstat, v$sysstat WHERE v$sysstat.name IN ('db block gets', * \. j C2 k; F3 X& I" I( |
'consistent gets') group by v$waitstat.class, v$waitstat.count
' W7 ]1 X! E# h( H
S+ T' {& t5 j- o# {18。查看sga情况" e1 X7 S" P* {" r: W
SELECT NAME, BYTES FROM SYS.V_$SGASTAT ORDER BY NAME ASC* V, \2 S: h' Y8 O! x
y- `0 \1 U3 [2 K) A8 c5 {, H+ g
19。查看catched object
$ [# C- ^. u. e$ g- h' _5 fSELECT owner, name, db_link, namespace,
& k7 ?) a6 |8 Q4 C type, sharable_mem, loads,
1 c6 r' @8 D T0 s* xexecutions, , a4 E& y* s; S* I, m
locks, pins, kept FROM / a; X9 f3 r/ `9 B1 K3 n8 v
v$db_object_cache$ Z5 J$ @. ]4 F- V8 x# G* t
# r4 C. a* U, P0 Q5 S9 @3 b
20。查看V$SQLAREA5 g/ @" s2 d+ N# R, o7 h
SELECT SQL_TEXT, SHARABLE_MEM, PERSISTENT_MEM, RUNTIME_MEM, SORTS, 2 n7 K0 ?% [" e$ P7 V2 d9 P. U
VERSION_COUNT, LOADED_VERSIONS, OPEN_VERSIONS, USERS_OPENING, EXECUTIONS, 5 D, R% b& G+ _/ \+ L I" N
USERS_EXECUTING, LOADS, FIRST_LOAD_TIME, INVALIDATIONS, PARSE_CALLS, DISK_READS,
3 v) X& K( ?$ _. T2 VBUFFER_GETS, ROWS_PROCESSED FROM V$SQLAREA1 Z/ \. b: ~0 x. s+ Z& o( f C
5 ?1 j' z9 S$ K2 M3 \1 w
21。查看object分类数量
8 l |* {3 k) T7 v9 y0 }select decode (o.type#,1,'INDEX' , 2,'TABLE' , 3 , 'CLUSTER' , 4, 'VIEW' , 5 , ; V" r- n( Z$ v2 C6 g
'SYNONYM' , 6 , 'SEQUENCE' , 'OTHER' ) object_type , count(*) quantity from
0 n: [9 R; s* k6 ]3 \sys.obj$ o where o.type# > 1 group by decode (o.type#,1,'INDEX' , 2,'TABLE' , 3 + @/ S2 X$ O, V( x' e. U+ j
, 'CLUSTER' , 4, 'VIEW' , 5 , 'SYNONYM' , 6 , 'SEQUENCE' , 'OTHER' ) union . k8 a. w0 h0 f$ U) N/ a- Y7 G
select
- D1 S7 r8 j6 W, w: E'COLUMN' , count(*) from sys.col$ union select 'DB LINK' , count(*) from 3 H: ^+ e0 w* p3 W' T* b; D
* e4 R m5 P, K) A
22。按用户查看object种类& N1 d+ x- n. U% ~( O
select u.name schema, sum(decode(o.type#, 1, 1, NULL)) indexes,
$ b1 N5 i8 _' g: C+ H8 Rsum(decode(o.type#, 2, 1, NULL)) tables, sum(decode(o.type#, 3, 1, NULL)) 4 \! A( D2 f$ P Q G$ }( F
clusters, sum(decode(o.type#, 4, 1, NULL)) views, sum(decode(o.type#, 5, 1,
( y! N& o" v" U) G+ v: y$ mNULL)) synonyms, sum(decode(o.type#, 6, 1, NULL)) sequences, ' m, J. Y1 J5 q5 |% r3 Y& u+ [& v
sum(decode(o.type#, 1, NULL, 2, NULL, 3, NULL, 4, NULL, 5, NULL, 6, NULL, 1)) % Z c% s- N/ G1 L0 W8 q- d$ j
others from sys.obj$ o, sys.user$ u where o.type# >= 1 and u.user# =
8 r; w8 c' Z6 A+ f- e% w8 W) ro.owner# and u.name <> 'PUBLIC' group by u.name order by
; F* g$ o3 k* {$ T0 R- jsys.link$ union select 'CONSTRAINT' , count(*) from sys.con$0 w7 F$ b; I% u C' I6 y9 ~
6 F' n! v6 R# v) X, c
23。有关connection的相关信息
% F; z7 B6 S$ ^% C1）查看有哪些用户连接1 B8 o2 V7 j- e9 ^2 M
select s.osuser os_user_name, decode(sign(48 - command), 1, to_char(command),8 S( e' w0 g( ^- Y2 g
'Action Code #' || to_char(command) ) action, p.program oracle_process,
9 z. G0 E7 ^5 Zstatus session_status, s.terminal terminal, s.program program,
% G* C- Q4 {! c9 |; |s.username user_name, s.fixed_table_sequence activity_meter, '' query,
& Q; v, d( l5 \' Q6 [0 memory, 0 max_memory, 0 cpu_usage, s.sid, s.serial# serial_num
) Q4 e$ i7 A) g Y# w' E. bfrom v$session s, v$process p where s.paddr=p.addr and s.type = 'USER'
/ d0 L: G u6 A# R; e# a* V5 i0 y# R jorder by s.username, s.osuser
, o# l7 \( z7 T! o. d2）根据v.sid查看对应连接的资源占用等情况
- {. ~" z$ t) e3 G& n! iselect n.name,
% {$ k9 ~4 q) P: \# ^, E v.value,
8 q# Z- L/ A5 R# b* _ n.class,+ ^8 }% }. |/ i& @+ x( ~
n.statistic#
, |" v/ z+ _- m8 `$ j+ c Vfrom v$statname n, . ~' d8 [: F: G, t. F7 i
v$sesstat v ; l. X7 F" T9 o
where v.sid = 71 and
; B/ {* Y {. ?+ F v.statistic# = n.statistic#
% A0 ?. W! }$ F1 F/ |order by n.class, n.statistic#
$ [% Q' c9 v' U- ~3）根据sid查看对应连接正在运行的sql; J3 f' y( `+ V! ?
select /*+ PUSH_SUBQ */
* |6 Y* t2 W8 d' b' g9 F6 ^( u6 ? command_type, 4 J, H, T/ l2 G: ^
sql_text, 8 D2 z- ?# j9 [3 {- a. {
sharable_mem,
: H% q7 c6 V! Q( p5 e6 F persistent_mem,
6 \ f$ M, m6 ?5 v+ p runtime_mem, , _9 o& z; R0 A1 t+ Q
sorts,
& ~" j2 ~& h4 f w$ ? version_count, 2 l* Z/ E$ K- L
loaded_versions, ) Y# Z5 p: O1 P0 u4 `
open_versions, Q9 e ^+ O! ^' ?8 F5 T2 D# _
users_opening,
: ], V, a U& ^- N0 Z( ` o" T5 G executions,
4 A2 {3 }* D) K5 w6 L2 A; Y users_executing,
' l$ ?* a/ o$ _) f$ _0 g* X' ^ loads,
, U" R- i3 o$ u5 n+ t. w5 G first_load_time, 6 ]. J8 o7 Y( W8 ]: Z" N- }) u
invalidations, " _0 g% ?' B3 N( u/ [- s: n
parse_calls, 3 t& k- A) H$ U' x- p
disk_reads,
, R" r5 m% [' K- w$ \ buffer_gets,
/ H* F1 q, Z1 s8 C7 z rows_processed,
5 Y* Z% z# Q% F2 U( f sysdate start_time,+ C% [$ W& b+ w
sysdate finish_time,
* r, _; v( V* r$ X/ C '>' || address sql_address,% \! g- F8 i! K+ \* a$ n9 S
'N' status $ f3 g6 M* a9 X T- u0 P, x$ b
from v$sqlarea& h# h4 y. i, m
where address = (select sql_address from v$session where sid = 71)
) v; Q2 y5 Y/ A9 a
9 r3 f, `4 ?3 n: L& E8 ?* j& f24．查询表空间使用情况3 c6 @: e( X: k- R K
select a.tablespace_name "表空间名称",/ T/ i; T( p9 X( A1 F) f1 O
100-round((nvl(b.bytes_free,0)/a.bytes_alloc)*100,2) "占用率(%)",
; ?. e: Q& q: wround(a.bytes_alloc/1024/1024,2) "容量(M)",4 _" L8 U N4 s' z
round(nvl(b.bytes_free,0)/1024/1024,2) "空闲(M)",8 z+ X; z5 b) G6 x, w
round((a.bytes_alloc-nvl(b.bytes_free,0))/1024/1024,2) "使用(M)",3 a; R" p w& `: p" v
Largest "最大扩展段(M)",
- y) V+ L- z% Oto_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "采样时间" 8 e2 i- r& e9 ~3 [1 G
from (select f.tablespace_name,9 _3 U% ^+ s3 \9 I$ h
sum(f.bytes) bytes_alloc,
& U- ~) E3 F" B. Y. n( D- x sum(decode(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes)) maxbytes 8 s. q- _# g+ `# B
from dba_data_files f 8 W( y7 a! y0 o/ M7 m
group by tablespace_name) a,
: S# v' N# j8 J! G, y: u(select f.tablespace_name,
; ~3 s9 y4 i: X+ r sum(f.bytes) bytes_free
2 u0 C' ~+ ~9 e' F8 G8 `) Pfrom dba_free_space f ' s" y/ x7 g$ w# h0 C
group by tablespace_name) b,
3 i1 @" v9 O+ O* c" I1 |: l(select round(max(ff.length)*16/1024,2) Largest,& y& i; Y& ~) f
ts.name tablespace_name
" o$ ` R0 z9 I9 @from sys.fet$ ff, sys.file$ tf,sys.ts$ ts
) V6 ^' h; f9 r& Z/ M3 v! p" _where ts.ts#=ff.ts# and ff.file#=tf.relfile# and ts.ts#=tf.ts# ' z- o k' E( i! R
group by ts.name, tf.blocks) c L. y% P1 C) e
where a.tablespace_name = b.tablespace_name and a.tablespace_name =
f# l5 R" z5 X m+ b# Gc.tablespace_name* [; `& I9 |. t! A: x
1 F1 |5 ^- p2 u, e5 k25. 查询表空间的碎片程度 8 V2 E; t; L8 [" t9 P, P2 P9 Z
1 F& f7 `; E% W2 x) Eselect tablespace_name,count(tablespace_name) from dba_free_space group by , N3 e* ?! I. n- u C5 E! `
tablespace_name - o p4 a. ^2 D3 j- n
having count(tablespace_name)>10;
, m0 S' f* L5 h1 ^
1 p7 D! H! @' t; Xalter tablespace name coalesce; 3 d! l& r2 _3 [$ U8 m L; W
alter table name deallocate unused; 3 M# _) k2 f, }# M# r& B2 H9 t
* W' X+ l, O! r& t2 ]( }create or replace view ts_blocks_v as
( P4 [' [! |0 F; {# Cselect tablespace_name,block_id,bytes,blocks,'free space' segment_name from
! b% {8 x- ?* B, U$ {/ o4 jdba_free_space
) `# d8 u+ Q& V7 x& Munion all # U: ]: I) d3 @
select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;
# F- g5 z/ Z5 n* `7 k
( J7 T/ U/ N$ E& cselect * from ts_blocks_v; 6 F. B, Q+ {3 _& s
9 m) i @: I5 e/ Dselect tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space ) Y$ t0 k. {, z& f7 x, ?9 E, f
0 x& V: U. t- m# H3 H! ^( Z" L
group by tablespace_name;9 `5 \8 r2 z0 W4 @
5 c! M# _, o6 v" E* `' N
26。查询有哪些数据库实例在运行
1 ~" ~8 G& l$ j! K5 j0 ^& c5 Eselect inst_name from v$active_instances;
9 c+ z! K' s. ?# a' @& m- t$ O / r$ P G; s: t* F4 j
===========================================================8 U! ^( m" F& J; q& I1 F; f3 ?. r
######### 创建数据库----look $ORACLE_HOME/rdbms/admin/buildall.sql #############
Z; ]. O+ ^1 [+ V" x4 X , ?' \: G% H P: f w. y& n
create database db01
1 p& o- w7 E% @$ b) kmaxlogfiles 10
; `. N# ]8 @' Y, V+ ?( X# Amaxdatafiles 1024
7 n$ Y% _& C7 f& r& Jmaxinstances 2 ) Z4 {; g# @7 ^$ H
logfile 5 @3 v* @- {; c4 R; J3 _3 ~
GROUP 1 ('/u01/oradata/db01/log_01_db01.rdo') SIZE 15M,
: E9 x( U! @9 S) i/ v1 eGROUP 2 ('/u01/oradata/db01/log_02_db01.rdo') SIZE 15M,
. ^; c0 e" s1 D& M3 ]GROUP 3 ('/u01/oradata/db01/log_03_db01.rdo') SIZE 15M,
4 O& p/ h% `& g# u# s3 q0 Vdatafile 'u01/oradata/db01/system_01_db01.dbf') SIZE 100M,
3 L0 }' _; p3 f/ B; k- w2 lundo tablespace UNDO
$ _1 Q; Q! T/ H. Y' i2 P2 s8 Q4 }datafile '/u01/oradata/db01/undo_01_db01.dbf' SIZE 40M 2 h$ k# ^; C9 {! z9 x2 F! p
default temporary tablespace TEMP
2 p6 v& p, X/ z7 u# {6 Rtempfile '/u01/oradata/db01/temp_01_db01.dbf' SIZE 20M " K8 e4 D5 d" e7 _4 p! P4 H
extent management local uniform size 128k ; f X" \, t7 A6 r( K, N+ I3 n& U
character set AL32UTE8
: W5 u* z, i1 v, Knational character set AL16UTF16
9 `1 w, f4 f! c d9 w4 H0 E$ T* L% uset time_zone='America/New_York';
3 W% A; p5 z5 ?0 x m; Q
2 _* }) S! t; v7 o- S% g############### 数据字典 ########## 7 _, m" T! e+ X! {
8 @# F# O) U; }% V1 x) rset wrap off
* ]8 I- P" `! k; x6 x ' o1 J7 ^7 M& x1 E# n$ z" W: K/ _
select * from v$dba_users; % g+ `1 R2 j7 z% |7 z) O
- f: q9 `- v7 R& n* `
grant select on table_name to user/rule;
$ `4 _6 I {6 `' Q& \0 v
8 _% z! p0 W" ^6 @$ q2 w2 t) Qselect * from user_tables;
3 B! L5 w1 h, K1 c! a2 k
: M0 Q6 j* h# L2 u, o# e; fselect * from all_tables;
6 q( k( S f4 Z+ ]$ [( k8 D $ B% R5 t4 x; [1 R7 g3 d
select * from dba_tables; 1 |6 ~0 d/ l4 W+ |+ y
6 K- v3 f* Q% F/ y& [; Y0 L6 T e% @revoke dba from user_name; ( l6 M; W: i7 {4 ^
) R1 ~5 q7 t( bshutdown immediate 1 V9 H, ` u# N. ^( x) |9 }+ {% k
& r! B4 k. K7 {# P+ ]0 C
startup nomount
% a7 ` L. Z1 \1 I; e4 D! y 3 _; q" E- d, {" S
select * from v$instance; & j- l/ w$ o. G
1 Y0 e$ u7 J" c. p `. q
select * from v$sga; 3 d! u+ }9 t$ z0 l
3 d6 C) D! B, c% pselect * from v$tablespace;
2 M: Z( H) o; \* A3 h& T ! t* _' f. c0 o2 d! a6 |
alter session set nls_language=american;
6 G$ Y6 P8 t# G2 g. C) ^" Q * I% F, N. l( E& m
alter database mount; " r0 m. o- a0 e J2 l# r- x; t9 S
2 @* Z4 m. [0 v3 B9 p
select * from v$database; ( \" x1 Q$ N+ |0 m* s, t9 z
- C) Q& O! P( v# Q1 ?. |" ^alter database open; . b3 ?; o: h2 g9 a
7 F. D! k: Y$ v0 k' a0 y! z' X9 Kdesc dictionary
2 n# D2 F7 x; B, v 8 r# f$ ?' ?/ Q% z3 `) l F
select * from dict; 0 U8 E3 m% _# e& U
: P1 b" J3 V, y9 _: ~2 r ?# b
desc v$fixed_table;
! s8 |4 t+ h# Y& D - S# R& N6 U# x, c4 v& p& v3 Y
select * from v$fixed_table; 5 `. ~; I' [: |& J. K
6 r" _/ R% D! B* L
set oracle_sid=foxconn
/ h! q5 s* K+ w/ V1 W, y
! E' ?2 T1 O7 Rselect * from dba_objects; 7 E( J4 J* }7 k! M9 H
# B5 H k9 h, N& R
set serveroutput on
8 j: l9 R7 A( q ?+ P 0 T3 ~8 D) c `- ?. h( z& ]0 g
execute dbms_output.put_line('sfasd');
, ~0 ]$ ~; t# z. ]
: ~+ @7 h. z! G* Q! D% i$ ?( a5 \############# 控制文件 ########### ( g/ C1 u2 F' L$ L
) ~2 ~% e m6 U" jselect * from v$database; 0 ?1 }+ E% l: B9 e4 j r( ?
. q- p) G2 S) o) }8 y! \$ H3 c
select * from v$tablespace;
0 ]1 R% S I! r$ x# B y8 E0 K6 O( [ : l5 p7 _- p/ |3 r
select * from v$logfile;
- @- n2 T" D1 }% P, b1 C+ Q * w/ \% P3 ? [9 Q \, x2 Z% U' X
select * from v$log;
9 f5 E7 y$ l: H9 f$ g4 T
0 g7 { {. R* ?2 J' Y) t1 Bselect * from v$backup;
( c4 h W% M9 W2 D4 b ^
3 }% _6 P! i8 N% Y/*备份用户表空间*/
( D" f; @! F& \/ `! [- @alter tablespace users begin backup;
% P, V: h5 t& ^: N3 ^( B' \
6 h s1 L& z* N" m5 Pselect * from v$archived_log;
R, C% p N( O" l0 N
' w: @8 e2 d2 ?! Rselect * from v$controlfile;
1 x9 X# D0 Z0 x4 i; L d6 s% W
6 u( O# ~6 k7 [alter system set control_files='$ORACLE_HOME/oradata/u01/ctrl01.ctl',
7 o! f$ N! q+ F& j'$ORACLE_HOME/oradata/u01/ctrl02.ctl' scope=spfile; ; K" S1 I" s* y! q1 c# L9 ]
/ J1 i) h' Z: u
cp $ORACLE_HOME/oradata/u01/ctrl01.ctl $ORACLE_HOME/oradata/u01/ctrl02.ctl
/ q+ E; B, C L! @( [- c. H' b% R' u
/ c9 C, e( \; Astartup pfile='../initSID.ora'
6 l/ i6 \: `- j v' f- F+ K- ` 6 S- y8 \# N, n; Z
select * from v$parameter where name like 'control%' ;
1 o4 t( }- \2 M7 y; I" k
j m2 [: C9 [5 L* zshow parameter control; 3 N+ J, ^5 _1 S
1 x) S5 { n8 h+ r2 Q9 O3 ?select * from v$controlfile_record_section;
. \9 [ ?( b& B
, k% A7 W+ {/ v, q! Fselect * from v$tempfile; # }3 m$ w9 w5 j# i
* l2 S3 o+ J" B1 `& a: V. B/*备份控制文件*/ h+ w1 D, N& I, g# B/ A3 G
alter database backup controlfile to '../filepath/control.bak';
2 Y1 L* J# f9 u, C- S7 N4 I 2 e* B( z0 E8 v0 _+ ~
/*备份控制文件，并将二进制控制文件变为了asc 的文本文件*/ 8 g7 z5 O! A0 a1 ?& d k8 K
alter database backup controlfile to trace; " Q5 o( C3 K9 ~' F+ v( s$ J. @
) x4 ^$ w, T8 P; h############### redo log ##############
/ {7 P, @& e" G, Z* d7 C 7 Q( |+ d6 [3 G$ O
archive log list; % b6 O1 ~8 @; C- ~6 Y7 n. k4 l
7 d2 L. D# H6 K/ K9 Aalter system archive log start;--启动自动存档
# s1 G o6 o: y9 M7 E
+ X6 C. o+ A1 n, K+ O1 `alter system switch logfile;--强行进行一次日志switch
1 u w7 _: ^% m8 Q" G' P * F# x( s8 \5 X( |. F
alter system checkpoint;--强制进行一次checkpoint ( T5 @" j R8 q1 ~% L" V5 g- _, D9 f: H
- ?" t! \" _3 |, A# @
alter tablspace users begin backup;
5 B p- } [6 i3 |8 H* b0 b" M4 S " A$ q0 y$ B8 e, L7 M
alter tablespace offline;
# } Z/ W3 W* v! t7 I5 [+ ~7 p
0 F4 @) Y% U3 {+ P6 A* E6 l' r3 E/*checkpoint 同步频率参数FAST_START_MTTR_TARGET,同步频率越高，系统恢复所需时间越短*/ / L3 H! V$ [3 U4 L, Z# K+ v; f
show parameter fast; / z$ I4 k" c9 e( o3 l
; c' l% }- h0 f
show parameter log_checkpoint; ) e+ ~3 W: Y' L, v
! c0 f% t/ t8 S; A U8 u/*加入一个日志组*/
8 J( G( T* w5 K1 J& X$ q- Ealter database add logfile group 3 ('/$ORACLE_HOME/oracle/ora_log_file6.rdo'
, b7 A8 v( t+ k( {size 10M); $ D4 x0 L+ y a2 K
# S4 ~, w w I7 q+ ?( q
/*加入日志组的一个成员*/
$ t5 _+ ~7 ~9 @6 z5 L# v# Ealter database add logfile member '/$ORACLE_HOME/oracle/ora_log_file6.rdo' to 7 `8 v4 p0 m; w( M* k' \
group 3;
+ t( ^" Z; o/ s+ c% z9 g H
: u) K5 W' W" f$ _0 q0 |7 E/*删除日志组:当前日志组不能删；活动的日志组不能删；非归档的日志组不能删*/
) n4 Z% e! }) R: ^alter database drop logfile group 3; 6 t; O4 C' ]: @
7 z6 |* m2 @8 s5 k8 ~/*删除日志组中的某个成员，但每个组的最后一个成员不能被删除*/
" ^' y* X' k+ U7 J! Ialter databse drop logfile member '$ORACLE_HOME/oracle/ora_log_file6.rdo';
' |/ A: D/ g9 G/ x/ j
% u1 o: O6 q% Z/*清除在线日志*/
6 R3 F; v: s7 ?% \alter database clear logfile '$ORACLE_HOME/oracle/ora_log_file6.rdo';
) k ?/ D. Q6 |" o% } 9 U- z! L! M% ]/ z, ^8 c% Q) B* B
alter database clear logfile group 3; : |8 N O# y" u/ i4 k6 u$ X3 g; c
$ w& T$ w$ E; E1 _, S7 j+ N1 q1 T
/*清除非归档日志*/
8 G7 I* e x! a) a' z9 Q' `alter database clear unarchived logfile group 3;
: {! E4 q4 U' i G* D. S& G
/ ^, q1 s1 R% _' {2 y/*重命名日志文件*/ `" Y R n- v7 o8 O
alter database rename file '$ORACLE_HOME/oracle/ora_log_file6.rdo' to
* A b% q( D9 a% O% ~+ f4 Y'$ORACLE_HOME/oracle/ora_log_file6a.rdo';
. q, o- s2 b6 O9 T6 ]! U; j3 \0 c
" s% f4 B% g# H% S4 mshow parameter db_create;
/ u2 t0 \1 ^ b, m ; ~* c' {" `6 ?+ a& r- x: q
alter system set db_create_online_log_dest_1='path_name';
* ~1 q; q' W* I H6 a& B6 _
o: F/ Q# L9 m) sselect * from v$log;
2 ]/ \& b4 L" H* _( p* n8 r, B
# w7 l( t# G! A' bselect * from v$logfile; & g1 F: g F# \0 {! d" l; m1 q- Z
# g3 g$ O) z* ~# y6 g6 M/*数据库归档模式到非归档模式的互换,要启动到mount状态下才能改变;startup mount;然后再打开数据库.*/
4 q f4 F$ E/ S" malter database noarchivelog/archivelog; 4 _# W2 O. }3 b4 }
- I1 w& J( R. q- }6 K8 Eachive log start;---启动自动归档
$ A$ ^9 g: K y& t4 U
' W9 h/ v/ w1 }' Y1 |7 f9 F1 }alter system archive all;－－手工归档所有日志文件 / ^. R; m- u5 W* b# P
) k( d: U4 m. N% d; bselect * from v$archived_log;
3 p% I8 L) n4 }9 r
+ }2 v+ J. `; A3 Cshow parameter log_archive; . [2 i# \0 W7 A. k
8 l! ?' z1 _0 Z0 v, N. j
###### 分析日志文件logmnr ############## 3 H5 p$ ~5 L" h8 p. }* O* H& U
+ Z" q& ^1 A& O( S1) 在init.ora中set utl_file_dir 参数
5 P6 e& m) R! p9 e, d2) 重新启动oracle 1 O2 [2 Z9 P: p, {+ a, x
3) create 目录文件
3 d3 Y' ^: m- A8 [! k+ h$ e( Gdesc dbms_logmnr_d; + i- E3 u( L" {" N. |; s
dbms_logmnr_d.build;
- a; e. ?9 G& Z- _4 J6 D5 U/ b4) 加入日志文件 add/remove log file % L' p& @. o4 u0 }6 {
dhms_logmnr.add_logfile 4 ]& J5 l) v1 Z" Y
dbms_logmnr.removefile ! \1 |3 ~$ `! r- O' O
5) start logmnr " q6 O$ ?3 J+ U( S3 B( a/ ]
dbms_logmnr.start_logmnr
" `5 f3 I5 H, c6) 分析出来的内容查询 v$logmnr_content --sqlredo/sqlundo , H' \3 K5 b. @3 U$ ?, a
3 M: u u# X) f! j0 f实践：
) w( w, O/ m9 ^' g# u5 [7 y4 H
5 A4 l2 l/ ?5 D: W! h) T9 Gdesc dbms_logmnr_d; 0 O$ f8 W- b1 Y2 ~
g8 d: ^$ l4 n4 s/*对数据表做一些操作，为恢复操作做准备*/ * t1 j% ~. \1 t7 X0 F; O+ t
update 表 set qty=10 where stor_id=6380; + a L3 p: Q, }' B
+ d& J3 O& l0 ydelete 表 where stor_id=7066;
' `, F: P; M6 W8 |% @/***********************************/
4 q7 o+ g; q8 Z7 T9 M* E3 e- Qutl_file_dir的路径
/ A+ G1 _7 S3 Iexecute
+ z0 Q, u) U+ @$ _3 Y; N' Sdbms_logmnr_d.build('foxdict.ora','$ORACLE_HOME/oracle/admin/fox/cdump');
1 T# L) ?" f$ U! }$ f8 L
( y1 @& n7 a* `. \) k: d$ mexecute
2 y! X1 Y8 O$ _7 e9 a( ^- Q1 Kdbms_logmnr.add_logfile('$ORACLE_HOME/oracle/ora_log_file6.log',dbms_logmnr.newfile);
- p, v" X9 C J; }9 L- S. u% @( X6 k) c. g5 f( h
4 f8 G$ A! _/ \& b5 L2 `3 f
execute , I4 V2 ^6 o7 k0 r: ^
dbms_logmnr.start_logmnr(dictfilename=>'$ORACLE_HOME/oracle/admin/fox/cdump/foxdict.ora'); , \1 }, g5 M- E# I- X$ R
( @: |6 g. n$ o9 F& Y5 _
g% T, l; S4 o; V( O+ d
######### tablespace ############## ; |4 t( F) n' B7 g
9 M/ f; ]2 Y' ]# G' k
select * form v$tablespace;
4 t% L7 B, z/ ?! N. ^( A( x ( |" T6 t3 M' O
select * from v$datafile;
( z& m7 Y* k5 j d& f
I- I" e/ e/ e) e' T5 y/*表空间和数据文件的对应关系*/ . c1 T' P8 B9 ]3 F6 s: s! w" a; c
select t1.name,t2.name from v$tablespace t1,v$datafile t2 where t1.ts#=t2.ts#;
' P+ A `* b) s8 X; K * |( L5 H- q0 k
alter tablespace users add datafile 'path' size 10M; 6 V6 P. F* ?0 a* k
9 O- W9 a7 z4 H8 k8 K2 g1 E5 O
select * from dba_rollback_segs; / q6 D6 @- V7 F
8 P; X! `4 f7 V x& S& Z# S( P
/*限制用户在某表空间的使用限额*/
8 P: A! b9 i$ a5 |alter user user_name quota 10m on tablespace_name; ; n G( B2 B# O: y) \ [2 {
6 y# ^* ~: B4 y2 Z# v
create tablespace xxx [datafile 'path_name/datafile_name'] [size xxx] [extent $ y- D+ r7 j, S& O# @) f5 d
management local/dictionary] [default storage(xxx)]; - Q9 T! V/ {% Z6 D: R/ }
( T" `. H* i- v! Mexmple: create tablespace userdata datafile
( O4 ^- ?% ]% w' @, k) P'$ORACLE_HOME/oradata/userdata01.dbf' size 100M AUTOEXTEND ON NEXT 5M MAXSIZE " l9 E, z6 j$ W* o( y# ~6 [
200M; & Q& z9 v0 \+ x+ j7 `
create tablespace userdata datafile '$ORACLE_HOME/oradata/userdata01.dbf' size 6 M4 v$ H& B% T, m" L5 m
100M extent management dictionary default storage(initial 100k next 100k ( h- ~2 }, s# t: T4 K/ Z
pctincrease 10) offline; : ]: E5 m/ M8 c% q
/*9i以后，oracle建议使用local管理，而不使用dictionary管理，因为local采用bitmap管理表空间
* E4 j. ~8 p# X* I5 G* Q，不会产生系统表空间的自愿争用;*/
0 N- d. K; b( W9 |! f6 x6 m- k& Pcreate tablespace userdata datafile '$ORACLE_HOME/oradata/userdata01.dbf' size ; `4 u( t% z7 k$ S5 `8 @
100M extent management local uniform size 1m;
+ p# B3 R! D0 T5 ycreate tablespace userdata datafile '$ORACLE_HOME/oradata/userdata01.dbf' size
* v% Q7 T) U; B) v100M extent management local autoallocate; " A5 H$ l0 i9 m. \8 ]
/*在创建表空间时，设置表空间内的段空间管理模式，这里用的是自动管理*/
: M$ l2 N% h& z9 V7 B& l1 jcreate tablespace userdata datafile '$ORACLE_HOME/oradata/userdata01.dbf' size
4 J4 ]) \7 ]5 A8 t! x5 U100M extent management local uniform size 1m segment space management auto;
4 x1 q; f: O+ h( X3 d, T
9 A; J- o8 t* \alter tablespace userdata mininum extent 10;
9 G$ B2 G: g0 @; v, N ( w+ e: q6 O) R$ w) u v+ g
alter tablespace userdata default storage(initial 1m next 1m pctincrease 20);
1 L! ~) W n- v# l
f2 d! v5 ?0 T/*undo tablespace(不能被用在字典管理模下) */ ! B! t( A' Q# Q! a
create undo tablespace undo1 datafile '$ORACLE_HOME/oradata/undo101.dbf' size
8 ?& F- x a+ q/ E$ @+ v5 Y40M extent management local; 0 `' u5 K5 ~. ^# k% p6 F
& Z1 n( U; A5 G" F5 C1 O' cshow parameter undo; " W8 ^0 v/ z" R
6 U G& T1 h8 O7 K/*temporary tablespace*/ 0 c) m4 e6 R3 {
create temporary tablespace userdata tempfile '$ORACLE_HOME/oradata/undo101.dbf' ! y: Q9 K2 D9 i5 P* r
size 10m extent management local; 2 C. O* f- C/ b8 M( u! _
1 [, J9 l& @. p- Y2 F$ y! Z/*设置数据库缺省的临时表空间*/ + ?. l5 g+ x% g$ K
alter database default temporary tablespace tablespace_name; * G# _/ E5 e+ a4 J7 K& `
8 I% H$ f) B ]+ b( h) Q/*系统/临时/在线的undo表空间不能被offline*/
( r7 [2 K" w, o3 f8 calter tablespace tablespace_name offline/online; 7 _& q5 G" I* ?; l* c9 @( I
8 @5 b3 }5 Y& Q
alter tablespace tablespace_name read only; 5 h! d7 T; d7 Q& j1 @* z
# ~' P6 l3 s" M8 n
/*重命名用户表空间*/ & {! h# B' ]' k: W- f
alter tablespace tablespace_name rename datafile
& n9 U$ N( J: W6 D'$ORACLE_HOME/oradata/undo101.dbf' to '$ORACLE_HOME/oradata/undo102.dbf'; : r; L, P" ^ I. W' B& `: F+ e
# L& V8 R, q( B0 }/*重命名系统表空间 ,但在重命名前必须将数据库shutdown,并重启到mount状态*/ / F" ~* \, _3 F( F
alter database rename file '$ORACLE_HOME/oradata/system01.dbf' to
: X) L4 ^' _. [1 X% t2 j1 R'$ORACLE_HOME/oradata/system02.dbf'; / e8 k3 L; w+ y: U. c, M
4 e, r) F$ A7 c' b3 @7 [& T$ _3 ]
drop tablespace userdata including contents and datafiles;---drop tablespce " E/ V; L9 s6 V5 z) v* y: s r# d8 N
& o4 M y1 R. u/ r7 V+ d& o/*resize tablespace,autoextend datafile space*/
* c% ^! u) M M' Lalter database datafile '$ORACLE_HOME/oradata/undo102.dbf' autoextend on next / W f6 d. h2 H9 m# [4 J$ b1 h
10m maxsize 500M; # U# E8 B5 l1 i0 K# B. I, ]
: l) m0 |. V4 W! N# x) k/ K- `/*resize datafile*/ ( q3 |, L/ Y- `/ {) q2 M
alter database datafile '$ORACLE_HOME/oradata/undo102.dbf' resize 50m; 6 r2 ?; \) L: X% ?8 Z% I8 D
- F$ v, G* s: Q$ _/ x/*给表空间扩展空间*/ 4 o- O0 A& U- S" h
alter tablespace userdata add datafile '$ORACLE_HOME/oradata/undo102.dbf' size $ W/ W5 A- b, M" M4 e
10m; - c% J( D7 I- g* i( y
/ M" s1 y6 K0 ^7 ?$ g2 G1 m3 N/*将表空间设置成OMF状态*/
9 L% \' P) [( f6 L& falter system set db_create_file_dest='$ORACLE_HOME/oradata';
9 H7 L# o! I s A % l8 l$ C$ C. a* M
create tablespace userdata;---use OMF status to create tablespace; / h( v! r3 A$ ^ y. q0 _! N& ]" t
- @& R" E9 U& J& R8 @0 g) B, V
drop tablespace userdata;---user OMF status to drop tablespace; : d0 v1 L. M$ D: G% c
' p. v0 G9 j/ A4 ?
select * from dba_tablespace/v$tablespace/dba_data_files; ' z1 ^6 u' ]# ?1 v% f& v: f
( m. S3 X" Q e. U+ n0 R6 G
/*将表的某分区移动到另一个表空间*/ * W G8 ?( A. P
alter table table_name move partition partition_name tablespace tablespace_name; 2 c" {3 p% {3 F" U
: t7 o& I( V/ |
a* m% C: {8 E. m###### ORACLE storage structure and relationships ######### ) X7 a1 c* n" v% ?. b# R
$ ~1 Z# n3 c" D5 I0 b9 Z. O1 K8 C% d
/*手工分配表空间段的分区(extend)大小*/ 1 \" R6 c1 ^8 G; Y4 ]
alter table kong.test12 allocate extent(size 1m datafile
1 B+ F6 f1 z/ W# h'$ORACLE_HOME/oradata/undo102.dbf');
. `% G0 L+ u" C& E7 j* ^ ' X* p k" X4 ]1 L/ ^* o* j1 c
alter table kong.test12 deallocate unused; ---释放表中没有用到的分区 - M' _: E, ^9 j! h& ~
* n( X& e Y: y) u$ C; _show parameter db;
; W7 _6 v+ F; d' L% M) U # T4 [% I+ _' V0 Z
alter system set db_8k_cache_size=10m; ---配置8k块的内存空间块参数
& F$ M4 Z. y& H: ~5 p3 c/ r " C9 m( e. Y" @. F* f
select * from dba_extents/dba_segments/data_tablespace; 2 u: k# x1 X" o2 L2 i# F$ q( `
/ n" A! _! o0 o% z6 {. W- \" `2 t* iselect * from dba_free_space/dba_data_file/data_tablespace;
: ^6 K# S$ m b" Z! M ; @/ \. o- I, b1 @' H( ^
/*数据对象所占用的字节数*/
' E# P. t( U- q. gselect sum(bytes) from dba_extents where onwer='kong' and segment_name ) Z, F# ^3 z' ?/ \) F* F
='table_name';
& c- Y5 m" N& x9 @. S( @3 p 3 Z6 C2 r4 G1 f* W$ c9 {
############ UNDO Data ################
/ F; |6 {1 C8 n- z* A* D+ v0 x : l; [& l. x* a
show parameter undo; 5 [* M! T+ \ F5 P# m
0 k+ `2 B5 V, e0 L. X4 z t5 V
alter tablespace users offline normal; ) g6 Q: I5 ^! v* @3 n& W. ~3 e# x
! e" C0 `6 v/ O4 P
alter tablespace users offline immediate;
+ V2 `2 B% R8 @+ a4 r
5 |( n1 P% s. d5 a; ~4 crecover datafile '$ORACLE_HOME/oradata/undo102.dbf';
" G m/ c; ]" S2 ~7 ^+ z
, r& I5 i1 |) P1 t! C' Salter tablespace users online ;
) m' f8 }9 E' Y( G8 ]2 c
! s L+ |/ m) S6 J% Y6 D- L0 ]" uselect * from dba_rollback_segs; " `; _8 ] g$ i) [4 R; J. ?( ?
\- k1 ~$ E' Q/ D
alter system set undo_tablespace=undotbs1;
- a$ N: ~+ E2 E2 E# H0 N: O ! B" g. P: Z. j, V/ e
/*忽略回滚段的错误提示*/
0 B: a4 @, ]5 G& Lalter system set undo_suppress_errors=true; ) E0 L% R% t" G
: Y9 z% r& v- n+ }$ V/*在自动管理模式下,不会真正建立rbs1;在手工管理模式则可以建立,且是私有回滚段*/
8 t$ s" p9 B6 Z( ]5 Rcreate rollback segment rbs1 tablespace undotbs; " K5 s6 x6 w6 B, q) f
- R l: f5 k+ Cdesc dbms_flashback; 4 z* g; Z) R, A8 ]$ U: C ~% [( m- r* Y
9 y% P4 Y" k6 v! \0 N: b
/*在提交了修改的数据后,9i提供了旧数据的回闪操作,将修改前的数据只读给用户看,但这部分数据不会又恢复在表中,而是旧数据的一个映射*" R1 W- g7 c5 [- B5 e! \+ b
( ?& t; d! r6 v& B0 a! p8 ]
/
4 N( i5 K( \3 w' C) X; H$ y, ~execute dbms_flashback.enable_at_time('26-JAN-04:12:17:00 pm');
; Y8 X7 e2 v# M+ [4 A+ e1 O7 \ + _; O i* A5 }: A
execute dbms_flashback.disable; ) x# L. k4 c; w# r" \
% Q2 W9 H4 T3 L! F7 @' ~
/*回滚段的统计信息*/ & M* [9 `! O8 T B1 U: F
select end_time,begin_time,undoblks from v$undostat;
6 i1 R1 b; \% `; z7 `/ x" r; M
# B% ]! U% w' k/*undo表空间的大小计算公式: UndoSpace=[UR * (UPS * DBS)] + (DBS * 24)
b7 E! V- M5 D: V& G( O- s( C; r% fUR :UNDO_RETENTION 保留的时间(秒)
6 M, Y0 Y9 u6 L% K2 L# G9 h, v( XUPS :每秒的回滚数据块
+ v N) e" o% d! W/ g5 fDBS:系统EXTENT和FILE SIZE(也就是db_block_size)*/ ( N$ U$ x8 D' Q" U
# ~# A4 `: _+ X: t; T) B/ }select * from
/ y3 w0 c' g4 p/ P n( S; Cdba_rollback_segs/v$rollname/v$rollstat/v$undostat/v$session/v$transaction; 2 d8 |8 K0 ]2 _8 p0 @) [* ?/ D
! @: v/ z6 w5 E% o8 E/ X0 Z3 v
show parameter transactions; % c4 j+ o3 @4 N6 y. B5 Z
; j6 D! h% |0 g; O
show parameter rollback;
. m3 e& r/ t* E' P* i% S9 W/ @ A+ K% h7 z * m! B9 Q# Y- G
/*在手工管理模式下,建立公共的回滚段*/ , [3 D% x) x. n& W8 a0 {* V
create public rollback segment prbs1 tablespace undotbs;
. Z8 X- J6 g |# v Q8 L, m( t3 n , \% n& y+ N/ B+ w% z6 e
alter rollback segment rbs1 online;----在手工管理模式
4 i! R) X6 V' ?1 k8 L6 o8 Z
. ?5 Z; T/ y) b, E5 n+ ?/*在手工管理模式中,initSID.ora中指定 undo_management=manual / N5 x5 p7 f! f" r9 x
、rollback_segment=('rbs1','rbs2',...)、
3 w4 j3 y* s* N' d' etransactions=100 、transactions_per_rollback_segment=10
% ^+ ~! r( e' Y9 t4 V. v, T然后 shutdown immediate ,startup pfile=....\???.ora */
+ E# v# T0 O0 `+ N# g- k4 @3 E, l: j $ K) t9 f& z l; {0 @6 c9 d
########## Managing Tables ###########
+ A* U, n5 W7 c; P, |9 Q4 P" a: y/ f8 F 5 R: H S0 D3 z& d1 y% I5 ^3 ?* K
/*char type maxlen=2000;varchar2 type maxlen=4000 bytes
+ P. ?4 N7 D& h Xrowid 是18位的64进制字符串 (10个bytes 80 bits)
6 I! g k$ `" H6 v+ K. {0 Jrowid组成: object#(对象号)--32bits,6位
& E ^* }1 C& Urfile#(相对文件号)--10bits,3位 9 B9 u( E5 O* l3 @$ }. D% ` ~8 X0 O
block#(块号)--22bits,6位 3 _1 }& B! u4 s1 B) {) ^
row#(行号)--16bits,3位
' q7 \. P+ T% `64进制: A-Z,a-z,0-9,/,+ 共64个符号 ' e3 w" g" `. M& _2 P4 M
# C2 C2 |8 L) o# k6 ~$ ~dbms_rowid 包中的函数可以提供对rowid的解释*/
" k' T& w- a4 N2 ~7 d/ p 0 J* \6 p+ W: M s
select
* e6 v ^1 L9 Q8 mrowid,dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_row_number(rowid) 3 |, Y7 X5 X- O( o$ H
from table_name;
( J0 ?* V& P7 l+ |' }
( L! C2 i, @1 ]! @create table test2 & H, m9 \7 A$ R9 m# U
(
) K% e2 X5 y/ @4 Y' y. h7 F4 [2 I2 lid int, 0 U+ J4 O% Z5 j
lname varchar2(20) not null,
0 y; ~/ B5 | F! |. Dfname varchar2(20) constraint ck_1 check(fname like 'k%'),
' j& o2 T) v) v w8 R9 z4 vempdate date default sysdate) 9 E! ? Z& H- z4 K
) tablespace tablespace_name; # b ?1 T2 ]9 C+ M/ d
6 T# Z1 A& ]! M s/ W8 H ! i2 {8 X' {; a% k
create global temporary table test2 on commit delete/preserve rows as select * / K8 ~6 u; X& E
from kong.authors; 6 a& y6 x2 ~% i$ s6 M$ c N
6 }/ R$ [! N7 Wcreate table user.table(...) tablespace tablespace_name storage(...) pctfree10
( L o* S3 F+ J" v" bpctused 40; 4 Z5 B& L% v) a7 o* }. A( F
`4 h' c, F5 r
alter table user.tablename pctfree 20 pctused 50 storage(...);---changing table
6 `) l, ^( g4 }# Fstorage
. p3 r+ r0 g3 q/ s) B8 u" e* h
4 |3 ]% @" j4 d, k; v/*手工分配分区,分配的数据文件必须是表所在表空间内的数据文件*/ 1 O: _6 r) x! Z6 }4 h$ p
alter table user.table_name allocate extent(size 500k datafile '...');
2 d* m. M+ y% i9 K# \9 W8 X! X$ H 4 y( A" T1 c; x$ D: P
/*释放表中没有用到的空间*/
! U5 q0 Q8 `2 u1 U7 Walter table table_name deallocate unused; 2 Z8 E1 H: X0 J" }1 y6 M5 Z
! Y- u* s+ H- v2 n- y s+ salter table table_name deallocate unused keep 8k; 8 L6 P8 R( Y7 r0 C- ]
+ l4 g" S& ^/ T0 M* v/*将非分区表的表空间搬到新的表空间,在移动表空间后，原表中的索引对象将会不可用，必须重建*/
- D& E+ }/ m+ Palter table user.table_name move tablespace new_tablespace_name;
4 {, k3 V2 F8 D2 m0 K+ A
( X5 V ? @! o9 c) xcreate index index_name on user.table_name(column_name) tablespace users; / N1 T' f4 c* i) t/ y4 G. w
Q; v" R: J. d0 I2 A. a( e
alter index index_name rebuild; 4 n5 o, V( t5 R t, i' j3 }( u' }
$ v2 q6 ]* p/ jdrop table table_name [CASCADE CONSTRAINTS];
% n- i2 _* h. L/ |8 }
3 x$ i) Z2 t& K9 f7 [alter table user.table_name drop column col_name [CASCADE CONSTRAINTS CHECKPOINT + y% k" j% S8 L) `% Y/ `; R5 v
1000];---drop column
$ ]" \+ A! @: B4 g 0 ^4 x2 ?# L6 D0 h8 i0 j
/*给表中不用的列做标记*/
1 w, g1 U: H: d! ~; k! Q7 Kalter table user.table_name set unused column comments CASCADE CONSTRAINTS; 5 _+ h) `' e7 F" V* Y
' Q x- `/ I6 n7 F& U3 M- k$ _/*drop表中不用的做了标记列*/ " F- w+ D( J' h. X+ ]2 f( l, k) s! W
alter table user.table_name drop unused columns checkpoint 1000; 0 Q1 D/ E8 t( l
) t: W4 K! z4 P* h
/*当在drop col是出现异常，使用CONTINUE，防止重删前面的column*/ ; B/ X6 u% y% U: |$ ~+ k1 E4 ?1 L% Z
ALTER TABLE USER.TABLE_NAME DROP COLUMNS CONTINUE CHECKPOINT 1000; 3 M: S' @/ N* s' L# I; z
0 @4 ]5 V6 u' o! p6 aselect * from dba_tables/dba_objects; , ^) ^( Z5 S8 v3 @8 K: M; I
0 B7 g( o) i" g
######## managing indexes ########## . ?! n& n* o3 r
2 @$ e% ?4 K j3 q" H' M/*create index*/
0 b5 i) f) V2 v Pexample: ! H3 {8 }2 P9 |+ E2 c
/*创建一般索引*/ ) E7 {8 K/ `1 h9 V H( x$ S, \
create index index_name on table_name(column_name) tablespace tablespace_name;
* p" |. M1 {+ f! b# O/*创建位图索引*/ 4 F# c3 v6 U6 {5 W/ ]3 m( A
create bitmap index index_name on table_name(column_name1,column_name2) 4 P, W E( ?, \" s
tablespace tablespace_name; ; D+ v' T7 S) ?5 m" C+ v
/*索引中不能用pctused*/
- a( h$ F( @+ Y3 Bcreate [bitmap] index index_name on table_name(column_name) tablespace * g1 l6 R, v( u! M, I Z
tablespace_name pctfree 20 storage(inital 100k next 100k) ; + Z, _" |- i6 }1 i+ N' k, \
/*大数据量的索引最好不要做日志*/
8 v2 u2 r* F1 J% b# jcreate [bitmap] index index_name table_name(column_name1,column_name2)
* W9 t% ]; P+ [5 [( t- K. Btablespace_name pctfree 20 storage(inital 100k next 100k) nologging; 4 x. h3 n' x& d6 O
/*创建反转索引*/ , Q& H# [: Z1 o+ p
create index index_name on table_name(column_name) reverse; 0 s @$ X i6 N8 c9 T. J+ N
/*创建函数索引*/ * r0 e/ D% q8 l/ A, S/ e! `
create index index_name on table_name(function_name(column_name)) tablespace 5 D9 e4 p/ Z: a2 I" `# R' n: b& k+ a
tablespace_name;
: U3 T# c; c2 X: U( I. H/*建表时创建约束条件*/
2 Q$ ^6 F( H5 n, Gcreate table user.table_name(column_name number(7) constraint constraint_name
g" x9 g& o1 f" Y' H/ vprimary key deferrable using index storage(initial 100k next 100k) tablespace
& \* E8 g9 w4 [% atablespace_name,column_name2 varchar2(25) constraint constraint_name not / Z- U e! c# ^+ M0 R6 B0 N
null,column_name3 number(7)) tablespace tablespace_name;
3 Y+ Y2 e/ N: h* e' t8 v! s
, j$ }: W2 W. o# A/*给创建bitmap index分配的内存空间参数，以加速建索引*/ ( O m2 i5 L! Y. a& {3 H6 W7 h
show parameter create_bit; % N0 V) a2 d$ O8 |( W
" t& G9 z/ `. l* x9 I+ P
/*改变索引的存储参数*/ 2 a. X4 Y* D6 i
alter index index_name pctfree 30 storage(initial 200k next 200k); 9 y1 U0 n' u% z. c3 v
& V$ E+ ?% d3 \& m9 L/*给索引手工分配一个分区*/
/ o" j1 }: f% a* {" Halter index index_name allocate extent (size 200k datafile
R+ G* {6 a& L' s6 p& \: N'$ORACLE/oradata/..'); ! Q) T/ ? Y% d. y/ J5 f
1 d; y. h% O8 ~8 O* K! F+ L5 \/*释放索引中没用的空间*/
+ x0 \ {. A# o2 calter index index_name deallocate unused;
C1 H1 t" a. K4 a+ {3 M0 H - F) R% E) R: s, k% Y
/*索引重建*/
0 c4 X H! o4 E k0 _) qalter index index_name rebuild tablespace tablespace_name;
* j9 m+ e* K' B% ], ? o. ^+ [% a7 k ! i5 Q# m G a* h2 L: A& L& k
/*普通索引和反转索引的互换*/ + R! x9 ]" X( V$ O) v c. z
alter index index_name rebuild tablespace tablespace_name reverse;
, V+ g' e& f! H! }7 s" }/ m
: e0 ?& Z3 \$ n7 s) u3 h/*重建索引时，不锁表*/ ; i8 |, O9 _- c
alter index index_name rebuild online;
- u- b8 W& l/ W$ d
* o" e9 t( A. \) }7 L C' z/*给索引整理碎片*/
% I" A& x2 y7 z' U( d' ^6 }alter index index_name COALESCE;
( B1 u8 b2 z* y( r
& }& m# F% k$ t N, F' o/*分析索引,事实上是更新统计的过程*/
% ]7 a& S7 F2 }: Y$ {analyze index index_name validate structure; ) |0 g# @# y7 K" S4 o- f
# {% @3 [5 B, [# Z0 Jdesc index_state; # p. ^; ~9 B0 m$ N% j
. X& b p# Q: t2 L9 b5 I5 bdrop index index_name;
& ] l, X) Q- v2 d
0 P; l7 X* v/ f0 ]( {, I2 Kalter index index_name monitoring usage;-----监视索引是否被用到 . q- P) S# i4 k! Y0 i: h
3 d0 l8 R2 ^9 T: M6 I
alter index index_name nomonitoring usage;----取消监视
& R4 E8 ~# A' k+ M& S! h
# X3 h" J7 J/ l$ }/ l$ N2 x1 ?/*有关索引信息的视图*/ ) @3 f# E/ j# z8 I% q7 c/ Q1 r
select * from dba_indexes/dba_ind_columns/dbs_ind_expressions/v$object_usage;
5 _2 Y, F6 S! D: q* X$ w 9 d4 k; C/ o" u) a
########## 数据完整性的管理(Maintaining data integrity) ##########
* t0 }4 b) \; l4 ]% C 7 K6 J: w2 I4 h$ O$ h) C) l
alter table table_name drop constraint constraint_name;----drop 约束
& T2 k6 o+ i: n
6 J# ]6 J1 T" K3 W) s: [7 D8 ?, Talter table table_name add constraint constraint_name primary . f- G7 C3 `& z: b
key(column_name1,column_name2);-----创建主键
7 V, u1 x0 p. Q' \. _ 9 U' _" x) R' V% z @
alter table table_name add constraint constraint_name
% \+ P5 l+ m; r% T3 }, C2 K# xunique(column_name1,column_name2);---创建唯一约束 + ~" ^3 R5 m* l- f0 c5 W! t
( y& m2 R) P- s$ B2 Z) ?3 d8 b& ]/*创建外键约束*/ 0 `$ i7 A9 u+ v9 L; ?) e
alter table table_name add constraint constraint_name foreign key(column_name1)
) B$ H& g( b, q9 oreferences table_name(column_name1);
! s$ X+ s! u( P- p , G- t1 E% A( Q k a& t
/*不效验老数据，只约束新的数据[enable/disable：约束/不约束新数据;novalidate/validate:不对/对老数据进行验证]*/ * I D2 ^1 ^, ]+ j* [) a6 z
alter table table_name add constraint constraint_name check(column_name like # F7 ?( q5 @# V0 G) G1 V
'B%') enable/disable novalidate/validate;
+ u* o8 @' Z3 k* P/ R ' x, q+ N( s# s n
/*修改约束条件，延时验证，commit时验证*/ . B" u' w* Q" O2 i: G Z, X
alter table table_name modify constraint constraint_name initially deferred; ^. D! R4 |- h3 ]1 n6 B8 e. q
) O3 }1 O5 {% X/*修改约束条件，立即验证*/ * x x8 L% R7 t
alter table table_name modify constraint constraint_name initially immediate;
" n( |4 D2 M5 G# }7 n " t) F/ s1 R+ t* b0 U+ ^
alter session set constraints=deferred/immediate;
9 V9 j' v3 a$ B/ D, I5 O ; ?/ D4 ?% L2 o
/*drop一个有外键的主键表,带cascade constraints参数级联删除*/
* n* r2 h. y4 z4 U4 E8 w2 ^drop table table_name cascade constraints;
2 x6 a. x' j0 a2 ]$ m* P- k
! I" Z- n0 m6 ~+ M- `& u( _/*当truncate外键表时，先将外键设为无效，再truncate;*/
! n6 R* J# l# X2 w9 Ntruncate table table_name;
E; p$ [! o. U6 m8 S - _0 H; Y. o& D. x
/*设约束条件无效*/
4 s, x' t& ^( d2 f& O5 Kalter table table_name disable constraint constraint_name;
7 l0 [ ]2 j; n: U+ I' _! s; K- x d 3 E" G# _9 s( d9 v
alter table table_name enable novalidate constraint constraint_name;
& S! H; {9 t4 k4 c( G' H8 R . e/ G$ ]; P* n8 k, P
/*将无效约束的数据行放入exception的表中，此表记录了违反数据约束的行的行号；在此之前，要先建exceptions表*/ 9 O2 w4 r# p. w6 o% \% }: @' X X
alter table table_name add constraint constraint_name check(column_name >15) ) I- [& e4 e! U1 W8 q v) R
enable validate exceptions into exceptions;
/ L/ q# N M9 U& h. } ; {; v& q% I4 c0 p
/*运行创建exceptions表的脚本*/ $ W( `. z7 U& ?; P. }- \4 w0 j8 Y
start $ORACLE_HOME/rdbms/admin/utlexcpt.sql; / x3 e+ k* F [# q8 T
+ X. z( S3 H0 `! j& w% V9 ~
/*获取约束条件信息的表或视图*/ 0 ]5 ~2 c( i" ~" [& J
select * from user_constraints/dba_constraints/dba_cons_columns;
% u. c: p6 o8 W6 s% h C9 @3 P: i
' z9 Y% E8 C! N" e################## managing password security and resources #################### # C# S3 f& q9 X" G4 R$ ]8 @* {
, F4 }' n% ~; g8 R$ N; Z: ?* J2 N
' R7 E6 `* D6 K! h& R
alter user user_name account unlock/open;----锁定/打开用户; 6 b& e4 A: v6 Z% X. Z+ E
+ l- |7 p! J' T! h. J1 V
alter user user_name password expire;---设定口令到期 + _2 J- \* \9 e% D9 R( a
" b; t6 {/ V8 S0 V& ?' w% H
/*建立口令配置文件,failed_login_attempts口令输多少次后锁，password_lock_times指多少天后口令被自动解锁*/
3 t D% h& o: ?2 O0 Ocreate profile profile_name limit failed_login_attempts 3 password_lock_times
8 |8 a$ K/ Q9 y" E% |' ^. q1/1440;
8 h9 N! A6 r% }/*创建口令配置文件*/ 4 ~/ r$ C5 K6 R* b
create profile profile_name limit failed_login_attempts 3 password_lock_time
* |* I* j) Y; Wunlimited password_life_time 30 password_reuse_time 30 password_verify_function % {) E; b: p3 [' J+ H) _
verify_function password_grace_time 5; , z. H7 b0 U5 t
/*建立资源配置文件*/ ; j _! Z$ @* p4 w
create profile prfile_name limit session_per_user 2 cpu_per_session 10000
# q3 n( I G6 R9 N* |5 k& ?idle_time 60 connect_time 480; ) h$ D7 }: y4 E: j$ o
+ o) }) b1 n1 k# X4 s7 F/ c
alter user user_name profile profile_name; . F3 v8 M& }( k$ q4 {: ~
/ P1 F- {' |, q }9 G8 L/ j/*设置口令解锁时间*/ " k# m: a- v7 m& `
alter profile profile_name limit password_lock_time 1/24;
+ P! |; H! [2 m# v# B; S! G ( s2 N1 x5 |8 ^, x( u8 o" y
/*password_life_time指口令文件多少时间到期，password_grace_time指在第一次成功登录后到口令到期有多少天时间可改变口令*/ 4 F% ~+ ?( {+ x2 h+ b
alter profile profile_name limit password_lift_time 2 password_grace_time 3; ; @3 t9 j9 ]; \3 C8 V7 I& `$ r+ X
6 \: a3 ?9 f4 t: i$ g1 v) B/*password_reuse_time指口令在多少天内可被重用,password_reuse_max口令可被重用的最大次数*/
- \6 F! r) q" C# n. Aalter profile profile_name limit password_reuse_time 10[password_reuse_max 3];
9 i* W8 o v( N3 [ $ D, K4 K8 j8 E! I. w7 m+ k
alter user user_name identified by input_password;-----修改用户口令
+ U$ P. ?/ }& S 4 m0 z! x8 T3 r9 f
drop profile profile_name;
" C$ G- Y% q4 [) t* h# m / n; L0 H+ H; ], h8 B k
/*建立了profile后，且指定给某个用户，则必须用CASCADE才能删除*/ ; \7 O6 i' g6 K; d9 ~3 c
drop profile profile_name CASCADE;
7 _$ U3 U- ~+ p T( {& E9 } - \! D( p+ A2 `& a I8 Z
alter system set resource_limit=true;---启用自愿限制,缺省是false 2 \7 |& L D# F
" B- Y4 V+ o5 F7 Q6 _
/*配置资源参数*/
# O6 Q A. B) k5 Q2 [3 W* ?# Palter profile profile_name limit cpu_per_session 10000 connect_time 60 idle_time
`# \( U/ l8 y/ P9 o! Y5;
' c4 T R- L9 x4 h) S$ X/*资源参数(session级) ( V6 c9 d! Y' N* T2 ]3 H) _
cpu_per_session 每个session占用cpu的时间 单位1/100秒
! V! R; M# w! ~- n" A; D* Bsessions_per_user 允许每个用户的并行session数
9 S0 l: D& i9 h4 Y* c% jconnect_time 允许连接的时间 单位分钟
$ {+ b- t; ~: E% Gidle_time 连接被空闲多少时间后，被自动断开 单位分钟 1 ^% C+ t/ y, d$ ~* M# K( C# k9 Q
logical_reads_per_session 读块数
& h$ d- Z+ w/ X% jprivate_sga 用户能够在SGA中使用的私有的空间数 单位bytes
( ^7 E2 k. r3 s3 V/ m5 j$ I# V( @; ~ ; P( G, H+ ^1 I5 A& R& M) @
(call级)
$ U3 X( u- j4 v% Ycpu_per_call 每次(1/100秒)调用cpu的时间
3 ^% O, N5 w, n4 A8 ?$ ]logical_reads_per_call 每次调用能够读的块数
3 i+ j% f7 s/ Z3 A: I Q' p2 R*/ 4 O; f9 n& Y0 ^
5 x0 r' `6 M$ C
alter profile profile_name limit cpu_per_call 1000 logical_reads_per_call 10; 8 e& \8 p7 R; ~* d1 ^2 ~2 d
( _; F5 J! v' ~4 g5 \desc dbms_resouce_manager;---资源管理器包 1 j4 R, `$ U+ ^
- {9 T6 z0 |! k/*获取资源信息的表或视图*/ ~, s& d4 |! ~
select * from dba_users/dba_profiles;
* {0 h6 W( \9 B. X$ }. g
; {$ U; u0 e6 s: r###### Managing users ############
! M) P/ E1 o( f % R3 w, l, i! N: `' ~, Q
show parameter os; 7 m8 N4 ?. P; A" T# z
6 M& Y# N3 X. s, E' n5 }3 A/ G
create user testuser1 identified by kxf_001;
; O' m9 q( k4 x
) q4 f9 \2 q) \. bgrant connect,createtable to testuser1;
6 K8 u+ @8 Q. A- d$ o1 M* X
6 H" ?+ v V+ f' |, ?# ?8 L: s4 x7 valter user testuser1 quota 10m on tablespace_name;
# g# v4 n5 z+ v' I9 K- R % [) w/ ^! z* X/ h2 X
/*创建用户*/
/ a, v1 e: @: E1 \% W/ }8 Ucreate user user_name identified by password default tablespace tablespace_name
' Y; _% k. ?/ I+ |, A* qtemporary tablespace tablespace_name quota 15m on tablespace_name password
& T3 Z9 q- T" Uexpire;
5 k7 J0 s! P8 E$ G. \! _0 p
5 J/ d7 Y) l5 `' r# i0 N: a/*数据库级设定缺省临时表空间*/ : ?6 w6 J5 S8 {$ e4 V L
alter database default temporary tablespace tablespace_name;
2 F* R( p$ h; }
" y) h; a' H9 ?/*制定数据库级的缺省表空间*/
6 m/ [9 J/ o4 a; G* p Z: Ialter database default tablespace tablespace_name; 5 A: W* M( p; @7 \, x
* O1 q( z- j8 i4 n& |/*创建os级审核的用户，需知道os_authent_prefix，表示oracle和os口令对应的前缀,'OPS$'为此参数的值，此值可以任意设置*/
. F5 D: [6 }/ u- H. ~7 ~8 O( V! N1 mcreate user user_name identified by externally default OPS$tablespace_name
* k) c% }1 `3 J( y* rtablespace_name temporary tablespace tablespace_name quota 15m on O& q/ l5 K( U/ a
tablespace_name password expire; ( P7 ]- `: m' H: T Y$ ]
% ~" G4 ~- ?4 F6 }3 \+ F# k3 G8 D/*修改用户使用表空间的限额,回滚表空间和临时表空间不允许授予限额*/ : H. O* }: g- K5 K" J
alter user user_name quota 5m on tablespace_name;
2 ?) j7 O3 f, ^ ]+ b, o7 }9 J ^9 j9 t, C3 [
/*删除用户或删除级联用户(用户对象下有对象的要用CASCADE，将其下一些对象一起删除)*/
5 V8 `0 @ v$ M! ~ N5 e: z" Adrop user user_name [CASCADE]; - V: s h8 ?' W/ C6 L* S
' u" W$ B# y) A; k, l* O
/*每个用户在哪些表空间下有些什么限额*/ 8 ~) _2 M0 y% g: v
desc dba_ts_quotas;select * from dba_ts_quotas where username='...';
# r0 V6 s% Y! Y* G2 |1 z: {( q
0 p5 w; G/ e# x% B: |7 K/*改变用户的缺省表空间*/
1 k @* T$ d3 n: Ealter user user_name default tablespace tablespace_name; 5 g$ F% ]. k( ^1 B" F" e
; c6 x: D' W- ]: I$ j0 P
######### Managing Privileges #############
! \" A9 E a% m0 \" l% B
+ }. O0 j7 p0 L: _' g0 u6 q# m& wgrant create table,create session to user_name;
9 G7 f! k$ ^: l" b . @3 ~& e) X! o8 I8 k \
grant create any table to user_name; revoke create any table from user_name;
- d+ v' a9 ^- p/ Z3 E: P * K0 I) H' w! T% C! `! g
/*授予权限语法,public 标识所有用户,with admin option允许能将权限授予第三者的权限*/ / Q! L- Z: R: f3 r: v/ h
grant system_privs,[......] to [user/role/public],[....] [with admin option]; 4 F( f) v$ W* \& |7 f- f$ ^* O
2 K8 ^( U- h( W5 g
select * from v$pwfile_users; ! Q) S9 L7 t% J! x. v' R. G
x( |& r- [# X: n6 ~: U% p1 Q/*当 O7_dictionary_accessiblity参数为True时，标识select any table时，包括系统表也能select 7 I' c0 P. t% ]; u, w
,否则，不包含系统表;缺省为false*/
' H9 m$ Z8 ~5 y) g2 ushow parameter O7;
" [9 X |/ T& ^# S6 Z" z0 N: H
4 q0 k0 M0 f* A2 i/*由于 O7_dictionary_accessiblity为静态参数，不能动态改变，故加scope=spfile,下次启动时才生效*/
, A( H4 A" G; Q, p1 Ualter system set O7_dictionary_accessiblity=true scope=spfile;
; [6 J' W' c @+ C! i5 \/ m' _" r# g
, M1 K z/ @3 R4 q/*授予对象中的某些字段的权限，如select 某表中的某些字段的权限*/ 1 @; d6 _1 W' @! Y, d
grant [object_privs(column,....)],[...] on object_name to user/role/public,...
' u2 V% ?9 `! j& G) N- Ywith grant option; : F Z3 P) a' _+ ~3 x3 X9 _
. I' U9 B8 Y: t# q9 d5 G& {, p
/*oracle不允许授予select某列的权限,但可以授insert ,update某列的权限*/ ! `: T; \9 y# z, _7 ^$ y) v
grant insert(column_name1,column_name2,...) on table_name to user_name with . _3 \& j' c4 G* w, \* }6 b
grant option;
! a, j) ~ T. @
# s: y- G8 ?) s/ ~6 x( wselect * from . f# Y* y9 M; Q6 p. l) z; k" w
dba_sys_privs/session_privs/dba_tab_privs/user_tab_privs/dba_col_privs/user_col_privs; ; ^ r8 I1 W0 e$ S- G, t$ \& z" m
2 o5 b. S; x6 C9 N $ }, L2 O! p; k8 X* h
/*db/os/none 审计被记录在 数据库/操作系统/不审计 缺省是none*/
2 _* J9 F& [6 t6 p& Rshow parameter audit_trail;
2 |4 \+ B9 B9 s* e1 A# X $ ^2 |6 j( S+ ?: g& ~& s
/*启动对表的select动作*/
& P2 s+ V8 H( b% y5 r# P5 }, U* Caudit select on user.table_name by session;
# e- [9 s# y$ ^3 Y / |6 n! y; S2 m1 w: y$ ?: A
/*by session在每个session中发出command只记录一次，by access则每个command都记录*/
. a; e! j Y5 I2 E8 ~2 X9 P' |audit [create table][select/update/insert on object by session/access][whenever
' d" _) Q1 b9 L4 ~7 isuccessful/not successful]; 3 M* A5 x# f5 n# Y! |1 {. }
4 K: r/ J1 l9 ?0 D: Q. X% W1 I# [desc dbms_fga;---进一步设计，则可使用dbms_fgs包 ( }( g/ t% E) M: g* r9 R# z* E8 k. |
" B( L) b( }- J! ]! o/*取消审计*/
( S! r9 K" V! `" [- N" wnoaudit select on user.table_name;
3 B" V _1 F3 q. @# @; x9 H+ p
, f9 x' P: q1 C# e8 H' C/*查被审计信息*/ 8 A% T% E8 X z! o
select * from & D# r% y4 V6 k7 x- m3 z( {5 g
all_def_audit_opts/dba_stmt_audit_opts/dba_priv_audit_opts/dba_obj_audit_opts;
. n( d- y/ N; e! y" R2 M 0 u" z' k( ^4 U/ H& M/ u5 Z, A& Q
/*获取审计记录*/
+ x' G0 H# X8 I4 c8 D/ Vselect * from
( u6 b9 Y2 @. s( F! L- ]dba_audit_trail/dba_audit_exists/dba_audit_object/dba_audit_session/dba_audit_statement; ; C* h0 P" _) b% T/ U/ }# l5 j
/ X3 z8 o% P( i8 _0 s
6 c# H4 B! B% O* |########### Managing Role #################
9 a6 O+ h3 Y0 H ?" q , a+ M- {; @5 o% J
create role role_name; grant select on table_name to role_name; grant role_name ) C) ?( X/ u: Z4 ?. Y$ S
to user_name; set role role_name; 2 z" g" m! c) j7 |+ ~ y
+ d4 V6 V: x7 J
create role role_name; ( `# t. J: { f. V3 q" F
create role role_name identified by password;
. Z$ K8 A8 T3 }, {% q% V) n7 qcreate role role_name identified externally; ' y0 [7 D+ Q: M- f
) Q0 @3 x6 a( D9 U' n. H6 }
set role role_name ; ----激活role 5 Q |2 ~3 X3 d1 C8 q ]
set role role_name identified by password; # W; S$ t1 w; U9 P6 c
7 {" }- v! i5 A- c) _
alter role role_name not identified;
0 T* E/ o2 q' O k0 [0 ralter role role_name identified by password;
( E' _9 E9 [+ E" U- balter role role_name identified externally;
& @5 _! v/ \( p3 K
% i' C% q. m3 p, U5 Bgrant priv_name to role_name [WITH ADMIN OPTION];
1 l1 b0 x/ K0 T \/ S$ ~" rgrant update(column_name1,col_name2,...) on table_name to role_name;
' F ]7 W& M9 `* p8 v+ Sgrant role_name1 to role_name2; ; v0 b2 @0 E6 G ^& F) D+ R" j
8 k, Z' z- C- u9 E+ |+ ~; R
/*建立default role,用户登录时，缺省激活default role*/ 0 y/ T. M% E" v' z$ S
alter user user_name default role role_name1,role_name2,...; 6 H2 z3 P+ s" @5 z/ C
alter user user_name default role all; $ T1 t* T! V( m2 u( P$ e# `
alter user user_name default role all except role_name1,...; . v7 f. C+ Y9 t' u$ a
alter user user_name default role none; + I) j2 p# I4 }. Q2 ^- g8 b& \$ b: C
# c* W% u" F$ m$ A$ P1 j7 e4 \
set role role1 [identified by password],role2,....; , [0 C* u& @! t- f" Q
set role all;
V" M) D7 H; Y- N' d5 N, v2 }set role except role1,role2,...; / H1 y H4 ]. Q( ?" ^
set role none;
5 n# j) I: Q; h! s% y ! ]) f' D' q2 m; n9 ^7 R9 d
revoke role_name from user_name; * Q2 u: _: O. E6 C1 v# k
revoke role_name from public; 6 G' a# l; t, _
2 y7 u1 [6 e# I, Ydrop role role_name;
! t2 R/ g; I4 }/ s6 | / @2 r0 ^1 f) w3 a& j+ Y
select * from T8 _+ I, m( p5 E9 s
dba_roles/dba_role_privs/role_role_privs/dba_sys_privs/role_sys_privs/role_tab_privs/session_roles; , b% A9 X7 q: D$ C* ^' C9 O d" r
: c5 U# k+ F3 n) v) U: K
3 z# u8 u7 Z, s: _) C! g
########### Basic SQL SELECT ################
2 {8 h6 `1 F% A4 @& w! K
( d8 \/ i" I/ r2 uselect col_name as col_alias from table_name ;
. N/ _, I5 s* s2 E, i8 q3 n/ w- M I0 R % u& @# H5 Q+ p& A$ z7 ^* `7 J, ~
select col_name from table_name where col1 like '_o%'; ----'_'匹配单个字符
1 q3 d+ f' Q; ~ 6 _5 G7 ~$ U# ?% k
/*使用字符函数(右边截取,字段中包含某个字符,左边填充某字符到固定位数,右边填充某字符到固定位数)*/
: Z8 m4 n: U1 cselect substr(col1,-3,5),instr(col2,'g'),LPAD(col3,10,'$'),RPAD(col4,10,'%') . |/ l) u: P. t7 I1 _0 I k% @/ K. z
from table_name;
) E4 m# H+ {, ~4 {" X * f3 l, M3 y) r3 H4 Z- [7 p8 _& t
/*使用数字函数(往右/左几位四舍五入,取整,取余)*/
$ N& E$ i6 g+ B1 ?6 o% C- f! ~select round(col1,-2),trunc(col2),mod(col3) from table_name ;
+ O3 T1 x+ x: R N; h
! U) c* J! u4 |/*使用日期函数(计算两个日期间相差几个星期,两个日期间相隔几个月,在某个月份上加几个月,某个日期的下一个日期,
4 E/ l6 Q! i K' H7 a# u& x7 S某日期所在月的最后的日期,对某个日期的月分四舍五入，对某个日期的月份进行取整)*/
7 u5 Z2 a( Z \) Nselect (sysdate-col1)/7 % ~* F( u* `+ ?( Z, }. B
week,months_between(sysdate,col1),add_months(col1,2),next_day(sysdate,'FRIDAY'),last_day(sysdate), 1 `& Y* {/ ]4 X. h
7 a# f- Y: d/ ?7 H, ~# M- N8 P
round(sysdate,'MONTH'),trunc(sysdate,'MONTH') from table_name; % X' e' s* l) \& E
) K* ?- R; s, l2 Y
/*使用NULL函数(当expr1为空取expr2/当expr1为空取expr2,否则取expr3/当expr1=expr2返回空)*/ ( V8 \* G& B4 t; z/ L ^
select nvl(expr1,expr2),nvl2(expr1,expr2,expr3),nullif(expr1,expr2) from
# {( W3 G# p2 J1 w* W. wtable_name;
: _' I7 N* }0 @, B( z" ~ & p: ^3 R2 O4 s3 ~; D7 w
select column1,column2,column3, case column2 when '50' then column2*1.1 ! a7 o$ t7 O" v% Y$ L9 y5 U( G: U
when '30' then column2*2.1 % h! y4 s4 w% f- D5 f6 ]5 H6 N# s
when '10' then column3/20 4 T2 u" f, T9 u' w5 E3 }
else column3 . D/ a* A: t9 p7 Q/ I
end as ttt
( s, ^( S9 A) i. K Kfrom table_name ; ------使用case函数 . g, J; ~, n5 f5 M* I0 m6 L
: D7 g- B2 r2 w$ k8 J+ K4 B' f
select table1.col1,table2.col2 from table1
# V5 C- P6 m( i2 o9 N: a[CROSS JOIN table2] | -----笛卡儿连接
5 ?6 V, T9 ^8 P# U0 ~) o2 `6 T[NATURAL JOIN table2] | -----用两个表中的同名列连接
5 \6 p" l4 m/ h/ S; G8 r+ C[JOIN table2 USING (column_name)] | -----用两个表中的同名列中的某一列或几列连接
6 H) a5 [& D" M* u" d$ I[JOIN table2
& ]: h4 {; |! n: j' \ON (table1.col1=table2.col2)] | ! H6 S" o; `% C- @
[LEFT|RIGHT|FULL OUTER JOIN table2 ------相当于(+)=,=(+)连接,全外连接
9 V/ M& E1 G; P& YON (table1.col1=table2.col2)]; ------SQL 1999中的JOIN语法; 4 q. R9 p$ }* F' ^; g
( I/ C/ `( @3 h; _' T7 Y5 _2 Gexample:
3 a v: p$ w* Rselect col1,col2 from table1 t1
0 u- A& f6 i5 X0 B0 f! J$ Z% Ejoin table2 t2 " D8 [. O/ H* b. _
on t1.col1=t2.col2 and t1.col3=t2.col1 9 q5 Q: M$ ]% \# R- J% v# [
join table3 t3 2 o# s5 T, u+ _: x! c8 n
on t2.col1=t3.col3; 3 Q1 B' E' o3 P5 k1 x: r
$ y/ F4 d/ s& W, X: Wselect * from table_name where col1 < any (select col2 from table_name2 where 1 J, W! O: O7 g8 f, t8 m [5 h2 v
continue group by col3); 0 V- D6 H. d3 q3 ^" O5 b) [5 b
4 H2 ^2 l: K7 F0 G1 hselect * from table_name where col1 < all (select col2 from table_name2 where
! V4 L. I; _/ a: Icontinue group by col3); , O9 c0 ?6 r7 Q
7 L' J2 z! _% B" T0 ]1 l/ dinsert into (select col1,col2,col3 form table_name where col1> 50 with check
1 d3 ?; k8 B. uoption) values (value1,value2,value3); % C" e5 H9 m9 h# l
6 b* @' q. }* {MERGE INTO table_name table1 / E3 H% `5 h+ J1 t' o
USING table_name2 table2 $ I0 n8 m$ G3 N1 G( c0 J
ON (table1.col1=table2.col2) * E8 y, Z* s0 P+ x k+ r
WHEN MATCHED THEN ( R8 S7 R( W$ b" B' `0 B
UPDATE SET 3 l( f" D( e0 `! I# P6 \' S, w
table1.col1=table2.col2, ' c0 p- m5 C! r/ w, q% F
table1.col2=table2.col3,
. I& T) b# Z; @/ }- W... ' U7 b% `# I, H, e) T/ I. A; Y- \
WHEN NOT MATCHED THEN
$ y/ v" ?. E' A8 K$ N/ XINSERT VALUES(table2.col1,table2.col2,table2.col3,...); -----合并语句 ! g* ^/ y& F, U% W% @- h6 V! m
6 ^/ r; o& H" Z( W# q! [' J1 E##################### CREATE/ALTER TABLE ####################### ! ^$ e9 [; L8 L0 i+ _/ y0 l
6 J6 n8 H: q: x' a# U
alter table table_name drop column column_name ;---drop column . p' w. K6 V+ k7 h; B; [
! @0 j$ ]' y' I) _8 z. }alter table table_name set unused (col1,col2,...);----设置列无效，这个比较快。 8 I9 B& u3 `( x$ v! e
alter table table_name drop unused columns;---删除被设为无效的列
' m# ?; T1 _1 @+ Q
/ p) w: ~. `' r' l7 O. grename table_name1 to table_name2; ---重命名表
# Z9 y, t' j: [2 x2 i! t' l. x4 q # p# k5 [ e2 B7 O. x) I
comment on table table_name is 'comment message';----给表放入注释信息
* @7 w9 x, y4 i/ Z ' D+ }: [* Y' Z5 n4 c! l1 B
create table table_name 0 z, ]# K! {* K( Q* A3 V3 V9 F
(col1 int not null,col2 varchar2(20),col3 varchar2(20), , W/ @4 F+ O! q: P, R, V' j- s
constraint uk_test2_1 unique(col2,col3))); -----定义表中的约束条件 " X" h. A2 [/ w6 z; e0 r( n
9 C% _' \4 ^# d% u+ h6 J
alter table table_name add constraint pk_test2 primary key(col1,col2,...); . x/ U. q0 m4 C
----创建主键 0 c n2 E' T' e/ |; i
" K% S9 C( A! v6 }+ d$ Q2 p; G
/*建立外键*/
8 \3 t3 \4 i8 L. k7 ^0 {create table table_name (rid int,name varchar2(20),constraint fk_test3 foreign
) }- ?3 P" G: G0 e7 W$ ~+ Jkey(rid) references other_table_name(id));
# }* g1 E0 R& g0 R
$ e4 g( f6 T3 B% O0 salter table table_name add constraint ck_test3 check(name like 'K%');
1 K0 A+ p! Q3 o3 r. r
. F3 K% h: t1 calter table table_name drop constraint constraint_name;
# s' h+ z& L: X% X" R5 O" C) E
0 t6 W3 [/ j" g. }alter table table_name drop primary key cascade;----级联删除主键 ' }! ?9 K) o$ K# u* L/ [1 A
3 d( j' r4 ~9 V
alter table table_name disable/enable constraint constraint_name;----使约束暂时无效 * Y+ j! I! ]0 d9 p
) j3 o+ a5 I% E3 G, y3 D. N
/*删除列，并级联删除此列下的约束条件*/
1 n+ Y# d2 H3 \4 t) nalter table table_name drop column column_name cascade constraint;
$ q2 @1 ?# Q. ?( C2 t, O. j. c% o 2 k6 g2 V% @. @% t" R
select * from user_constraints/user_cons_columns;---约束条件相关视图
8 L& N- x& D& `! ?# s
0 R' R) t" {. J8 r############## Create Views #####################
) S& K* C& |4 @: B7 @ 4 V( F. m3 b/ A q/ d( P& j5 k; S
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name [(alias[,alias]...)] $ x& D: B- n2 I/ u
AS subquery
$ U9 w2 C- c% T" p+ S1 a7 L[WITH CHECK OPTION [CONSTRAINT constraint_name]]
" ?0 O8 g8 t2 j# K, v[WITH READ ONLY [CONSTRAINT constraint_name]]; ------创建视图的语法
3 J3 m: g6 C* k6 z8 k7 s8 ] ) }, F1 q! i' Z- m2 [. @7 z
example: Create or replace view testview as select col1,col2,col3 from 4 R" @; v/ s, J+ ^9 x5 x
table_name; ------创建视图
5 b9 ?$ ^$ W0 i4 T6 Y! ^, ]/*使用别名*/ 7 c$ q# x2 F1 A2 F+ z2 D& g
Create or replace view testview as select col1,sum(col2) col2_alias from * o/ x/ Z+ G- ^5 H
table_name;
1 v$ V. p) K/ T$ j/*创建复杂视图*/
- C) E. b' w. L4 U' T0 sCreate view view_name (alias1,alias2,alias3,alias4) as select
5 X$ e" L4 B; e9 qd.col1,min(e.col1),max(e.col1),avg(e.col1) from table_name1 e,table_name2 d
, ~% ~/ Z) ?, L9 r/ Ywhere e.col2=d.col2 group by d.col1;
y. K; Q9 ]; Z' t5 _/*当用update修改数据时，必须满足视图的col1>10的条件，不满足则不能被改变.*/
9 S0 j& [5 t$ g2 QCreate or replace view view_name as select * from table_name where col1>10 with
% q9 ?" @" z0 |6 f' |' j" U( G* ocheck option;
( }6 ?* _& O/ r8 e( J5 m ! \8 l+ t6 P5 ~) `
/*改变视图的值.对于简单视图可以用update语法修改表数据，但复杂视图则不一定能改。如使用了函数，group by ,distinct等的列*/ # l: j+ q7 u: N& ^: Y: m5 w' L5 ]
update view_name set col1=value1; # _' ?* X3 W" D' o2 P& `/ \
4 M6 Z) l9 ]. }% |" l6 }. r
/*TOP-N分析*/ 9 T) ^1 `5 E$ F; \0 f( n" n+ Q
select [column_list],rownum from (select [column_list] from table_name order by
+ I8 r/ R9 P" \/ m8 U. g4 \Top-N_column) where rownum<=N;
% f" [3 i! ~! d) d* h7 | : Y$ @. u- @ y6 r1 _. R
/*找出某列三条最大值的记录*/ ! E& H( Y1 I% @! q l- |! h. R
example: select rownum as rank ,col1 ,col2 from (select col1 ,col2 from
. C" o# y6 j2 O6 Ktable_name order by col2 desc) where rownum<=3;
: G) p' G' w' y. V3 ] 5 \; o( `/ s1 S6 t3 O. B
############# Other database Object ###############
3 U$ s& p! t# s# u+ E$ Q* Q1 H. \0 O 0 R" Y7 d" v, P5 a( q0 ]9 |- n" Z! S
CREATE SEQUENCE sequence_name [INCREMENT BY n] ; [+ D7 H7 ]4 r! n7 M
[START WITH n]
! o9 h, [% _- u% M: Q' m[{MAXVALUE n | NOMAXVALUE}] - |9 _4 r1 x% w& s& t7 f
[{MINVALUE n | NOMINVALUE}] + I8 {" ~4 t- z. d9 v4 q' _. S
[{CYCEL | NOCYCLE}] . D0 g, ~+ w8 L1 J( v
[{CACHE n | NOCACHE}]; -----创建SEQUENCE
# X. S% ^# m2 W
3 P- v: K5 Q; z: @. iexample:
2 f* M# L' N# x! l& K3 a. M7 XCREATE SEQUENCE sequence_name INCREMENT BY 10
, F! F t2 \& fSTART WITH 120 x+ r6 m6 L! v/ a+ ~2 i
MAXVALUE 9999 : s- M$ s" ^2 V0 p8 E8 i
NOCACHE
4 S* s6 N, ]4 D# `NOCYCLE;
! J! z+ C2 O% V" D8 D/ N; \ V ; c" f# R$ A' I* E" N
select * from user_sequences ;---当前用户下记录sequence的视图 ; [) ?9 e7 k2 Q* c+ T1 [
6 P( ]) Q& ^) d# p6 K
select sequence_name.nextval,sequence_name.currval from dual;-----sequence的引用 * L$ B$ N4 v& C- c g- p
6 w6 [/ d' l* H) C) h( r& s5 L
alter sequence sequence_name INCREMENT BY 20
) N9 d1 ^# b" y7 t- Q" }MAXVALUE 999999 ; k3 f* f2 \8 F& Y
NOCACHE
+ K/ M: p3 q* D' J- ?NOCYCLE; -----修改sequence,不能改变起始序号
1 ^0 z- s5 y2 y" F4 c& U% I6 C
3 i3 j" @) Z0 Z# Hdrop sequence sequence_name; ----删除sequence
: @8 f5 u* J6 l9 b- i' C
8 Z% e+ b* u! z w; e9 G9 d3 FCREATE [PUBLIC] SYNONYM synonym_name FOR object; ------创建同义词 : D5 j( k5 D8 J ]. K; o
* C, s4 R8 [7 H: mDROP [PUBLIC] SYNONYM synonym_name;----删除同义词 9 n9 B9 j2 t. g: B' W: Y
* h$ c/ \9 x7 h! e% }3 z, j! i
CREATE PUBLIC DATABASE LINK link_name USEING OBJECT;----创建DBLINK
; F. x1 H* ?& _; N: s: U) y F
5 E q: ]7 K2 z& kselect * from object_name@link_name; ----访问远程数据库中的对象
$ N8 b* F7 q! F0 n3 Z
& Q+ d* e& D! [6 Z/ |/*union 操作，它将两个集合的交集部分压缩，并对数据排序*/ 2 t# |: L+ M4 Q. \1 I% t6 w
select col1,col2,col3 from table1_name union select col1,col2,col3 from 0 j. d* G- n1 l( l! v8 I1 H* p. C+ h
table2_name;
* U" ]6 Y* C8 Z" [
2 G+ o9 ^( w) Q! d) y/*union all 操作，两个集合的交集部分不压缩，且不对数据排序*/ $ _2 M* p8 s1 _/ l4 Q
select col1,col2,col3 from table1_name union all select col1,col2,col3 from % I1 N3 r. `9 T$ N6 ?8 S, l; @- z
table2_name; / m' d6 {1 v: }
( @: D; p6 M s) V# }0 a$ @/*intersect 操作，求两个集合的交集,它将对重复数据进行压缩，且排序*/
% i: b2 q: i ]6 Q$ Wselect col1,col2,col3 from table1_name intersect select col1,col2,col3 from
# P# S2 Y" K7 ~( p9 n/ B6 vtable2_name;
% B5 l# I0 c0 W$ o. i8 ` 2 p3 g9 M7 K; B- r1 o
/*minus 操作，集合减,它将压缩两个集合减后的重复记录, 且对数据排序*/
8 c2 j# c3 D8 ?/ o3 z9 N5 q6 t. Tselect col1,col2,col3 from table1_name minus select col1,col2,col3 from 1 k' C3 e8 t9 V) x2 Y: H
table2_name; " R( f4 E7 H/ U# F, r6 ?5 U5 V
1 N2 f, O2 C- `
/*EXTRACT 抽取时间函数. 此例是抽取当前日期中的年*/ 7 m2 L5 b- g- W5 W
select EXTRACT(YEAR FROM SYSDATE) from dual; ' {! S/ k0 {& X
/*EXTRACT 抽取时间函数. 此例是抽取当前日期中的月*/
9 r& \, c+ y4 k9 d/ Sselect EXTRACT(MONTH FROM SYSDATE) from dual;
8 i& Z8 V" K# h& W: X; i* T , O2 Z) c! F- @7 O2 t- i) ]! v
########################## 增强的 group by 子句 ######################### $ D3 x; G8 c! W( b# C
+ U# U( w3 l2 Y$ t! [- L! |- l
select [column,] group_function(column)... ) h- {0 y! b5 b
from table
. Z7 t" f# t7 L2 z$ q[WHERE condition] : b& d8 R/ ~' I+ D
[GROUP BY [ROLLUP] group_by_expression] ) X: I$ i3 m0 i- e9 V! X( ^* k
[HAVING having_expression];
8 H' h9 D- D$ i5 c" w- w" ]5 J! g, } d[ORDER BY column]; -------ROLLUP操作字，对group by子句的各字段从右到左进行再聚合
/ P1 R/ u6 T/ G2 x9 n& }+ t- Z7 d
- `) u5 Y: G" k; X% Q% Xexample:
/ p1 X. Y) S- n+ d# W1 G/*其结果看起来象对col1做小计*/ 3 F$ |: a& \- y; h
select col1,col2,sum(col3) from table group by rollup(col1,col2); # ]/ @( e" k% W! l# H3 t& `
/*复合rollup表达式*/
2 m' T ^$ I ]; bselect col1,col2,sum(col3) from table group by rollup((col1,col2));
6 [7 S6 P2 _; k1 u3 z5 V$ Q2 q % |4 y1 m4 _0 @
select [column,] group_function(column)... * `+ x! [1 P" o; z, I$ x
from table
3 `- Z8 b, H$ Z! I/ y[WHERE condition] 4 Q( I; d- R4 v. B
[GROUP BY [CUBE] group_by_expression] - v" e/ f, E' F
[HAVING having_expression]; 6 D/ w& L+ }; n
[ORDER BY column]; -------CUBE操作字，除完成ROLLUP的功能外，再对ROLLUP后的结果集从右到左再聚合
0 r/ l, c1 }' g) v
3 W, \" G# D: c8 U9 `: v. W* Cexample:
& J5 C/ M1 i ?! j! Q, \/*其结果看起来象对col1做小计后，再对col2做小计，最后算总计*/ , R# a1 f5 s' M( m( K: C/ W7 G
select col1,col2,sum(col3) from table group by cube(col1,col2); ! E0 N8 B% Z* N& A" G( S7 j
/*复合rollup表达式*/
) F% R% z; _1 F _. O9 C/ Yselect col1,col2,sum(col3) from table group by cube((col1,col2)); ) C! N( b' ?% D
/*混合rollup,cube表达式*/
& M# m4 [6 ?: k2 _select col1,col2,col3,sum(col4) from table group by " p4 h1 n/ `4 j/ |2 m5 [% b
col1,rollup(col2),cube(col3);
& c/ _! t$ G! H& g. E4 V # k8 c' m x0 c+ q/ M& |2 d5 ^
/*GROUPING(expr)函数，查看select语句种以何字段聚合，其取值为0或1*/ 1 P0 X* b% [. h% F
select [column,] group_function(column)...,GROUPING(expr) S1 {; B0 t8 k" C( ?& z! M
from table / G- Y; A. s' w' s9 l. y h
[WHERE condition]
9 k( J$ A2 L* F; c' T1 l, @* V; u ^[GROUP BY [ROLLUP] group_by_expression]
( {( O; q W7 K! m1 C[HAVING having_expression];
: G" N% @" z8 l" x( h1 ]# m[ORDER BY column]; / v6 ^2 D1 s; O4 L3 w+ y# C5 N
' R- J9 w$ v" L! w1 A5 o" a
example: - }+ ]: \; x7 s$ ~
select col1,col2,sum(col3),grouping(col1),grouping(col2) from table group by
. [# J, c* M: h- vcube(col1,col2); $ b: t# q' @" l+ C7 L6 z
7 z" x- `; a- Q/*grouping sets操作，对group by结果集先对col1求和，再对col2求和，最后将其结果集并在一起*/ 4 ]% k9 F" _; z7 j; w) Y7 v7 X
select col1,col2,sum(col3) from table group by grouping sets((col1),(col2));
