|
DBA常用的脚本很多。每个人使用的可能不尽相同。下面提供一些供大家使用 一、数据库构架体系
3、除了监控表空间的剩余空间，有时候我们也有必要了解一下该表空间是否具有自动扩展空间的能力，虽然我们建议在生产系统中预先分配空间。以下语句将完成这一功能
4、我相信使用字典管理的表空间的也不少吧，因为字典管理的表空间中，每个表的下一个区间的大小是不可以预料的，所以我们必须监控那些表在字典管理的表空间中的下一个区间的分配将会引起性能问题或由于是非扩展的表空间而导致系统停止。以下语句检查那些表的扩展将引起表空间的扩展。
5、段的占用空间与区间数也是很需要注意的一个问题，如果一个段的占用空间太大，或者跨越太多的区间（在字典管理的表空间中，将有严重的性能影响），如果段没有可以再分配的区间，将导致数据库错误。所以，段的大小与区间监控也是一个很重要的工作
6、对象的空间分配与空间利用情况，除了从各个方面的分析，如分析表，查询rowid等方法外，其实oracle提供了一个查询空间的包dbms_space，如果我们稍封装一下，将是非常好用的一个东西。
SQL> set heading off
SELECT table_name FROM all_tables WHERE owner = USER MINUS SELECT table_name FROM all_constraints WHERE owner = USER AND constraint_type = 'P' 二、性能监控
1、数据缓冲区的命中率已经不是性能调整中的主要问题了，但是，过低的命中率肯定是不可以的，在任何情况下，我们必须保证有一个大的data buffer和一个高的命中率。 这个语句可以获得整体的数据缓冲命中率，越高越好 SELECT a.VALUE + b.VALUE logical_reads, c.VALUE phys_reads, round(100*(1-c.value/(a.value+b.value)),4) hit_ratio FROM v$sysstat a,v$sysstat b,v$sysstat c WHERE a.NAME='db block gets' AND b.NAME='consistent gets' AND c.NAME='physical reads' 2、库缓冲说明了SQL语句的重载率，当然，一个SQL语句应当被执行的越多越好，如果重载率比较高，就考虑增加共享池大小或者是提高Bind变量的使用
以下语句查询了Sql语句的重载率，越低越好 SELECT SUM(pins) total_pins,SUM(reloads) total_reloads, SUM(reloads)/SUM(pins)*100 libcache_reload_ratio FROM v$librarycache 3、用户锁，数据库的锁有的时候是比较耗费资源的，特别是发生锁等待的时候，我们必须找到发生等待的锁，有可能的话，杀掉该进程。 这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现，任何DML语句其实产生了两个锁，一个是表锁，一个是行锁。 可以通过alter system kill session ‘sid,serial#’来杀掉会话 SELECT /*+ rule */ 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 l.sid = s.sid AND l.id1 = o.object_id(+) AND s.username is NOT NULL 4、锁与等待，如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待
以下的语句可以查询到谁锁了表，而谁在等待。 SELECT /*+ rule */ 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,xidusn DESC 以上查询结果是一个树状结构，如果有子节点，则表示有等待发生。如果想知道锁用了哪个回滚段，还可以关联到V$rollname，其中xidusn就是回滚段的USN
5、如果发生了事务或锁，想知道哪些回滚段正在被使用吗？其实通过事务表，我们可以详细的查询到事务与回滚段之间的关系。同时，如果关联会话表，我们则可以知道是哪个会话发动了这个事务。 SELECT s.USERNAME,s.SID,s.SERIAL#,t.UBAFIL "UBA filenum",
t.UBABLK "UBA Block number",t.USED_UBLK "Number os undo Blocks Used", t.START_TIME,t.STATUS,t.START_SCNB,t.XIDUSN RollID,r.NAME RollName FROM v$session s,v$transaction t,v$rollname r WHERE s.SADDR=t.SES_ADDR AND t.XIDUSN=r.usn 7、如果利用会话跟踪或者是想查看某个会话的跟踪文件，那么查询到OS上的进程或线程号是非常重要的，因为文件的令名中，就包含这个信息，以下的语句可以查询到进程或线程号，由此就可以找到对应的文件。
SELECT p1.value||'\'||p2.value||'_ora_'||p.spid filename FROM v$process p, v$session s, v$parameter p1, v$parameter p2 WHERE p1.name = 'user_dump_dest' AND p2.name = 'db_name' AND p.addr = s.paddr AND s.audsid = USERENV ('SESSIONID'); 8、在ORACLE 9i中，可以监控索引的使用，如果没有使用到的索引，完全可以删除掉，减少DML操作时的操作。 以下就是开始索引监控与停止索引监控的脚本 set heading off
set echo off set feedback off set pages 10000 spool start_index_monitor.sql SELECT 'alter index '||owner||'.'||index_name||' monitoring usage;' FROM dba_indexes WHERE owner = USER; spool off set heading on set echo on set feedback on ------------------------------------------------ set heading off set echo off set feedback off set pages 10000 spool stop_index_monitor.sql SELECT 'alter index '||owner||'.'||index_name||' nomonitoring usage;' FROM dba_indexes WHERE owner = USER; spool off set heading on set echo on set feedback on 如果需要监控更多的用户，可以将owner=User改写成别的 监控结果在视图v$object_usage中查询 感谢fenng，他提供了一个更新版的show_space脚本 CREATE OR REPLACE PROCEDURE show_space ( p_segname IN VARCHAR2, p_owner IN VARCHAR2 DEFAULT USER, p_type IN VARCHAR2 DEFAULT 'TABLE', p_partition IN VARCHAR2 DEFAULT NULL ) -- This procedure uses AUTHID CURRENT USER so it can query DBA_* -- views using privileges from a ROLE and so it can be installed -- once per database, instead of once per user who wanted to use it. AUTHID CURRENT_USER as l_free_blks number; l_total_blocks number; l_total_bytes number; l_unused_blocks number; l_unused_bytes number; l_LastUsedExtFileId number; l_LastUsedExtBlockId number; l_LAST_USED_BLOCK number; l_segment_space_mgmt varchar2(255); l_unformatted_blocks number; l_unformatted_bytes number; l_fs1_blocks number; l_fs1_bytes number; l_fs2_blocks number; l_fs2_bytes number; l_fs3_blocks number; l_fs3_bytes number; l_fs4_blocks number; l_fs4_bytes number; l_full_blocks number; l_full_bytes number; -- Inline procedure to print out numbers nicely formatted -- with a simple label. PROCEDURE p( p_label in varchar2, p_num in number ) IS BEGIN dbms_output.put_line( rpad(p_label,40,'.') || to_char(p_num,'999,999,999,999') ); END; BEGIN -- This query is executed dynamically in order to allow this procedure -- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES -- via a role as is customary. -- NOTE: at runtime, the invoker MUST have access to these two -- views! -- This query determines if the object is an ASSM object or not. BEGIN EXECUTE IMMEDIATE 'select ts.segment_space_management FROM dba_segments seg, dba_tablespaces ts WHERE seg.segment_name = :p_segname AND (:p_partition is null or seg.partition_name = :p_partition) AND seg.owner = :p_owner AND seg.tablespace_name = ts.tablespace_name' INTO l_segment_space_mgmt USING p_segname, p_partition, p_partition, p_owner; EXCEPTION WHEN too_many_rows THEN dbms_output.put_line ( 'This must be a partitioned table, use p_partition => '); RETURN; END; -- If the object is in an ASSM tablespace, we must use this API -- call to get space information; else we use the FREE_BLOCKS -- API for the user managed segments. IF l_segment_space_mgmt = 'AUTO' THEN dbms_space.space_usage ( p_owner, p_segname, p_type, l_unformatted_blocks, l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes, l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes, l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition); p( 'Unformatted Blocks ', l_unformatted_blocks ); p( 'FS1 Blocks (0-25) ', l_fs1_blocks ); p( 'FS2 Blocks (25-50) ', l_fs2_blocks ); p( 'FS3 Blocks (50-75) ', l_fs3_blocks ); p( 'FS4 Blocks (75-100)', l_fs4_blocks ); p( 'Full Blocks ', l_full_blocks ); ELSE dbms_space.free_blocks( segment_owner => p_owner, segment_name => p_segname, segment_type => p_type, freelist_group_id => 0, free_blks => l_free_blks); p( 'Free Blocks', l_free_blks ); END IF; -- And then the unused space API call to get the rest of the -- information. dbms_space.unused_space ( segment_owner => p_owner, segment_name => p_segname, segment_type => p_type, partition_name => p_partition, total_blocks => l_total_blocks, total_bytes => l_total_bytes, unused_blocks => l_unused_blocks, unused_bytes => l_unused_bytes, LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId, LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId, LAST_USED_BLOCK => l_LAST_USED_BLOCK ); p( 'Total Blocks', l_total_blocks ); p( 'Total Bytes', l_total_bytes ); p( 'Total MBytes', trunc(l_total_bytes/1024/1024) ); p( 'Unused Blocks', l_unused_blocks ); p( 'Unused Bytes', l_unused_bytes ); p( 'Last Used Ext FileId', l_LastUsedExtFileId ); p( 'Last Used Ext BlockId', l_LastUsedExtBlockId ); p( 'Last Used Block', l_LAST_USED_BLOCK ); END; 隐含参数：
select a.ksppinm "parameter ", a.ksppdesc "descriptoin " from x$ksppi a,x$ksppcv b,x$ksppsv c where a.indx=b.indx and a.indx=c.indx and a.ksppinm like '/_%' escape '/'; 回复： DBA常用SQL语句系列，欢迎补充，在不断更新中 Check OS process id from Oracle sid
select spid from v$process where addr in ( select paddr from v$session where sid=[$sid) ] Check Oracle sid from OS process id select sid from v$session where paddr in ( select addr from v$process where spid=[$pid) ] Check current SQL in a session
select SQL_TEXT from V$SQLTEXT
where HASH_VALUE = ( select SQL_HASH_VALUE from v$session where sid = &sid) order by PIECE Checking v$session_wait select * from v$session_wait where event not like 'rdbms%' and event not like 'SQL*N%' and event not like '%timer'; Dictionary Cache Hits
SELECT sum(getmisses)/sum(gets) FROM v$rowcache;
/*It should be < 15%, otherwise Add share_pool_size*/ Check DB object name from file id and block# select owner,segment_name,segment_type
from dba_extents where file_id = [$fno and &dno between block_id and block_id + blocks – 1 ] 回复： DBA常用SQL语句系列，欢迎补充，在不断更新中 #寻找hot block
select /*+ ordered */ e.owner ||'.'|| e.segment_name segment_name, e.extent_id extent#, x.dbablk - e.block_id + 1 block#, x.tch, l.child# from sys.v$latch_children l, sys.x$bh x, sys.dba_extents e where l.name = 'cache buffers chains' and l.sleeps > &sleep_count and x.hladdr = l.addr and e.file_id = x.file# and x.dbablk between e.block_id and e.block_id + e.blocks - 1; #找出每个文件上的等待事件 select df.name,kf.count from v$datafile df,x$kcbfwait kf where (kf.indx+1)=df.file#; #找出引起等待事件的SQL语句. select sql_text from v$sqlarea a,v$session b,v$session_wait c where a.address=b.sql_address and b.sid=c.sid and c.event=[$ll] #监控共享池中哪个对象引起了大的内存分配 SELECT * FROM X$KSMLRU WHERE ksmlrsiz > 0; 判断你是从pfile启动还是spfile启动的简单方法！！！
判断你是从pfile启动还是spfile启动的简单方法！！！
select decode(count(*), 1, 'spfile', 'pfile' ) from v$spparameter where rownum=1 and isspecified='TRUE' / DECODE ------ spfile ORACLE常用技巧和脚本 ORACLE常用技巧和脚本
1.如何查看ORACLE的隐含参数？ ORACLE的显式参数，除了在INIT.ORA文件中定义的外，在svrmgrl中用"show parameter *"，可以显示。但ORACLE还有一些参数是以“_”，开头的。如我们非常熟悉的“_offline_rollback_segments”等。 这些参数可在sys.x$ksppi表中查出。 语句：“select ksppinm from x$ksppi where substr(ksppinm,1,1)='_'; ” 2.如何查看安装了哪些ORACLE组件？
进入${ORACLE_HOME}/orainst/，运行./inspdver，显示安装组件和版本号。 3.如何查看ORACLE所占用共享内存的大小？ 可用UNIX命令“ipcs”查看共享内存的起始地址、信号量、消息队列。 在svrmgrl下，用“oradebug ipc”，可看出ORACLE占用共享内存的分段和大小。 example: SVRMGR> oradebug ipc -------------- Shared memory -------------- Seg Id Address Size 1153 7fe000 784 1154 800000 419430400 1155 19800000 67108864 4.如何查看当前SQL*PLUS用户的sid和serial#? 在SQL*PLUS下，运行： “select sid, serial#, status from v$session where audsid=userenv('sessionid');” 5.如何查看当前数据库的字符集？ 在SQL*PLUS下，运行： “select userenv('language') from dual;” 或：“select userenv('lang') from dual; ” 6.如何查看数据库中某用户，正在运行什么SQL语句？ 根据MACHINE、USERNAME或SID、SERIAL#，连接表V$SESSION和V$SQLTEXT，可查出。 SQL*PLUS语句： “SELECT SQL_TEXT FROM V$SQL_TEXT T, V$SESSION S WHERE T.ADDRESS=S.SQL_ADDRESS AND T.HASH_VALUE=S.SQL_HASH_VALUE AND S.MACHINE='XXXXX' OR USERNAME='XXXXX' -- 查看某主机名，或用户名 /” 7.如何删除表中的重复记录？ 例句： DELETE FROM table_name a WHERE rowid > ( SELECT min(rowid) FROM table_name b WHERE b.pk_column_1 = a.pk_column_1 and b.pk_column_2 = a.pk_column_2 ); 8.手工临时强制改变服务器字符集
以sys或system登录系统，sql*plus运行：“create database character set us7ascii;". 有以下错误提示： * create database character set US7ASCII ERROR at line 1: ORA-01031: insufficient privileges 实际上，看v$nls_parameters，字符集已更改成功。但重启数据库后，数据库字符集又变回原来的了。 该命令可用于临时的不同字符集服务器之间数据倒换之用。 9.怎样查询每个instance分配的PCM锁的数目 用以下命令： select count(*) "Number of hashed PCM locks" from v$lock_element where bitand(flags,4)<>0 / select count(*) "Number of fine grain PCM locks" from v$lock_element where bitand(flags,4)=0 / 10. 怎么判断当前正在使用何种SQL优化方式？ 用explain plan产生EXPLAIN PLAN，检查PLAN_TABLE中ID=0的POSITION列的值。 e.g. select decode(nvl(position,-1),-1,'RBO',1,'CBO') from plan_table where id=0 / 11. 做EXPORT时，能否将DUMP文件分成多个？ ORACLE8I中EXP增加了一个参数FILESIZE，可将一个文件分成多个： EXP SCOTT/TIGER FILE=（ORDER_1.DMP,ORDER_2.DMP,ORDER_3.DMP) FILESIZE=1G TABLES=ORDER； 其他版本的ORACLE在UNIX下可利用管道和split分割： mknod pipe p split -b 2048m pipe order & #将文件分割成，每个2GB大小的，以order为前缀的文件： #orderaa,orderab,orderac,... 并将该进程放在后台。 EXP SCOTT/TIGER FILE=pipe tables=order 户如何有效地利用数据字典 用户如何有效地利用数据字典
ORACLE的数据字典是数据库的重要组成部分之一，它随着数据库的产生而产生, 随着数据库的变化而变化,
体现为sys用户下的一些表和视图。数据字典名称是大写的英文字符。 数据字典里存有用户信息、用户的权限信息、所有数据对象信息、表的约束条件、统计分析数据库的视图等。
我们不能手工修改数据字典里的信息。 很多时候，一般的ORACLE用户不知道如何有效地利用它。
dictionary 全部数据字典表的名称和解释，它有一个同义词dict
dict_column 全部数据字典表里字段名称和解释 如果我们想查询跟索引有关的数据字典时，可以用下面这条SQL语句: SQL>select * from dictionary where instr(comments,'index')>0; 如果我们想知道user_indexes表各字段名称的详细含义，可以用下面这条SQL语句: SQL>select column_name,comments from dict_columns where table_name='USER_INDEXES'; 依此类推，就可以轻松知道数据字典的详细名称和解释，不用查看ORACLE的其它文档资料了。 下面按类别列出一些ORACLE用户常用数据字典的查询使用方法。 一、用户 查看当前用户的缺省表空间
SQL>select username,default_tablespace from user_users; 查看当前用户的角色
SQL>select * from user_role_privs; 查看当前用户的系统权限和表级权限
SQL>select * from user_sys_privs; SQL>select * from user_tab_privs; |
|
|
