数据库运维说明书

数据库运维说明书

一、数据库

1、数据库名称 select name from v$database; 2、数据库唯一名称 select db_unique_name from v$database; 3、数据库ID select dbid from v$database; 4、数据库创建时间 select to_char(created,'yyyy/mm/dd hh24:mi:ss') from v$database; 5、日志归档模式 select log_mode from v$database; 6、打开模式 select open_mode from v$database; 7、数据库角色 select database_role from v$database; 8、服务器参数文件 select value from v$parameter where name='spfile'; 9、控制文件列表 select value from v$parameter2 where name='control_files'; 10、检查监听状态 cmd lsnrctl status

二、表空间

1、检查表空间的使用情况 select a.tablespace_name \表空名\ round(total, 2) 表空间大小, round(free, 2) 表空间剩余大小, round(total - free, 2) 表空间使用大小, round((total - free) / total, 4) * 100 \使用率%\ from (select tablespace_name, sum(bytes) / power(1024, 3) free from dba_free_space group by tablespace_name) a, (select tablespace_name, sum(bytes) / power(1024, 3) total from dba_data_files group by tablespace_name) b where a.tablespace_name = b.tablespace_name; 2、表空间列表 select name from v$tablespace; 3、数据文件列表 select name from v$datafile; 4、临时文件列表 select name from v$tempfile; 5、日志组列表 select group#, bytes/1024/1024||'M' from v$log; 6、检查数据文件的自动增长是否关闭 select file_name,autoextensible from dba_data_files where autoextensible='SYS'; 7、检查是否有用户的缺省表空间和临时表空间设置为SYSTEM表空间 select username,default_tablespace,temporary_tablespace from dba_users; 三、用户与模式对象

1、 查看oracle的权限角色: Select * From dba_role_privs; --授予用户和其他角色的角色 Select * From dba_sys_privs; --授予用户和其他角色的系统权限 Select * From dba_tab_privs; --数据库中对象的所有授权 Select * From user_role_privs; --查看当前用户的角色 2、 数据库用户列表 select username from dba_users order by created; 3、 模式对象数量列表 descdba_objects select owner, object_type, count(*) from dba_objects group by owner, object_type order by owner, object_type; 4、 计算每个用户占用的磁盘空间 select owner, sum(bytes) / 1024 / 1024 / 1024 \ from dba_segments group by owner order by owner; 四、实例与会话

1、检查数据库状态 select status from v$instance; 2、主机名称 select host_name from v$instance; 3、实例名称 select instance_name from v$instance 4、服务名称 select value from v$parameter where name='service_names'; 5、数据库软件版本 select version from v$instance; 6、实例启动时间 select to_char(startup_time,'yyyy/mm/dd hh24:mi:ss') from v$instance; 7、当前会话列表 select sid, serial#, username from v$session; 8、检查SGA的使用情况 select * from v$sgastat; select pool,count(pool),sum(bytes)/1024/1024 from v$sgastat group by pool; 9、检查回滚段使用情况

select n.name,wraps,extends,shrinks,optsize,waits,xacts,aveactive,hwmsize from v$rollstat r, v$rollname n where r.usn=n.usn; 五、系统参数

1、最大进程数 select value from v$parameter where name = 'processes'; 2、修改Oracle最大进程数 a、以sysdba身份登陆PL/SQL 或者 Worksheet b、查询目前连接数 c、更改系统连接数 alter system set processes=1000 scope=spfile; d、创建pfile create pfile from spfile; e、重启Oracle服务或重启Oracle服务器 3、最大会话数 select value from v$parameter where name = 'sessions'; 4、数据块大小 select value from v$parameter where name = 'db_block_size'; 5、游标共享模式 select value from v$parameter where name = 'cursor_sharing'; 6、 多块读批量 select value from v$parameter where name = 'db_file_multiblock_read_count'; 7、回滚段表空间 select value from v$parameter where name = 'undo_tablespace'; 8、回滚段保留时间 select value from v$parameter where name = 'undo_retention'; 9、回滚段管理模式 select value from v$parameter where name = 'undo_management'; 10、系统全局区目标大小 select value/1024/1024||'M' from v$parameter where name = 'sga_target'; 11、系统全局区最大值 select value/1024/1024||'M' from v$parameter where name = 'sga_max_size'; 12、程序全局区目标和 select value/1024/1024||'M' from v$parameter where name = 'pga_aggregate_target'; 13、时间统计 select value from v$parameter where name = 'timed_statistics'; 14、初始化参数文件 select value from v$parameter where name = 'spfile'; 15、数据库名称 select value from v$parameter where name='db_name'; 16、数据库唯一名称 select value from v$parameter where name='db_unique_name'; 17、实例名称 select value from v$parameter where name='instance_name'; 18、服务名称 select value from v$parameter where name='service_names'; 六、当前用户概况

