ORACLE常用系统表

化参数 PROCESS 设定的值，否则将导致客户端连接失败。如果该值接近 process 值，应考虑加大设置。
◆ 查询 Oracle 的版本号 select * from v$version;
◆ 监控事例的等待 select event, sum(decode(wait_Time, 0, 0, 1)) "Prev", sum(decode(wait_Time, 0, 1, 0)) "Curr", count(*) "Tot" from v$session_Wait group by event order by 4;
◆ 来自控制文件中的数据文件信息： select * from V$datafile;
◆ NLS 参数当前值： select * from V$nls_parameters;
◆ ORACLE 版本信息： select * from v$version;
◆ 描述后台进程： select * from v$bgprocess;
◆ 锁及资源信息： select * from v$lock;不包括 DDL 锁
◆ 数据库字符集：
select name, value$ from props$ where name='NLS_CHARACTERSET';
◆ inin.ora 参数： select name, value from v$parameter order by name;
◆ 表： select * from cat; select * from tab; select table_name from user_tables;
◆ 视图： select text from user_views where view_name=upper('&view_name');
◆ 索引： select index_name, table_owner, table_name, tablespace_name, status from user_indexes
◆ 表空间剩余自由空间情况： select tablespace_name, sum(bytes) 总字节数, max(bytes), count(*) from dba_free_space
group by tablespace_name;
◆ 数据字典： select table_name from dict order by table_name;
◆ 查看版本信息： select * from product_component_version;
◆ 定期更新索引 Exec dbms_stats.gather_table_stats('用户名', '表名');
◆ 查看 oracle 连接数 select count(*)from v$process; 可以查看当前 oracle 的进程数（包括后台进程）。Oracle 的当前进程数不可能超过初始
◆ 回滚段的争用情况 select name, waits, gຫໍສະໝຸດ Baiduts, waits/gets "Ratio" from v$rollstat a, v$rollname b where a.usn = b.usn;
◆ 监控表空间的 I/O 比例 select df.tablespace_name name, df.file_name "file", f.phyrds pyr, f.phyblkrd pbr, f.phywrts pyw, f.phyblkwrt pbw from v$filestat f, dba_data_files df where f.file# = df.file_id order by df.tablespace_name;
◆ 监控文件系统的 I/O 比例 select substr(a.file#, 1, 2) "#", substr(a.name, 1, 30) "Name", a.status, a.bytes, b.phyrds, b.phywrts from v$datafile a, v$filestat b where a.file# = b.file#;
◆ 死锁检查 select lpad(' ', decode(l.xidusn, 0, 3, 0)) || l.oracle_username User_name, o.owner, o.object_name, o.object_type, s.sid, s.serial# from v$locked_object l, dba_objects o, v$session s where l.object_id=o.object_id AND l.session_id=s.sid order by o.object_id desc;
order by table_name;
◆ 触发器： select trigger_name, trigger_type, table_owner, table_name, status from user_triggers;
◆ 快照： select owner, name, master, table_name, last_refresh, next from user_snapshots order by
select s.username, decode(l.type, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL, o.owner, o.object_name, o.object_type, s.sid, s.serial#, s.terminal, s.machine, s.program, s.osuser from v$session s, v$lock l, dba_objects o where s.sid=l.sid and o.object_id(+)=l.id1 and s.username is not null;
user_constraints;
◆ 本用户读取其他用户对象的权限： select * from user_tab_privs;
◆ 本用户所拥有的系统权限： select * from user_sys_privs;
◆ 用户： select * from all_users order by user_id;
◆ 在某个用户下找所有的索引 select user_indexes.table_name, user_indexes.index_name, uniqueness, from user_ind_columns, user_indexes where user_ind_columns.index_name = user_indexes.index_name and user_ind_columns.table_name = user_indexes.table_name order by user_indexes.table_type, user_indexes.table_name, user_indexes.index_name, column_position;
select retries.value/entries.value "redo log buffer retry ration" from v$sysstat retries, v$sysstat entries where retries.name='redo buffer allocation retries' and entries.name='redo entries';
◆ 监控 SGA 中字典缓冲区的命中率 select parameter, gets, Getmisses , getmisses/(gets+getmisses)*100 "miss ratio",
(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio" from v$rowcache where gets+getmisses <>0 group by parameter, gets, getmisses;
以 oracle 的超级用户(sys)登录数据库.
◆ Cache 命中率检查 select namespace, gethitratio, pinhitratio, reloads, invalidations from v$librarycache where namespace in('SQL AREA', 'TABLE/PROCEDURE', 'BODY', 'TRIGGER');
select 1-(sum(getmisses)/sum(gets)) "data dictonary hit ratio" from v$rowcache;
select 1-(physical.value/(blockgets.value+consistent.value)) "buffer cache hit ratio" from v$sysstat physical, v$sysstat blockgets, v$sysstat consistent where physical.name='physical reads' and blockgets.name='db block gets' and consistent.name='consistent gets';
◆ SQL 共享池： select sql_text from v$sqlarea;
◆ 数据库： select * from v$database
◆ 控制文件： select * from V$controlfile;
◆ 重做日志文件信息： select * from V$logfile;
◆ 来自控制文件中的日志文件信息： select * from V$log;
◆ 显示所有数据库对象的类别和大小 select count(name) num_instances , type , sum(source_size) source_size , sum(parsed_size) parsed_size , sum(code_size) code_size , sum(error_size) error_size, sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required from dba_object_size group by type order by 2;
◆ 监控 SGA 中共享缓存区的命中率 select sum(pins) "Total Pins", sum(reloads) "Total Reloads", sum(reloads)/sum(pins) *100 libcache from v$librarycache;
select sum(pinhits-reloads)/sum(pins) "hit radio", sum(reloads)/sum(pins) "reload percent" from v$librarycache;
owner, next;
◆ 同义词： select * from syn;
◆ 序列： select * from seq;
◆ 数据库链路： select * from user_db_links;
◆ 约束限制： select TABLE_NAME, CONSTRAINT_NAME, SEARCH_CONDITION, STATUS from
column_name
◆ 监控 SGA 的命中率 select a.value + b.value "logical_reads", c.value "phys_reads", round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO" from v$sysstat a, v$sysstat b, v$sysstat c where a.statistic# = 38 and b.statistic# = 39 and c.statistic# = 40;