數(shù)據(jù)庫運維說明書

1、數(shù)據(jù)庫運維說明書、數(shù)據(jù)庫1、數(shù)據(jù)庫名稱 select n ame from v$database;2、數(shù)據(jù)庫唯一名稱 select db_unique_n ame from v$database;3、數(shù)據(jù)庫ID select dbid from v$database;4、數(shù)據(jù)庫創(chuàng)建時間 select to_char(created,'yyyy/mm/dd hh24:mi:ss') from v$database;5、日志歸檔模式 select log_mode from v$database;6、打開模式 select ope n_mode from v$database;7、

2、數(shù)據(jù)庫角色 select database_role from v$database;8、服務(wù)器參數(shù)文件 select value from v$parameter where n ame='spfile'9、控制文件列表 select value from v$parameter2 where n ame='c on trol_files'10、檢查監(jiān)聽狀態(tài) cmdIsnrctl status二、表空間1、檢查表空間的使用情況select a.tablespace_name "表空名",round(total, 2)表空間大小，round(

3、free, 2) 表空間剩余大小，round(total - free, 2)表空間使用大小，round(total - free) / total, 4) * 100 " 使用率 %"from (select tablespace_ name, sum(bytes) / power(1024, 3) freefrom dba_free_spacegroup by tablespace_ name) a,(select tablespace_ name, sum(bytes) / power(1024, 3) total from dba_data_filesgroup b

4、y tablespace_ name) bwhere a.tablespace_ name = b.tablespace_ name;2、表空間列表select n ame from v$tablespace;3、數(shù)據(jù)文件列表select n ame from v$datafile;4、臨時文件列表select n ame from v$tempfile;5、日志組列表select group#, bytes/1024/1024M' from v$log;6、檢查數(shù)據(jù)文件的自動增長是否關(guān)閉 select file_ name,autoexte nsible from dba_data_

5、files where autoexte nsible='SYS：7、檢查是否有用戶的缺省表空間和臨時表空間設(shè)置為SYSTEM表空間 select user name,default_tablespace,temporary_tablespace from dba_users;三、用戶與模式對象1、查看oracle的權(quán)限角色Select * From dba_role_privs;-授予用戶和其他角色的角色Select * From dba_sys_privs;-授予用戶和其他角色的系統(tǒng)權(quán)限Select * From dba_tab_privs;-數(shù)據(jù)庫中對象的所有授權(quán)Select *

6、 From user_role_privs;-查看當前用戶的角色2、數(shù)據(jù)庫用戶列表select user name from dba_users order by created;3、模式對象數(shù)量列表descdba_objectsselect owner, object_type, coun t(*)from dba_objectsgroup by owner, object_typeorder by owner, object_type;4、計算每個用戶占用的磁盤空間select owner, sum(bytes) / 1024 / 1024 / 1024 "space(g)&qu

7、ot;from dba_segme ntsgroup by ownerorder by owner;四、實例與會話1、檢查數(shù)據(jù)庫狀態(tài) select status from v$in sta nee;2、主機名稱 select host_ name from v$in sta nee;3、實例名稱 select in sta nce_n ame from v$in sta nee 4、服務(wù)名稱 select value from v$parameter where n ame='service _n ames'5、數(shù)據(jù)庫軟件版本 select version from v$in

8、sta nee;6、實例啟動時間 select to_char(startup_time,'yyyy/mm/dd hh24:mi:ss') from v$in sta nee;7、當前會話列表 select sid, serial#, user name from v$sessi on;8、檢查SGA的使用情況 select * from v$sgastat;select pool,co un t(pool),sum(bytes)/1024/1024 from v$sgastat group by pool;9、檢查回滾段使用情況from v$rollstat r, v$rol

9、lname nselect n.n ame,wraps,exte nds,shri nks,optsize,waits,xacts,aveactive,hwmsizewhere r.usn=n.usn;五、系統(tǒng)參數(shù)1、最大進程數(shù)select value from v$parameter where n ame = 'processes'2、修改Oracle最大進程數(shù)a、以 sysdba 身份登陸 PL/SQL 或者 Worksheetb、查詢目前連接數(shù)c、更改系統(tǒng)連接數(shù)alter system set processes=1000 scope=spfile;d、創(chuàng)建 pfile

10、create pfile from spfile;e、重啟 Oracle服務(wù)或重啟 Oracle服務(wù)器3、最大會話數(shù)select value from v$parameter where n ame = 'sessi on s'4、數(shù)據(jù)塊大小select value from v$parameter where n ame = 'db_block_size：5、游標共享模式select value from v$parameter where n ame = 'cursor_shari ng：6、多塊讀批量select value from v$paramete

