oracle siplugincol表,ORACLE 经常使用的命令工具-第三章:表

第三章:表

T-P*sd:@1t0　　1.create a table51Testing软件测试网

i6}2pdjT1Wf

51Testing软件测试网,UPta6i(W/L(w

sql> create table table_name (column datatype,column datatype]....)%f;B)R%B)Wr.}.[x!TL0

RSGuyLc?8G(?0　　sql> tablespace tablespace_name [pctfree integer] [pctused integer]51Testing软件测试网-b&guQ3h

{V"B0vHT9H

d'G0　　sql> [initrans integer] [maxtrans integer]V@B i{F(pA!a0

51Testing软件测试网i'Kn)BD``d

sql> storage(initial 200k next 200k pctincrease 0 maxextents 50)!l,rf4c6['cd3^0

5C^3kf)Y{!kr0　　sql> [logging|nologging] [cache|nocache]51Testing软件测试网9Dlr;[z0TW3t

LU

qFt*W0　　2.copy an existing table51Testing软件测试网*t4B[

R

wr!hV O

p\$\/gC0　　sql> create table table_name [logging|nologging] as subquery51Testing软件测试网_r``O\\

51Testing软件测试网jD?7e7cY/E

3.create temporary table51Testing软件测试网{(L,KV9|1^3}b_

51Testing软件测试网l:e L1b%w$D @,T

sql> create global temporary table xay_temp as select * from xay;51Testing软件测试网Yz9A(Hfl/ji];@nI

51Testing软件测试网6\n3p2uZ7zD`

on commit preserve rows/on commit delete rows51Testing软件测试网ySJc|vP:Tl8M

cB&y2~h*?Y R0　　4.pctfree = (average row size - initial row size) *100 /average row size51Testing软件测试网:~}qR O4[?f

4dD.i4JLK0　　pctused = 100-pctfree- (average row size*100/available data space)51Testing软件测试网eWHT|pg5eT

X:wOV/HIT0　　5.change storage and block utilization parameter51Testing软件测试网n|]#j2t2Ky?Jp-|

"L3|w#Wxx9Z\R:R0　　sql> alter table table_name pctfree=30 pctused=50 storage(next 500k+U&zJm)n"dA8XZ(ekz?0

2H0~LA'UoD3V0　　sql> minextents 2 maxextents 100);*V _-\q5{"G v0

"i s(ls;eXf*r0　　6.manually allocating extents51Testing软件测试网;oFPnb_)J

51Testing软件测试网T'b^j6Q

sql> alter table table_name allocate extent(size 500k datafile 'c:/oracle/data.dbf');\u~ R%A0

51Testing软件测试网+c;j,Yo9w2q$r.?"yy:pR

7.move tablespaceo8ep7_UIGl0

51Testing软件测试网%s?f

z%d4}8c

sql> alter table employee move tablespace users;\}h%_zTso0

51Testing软件测试网u;X@9`6XEE

8.deallocate of unused spaceRY(uF@L1h#N4g0

*w|@7PE IP f|#v0　　sql> alter table table_name deallocate unused [keep integer]+C*h#z.aA0FF0

51Testing软件测试网l Lb}e&V(z+R

9.truncate a table7LF-y$TGZd0

v'aBNI6O3x0　　sql> truncate table table_name;51Testing软件测试网9]Co+sR

51Testing软件测试网(L(eOpFF%[

10.drop a table)E,m0d?,t$a4t#}0

51Testing软件测试网5AX)d!T;c0ab

sql> drop table table_name [cascade constraints];.Q*Sv/^+k0

51Testing软件测试网-uyCyE;Im(H

11.drop a columnwG4_h%]jb0

51Testing软件测试网0V.W4B4~{ks

sql> alter table table_name drop column comments cascade constraints checkpoint 1000;a6R(Kx}0

51Testing软件测试网8? X?'T?D B

alter table table_name drop columns continue;8QY

J~!v"z0

51Testing软件测试网)GM@C]aW

12.mark a column as unused\]*u,dubi$h0

x,luc.Jc0　　sql> alter table table_name set unused column comments cascade constraints;51Testing软件测试网c)gdB:kk&Z

6Y(A_ U)Ix't6E"f0　　alter table table_name drop unused columns checkpoint 1000;51Testing软件测试网8gb%U)YU4ui0s

51Testing软件测试网9O^oH\%t2L

alter table orders drop columns continue checkpoint 1000]hW/XgL@0

51Testing软件测试网?nB{

EF"`

data_dictionary : dba_unused_col_tabs51Testing软件测试网oU.UU!H"HZq;u