第一章日志管理
forcing log switches
sql> alter system switch logfile;
forcing checkpoints
sql> alter system checkpoint;
adding online redo log groups
sql> alter database add logfile [group ]
sql> (/disk/logardo/disk/logbrdo) size m;
adding online redo log members
sql> alter database add logfile member
sql> /disk/logbrdo to group
sql> /disk/logbrdo to group ;
changes the name of the online redo logfile
sql> alter database rename file c:/oracle/oradata/oradb/redolog
sql> to c:/oracle/oradata/redolog;
drop online redo log groups
sql> alter database drop logfile group ;
drop online redo log members
sql> alter database drop logfile member c:/oracle/oradata/redolog;
clearing online redo log files
sql> alter database clear [unarchived] logfile c:/oracle/logardo;
using logminer analyzing redo logfiles
a in the initora specify utl_file_dir =
b sql> execute dbms_logmnr_dbuild(oradborac:\oracle\oradb\log);
c sql> execute dbms_logmnr_add_logfile(c:\oracle\oradata\oradb\redolog
sql> dbms_logmnrnew);
d sql> execute dbms_logmnradd_logfile(c:\oracle\oradata\oradb\redolog
sql> dbms_logmnraddfile);
e sql> execute dbms_logmnrstart_logmnr(dictfilename=>c:\oracle\oradb\log\oradbora);
f sql> select * from v$logmnr_contents(v$logmnr_dictionary
v$logmnr_parameters sql> v$logmnr_logs);
g sql> execute dbms_logmnrend_logmnr;
第二章表空间管理
create tablespaces
sql> create tablespace tablespace_name datafile c:\oracle\oradata\filedbf
size m
sql> c:\oracle\oradata\filedbf size m minimum extent k [logging/nologging]
sql> default storage (initial k next k maxextents pctinccease )
sql> [online/offline] [permanent/temporary] [extent_management_clause]
locally managed tablespace
sql> create tablespace user_data datafile c:\oracle\oradata\user_datadbf
sql> size m extent management local uniform size m;
temporary tablespace
sql> create temporary tablespace temp tempfile c:\oracle\oradata\tempdbf
sql> size m extent management local uniform size m;
change the storage setting
sql> alter tablespace app_data minimum extent m;
sql> alter tablespace app_data default storage
(initial m next m maxextents );
taking tablespace offline or online
sql> alter tablespace app_data offline;
sql> alter tablespace app_data online;
read_only tablespace
sql> alter tablespace app_data read only|write;
droping tablespace
sql> drop tablespace app_data including contents;
enableing automatic extension of data files
sql> alter tablespace app_data add datafile
c:\oracle\oradata\app_datadbf size m
sql> autoextend on next m maxsize m;
change the size fo data files manually
sql> alter database datafile c:\oracle\oradata\app_datadbf resize m;
Moving data files: alter tablespace
sql> alter tablespace app_data rename datafile c:\oracle\oradata\app_datadbf
sql> to c:\oracle\app_datadbf;
moving data files:alter database
sql> alter database rename file c:\oracle\oradata\app_datadbf
sql> to c:\oracle\app_datadbf;
第三章表
create a table
sql> create table table_name (column datatypecolumn datatype])
sql> tablespace tablespace_name [pctfree integer] [pctused integer]
sql> [initrans integer] [maxtrans integer]
sql> storage(initial k next k pctincrease maxextents )
sql> [logging|nologging] [cache|nocache]
py an existing table
sql> create table table_name [logging|nologging] as subquery
create temporary table
sql> create global temporary table xay_temp as select * from xay;
on commit preserve rows/on commit delete rows
pctfree = (average row size initial row size) * /average row size
pctused = pctfree (average row size*/available data space)
change storage and block utilization parameter
sql> alter table table_name pctfree= pctused= storage(next k
sql> minextents maxextents );
manually allocating extents
sql> alter table table_name allocate extent(size k datafile c:/oracle/datadbf);
move tablespace
sql> alter table employee move tablespace users;
deallocate of unused space
sql> alter table table_name deallocate unused [keep integer]
truncate a table
sql> truncate table table_name;
drop a table
sql> drop table table_name [cascade constraints];
drop a column
sql> alter table table_name drop column comments cascade
constraints checkpoint ;
alter table table_name drop columns continue;
mark a column as unused
sql> alter table table_name set unused column comments cascade constraints;
alter table table_name drop unused columns checkpoint ;
alter table orders drop columns continue checkpoint
data_dictionary : dba_unused_col_tabs
第四章索引
creating functionbased indexes
sql> create index em_quantity on em
(quantityquantity_shipped);
create a Btree 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 k next k pctincrease
sql> maxextents );
pctfree(index)=(maximum number of rowsinitial number of rows)*/maximum number of rows
creating reverse key indexes
sql> create unique index xay_id on xay(a) reverse pctfree storage
(initial k sql> next k pctincrease maxextents ) tablespace indx;
create bitmap index
sql> create bitmap index xay_id on xay(a) pctfree storage( initial k next k
sql> pctincrease maxextents ) tablespace indx;
change storage parameter of index
sql> alter index xay_id storage (next k maxextents );
allocating index space
sql> alter index xay_id allocate extent
(size k datafile c:/oracle/indexdbf);
alter index xay_id deallocate unused;
第五章约束
define constraints as immediate or deferred
sql> alter session set constraint[s] = immediate/deferred/default;
set constraint[s] constraint_name/all immediate/deferred;
sql> drop table table_name cascade constraints
sql> drop tablespace tablespace_name including contents cascade constraints
define constraints while create a table
sql> create table xay(id number() constraint xay_id primary key deferrable
sql> using index storage(initial k next k) tablespace indx);
primary key/unique/references table(column)/check
enable constraints
sql> alter table xay enable novalidate constraint xay_id;
enable constraints
sql> alter table xay ena
