ORACLE之常用FAQ V1.0，已經(jīng)停止更新，準(zhǔn)備出chm版本
為便于大家閱讀．此帖置頂．請(qǐng)不要在后面跟上一些"好"，"頂"之類的帖子,如果真的要感謝Piner，請(qǐng)多多提交FAQ．謝謝合作 --by Fenng
-----------------------------------------------------------------------------------------------------------------

第一部分、SQL&PL/SQL
[Q]怎么樣查詢特殊字符，如通配符%與_
[A]select * from table where name like 'A\_%' escape '\'

[Q]如何插入單引號(hào)到數(shù)據(jù)庫(kù)表中
[A]可以用ASCII碼處理，其它特殊字符如&也一樣，如
insert into t values('i'||chr(39)||'m'); -- chr(39)代表字符'
或者用兩個(gè)單引號(hào)表示一個(gè)
or insert into t values('I''m'); -- 兩個(gè)''可以表示一個(gè)'

[Q]怎樣設(shè)置事務(wù)一致性
[A]set transaction [isolation level] read committed; 默認(rèn)語(yǔ)句級(jí)一致性
set transaction [isolation level] serializable;
read only; 事務(wù)級(jí)一致性

[Q]怎么樣利用游標(biāo)更新數(shù)據(jù)
[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，錯(cuò)誤信息最大2048B
異常變量
SQLCODE 錯(cuò)誤代碼
SQLERRM 錯(cuò)誤信息

[Q]十進(jìn)制與十六進(jìn)制的轉(zhuǎn)換
[A]8i以上版本：
to_char(100,'XX')
to_number('4D','XX')
8i以下的進(jìn)制之間的轉(zhuǎn)換參考如下腳本
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 < 0 ) then
raise PROGRAM_ERROR;
end if;
loop
l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str;
l_num := trunc( l_num/p_base );
exit when ( l_num = 0 );
end loop;
return l_str;
end to_base;
/
create or replace function to_dec
( p_str in varchar2,
p_from_base in number default 16 ) return number
is
l_num number default 0;
l_hex varchar2(16) default '0123456789ABCDEF';
begin
if ( p_str is null or p_from_base is null ) then
return null;
end if;
for i in 1 .. length(p_str) loop
l_num := l_num * p_from_base + instr(l_hex,upper(substr(p_str,i,1)))-1;
end loop;
return l_num;
end to_dec;
/

[Q]能不能介紹SYS_CONTEXT的詳細(xì)用法
[A]利用以下的查詢，你就明白了
select
SYS_CONTEXT('USERENV','TERMINAL') terminal,
SYS_CONTEXT('USERENV','LANGUAGE') language,
SYS_CONTEXT('USERENV','SESSIONID') sessionid,
SYS_CONTEXT('USERENV','INSTANCE') instance,
SYS_CONTEXT('USERENV','ENTRYID') entryid,
SYS_CONTEXT('USERENV','ISDBA') isdba,
SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory,
SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency,
SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar,
SYS_CONTEXT('USERENV','NLS_DATE_formAT') nls_date_format,
SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language,
SYS_CONTEXT('USERENV','NLS_SORT') nls_sort,
SYS_CONTEXT('USERENV','CURRENT_USER') current_user,
SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid,
SYS_CONTEXT('USERENV','SESSION_USER') session_user,
SYS_CONTEXT('USERENV','SESSION_USERID') session_userid,
SYS_CONTEXT('USERENV','PROXY_USER') proxy_user,
SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid,
SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain,
SYS_CONTEXT('USERENV','DB_NAME') db_name,
SYS_CONTEXT('USERENV','HOST') host,
SYS_CONTEXT('USERENV','OS_USER') os_user,
SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name,
SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address,
SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol,
SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id,
SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id,
SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') authentication_type,
SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') authentication_data
from dual

[Q]怎么獲得今天是星期幾，還關(guān)于其它日期函數(shù)用法
[A]可以用to_char來(lái)解決，如
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;
在獲取之前可以設(shè)置日期語(yǔ)言，如
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
還可以在函數(shù)中指定
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;
其它更多用法，可以參考to_char與to_date函數(shù)
如獲得完整的時(shí)間格式
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
隨便介紹幾個(gè)其它函數(shù)的用法：
本月的天數(shù)
SELECT to_char(last_day(SYSDATE),'dd') days FROM dual
今年的天數(shù)
select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual
下個(gè)星期一的日期
SELECT Next_day(SYSDATE,'monday') FROM dual

[Q]隨機(jī)抽取前N條記錄的問(wèn)題
[A]8i以上版本
select * from (select * from tablename order by sys_guid()) where rownum < N;
select * from (select * from tablename order by dbms_random.value) where rownum< N;
注：dbms_random包需要手工安裝，位于$ORACLE_HOME/rdbms/admin/dbmsrand.sql
dbms_random.value(100,200)可以產(chǎn)生100到200范圍的隨機(jī)數(shù)

[Q]抽取從N行到M行的記錄，如從20行到30行的記錄
[A]select * from (select rownum id,t.* from table where ……
and rownum <= 30) where id > 20;

[Q]怎么樣抽取重復(fù)記錄
[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
如果想刪除重復(fù)記錄，可以把第一個(gè)語(yǔ)句的select替換為delete

[Q]怎么樣設(shè)置自治事務(wù)
[A]8i以上版本，不影響主事務(wù)
pragma autonomous_transaction;
……
commit|rollback;

[Q]怎么樣在過(guò)程中暫停指定時(shí)間
[A]DBMS_LOCK包的sleep過(guò)程
如：dbms_lock.sleep(5);表示暫停5秒。

[Q]怎么樣快速計(jì)算事務(wù)的時(shí)間與日志量
[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]怎樣創(chuàng)建臨時(shí)表
[A]8i以上版本
create global temporary tablename(column list)
on commit preserve rows; --提交保留數(shù)據(jù) 會(huì)話臨時(shí)表
on commit delete rows; --提交刪除數(shù)據(jù) 事務(wù)臨時(shí)表
臨時(shí)表是相對(duì)于會(huì)話的，別的會(huì)話看不到該會(huì)話的數(shù)據(jù)。

[Q]怎么樣在PL/SQL中執(zhí)行DDL語(yǔ)句
[A]1、8i以下版本dbms_sql包
2、8i以上版本還可以用
execute immediate sql;
dbms_utility.exec_ddl_statement('sql');

[Q]怎么樣獲取IP地址
[A]服務(wù)器(817以上)：utl_inaddr.get_host_address
客戶端：sys_context('userenv','ip_address')

[Q]怎么樣加密存儲(chǔ)過(guò)程
[A]用wrap命令，如（假定你的存儲(chǔ)過(guò)程保存為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轉(zhuǎn)換為a.plb，這就是加密了的腳本，執(zhí)行a.plb即可生成加密了的存儲(chǔ)過(guò)程

[Q]怎么樣在ORACLE中定時(shí)運(yùn)行存儲(chǔ)過(guò)程
[A]可以利用dbms_job包來(lái)定時(shí)運(yùn)行作業(yè)，如執(zhí)行存儲(chǔ)過(guò)程，一個(gè)簡(jiǎn)單的例子，提交一個(gè)作業(yè)：
VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno, 'ur_procedure;',SYSDATE,'SYSDATE + 1');
commit;
END;
之后，就可以用以下語(yǔ)句查詢已經(jīng)提交的作業(yè)
select * from user_jobs;

