熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

oracle 中的常用命令重點展播

2013-11-13 16:09:16  來源: Oracle 

  第一章日志管理
  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
From:http://tw.wingwit.com/Article/program/Oracle/201311/17903.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.