oracle DBA 常用監控指令碼1(轉)

oracle DBA 常用監控指令碼1(轉)
2008-02-27 20:17
一、資料庫構架體系

1、表空間的監控是一個重要的任務，我們必須時刻關心表空間的設定，是否滿足現在應用的需求，以下的語句可以查詢到表空間的詳細資訊

SELECT TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,
MAX_EXTENTS,PCT_INCREASE,MIN_EXTLEN,STATUS,
CONTENTS,LOGGING,
EXTENT_MANAGEMENT, -- Columns not available in v8.0.x
ALLOCATION_TYPE, -- Remove these columns if running
PLUGGED_IN, -- against a v8.0.x database
SEGMENT_SPACE_MANAGEMENT --use only in v9.2.x or later
FROM DBA_TABLESPACES
ORDER BY TABLESPACE_NAME;

 

2、對於某些資料檔案沒有設定為自動擴充套件的表空間來說，如果表空間滿了，就將意味著資料庫可能會因為沒有空間而停止下來。監控表空間，最主要的就是監控剩餘空間的大小或者是使用率。以下是監控表空間使用率與剩餘空間大小的語句

SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",
ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --if have tempfile
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

3、除了監控表空間的剩餘空間，有時候我們也有必要了解一下該表空間是否具有自動擴充套件空間的能力，雖然我們建議在生產系統中預先分配空間。以下語句將完成這一功能

 

SELECT T.TABLESPACE_NAME,D.FILE_NAME,
D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS
FROM DBA_TABLESPACES T,
DBA_DATA_FILES D
WHERE T. TABLESPACE_NAME =D. TABLESPACE_NAME
ORDER BY TABLESPACE_NAME,FILE_NAME

 

4、我相信使用字典管理的表空間的也不少吧，因為字典管理的表空間中，每個表的下一個區間的大小是不可以預料的，所以我們必須監控那些表在字典管理的表空間中的下一個區間的分配將會引起效能問題或由於是非擴充套件的表空間而導致系統停止。以下語句檢查那些表的擴充套件將引起表空間的擴充套件。

 

