第四章：索引 <P>    1.creating function-based indexes <P>    sql> create index summit.item_quantity on summit.item(quantity-quantity_shipped); <P>    2.create a B-tree index <P>    sql> create [unique] index index_name on table_name(column,.. asc/desc) tablespace <P>    sql> tablespace_name [pctfree integer] [initrans integer] [maxtrans integer] <P>    sql> [logging | nologging] [nosort] storage(initial 200k next 200k pctincrease 0 <P>    sql> maxextents 50); <P>    3.pctfree(index)=(maximum number of rows-initial number of rows)*100/maximum number of rows <P>    4.creating reverse key indexes <P>    sql> create unique index xay_id on xay(a) reverse pctfree 30 storage(initial 200k <P>    sql> next 200k pctincrease 0 maxextents 50) tablespace indx; <P>    5.create bitmap index <P>    sql> create bitmap index xay_id on xay(a) pctfree 30 storage( initial 200k next 200k <P>    sql> pctincrease 0 maxextents 50) tablespace indx; <P>    6.change storage parameter of index <P>    sql> alter index xay_id storage (next 400k maxextents 100); <P>    7.allocating index space <P>    sql> alter index xay_id allocate extent(size 200k datafile 'c:/oracle/index.dbf'); <P>    8.alter index xay_id deallocate unused;</P>

第三章：表 <P>    1.create a table <P>    sql> create table table_name (column datatype,column datatype]....) <P>    sql> tablespace tablespace_name [pctfree integer] [pctused integer] <P>    sql> [initrans integer] [maxtrans integer] <P>    sql> storage(initial 200k next 200k pctincrease 0 maxextents 50) <P>    sql> [logging|nologging] [cache|nocache] <P>    2.copy an existing table <P>    sql> create table table_name [logging|nologging] as subquery <P>    3.create temporary table <P>    sql> create global temporary table xay_temp as select * from xay; <P>    on commit preserve rows/on commit delete rows <P>    4.pctfree = (average row size - initial row size) *100 /average row size <P>    pctused = 100-pctfree- (average row size*100/available data space) <P>    5.change storage and block utilization parameter <P>    sql> alter table table_name pctfree=30 pctused=50 storage(next 500k <P>    sql> minextents 2 maxextents 100); <P>    6.manually allocating extents <P>    sql> alter table table_name allocate extent(size 500k datafile 'c:/oracle/data.dbf'); <P>    7.move tablespace <P>    sql> alter table employee move tablespace users; <P>    8.deallocate of unused space <P>    sql> alter table table_name deallocate unused [keep integer] <P>    9.truncate a table <P>    sql> truncate table table_name; <P>    10.drop a table <P>    sql> drop table table_name [cascade constraints]; <P>    11.drop a column <P>    sql> alter table table_name drop column comments cascade constraints checkpoint 1000; <P>    alter table table_name drop columns continue; <P>    12.mark a column as unused <P>    sql> alter table table_name set unused column comments cascade constraints; <P>    alter table table_name drop unused columns checkpoint 1000; <P>    alter table orders drop columns continue checkpoint 1000 <P>    data_dictionary : dba_unused_col_tabs </P>

第二章：表空间管理 <P>    1.create tablespaces <P>    sql> create tablespace tablespace_name datafile 'c:\oracle\oradata\file1.dbf' size 100m, <P>    sql> 'c:\oracle\oradata\file2.dbf' size 100m minimum extent 550k [logging/nologging] <P>    sql> default storage (initial 500k next 500k maxextents 500 pctinccease 0) <P>    sql> [online/offline] [permanent/temporary] [extent_management_clause] <P>    2.locally managed tablespace <P>    sql> create tablespace user_data datafile 'c:\oracle\oradata\user_data01.dbf' <P>    sql> size 500m extent management local uniform size 10m; <P>    3.temporary tablespace <P>    sql> create temporary tablespace temp tempfile 'c:\oracle\oradata\temp01.dbf' <P>    sql> size 500m extent management local uniform size 10m; <P>    4.change the storage setting <P>    sql> alter tablespace app_data minimum extent 2m; <P>    sql> alter tablespace app_data default storage(initial 2m next 2m maxextents 999); <P>    5.taking tablespace offline or online <P>    sql> alter tablespace app_data offline; <P>    sql> alter tablespace app_data online; <P>    6.read_only tablespace <P>    sql> alter tablespace app_data read only|write; <P>    7.droping tablespace <P>    sql> drop tablespace app_data including contents; <P>    8.enableing automatic extension of data files <P>    sql> alter tablespace app_data add datafile 'c:\oracle\oradata\app_data01.dbf'size 200m <P>    sql> autoextend on next 10m maxsize 500m; <P>    9.change the size fo data files manually <P>    sql> alter database datafile 'c:\oracle\oradata\app_data.dbf'resize 200m; <P>    10.Moving data files: alter tablespace <P>    sql> alter tablespace app_data rename datafile 'c:\oracle\oradata\app_data.dbf' <P>    sql> to 'c:\oracle\app_data.dbf'; <P>    11.moving data files:alter database <P>    sql> alter database rename file 'c:\oracle\oradata\app_data.dbf' <P>    sql> to 'c:\oracle\app_data.dbf';  </P>