[Q]怎么樣從數(shù)據(jù)庫(kù)中獲得毫秒
[A]9i以上版本，有一個(gè)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中對(duì)應(yīng)的是FF。
8i以上版本可以創(chuàng)建一個(gè)如下的java函數(shù)
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的語(yǔ)法，注意大小寫
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]如果存在就更新，不存在就插入可以用一個(gè)語(yǔ)句實(shí)現(xiàn)嗎
[A]9i已經(jīng)支持了，是Merge，但是只支持select子查詢，
如果是單條數(shù)據(jù)記錄，可以寫作select …… from dual的子查詢。
語(yǔ)法為：
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]怎么實(shí)現(xiàn)左聯(lián)，右聯(lián)與外聯(lián)
[A]在9i以前可以這么寫:
左聯(lián)：
select a.id,a.name,b.address from a,b
where a.id=b.id(+)
右聯(lián):
select a.id,a.name,b.address from a,b
where a.id(+)=b.id
外聯(lián)
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以上，已經(jīng)開(kāi)始支持SQL99標(biāo)準(zhǔn)，所以，以上語(yǔ)句可以寫成：
默認(rèn)內(nèi)部聯(lián)結(jié)：
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
左聯(lián)
select a.id,a.name,b.address
from a left outer join b on a.id=b.id
where other_clause
右聯(lián)
select a.id,a.name,b.address
from a right outer join b on a.id=b.id
where other_clause
外聯(lián)
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]怎么實(shí)現(xiàn)一條記錄根據(jù)條件多表插入
[A]9i以上可以通過(guò)Insert all語(yǔ)句完成，僅僅是一個(gè)語(yǔ)句，如：
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;
如果沒(méi)有條件的話，則完成每個(gè)表的插入，如
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]如何實(shí)現(xiàn)行列轉(zhuǎn)換
[A]1、固定列數(shù)的行列轉(zhuǎn)換
如
student subject grade
---------------------------
student1 語(yǔ)文 80
student1 數(shù)學(xué) 70
student1 英語(yǔ) 60
student2 語(yǔ)文 90
student2 數(shù)學(xué) 80
student2 英語(yǔ) 100
……
轉(zhuǎn)換為
語(yǔ)文 數(shù)學(xué) 英語(yǔ)
student1 80 70 60
student2 90 80 100
……
語(yǔ)句如下：
select student,sum(decode(subject,'語(yǔ)文', grade,null)) "語(yǔ)文",
sum(decode(subject,'數(shù)學(xué)', grade,null)) "數(shù)學(xué)",
sum(decode(subject,'英語(yǔ)', grade,null)) "英語(yǔ)"
from table
group by student

2、不定列行列轉(zhuǎn)換
如
c1 c2
--------------
1 我
1 是
1 誰(shuí)
2 知
2 道
3 不
……
轉(zhuǎn)換為
1 我是誰(shuí)
2 知道
3 不
這一類型的轉(zhuǎn)換必須借助于PL/SQL來(lái)完成，這里給一個(gè)例子
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]怎么樣實(shí)現(xiàn)分組取前N條記錄
[A]8i以上版本，利用分析函數(shù)
如獲取每個(gè)部門薪水前三名的員工或每個(gè)班成績(jī)前三名的學(xué)生。
Select * from
(select depno,ename,sal,row_number() over (partition by depno
order by sal desc) rn
from emp)
where rn<=3

[Q]怎么樣把相鄰記錄合并到一條記錄
[A]8i以上版本，分析函數(shù)lag與lead可以提取后一條或前一天記錄到本記錄。
Select deptno,ename,hiredate,lag(hiredate,1,null) over
(partition by deptno order by hiredate,ename) last_hire
from emp
order by depno,hiredate

[Q]如何取得一列中第N大的值？
[A]select * from
(select t.*,dense_rank() over (order by t2 desc) rank from t)
where rank = &N;

[Q]怎么樣把查詢內(nèi)容輸出到文本
[A]用spool如
如sqlplus –s " / as sysdba" <<EOF
set heading off
set feedback off
spool temp.txt
　 select * from tab;
dbms_output.put_line(‘test’);
spool off
exit
EOF

[Q] 如何在SQL*PLUS環(huán)境中執(zhí)行OS命令？
[A] 比如進(jìn)入了SQLPLUS，啟動(dòng)了數(shù)據(jù)庫(kù)，忽然想起監(jiān)聽(tīng)還沒(méi)有啟動(dòng)，此時(shí)不用退出SQLPLUS，也不用另外起一個(gè)命令行窗口，直接輸入：
SQL> host lsntctl start
或者unix/linux平臺(tái)下
SQL>!<OS command>
windows平臺(tái)下
SQL>$<OS command>
總結(jié)：HOST <OS command>可以直接執(zhí)行OS命令。
備注：cd命令無(wú)法正確執(zhí)行。

[Q]怎么設(shè)置存儲(chǔ)過(guò)程的調(diào)用者權(quán)限
[A]普通存儲(chǔ)過(guò)程都是所有者權(quán)限，如果想設(shè)置調(diào)用者權(quán)限，請(qǐng)參考如下語(yǔ)句
create or replace
procedure ……()
AUTHID CURRENT_USER
As
begin
……
end;

