ORACLE技術文件

star_guan2008發表於2008-05-26
Oracle

如何開啟/關閉歸檔
如果開啟歸檔，請保證log_archive_start=true開啟自動歸檔，否則只能手工歸檔，如果是關閉了歸檔，則設定該引數為false
注意：如果是OPS/RAC環境，需要先把parallel_server = true註釋掉，然後執行如下步驟，最後用這個引數重新啟動
1、開啟歸檔
a. 關閉資料庫shutdown immediate
b. startup mount
c. alter database archivelog
d. alter database opne
2、禁止歸檔
a. 關閉資料庫shutdown immediate
b. startup mount
c. alter database noarchivelog
d. alter database open
歸檔資訊可以通過如下語句檢視
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination E:oracleora92databasearchive
Oldest online log sequence 131
Next log sequence to archive 133
Current log sequence 133
[Q]怎樣設定定時歸檔
[A]9i以上版本，保證歸檔的最小間隔不超過n秒
設定Archive_lag_target = n
單位：秒 範圍：0~7200
[Q]不同版本怎麼匯出/匯入
[A]匯出用低版本，匯入用當前版本
如果版本跨越太大，需要用到中間版本過渡
[Q]不同的字符集之前怎麼導資料
[A]a.前條件是保證匯出/匯入符合其他字符集標準，如客戶環境與資料庫字符集一致。
b.修改dmp檔案的2、3位元組為目標資料庫的字符集，注意要換成十六進位制。
參考函式（以下函式中的ID是十進位制的）：
nls_charset_name 根據字符集ID獲得字符集名稱
nls_charset_id 根據字符集名稱獲得字符集ID
[Q]怎麼樣備份控制檔案
[A]再線備份為一個二進位制的檔案
alter database backup controlfile to '$BACKUP_DEPT/controlfile.000' [reuse];
備份為文字檔案方式
alter database backup controlfile to trace [resetlogs|noresetlogs];
[Q]控制檔案損壞如何恢復
[A]1、如果是損壞單個控制檔案
只需要關閉資料庫，拷貝一個好的資料檔案覆蓋掉壞的資料檔案即可
或者是修改init.ora檔案的相關部分
2、如果是損失全部控制檔案，則需要建立控制檔案或從備份恢復
建立控制檔案的指令碼可以通過alter database backup controlfile to trace獲取。
[Q]怎麼樣熱備份一個表空間
[A]Alter tablespace 名稱 begin backup;
host cp 這個表空間的資料檔案 目的地;
Alter tablespace 名稱 end backup;
如果是備份多個表空間或整個資料庫，只需要一個一個表空間的操作下來就可以了。
[Q]怎麼快速得到整個資料庫的熱備指令碼
[A]可以寫一段類似的指令碼
SQL>set serveroutput on
begin
dbms_output.enable(10000);
for bk_ts in (select distinct t.ts#,t.name from v$tablespace t,v$datafile d where t.ts#=d.ts#) loop
dbms_output.put_line('--'||bk_ts.name);
dbms_output.put_line('alter tablespace '||bk_ts.name||' begin backup;');
for bk_file in (select file#,name from v$datafile where ts#=bk_ts.ts#) loop
dbms_output.put_line('host cp '||bk_file.name||' $BACKUP_DEPT/');
end loop;
dbms_output.put_line('alter tablespace '||bk_ts.name||' end backup;');
end loop;
end;
/
[Q]丟失一個資料檔案，但是沒有備份，怎麼樣開啟資料庫
[A]如果沒有備份只能是刪除這個資料檔案了，會導致相應的資料丟失。
SQL>startup mount
--ARCHIVELOG模式命令
SQL>Alter database datafile 'file name' offline;
--NOARCHIVELOG模式命令
SQL>Alter database datafile 'file name' offline drop;
SQLl>Alter database open;
注意：該資料檔案不能是系統資料檔案
[Q]丟失一個資料檔案，沒有備份但是有該資料檔案建立以來的歸檔怎麼恢復
[A]保證如下條件
a. 不能是系統資料檔案
b. 不能丟失控制檔案
如果滿足以上條件，則
SQL>startup mount
SQL>Alter database create datafile 'file name' as 'file name' size ... reuse;
SQL>recover datafile n; -檔案號
或者
SQL>recover datafile 'file name';
或者
SQL>recover database;
SQL>Alter database open;
[Q]聯機日誌損壞如何恢復
[A]1、如果是非當前日誌而且歸檔，可以使用
Alter database clear logfile group n來建立一個新的日誌檔案
如果該日誌還沒有歸檔，則需要用
Alter database clear unarchived logfile group n
2、如果是當前日誌損壞，一般不能clear，則可能意味著丟失資料
如果有備份，可以採用備份進行不完全恢復
如果沒有備份，可能只能用_allow_resetlogs_corruption=true來進行強制恢復了，但是，這樣的方法是不建議的，最好在有Oracle support的指導下進行。
[Q]怎麼樣建立RMAN恢復目錄
[A]首先，建立一個資料庫使用者，一般都是RMAN，並給予recovery_catalog_owner角色許可權
sqlplus sys
SQL> create user rman identified by rman;
SQL> alter user rman default tablespace tools temporary tablespace temp;
SQL> alter user rman quota unlimited on tools;
SQL> grant connect, resource, recovery_catalog_owner to rman;
SQL> exit;
然後，用這個使用者登入，建立恢復目錄
rman catalog rman/rman
RMAN> create catalog tablespace tools;
RMAN> exit;
最後，你可以在恢復目錄註冊目標資料庫了
rman catalog rman/rman target backdba/backdba
RMAN> register database;
[Q]怎麼樣在恢復的時候移動資料檔案，恢復到別的地點
[A]給一個RMAN的例子
run {
set until time 'Jul 01 1999 00:05:00';
allocate channel d1 type disk;
set newname for datafile '/u04/oracle/prod/sys1prod.dbf'
to '/u02/oracle/prod/sys1prod.dbf';
set newname for datafile '/u04/oracle/prod/usr1prod.dbf'
to '/u02/oracle/prod/usr1prod.dbf';
set newname for datafile '/u04/oracle/prod/tmp1prod.dbf'
to '/u02/oracle/prod/tmp1prod.dbf';
restore controlfile to '/u02/oracle/prod/ctl1prod.ora';
replicate controlfile from '/u02/oracle/prod/ctl1prod.ora';
restore database;
sql "alter database mount";
switch datafile all;
recover database;
sql "alter database open resetlogs";
release channel d1;
}
[Q]怎麼從備份片（backuppiece）中恢復(restore)控制檔案與資料檔案
[A]可以使用如下方法，在RMAN中恢復備份片的控制檔案
restore controlfile from backuppiecefile;
如果是9i的自動備份，可以採用如下的方法
restore controlfile from autobackup;
但是，如果控制檔案全部丟失，需要指定DBID，如SET DBID=?
自動備份控制檔案的預設格式是%F，這個格式的形式為
c-IIIIIIIIII-YYYYMMDD-QQ，其中IIIIIIIIII就是DBID
至於恢復(restore)資料檔案，oracle 816開始有個包dbms_backup_restore
在 nomount 狀態下就可以執行，可以讀 815甚至之前的備份片，讀出來的檔案用於恢復
可以在SQLPLUS中執行，如下
SQL>startup nomount
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype := dbms_backup_restore.deviceallocate('', params=>'');
6 dbms_backup_restore.restoresetdatafile;
7 dbms_backup_restore.restorecontrolfileto('E:Oracleoradatapennycontrol01.ctl');
8 dbms_backup_restore.restoreDataFileto(1,'E:Oracleoradatapennysystem01.dbf');
9 dbms_backup_restore.restoreDataFileto(2,'E:OracleoradatapennyUNDOTBS01.DBF');
10 dbms_backup_restore.restoreDataFileto(3,'E:ORACLEORADATAPENNYUSERS01.DBF');
11 dbms_backup_restore.restorebackuppiece('D:orabakBACKUP_1_4_04F4IAJT.PENNY',done=>done);
12 END;
13 /
PL/SQL 過程已成功完成。
SQL> alter database mount;
[Q]Rman的format格式中的%s類似的東西代表什麼意義
[A]可以參考如下
%c 備份片的拷貝數
%d 資料庫名稱
%D 位於該月中的第幾天 (DD)
%M 位於該年中的第幾月 (MM)
%F 一個基於DBID唯一的名稱,這個格式的形式為c-IIIIIIIIII-YYYYMMDD-QQ,其中IIIIIIIIII為該資料庫的DBID，YYYYMMDD為日期，QQ是一個1-256的序列
%n 資料庫名稱，向右填補到最大八個字元
%u 一個八個字元的名稱代表備份集與建立時間
%p 該備份集中的備份片號，從1開始到建立的檔案數
%U 一個唯一的檔名，代表%u_%p_%c
%s 備份集的號
%t 備份集時間戳
%T 年月日格式(YYYYMMDD)
[Q]執行exec dbms_logmnr_d.build('Logminer.ora','file directory')，提示下標超界，怎麼辦
[A]完整錯誤資訊如下,
SQL> exec dbms_logmnr_d.build('Logminer.ora','file directory')
BEGIN dbms_logmnr_d.build('Logminer.ora','file directory'); END;
*
ERROR 位於第 1 行:
ORA-06532: 下標超出限制
ORA-06512: 在"SYS.DBMS_LOGMNR_D", line 793
ORA-06512: 在line 1
解決辦法為：
1.編輯位於"$ORACLE_HOME/rdbms/admin"目錄下的檔案"dbmslmd.sql"
改變行:
TYPE col_desc_array IS VARRAY(513) OF col_description;
為
TYPE col_desc_array IS VARRAY(700) OF col_description;
並儲存檔案
2. 執行改變後的指令碼
SQLPLUS> Connect internal
SQLPLUS> @$ORACLE_HOME/rdbms/admin/dbmslmd.sql
3.重新編譯該包
SQLPLUS> alter package DBMS_LOGMNR_D compile body;
[Q]執行execute dbms_logmnr.start_logmnr(DictFileName=>'DictFileName')提示ORA-01843:無效的月份，這個是什麼原因
[A]我們分析start_logmnr包
PROCEDURE start_logmnr(
startScn IN NUMBER default 0 ,
endScn IN NUMBER default 0,
startTime IN DATE default TO_DATE('01-jan-1988','DD-MON-YYYY'),
endTime IN DATE default TO_DATE('01-jan-2988','DD-MON-YYYY'),
DictFileName IN VARCHAR2 default '',
Options IN BINARY_INTEGER default 0 );
可以知道，如果TO_DATE('01-jan-1988','DD-MON-YYYY')失敗，將導致以上錯誤
所以解決辦法可以為
1、Alter session set NLS_LANGUAGE=American
2、用類似如下的方法執行
execute dbms_logmnr.start_logmnr (DictFileName=> 'f:temp2TESTDICT.ora', starttime => TO_DATE(
'01-01-1988','DD-MM-YYYY'), endTime=>TO_DATE('01-01-2988','DD-MM-YYYY'));

四部分、效能調整
[Q]如果設定自動跟蹤
[A]用system登入
執行$ORACLE_HOME/rdbms/admin/utlxplan.sql建立計劃表
執行$ORACLE_HOME/sqlplus/admin/plustrce.sql建立plustrace角色
如果想計劃表讓每個使用者都能使用，則
SQL>create public synonym plan_table for plan_table;
SQL> grant all on plan_table to public;
如果想讓自動跟蹤的角色讓每個使用者都能使用，則
SQL> grant plustrace to public;
通過如下語句開啟/停止跟蹤
SET AUTOTRACE ON |OFF
| ON EXPLAIN | ON STATISTICS | TRACEONLY | TRACEONLY EXPLAIN
[Q]如果跟蹤自己的會話或者是別人的會話
[A]跟蹤自己的會話很簡單
Alter session set sql_trace true|false
Or
Exec dbms_session.set_sql_trace(TRUE);
如果跟蹤別人的會話，需要呼叫一個包
exec dbms_system.set_sql_trace_in_session(sid,serial#,true|false)
跟蹤的資訊在user_dump_dest 目錄下可以找到或通過如下指令碼獲得檔名稱（適用於Win環境，如果是unix需要做一定修改）
SELECT p1.value||''||p2.value||'_ora_'||p.spid||'.ora' 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')
最後，可以通過Tkprof來解析跟蹤檔案，如
Tkprof 原檔案 目標檔案 sys=n
[Q]怎麼設定整個資料庫系統跟蹤
[A]其實文件上的alter system set sql_trace=true是不成功的
但是可以通過設定事件來完成這個工作，作用相等
alter system set events
'10046 trace name context forever,level 1';
如果關閉跟蹤，可以用如下語句
alter system set events
'10046 trace name context off';
其中的level 1與上面的8都是跟蹤級別
level 1：跟蹤SQL語句，等於sql_trace=true
level 4：包括變數的詳細資訊
level 8：包括等待事件
level 12：包括繫結變數與等待事件
[Q]怎麼樣根據OS程式快速獲得DB程式資訊與正在執行的語句
[A]有些時候，我們在OS上操作，象TOP之後我們得到的OS程式，怎麼快速根據OS資訊獲得DB資訊呢？
我們可以編寫如下指令碼：
$more whoit.sh
#!/bin/sh
sqlplus /nolog 100,cascade=> TRUE);
dbms_stats.gather_table_stats(User,TableName,degree => 4,cascade => true);
這是對命令與工具包的一些總結
1、對於分割槽表，建議使用DBMS_STATS，而不是使用Analyze語句。
a) 可以並行進行，對多個使用者，多個Table
b) 可以得到整個分割槽表的資料和單個分割槽的資料。
c) 可以在不同級別上Compute Statistics：單個分割槽，子分割槽，全表，所有分割槽
d) 可以倒出統計資訊
e) 可以使用者自動收集統計資訊
2、DBMS_STATS的缺點
a) 不能Validate Structure
b) 不能收集CHAINED ROWS, 不能收集CLUSTER TABLE的資訊，這兩個仍舊需要使用Analyze語句。
c) DBMS_STATS 預設不對索引進行Analyze，因為預設Cascade是False，需要手工指定為True
3、對於oracle 9裡面的External Table，Analyze不能使用，只能使用DBMS_STATS來收集資訊。
[Q]怎麼樣快速重整索引
[A]通過rebuild語句，可以快速重整或移動索引到別的表空間
rebuild有重建整個索引數的功能，可以在不刪除原始索引的情況下改變索引的儲存引數
語法為
alter index index_name rebuild tablespace ts_name
storage(……);
如果要快速重建整個使用者下的索引，可以用如下指令碼，當然，需要根據你自己的情況做相應修改
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
另外一個合併索引的語句是
alter index index_name coalesce，這個語句僅僅是合併索引中同一級的leaf block
消耗不大，對於有些索引中存在大量空間浪費的情況下，有一些作用。
[Q]如何使用Hint提示
[A] 在select/delete/update後寫/*+ hint */
如 select /*+ index(TABLE_NAME INDEX_NAME) */ col1...
注意/*和+之間不能有空格
如用hint指定使用某個索引
select /*+ index(cbotab) */ col1 from cbotab;
select /*+ index(cbotab cbotab1) */ col1 from cbotab;
select /*+ index(a cbotab1) */ col1 from cbotab a;
其中
TABLE_NAME是必須要寫的，且如果在查詢中使用了表的別名，在hint也要用表的別名來代替表名;
INDEX_NAME可以不必寫，Oracle會根據統計值選一個索引;
如果索引名或表名寫錯了，那這個hint就會被忽略;
[Q]怎麼樣快速複製表或者是插入資料
[A]快速複製表可以指定Nologging選項
如：Create table t1 nologging
as select * from t2;
快速插入資料可以指定append提示，但是需要注意
noarchivelog模式下，預設用了append就是nologging模式的。
在archivelog下，需要把表設定程Nologging模式。
如insert /*+ append */ into t1
select * from t2
注意：如果在9i環境中並設定了FORCE LOGGING，則以上操作是無效的，並不會加快，當然，可以通過如下語句設定為NO FORCE LOGGING。
Alter database no force logging;
是否開啟了FORCE LOGGING，可以用如下語句檢視
SQL> select force_logging from v$database;
[Q]怎麼避免使用特定索引
[A]在很多時候，Oracle會錯誤的使用索引而導致效率的明顯下降，我們可以使用一點點技巧而避免使用不該使用的索引，如：
表test,有欄位a,b,c,d，在a,b,c上建立聯合索引inx_a(a,b,c)，在b上單獨建立了一個索引Inx_b(b)。
在正常情況下，where a=? and b=? and c=?會用到索引inx_a，
where b=?會用到索引inx_b
但是，where a=? and b=? and c=? group by b會用到哪個索引呢？在分析資料不正確（很長時間沒有分析）或根本沒有分析資料的情況下，oracle往往會使用索引inx_b。通過執行計劃的分析，這個索引的使用，將大大耗費查詢時間。
當然，我們可以通過如下的技巧避免使用inx_b，而使用inx_a。
where a=? and b=? and c=? group by b||'' --如果b是字元
where a=? and b=? and c=? group by b+0 --如果b是數字
通過這樣簡單的改變，往往可以是查詢時間提交很多倍
當然，我們也可以使用no_index提示，相信很多人沒有用過，也是一個不錯的方法：
select /*+ no_index(t,inx_b) */ * from test t
where a=? and b=? and c=? group by b
[Q]Oracle什麼時候會使用跳躍式索引掃描
[A]這是9i的一個新特性跳躍式索引掃描(Index Skip Scan).
例如表有索引index(a,b,c)，當查詢條件為
where b=?的時候，可能會使用到索引index(a,b,c)
如，執行計劃中出現如下計劃：
INDEX (SKIP SCAN) OF 'TEST_IDX' (NON-UNIQUE)
Oracle的優化器(這裡指的是CBO)能對查詢應用Index Skip Scans至少要有幾個條件:
1 優化器認為是合適的。
2 索引中的前導列的唯一值的數量能滿足一定的條件（如重複值很多）。
3 優化器要知道前導列的值分佈(通過分析/統計表得到)。
4 合適的SQL語句
等。
[Q]怎麼樣建立使用虛擬索引
[A]可以使用nosegment選項，如
create index virtual_index_name on table_name(col_name) nosegment;
如果在哪個session需要測試虛擬索引，可以利用隱含引數來處理
alter session set "_use_nosegment_indexes" = true;
就可以利用explain plan for select ……來看虛擬索引的效果
利用@$ORACLE_HOME/rdbms/admin/utlxpls檢視執行計劃
最後，根據需要，我們可以刪除虛擬索引，如普通索引一樣
drop index virtual_index_name;
注意：虛擬索引並不是物理存在的，所以虛擬索引並不等同於物理索引，不要用自動跟蹤去測試虛擬索引，因為那是實際執行的效果，是用不到虛擬索引的。
[Q]怎樣監控無用的索引
[A]Oracle 9i以上，可以監控索引的使用情況，如果一段時間內沒有使用的索引，一般就是無用的索引
語法為：
開始監控：alter index index_name monitoring usage;
檢查使用狀態：select * from v$object_usage;
停止監控：alter index index_name nomonitoring usage;
當然，如果想監控整個使用者下的索引，可以採用如下的指令碼：
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
[Q]怎麼樣能固定我的執行計劃
[A]可以使用OUTLINE來固定SQL語句的執行計劃
用如下語句可以建立一個OUTLINE
Create oe replace outline OutLn_Name on
Select Col1,Col2 from Table
where ……
如果要刪除Outline，可以採用
Drop Outline OutLn_Name;
對於已經建立了的OutLine，存放在OUTLN使用者的OL$HINTS表下面
對於有些語句，你可以使用update outln.ol$hints來更新outline
如update outln.ol$hints(ol_name,'TEST1','TEST2','TEST2','TEST1)
where ol_name in ('TEST1','TEST2');
這樣，你就把Test1 OUTLINE與Test2 OUTLINE互換了
如果想利用已經存在的OUTLINE，需要設定以下引數
Alter system/session set Query_rewrite_enabled = true
Alter system/session set use_stored_outlines = true
[Q]v$sysstat中的class分別代表什麼
[A]統計類別
1 代表事例活動
2 代表Redo buffer活動
4 代表鎖
8 代表資料緩衝活動
16 代表OS活動
32 代表並行活動
64 代表表訪問
128 代表除錯資訊
[Q]怎麼殺掉特定的資料庫會話
[A] Alter system kill session 'sid,serial#';
或者
alter system disconnect session 'sid,serial#' immediate;
在win上，還可以採用oracle提供的orakill殺掉一個執行緒（其實就是一個Oracle程式）
在Linux/Unix上，可以直接利用kill殺掉資料庫程式對應的OS程式
[Q]怎麼快速查詢鎖與鎖等待
[A]資料庫的鎖是比較耗費資源的，特別是發生鎖等待的時候，我們必須找到發生等待的鎖，有可能的話，殺掉該程式。
這個語句將查詢到資料庫中所有的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
如果發生了鎖等待，我們可能更想知道是誰鎖了表而引起誰的等待
以下的語句可以查詢到誰鎖了表，而誰在等待。
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
[Q] 如何有效的刪除一個大表(extent數很多的表)
[A] 一個有很多(100k)extent的表，如果只是簡單地用drop table的話，會很大量消耗CPU（Oracle要對fet$、uet$資料字典進行操作），可能會用上幾天的時間，較好的方法是分多次刪除extent，以減輕這種消耗：
1. truncate table big-table reuse storage;
2. alter table big-table deallocate unused keep 2000m ( 原來大小的n-1/n);
3. alter table big-table deallocate unused keep 1500m ;
....
4. drop table big-table;
[Q]如何收縮臨時資料檔案的大小
[A]9i以下版本採用
ALTER DATABASE DATAFILE 'file name' RESIZE 100M類似的語句
9i以上版本採用
ALTER DATABASE TEMPFILE 'file name' RESIZE 100M
注意，臨時資料檔案在使用時，一般不能收縮，除非關閉資料庫或斷開所有會話，停止對臨時資料檔案的使用。
[Q]怎麼清理臨時段
[A]可以使用如下辦法
1、 使用如下語句檢視一下認誰在用臨時段
SELECT username,sid,serial#,sql_address,machine,program,
tablespace,segtype, contents
FROM v$session se,v$sort_usage su
WHERE se.saddr=su.session_addr
2、 那些正在使用臨時段的程式
SQL>Alter system kill session 'sid,serial#';
3、把TEMP表空間回縮一下
SQL>Alter tablespace TEMP coalesce;
還可以使用診斷事件
1、 確定TEMP表空間的ts#
SQL> select ts#, name FROM v$tablespace;
TS# NAME
-----------------------
0 SYSYEM
1 RBS
2 USERS
3* TEMP
……
2、 執行清理操作
alter session set events 'immediate trace name DROP_SEGMENTS level TS#+1'
說明：
temp表空間的TS# 為 3*, So TS#+ 1= 4
如果想清除所有表空間的臨時段，則
TS# = 2147483647
[Q]怎麼樣dump資料庫內部結構，如上面顯示的控制檔案的結構
[A]常見的有
1、分析資料檔案塊，轉儲資料檔案n的塊m
alter system dump datafile n block m
2、分析日誌檔案
alter system dump logfile logfilename;
3、分析控制檔案的內容
alter session set events 'immediate trace name CONTROLF level 10'
4、分析所有資料檔案頭
alter session set events 'immediate trace name FILE_HDRS level 10'
5、分析日誌檔案頭
alter session set events 'immediate trace name REDOHDR level 10'
6、分析系統狀態，最好每10分鐘一次，做三次對比
alter session set events 'immediate trace name SYSTEMSTATE level 10'
7、分析程式狀態
alter session set events 'immediate trace name PROCESSSTATE level 10'
8、分析Library Cache的詳細情況
alter session set events 'immediate trace name library_cache level 10'
[Q]如何獲得所有的事件程式碼
[A] 事件程式碼範圍一般從10000 to 10999，以下列出了這個範圍的事件程式碼與資訊
SET SERVEROUTPUT ON
DECLARE
err_msg VARCHAR2(120);
BEGIN
dbms_output.enable (1000000);
FOR err_num IN 10000..10999
LOOP
err_msg := SQLERRM (-err_num);
IF err_msg NOT LIKE '%Message '||err_num||' not found%' THEN
dbms_output.put_line (err_msg);
END IF;
END LOOP;
END;
/
在Unix系統上，事件資訊放在一個文字檔案裡
$ORACLE_HOME/rdbms/mesg/oraus.msg
可以用如下指令碼檢視事件資訊
event=10000
while [ $event -ne 10999 ]
do
event=`expr $event + 1`
oerr ora $event
done
對於已經確保的/正在跟蹤的事件，可以用如下指令碼獲得
SET SERVEROUTPUT ON
DECLARE
l_level NUMBER;
BEGIN
FOR l_event IN 10000..10999
LOOP
dbms_system.read_ev (l_event,l_level);
IF l_level > 0 THEN
dbms_output.put_line ('Event '||TO_CHAR (l_event)||
' is set at level '||TO_CHAR (l_level));
END IF;
END LOOP;
END;
/
[Q]什麼是STATSPACK，我怎麼使用它？
[A]Statspack是Oracle 8i以上提供的一個非常好的效能監控與診斷工具，基本上全部包含了BSTAT/ESTAT的功能，更多的資訊
可以參考附帶文件$ORACLE_HOME/rdbms/admin/spdoc.txt。
安裝Statspack:
cd $ORACLE_HOME/rdbms/admin
sqlplus "/ as sysdba" @spdrop.sql -- 解除安裝，第一次可以不需要
sqlplus "/ as sysdba" @spcreate.sql -- 需要根據提示輸入表空間名
使用Statspack:
sqlplus perfstat/perfstat
exec statspack.snap; -- 進行資訊收集統計，每次執行都將產生一個快照號
-- 獲得快照號，必須要有兩個以上的快照，才能生成報表
select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT;
@spreport.sql -- 輸入需要檢視的開始快照號與結束快照號
其他相關指令碼s:
spauto.sql - 利用dbms_job提交一個作業，自動的進行STATPACK的資訊收集統計
sppurge.sql - 清除一段範圍內的統計資訊，需要提供開始快照與結束快照號
sptrunc.sql - 清除(truncate)所有統計資訊

ORACLE（2）

orafaq--（ ORACLE構架體系 ）
發表人:lijietz | 發表時間: 2005年四月23日, 22:39

二部分、ORACLE構架體系
[Q]ORACLE的有那些資料型別
[A]常見的資料型別有
CHAR固定長度字元域，最大長度可達2000個位元組
NCHAR多位元組字符集的固定長度字元域，長度隨字符集而定，最多為2000個字元或2000個位元組
VARCHAR2可變長度字元域，最大長度可達4000個字元
NVARCHAR2多位元組字符集的可變長度字元域，長度隨字符集而定，最多為4000個字元或4000個位元組
DATE用於儲存全部日期的固定長度(7個位元組)字元域，時間作為日期的一部分儲存其中。除非
通過設定init.ora檔案的NLS_DATE_FORMAT引數來取代日期格式，否則查詢時，日期以
DD-MON-YY格式表示，如13-APR-99表示1999.4.13
NUMBER可變長度數值列，允許值為0、正數和負數。NUMBER值通常以4個位元組或更少的位元組儲存，最多21位元組
LONG可變長度字元域，最大長度可到2GB
RAW表示二進位制資料的可變長度字元域，最長為2000個位元組
LONGRAW表示二進位制資料的可變長度字元域，最長為2GB
MLSLABEL只用於TrustedOracle，這個資料型別每行使用2至5個位元組
BLOB二進位制大物件，最大長度為4GB
CLOB字元大物件，最大長度為4GB
NCLOB多位元組字符集的CLOB資料型別，最大長度為4GB
BFILE外部二進位制檔案，大小由作業系統決定
ROWID表示RowID的二進位制資料，Oracle8RowID的數值為10個位元組，在Oracle7中使用的限定
RowID格式為6個位元組
UROWID用於資料定址的二進位制資料，最大長度為4000個位元組
[Q]Oracle有哪些常見關鍵字，不能被用於物件名
[A]以8i版本為例，一般保留關鍵字不能用做物件名
ACCESS ADD ALL ALTER AND ANY AS ASC AUDIT BETWEEN BY CHAR CHECK CLUSTER COLUMN COMMENT COMPRESS CONNECT CREATE CURRENT DATE DECIMAL DEFAULT DELETE DESC DISTINCT DROP ELSE EXCLUSIVE EXISTS FILE FLOAT FOR FROM GRANT GROUP HAVING IDENTIFIED IMMEDIATE IN INCREMENT INDEX INITIAL INSERT INTEGER INTERSECT INTO IS LEVEL LIKE LOCK LONG MAXEXTENTS MINUS MLSLABEL MODE MODIFY NOAUDIT NOCOMPRESS NOT NOWAIT NULL NUMBER OF OFFLINE ON ONLINE OPTION OR ORDER PCTFREE PRIOR PRIVILEGES PUBLIC RAW RENAME RESOURCE REVOKE ROW ROWID ROWNUM ROWS SELECT SESSION SET SHARE SIZE SMALLINT START SUCCESSFUL SYNONYM SYSDATE TABLE THEN TO TRIGGER UID UNION UNIQUE UPDATE USER VALIDATE VALUES VARCHAR VARCHAR2 VIEW WHENEVER WHERE WITH
詳細資訊可以檢視v$reserved_words檢視
[Q]怎麼檢視資料庫版本
[A]select * from v$version
包含版本資訊，核心版本資訊，位數資訊(32位或64位)等
至於位數資訊，在linux/unix平臺上，可以通過file檢視，如
file $ORACLE_HOME/bin/oracle
[Q]怎麼檢視資料庫引數
[A]show parameter 引數名
如通過show parameter spfile可以檢視9i是否使用spfile檔案
或者select * from 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
[Q]怎麼樣檢視資料庫字符集
[A]資料庫伺服器字符集select * from nls_database_parameters，其來源於props$，是表示資料庫的字符集。
客戶端字符集環境select * from nls_instance_parameters,其來源於v$parameter，
表示客戶端的字符集的設定，可能是引數檔案，環境變數或者是登錄檔
會話字符集環境 select * from nls_session_parameters，其來源於v$nls_parameters，表示會話自己的設定，可能是會話的環境變數或者是alter session完成，如果會話沒有特殊的設定，將與nls_instance_parameters一致。
客戶端的字符集要求與伺服器一致，才能正確顯示資料庫的非Ascii字元。如果多個設定存在的時候，alter session>環境變數>登錄檔>引數檔案
字符集要求一致，但是語言設定卻可以不同，語言設定建議用英文。如字符集是zhs16gbk，則nls_lang可以是American_America.zhs16gbk。
[Q]怎麼樣修改字符集
[A]8i以上版本可以通過alter database來修改字符集，但也只限於子集到超集，不建議修改props$表，將可能導致嚴重錯誤。
Startup nomount;
Alter database mount exclusive;
Alter system enable restricted session;
Alter system set job_queue_process=0;
Alter database open;
Alter database character set zhs16gbk;
[Q]怎樣建立基於函式索引
[A]8i以上版本，確保
Query_rewrite_enabled=true
Query_rewrite_integrity=trusted
Compatible=8.1.0以上
Create index indexname on table (function(field));
[Q]怎麼樣移動表或表分割槽
[A]移動表的語法
Alter table tablename move
[Tablespace new_name
Storage(initial 50M next 50M
pctincrease 0 pctfree 10 pctused 50 initrans 2) nologging]
移動分割槽的語法
alter table tablename move (partition partname)
[update global indexes]
之後之後必須重建索引
Alter index indexname rebuild
如果表有Lob段，那麼正常的Alter不能移動Lob段到別的表空間，而僅僅是移動了表段，可以採用如下的方法移動Lob段
alter table tablename move
lob(lobsegname) store as (tablespace newts);
[Q]怎麼獲得當前的SCN
[A]9i以下版本
select max(ktuxescnw*power(2,32)+ktuxescnb) from x$ktuxe;
如果是9i以上版本，還可以通過以下語句獲取
select dbms_flashback.get_system_change_number from dual;
[Q]ROWID的結構與組成
[A]8以上版本的ROWID組成
OOOOOOFFFBBBBBBRRR
8以下ROWID組成（也叫受限Rowid）
BBBBBBBB.RRRR.FFFF
其中，O是物件ID，F是檔案ID，B是塊ID，R是行ID
如果我們查詢一個表的ROWID，根據其中塊的資訊，可以知道該表確切佔用了多少個塊，進而知道佔用了多少資料空間（此資料空間不等於表的分配空間）
[Q]怎麼樣獲取物件的DDL語句
[A]第三方工具就不說了主要說一下9i以上版本的dbms_metadata
1、獲得單個物件的DDL語句
set heading off
set echo off
set feedback off
set pages off
set long 90000
select dbms_metadata.get_ddl('TABLE','TABLENAME','SCAME') from dual;
如果獲取整個使用者的指令碼，可以用如下語句
select dbms_metadata.get_ddl('TABLE',u.table_name) from user_tables u;
當然，如果是索引，則需要修改相關table到index
[Q]如何建立約束的索引在別的表空間上
[A]1、先建立索引，再建立約束
2、利用如下語句建立
create table test
(c1 number constraint pk_c1_id primary key
using index tablespace useridex,
c2 varchar2(10)
) tablespace userdate;
[Q]怎麼知道那些表沒有建立主鍵
[A]一般的情況下，表的主鍵是必要的，沒有主鍵的表可以說是不符合設計規範的。
SELECT table_name
FROM User_tables t
WHERE NOT EXISTS
(SELECT table_name
FROM User_constraints c
WHERE constraint_type = 'P'
AND t.table_name=c.table_name)
其它相關資料字典解釋
user_tables 表
user_tab_columns 表的列
user_constraints 約束
user_cons_columns 約束與列的關係
user_indexes 索引
user_ind_columns 索引與列的關係
[Q]dbms_output提示緩衝區不夠，怎麼增加
[A]dbms_output.enable(20000);
另外，如果dbms_output的資訊不能顯示，
需要設定
set serveroutput on
[Q]怎麼樣修改表的列名
[A]9i以上版本可以採用rname命令
ALTER TABLE UserName.TabName
RENAME COLUMN SourceColumn TO DestColumn
9i以下版本可以採用create table …… as select * from SourceTable的方式。
另外，8i以上可以支援刪除列了
ALTER TABLE UserName.TabName
SET UNUSED (ColumnName) CASCADE CONSTRAINTS
ALTER TABLE UserName.TabName
DROP (ColumnName) CASCADE CONSTRAINTS
[Q]怎麼樣給sqlplus安裝幫助
[A]SQLPLUS的幫助必須手工安裝，shell指令碼為$ORACLE_HOME/bin/helpins
在安裝之前，必須先設定SYSTEM_PASS環境變數，如：
$ setenv SYSTEM_PASS SYSTEM/MANAGER
$ helpins
如果不設定該環境變數，將在執行指令碼的時候提示輸入環境變數
當然，除了shell指令碼，還可以利用sql指令碼安裝，那就不用設定環境變數了，但是，我們必須以system登入。
$ sqlplus system/manager
SQL> @?/sqlplus/admin/help/helpbld.sql helpus.sql
安裝之後，你就可以象如下的方法使用幫助了
SQL> help index
[Q]怎麼樣快速下載Oracle補丁
[A]我們先獲得下載伺服器地址，在http頁面上有
ftp://updates.oracle.com
然後用ftp登入，使用者名稱與密碼是metalink的使用者名稱與密碼
如我們知道了補丁號3095277 (9204的補丁集)，則
ftp> cd 3095277
250 Changed directory OK.
ftp> ls
200 PORT command OK.
150 Opening data connection for file listing.
p3095277_9204_AIX64-5L.zip
p3095277_9204_AIX64.zip
……
p3095277_9204_WINNT.zip
226 Listing complete. Data connection has been closed.
ftp: 208 bytes received in 0.02Seconds 13.00Kbytes/sec.
ftp>
知道了這個資訊，我們用用flashget，網路螞蟻就可以下載了。
新增如下連線
ftp://updates.oracle.com/3095277/p3...04_AIX64-5L.zip
或替換後面的部分為所需要的內容
注意，如果是flashget，網路螞蟻請輸入認證使用者名稱及密碼，就是你的metalink的使用者名稱與密碼！
[Q]如何移動資料檔案
[A]1、關閉資料庫，利用os拷貝
a.shutdown immediate關閉資料庫
b.在os下拷貝資料檔案到新的地點
c.Startup mount 啟動到mount下
d.Alter database rename datafile '老檔案' to '新檔案';
e.Alter database open; 開啟資料庫
2、利用Rman聯機操作
RMAN> sql "alter database datafile ''file name'' offline";
RMAN> run {
2> copy datafile 'old file location'
3> to 'new file location';
4> switch datafile ' old file location'
5> to datafilecopy ' new file location';
6> }
RMAN> sql "alter database datafile ''file name'' online";
說明：利用OS拷貝也可以聯機操作，不關閉資料庫，與rman的步驟一樣，利用rman與利用os拷貝的原理一樣，在rman中copy是拷貝資料檔案，相當於OS的cp，而switch則相當於alter database rename，用來更新控制檔案。
[Q]如果管理聯機日誌組與成員
[A]以下是常見操作，如果在OPA/RAC下注意執行緒號
增加一個日誌檔案組
Alter database add logfile [group n] '檔案全名' size 10M;
在這個組上增加一個成員
Alter database add logfile member '檔案全名' to group n;
在這個組上刪除一個日誌成員
Alter database drop logfile member '檔案全名';
刪除整個日誌組
Alter database drop logfile group n;
[Q]怎麼樣計算REDO BLOCK的大小
[A]計算方法為(redo size + redo wastage) / redo blocks written + 16
具體見如下例子
SQL> select name ,value from v$sysstat where name like '%redo%';
NAME VALUE
---------------------------------------------------------------- ----------
redo synch writes 2
redo synch time 0
redo entries 76
redo size 19412
redo buffer allocation retries 0
redo wastage 5884
redo writer latching time 0
redo writes 22
redo blocks written 51
redo write time 0
redo log space requests 0
redo log space wait time 0
redo log switch interrupts 0
redo ordering marks 0
SQL> select (19412+5884)/51 + 16 '"Redo black(byte)" from dual;
Redo black(byte)
------------------
512
[Q]控制檔案包含哪些基本內容
[A]控制檔案主要包含如下條目，可以通過dump控制檔案內容看到
DATABASE ENTRY
CHECKPOINT PROGRESS RECORDS
REDO THREAD RECORDS
LOG FILE RECORDS
DATA FILE RECORDS
TEMP FILE RECORDS
TABLESPACE RECORDS
LOG FILE HISTORY RECORDS
OFFLINE RANGE RECORDS
ARCHIVED LOG RECORDS
BACKUP SET RECORDS
BACKUP PIECE RECORDS
BACKUP DATAFILE RECORDS
BACKUP LOG RECORDS
DATAFILE COPY RECORDS
BACKUP DATAFILE CORRUPTION RECORDS
DATAFILE COPY CORRUPTION RECORDS
DELETION RECORDS
PROXY COPY RECORDS
INCARNATION RECORDS
[Q]如果發現表中有壞塊，如何檢索其它未壞的資料
[A]首先需要找到壞塊的ID（可以執行dbverify實現），假設為，假定檔案編碼為。執行下面的查詢查詢段名：
SELECT segment_name,segment_type,extent_id,block_id, blocks
from dba_extents t
where
file_id =
AND between block_id and (block_id + blocks - 1)
一旦找到壞段名稱，若段是一個表，則最好建立一個臨時表，存放好的資料。若段是索引，則刪除它，再重建。
create table good_table
as
select from bad_table where rowid not in
(select rowid
from bad_table where substr(rowid,10,6) = )
在這裡要注意8以前的受限ROWID與現在ROWID的差別。
還可以使用診斷事件10231
SQL> ALTER SYSTEM SET EVENTS '10231 trace name context forever,level 10';
建立一個臨時表good_table的表中除壞塊的資料都檢索出來
SQL>CREATE TABLE good_table as select * from bad_table;
最後關閉診斷事件
SQL> ALTER SYSTEM SET EVENTS '10231 trace name context off ';
關於ROWID的結構，還可以參考dbms_rowid.rowid_create函式。
[Q]我建立了資料庫的所有使用者，我可以刪除這些使用者嗎
[A]ORACLE資料庫建立的時候，建立了一系列預設的使用者和表空間，以下是他們的列表
·SYS/CHANGE_ON_INSTALL or INTERNAL
系統使用者，資料字典所有者，超級許可權所有者(SYSDBA)
建立指令碼：?/rdbms/admin/sql.bsq and various cat*.sql
建議建立後立即修改密碼
此使用者不能被刪除
·SYSTEM/MANAGER
資料庫預設管理使用者，擁有DBA角色許可權
建立指令碼：?/rdbms/admin/sql.bsq
建議建立後立即修改密碼
此使用者不能被刪除
·OUTLN/OUTLN
優化計劃的儲存大綱使用者
建立指令碼：?/rdbms/admin/sql.bsq
建議建立後立即修改密碼
此使用者不能被刪除
---------------------------------------------------
·SCOTT/TIGER, ADAMS/WOOD, JONES/STEEL, CLARK/CLOTH and BLAKE/PAPER.
實驗、測試使用者，含有例表EMP與DEPT
建立指令碼：?/rdbms/admin/utlsampl.sql
可以修改密碼
使用者可以被刪除，在產品環境建議刪除或鎖定
·HR/HR (Human Resources), OE/OE (Order Entry), SH/SH (Sales History).
實驗、測試使用者，含有例表EMPLOYEES與DEPARTMENTS
建立指令碼：?/demo/schema/mksample.sql
可以修改密碼
使用者可以被刪除，在產品環境建議刪除或鎖定
·DBSNMP/DBSNMP
Oracle Intelligent agent
建立指令碼：?/rdbms/admin/catsnmp.sql, called from catalog.sql
可以改變密碼--需要放置新密碼到snmp_rw.ora檔案
如果不需要Intelligent Agents，可以刪除
---------------------------------------------------
以下使用者都是可選安裝使用者,如果不需要，就不需要安裝
·CTXSYS/CTXSYS
Oracle interMedia (ConText Cartridge)管理使用者
建立指令碼：?/ctx/admin/dr0csys.sql
·TRACESVR/TRACE
Oracle Trace server
建立指令碼：?/rdbms/admin/otrcsvr.sql
·ORDPLUGINS/ORDPLUGINS
Object Relational Data (ORD) User used by Time Series, etc.
建立指令碼：?/ord/admin/ordinst.sql
·ORDSYS/ORDSYS
Object Relational Data (ORD) User used by Time Series, etc
建立指令碼：?/ord/admin/ordinst.sql
·DSSYS/DSSYS
Oracle Dynamic Services and Syndication Server
建立指令碼：?/ds/sql/dssys_init.sql
·MDSYS/MDSYS
Oracle Spatial administrator user
建立指令碼：?/ord/admin/ordinst.sql
·AURORA$ORB$UNAUTHENTICATED/INVALID
Used for users who do not authenticate in Aurora/ORB
建立指令碼：?/javavm/install/init_orb.sql called from ?/javavm/install/initjvm.sql
·PERFSTAT/PERFSTAT
Oracle Statistics Package (STATSPACK) that supersedes UTLBSTAT/UTLESTAT
建立指令碼：?/rdbms/admin/statscre.sql

oracle（PL/SQL）

如何在procedure返回結果集（zz）
發表人:lijietz | 發表時間: 2005年四月23日, 21:48

在很多時候我們需要通過bind var來提高整個DB的performance,在我們用第三次開發軟體做對結果集的查詢。我們如何在procedure中完成對結果集的查詢呢，從 oracle7.3才被支援，在9i以後又有新的變化，在9i以前要define一個type才可以。而在9i以後oracle引入了一個新的型別為 sys_refcursor，這樣就不需要我們重新定義。我們來看一個例子吧。


C:oracleora92sqlplusdemo>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on 星期六 4月 2 11:09:06 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> conn scott/tiger@vongates
已連線.
SQL> create or replace procedure getEmpByDept(in_deptNo  in emp.deptno%type,
  2                                           out_curEmp out SYS_REFCURSOR) as
  3
  4  begin
  5    open out_curEmp for
  6      SELECT * FROM emp WHERE deptno = in_deptNo ;
  7  EXCEPTION
  8    WHEN OTHERS THEN
  9      RAISE_APPLICATION_ERROR(-20101,
 10                              'Error in getEmpByDept' || SQLCODE );
 12  end getEmpByDept;
 13  /

已建立程式.

SQL> var rset refcursor;
SQL> exec getEmpByDept(10,:rset);

PL/SQL 程式順利完成.

SQL> print rset;

     EMPNO ENAME                JOB                       MGR HIREDATE      SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
      7934 MILLER               CLERK                    7782 23-1月 -82      1300                    10
      7782 CLARK                MANAGER                  7839 09-1月 -81      2450                    10
      7839 KING                 PRESIDENT                     17-11月-81      5000                    10

SQL>

根據rowid dump資料塊

根據rowid dump資料塊，可以用下面的方法實現。

SQL> SELECT DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) R_FILE_NO,
  2  DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK_NO
  3  FROM T WHERE ROWNUM = 1;

 R_FILE_NO   BLOCK_NO
