前一段時間做數(shù)據(jù)庫日常管理、分析，一些腳本沒有時間整理，現(xiàn)列出來，有空再整：
--oracle 常用sql
--查詢數(shù)據(jù)庫信息
select * from v$database;
--查詢當前實例信息
select * from v$instance;
--查詢數(shù)據(jù)庫版本信息
select * from v$version;
--查詢所有用戶(dba權限)
select * from dba_users;
--查詢共享內存中的sql信息，執(zhí)行較慢
select hash_value from v$sqltext where sql_text like '%查詢sql%';
select sql_text from v$sqltext where hash_value =查找的hash_value order by hash_value,piece;
--查詢當前系統(tǒng)表空間存儲信息
select a.file_id,a.file_name, a.TABLESPACE_NAME,a.bytes/1024/1024||'M' as totalspace,
nvl(b.freespace,0)||'M' as freespace,a.autoextensible from dba_data_files a,
(select sum(bytes)/1024/1024 as freespace,file_id,tablespace_name from dba_free_space
group by tablespace_name,file_id ) b
where a.file_id=b.file_id(+)
order? by a.tablespace_name,file_name;
--統(tǒng)計已占用的表空間
select sum(a.bytes/1024/1024-nvl(b.freespace,0))||'M' from dba_data_files a,
(select sum(bytes)/1024/1024 as freespace,file_id,tablespace_name from dba_free_space
group by tablespace_name,file_id ) b
where a.file_id=b.file_id(+) and a.tablespace_name='tablespace名稱'
?
--查詢當前活動session最近一次執(zhí)行的sql
select sql_text from v$sqltext where hash_value=(
select?? PREV_HASH_VALUE from v$session where status='ACTIVE' and username is not null ) order by piece
?
--查詢相關用戶的索引情況
select a.*,b.column_name,b.column_length from
(select owner,index_name,table_name,index_type,tablespace_name
from dba_indexes where owner ='用戶名' and index_name not in
(select constraint_name from dba_constraints where owner='用戶名' and constraint_type in ('P','R'))
)a,
(select * from dba_ind_columns b where index_owner='用戶名') b
where a.index_name=b.index_name
order by a.owner,a.table_name,a.index_name
--重建索引
alter index 索引名稱 rebuild nologging;
--查看事務回滾率
select name,value from v$sysstat where name in('user commits','transaction rollbacks');
?
?
--sql分析優(yōu)化
--腳本位置${oracle_home}/rdbms/admin/utlxplan.sql 創(chuàng)建執(zhí)行計劃表
--授權訪問
create public synonym plan_table for plan_table;
grant select,update,insert,delete on plan_table to public;
--腳本位置${oracle_home}/sqlplus/admin/plustrce.sql 執(zhí)行授權角色
?
--spfile
--數(shù)據(jù)庫建庫完成后，第一次手工啟動手工創(chuàng)建spfile文件,命令格式為
create spfile[='filename'] from pfile[='filename'];
--判斷是采用spfile還是pfile啟動數(shù)據(jù)庫的，可以下面sql根據(jù)是那種方式
select decode(count(*),0,'pfile',1,'spfile') from V$spparameter where isspecified='TRUE' and rownum=1;
--spfile,pfile創(chuàng)建默認位置為$ORACLE_HOME/dbs/,$ORACLE_HOME/database/(windows)
--啟動時公司搜索默認位置下spfile.ora,spfile$ORACLE_SID.ora,init$ORACLE_SID.ora
--采用指定的方式啟動
startup pfile='filename';
--創(chuàng)建spfile,pfile可以在數(shù)據(jù)庫關閉后仍然可以創(chuàng)建
--如果pfile內有spfile文件參數(shù)的設定，采用pfile方式啟動后還可以修改初始參數(shù)到spfile中
--oracle9.2.0.1版本后創(chuàng)建數(shù)據(jù)庫時系統(tǒng)會自動創(chuàng)建spfile，默認的啟動方式就是采用spfile
--運行時間spfile文件沒有鎖定
--修改密碼
alter user username identified by newpassword;
--oracle9i 驗證方式有兩種，可以在sqlnet.ora中配置
SQLNET.AUTHENTICATION_SERVICES=(NTS)?? --操作系統(tǒng)驗證，無須用戶輸入密碼
SQLNET.AUTHENTICATION_SERVICES=(none) --用正確的用戶和密碼驗證
--設置密碼級別 remote_login_passwordfile
--設置為exclusive，表示口令文件由一個數(shù)據(jù)庫使用 ，遠程客戶端可以用sys登陸（如果密碼文件刪除后，遠程無法登陸）
--設置為shared，表示 多個數(shù)據(jù)庫可以共享一個口令文件,但是只可以識別一個用戶SYS，不能將sysdba權限授權給其他用戶
--設置為none,表示沒有口令文件?? 遠程無法用sys登陸，只能通過操作系統(tǒng)驗證方式
--密碼文件如果丟失或損壞，系統(tǒng)無法啟動
--可以手工創(chuàng)建oracle系統(tǒng)密碼
orapwd file=<fname> password=<password> entries=<users>
entries? --sysdba鏈接最大數(shù)
--密碼文件沒有鎖定，只是啟動時的引導作用
V$SYSTEM_EVENT? 數(shù)據(jù)庫實例整個運行期間所有進程事件的等待時間、次數(shù)視圖，作為系統(tǒng)優(yōu)化的依據(jù)
等待事件分為三種類型 空閑等待、例行等待、資源等待
注意很多時候進程是空閑等待處理的，所以在該視圖主要查看資源等待時間，
如果是進程在例行操作中等待，可以查看該事件的平均等待時間
?
--v$session_wait
--常見的空閑等待事件：client message、null event、pipe get、pmon/smon timer、rdbms rpc message及sql*net等；
--常見的非等閑事件：
buffer busy waits、
db file scattered read、
db file sequential read、
enqueue、
free buffer waits、
latch free、
log file sync、
log file paralle write
?
導入導出建議一定要同版本的導入導出工具
常見錯誤 exp-00003
估算導出dmp文件大小
select sum(bytes) from user_segments where segment_type='TABLE';
這個計算結果不包含LOB, 和VARRAY, 亦不含分區(qū)表數(shù)據(jù)
--設置歸檔模式
startup mount;
alter database archivelog;
alter database open;
alter system set ?log_archive_start=true scope=spfile;
shutdown immediate
startup;
?
?
數(shù)據(jù)庫已經誤刪除數(shù)據(jù)文件，如何啟動
startup mount
alter database datafile 'filepos'? offline drop;(noarchivelog)
alter database datafile 'filepos'? offline;(archivelog)
alter database open;
drop tablespace 'spacename'?? including contents;
?
log_miner的使用
?
?
?
--查看聯(lián)機日志信息
select a.group#,b.member, sequence#,first_change# from v$log a ,v$logfile b
where a.group#=b.group#;
?
v$rollstat三個字段說明
rssize 回滾段大小
optsize? optimal大小
hwmsize?? 你的回滾段曾經最高大小
--統(tǒng)計當前回滾段大小、最高峰大小
select sum(rssize)/1024/1024||'M',sum(hwmsize/1024/1024)||'M'
from v$rollstat;
?
--查看事務占用的回滾段大小(事務尚未提交)
select b.used_ublk,b.xidusn,a.sid from v$session a,v$transaction b
where a.taddr=b.addr;
?
--查看操作系統(tǒng)硬件信息
prtdiag -v
--臨時表空間為空
錯誤號ORA-25153:
alter tablespace 表空間名? add tempfile '文件名' size 文件大小;
創(chuàng)建臨時表空間
create temporary tablespace 新表空間名 tempfile '文件名' size 文件大小;
alter? database default temporary tablespace 新表空間名;
drop tablespace 舊空間名 including contents;
rman數(shù)據(jù)文件損壞恢復腳本
--數(shù)據(jù)文件損壞
run{
allocate channel c1 type disk;
restore datafile 數(shù)據(jù)文件號;
recover datafile 數(shù)據(jù)文件號;
release channel c1;
}
一次控制文件損壞，恢復過程
故意修改控制文件，出現(xiàn)ora-00205錯誤
在rman下恢復控制文件
restore controlfile from file='最近的一次控制文件備份'
在alter database open resetlogs 出現(xiàn)
ORA 1152 file <name> was not restored from a sufficiently old backup? 錯誤
不行
然后在rman下從自動文件恢復
restore controlfile from autobackup;
啟動仍然出現(xiàn)該錯誤，
通過rman恢復database也不行，
最后沒辦法，采用
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
然后根據(jù)trc文件中的指導
采用第二種方式也不行，只能采用第一種建立
在數(shù)據(jù)庫nomount狀態(tài)下
執(zhí)行trc相關?腳本
alter database open;
數(shù)據(jù)庫啟動正常