[Q]怎么快速獲得用戶下每個(gè)表或表分區(qū)的記錄數(shù)
[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中發(fā)郵件
[Q]可以利用utl_smtp包發(fā)郵件，以下是一個(gè)發(fā)送簡(jiǎn)單郵件的例子程序
/****************************************************************************
parameter: Rcpter in varchar2 接收者郵箱
Mail_Content in Varchar2 郵件內(nèi)容
desc: ·發(fā)送郵件到指定郵箱
·只能指定一個(gè)郵箱，如果需要發(fā)送到多個(gè)郵箱，需要另外的輔助程序
****************************************************************************/
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" <'||rcpter||'>');
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中寫操作系統(tǒng)文件，如寫日志
[Q]可以利用utl_file包，但是，在此之前，要注意設(shè)置好Utl_file_dir初始化參數(shù)
/**************************************************************************
parameter:textContext in varchar2 日志內(nèi)容
desc: ·寫日志,把內(nèi)容記到服務(wù)器指定目錄下
·必須配置Utl_file_dir初始化參數(shù)，并保證日志路徑與Utl_file_dir路徑一致或者是其中一個(gè)
****************************************************************************/
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構(gòu)架體系
[Q]ORACLE的有那些數(shù)據(jù)類型
[A]常見(jiàn)的數(shù)據(jù)類型有
CHAR固定長(zhǎng)度字符域，最大長(zhǎng)度可達(dá)2000個(gè)字節(jié)
NCHAR多字節(jié)字符集的固定長(zhǎng)度字符域，長(zhǎng)度隨字符集而定，最多為2000個(gè)字符或2000個(gè)字節(jié)
VARCHAR2可變長(zhǎng)度字符域，最大長(zhǎng)度可達(dá)4000個(gè)字符
NVARCHAR2多字節(jié)字符集的可變長(zhǎng)度字符域，長(zhǎng)度隨字符集而定，最多為4000個(gè)字符或4000個(gè)字節(jié)
DATE用于存儲(chǔ)全部日期的固定長(zhǎng)度(7個(gè)字節(jié))字符域，時(shí)間作為日期的一部分存儲(chǔ)其中。除非
通過(guò)設(shè)置init.ora文件的NLS_DATE_formAT參數(shù)來(lái)取代日期格式，否則查詢時(shí)，日期以
DD-MON-YY格式表示，如13-APR-99表示1999.4.13
NUMBER可變長(zhǎng)度數(shù)值列，允許值為0、正數(shù)和負(fù)數(shù)。NUMBER值通常以4個(gè)字節(jié)或更少的字節(jié)存儲(chǔ)，最多21字節(jié)
LONG可變長(zhǎng)度字符域，最大長(zhǎng)度可到2GB
RAW表示二進(jìn)制數(shù)據(jù)的可變長(zhǎng)度字符域，最長(zhǎng)為2000個(gè)字節(jié)
LONGRAW表示二進(jìn)制數(shù)據(jù)的可變長(zhǎng)度字符域，最長(zhǎng)為2GB
MLSLABEL只用于TrustedOracle，這個(gè)數(shù)據(jù)類型每行使用2至5個(gè)字節(jié)
BLOB二進(jìn)制大對(duì)象，最大長(zhǎng)度為4GB
CLOB字符大對(duì)象，最大長(zhǎng)度為4GB
NCLOB多字節(jié)字符集的CLOB數(shù)據(jù)類型，最大長(zhǎng)度為4GB
BFILE外部二進(jìn)制文件，大小由操作系統(tǒng)決定
ROWID表示RowID的二進(jìn)制數(shù)據(jù)，Oracle8RowID的數(shù)值為10個(gè)字節(jié)，在Oracle7中使用的限定
RowID格式為6個(gè)字節(jié)
UROWID用于數(shù)據(jù)尋址的二進(jìn)制數(shù)據(jù)，最大長(zhǎng)度為4000個(gè)字節(jié)

[Q]Oracle有哪些常見(jiàn)關(guān)鍵字，不能被用于對(duì)象名
[A]以8i版本為例，一般保留關(guān)鍵字不能用做對(duì)象名
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
詳細(xì)信息可以查看v$reserved_words視圖

[Q]怎么查看數(shù)據(jù)庫(kù)版本
[A]select * from v$version
包含版本信息，核心版本信息，位數(shù)信息(32位或64位)等
至于位數(shù)信息，在linux/unix平臺(tái)上，可以通過(guò)file查看，如
file $ORACLE_HOME/bin/oracle

[Q]怎么查看數(shù)據(jù)庫(kù)參數(shù)
[A]show parameter 參數(shù)名
如通過(guò)show parameter spfile可以查看9i是否使用spfile文件
或者select * from v$parameter
除了這部分參數(shù)，Oracle還有大量隱含參數(shù)，可以通過(guò)如下語(yǔ)句查看:
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]怎么樣查看數(shù)據(jù)庫(kù)字符集
[A]數(shù)據(jù)庫(kù)服務(wù)器字符集select * from nls_database_parameters，其來(lái)源于props$，是表示數(shù)據(jù)庫(kù)的字符集。
客戶端字符集環(huán)境select * from nls_instance_parameters,其來(lái)源于v$parameter，
表示客戶端的字符集的設(shè)置，可能是參數(shù)文件，環(huán)境變量或者是注冊(cè)表
會(huì)話字符集環(huán)境 select * from nls_session_parameters，其來(lái)源于v$nls_parameters，表示會(huì)話自己的設(shè)置，可能是會(huì)話的環(huán)境變量或者是alter session完成，如果會(huì)話沒(méi)有特殊的設(shè)置，將與nls_instance_parameters一致。
客戶端的字符集要求與服務(wù)器一致，才能正確顯示數(shù)據(jù)庫(kù)的非Ascii字符。如果多個(gè)設(shè)置存在的時(shí)候，alter session>環(huán)境變量>注冊(cè)表>參數(shù)文件
字符集要求一致，但是語(yǔ)言設(shè)置卻可以不同，語(yǔ)言設(shè)置建議用英文。如字符集是zhs16gbk，則nls_lang可以是American_America.zhs16gbk。

[Q]怎么樣修改字符集
[A]8i以上版本可以通過(guò)alter database來(lái)修改字符集，但也只限于子集到超集，不建議修改props$表，將可能導(dǎo)致嚴(yán)重錯(cuò)誤。
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]怎樣建立基于函數(shù)索引
[A]8i以上版本，確保
Query_rewrite_enabled=true
Query_rewrite_integrity=trusted
Compatible=8.1.0以上
Create index indexname on table (function(field));

[Q]怎么樣移動(dòng)表或表分區(qū)
[A]移動(dòng)表的語(yǔ)法
Alter table tablename move
[Tablespace new_name
Storage(initial 50M next 50M
pctincrease 0 pctfree 10 pctused 50 initrans 2) nologging]
移動(dòng)分區(qū)的語(yǔ)法
alter table tablename move (partition partname)
[update global indexes]
之后之后必須重建索引
Alter index indexname rebuild
如果表有Lob段，那么正常的Alter不能移動(dòng)Lob段到別的表空間，而僅僅是移動(dòng)了表段，可以采用如下的方法移動(dòng)Lob段
alter table tablename move
lob(lobsegname) store as (tablespace newts);

[Q]怎么獲得當(dāng)前的SCN
[A]9i以下版本
select max(ktuxescnw*power(2,32)+ktuxescnb) from x$ktuxe;
如果是9i以上版本，還可以通過(guò)以下語(yǔ)句獲取
select dbms_flashback.get_system_change_number from dual;

[Q]ROWID的結(jié)構(gòu)與組成
[A]8以上版本的ROWID組成
OOOOOOFFFBBBBBBRRR
8以下ROWID組成（也叫受限Rowid）
BBBBBBBB.RRRR.FFFF
其中，O是對(duì)象ID，F(xiàn)是文件ID，B是塊ID，R是行ID
如果我們查詢一個(gè)表的ROWID，根據(jù)其中塊的信息，可以知道該表確切占用了多少個(gè)塊，進(jìn)而知道占用了多少數(shù)據(jù)空間（此數(shù)據(jù)空間不等于表的分配空間）

[Q]怎么樣獲取對(duì)象的DDL語(yǔ)句
[A]第三方工具就不說(shuō)了主要說(shuō)一下9i以上版本的dbms_metadata
1、獲得單個(gè)對(duì)象的DDL語(yǔ)句
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;
如果獲取整個(gè)用戶的腳本，可以用如下語(yǔ)句
select dbms_metadata.get_ddl('TABLE',u.table_name) from user_tables u;
當(dāng)然，如果是索引，則需要修改相關(guān)table到index

[Q]如何創(chuàng)建約束的索引在別的表空間上
[A]1、先創(chuàng)建索引，再創(chuàng)建約束
2、利用如下語(yǔ)句創(chuàng)建
create table test
(c1 number constraint pk_c1_id primary key
using index tablespace useridex,
c2 varchar2(10)
) tablespace userdate;

[Q]怎么知道那些表沒(méi)有建立主鍵
[A]一般的情況下，表的主鍵是必要的，沒(méi)有主鍵的表可以說(shuō)是不符合設(shè)計(jì)規(guī)范的。
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)
其它相關(guān)數(shù)據(jù)字典解釋
user_tables 表
user_tab_columns 表的列
user_constraints 約束
user_cons_columns 約束與列的關(guān)系
user_indexes 索引
user_ind_columns 索引與列的關(guān)系

[Q]dbms_output提示緩沖區(qū)不夠，怎么增加
[A]dbms_output.enable(20000);
另外，如果dbms_output的信息不能顯示，
需要設(shè)置
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
在安裝之前，必須先設(shè)置SYSTEM_PASS環(huán)境變量，如：
$ setenv SYSTEM_PASS SYSTEM/MANAGER
$ helpins
如果不設(shè)置該環(huán)境變量，將在運(yùn)行腳本的時(shí)候提示輸入環(huán)境變量
當(dāng)然，除了shell腳本，還可以利用sql腳本安裝，那就不用設(shè)置環(huán)境變量了，但是，我們必須以system登錄。
$ sqlplus system/manager
SQL> @?/sqlplus/admin/help/helpbld.sql helpus.sql
安裝之后，你就可以象如下的方法使用幫助了
SQL> help index

[Q]怎么樣快速下載Oracle補(bǔ)丁
[A]我們先獲得下載服務(wù)器地址，在http頁(yè)面上有
ftp://updates.oracle.com/
然后用ftp登錄，用戶名與密碼是metalink的用戶名與密碼
如我們知道了補(bǔ)丁號(hào)3095277 (9204的補(bǔ)丁集)，則
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>
知道了這個(gè)信息，我們用用flashget，網(wǎng)絡(luò)螞蟻就可以下載了。
添加如下連接
ftp://updates.oracle.com/3095277/p3...04_AIX64-5L.zip
或替換后面的部分為所需要的內(nèi)容
注意，如果是flashget，網(wǎng)絡(luò)螞蟻請(qǐng)輸入認(rèn)證用戶名及密碼，就是你的metalink的用戶名與密碼！