1、段的总数量 select count(*) -- 段的总数量 from user_segments; 2、各类型段的数量 -- 查看各种类型的段的数量 select segment_type, -- 段的类型 count(*) -- 该类型的段的数量 from user_segments group by segment_type order by segment_type; 3、用户存储空间消耗 -- 计算整个用户占用的存储空间 select user, -- 数据库用户名 round(sum(bytes/1024/1024/1024),2) as space_GB -- 占用存储空间大小 from user_segments; 4、前20个大段 -- 列出占用存储空间最多的前20个段 select segment_name, -- 段的名称 segment_type, -- 段的类型 tablespace_name, -- 所在表空间 bytes, -- 段的大小 round(bytes / 1024 / 1024 / 1024, 3) as space_gb from (select segment_name, segment_type, tablespace_name, bytes, blocks, extents from user_segments order by bytes desc) where rownum<= 10; 5、计算每个表用的磁盘空间 select segment_name, sum(bytes) / 1024 / 1024 \ from dba_segments where segment_name = upper('你要查找的表的名字') group by segment_name; 6、表的基本信息 -- 查看表的基本信息 select table_name, -- 表的名称 tablespace_name, -- 表空间的名称 num_rows, -- 记录数 avg_row_len, -- 行的平均长度 last_analyzed, -- 统计信息收集时间 sample_size -- 统计信息收集样本 from user_tables where table_name = 'POSTPAY_BILLED_REVENUE'; 7、字段的基本信息

-- 查看字段的基本信息 select table_name, -- 表的名称 column_id, -- 字段的顺序 column_name, -- 字段的名称 data_type, -- 数据类型 data_length, -- 字段长度 nullable, -- 是否允许空值 num_distinct, -- 不同值的个数 num_nulls -- 空值记录数 from user_tab_columns where table_name = 'POSTPAY_BILLED_REVENUE' order by column_id; 8、表的备注 -- 查看表的备注 select table_name, -- 名称 table_type, -- 类型 comments -- 备注 from user_tab_comments where table_name = 'POSTPAY_BILLED_REVENUE'; 9、字段的备注 -- 查看字段的备注 select table_name, -- 表的名称 column_name, -- 字段的名称 comments -- 备注 from user_col_comments where table_name = 'POSTPAY_BILLED_REVENUE'; 10、表的约束 -- 查看表的约束 select owner, constraint_name, -- 约束的名称 constraint_type, -- 约束的类型 table_name -- 归属表的名称 from user_constraints where table_name = 'POSTPAY_BILLED_REVENUE' and constraint_type = 'P'; 11、字段的约束 -- 查看约束的字段 select owner, constraint_name, -- 约束的名称 table_name, -- 归属表的名称 column_name, -- 字段的名称 position -- 字段的顺序 from user_cons_columns where constraint_name = 'POSTPAY_BILLED_REVENUE_PK' order by position; 12、索引的字段 -- 查看索引的字段 select table_name, -- 表名称

index_name, -- 字段名称 column_position, -- 字段顺序 column_name -- 字段名称 from user_ind_columns where table_name = 'POSTPAY_BILLED_REVENUE' order by index_name, column_position; 13、检查是否有失效的索引 Select * From User_Indexes t Where t.Status != 'VALID'; 14、对象总数量 -- 查看对象的数量 select count(*) from user_objects; 15、对象分类数量 -- 查看各种类型对象的数量 select object_type, -- 对象的类型 count(*) -- 对象的数量 from user_objects group by object_type order by object_type; 16、数据库链接列表 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_length from dba_views where view_name='&view_name'; 21、视图定义 -- 从数据字典视图中直接查询视图的定义 select text from dba_views where view_name='&view_name'; 22、视图定义 -- 通过应用程序编程接口获取视图的定义 select dbms_metadata.get_ddl('VIEW','&view_name','CAMPAIGN') from dual; 23、检查是否有无效的对象 col object_name format a25; select object_name,object_type,owner,status from dba_objects where status !='VALID' and owner not in ('SYS','SYSTEM') select owner,object_name,object_type from dba_objects where status='INVALID'; 24、检查sequence使用 set linesize 120 select sequence_owner,sequence_name,min_value,max_value,increment_by,last_number,cache_size,cycle_flag dba_sequences; 25、检查是否有运行失败的job col what format a20; select job,this_date,this_sec,next_date,next_sec,failures,what from dba_jobs where failures !=0 or failures is not null; from 26、检查不起作用的约束 select owner,constraint_name,table_name,constraint_type,status from dba_constraints where status='DISABLED' and constraint_type='p'; 27、检查无效的trigger select owner,trigger_name,table_name,status from dba_triggers where status='DISABLED'; 五、SQL

