Oracle常用命令大全集(2)

第三章：表

    1.create a table

    sql> create table table_name (column datatype,column datatype]....)

    sql> tablespace tablespace_name [pctfree integer] [pctused integer]

    sql> [initrans integer] [maxtrans integer]

    sql> storage(initial 200k next 200k pctincrease 0 maxextents 50)

    sql> [logging|nologging] [cache|nocache]

    2.copy an existing table

    sql> create table table_name [logging|nologging] as subquery

    3.create temporary table

    sql> create global temporary table xay_temp as select * from xay;

    on commit preserve rows/on commit delete rows

    4.pctfree = (average row size - initial row size) *100 /average row size

    pctused = 100-pctfree- (average row size*100/available data space)

    5.change storage and block utilization parameter

    sql> alter table table_name pctfree=30 pctused=50 storage(next 500k

    sql> minextents 2 maxextents 100);

    6.manually allocating extents

    sql> alter table table_name allocate extent(size 500k datafile 'c:/oracle/data.dbf');

    7.move tablespace

    sql> alter table employee move tablespace users;

    8.deallocate of unused space

    sql> alter table table_name deallocate unused [keep integer]

    9.truncate a table

    sql> truncate table table_name;

    10.drop a table

    sql> drop table table_name [cascade constraints];

    11.drop a column

    sql> alter table table_name drop column comments cascade constraints checkpoint 1000;

    alter table table_name drop columns continue;

    12.mark a column as unused

    sql> alter table table_name set unused column comments cascade constraints;

    alter table table_name drop unused columns checkpoint 1000;

    alter table orders drop columns continue checkpoint 1000

    data_dictionary : dba_unused_col_tabs

    第四章：索引

上一页  [1] [2] [3] [4] [5] [6] [7] 下一页

    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

    第七章：reorganizing data

    1.using expoty

    $exp scott/tiger tables(dept,emp) file=c:\emp.dmp log=exp.log compress=n direct=y

    2.using import

    $imp scott/tiger tables(dept,emp) file=emp.dmp log=imp.log ignore=y

上一页  [1] [2] [3] [4] [5] [6] [7] 下一页

    3.transporting a tablespace

    sql>alter tablespace sales_ts read only;

    $exp sys/.. file=xay.dmp transport_tablespace=y tablespace=sales_ts

    triggers=n constraints=n

    $copy datafile

    $imp sys/.. file=xay.dmp transport_tablespace=y datafiles=(/disk1/sles01.dbf,/disk2

    /sles02.dbf)

    sql> alter tablespace sales_ts read write;

    4.checking transport set

    sql> DBMS_tts.transport_set_check(ts_list =>'sales_ts' ..,incl_constraints=>true);

    在表transport_set_violations 中查看

    sql> dbms_tts.isselfcontained 为true 是， 表示自包含