第三章:表
51Testing软件测试网sg7O uOU` 1.create a table51Testing软件测试网d(@ {]7L8|``"h
~6JiO ~I%{0 sql> create table table_name (column datatype,column datatype]....)51Testing软件测试网3Ya{B&B3`
\s|p#v5E6].o![0 sql> tablespace tablespace_name [pctfree integer] [pctused integer]51Testing软件测试网 W]3Je7`2?o,T
[1Fc
L:n;C{,Y0 sql> [initrans integer] [maxtrans integer]51Testing软件测试网%Rf `M;f3au B
${hWp1T$N^-jl0 sql> storage(initial 200k next 200k pctincrease 0 maxextents 50)51Testing软件测试网 s0Ew&T5y-fET6[
51Testing软件测试网1Kg1_)y8uN`!W sql> [logging|nologging] [cache|nocache]
/M%Jd
m-~"a&Z2v5t0v0
51Testing软件测试网 G]-{\/O1t},j 2.copy an existing table51Testing软件测试网+ws)|7s9L `&cL#_
*~7K XHFG+mz*z0 sql> create table table_name [logging|nologging] as subquery
W[4_0c$j;Il~!f0
51Testing软件测试网 Dt;_4}*H6? 3.create temporary table51Testing软件测试网+gvI lC4jg&V5BS
51Testing软件测试网}&N&SC5I `7KP sql> create global temporary table xay_temp as select * from xay;51Testing软件测试网(lFI%_]*JS3vH
51Testing软件测试网 hqf-U&uI"DFH&A_8Z on commit preserve rows/on commit delete rows
+pJ#Y~ ~[0
G/^6OH:i7_6Er
y#K#j$z0 4.pctfree = (average row size - initial row size) *100 /average row size51Testing软件测试网 {[P*?W"j
6j uCz!}0 pctused = 100-pctfree- (average row size*100/available data space)
.m#_'nT N*T0
51Testing软件测试网7V*[F.L vg{ 5.change storage and block utilization parameter
:?Nzx7D6vn0
51Testing软件测试网s,B-g;V.PQvM sql> alter table table_name pctfree=30 pctused=50 storage(next 500k
I+A$UwQl.j`ZA0
51Testing软件测试网x`
G{c2]Vf"oh sql> minextents 2 maxextents 100);
-rH-A3~0Ul#qA,I%P0
RfOi7qd2b0 6.manually allocating extents51Testing软件测试网UR AHMe
51Testing软件测试网4h TGghf(Sv
\2`$J sql> alter table table_name allocate extent(size 500k datafile 'c:/oracle/data.dbf');
@I2KNxtJ7mD0
eSf
}0cKQ0 7.move tablespace51Testing软件测试网A&t%b1~bJ GG
%K0n)}o)y
_6b8_7s0 sql> alter table employee move tablespace users;51Testing软件测试网#g;NJ!q%w'xEK2`
51Testing软件测试网TC+EjY| L.j 8.deallocate of unused space
;wz$dV[cT-Y0
51Testing软件测试网up~h-nc? sql> alter table table_name deallocate unused [keep integer]
/sWW6xnN&G+i0
51Testing软件测试网 ['N3xU0L
rCA 9.truncate a table
5l$L^:Kp#?4`W0
p-Gfe$o6t7s\J0 sql> truncate table table_name;
/c,m8w2wdA0
2C.oFpj
b0 10.drop a table51Testing软件测试网P{RH8Z"J1_s
51Testing软件测试网i'F\}!ZK6t?oDf sql> drop table table_name [cascade constraints];51Testing软件测试网l"^L!pQ9Q.{v`)FA
51Testing软件测试网{ u0Y @3gt)x9B 11.drop a column51Testing软件测试网A
l(h(E:^M l'e
51Testing软件测试网&?
EK
j;YI4I+G sql> alter table table_name drop column comments cascade constraints checkpoint 1000;51Testing软件测试网+|j(CclZ
51Testing软件测试网,C/Ofcp/H'T] alter table table_name drop columns continue;51Testing软件测试网$h$qt&R6~
A[(^
51Testing软件测试网F9c7q4Qxy*j&| 12.mark a column as unused51Testing软件测试网.[i0k
d*kSW
51Testing软件测试网*W
CZn{ sql> alter table table_name set unused column comments cascade constraints;
*guI+@(?V0
C9]gZ f8j2h8x~0 alter table table_name drop unused columns checkpoint 1000;
&?
f4dD J&`o2[L4r0
8IKW#nI9XQ.|
RG0 alter table orders drop columns continue checkpoint 100051Testing软件测试网-W(Y
F-`%r
P
51Testing软件测试网k#Ui5\j(z/S4Y,O data_dictionary : dba_unused_col_tabs
_"s e
g`#b4dr(o
w0