[Q]如何移動(dòng)數(shù)據(jù)文件
[A]1、關(guān)閉數(shù)據(jù)庫(kù)，利用os拷貝
a.shutdown immediate關(guān)閉數(shù)據(jù)庫(kù)
b.在os下拷貝數(shù)據(jù)文件到新的地點(diǎn)
c.Startup mount 啟動(dòng)到mount下
d.Alter database rename datafile '老文件' to '新文件';
e.Alter database open; 打開(kāi)數(shù)據(jù)庫(kù)
2、利用Rman聯(lián)機(jī)操作
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";
說(shuō)明：利用OS拷貝也可以聯(lián)機(jī)操作，不關(guān)閉數(shù)據(jù)庫(kù)，與rman的步驟一樣，利用rman與利用os拷貝的原理一樣，在rman中copy是拷貝數(shù)據(jù)文件，相當(dāng)于OS的cp，而switch則相當(dāng)于alter database rename，用來(lái)更新控制文件。

[Q]如果管理聯(lián)機(jī)日志組與成員
[A]以下是常見(jiàn)操作，如果在OPA/RAC下注意線程號(hào)
增加一個(gè)日志文件組
Alter database add logfile [group n] '文件全名' size 10M;
在這個(gè)組上增加一個(gè)成員
Alter database add logfile member '文件全名' to group n;
在這個(gè)組上刪除一個(gè)日志成員
Alter database drop logfile member '文件全名';
刪除整個(gè)日志組
Alter database drop logfile group n;

[Q]怎么樣計(jì)算REDO BLOCK的大小
[A]計(jì)算方法為(redo size + redo wastage) / redo blocks written + 16
具體見(jiàn)如下例子
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]控制文件包含哪些基本內(nèi)容
[A]控制文件主要包含如下條目，可以通過(guò)dump控制文件內(nèi)容看到
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]如果發(fā)現(xiàn)表中有壞塊，如何檢索其它未壞的數(shù)據(jù)
[A]首先需要找到壞塊的ID（可以運(yùn)行dbverify實(shí)現(xiàn)），假設(shè)為<BID>，假定文件編碼為<FID>。運(yùn)行下面的查詢查找段名：
SELECT segment_name,segment_type,extent_id,block_id, blocks
from dba_extents t
where
file_id = <FID>
AND <BID> between block_id and (block_id + blocks - 1)
一旦找到壞段名稱，若段是一個(gè)表，則最好建立一個(gè)臨時(shí)表，存放好的數(shù)據(jù)。若段是索引，則刪除它，再重建。
create table good_table
as
select from bad_table where rowid not in
(select rowid
from bad_table where substr(rowid,10,6) = <BID> )
在這里要注意8以前的受限ROWID與現(xiàn)在ROWID的差別。
還可以使用診斷事件10231
SQL> ALTER SYSTEM SET EVENTS '10231 trace name context forever,level 10';
創(chuàng)建一個(gè)臨時(shí)表good_table的表中除壞塊的數(shù)據(jù)都檢索出來(lái)
SQL>CREATE TABLE good_table as select * from bad_table;
最后關(guān)閉診斷事件
SQL> ALTER SYSTEM SET EVENTS '10231 trace name context off ';
關(guān)于ROWID的結(jié)構(gòu)，還可以參考dbms_rowid.rowid_create函數(shù)。

[Q]我創(chuàng)建了數(shù)據(jù)庫(kù)的所有用戶，我可以刪除這些用戶嗎
[A]ORACLE數(shù)據(jù)庫(kù)創(chuàng)建的時(shí)候，創(chuàng)建了一系列默認(rèn)的用戶和表空間，以下是他們的列表
·SYS/CHANGE_ON_INSTALL or INTERNAL
系統(tǒng)用戶，數(shù)據(jù)字典所有者，超級(jí)權(quán)限所有者(SYSDBA)
創(chuàng)建腳本：?/rdbms/admin/sql.bsq and various cat*.sql
建議創(chuàng)建后立即修改密碼
此用戶不能被刪除
·SYSTEM/MANAGER
數(shù)據(jù)庫(kù)默認(rèn)管理用戶，擁有DBA角色權(quán)限
創(chuàng)建腳本：?/rdbms/admin/sql.bsq
建議創(chuàng)建后立即修改密碼
此用戶不能被刪除
·OUTLN/OUTLN
優(yōu)化計(jì)劃的存儲(chǔ)大綱用戶
創(chuàng)建腳本：?/rdbms/admin/sql.bsq
建議創(chuàng)建后立即修改密碼
此用戶不能被刪除
---------------------------------------------------
·SCOTT/TIGER, ADAMS/WOOD, JONES/STEEL, CLARK/CLOTH and BLAKE/PAPER.
實(shí)驗(yàn)、測(cè)試用戶，含有例表EMP與DEPT
創(chuàng)建腳本：?/rdbms/admin/utlsampl.sql
可以修改密碼
用戶可以被刪除，在產(chǎn)品環(huán)境建議刪除或鎖定
·HR/HR (Human Resources), OE/OE (Order Entry), SH/SH (Sales History).
實(shí)驗(yàn)、測(cè)試用戶，含有例表EMPLOYEES與DEPARTMENTS
創(chuàng)建腳本：?/demo/schema/mksample.sql
可以修改密碼
用戶可以被刪除，在產(chǎn)品環(huán)境建議刪除或鎖定
·DBSNMP/DBSNMP
Oracle Intelligent agent
創(chuàng)建腳本：?/rdbms/admin/catsnmp.sql, called from catalog.sql
可以改變密碼--需要放置新密碼到snmp_rw.ora文件
如果不需要Intelligent Agents，可以刪除
---------------------------------------------------
以下用戶都是可選安裝用戶,如果不需要，就不需要安裝
·CTXSYS/CTXSYS
Oracle interMedia (ConText Cartridge)管理用戶
創(chuàng)建腳本：?/ctx/admin/dr0csys.sql
·TRACESVR/TRACE
Oracle Trace server
創(chuàng)建腳本：?/rdbms/admin/otrcsvr.sql
·ORDPLUGINS/ORDPLUGINS
Object Relational Data (ORD) User used by Time Series, etc.
創(chuàng)建腳本：?/ord/admin/ordinst.sql
·ORDSYS/ORDSYS
Object Relational Data (ORD) User used by Time Series, etc
創(chuàng)建腳本：?/ord/admin/ordinst.sql
·DSSYS/DSSYS
Oracle Dynamic Services and Syndication Server
創(chuàng)建腳本：?/ds/sql/dssys_init.sql
·MDSYS/MDSYS
Oracle Spatial administrator user
創(chuàng)建腳本：?/ord/admin/ordinst.sql
·AURORA$ORB$UNAUTHENTICATED/INVALID
Used for users who do not authenticate in Aurora/ORB
創(chuàng)建腳本：?/javavm/install/init_orb.sql called from ?/javavm/install/initjvm.sql
·PERFSTAT/PERFSTAT
Oracle Statistics Package (STATSPACK) that supersedes UTLBSTAT/UTLESTAT
創(chuàng)建腳本：?/rdbms/admin/statscre.sql
第三部分、備份與恢復(fù)