11、r where n ame = 'db_file_multiblock_read_co un t'7、回滾段表空間select value from v$parameter where n ame = 'un do_tablespace'8、回滾段保留時間select value from v$parameter where n ame = 'un do_rete nti on：9、回滾段管理模式select value from v$parameter where n ame = 'un do_ma nageme nt：10、系統(tǒng)全局區(qū)目標大小

12、select value/1024/1024M' from v$parameter where n ame = 'sga_target：11、系統(tǒng)全局區(qū)最大值select value/1024/1024M' from v$parameter where n ame = 'sga_max_size：12、程序全局區(qū)目標和select value/1024/1024M' from v$parameter where n ame = 'pga_aggregate_target'13、時間統(tǒng)計select value from v$paramet

13、er where n ame = 'timed_statistics'14、初始化參數(shù)文件select value from v$parameter where n ame = 'spfile：15、數(shù)據(jù)庫名稱select value from v$parameter where n ame='db_ name：16、數(shù)據(jù)庫唯一名稱select value from v$parameter where n ame='db_ unique_n ame'17、實例名稱select value from v$parameter where n ame=

14、'i nsta nce_n ame'18、服務(wù)名稱select value from v$parameter where n ame='service _n ames'六、當前用戶概況1、段的總數(shù)量select count(*)-段的總數(shù)量from user_segme nts;2、各類型段的數(shù)量查看各種類型的段的數(shù)量select segme nt_type,-段的類型cou nt(*)-該類型的段的數(shù)量from user_segme nts group by segme nt_type order by segme nt_type;3、用戶存儲空間消耗 -計算

15、整個用戶占用的存儲空間select user, -數(shù)據(jù)庫用戶名rou nd(sum(bytes/1024/1024/1024),2) as space_GB -占用存儲空間大小from user_segme nts;4、前20個大段-列出占用存儲空間最多的前20個段select segme nt_n ame,-段的名稱segme nt_type,-段的類型tablespace_ name,-所在表空間bytes,- 段的大小roun d(bytes / 1024 / 1024 / 1024, 3) as space_gbfrom (select segme nt_n ame, segme nt

16、_type, tablespace_ name,bytes, blocks, exte nts from user_segme nts order by bytes desc) where row num<= 10;5、計算每個表用的磁盤空間 select segme nt_n ame, sum(bytes) / 1024 / 1024 "space(mb)"from dba_segme ntswhere segme nt_n ame = upper('你要查找的表的名字 ')group by segme nt_n ame;6、表的基本信息-查看表的基

17、本信息select table_ name,-表的名稱tablespace_ name,-表空間的名稱nu m_rows,-記錄數(shù)avg_row_le n,-行的平均長度last_a nalyzed,-統(tǒng)計信息收集時間sample_size-統(tǒng)計信息收集樣本from user_tableswhere table_ name = 'POSTPAY_BILLED_REVENUE：7、字段的基本信息-查看字段的基本信息select table_name,-表的名稱colu mnd,-字段的順序colu mn_n ame,-字段的名稱data_type,-數(shù)據(jù)類型data_le ngth, -

18、字段長度n ullable,-是否允許空值nu m_disti net,-不同值的個數(shù)num_nu Ils-空值記錄數(shù)from user_tab_colu mnswhere table_ name = 'POSTPAY_BILLED_REVENUE'order by colu mn_id；8、表的備注-查看表的備注select table_name,-名稱table_type,-類型comments-備注from user_tab_comme ntswhere table_ name = 'POSTPAY_BILLED_REVENUE'9、字段的備注-查看字段的

19、備注select table_ name,-表的名稱colu mn_n ame,-字段的名稱comme nts-備注from user_col_comme ntswhere table_ name='POSTPAY_BILLED_REVENUE：10、表的約束-查看表的約束select owner,con strai nt_n ame,-約束的名稱con stra in t_type,-約束的類型table_ name-歸屬表的名稱from user_c on stra intswhere table name='POSTPAY BILLED REVENUE'and c

20、on stra in t_type = 'P： 11、字段的約束 -查看約束的字段select owner.con strai nt_n ame,-約束的名稱table_ name,-歸屬表的名稱colu mn_n ame,-字段的名稱positi on-字段的順序from user_c on s_colu mnswhere con strai nt_n ame = 'POSTPAY_BILLED_REVENUE_PK'order by position;12、索引的字段-查看索引的字段select table_ name.-表名稱in dex_ name,-字段名稱c

21、olu mn _positi on,-字段順序colu mn_n ame-字段名稱from user_ in d_colu mnswhere table_ name ='POSTPAY_BILLED_REVENUE'order by in dex_ name, colu mn _positi on;13、檢查是否有失效的索引Select * From Userndexes t Where t.Status != 'VALID'14、對象總數(shù)量-查看對象的數(shù)量select coun t(*)from user_objects;15、對象分類數(shù)量-查看各種類型對象的

