网友ORACLE学习笔记

自己虽有两年开发经验，但不注意总结，今天借花献佛#########创建数据库----look$Oracle_HOME/rdbms/admin/buildall.sql#############createdatabasedb01maxlogfiles10maxdatafiles1024maxinstances2logfileGROUP1('/u01/oradata/db01/log_01_db01.rdo')SIZE15M,GROUP2('/u01/oradata/db01/log_02_db01.rdo')SIZE15M,GROUP3('/u01/oradata/db01/log_03_db01.rdo')SIZE15M,datafile'u01/oradata/db01/system_01_db01.dbf')SIZE100M,undotablespaceUNDOdatafile'/u01/oradata/db01/undo_01_db01.dbf'SIZE40MdefaulttemporarytablespaceTEMPtempfile'/u01/oradata/db01/temp_01_db01.dbf'SIZE20Mextentmanagementlocaluniformsize128kcharactersetAL32UTE8nationalcharactersetAL16UTF16settime_zone='America/New_York';###############数据字典##########setwrapoffselect*fromv$dba_users;grantselectontable_nametouser/rule;select*fromuser_tables;select*fromall_tables;select*fromdba_tables;revokedbafromuser_name;shutdownimmediatestartupnomountselect*fromv$instance;select*fromv$sga;select*fromv$tablespace;altersessionsetnls_language=american;alterdatabasemount;select*fromv$database;alterdatabaseopen;descdictionaryselect*fromdict;descv$fixed_table;select*fromv$fixed_table;setoracle_sid=foxconnselect*fromdba_objects;setserveroutputonexecutedbms_output.put_line('sfasd');#############控制文件###########select*fromv$database;select*fromv$tablespace;select*fromv$logfile;select*fromv$log;select*fromv$backup;/*备份用户表空间*/altertablespaceusersbeginbackup;select*fromv$archived_log;select*fromv$controlfile;altersystemsetcontrol_files='$ORACLE_HOME/oradata/u01/ctrl01.ctl','$ORACLE_HOME/oradata/u01/ctrl02.ctl'scope=spfile;cp$ORACLE_HOME/oradata/u01/ctrl01.ctl$ORACLE_HOME/oradata/u01/ctrl02.ctlstartuppfile='../initSID.ora'select*fromv$parameterwherenamelike'control%';showparametercontrol;select*fromv$controlfile_record_section;select*fromv$tempfile;/*备份控制文件*/alterdatabasebackupcontrolfileto'../filepath/control.bak';/*备份控制文件，并将二进制控制文件变为了asc的文本文件*/alterdatabasebackupcontrolfiletotrace;###############redolog##############archiveloglist;altersystemarchivelogstart;--启动自动存档altersystemswitchlogfile;--强行进行一次日志switchaltersystemcheckpoint;--强制进行一次checkpointaltertablspaceusersbeginbackup;altertablespaceoffline;/*checkpoint同步频率参数FAST_START_MTTR_TARGET,同步频率越高，系统恢复所需时间越短*/showparameterfast;showparameterlog_checkpoint;/*加入一个日志组*/alterdatabaseaddlogfilegroup3('/$ORACLE_HOME/oracle/ora_log_file6.rdo'size10M);/*加入日志组的一个成员*/alterdatabaseaddlogfilemember'/$ORACLE_HOME/oracle/ora_log_file6.rdo'togroup3;/*删除日志组:当前日志组不能删；活动的日志组不能删；非归档的日志组不能删*/alterdatabasedroplogfilegroup3;/*删除日志组中的某个成员，但每个组的最后一个成员不能被删除*/alterdatabsedroplogfilemember'$ORACLE_HOME/oracle/ora_log_file6.rdo';/*清除在线日志*/alterdatabaseclearlogfile'$ORACLE_HOME/oracle/ora_log_file6.rdo';alterdatabaseclearlogfilegroup3;/*清除非归档日志*/alterdatabaseclearunarchivedlogfilegroup3;/*重命名日志文件*/alterdatabaserenamefile'$ORACLE_HOME/oracle/ora_log_file6.rdo'to'$ORACLE_HOME/oracle/ora_log_file6a.rdo';showparameterdb_create;altersystemsetdb_create_online_log_dest_1='path_name';select*fromv$log;select*fromv$logfile;/*数据库归档模式到非归档模式的互换,要启动到mount状态下才能改变;startupmount;然后再打开数据库.*/alterdatabasenoarchivelog/archivelog;achivelogstart;---启动自动归档altersystemarchiveall;－－手工归档所有日志文件select*fromv$archived_log;showparameterlog_archive;######分析日志文件logmnr##############1)在init.ora中setutl_file_dir参数2)重新启动oracle3)create目录文件descdbms_logmnr_d;dbms_logmnr_d.build;4)加入日志文件add/removelogfiledhms_logmnr.add_logfiledbms_logmnr.removefile5)startlogmnrdbms_logmnr.start_logmnr6)分析出来的内容查询v$logmnr_content--sqlredo/sqlundo实践：descdbms_logmnr_d;/*对数据表做一些操作，为恢复操作做准备*/update表setqty=10wherestor_id=6380;delete表wherestor_id=7066;/***********************************/utl_file_dir的路径executedbms_logmnr_d.build('foxdict.ora','$ORACLE_HOME/oracle/admin/fox/cdump');executedbms_logmnr.add_logfile('$ORACLE_HOME/oracle/ora_log_file6.log',dbms_logmnr.newfile);executedbms_logmnr.start_logmnr(dictfilename='$ORACLE_HOME/oracle/admin/fox/cdump/foxdict.ora');#########tablespace##############select*formv$tablespace;select*fromv$datafile;/*表空间和数据文件的对应关系*/selectt1.name,t2.namefromv$tablespacet1,v$datafilet2wheret1.ts#=t2.ts#;altertablespaceusersadddatafile'path'size10M;select*fromdba_rollback_segs;/*限制用户在某表空间的使用限额*/alteruseruser_namequota10montablespace_name;createtablespacexxx[datafile'path_name/datafile_name'][sizexxx][extentmanagementlocal/dictionary][defaultstorage(xxx)];exmple:createtablespaceuserdatadatafile'$ORACLE_HOME/oradata/userdata01.dbf'size100MAUTOEXTENDONNEXT5MMAXSIZE200M;createtablespaceuserdatadatafile'$ORACLE_HOME/oradata/userdata01.dbf'size100Mextentmanagementdictionarydefaultstorage(initial100knext100kpctincrease10)offline;/*9i以后，oracle建议使用local管理，而不使用dictionary管理，因为local采用bitmap管理表空间，不会产生系统表空间的自愿争用;*/createtablespaceuserdatadatafile'$ORACLE_HOME/oradata/userdata01.dbf'size100Mextentmanagementlocaluniformsize1m;createtablespaceuserdatadatafile'$ORACLE_HOME/oradata/userdata01.dbf'size100Mextentmanagementlocalautoallocate;/*在创建表空间时，设置表空间内的段空间管理模式，这里用的是自动管理*/createtablespaceuserdatadatafile'$ORACLE_HOME/oradata/userdata01.dbf'size100Mextentmanagementloc