SELECT A.OWNER,A.TABLE_NAME,A.NEXT_EXTENT,A.TABLESPACE_NAME
FROM ALL_TABLES A,
(SELECT TABLESPACE_NAME, MAX(BYTES) BIG_CHUNK
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE F.TABLESPACE_NAME = A.TABLESPACE_NAME
AND A.NEXT_EXTENT > F.BIG_CHUNK

 

5、段的佔用空間與區間數也是很需要注意的一個問題，如果一個段的佔用空間太大，或者跨越太多的區間（在字典管理的表空間中，將有嚴重的效能影響），如果段沒有可以再分配的區間，將導致資料庫錯誤。所以，段的大小與區間監控也是一個很重要的工作

 

SELECT S.OWNER,S.SEGMENT_NAME,S.SEGMENT_TYPE,S.PARTITION_NAME,
ROUND(BYTES/(1024*1024),2) "USED_SPACE(M)",
EXTENTS USED_EXTENTS,S.MAX_EXTENTS,S.BLOCKS ALLOCATED_BLOCKS,
S.BLOCKS USED_BOLCKS,S.PCT_INCREASE,S.NEXT_EXTENT/1024 "NEXT_EXTENT(K)"
FROM DBA_SEGMENTS S
WHERE S.OWNER NOT IN ('SYS','SYSTEM')
ORDER BY Used_Extents DESC

 

6、物件的空間分配與空間利用情況，除了從各個方面的分析，如分析表，查詢rowid等方法外，其實oracle提供了一個查詢空間的包dbms_space，如果我們稍封裝一下，將是非常好用的一個東西。

CREATE OR REPLACE PROCEDURE show_space
(p_segname in varchar2,
p_type in varchar2 default 'TABLE' ,
p_owner in varchar2 default user)
AS
v_segname varchar2(100);
v_type varchar2(10);
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;
PROCEDURE p( p_label in varchar2, p_num in number )
IS
BEGIN
dbms_output.put_line( rpad(p_label,40,'.')|| p_num );
END;
BEGIN
v_segname := upper(p_segname);
v_type := p_type;
if (p_type = 'i' or p_type = 'I') then
v_type := 'INDEX';
end if;
if (p_type = 't' or p_type = 'T') then
v_type := 'TABLE';
end if;
if (p_type = 'c' or p_type = 'C') then
v_type := 'CLUSTER';
end if;
--以下部分不能用於ASSM
dbms_space.free_blocks
( segment_owner => p_owner,
segment_name => v_segname,
segment_type => v_type,
freelist_group_id => 0,
free_blks => l_free_blks );
--以上部分不能用於ASSM
dbms_space.unused_space
( segment_owner => p_owner,
segment_name => v_segname,
segment_type => v_type,
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( 'Free Blocks', l_free_blks );
p( 'Total Blocks', l_total_blocks );
p( 'Total Bytes', l_total_bytes );
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;

 

執行結果將如下所示

SQL> set serveroutput on;
SQL> exec show_space('test');
Free Blocks.............................1
Total Blocks............................8
Total Bytes.............................65536
Unused Blocks...........................6
Unused Bytes............................49152
Last Used Ext FileId....................1
Last Used Ext BlockId...................48521
Last Used Block.........................2
PL/SQL procedure successfully completed

 

 

8、資料庫的索引如果有比較頻繁的Delete操作，將可能導致索引產生很多碎片，所以，在有的時候，需要對所有的索引重新REBUILD，以便合併索引塊，減少碎片，提高查詢速度。

SQL> set heading off
SQL> set feedback off
SQL> spool d:index.sql
SQL> SELECT 'alter index ' || index_name || ' rebuild '
||'tablespace INDEXES storage(initial 256K next 256K pctincrease 0);'
FROM all_indexes
WHERE ( tablespace_name != 'INDEXES'
OR next_extent != ( 256 * 1024 )
)
AND wner = USER
SQL>spool off

 

這個時候，我們開啟spool出來的檔案，就可以直接執行了。

9、表的主鍵是必要的，沒有主鍵的表可以說是不符合設計規範的，所以我們需要監控表是否有主鍵

SELECT table_name
FROM all_tables
WHERE wner = USER
MINUS
SELECT table_name
FROM all_constraints
WHERE wner = 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 wner = 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 wner = 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 '/';

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.如何檢視當前資料庫的字符集？
在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;

二、表

檢視使用者下所有的表
SQL>select * from user_tables;

檢視名稱包含log字元的表
SQL>select object_name,object_id from user_objects
where instr(object_name,'LOG')>0;

檢視某表的建立時間
SQL>select object_name,created from user_objects where object_name=upper('&table_name');

檢視某表的大小
SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments
where segment_name=upper('&table_name');

檢視放在ORACLE的記憶體區裡的表
SQL>select table_name,cache from user_tables where instr(cache,'Y')>0;

三、索引

檢視索引個數和類別
SQL>select index_name,index_type,table_name from user_indexes order by table_name;

檢視索引被索引的欄位
SQL>select * from user_ind_columns where index_name=upper('&index_name');

檢視索引的大小
SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments
where segment_name=upper('&index_name');

四、序列號

檢視序列號，last_number是當前值
SQL>select * from user_sequences;

五、檢視

檢視檢視的名稱
SQL>select view_name from user_views;

檢視建立檢視的select語句
SQL>set view_name,text_length from user_views;
SQL>set long 2000; 說明：可以根據檢視的text_length值設定set long 的大小
SQL>select text from user_views where view_name=upper('&view_name');

六、同義詞

檢視同義詞的名稱
SQL>select * from user_synonyms;

七、約束條件

檢視某表的約束條件
SQL>select constraint_name, constraint_type,search_condition, r_constraint_name
from user_constraints where table_name = upper('&table_name');

SQL>select c.constraint_name,c.constraint_type,cc.column_name
from user_constraints c,user_cons_columns cc
where c.owner = upper('&table_owner') and c.table_name = upper('&table_name')
and c.owner = cc.owner and c.constraint_name = cc.constraint_name
order by cc.position;

八、儲存函式和過程

檢視函式和過程的狀態
SQL>select object_name,status from user_objects where object_type='FUNCTION';
SQL>select object_name,status from user_objects where object_type='PROCEDURE';

檢視函式和過程的原始碼
SQL>select text from all_source where wner=user and name=upper('&plsql_name');

九、觸發器

檢視觸發器

set long 50000;
set heading off;
set pagesize 2000;

select
'create or replace trigger "' ||
trigger_name || '"' || chr(10)||
decode( substr( trigger_type, 1, 1 ),
'A', 'AFTER', 'B', 'BEFORE', 'I', 'INSTEAD OF' ) ||
chr(10) ||
triggering_event || chr(10) ||
'ON "' || table_owner || '"."' ||
table_name || '"' || chr(10) ||
decode( instr( trigger_type, 'EACH ROW' ), 0, null,
'FOR EACH ROW' ) || chr(10) ,
trigger_body
from user_triggers;

--分析資料庫效能的SQL

--用於檢視哪些例項的哪些操作使用了大量的臨時段

SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
operation_type OPERATION,trunc(EXPECTED_SIZE/1024) ESIZE,
trunc(ACTUAL_MEM_USED/1024) MEM, trunc(MAX_MEM_USED/1024) "MAX MEM",
NUMBER_PASSES PASS, trunc(TEMPSEG_SIZE/1024) TSIZE
FROM V$SQL_WORKAREA_ACTIVE
ORDER BY 1,2;
---查詢有熱塊查詢的SQL語句
select hash_value
from v$sqltext a,
(select distinct a.owner,a.segment_name,a.segment_type from
dba_extents a,
(select dbarfil,dbablk
from (select dbarfil,dbablk
from x$bh order by tch desc) where rownum < 11) b
where a.RELATIVE_FNO = b.dbarfil
and a.BLOCK_ID <= b.dbablk and a.block_id + a.blocks > b.dbablk) b
where a.sql_text like '%'||b.segment_name||'%' and b.segment_type = 'TABLE'
order by a.hash_value,a.address,a.piece;
--全表掃描
select opname,target,b.num_rows,b.tablespace_name,count(target) from v$session_longops a,all_all_tables b
where a.TARGET=b.owner||'.'||b.table_name
having count(target)>10 group by opname,target,b.num_rows,b.tablespace_name
--檢視磁碟排序和快取排序次數
select to_char(sn.snap_time,'yyyy-mm-dd hh24') time_,
avg(newmen.value - oldmen.value) sorts_memeory,
avg(newdsk.value - olddsk.value) disk_sort
from stats$sysstat oldmen,
stats$sysstat newmen,
stats$sysstat newdsk,
stats$sysstat olddsk,
stats$snapshot sn
where newdsk.snap_id=sn.snap_id
and olddsk.snap_id=sn.snap_id-1
and newmen.snap_id=sn.snap_id
and newdsk.snap_id=sn.snap_id -1
and oldmen.name='sorts (memory)'
and newmen.name='sorts (memory)'
and olddsk.name='sorts (disk)'
and newdsk.name='sorts (disk)'
group by to_char(sn.snap_time,'yyyy-mm-dd hh24')
--執行最慢的前10個SQL？？？
select * from (
select
to_char(snap_time,'dd Mon HH24:mi:ss') mydate,
executions exec,
loads loads,
parse_calls parse,
disk_reads reads,
buffer_gets gets,
rows_processed rows_proc,
sorts sorts,
sql_text,
hash_value
from
perfstat.stats$sql_summary sql,
perfstat.stats$snapshot sn
where
sql.snap_id >
(select min(snap_id) min_snap
from stats$snapshot where snap_time > sysdate-$days_back)
and
sql.snap_id = sn.snap_id
order by $sortskey desc) tt where rownum<11;
--SQL快取池的命中率查詢（pinhitratio，gethitratio應該大於90%以上）

select namespace,gethitratio,pinhitratio,reloads,invalidations
from v$librarycache
where namespace in ('SQL AREA','TABLE/PROCEDURE','BODY','TRIGGER')

--資料庫的常規引數我就不說了，除了V$parameter中的常規引數外，ORACLE還有大量的隱含引數，下面的語句就可以查詢到資料庫的所有隱含引數以及其值與引數的描述。
SELECT NAME
,VALUE
,decode(isdefault, 'TRUE','Y','N') as "Default"
,decode(ISEM,'TRUE','Y','N') as SesMod
,decode(ISYM,'IMMEDIATE', 'I',
'DEFERRED', 'D',
'FALSE', 'N') as SysMod
,decode(IMOD,'MODIFIED','U',
'SYS_MODIFIED','S','N') as Modified
,decode(IADJ,'TRUE','Y','N') as Adjusted
,description
FROM ( --GV$SYSTEM_PARAMETER
SELECT x.inst_id as instance
,x.indx+1
,ksppinm as NAME
,ksppity
,ksppstvl as VALUE
,ksppstdf as isdefault
,decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE') as ISEM
,decode(bitand(ksppiflg/65536,3),
1,'IMMEDIATE',2,'DEFERRED','FALSE') as ISYM
,decode(bitand(ksppstvf,7),1,'MODIFIED','FALSE') as IMOD
,decode(bitand(ksppstvf,2),2,'TRUE','FALSE') as IADJ
,ksppdesc as DESCRIPTION
FROM x$ksppi x
,x$ksppsv y
WHERE x.indx = y.indx
AND substr(ksppinm,1,1) = '_'
AND x.inst_id = USERENV('Instance')
)
ORDER BY NAME
--想知道現在哪個使用者正在利用臨時段嗎？這個語句將告訴你哪個使用者正在利用臨時段。

SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,
a.username, a.osuser, a.status,c.sql_text
FROM v$session a,v$sort_usage b, v$sql c
WHERE a.saddr = b.session_addr
AND a.sql_address = c.address(+)
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;
--檢視磁碟碎片
select tablespace_name,sqrt(max(blocks)/sum(blocks))*
(100/sqrt(sqrt(count(blocks)))) FSFI
from dba_free_space
group by tablespace_name order by 1
1.檢視錶空間的名稱及大小
select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;

2.檢視錶空間物理檔案的名稱及大小
select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;

3.檢視回滾段名稱及大小
select segment_name, tablespace_name, r.status,
(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,
max_extents, v.curext CurExtent
From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+)
order by segment_name
15。耗資源的程式（top session)
select s.schemaname schema_name, decode(sign(48 - command), 1,
to_char(command), 'Action Code #' || to_char(command) ) action, status
session_status, s.osuser os_user_name, s.sid, p.spid , s.serial# serial_num,
nvl(s.username, ' Oracle process ') user_name, s.terminal terminal,
s.program program, st.value criteria_value from v$sesstat st, v$session s , v$process p
where st.sid = s.sid and st.statistic# = to_number('38') and ('ALL' = 'ALL'
or s.status = 'ALL') and p.addr = s.paddr order by st.value desc, p.spid asc, s.username asc, s.osuser asc

16。檢視鎖（lock）情況
select /*+ RULE */ ls.osuser os_user_name, ls.username user_name,
decode(ls.type, 'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock', 'TX',
'Transaction enqueue lock', 'UL', 'User supplied lock') lock_type,
o.object_name object, decode(ls.lmode, 1, null, 2, 'Row Share', 3,
'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', null)
lock_mode, o.owner, ls.sid, ls.serial# serial_num, ls.id1, ls.id2
from sys.dba_objects o, ( select s.osuser, s.username, l.type,
l.lmode, s.sid, s.serial#, l.id1, l.id2 from v$session s,
v$lock l where s.sid = l.sid ) ls where o.object_id = ls.id1 and o.owner
<> 'SYS' order by o.owner, o.object_name
--檢視低效率的SQL語句
SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS>0
AND BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 DESC

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16486600/viewspace-521257/，如需轉載，請註明出處，否則將追究法律責任。