1、 查找死锁的语句 Select l.session_idsid, s.serial#, l.locked_mode, l.oracle_username, l.os_user_name, s.machine, s.terminal, o.object_name, s.logon_time From v$locked_object l, all_objects o, v$session s Where l.object_id = o.object_id And l.session_id = s.sid Order By sid, s.serial#; 2、 查看已经执行过的sql这些是存在共享池中的： Select * From v$sqlarea t Order By t.LAST_ACTIVE_TIMEDesc; 3、 获取SQL消耗资源 Select Distinct sl.target, s.sql_text, sp.options, sp.cost, sp.cpu_cost, sp.io_cost From v$session_longopssl, v$sql s, v$sql_plansp Where sl.SQL_ADDRESS = s.ADDRESS And sp.address = s.address And Cost Is Not Null And options Is Not Null 以上sql通过关联v$sql,v$session_longops(这个视图存放超过6秒的sql，尤其是存在大量的数据分散读等待事件时)得到相关信息。 target：对象名 sql_text：sql语句 options:执行情况 cost：总的成本 cpu_cost：cpu使用情况 io_cost：磁盘io读写情况 4、 查看值得怀疑的SQL Select Substr(to_char(s.pct, '99.00'), 2) || '%' Load, s.executions executes, p.sql_text From (Select address, disk_reads, executions, pct, Rank() Over(Order By disk_readsDesc) ranking From (Select address, disk_reads, executions, 100 * ratio_to_report(disk_reads) Over() pct From sys.v_$sql Where command_type != 47) Where disk_reads> 50 * executions) s, sys.v_$sqltext p Where s.ranking<= 5 And p.address = s.address Order By 1, s.address, p.piece; 5、 查看消耗内存多的SQL Select b.username, a. buffer_gets, a.executions, a.disk_reads / Decode(a.executions, 0, 1, a.executions), a.sql_text SQL From v$sqlarea a, dba_users b Where a.parsing_user_id = b.user_id And a.disk_reads> 10000 Order By disk_readsDesc; 6、 查看逻辑读多的SQL Select * From (Select buffer_gets, sql_text From v$sqlarea Where buffer_gets> 500000 Order By buffer_getsDesc) Where Rownum<= 30; 7、 查看执行次数多的SQL Select sql_text, executions From (Select sql_text, executions From v$sqlareaOrder By executions Desc) Where Rownum< 81; 8、 查看读硬盘多的SQL Select sql_text, disk_reads From (Select sql_text, disk_reads From v$sqlarea Order By disk_readsDesc) Where Rownum< 21; 9、 查看排序多的SQL Select sql_text, sorts From (Select sql_text, sorts From v$sqlarea Order By sorts Desc) Where Rownum< 21; 10、 分析的次数太多，执行的次数太少，要用绑变量的方法来写sql Set pagesize 600; Set linesize 120; Select Substr(sql_text, 1, 80) \ From v$sqlarea Where executions < 5 Group By Substr(sql_text, 1, 80) Having Count(*) > 30 Order By 2; 11、 游标的观察 Set pages 300; Select Sum(a.value), b.name From v$sesstat a, v$statname b Where a.statistic# = b.statistic# And b.name = 'opened cursors current' Group By b.name; Select Count(0) From v$open_cursor; Select user_name, sql_text, Count(0) From v$open_cursor Group By user_name, sql_text Having Count(0) > 30; 12、 查看当前用户&username执行的SQL Select sql_text From v$sqltext_with_newlines Where (hash_value, address) In (Select sql_hash_value, sql_address From v$session Where username = '&username') Order By address, piece;

本文来源：https://www.bwwdw.com/article/o8n.html