---------- ----------
         6        578

一部分、SQL&PL/SQL

[Q]怎麼樣查詢特殊字元，如萬用字元%與_
[A]select * from table where name like 'A_%' escape ''
[Q]如何插入單引號到資料庫表中
[A]可以用ASCII碼處理，其它特殊字元如&也一樣，如
insert into t values('i'||chr(39)||'m'); -- chr(39)代表字元'
或者用兩個單引號表示一個
or insert into t values('I''m'); -- 兩個''可以表示一個'
[Q]怎樣設定事務一致性
[A]set transaction [isolation level] read committed; 預設語句級一致性
set transaction [isolation level] serializable;
read only; 事務級一致性
[Q]怎麼樣利用遊標更新資料
[A]cursor c1 is
select * from tablename
where name is null for update [of column]
……
update tablename set column = ……
where current of c1;
[Q]怎樣自定義異常
[A] pragma_exception_init(exception_name,error_number);
如果立即丟擲異常
raise_application_error(error_number,error_msg,true|false);
其中number從-20000到-20999，錯誤資訊最大2048B
異常變數
SQLCODE 錯誤程式碼
SQLERRM 錯誤資訊
[Q]十進位制與十六進位制的轉換
[A]8i以上版本：
to_char(100,'XX')
to_number('4D','XX')
8i以下的進位制之間的轉換參考如下指令碼
create or replace function to_base( p_dec in number, p_base in number )
return varchar2
is
l_str varchar2(255) default NULL;
l_num number default p_dec;
l_hex varchar2(16) default '0123456789ABCDEF';
begin
if ( p_dec is null or p_base is null ) then
return null;
end if;
if ( trunc(p_dec) p_dec OR p_dec 20;
[Q]怎麼樣抽取重複記錄
[A]select * from table t1 where where t1.rowed !=
(select max(rowed) from table t2
where t1.id=t2.id and t1.name=t2.name)
或者
select count(*), t.col_a,t.col_b from table t
group by col_a,col_b
having count(*)>1
如果想刪除重複記錄，可以把第一個語句的select替換為delete
[Q]怎麼樣設定自治事務
[A]8i以上版本，不影響主事務
pragma autonomous_transaction;
……
commit|rollback;
[Q]怎麼樣在過程中暫停指定時間
[A]DBMS_LOCK包的sleep過程
如：dbms_lock.sleep(5);表示暫停5秒。
[Q]怎麼樣快速計算事務的時間與日誌量
[A]可以採用類似如下的指令碼
DECLARE
start_time NUMBER;
end_time NUMBER;
start_redo_size NUMBER;
end_redo_size NUMBER;
BEGIN
start_time := dbms_utility.get_time;
SELECT VALUE INTO start_redo_size FROM v$mystat m,v$statname s
WHERE m.STATISTIC#=s.STATISTIC#
AND s.NAME='redo size';
--transaction start
INSERT INTO t1
SELECT * FROM All_Objects;
--other dml statement
COMMIT;
end_time := dbms_utility.get_time;
SELECT VALUE INTO end_redo_size FROM v$mystat m,v$statname s
WHERE m.STATISTIC#=s.STATISTIC#
AND s.NAME='redo size';
dbms_output.put_line('Escape Time:'||to_char(end_time-start_time)||' centiseconds');
dbms_output.put_line('Redo Size:'||to_char(end_redo_size-start_redo_size)||' bytes');
END;
[Q]怎樣建立臨時表
[A]8i以上版本
create global temporary tablename(column list)
on commit preserve rows; --提交保留資料 會話臨時表
on commit delete rows; --提交刪除資料 事務臨時表
臨時表是相對於會話的，別的會話看不到該會話的資料。
[Q]怎麼樣在PL/SQL中執行DDL語句
[A]1、8i以下版本dbms_sql包
2、8i以上版本還可以用
execute immediate sql;
dbms_utility.exec_ddl_statement('sql');
[Q]怎麼樣獲取IP地址
[A]伺服器(817以上)：utl_inaddr.get_host_address
客戶端：sys_context('userenv','ip_address')
[Q]怎麼樣加密儲存過程
[A]用wrap命令，如（假定你的儲存過程儲存為a.sql）
wrap iname=a.sql
PL/SQL Wrapper: Release 8.1.7.0.0 - Production on Tue Nov 27 22:26:48 2001
Copyright (c) Oracle Corporation 1993, 2000. All Rights Reserved.
Processing a.sql to a.plb
提示a.sql轉換為a.plb，這就是加密了的指令碼，執行a.plb即可生成加密了的儲存過程
[Q]怎麼樣在ORACLE中定時執行儲存過程
[A]可以利用dbms_job包來定時執行作業，如執行儲存過程，一個簡單的例子，提交一個作業：
VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno, 'ur_procedure;',SYSDATE,'SYSDATE + 1');
commit;
END;
之後，就可以用以下語句查詢已經提交的作業
select * from user_jobs;
[Q]怎麼樣從資料庫中獲得毫秒
[A]9i以上版本，有一個timestamp型別獲得毫秒，如
SQL>select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ssxff') time1,
to_char(current_timestamp) time2 from dual;
TIME1 TIME2
----------------------------- ----------------------------------------------------------------
2003-10-24 10:48:45.656000 24-OCT-03 10.48.45.656000 AM +08:00
可以看到，毫秒在to_char中對應的是FF。
8i以上版本可以建立一個如下的java函式
SQL>create or replace and compile
java source
named "MyTimestamp"
as
import java.lang.String;
import java.sql.Timestamp;
public class MyTimestamp
{
public static String getTimestamp()
{
return(new Timestamp(System.currentTimeMillis())).toString();
}
};
SQL>java created.
注：注意java的語法，注意大小寫
SQL>create or replace function my_timestamp return varchar2
as language java
name 'MyTimestamp.getTimestamp() return java.lang.String';
/
SQL>function created.
SQL>select my_timestamp,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') ORACLE_TIME from dual;
MY_TIMESTAMP ORACLE_TIME
------------------------ -------------------
2003-03-17 19:15:59.688 2003-03-17 19:15:59
如果只想獲得1/100秒(hsecs)，還可以利用dbms_utility.get_time
[Q]如果存在就更新，不存在就插入可以用一個語句實現嗎
[A]9i已經支援了，是Merge，但是隻支援select子查詢，
如果是單條資料記錄，可以寫作select …… from dual的子查詢。
語法為：
MERGE INTO table
USING data_source
ON (condition)
WHEN MATCHED THEN update_clause
WHEN NOT MATCHED THEN insert_clause;
如
MERGE INTO course c
USING (SELECT course_name, period,
course_hours
FROM course_updates) cu
ON (c.course_name = cu.course_name
AND c.period = cu.period)
WHEN MATCHED THEN
UPDATE
SET c.course_hours = cu.course_hours
WHEN NOT MATCHED THEN
INSERT (c.course_name, c.period,
c.course_hours)
VALUES (cu.course_name, cu.period,
cu.course_hours);
[Q]怎麼實現左聯，右聯與外聯
[A]在9i以前可以這麼寫:
左聯：
select a.id,a.name,b.address from a,b
where a.id=b.id(+)
右聯:
select a.id,a.name,b.address from a,b
where a.id(+)=b.id
外聯
SELECT a.id,a.name,b.address
FROM a,b
WHERE a.id = b.id(+)
UNION
SELECT b.id,'' name,b.address
FROM b
WHERE NOT EXISTS (
SELECT * FROM a
WHERE a.id = b.id);
在9i以上，已經開始支援SQL99標準，所以，以上語句可以寫成：
預設內部聯結：
select a.id,a.name,b.address,c.subject
from (a inner join b on a.id=b.id)
inner join c on b.name = c.name
where other_clause
左聯
select a.id,a.name,b.address
from a left outer join b on a.id=b.id
where other_clause
右聯
select a.id,a.name,b.address
from a right outer join b on a.id=b.id
where other_clause
外聯
select a.id,a.name,b.address
from a full outer join b on a.id=b.id
where other_clause
or
select a.id,a.name,b.address
from a full outer join b using (id)
where other_clause
[Q]怎麼實現一條記錄根據條件多表插入
[A]9i以上可以通過Insert all語句完成，僅僅是一個語句，如：
INSERT ALL
WHEN (id=1) THEN
INTO table_1 (id, name)
values(id,name)
WHEN (id=2) THEN
INTO table_2 (id, name)
values(id,name)
ELSE
INTO table_other (id, name)
values(id, name)
SELECT id,name
FROM a;
如果沒有條件的話，則完成每個表的插入，如
INSERT ALL
INTO table_1 (id, name)
values(id,name)
INTO table_2 (id, name)
values(id,name)
INTO table_other (id, name)
values(id, name)
SELECT id,name
FROM a;
[Q]如何實現行列轉換
[A]1、固定列數的行列轉換
如
student subject grade
---------------------------
student1 語文 80
student1 數學 70
student1 英語 60
student2 語文 90
student2 數學 80
student2 英語 100
……
轉換為
語文 數學 英語
student1 80 70 60
student2 90 80 100
……
語句如下：
select student,sum(decode(subject,'語文', grade,null)) "語文",
sum(decode(subject,'數學', grade,null)) "數學",
sum(decode(subject,'英語', grade,null)) "英語"
from table
group by student
2、不定列行列轉換
如
c1 c2
--------------
1 我
1 是
1 誰
2 知
2 道
3 不
……
轉換為
1 我是誰
2 知道
3 不
這一型別的轉換必須藉助於PL/SQL來完成，這裡給一個例子
CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER)
RETURN VARCHAR2
IS
Col_c2 VARCHAR2(4000);
BEGIN
FOR cur IN (SELECT c2 FROM t WHERE c1=tmp_c1) LOOP
Col_c2 := Col_c2||cur.c2;
END LOOP;
Col_c2 := rtrim(Col_c2,1);
RETURN Col_c2;
END;
/
SQL> select distinct c1 ,get_c2(c1) cc2 from table;即可
[Q]怎麼樣實現分組取前N條記錄
[A]8i以上版本，利用分析函式
如獲取每個部門薪水前三名的員工或每個班成績前三名的學生。
Select * from
(select depno,ename,sal,row_number() over (partition by depno
order by sal desc) rn
from emp)
where rn host lsntctl start
或者unix/linux平臺下
SQL>!
windows平臺下
SQL>$
總結：HOST 可以直接執行OS命令。
備註：cd命令無法正確執行。
[Q]怎麼設定儲存過程的呼叫者許可權
[A]普通儲存過程都是所有者許可權，如果想設定呼叫者許可權，請參考如下語句
create or replace
procedure ……()
AUTHID CURRENT_USER
As
begin
……
end;
[Q]怎麼快速獲得使用者下每個表或表分割槽的記錄數
[A]可以分析該使用者，然後查詢user_tables字典，或者採用如下指令碼即可
SET SERVEROUTPUT ON SIZE 20000
DECLARE
miCount INTEGER;
BEGIN
FOR c_tab IN (SELECT table_name FROM user_tables) LOOP
EXECUTE IMMEDIATE 'select count(*) from "' || c_tab.table_name || '"' into miCount;
dbms_output.put_line(rpad(c_tab.table_name,30,'.') || lpad(miCount,10,'.'));
--if it is partition table
SELECT COUNT(*) INTO miCount FROM User_Part_Tables WHERE table_name = c_tab.table_name;
IF miCount >0 THEN
FOR c_part IN (SELECT partition_name FROM user_tab_partitions WHERE table_name = c_tab.table_name) LOOP
EXECUTE IMMEDIATE 'select count(*) from ' || c_tab.table_name || ' partition (' || c_part.partition_name || ')'
INTO miCount;
dbms_output.put_line(' '||rpad(c_part.partition_name,30,'.') || lpad(miCount, 10,'.'));
END LOOP;
END IF;
END LOOP;
END;
[A]怎麼在Oracle中發郵件
[Q]可以利用utl_smtp包發郵件，以下是一個傳送簡單郵件的例子程式
/****************************************************************************
parameter: Rcpter in varchar2 接收者郵箱
Mail_Content in Varchar2 郵件內容
desc: ·傳送郵件到指定郵箱
·只能指定一個郵箱，如果需要傳送到多個郵箱，需要另外的輔助程式
****************************************************************************/
CREATE OR REPLACE PROCEDURE sp_send_mail( rcpter IN VARCHAR2,
mail_content IN VARCHAR2)
IS
conn utl_smtp.connection;
--write title
PROCEDURE send_header(NAME IN VARCHAR2, HEADER IN VARCHAR2) AS
BEGIN
utl_smtp.write_data(conn, NAME||': '|| HEADER||utl_tcp.CRLF);
END;
BEGIN
--opne connect
conn := utl_smtp.open_connection('smtp.com');
utl_smtp.helo(conn, 'oracle');
utl_smtp.mail(conn, 'oracle info');
utl_smtp.rcpt(conn, Rcpter);
utl_smtp.open_data(conn);
--write title
send_header('From', 'Oracle Database');
send_header('To', '"Recipient" ');
send_header('Subject', 'DB Info');
--write mail content
utl_smtp.write_data(conn, utl_tcp.crlf || mail_content);
--close connect
utl_smtp.close_data(conn);
utl_smtp.quit(conn);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
utl_smtp.quit(conn);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
WHEN OTHERS THEN
NULL;
END sp_send_mail;
[A]怎麼樣在Oracle中寫作業系統檔案，如寫日誌
[Q]可以利用utl_file包，但是，在此之前，要注意設定好Utl_file_dir初始化引數
/**************************************************************************
parameter:textContext in varchar2 日誌內容
desc: ·寫日誌,把內容記到伺服器指定目錄下
·必須配置Utl_file_dir初始化引數，並保證日誌路徑與Utl_file_dir路徑一致或者是其中一個
****************************************************************************/
CREATE OR REPLACE PROCEDURE sp_Write_log(text_context VARCHAR2)
IS
file_handle utl_file.file_type;
Write_content VARCHAR2(1024);
Write_file_name VARCHAR2(50);
BEGIN
--open file
write_file_name := 'db_alert.log';
file_handle := utl_file.fopen('/u01/logs',write_file_name,'a');
write_content := to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss')||'||'||text_context;
--write file
IF utl_file.is_open(file_handle) THEN
utl_file.put_line(file_handle,write_content);
END IF;
--close file
utl_file.fclose(file_handle);
EXCEPTION
WHEN OTHERS THEN
BEGIN
IF utl_file.is_open(file_handle) THEN
utl_file.fclose(file_handle);
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END sp_Write_log;