22、數(shù)量select object_type,-對象的類型coun t(*)-對象的數(shù)量from user_objectsgroup by object_typeorder by object_type;16、數(shù)據(jù)庫鏈接列表 select * from user_db_links;17、大對象列表select * from user_lobs;18、物化視圖列表select * from user_mviews;19、視圖列表select * from user_views;20、視圖長度-查看視圖定義的長度select owner, view_ name, text_le ngth from d

23、ba_views where view_ name='&view_ name：21、視圖定義-從數(shù)據(jù)字典視圖中直接查詢視圖的定義select text from dba_views where view_ name='&view_ name：22、視圖定義-通過應(yīng)用程序編程接口獲取視圖的定義select dbms_metadata.get_ddl('VIEW,'&view_ name','CAMPAIGN') from dual;23、檢查是否有無效的對象col object_ name format a25;se

24、lect object_name,object_type,owner,statusfrom dba_objects where status !='VALID' and owner not in('SYS','SYSTEM')select own er,object_ name,object_type from dba_objects where status='INVALID'24、檢查sequenee 使用set lin esize 120select seque nce_ow ner,seque nce_n ame, min

25、 _value,max_value,i ncreme nt_by,last_ nu mber,cache_size,cycle_flag from dba_seque nces;25、檢查是否有運行失敗的jobcol what format a20;select job,this_date,this_sec, next_date ,n ext_sec,failures,what from dba_jobs where failures !=0 or failures is not null;26、檢查不起作用的約束selectown er,c on stra int_n ame,table_

26、name,c on stra in t_type,statusfromdba_c on stra intswherestatus='DISABLED' and con strai nt_type='p：27、檢查無效的 triggerselect own er,trigger_ name,table_ name,status from dba_triggers where status='DISABLED：五、SQL1、查找死鎖的語句Select l.sessi on _idsid, s.serial#, l.locked_mode, l.oracle_user

27、 name, l.os_user_ name, s.mach ine,s.term in al,o.object_ name,s.logo n_timeFrom v$locked_object I, all_objects o, v$sessi on sWhere l.object_id = o.object_idAnd l.sessi on_id = s.sidOrder By sid, s.serial#;2、查看已經(jīng)執(zhí)行過的 sql這些是存在共享池中的：Select * From v$sqlarea t Order By t.LAST_ACTIVE_TIMEDesc;3、獲取SQL消耗資

28、源Select Disti net sl.target,s.sql_text,sp.opti ons,sp.cost,sp.cpu_cost,sp.io_costFrom v$sessi on_lon gopssl, v$sql s, v$sql_pla nspWhere sl.SQL_ADDRESS = s.ADDRESSAnd sp.address = s.addressAnd Cost Is Not NullAnd optio ns Is Not Null以上sql通過關(guān)聯(lián)v$sql,v$session_longops（ 這個視圖存放超過 6秒的sql，尤其是存在大量的數(shù)據(jù)分散讀等待事件

29、時）得到相關(guān)信息。target :對象名sql_text : sql 語句options:執(zhí)行情況cost :總的成本cpu_cost : cpu使用情況io_cost :磁盤io讀寫情況4、查看值得懷疑的SQLSelect Substr(to_char(s.pct, '99.00'), 2) | '%' Load,s.executio ns executes,p.sql_textFrom (Select address,disk_reads,executi ons,pct,Ran k() Over(Order By disk_readsDesc) rankin

30、gFrom (Select address,disk_reads,executi ons,100 * ratio_to_report(disk_reads) Over() pctFrom sys.v_$sqlWhere comma nd_type != 47)Where disk_reads> 50 * executio ns) s,sys.v_$sqltext pWhere s.ra nkin g<= 5And p.address = s.addressOrder By 1, s.address, p.piece;5、查看消耗內(nèi)存多的 SQLSelect b.user name,

31、a. buffer_gets,a.executi ons,a.disk_reads / Decode(a.executions, 0, 1, a.executions),a.sql_text SQLFrom v$sqlarea a, dba_users bWhere a.pars in g_user_id = b.user_idAnd a.disk_reads> 10000Order By disk_readsDesc;6、查看邏輯讀多的SQLSelect *From (Select buffer_gets, sql_textFrom v$sqlareaWhere buffer_gets> 500000Order By buffer_getsDesc)Where Row num<= 30;7、查看執(zhí)行次數(shù)多的 SQLSelect sql_text, executi onsFrom (Select sql_text, executi ons From v$sqlareaOrder By executi ons Desc)Where Rownum< 81;8、查看讀硬盤多的 SQLSelect sql_text, disk_