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;