[Q]如何開(kāi)啟/關(guān)閉歸檔
[A]如果開(kāi)啟歸檔，請(qǐng)保證log_archive_start=true開(kāi)啟自動(dòng)歸檔，否則只能手工歸檔，如果是關(guān)閉了歸檔，則設(shè)置該參數(shù)為false
注意：如果是OPS/RAC環(huán)境，需要先把parallel_server = true注釋掉，然后執(zhí)行如下步驟，最后用這個(gè)參數(shù)重新啟動(dòng)
1、開(kāi)啟歸檔
a. 關(guān)閉數(shù)據(jù)庫(kù)shutdown immediate
b. startup mount
c. alter database archivelog
d. alter database opne
2、禁止歸檔
a. 關(guān)閉數(shù)據(jù)庫(kù)shutdown immediate
b. startup mount
c. alter database noarchivelog
d. alter database open
歸檔信息可以通過(guò)如下語(yǔ)句查看
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination E:\oracle\ora92\database\archive
Oldest online log sequence 131
Next log sequence to archive 133
Current log sequence 133

[Q]怎樣設(shè)置定時(shí)歸檔
[A]9i以上版本，保證歸檔的最小間隔不超過(guò)n秒
設(shè)置Archive_lag_target = n
單位：秒 范圍：0~7200

[Q]不同版本怎么導(dǎo)出/導(dǎo)入
[A]導(dǎo)出用低版本，導(dǎo)入用當(dāng)前版本
如果版本跨越太大，需要用到中間版本過(guò)渡

[Q]不同的字符集之前怎么導(dǎo)數(shù)據(jù)
[A]a.前條件是保證導(dǎo)出/導(dǎo)入符合其他字符集標(biāo)準(zhǔn)，如客戶環(huán)境與數(shù)據(jù)庫(kù)字符集一致。
b.修改dmp文件的2、3字節(jié)為目標(biāo)數(shù)據(jù)庫(kù)的字符集，注意要換成十六進(jìn)制。
參考函數(shù)（以下函數(shù)中的ID是十進(jìn)制的）：
nls_charset_name 根據(jù)字符集ID獲得字符集名稱
nls_charset_id 根據(jù)字符集名稱獲得字符集ID

[Q]怎么樣備份控制文件
[A]再線備份為一個(gè)二進(jìn)制的文件
alter database backup controlfile to '$BACKUP_DEPT/controlfile.000' [reuse];
備份為文本文件方式
alter database backup controlfile to trace [resetlogs|noresetlogs];

[Q]控制文件損壞如何恢復(fù)
[A]1、如果是損壞單個(gè)控制文件
只需要關(guān)閉數(shù)據(jù)庫(kù)，拷貝一個(gè)好的數(shù)據(jù)文件覆蓋掉壞的數(shù)據(jù)文件即可
或者是修改init.ora文件的相關(guān)部分
2、如果是損失全部控制文件，則需要?jiǎng)?chuàng)建控制文件或從備份恢復(fù)
創(chuàng)建控制文件的腳本可以通過(guò)alter database backup controlfile to trace獲取。

[Q]怎么樣熱備份一個(gè)表空間
[A]Alter tablespace 名稱 begin backup;
host cp 這個(gè)表空間的數(shù)據(jù)文件 目的地;
Alter tablespace 名稱 end backup;
如果是備份多個(gè)表空間或整個(gè)數(shù)據(jù)庫(kù)，只需要一個(gè)一個(gè)表空間的操作下來(lái)就可以了。

[Q]怎么快速得到整個(gè)數(shù)據(jù)庫(kù)的熱備腳本
[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]丟失一個(gè)數(shù)據(jù)文件，但是沒(méi)有備份，怎么樣打開(kāi)數(shù)據(jù)庫(kù)
[A]如果沒(méi)有備份只能是刪除這個(gè)數(shù)據(jù)文件了，會(huì)導(dǎo)致相應(yīng)的數(shù)據(jù)丟失。
SQL>startup mount
--ARCHIVELOG模式命令
SQL>Alter database datafile 'file name' offline;
--NOARCHIVELOG模式命令
SQL>Alter database datafile 'file name' offline drop;
SQLl>Alter database open;
注意：該數(shù)據(jù)文件不能是系統(tǒng)數(shù)據(jù)文件

[Q]丟失一個(gè)數(shù)據(jù)文件，沒(méi)有備份但是有該數(shù)據(jù)文件創(chuàng)建以來(lái)的歸檔怎么恢復(fù)
[A]保證如下條件
a. 不能是系統(tǒng)數(shù)據(jù)文件
b. 不能丟失控制文件
如果滿足以上條件，則
SQL>startup mount
SQL>Alter database create datafile 'file name' as 'file name' size ... reuse;
SQL>recover datafile n; -文件號(hào)
或者
SQL>recover datafile 'file name';
或者
SQL>recover database;
SQL>Alter database open;

[Q]聯(lián)機(jī)日志損壞如何恢復(fù)
[A]1、如果是非當(dāng)前日志而且歸檔，可以使用
Alter database clear logfile group n來(lái)創(chuàng)建一個(gè)新的日志文件
如果該日志還沒(méi)有歸檔，則需要用
Alter database clear unarchived logfile group n
2、如果是當(dāng)前日志損壞，一般不能clear，則可能意味著丟失數(shù)據(jù)
如果有備份，可以采用備份進(jìn)行不完全恢復(fù)
如果沒(méi)有備份，可能只能用_allow_resetlogs_corruption=true來(lái)進(jìn)行強(qiáng)制恢復(fù)了，但是，這樣的方法是不建議的，最好在有Oracle support的指導(dǎo)下進(jìn)行。

[Q]怎么樣創(chuàng)建RMAN恢復(fù)目錄
[A]首先，創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)用戶，一般都是RMAN，并給予recovery_catalog_owner角色權(quán)限
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;
然后，用這個(gè)用戶登錄，創(chuàng)建恢復(fù)目錄
rman catalog rman/rman
RMAN> create catalog tablespace tools;
RMAN> exit;
最后，你可以在恢復(fù)目錄注冊(cè)目標(biāo)數(shù)據(jù)庫(kù)了
rman catalog rman/rman target backdba/backdba
RMAN> register database;

[Q]怎么樣在恢復(fù)的時(shí)候移動(dòng)數(shù)據(jù)文件，恢復(fù)到別的地點(diǎn)
[A]給一個(gè)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）中恢復(fù)(restore)控制文件與數(shù)據(jù)文件
[A]可以使用如下方法，在RMAN中恢復(fù)備份片的控制文件
restore controlfile from backuppiecefile;
如果是9i的自動(dòng)備份，可以采用如下的方法
restore controlfile from autobackup;
但是，如果控制文件全部丟失，需要指定DBID，如SET DBID=?
自動(dòng)備份控制文件的默認(rèn)格式是%F，這個(gè)格式的形式為
c-IIIIIIIIII-YYYYMMDD-QQ，其中IIIIIIIIII就是DBID
至于恢復(fù)(restore)數(shù)據(jù)文件，oracle 816開(kāi)始有個(gè)包dbms_backup_restore
在 nomount 狀態(tài)下就可以執(zhí)行，可以讀 815甚至之前的備份片，讀出來(lái)的文件用于恢復(fù)
可以在SQLPLUS中運(yùn)行，如下
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:\Oracle\oradata\penny\control01.ctl');
8 dbms_backup_restore.restoreDataFileto(1,'E:\Oracle\oradata\penny\system01.dbf');
9 dbms_backup_restore.restoreDataFileto(2,'E:\Oracle\oradata\penny\UNDOTBS01.DBF');
10 dbms_backup_restore.restoreDataFileto(3,'E:\ORACLE\ORADATA\PENNY\USERS01.DBF');
11 dbms_backup_restore.restorebackuppiece('D:\orabak\BACKUP_1_4_04F4IAJT.PENNY',done=>done);
12 END;
13 /
PL/SQL 過(guò)程已成功完成。
SQL> alter database mount;