ORACLE學習東東（效能調整）

把別人的BLOG上的東東直接拉到自己這裡來，為了自己以後也好查一些。。希望別人不要對我拍磚！！

之所以這麼做是怕有一天給忘記大師的地址，自己使用起來方便！！

大師的地址：http://blog.itpub.net/category/4988/9259

什麼是繫結變數？


查詢通常只是因為改變where子句中的內容而產生不同的結果。為了在這種情況下避免硬解析，需要使用繫結變數(bind variable)。它是使用者放入查詢中的佔位符，它會告訴Oracle"我會隨後為這個變數提供一個值，現在需要生成一個方案，但我實際執行語句的時候，我會為您提供應該使用的實際值"。
select * from emp where ename='KING'; //不使用繫結變數
select * from emp where ename=:bv //使用繫結變數

一般在 procedure or function 中使用，可以優化共享池的使用。

 

通過sql查詢獲得當前session的trace檔名稱 SELECT d.VALUE || '' || LOWER (RTRIM (i.INSTANCE, CHR (0))) || '_ora_' || p.spid || '.trc' trace_file_name FROM (SELECT p.spid FROM v$mystat m, v$session s, v$process p WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p, (SELECT t.INSTANCE FROM v$thread t, v$parameter v WHERE v.NAME = 'thread' AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i, (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d 跳躍式索引的一個例子.
發表人:lijietz | 發表時間: 2005年四月19日, 19:52

      從Oracle9i開始，索引跳躍式掃描特性可以允許優化器使用組合索引，即便索引的前導列沒有出現在WHERE子句中。索引跳躍式掃描比全索引掃描要快的多。下面的程式清單顯示出效能的差別：


    create index idx_skip on emp5(job,empno);
    index created.

    select count(*)
    from emp5
    where empno=7900;

    Elapsed:00:00:03.13

    Execution Plan
    0     SELECT STATEMENT ptimizer=CHOOSE(Cost=4 Card=1 Bytes=5)
    1  0    SORT(AGGREGATE)
    2  1      INDEX(FAST FULL SCAN) OF 'idx_skip'(NON-UNIQUE)

    Statistics

    6826 consistent gets
    6819 physical   reads

    select /*+ index(emp5 idx_skip)*/ count(*)
    from emp5
    where empno=7900;

    Elapsed:00:00:00.56

    Execution Plan
    0     SELECT STATEMENT ptimizer=CHOOSE(Cost=6 Card=1 Bytes=5)
    1  0    SORT(AGGREGATE)
    2  1      INDEX(SKIP SCAN) OF 'idx_skip' (NON-UNIQUE)

    Statistics

    21 consistent gets
    17 physical   reads

對hight water mark的理解.
發表人:lijietz | 發表時間: 2005年四月22日, 21:43

可以用以下幾點對high-water mark進行理解。


1.指一個表中曾經被用過的最後一個塊
2.如果有資料被插入表，high-water mark 就移到到被使用的最後一個塊。
3.如果有資料被刪除，high-water mark的位置不會變。
4.high-water mark被儲存在表的段頭(segment header of the table)。
5.當對錶執行全表掃描時，oracle server 被所有的塊直到high-water mark。

這樣看來high-wate mark就是用來標識最後一個被用過的block的

另外我的理解如下：

1 如果為表分配了大量的extents , 但這些extent 還沒使用可以手工收回。並且有如下兩種情況
第一種 minextent < hwm可以使用
alter table tablename deallocate unused;
將hwm以上所有沒使用的空間釋放
第二種 minextent >hwm 則釋放minextents 以上的空間。
如果要釋放hwm以上的空間則使用keep 0。
2 truncate 將minextent 之上的空間釋放。
3 只是將hwm移動，釋放的空間不會被其他段使用

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

相關文章