Oracle常用命令Word文档格式.docx

1、/oracle/oradata/oradb/redo01.log to /oracle/oradata/redo01.log;6.drop online redo log groups alter database drop logfile group 3;7.drop online redo log members alter database drop logfile member 8.clearing online redo log files alter database clear unarchived logfile /oracle/log2a.rdo9.using logmine

2、r analyzing redo logfiles a. in the init.ora specify utl_file_dir = b. sql execute dbms_logmnr_d.build（oradb.oraoracleoradblog）;c. sql execute dbms_logmnr_add_logfile（oracleoradataoradbredo01.log, dbms_logmnr.new）;d. sql execute dbms_logmnr.add_logfile（oracleoradataoradbredo02.log dbms_logmnr.addfil

3、e）;e. sql execute dbms_logmnr.start_logmnr（dictfilename=oracleoradblogoradb.oraf. sql select * from v$logmnr_contents（v$logmnr_dictionary,v$logmnr_parameters v$logmnr_logs）;g. sql execute dbms_logmnr.end_logmnr;第二章：表空间管理 1.create tablespaces create tablespace tablespace_name datafile oracleoradatafi

4、le1.dbf size 100m, oracleoradatafile2.dbf size 100m minimum extent 550k logging/nologging default storage （initial 500k next 500k maxextents 500 pctinccease 0） online/offline permanent/temporary extent_management_clause 2.locally managed tablespace create tablespace user_data datafile oracleoradatau

5、ser_data01.dbf size 500m extent management local uniform size 10m;3.temporary tablespace create temporary tablespace temp tempfile oracleoradatatemp01.dbf4.change the storage setting alter tablespace app_data minimum extent 2m; alter tablespace app_data default storage（initial 2m next 2m maxextents

6、999）;5.taking tablespace offline or online alter tablespace app_data offline; alter tablespace app_data online;6.read_only tablespace alter tablespace app_data read only|write;7.droping tablespace drop tablespace app_data including contents;8.enableing automatic extension of data files alter tablesp

7、ace app_data add datafile oracleoradataapp_data01.dbf size 200m autoextend on next 10m maxsize 500m;9.change the size fo data files manually alter database datafile oracleoradataapp_data.dbf resize 200m;10.Moving data files: alter tablespace alter tablespace app_data rename datafile oracleapp_data.d

8、bf11.moving data files:alter database 第三章：表 1.create a table create table table_name （column datatype,column datatype.） tablespace tablespace_name pctfree integer pctused integer initrans integer maxtrans integer storage（initial 200k next 200k pctincrease 0 maxextents 50） logging|nologging cache|noc

9、ache 2.copy an existing table create table table_name logging|nologging as subquery 3.create temporary table 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 = 10

10、0-pctfree- （average row size*100/available data space） 5.change storage and block utilization parameter alter table table_name pctfree=30 pctused=50 storage（next 500k minextents 2 maxextents 100）;6.manually allocating extents alter table table_name allocate extent（size 500k datafile /oracle/data.dbf

11、7.move tablespace alter table employee move tablespace users;8.deallocate of unused space alter table table_name deallocate unused keep integer 9.truncate a table truncate table table_name;10.drop a table drop table table_name cascade constraints;11.drop a column alter table table_name drop column c

12、omments cascade constraints checkpoint 1000;alter table table_name drop columns continue;12.mark a column as unused 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 100

13、0 data_dictionary : dba_unused_col_tabs 第四章：索引 1.creating function-based indexes create index summit.item_quantity on summit.item（quantity-quantity_shipped）;2.create a B-tree index create unique index index_name on table_name（column,. asc/desc） tablespace tablespace_name pctfree integer initrans int

14、eger maxtrans integer logging | nologging nosort storage（initial 200k next 200k pctincrease 0 maxextents 50）;3.pctfree（index）=（maximum number of rows-initial number of rows）*100/maximum number of rows 4.creating reverse key indexes create unique index xay_id on xay（a） reverse pctfree 30 storage（init

15、ial 200k next 200k pctincrease 0 maxextents 50） tablespace indx;5.create bitmap index create bitmap index xay_id on xay（a） pctfree 30 storage（ initial 200k next 200k pctincrease 0 maxextents 50） tablespace indx;6.change storage parameter of index alter index xay_id storage （next 400k maxextents 100）

16、;7.allocating index space alter index xay_id allocate extent（size 200k datafile /oracle/index.dbf8.alter index xay_id deallocate unused;第五章：约束 1.define constraints as immediate or deferred alter session set constraints = immediate/deferred/default;set constraints constraint_name/all immediate/deferr

17、ed;2. sql drop table table_name cascade constraints drop tablespace tablespace_name including contents cascade constraints 3. define constraints while create a table create table xay（id number（7） constraint xay_id primary key deferrable using index storage（initial 100k next 100k） tablespace indx）;pr

18、imary key/unique/references table（column）/check 4.enable constraints alter table xay enable novalidate constraint xay_id;5.enable constraints alter table xay enable validate constraint xay_id;第六章：LOAD数据 1.loading data using direct_load insert insert /*+append */ into emp nologging select * from emp_

19、old;2.parallel direct-load insert alter session enable parallel dml; insert /*+parallel（emp,2） */ into emp nologging 3.using sql*loader sqlldr scott/tiger control = ulcase6.ctl log = ulcase6.log direct=true 第七章：reorganizing data 1.using expoty $exp scott/tiger tables（dept,emp） file=c:emp.dmp log=exp

20、.log compress=n direct=y 2.using import $imp scott/tiger tables（dept,emp） file=emp.dmp log=imp.log ignore=y 3.transporting a tablespace 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.d

21、mp transport_tablespace=y datafiles=（/disk1/sles01.dbf,/disk2 /sles02.dbf） alter tablespace sales_ts read write;4.checking transport set DBMS_tts.transport_set_check（ts_list =sales_ts .,incl_constraints=true）;在表transport_set_violations 中查看 dbms_tts.isselfcontained 为true 是， 表示自包含 第八章： managing passwo

22、rd security and resources 1.controlling account lock and password alter user juncky identified by oracle account unlock;2.user_provided password function function_name（userid in varchar2（30）,password in varchar2（30）, old_password in varchar2（30） return boolean 3.create a profile : password setting c

23、reate profile grace_5 limit failed_login_attempts 3 password_lock_time unlimited password_life_time 30 password_reuse_time 30 password_verify_function verify_function password_grace_time 5;4.altering a profile alter profile default failed_login_attempts 3 password_life_time 60 password_grace_time 10

24、;5.drop a profile drop profile grace_5 cascade;6.create a profile : resource limit create profile developer_prof limit sessions_per_user 2 cpu_per_session 10000 idle_time 60 connect_time 480;7. view = resource_cost : alter resource cost dba_Users,dba_profiles 8. enable resource limits alter system s

25、et resource_limit=true;第九章：Managing users 1.create a user: database authentication create user juncky identified by oracle default tablespace users temporary tablespace temp quota 10m on data password expire account lock|unlock profile profilename|default;2.change user quota on tablespace alter user

26、 juncky quota 0 on users;3.drop a user drop user juncky cascade;4. monitor user view: dba_users , dba_ts_quotas第十章：managing privileges 1.system privileges: view = system_privilege_map ,dba_sys_privs,session_privs 2.grant system privilege grant create session,create table to managers; grant create se

27、ssion to scott with admin option;with admin option can grant or revoke privilege from any user or role;3.sysdba and sysoper privileges:sysoper: startup,shutdown,alter database open|mount,alter database backup controlfile, alter tablespace begin/end backup,recover database alter database archivelog,restricted session sysdba: sysoper privileges with ad