[Q]Rman的format格式中的%s類似的東西代表什么意義
[A]可以參考如下
%c 備份片的拷貝數(shù)
%d 數(shù)據(jù)庫(kù)名稱
%D 位于該月中的第幾天 (DD)
%M 位于該年中的第幾月 (MM)
%F 一個(gè)基于DBID唯一的名稱,這個(gè)格式的形式為c-IIIIIIIIII-YYYYMMDD-QQ,其中IIIIIIIIII為該數(shù)據(jù)庫(kù)的DBID，YYYYMMDD為日期，QQ是一個(gè)1-256的序列
%n 數(shù)據(jù)庫(kù)名稱，向右填補(bǔ)到最大八個(gè)字符
%u 一個(gè)八個(gè)字符的名稱代表備份集與創(chuàng)建時(shí)間
%p 該備份集中的備份片號(hào)，從1開(kāi)始到創(chuàng)建的文件數(shù)
%U 一個(gè)唯一的文件名，代表%u_%p_%c
%s 備份集的號(hào)
%t 備份集時(shí)間戳
%T 年月日格式(YYYYMMDD)

[Q]執(zhí)行exec dbms_logmnr_d.build('Logminer.ora','file directory')，提示下標(biāo)超界，怎么辦
[A]完整錯(cuò)誤信息如下,
SQL> exec dbms_logmnr_d.build('Logminer.ora','file directory')
BEGIN dbms_logmnr_d.build('Logminer.ora','file directory'); END;
*
ERROR 位于第 1 行:
ORA-06532: 下標(biāo)超出限制
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. 運(yùn)行改變后的腳本
SQLPLUS> Connect internal
SQLPLUS> @$ORACLE_HOME/rdbms/admin/dbmslmd.sql
3.重新編譯該包
SQLPLUS> alter package DBMS_LOGMNR_D compile body;

[Q]執(zhí)行execute dbms_logmnr.start_logmnr(DictFileName=>'DictFileName')提示ORA-01843:無(wú)效的月份，這個(gè)是什么原因
[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')失敗，將導(dǎo)致以上錯(cuò)誤
所以解決辦法可以為
1、Alter session set NLS_LANGUAGE=American
2、用類似如下的方法執(zhí)行
execute dbms_logmnr.start_logmnr (DictFileName=> 'f:\temp2\TESTDICT.ora', starttime => TO_DATE(
'01-01-1988','DD-MM-YYYY'), endTime=>TO_DATE('01-01-2988','DD-MM-YYYY'));


__________________
if not now,when?
if not me,who?

歡迎訪問(wèn)我的Blog

天將降大任于斯人也，必先苦其心志，勞其筋骨，餓其體膚

由 piner 于 04-01-17 09:53 最后編輯



?03-12-11 10:25 ? ? ? ?


?
piner
that's life

注冊(cè)日期: 2003 Feb
來(lái)自: 西子湖畔
發(fā)帖數(shù)量: 2527

第四部分、性能調(diào)整
[Q]如果設(shè)置自動(dòng)跟蹤
[A]用system登錄
執(zhí)行$ORACLE_HOME/rdbms/admin/utlxplan.sql創(chuàng)建計(jì)劃表
執(zhí)行$ORACLE_HOME/sqlplus/admin/plustrce.sql創(chuàng)建plustrace角色
如果想計(jì)劃表讓每個(gè)用戶都能使用，則
SQL>create public synonym plan_table for plan_table;
SQL> grant all on plan_table to public;
如果想讓自動(dòng)跟蹤的角色讓每個(gè)用戶都能使用，則
SQL> grant plustrace to public;
通過(guò)如下語(yǔ)句開(kāi)啟/停止跟蹤
SET AUTOTRACE ON |OFF
| ON EXPLAIN | ON STATISTICS | TRACEONLY | TRACEONLY EXPLAIN

[Q]如果跟蹤自己的會(huì)話或者是別人的會(huì)話
[A]跟蹤自己的會(huì)話很簡(jiǎn)單
Alter session set sql_trace true|false
Or
Exec dbms_session.set_sql_trace(TRUE);
如果跟蹤別人的會(huì)話，需要調(diào)用一個(gè)包
exec dbms_system.set_sql_trace_in_session(sid,serial#,true|false)
跟蹤的信息在user_dump_dest 目錄下可以找到或通過(guò)如下腳本獲得文件名稱（適用于Win環(huán)境，如果是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')
最后，可以通過(guò)Tkprof來(lái)解析跟蹤文件，如
Tkprof 原文件 目標(biāo)文件 sys=n


[Q]怎么設(shè)置整個(gè)數(shù)據(jù)庫(kù)系統(tǒng)跟蹤
[A]其實(shí)文檔上的alter system set sql_trace=true是不成功的
但是可以通過(guò)設(shè)置事件來(lái)完成這個(gè)工作，作用相等
alter system set events
'10046 trace name context forever,level 1';
如果關(guān)閉跟蹤，可以用如下語(yǔ)句
alter system set events
'10046 trace name context off';
其中的level 1與上面的8都是跟蹤級(jí)別
level 1：跟蹤SQL語(yǔ)句，等于sql_trace=true
level 4：包括變量的詳細(xì)信息
level 8：包括等待事件
level 12：包括綁定變量與等待事件

[Q]怎么樣根據(jù)OS進(jìn)程快速獲得DB進(jìn)程信息與正在執(zhí)行的語(yǔ)句
[A]有些時(shí)候，我們?cè)贠S上操作，象TOP之后我們得到的OS進(jìn)程，怎么快速根據(jù)OS信息獲得DB信息呢？
我們可以編寫如下腳本：
$more whoit.sh
#!/bin/sh
sqlplus /nolog <<EOF
connect / as sysdba
col machine format a30
col program format a40
set line 200
select sid,serial# ,username,osuser,machine,program,process,to_char(logon_time,'yyyy/mm/dd hh24:mi:ss')
from v\$session where paddr in
( select addr from v\$process where spid in($1));

select sql_text from v\$sqltext_with_newlines
where hash_value in
(select SQL_HASH_value from v\$session where
paddr in (select addr from v\$process where spid=$1)
)
order by piece;

exit;
EOF
然后，我們只要在OS環(huán)境下如下執(zhí)行即可
$./whoit.sh Spid

[Q]怎么樣分析表或索引
[A]命令行方式可以采用analyze命令
如Analyze table tablename compute statistics;
Analyze index|cluster indexname estimate statistics;
ANALYZE TABLE tablename COMPUTE STATISTICS
FOR TABLE
FOR ALL [LOCAL] INDEXES
FOR ALL [INDEXED] COLUMNS;
ANALYZE TABLE tablename DELETE STATISTICS
ANALYZE TABLE tablename VALIDATE REF UPDATE
ANALYZE TABLE tablename VALIDATE STRUCTURE
[CASCADE]|[INTO TableName]
ANALYZE TABLE tablename LIST CHAINED ROWS [INTO TableName]
等等。
如果想分析整個(gè)用戶或數(shù)據(jù)庫(kù)，還可以采用工具包，可以并行分析
Dbms_utility(8i以前的工具包)
Dbms_stats(8i以后提供的工具包)
如
dbms_stats.gather_schema_stats(User,estimate_percent=>100,cascade=> TRUE);
dbms_stats.gather_table_stats(User,TableName,degree => 4,cascade => true);
這是對(duì)命令與工具包的一些總結(jié)
1、對(duì)于分區(qū)表，建議使用DBMS_STATS，而不是使用Analyze語(yǔ)句。
a) 可以并行進(jìn)行，對(duì)多個(gè)用戶，多個(gè)Table
b) 可以得到整個(gè)分區(qū)表的數(shù)據(jù)和單個(gè)分區(qū)的數(shù)據(jù)。
c) 可以在不同級(jí)別上Compute Statistics：?jiǎn)蝹€(gè)分區(qū)，子分區(qū)，全表，所有分區(qū)
d) 可以倒出統(tǒng)計(jì)信息
e) 可以用戶自動(dòng)收集統(tǒng)計(jì)信息
2、DBMS_STATS的缺點(diǎn)
a) 不能Validate Structure
b) 不能收集CHAINED ROWS, 不能收集CLUSTER TABLE的信息，這兩個(gè)仍舊需要使用Analyze語(yǔ)句。
c) DBMS_STATS 默認(rèn)不對(duì)索引進(jìn)行Analyze，因?yàn)槟J(rèn)Cascade是False，需要手工指定為True
3、對(duì)于oracle 9里面的External Table，Analyze不能使用，只能使用DBMS_STATS來(lái)收集信息。

