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 '/';
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 ]
#寻找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.如何查看当前数据库的