oracle dba 常用语句4

===========================================================######### 创建数据库----look $ORACLE_HOME/rdbms/admin/buildall.sql ############# create database db01maxlogfiles 10maxdatafiles 1024maxinstances 2logfileGROUP 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/oradata/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 time_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 immediatestartup nomountselect * from v$instance;select * from v$sga;select * from v$tablespace;alter session set nls_language=american;alter database mount;select * from v$database;alter database open;desc dictionaryselect * from dict;desc v$fixed_table;select * from v$fixed_table;set oracle_sid=foxconnselect * from dba_objects;set serveroutput onexecute dbms_output.put_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/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.ora'select * from v$parameter where name like 'control%' ;show parameter control;select * from v$controlfile_record_section;select * from v$tempfile;/* 备份控制文件 */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;-- 强制进行一次 checkpointalter 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 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/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_log_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;