[Q]怎么樣快速重整索引
[A]通過(guò)rebuild語(yǔ)句，可以快速重整或移動(dòng)索引到別的表空間
rebuild有重建整個(gè)索引數(shù)的功能，可以在不刪除原始索引的情況下改變索引的存儲(chǔ)參數(shù)
語(yǔ)法為
alter index index_name rebuild tablespace ts_name
storage(……);
如果要快速重建整個(gè)用戶下的索引，可以用如下腳本，當(dāng)然，需要根據(jù)你自己的情況做相應(yīng)修改
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 owner = USER
SQL>spool off
另外一個(gè)合并索引的語(yǔ)句是
alter index index_name coalesce，這個(gè)語(yǔ)句僅僅是合并索引中同一級(jí)的leaf block
消耗不大，對(duì)于有些索引中存在大量空間浪費(fèi)的情況下，有一些作用。

[Q]如何使用Hint提示
[A] 在select/delete/update后寫/*+ hint */
如 select /*+ index(TABLE_NAME INDEX_NAME) */ col1...
注意/*和+之間不能有空格
如用hint指定使用某個(gè)索引

select /*+ index(cbotab) */ col1 from cbotab;
select /*+ index(cbotab cbotab1) */ col1 from cbotab;
select /*+ index(a cbotab1) */ col1 from cbotab a;
其中
TABLE_NAME是必須要寫的，且如果在查詢中使用了表的別名，在hint也要用表的別名來(lái)代替表名;
INDEX_NAME可以不必寫，Oracle會(huì)根據(jù)統(tǒng)計(jì)值選一個(gè)索引;
如果索引名或表名寫錯(cuò)了，那這個(gè)hint就會(huì)被忽略;

[Q]怎么樣快速?gòu)?fù)制表或者是插入數(shù)據(jù)
[A]快速?gòu)?fù)制表可以指定Nologging選項(xiàng)
如：Create table t1 nologging
as select * from t2;
快速插入數(shù)據(jù)可以指定append提示，但是需要注意
noarchivelog模式下，默認(rèn)用了append就是nologging模式的。
在archivelog下，需要把表設(shè)置程N(yùn)ologging模式。
如insert /*+ append */ into t1
select * from t2
注意：如果在9i環(huán)境中并設(shè)置了FORCE LOGGING，則以上操作是無(wú)效的，并不會(huì)加快，當(dāng)然，可以通過(guò)如下語(yǔ)句設(shè)置為NO FORCE LOGGING。
Alter database no force logging;
是否開(kāi)啟了FORCE LOGGING，可以用如下語(yǔ)句查看
SQL> select force_logging from v$database;

[Q]怎么避免使用特定索引
[A]在很多時(shí)候，Oracle會(huì)錯(cuò)誤的使用索引而導(dǎo)致效率的明顯下降，我們可以使用一點(diǎn)點(diǎn)技巧而避免使用不該使用的索引，如：
表test,有字段a,b,c,d，在a,b,c上建立聯(lián)合索引inx_a(a,b,c)，在b上單獨(dú)建立了一個(gè)索引Inx_b(b)。
在正常情況下，where a=? and b=? and c=?會(huì)用到索引inx_a，
where b=?會(huì)用到索引inx_b
但是，where a=? and b=? and c=? group by b會(huì)用到哪個(gè)索引呢？在分析數(shù)據(jù)不正確（很長(zhǎng)時(shí)間沒(méi)有分析）或根本沒(méi)有分析數(shù)據(jù)的情況下，oracle往往會(huì)使用索引inx_b。通過(guò)執(zhí)行計(jì)劃的分析，這個(gè)索引的使用，將大大耗費(fèi)查詢時(shí)間。
當(dāng)然，我們可以通過(guò)如下的技巧避免使用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是數(shù)字
通過(guò)這樣簡(jiǎn)單的改變，往往可以是查詢時(shí)間提交很多倍
當(dāng)然，我們也可以使用no_index提示，相信很多人沒(méi)有用過(guò)，也是一個(gè)不錯(cuò)的方法：
select /*+ no_index(t,inx_b) */ * from test t
where a=? and b=? and c=? group by b

[Q]Oracle什么時(shí)候會(huì)使用跳躍式索引掃描
[A]這是9i的一個(gè)新特性跳躍式索引掃描(Index Skip Scan).
例如表有索引index(a,b,c)，當(dāng)查詢條件為
where b=?的時(shí)候，可能會(huì)使用到索引index(a,b,c)
如，執(zhí)行計(jì)劃中出現(xiàn)如下計(jì)劃：
INDEX (SKIP SCAN) OF 'TEST_IDX' (NON-UNIQUE)
Oracle的優(yōu)化器(這里指的是CBO)能對(duì)查詢應(yīng)用Index Skip Scans至少要有幾個(gè)條件:
1 優(yōu)化器認(rèn)為是合適的。
2 索引中的前導(dǎo)列的唯一值的數(shù)量能滿足一定的條件（如重復(fù)值很多）。
3 優(yōu)化器要知道前導(dǎo)列的值分布(通過(guò)分析/統(tǒng)計(jì)表得到)。
4 合適的SQL語(yǔ)句
等。

[Q]怎么樣創(chuàng)建使用虛擬索引
[A]可以使用nosegment選項(xiàng)，如
create index virtual_index_name on table_name(col_name) nosegment;
如果在哪個(gè)session需要測(cè)試虛擬索引，可以利用隱含參數(shù)來(lái)處理
alter session set "_use_nosegment_indexes" = true;
就可以利用explain plan for select ……來(lái)看虛擬索引的效果
利用@$ORACLE_HOME/rdbms/admin/utlxpls查看執(zhí)行計(jì)劃
最后，根據(jù)需要，我們可以刪除虛擬索引，如普通索引一樣
drop index virtual_index_name;
注意：虛擬索引并不是物理存在的，所以虛擬索引并不等同于物理索引，不要用自動(dòng)跟蹤去測(cè)試虛擬索引，因?yàn)槟鞘菍?shí)際執(zhí)行的效果，是用不到虛擬索引的。

[Q]怎樣監(jiān)控?zé)o用的索引
[A]Oracle 9i以上，可以監(jiān)控索引的使用情況，如果一段時(shí)間內(nèi)沒(méi)有使用的索引，一般就是無(wú)用的索引
語(yǔ)法為：
開(kāi)始監(jiān)控：alter index index_name monitoring usage;
檢查使用狀態(tài)：select * from v$object_usage;
停止監(jiān)控：alter index index_name nomonitoring usage;
當(dāng)然，如果想監(jiān)控整個(gè)用戶下的索引，可以采用如下的腳本：
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

