《网友ORACLE学习笔记》由会员分享，可在线阅读，更多相关《网友ORACLE学习笔记（24页珍藏版）》请在金锄头文库上搜索。
1、# 创建数据库look $Oracle_HOME/rdbms/admin/buildall.sql #create database db01 maxlogfiles 10 maxdatafiles 1024 maxinstances 2 logfileGROUP 1 (/u01/oradata/db01/log_01_db01.rdo) SIZE 15M,GROUP 2 (/u01/oradata/db01/log_02_db01.rdo) SIZE 15M,GROUP 3 (/u01/oradata/db01/log_03_db01.rdo) SIZE 15M,datafile u01/o
2、radata/db01/system_01_db01.dbf) SIZE 100M,undo tablespace UNDOdatafile /u01/oradata/db01/undo_01_db01.dbf SIZE 40Mdefault temporary tablespace TEMPtempfile /u01/oradata/db01/temp_01_db01.dbf SIZE 20Mextent management local uniform size 128kcharacter set AL32UTE8national character set AL16UTF16set ti
3、me_zone=America/New_York;# 数据字典 #set wrap offselect * from v$dba_users;grant select on table_name to user/rule;select * from user_tables;select * from all_tables;select * from dba_tables;revoke dba from user_name;shutdown immediate startup nomount select * from v$instance;select * from v$sga;select
4、* from v$tablespace;alter session set nls_language=american;alter database mount;select * from v$database;alter database open;desc dictionary select * from dict;desc v$fixed_table;select * from v$fixed_table;set oracle_sid=foxconn select * from dba_objects;set serveroutput on execute dbms_output.put
5、_line(sfasd);# 控制文件 #select * from v$database;select * from v$tablespace;select * from v$logfile;select * from v$log;select * from v$backup;/*备份用户表空间*/alter tablespace users begin backup;select * from v$archived_log;select * from v$controlfile;alter system set control_files=$ORACLE_HOME/oradata/u01/
6、ctrl01.ctl, $ORACLE_HOME/oradata/u01/ctrl02.ctl scope=spfile;cp $ORACLE_HOME/oradata/u01/ctrl01.ctl $ORACLE_HOME/oradata/u01/ctrl02.ctlstartup pfile=./initSID.oraselect * from v$parameter where name like control% ;show parameter control;select * from v$controlfile_record_section;select * from v$temp
7、file;/*备份控制文件*/alter database backup controlfile to ./filepath/control.bak;/*备份控制文件，并将二进制控制文件变为了 asc 的文本文件*/ alter database backup controlfile to trace;# redo log # archive log list;alter system archive log start;-启动自动存档alter system switch logfile;-强行进行一次日志 switchalter system checkpoint;-强制进行一次 chec
8、kpointalter tablspace users begin backup;alter tablespace offline;/Checkpoint同步频率参数FAST_START_MTTR_TARGET,同步频率越高，系统恢复所需时间越短*/show parameter fast;show parameter log_checkpoint;/*加入一个日志组*/alter database add logfile group 3 (/$ORACLE_HOME/oracle/ora_log_file6.rdo size 10M);/*加入日志组的一个成员*/alter database
9、add logfile member /$ORACLE_HOME/oracle/ora_log_file6.rdo to group 3;/*删除日志组:当前日志组不能删；活动的日志组不能删；非归档的日志组不能删*/alter database drop logfile group 3;/*删除日志组中的某个成员，但每个组的最后一个成员不能被删除*/alter databse drop logfile member $ORACLE_HOME/oracle/ora_log_file6.rdo;/*清除在线日志*/alter database clear logfile $ORACLE_HOME/
10、oracle/ora_log_file6.rdo;alter database clear logfile group 3;/*清除非归档日志*/alter database clear unarchived logfile group 3;/*重命名日志文件*/alter database rename file $ORACLE_HOME/oracle/ora_log_file6.rdo to $ORACLE_HOME/oracle/ora_log_file6a.rdo;show parameter db_create;alter system set db_create_online_lo
11、g_dest_1=path_name;select * from v$log;select * from v$logfile;/*数据库归档模式到非归档模式的互换，要启动到mount状态下才能改变;startup mount;然后再打开 数据库.*/alter database noarchivelog/archivelog;achive log start;-启动自动归档alter system archive all;手工归档所有日志文件select * from v$archived_log;show parameter log_archive;# 分析日志文件 logmnr #1) 在
12、 init.ora 中 set utl_file_dir 参数2) 重新启动 oracle3) create 目录文件desc dbms_logmnr_d;dbms_logmnr_d.build;4) 加入日志文件 add/remove log filedhms_logmnr.add_logfiledbms_logmnr.removefile5) start logmnrdbms_logmnr.start_logmnr6) 分析出来的内容查询 v$logmnr_content -sqlredo/sqlundo实践：desc dbms_logmnr_d;/*对数据表做一些操作，为恢复操作做准备*
13、/update 表 set qty=10 where stor_id=6380;delete 表 where stor_id=7066;utl_file_dir 的路径execute dbms_logmnr_d.build(foxdict.ora,$ORACLE_HOME/oracle/admin/fox/cdump);execute dbms_logmnr.add_logfile($ORACLE_HOME/oracle/ora_log_file6.log,dbms_logmnr.newfile); executedbms_logmnr.start_logmnr(dictfilename=$O
14、RACLE_HOME/oracle/admin/fox/cdump/foxdict.ora);# tablespace #select * form v$tablespace;select * from v$datafile;/*表空间和数据文件的对应关系*/select t1.name,t2.name from v$tablespace t1,v$datafile t2 where t1.ts#=t2.ts#;alter tablespace users add datafile path size 10M;select * from dba_rollback_segs;/*限制用户在某表空
15、间的使用限额*/alter user user_name quota 10m on tablespace_name; create tablespace xxx datafile path_name/datafile_name size xxx extent management local/dictionary default storage(xxx);exmple: create tablespace userdata datafile $ORACLE_HOME/oradata/userdata01.dbf size 100M AUTOEXTEND ON NEXT 5M MAXSIZE 200M;create tablespace userdata datafile $ORACLE_HOME/or
