oracle常用命令大汇总(二)

第四章：索引

1.creating function-based indexes

sql> create index summit.item_quantity on summit.item(quantity-quantity_shipped);

2.create a b-tree index

sql> create [unique] index index_name on table_name(column,.. asc/desc) tablespace

sql> tablespace_name [pctfree integer] [initrans integer] [maxtrans integer]

sql> [logging | nologging] [nosort] storage(initial 200k next 200k pctincrease 0

sql> maxextents 50);

3.pctfree(index)=(maximum number of rows-initial number of rows)*100/maximum number of rows

4.creating reverse key indexes

sql> create unique index xay_id on xay(a) reverse pctfree 30 storage(initial 200k

sql> next 200k pctincrease 0 maxextents 50) tablespace indx;

5.create bitmap index

sql> create bitmap index xay_id on xay(a) pctfree 30 storage( initial 200k next 200k

sql> pctincrease 0 maxextents 50) tablespace indx;

6.change storage parameter of index

sql> alter index xay_id storage (next 400k maxextents 100);

7.allocating index space

sql> alter index xay_id allocate extent(size 200k datafile ''c:/oracle/index.dbf'');

8.alter index xay_id deallocate unused;


第五章：约束

1.define constraints as immediate or deferred

sql> alter session set constraint[s] = immediate/deferred/default;

set constraint[s] constraint_name/all immediate/deferred;

2. sql> drop table table_name cascade constraints

sql> drop tablespace tablespace_name including contents cascade constraints

3. define constraints while create a table

sql> create table xay(id number(7) constraint xay_id primary key deferrable

sql> using index storage(initial 100k next 100k) tablespace indx);

primary key/unique/references table(column)/check

4.enable constraints

sql> alter table xay enable novalidate constraint xay_id;

5.enable constraints

sql> alter table xay enable validate constraint xay_id;



第六章：load数据

1.loading data using direct_load insert

sql> insert /*+append */ into emp nologging

sql> select * from emp_old;

2.parallel direct-load insert

sql> alter session enable parallel dml;

sql> insert /*+parallel(emp,2) */ into emp nologging

sql> select * from emp_old;

3.using sql*loader

sql> sqlldr scott/tiger \

sql> control = ulcase6.ctl \

sql> log = ulcase6.log direct=true