[Q]怎么樣能固定我的執(zhí)行計(jì)劃
[A]可以使用OUTLINE來(lái)固定SQL語(yǔ)句的執(zhí)行計(jì)劃
用如下語(yǔ)句可以創(chuàng)建一個(gè)OUTLINE
Create oe replace outline OutLn_Name on
Select Col1,Col2 from Table
where ……
如果要?jiǎng)h除Outline，可以采用
Drop Outline OutLn_Name;
對(duì)于已經(jīng)創(chuàng)建了的OutLine，存放在OUTLN用戶的OL$HINTS表下面
對(duì)于有些語(yǔ)句，你可以使用update outln.ol$hints來(lái)更新outline
如update outln.ol$hints(ol_name,'TEST1','TEST2','TEST2','TEST1)
where ol_name in ('TEST1','TEST2');
這樣，你就把Test1 OUTLINE與Test2 OUTLINE互換了
如果想利用已經(jīng)存在的OUTLINE，需要設(shè)置以下參數(shù)
Alter system/session set Query_rewrite_enabled = true
Alter system/session set use_stored_outlines = true

[Q]v$sysstat中的class分別代表什么
[A]統(tǒng)計(jì)類別
1 代表事例活動(dòng)
2 代表Redo buffer活動(dòng)
4 代表鎖
8 代表數(shù)據(jù)緩沖活動(dòng)
16 代表OS活動(dòng)
32 代表并行活動(dòng)
64 代表表訪問(wèn)
128 代表調(diào)試信息

[Q]怎么殺掉特定的數(shù)據(jù)庫(kù)會(huì)話
[A] Alter system kill session 'sid,serial#';
或者
alter system disconnect session 'sid,serial#' immediate;
在win上，還可以采用oracle提供的orakill殺掉一個(gè)線程（其實(shí)就是一個(gè)Oracle進(jìn)程）
在Linux/Unix上，可以直接利用kill殺掉數(shù)據(jù)庫(kù)進(jìn)程對(duì)應(yīng)的OS進(jìn)程

[Q]怎么快速查找鎖與鎖等待
[A]數(shù)據(jù)庫(kù)的鎖是比較耗費(fèi)資源的，特別是發(fā)生鎖等待的時(shí)候，我們必須找到發(fā)生等待的鎖，有可能的話，殺掉該進(jìn)程。
這個(gè)語(yǔ)句將查找到數(shù)據(jù)庫(kù)中所有的DML語(yǔ)句產(chǎn)生的鎖，還可以發(fā)現(xiàn)，任何DML語(yǔ)句其實(shí)產(chǎn)生了兩個(gè)鎖，一個(gè)是表鎖，一個(gè)是行鎖。
可以通過(guò)alter system kill session ‘sid,serial#’來(lái)殺掉會(huì)話
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
如果發(fā)生了鎖等待，我們可能更想知道是誰(shuí)鎖了表而引起誰(shuí)的等待
以下的語(yǔ)句可以查詢到誰(shuí)鎖了表，而誰(shuí)在等待。
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
以上查詢結(jié)果是一個(gè)樹(shù)狀結(jié)構(gòu)，如果有子節(jié)點(diǎn)，則表示有等待發(fā)生。如果想知道鎖用了哪個(gè)回滾段，還可以關(guān)聯(lián)到V$rollname，其中xidusn就是回滾段的USN

[Q] 如何有效的刪除一個(gè)大表(extent數(shù)很多的表)
[A] 一個(gè)有很多(100k)extent的表，如果只是簡(jiǎn)單地用drop table的話，會(huì)很大量消耗CPU（Oracle要對(duì)fet$、uet$數(shù)據(jù)字典進(jìn)行操作），可能會(huì)用上幾天的時(shí)間，較好的方法是分多次刪除extent，以減輕這種消耗：
1. truncate table big-table reuse storage;
2. alter table big-table deallocate unused keep 2000m ( 原來(lái)大小的n-1/n);
3. alter table big-table deallocate unused keep 1500m ;
....
4. drop table big-table;

[Q]如何收縮臨時(shí)數(shù)據(jù)文件的大小
[A]9i以下版本采用
ALTER DATABASE DATAFILE 'file name' RESIZE 100M類似的語(yǔ)句
9i以上版本采用
ALTER DATABASE TEMPFILE 'file name' RESIZE 100M
注意，臨時(shí)數(shù)據(jù)文件在使用時(shí)，一般不能收縮，除非關(guān)閉數(shù)據(jù)庫(kù)或斷開(kāi)所有會(huì)話，停止對(duì)臨時(shí)數(shù)據(jù)文件的使用。

[Q]怎么清理臨時(shí)段
[A]可以使用如下辦法
1、 使用如下語(yǔ)句查看一下認(rèn)誰(shuí)在用臨時(shí)段
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、 那些正在使用臨時(shí)段的進(jìn)程
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、 執(zhí)行清理操作
alter session set events 'immediate trace name DROP_SEGMENTS level TS#+1'
說(shuō)明：
temp表空間的TS# 為 3*, So TS#+ 1= 4
如果想清除所有表空間的臨時(shí)段，則
TS# = 2147483647

[Q]怎么樣dump數(shù)據(jù)庫(kù)內(nèi)部結(jié)構(gòu)，如上面顯示的控制文件的結(jié)構(gòu)
[A]常見(jiàn)的有
1、分析數(shù)據(jù)文件塊，轉(zhuǎn)儲(chǔ)數(shù)據(jù)文件n的塊m
alter system dump datafile n block m
2、分析日志文件
alter system dump logfile logfilename;
3、分析控制文件的內(nèi)容
alter session set events 'immediate trace name CONTROLF level 10'
4、分析所有數(shù)據(jù)文件頭
alter session set events 'immediate trace name FILE_HDRS level 10'
5、分析日志文件頭
alter session set events 'immediate trace name REDOHDR level 10'
6、分析系統(tǒng)狀態(tài)，最好每10分鐘一次，做三次對(duì)比
alter session set events 'immediate trace name SYSTEMSTATE level 10'
7、分析進(jìn)程狀態(tài)
alter session set events 'immediate trace name PROCESSSTATE level 10'
8、分析Library Cache的詳細(xì)情況
alter session set events 'immediate trace name library_cache level 10'

[Q]如何獲得所有的事件代碼
[A] 事件代碼范圍一般從10000 to 10999，以下列出了這個(gè)范圍的事件代碼與信息
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系統(tǒng)上，事件信息放在一個(gè)文本文件里
$ORACLE_HOME/rdbms/mesg/oraus.msg
可以用如下腳本查看事件信息
event=10000
while [ $event -ne 10999 ]
do
event=`expr $event + 1`
oerr ora $event
done
對(duì)于已經(jīng)確保的/正在跟蹤的事件，可以用如下腳本獲得
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以上提供的一個(gè)非常好的性能監(jiān)控與診斷工具，基本上全部包含了BSTAT/ESTAT的功能，更多的信息
可以參考附帶文檔$ORACLE_HOME/rdbms/admin/spdoc.txt。
安裝Statspack:
cd $ORACLE_HOME/rdbms/admin
sqlplus "/ as sysdba" @spdrop.sql -- 卸載，第一次可以不需要
sqlplus "/ as sysdba" @spcreate.sql -- 需要根據(jù)提示輸入表空間名
使用Statspack:
sqlplus perfstat/perfstat
exec statspack.snap; -- 進(jìn)行信息收集統(tǒng)計(jì)，每次運(yùn)行都將產(chǎn)生一個(gè)快照號(hào)
-- 獲得快照號(hào)，必須要有兩個(gè)以上的快照，才能生成報(bào)表
select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT;
@spreport.sql -- 輸入需要查看的開(kāi)始快照號(hào)與結(jié)束快照號(hào)
其他相關(guān)腳本s:
spauto.sql - 利用dbms_job提交一個(gè)作業(yè)，自動(dòng)的進(jìn)行STATPACK的信息收集統(tǒng)計(jì)
sppurge.sql - 清除一段范圍內(nèi)的統(tǒng)計(jì)信息，需要提供開(kāi)始快照與結(jié)束快照號(hào)
sptrunc.sql - 清除(truncate)所有統(tǒng)計(jì)信息