|
Oracle 小知識 總結(jié)(一) 收藏
1. 每天的8：00到23：00每隔5分鐘執(zhí)行一個sql語句的JOB --建立一個存儲過程 CREATE OR REPLACE PROCEDURE p_jobtest IS
v_hh VARCHAR2(2);
BEGIN
v_hh := to_char(SYSDATE, 'hh24');
IF v_hh >= '08' AND v_hh <= '22' THEN
--你的sql語句
NULL;
END IF;
END;
/
--提交一個JOB DECLARE
v_jobno NUMBER;
BEGIN
dbms_job.submit(v_jobno,
'p_jobtest;',
trunc(SYSDATE, 'mi') + 1 / 1440,
'trunc(SYSDATE, ''mi'') + 5 / 1440');
END;
/
2. RMAN 中的list 命令顯示的信息是從控制文件里獲取的,，如果使用rm等命令手工的刪除備份文件,，這個動作不會同步到控制文件,，造成不一致,，這種不一致會導(dǎo)致使用rman時報(bào)錯,?？梢允褂胐elete 刪除這些過期的記錄，在用就不會報(bào)錯了,。 RMAN>crosscheck copy; RMAN>list copy;
RMAN>delete expired copy;
3. 觸發(fā)LGWR進(jìn)程的條件有： 1. 用戶提交
2. 有1/3重做日志緩沖區(qū)未被寫入磁盤
3. 有大于1M的重做日志緩沖區(qū)未被寫入磁盤
4. 3秒超時
5. DBWR 需要寫入的數(shù)據(jù)的SCN大于LGWR記錄的SCN,，DBWR 觸發(fā)LGWR寫入。
4. 觸發(fā)DBWR進(jìn)程的條件有： 1. DBWR超時,，大約3秒
2. 系統(tǒng)中沒有多余的空緩沖區(qū)來存放數(shù)據(jù)
3. CKPT 進(jìn)程觸發(fā)DBWR
5. 每隔3秒鐘ckpt會去更新控制文件和數(shù)據(jù)文件,，記錄checkpoint執(zhí)行的情況。 當(dāng)發(fā)生checkpoint時,，會把SCN寫到四個地方去,。 三個地方于control file內(nèi)，一個在datafile header,。
6. 觸發(fā)CheckPoint(檢查點(diǎn)) 條件有很多,，比如： 1. 通過正常事務(wù)處理或者立即選項(xiàng)關(guān)閉例程時(shutdown immediate或者Shutdown normal), 2. 當(dāng)通過設(shè)置初始化參數(shù):
LOG_CHECKPOINT_INTERVAL,
LOG_CHECKPOINT_TIMEOUT ,
FAST_START_IO_TARGET 強(qiáng)制時；
3. 當(dāng)數(shù)據(jù)庫管理員手動請求時:
ALter system checkpoint;
alter tablespace ... offline;
4. 每次日志切換時;
alter system switch logfile
注意： 1. alter system switch logfile也將觸發(fā)完全檢查點(diǎn)的發(fā)生,。
2. alter database datafile ... offline 不會觸發(fā)檢查點(diǎn)進(jìn)程,。
7. RECOVER DATABASE UNTIL CANCEL 和 RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE; 區(qū)別 1) RECOVER DATABASE UNTIL CANCEL ==> OPEN DATABASE RESETLOG ==> DATAFILE HEADER SCN一定會小于CONTROLFILE的DATAFILE SCN
如果你有進(jìn)行RESTORE DATAFILE，則該RESTORE的DATAFILE HEADER SCN一定會小于目前CONTROLFILE的DATAFILE SCN,，此時會無法開啟數(shù)據(jù)庫,，必須進(jìn)行media recovery。 重做archive log直到該datafile header的SCN=current scn 8. 建表前判斷表是否存在的存儲過程,。 在Oracle 中沒有drop table... if exists語法,。 所以我們可以在創(chuàng)建表之前用如下存儲過程來判斷,。
create or replace procedure proc_dropifexists(
p_table in varchar2
) is
v_count number(10);
begin
select count(*)
into v_count
from user_objects
where object_name = upper(p_table);
if v_count > 0 then
execute immediate 'drop table ' || p_table ||' purge';
end if;
end;
9. 表屬性中pctused,和 pctfree 作用 表示數(shù)據(jù)塊什么時候移入和移出freelist。 pctused：如果數(shù)據(jù)塊的使用率小于pctused的值,，則該數(shù)據(jù)塊重新加入到fresslist中,。
pctfree：如果數(shù)據(jù)塊的使用率高于pctfree的值，則該數(shù)據(jù)塊從freelist中移出,。
10. oracle表空間大小沒有限制,，根存儲空間而定。 oracle9i或以下,單個數(shù)據(jù)文件最大32G(對于8K的數(shù)據(jù)塊),整個數(shù)據(jù)庫最多有64K個數(shù)據(jù)文件.單個表空間的數(shù)據(jù)文件數(shù)量沒有具體的限制,也應(yīng)該是在64K以下. oracle10g以上,引入了bigfile tablespace,bigfile tablespace只有一個數(shù)據(jù)文件,最大為4G*8k=32T database file size: Operating system dependent. Limited by maximum operating system file size; typically 222(2的22次方） or 4M blocks
11. Oracle利用現(xiàn)有的表創(chuàng)建一張新表,，只要表結(jié)構(gòu)相同 create table david as select * from all_users where 1<>1; 12. 循環(huán)插入數(shù)據(jù)
declare i integer; begin for i in 1..100000 loop insert into test values(i); end loop; commit; end; 13. 開發(fā)人員通常習(xí)慣賦予所有用戶DBA權(quán)限,，查看權(quán)限
Select * From User_Role_Privs Select * From User_Sys_Privs 14. 看數(shù)據(jù)文件大小,單位是M
select round(bytes/(1024*1024),0) total_space from dba_data_files select sum(bytes/(1024*1024)) total_space from dba_data_files 15 控制文件大小
select sum( block_size*file_size_blks )/1024/1024 from v$controlfile 16. 建立表空間
CREATE TABLESPACE data01 DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M UNIFORM SIZE 128k; #指定區(qū)尺寸為128k,如不指定，區(qū)尺寸默認(rèn)為64k刪除表空間 DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES; 修改表空間大小 alter database datafile '/path/NADDate05.dbf' resize 100M 查看表空間： select tablespace_name, file_name, sum(bytes)/1024/1024 table_size from dba_data_files group by tablespace_name,file_name;
17. 有沒有被lock,，可以通過這2張dynamic view來確定：
v$locked_object,V$session 可以把該 session殺掉,。
select sid,serial# from v$session where username ='XXXX' 把得到的sid，serial#號替換到下面的語句中： alter system kill session 'SID,SERIAL#' 18. PL/SQL oracle 查詢前10條信息
SELECT * FROM table WHERE ROWNUM < 11 select * from ( select * from table order by desc) where rownum <=5 == select top 5 * from table; 19. 查看表上是否存在的索引
select * from user_indexes where table_name = 'yourtablename' create index IX_Tablename_column on tablename(column) 20. select id, id2, round((id/id2)*100,2) || '%' percent from test;
21. 查詢表的行數(shù)
select count(*) from table_name; 全表掃描 ,，會自已找表有索引列并且該列為非空的(因?yàn)橹挥蟹强詹拍艽_保記錄數(shù)是全的),走INDEX_FFS. select count(1) from table_name; 不走索引,，效率要高，但在表中有非空索引時也是走 INDEX_FFS 的 22. 用function來查看當(dāng)前session的trace文件的文件名
如下 create or replace function gettracename return varchar2 is v_result varchar2(200); begin SELECT d.VALUE || '/' || LOWER (RTRIM (i.INSTANCE, CHR (0))) || '_ora_' || p.spid || '.trc' into v_result 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; return v_result; end gettracename; 運(yùn)行SQL> select gettracename() from dual;即可
GETTRACENAME() ----------------------------------------------------------------------- F:\DEVELOPER\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP/orcl_ora_3800.trc
23 . select case when(a>b) then a else b end from TableA; select greatest(a,b) from tb 24. TRUNCATE TABLE Table_name
使用truncate時,，速度比delete,，但是系統(tǒng)不做Log。清空表的數(shù)據(jù),，僅保留類別結(jié)構(gòu),，被刪除的數(shù)據(jù)不能ROLLBACK，這點(diǎn)是與DELETE的主要差別.不能Rollback也就是會刪除log文件.在SQL Server里面如果你用Truncate,，自動增長的行又會從1開始 25. select substrb('大小abc',1,6) from dual;
26. 表中的數(shù)據(jù)如下圖所示 a b c 1 PP41982 SO90029 2 PP41982 SO90029 只取出字段b,，c不重復(fù)的字段， select b,c from t group by b,c having(count(b) <2)
27. 查詢鎖的情況
1). insert into test values(1); 2). select userenv('sid') from dual; 3). select * from v$lock where sid='' sid 在v$session 中有這個列,，可以結(jié)合v$session 查詢更多的信息 28. 刪除重復(fù)行：
SQL> DELETE FROM a WHERE ROWID IN( SELECT MAX(ROWID) FROM a); 已刪除 1 行,。 SQL> select * from a; NAME ID ORDER_TITLE -------------------- ---------- -------------------- 中國科學(xué)技術(shù)大學(xué) 1 科學(xué) 中國科學(xué)技術(shù)大學(xué) 1 科學(xué) SQL> 29. 查看索引信息
--查看索引名稱
SELECT * FROM USER_INDEXES; -- 查看索引列名 SELECT * FROM DBA_IND_COLUMNS; SELECT * FROM USER_IND_COLUMNS; SELECT * FROM ALL_IND_COLUMNS; 30. oracle trunc()函數(shù)用法
1.TRUNC(for dates) TRUNC函數(shù)為指定元素而截去的日期值。 其具體的語法格式如下： TRUNC（date[,fmt]） 其中： date 一個日期值 fmt 日期格式,，該日期將由指定的元素格式所截去,。忽略它則由最近的日期截去 下面是該函數(shù)的使用情況： TRUNC（TO_DATE(’24-Nov-1999 08:00 pm’,’dd-mon-yyyy hh:mi am’)） =’24-Nov-1999 12:00:00 am’ TRUNC（TO_DATE(’24-Nov-1999 08:37 pm’,’dd-mon-yyyy hh:mi am’,’hh’)） =’24-Nov-1999 08:00:00 am’ 2.TRUNC(for number)
TRUNC函數(shù)返回處理后的數(shù)值，其工作機(jī)制與ROUND函數(shù)極為類似,，只是該函數(shù)不對指定小數(shù)前或后的部分做相應(yīng)舍入選擇處理,，而統(tǒng)統(tǒng)截去。 其具體的語法格式如下 TRUNC（number[,decimals]） 其中： number 待做截取處理的數(shù)值 decimals 指明需保留小數(shù)點(diǎn)后面的位數(shù),?？蛇x項(xiàng)，忽略它則截去所有的小數(shù)部分 下面是該函數(shù)的使用情況： TRUNC（89.985，2）=89.98 TRUNC（89.985）=89 TRUNC（89.985,，-1）=80 注意：第二個參數(shù)可以為負(fù)數(shù),，表示為小數(shù)點(diǎn)左邊指定位數(shù)后面的部分截去,，即均以0記,。 31. local是局部有序，整體無序,，global是有序的,，所以local可能會比global慢，得看你的sql語句怎么寫的,，需求是什么樣的
global索引->自己想怎么玩就怎么玩 local索引->表怎么玩它就怎么玩 32,， 查看磁盤物理讀寫情況：
SELECT NAME,phyrds, phywrts,readtim,writetim FROM v$filestat a, v$datafile b WHERE a.FILE#=b.FILE# ORDER BY readtim DESC; 33. 從表中篩選出所有能被5整除的value值數(shù)據(jù)
select * from table where mod(datavalue,5) = 0; 34. Union與Union All的區(qū)別 如果我們需要將兩個select語句的結(jié)果作為一個整體顯示出來，我們就需要用到union或者union all關(guān)鍵字,。union(或稱為聯(lián)合)的作用是將多個結(jié)果合并在一起顯示出來,。 union和union all的區(qū)別是,union會自動壓縮多個結(jié)果集合中的重復(fù)結(jié)果，而union all則將所有的結(jié)果全部顯示出來,，不管是不是重復(fù),。 Union：對兩個結(jié)果集進(jìn)行并集操作，不包括重復(fù)行,，同時進(jìn)行默認(rèn)規(guī)則的排序,； Union All：對兩個結(jié)果集進(jìn)行并集操作，包括重復(fù)行,，不進(jìn)行排序,； select empno,ename from emp union select deptno,dname from dept 我們沒有必要在每一個select結(jié)果集中使用order by子句來進(jìn)行排序，我們可以在最后使用一條order by來對整個結(jié)果進(jìn)行排序,。例如：
select empno,ename from emp union select deptno,dname from dept order by ename; 35. 查看看到A用戶下的所有數(shù)據(jù)量>100萬的表的信息
select * from user_all_tables a where a.num_rows>1000000 前提是a用戶下所有表的統(tǒng)計(jì)信息都是最新的,。 保險的辦法是所有表都count一遍: select 'select '||''''||table_name ||''','||'count(*) from '||table_name from user_all_tables ; 把上面這段sql的執(zhí)行結(jié)果拷貝出來執(zhí)行即可 36. SQLPLUS 里執(zhí)行 EXPLAIN PLAN SQL>EXPLAIN PLAN FOR 你的sql語句; 如 SQL>EXPLAIN PLAN FOR SELECT * FROM EMP WHERE EMPNO=7369; 然后 SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE')); 查看結(jié)果就是前面SQL語句的執(zhí)行計(jì)劃。 37. nvarchar2(2000) 不區(qū)別漢字和字母 varchar2(4000) 只能存儲2000 個漢字 nvarchar2最大2000 varchar2 最大4000 38. 更新表被鎖,，KILL spid后,，select * from tabname for update 獨(dú)占資源。
v$locked_object dba_objects 聯(lián)合可以知道鎖表的session 39. PGA中sort_area_size大小不夠時,，用到臨時表空間,。 40. oracle沒有標(biāo)識列，自動增長的這個概念 需要用序列來實(shí)現(xiàn)
CREATE SEQUENCE sid INCREMENT BY 1 START WITH 1 MAXVALUE 99999999 select sid.nextval, --取下一個序列
sid.currval --取當(dāng)前序列 from dual; 41. IMP/EXP 的buffer達(dá)到1M以后,，性能的提升并不大,，5M或者10M的足夠用 42. 如果null參與聚集運(yùn)算，則除count(*)之外其它聚集函數(shù)都忽略null.
如： ID DD 1 e 2 null select count(*) from table --結(jié)果是2 select count(DD) from table ---結(jié)果是1 count(1)和count(主鍵) 這兩個只掃描主鍵Index就可以得到數(shù)據(jù),， count(*)是掃描表的,。 所以count(1)和count(主鍵)這兩個效率高。 還有一種寫法是count（ROWID）這也是只掃描Index的，效率高,。 43. linux 掛在windows 共享的盤 1. 啟動nfs服務(wù): service nfs start 2.mount -o username=user,password=123456 //10.85.2.194/share /mnt 44. Kill session 并使?fàn)顟B(tài)直接變成killed
EXECUTE IMMEDIATE ''ALTER SYSTEM KILL SESSION '''':sid,:serial#'''' IMMEDIATE''
EXECUTE IMMEDIATE ''ALTER SYSTEM KILL SESSION '''':sid,:serial#'''' immediate是立即kill,，不會有status 會變成killed狀態(tài)的，清楚了在v$session里的信息
45. truncate 只是刪除了表中的記錄,，并不會改變表的結(jié)構(gòu)及依賴約束,，所以truncate表后表的索引依然存在,但是表和索引所占用的空間會恢復(fù)到初始大小 46. 這是看高速緩存命中率小于80%的SQL SELECT EXECUTIONS , DISK_READS, BUFFER_GETS, round((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio, ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run, SQL_TEXT FROM V$SQLAREA WHERE EXECUTIONS>0 AND BUFFER_GETS > 0 AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8 ORDER BY 2 desc,4 DESC; 47. 數(shù)據(jù)庫在主備庫切換之后要手動的用SQL來檢查檢查有沒有死鎖，如果有,kill 就可以了.. SQL> select 'alter system kill session '''||sid||','||serial#||''';' from v$session where sid in (select sid from v$lock where block = 1);
'ALTER SYSTEM KILL SESSION'''||SID||','||SERIAL#||''';' 48. EOF是標(biāo)記控制字符開始,，到結(jié)束,，隨便什么字符都可以用的
sqlplus '/ as sysdba' <<eof
{ shutdown immediate; startup force dba pfile=$ORACLE_HOME/dbs/init.ora; shutdown immediate; } exit; eof 49. parallel(table,4) 并行度為4 parallel(table) 如果使用parallel 但未指定并行度，則DOP要通過初始化參數(shù)CPU_count 和Parallel_THREADS_PER_CPU計(jì)算得到,， 并行度為4的程序,，最多可以分配或創(chuàng)建9個并行執(zhí)行服務(wù)器來滿足這個事務(wù)操作，所以并行操作速度有很大提高,，但對CPU占用比較多
并行操作增加了事務(wù)操作的性能,，但會連續(xù)的記錄重做日志，并且造成瓶頸,，所以可以使用nologging 模式來避免瓶頸
sql> alter table table_name NOLOGGING; 50. 約束名從表user_constraints表中找 SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME='FJ5W_FZ_JMD_S' 51. Oracle確實(shí)沒有convert函數(shù),，只有to_char() 和 to_date()... sql: CONVERT(VARCHAR(10),GETDATE(),111) oracle: to_char(sysdate,'yyyy/mm/dd') 52. V$視圖 是由catalog.sql 腳本創(chuàng)建的. 所以升級系統(tǒng)后要執(zhí)行這個腳本.. 53. 用Oracle的orapwd 命令 orapwd file=D:\PWDorcl.ora password=admin entries=5; windows下oracle默認(rèn)的位置是db_1/database目錄，默認(rèn)的文件名是pwdSID.ora,，對于別的文件名是不認(rèn)的,。linux下oracle默認(rèn)的位置是$ORACLE_HOME/dbs目錄，默認(rèn)的文件名是orapwSID,，對于別的文件名是不認(rèn)的,。 其中參數(shù)entries的含義是表示口令文件中可以存放的最大用戶數(shù)，對應(yīng)于允許以SYSDBA/SYSOPER權(quán)限登錄數(shù)據(jù)庫的最大用戶數(shù),，如果用戶數(shù)超過這個值只能重建口令文件,，增大entries。 54. oracle表空間大小沒有限制,，根存儲空間而定,。 oracle9i或以下,單個數(shù)據(jù)文件最大32G(對于8K的數(shù)據(jù)塊),整個數(shù)據(jù)庫最多有64K個數(shù)據(jù)文件.單個表空間的數(shù)據(jù)文件數(shù)量沒有具體的限制,也應(yīng)該是在64K以下. oracle10g以上,引入了bigfile tablespace,bigfile tablespace只有一個數(shù)據(jù)文件,最大為4G*8k=32T database file size: Operating system dependent. Limited by maximum operating system file size; typically 222(2的22次方） or 4M blocks 55. ROWNUM 是查詢時的一個記錄號，是一個偽列 rownum只和最終輸出結(jié)果order by之前的順序一致 select rownum,t.* from user_tables t;
select rownum,t.* from(select * from user_tables order by table_name)t; select * from (select rownum,t.* from user_tables t order by table_name); 56. 你的數(shù)據(jù)庫是dedicated還是shared模式Oracle數(shù)據(jù)庫服務(wù)器,，根據(jù)客戶端請求后process的調(diào)度方式,，分為dedicated（專用）模式和shared（共享）模式。
專用模式意味著每個客戶端的連接后,，Oracle都會分配一個新的process和自進(jìn)行交互,。而共享模式是，通過一個調(diào)度程序,，來分配process,，有可能是以前客戶端已經(jīng)處理過的空閑的process,，由于process的模式不是專用的，所以相對來說,，節(jié)省資源,。 那么我們?nèi)绾蝸聿榭次覀兊膐racle是哪種模式呢? Window系統(tǒng)下，通過任務(wù)管理列表里,，查看Oracle.exe進(jìn)程所占用的線程數(shù),，如果沒有這個指標(biāo)，可以通過view->select columns->checked Thread count.即可,。 用sqlplus連接成功以后,，thread count如果會增加，即為專用模式,，來一個增加一個，走一個減一個,。反之,，共享模式。 Linux下,，通過ps oracle查看oracle的進(jìn)程個數(shù),。 和window的判斷方法一樣，不過命令不同而已,。 我們也可以查看數(shù)據(jù)庫的parameter,，如果shared_servers的數(shù)目大于0的話，即是,。 57. order by t.tm_error desc,， 必須是 group by里的字符，或者是統(tǒng)計(jì)字段,。 58. 大量更新表時：
1.關(guān)掉tableb 的所有觸發(fā)器,，這個一定要關(guān)掉，moving data的時候一定要全部關(guān)掉,，不然批量操作的時候卡死你Y的,。 alter system tableb disable all triggers; 執(zhí)行完畢之后，啟動觸發(fā)器 alter system tableb enable all triggers; 2,，除了主鍵索引之外,，tableb表剩余的索引全部刪除掉。等執(zhí)行完畢之后,，重建索引(索引重建很快,，我的800萬數(shù)據(jù)的表的6個索引重建才花了2分鐘而已) 59. 訪問V$FIXED_VIEW_DEFINITION 視圖可以獲取組成V$視圖的底層X$表的所有信息 select count(*) from v$fixed_table where name like 'V%'; select view_name from V$fixed_view_definition; select count(*) from v$fixed_view_definition; select view_definition from V$FIXED_VIEW_DEFINITION WHERE view_name='V$PX_SESSION'; 60. dba_views 是從Oracle底層數(shù)據(jù)庫的表中得到的，不是從X$表或者v$視圖,。 SQL> SET LONG 2000000 SQL>select text from dba_views where viewname='DBA_IND_PARTITIONS'; 61. Oracle 10.2.0.1 中有613張X$ 表,，9i 有394張。 X$表包含了特定實(shí)例的各方面的信息，如當(dāng)前的配置信息,，連接到實(shí)例的會話,，以及豐富而有價值的性能信息。 X$表并不是駐留在數(shù)據(jù)庫文件的永久表或臨時表,。X$表僅僅駐留在內(nèi)存中,，當(dāng)實(shí)例啟動時，他們就創(chuàng)建了,，在內(nèi)存中進(jìn)行實(shí)時的維護(hù),。 它們中的大多數(shù)至少需要裝載或已經(jīng)打開的數(shù)據(jù)庫。X$表為SYS用戶所擁有,，并且是只讀的,。 不能進(jìn)行DML(更新，插入,，刪除),。 62. Parse CPU to Parse Elapsd %: 127.27 % Non-Parse CPU: 97.12 parse cpu = amount of cpu time used to parse elapsed time parsing = amount of time on the wall clock spent parsing. 100*（parse time cpu / parse time elapsed）= Parse CPU to Parse Elapsd % in a perfect world, with no contention -- parse cpu = parse elapsed. ratio = 100% in a bad world, it takes longer to parse (elapsed) then cpu time used (contention). ratio < 100% in your case, what this is saying is the CPU exceeded the elapsed, which technically is not possible -- but happens due to the way "small fast things" are measured on computers. It is hard to measure things that happen very rapidly accurately. So, this ratio, when > 100%, is the same as "100%" for all intents and purposes 63. sqlnet.ora文件里的內(nèi)容注釋掉，在重啟下lsnrctl,，應(yīng)該就可以： #SQLNET.AUTHENTICATION_SERVICES = (NTS) Easy Connect指的是使用conn scott/tiger@hostname (or ip)：port/global database name的方式連接數(shù)據(jù)庫,，這種方法不需要tnsnames.ora文件的任何內(nèi)容． 在sqlnet.ora中需要聲明你使用的命名方法。需要注意的是default domain,，如果你聲明了,，那么在tnsnames.ora中必須在net service name后面把域名附加上。這樣才能保證你在conn scott/tiger@netsvname 時候能夠成功 64. sqlnet.ora文件決定找數(shù)據(jù)庫服務(wù)器別名的方式 默認(rèn)的參數(shù)有 NAMES.DEFAULT_DOMAIN = WORLD NAMES.DIRECTORY_PATH = (TNSNAMES, ONAMES, HOSTNAME) 如果你的ORACLE客戶端和服務(wù)器默認(rèn)的域名不一樣,，需要用#號注釋第一行 #NAMES.DEFAULT_DOMAIN = WORLD 使它不起作用,。 NAMES.DIRECTORY_PATH指定找服務(wù)器別名的順序 (本地的tnsnames.ora文件, 命名服務(wù)器, 主機(jī)名方式) 65. 在日文操作系統(tǒng)下用pl sql開發(fā) 要求只能輸入半角，用Length(a),，LengthB(a)可以判斷出是否是半角,。 但是還有個要求是不能輸入日本語，也就是要怎么判斷是'半角片假名'呢,？ 用TO_SINGLE_BYTE()函數(shù)轉(zhuǎn)成半角 在插入 66. SQL> ,！lsnrctl set log_status off; ！ 在SQL里面表示執(zhí)行非SQL的語句 如： SQL> ,！fdisk -l window下是$,linux下是! 67. 跨schema的交叉型trigger在exp/imp時會丟失,，因?yàn)樗蕾嚨幕碓趀xp/imp時斷開了。 除非你把這些shema全都導(dǎo)出,。 68. Number的數(shù)據(jù)聲明如下： 表示 作用 說明 Number(p, s) 聲明一個定點(diǎn)數(shù) p(precision)為精度,，s(scale)表示小數(shù)點(diǎn)右邊的數(shù)字個數(shù)，精度最大值為38,，scale的取值范圍為-84到127 Number(p) 聲明一個整數(shù) 相當(dāng)于Number(p, 0) Number 聲明一個浮點(diǎn)數(shù) 其精度為38,，要注意的是scale的值沒有應(yīng)用,，也就是說scale的指不能簡單的理解為0，或者其他的數(shù),。 定點(diǎn)數(shù)的精度(p)和刻度(s)遵循以下規(guī)則： 當(dāng)一個數(shù)的整數(shù)部分的長度 > p-s 時,，Oracle就會報(bào)錯 當(dāng)一個數(shù)的小數(shù)部分的長度 > s 時，Oracle就會舍入,。 當(dāng)s(scale)為負(fù)數(shù)時,，Oracle就對小數(shù)點(diǎn)左邊的s個數(shù)字進(jìn)行舍入。 當(dāng)s > p 時, p表示小數(shù)點(diǎn)后第s位向左最多可以有多少位數(shù)字,，如果大于p則Oracle報(bào)錯,，小數(shù)點(diǎn)后s位向右的數(shù)字被舍入 69. oracle update 多表關(guān)聯(lián) UPDATE a SET (ID, NAME) = (SELECT b.ID, b.NAME FROM b WHERE a.ID = b.ID) WHERE EXISTS (SELECT 1 FROM b WHERE a.ID = b.ID) 70. 查看SCN: SELECT dbms_flashback.get_system_change_number FROM dual; SELECT CURRENT_SCN FROM V$DATABASE; 71. 注意理解系統(tǒng)時間標(biāo)記與scn的每5分鐘匹配一次這句話，舉個例子,，比如scn:339988,339989分別匹配08-05-3013:52:00和2008-13:57:00,，則當(dāng)你通過as of timestamp查詢08-05-30 13:52:00或08-05-30 13:56:59這段時間點(diǎn)內(nèi)的時間時，oracle都會將其匹配為scn:339988到undo表空間中查找,，也就說在這個時間內(nèi),，不管你指定的時間點(diǎn)是什么，查詢返回的都將是08-05-30 13:52:00這個時刻的數(shù)據(jù),。 查看SCN和timestamp之間的對應(yīng)關(guān)系：
select scn,to_char(time_dp,'yyyy-mm-dd hh24:mi:ss')from sys.smon_scn_time; 72. 當(dāng)查詢的記錄的結(jié)果集大于總記錄的20%時，一定要使用全表掃描 73. 當(dāng)AWR顯示,，占用資源較多的SQL是類似時,，這是對這些SQL就應(yīng)該使用綁定變量來減少硬解析. 74. select name,value ,ISSYS_MODIFIABLE from v$parameter 如果ISSYS_MODIFIABLE 返回的是false，說明該參數(shù)無法用alter system語句動態(tài)修改,需要重啟數(shù)據(jù)庫 75. oracle子查詢中能使用order by from 子句后面的內(nèi)聯(lián)視圖是可以使用order by子句進(jìn)行排序的,。 然而,，其它視圖或子查詢是不能用order by進(jìn)行排序的 如果你要用選擇前幾條的話，需要在套一層變成from后面的內(nèi)聯(lián)視圖,。 比如 select * from dept a 2 where a.deptno in 3 ( select depton from ( 4 select b.deptno from dept b 5 order by b.dname 6 ) [where rownum < 5]) 76. 修改temp表空間自動增長： alter database tempfile 'D:\ORACLE\ORADATA\DBA\TEMP01.DBF' autoextend on next 20m; 修改表空間自動增長： alter database datefile 'D:\ORACLE\ORADATA\DBA\user01.DBF' autoextend on next 20m; 77. alter index rebuild與alter index rebuild online的區(qū)別 online時可以在該索引的基表上執(zhí)行DML,，在在對基表操作的同時可以REBUILD INDEX，但是不能執(zhí)行DDL語句,，所以他們的鎖機(jī)制是不樣的,。 創(chuàng)建索引時通常會對該表設(shè)置一個表級共享(DML)鎖,如果設(shè)置ONLINE , 如果是非ONLINE方式，通常會對該表設(shè)置一個表級共享(DML)鎖,，那么就對DML語句沖突,，如果設(shè)置ONLINE ,(會使用臨時日志IOT表來記錄中間改變的數(shù)據(jù))，但要使用兩倍于傳統(tǒng)方法的空間.表會變成行級共享鎖,在創(chuàng)建索引或者ALTER完成后,對臨時日志表與基表進(jìn)行MERGE 注意并行處理,，DDL,，位圖索引不能使用ONLINE。
78. colb,，字符串大對象,，存的是長字符串?dāng)?shù)據(jù) blob,，二進(jìn)制大對象，存的是二進(jìn)制型,，比如圖像,、音頻數(shù)據(jù) 79. SQLPLUS 默認(rèn)不是自動提交的. 自動提交命令： SQL>set autocommit on 退出SQLPLUS 時會自動提交 指定DDL，如CREATE , ALTER, DROP ,，會自動提交 執(zhí)行DCL,，如GRANT,REVOKE，會自動提交 80. 臨時表空間不能脫機(jī),。system,和正在使用（有活動session或transaction）的也不能,。 81. 查詢正在執(zhí)行的sql select OSUSER, PROGRAM, USERNAME, SCHEMANAME, B.Cpu_Time, STATUS, B.SQL_TEXT from V$SESSION A LEFT JOIN V$SQL B ON A.SQL_ADDRESS = B.ADDRESS AND A.SQL_HASH_VALUE = B.HASH_VALUE where b.SQL_TEXT is not null order by b.cpu_time desc 82. Oracle在to_char()函數(shù)在計(jì)算一年中第幾周是從該年的1月1日開始的。 83. 正在連接的用戶不能刪除,，確實(shí)要刪除的話,，如下 1、select sid,serial#,username from v$session where user='USERNAME'; 2,、alter system kill session 'sid,serial#'; 3,、drop user username cascade; 84. 在排除索引限制的條件下，如果索引還是沒有被引用,，可以檢查下參數(shù),。 optimizer_index_cost_adj為100,該參數(shù)影響優(yōu)化器選擇索引還是全表掃描的傾向,將其修改為35. 85. 在Oracle中，要獲得日期中的年份,，例如把sysdate中的年份取出來,，并不是一件難事。 常用的方法是：Select to_number(to_char(sysdate,''yyyy'')) from dual,， 而實(shí)際上,，oracle本身有更好的方法，那就是使用Extract函數(shù),， 使用方法是：Select Extract(year from sysdate) from dual,，這種方法省掉了類型轉(zhuǎn)換，看上去更加簡潔,。 相應(yīng)的,，要取得月份或日，可以用select extract (month from sysdate) from dual和select extract (day from sysdate) from dual,。 此方法獲得的結(jié)果,，是數(shù)值型的，大家可以設(shè)置一個方法測試一下,。 select EXTRACT(year FROM to_date('2009-11-10','yyyy-mm-dd')) year from dual; 86. 查詢數(shù)據(jù)庫默認(rèn)的表空間類型： SQL> select property_name,property_value from database_properties where property_name='DEFAULT_TBS_TYPE'; PROPERTY_NAME PROPERTY_VALUE ------------------ ------------------ DEFAULT_TBS_TYPE BIGFILE 87. 在10g中,，有一個特性，就是bigfile tablespace,，這種類型的表空間只能有一個數(shù)據(jù)文件,，且該數(shù)據(jù)文件允許有4G的數(shù)據(jù)快,，即如果db_block_size=8k的話，最大容量為4G*8K=32T,，當(dāng)然,，這個還要看操作系統(tǒng)的限制了。
修改數(shù)據(jù)庫默認(rèn)的表空間類型為smallfile,就可以為表空間創(chuàng)建多個數(shù)據(jù)文件了,。
SQL> alter database set default smallfile tablespace; Database altered. 也可以在創(chuàng)建表空間時,，指定表空間類型：create smallfile/bigfile tablespace ....
88. exp 失敗執(zhí)行的腳本： Catexp.sql ： 這個腳本是用于生成exp命令執(zhí)行時所需要的一些表和視圖，在執(zhí)行exp命令出現(xiàn)找不到什么什么表,，什么什么視圖時使用,。
Catmeta.sql ：這個腳本是在升級后執(zhí)行exp命令出現(xiàn)錯誤時運(yùn)行，說是因?yàn)樯壊怀晒?，運(yùn)行這個腳本可以重新創(chuàng)建系統(tǒng)表,。 89. 日期一般就用to_date(str,format)格式轉(zhuǎn)換， 像'yyyy-mm-dd'這樣的可以直接用date'xxxx'簡化 select date'2009-11-11' as d fromdual;
select * from t where t.day=date'2009-11-11'; 90. sqlplus命令save可以把sql語句保存到文件中,，可是默認(rèn)的存放路徑是$ORACLE_HOME/bin,，即sqlplus可執(zhí)行文件存放的位置，當(dāng)然有的人說在文件名前加絕對路徑即可：save d:\oracle\admin\oradb\emp.sql 可是這種寫法未免太麻煩,，特別是使用get命令,，也得使用絕對路徑，因此如果能夠修改save的默認(rèn)位置,，那么save/get就好寫的多了,。 91. 查詢某一對象的類型，比如查詢'v$datafile'是同義詞還是視圖,？ select * from all_objects where object_name=upper('v$datafile') 93. Index ENABLE和DISABLE適用于FUNCTION-BASED INDEX 如果普通索引的話,，你就用unusable 而不是disable ENABLE和DISABLE只針對函數(shù)索引。 ENABLE applies only to a function-based index that has been disabled because a user-defined function used by the index was dropped or replaced. This clause enables such an index if these conditions are true:
* The function is currently valid
* The signature of the current function matches the signature of the function when the index was created * The function is currently marked as DETERMINISTIC Restriction on Enabling Function-based Indexes You cannot specify any other clauses of ALTER INDEX in the same statement with ENABLE. DISABLE Clause
DISABLE applies only to a function-based index. This clause lets you disable the use of a function-based index. You might want to do so, for example, while working on the body of the function. Afterward you can either rebuild the index or specify another ALTER INDEX statement with the ENABLE keyword.
樓主試試：
alter index xx unusable; UNUSABLE Clause Specify UNUSABLE to mark the index or index partition(s) or index subpartition(s) UNUSABLE. An unusable index must be rebuilt, or dropped and re-created, before it can be used. While one partition is marked UNUSABLE, the other partitions of the index are still valid. You can execute statements that require the index if the statements do not access the unusable partition. You can also split or rename the unusable partition before rebuilding it.
94. 如何清除inactive的session 1.方法一 (1)UNIX的方法 A,。sql>select usename,sid,paddr,status from v$session where usename='USERNAME' AND STATUS='INACTIVE'; B,。sql>SELECT SPID FROM V$PROCESS WHERE ADDR=上一步查出的PADDR C。$KILL SPID （2）WINDOWSnt/2000的方法
c:\>orakill SID SPID 2 ,方法二
select A.SID,B.SPID,A.SERIAL#,a.lockwait,A.USERNAME,A.OSUSER,a.logon_time,a.last_call_et/3600 LAST_HOUR,A.STATUS, 'orakill '||sid||' '||spid HOST_COMMAND, 'alter system kill session '''||A.sid||','||A.SERIAL#||'''' SQL_COMMAND from v$session A,V$PROCESS B where A.PADDR=B.ADDR AND SID>6 95. sqlnet.ora 文件中配置 sqlnet.expire_time參數(shù),，Dead Connection Detection 在服務(wù)器端使用,。每當(dāng)一個客戶端的連接建立時，SQL*NET讀取此參數(shù),，以決定多長時間發(fā)送包給連接的客戶端,，偵測連接是否還有效，如果無效,，則通知操作系統(tǒng)釋放該會話持有的資源,。防止因網(wǎng)絡(luò)的異常中斷導(dǎo)致會話長期持有資源不釋放,。設(shè)置為0應(yīng)該是不啟用DCD。 如sqlnet.expire_time=10,，則表示10分鐘 sqlnet.expire_time的單位為分鐘. 96. EXP/IMP 可以使用參數(shù)文件,。如mypar.par,內(nèi)容就是你要指定的參數(shù),如 owner=scott file=mydump.dmp log=mydump.log direct=y 在用exp時用parfile參數(shù)指定這個文件就可以了
exp myname/mypass@mydb parfile=mypar.par 97. 分頁一般用到兩種辦法： 1，利用rownum 2,，分析函數(shù)row_number()over() 1. select from( select t.*,rownum rn from( select * from a order by col1)t where rn between 101 and 200) 2. select * from(select t.*,row_number()over(order by col1)rn from t) where rn between 101 and 200 98. row_number()和ROWNUM是看起來相似但概念完全不同的東西,， row_number()是一個分析函數(shù)（Analytic Function），它返回的是基于over()參數(shù)的行號,。 rownum是oracle特別提供的一個偽列,，它只作用于查詢的結(jié)果集，根據(jù)結(jié)果集輸出的先后次序給每個紀(jì)錄順次編號,。 row_number() 要比rownum 高很多. 對一大表測試時,， row_number() 用時6s，rownum 用時 17s. 99. listener 主要是偵聽從客戶端發(fā)來的對數(shù)據(jù)庫的連接請求,。 如果你在服務(wù)器端用sqlplus 進(jìn)行連接,，監(jiān)聽沒有啟動也是可以連上的，但是從遠(yuǎn)程來訪問數(shù)據(jù)庫,，或者用PL/SQL dev 或者TOAD等進(jìn)行連接,，就必須啟動監(jiān)聽。 100. 這個命令可以查看建表的SQL語句.. select dbms_metadata.get_ddl('TABLE','&tname') from dual; 101. 查詢視圖可以通過 select * from all_views 索引:all_indexes, 索引和列的關(guān)系 all_ind_columns,。table_name即索引所在的表 如果只想查詢當(dāng)前用戶下的,，將上面數(shù)據(jù)字典的all改成user 102. char 最大長度是2000. SQL> create table test (v2 char(2001)); create table test (c char(2001)) ERROR at line 1: ORA-00910: specified length too long for its datatype SQL> create table test1 (c char(2000)); Table created. 103. Oralce 快照是Oralce 7時候的叫法吧，8i之后改名物化視圖 104, 一個小觸發(fā)器 create table t_temp ( id varchar2(10) primary key, len1 number(6,0), len2 number(6,0), len number(7,0) ) 當(dāng)update某一行的len1或len2值后,，則修改該行l(wèi)en的值(len = len1 + len2),， 或者insert 一條新的記錄后，修改len = len1 + len2 哪位幫我用觸發(fā)器實(shí)現(xiàn)上面的功能 create trigger tri befor insert or update on t_temp for each row begin :NEW.len := :NEW.len1 + :NEW.len2; end; create or replace trigger tgtemp
before insert or update of len1,len2 on t_temp for each row begin :new.len:=:new.len1+:new.len2; end; oracle 不允許觸發(fā)器修改它正在觸發(fā)的表,，故用before 可以,，after不行.. 105. udump下的trc文件可以通過配置不讓產(chǎn)生,利用命令 alter system set sql_trace=false; 其他的不能修改，只能手動的啟動trace,，手動的關(guān)閉trace. 比如：
alter session set events 'immediate trace name library_cache|controlf|systemstate|processstate|file_hdrs|REDOHDR level 10'; alter session set events 'immediate trace name off'; alter session set events '10046 trace name context forever,level 12';
alter session set events '10046 trace name context off'; alter system set events '10046 trace name context forever,level 12';
alter system set events '10046 trace name context off'; 106. 重復(fù)數(shù)據(jù)只顯示一條： select min(id) id,b,c from tb group by b,c 107. 刪除重復(fù)數(shù)據(jù)：
delete from tb where rowid not in (select min(rowid) from tb group by b,c); 108. oracle 批量重建索引 create or replace procedure p_rebuild_all_index (tablespace_name in varchar2) as sqlt varchar(200); begin for idx in (select index_name, tablespace_name, status from user_indexes where tablespace_name=tablespace_name and status='VALID' and temporary = 'N') loop begin sqlt := 'alter index ' || idx.index_name || ' rebuild '; dbms_output.put_line(idx.index_name); dbms_output.put_line(sqlt); EXECUTE IMMEDIATE sqlt; --錯誤后循環(huán)繼續(xù)執(zhí)行,。 EXCEPTION WHEN OTHERS THEN dbms_output.put_line(SQLERRM); end; end loop; end; oracle 存儲過程批量重建索引。 測試方法 declare --表空間名稱 tablespace_name varchar2(100); begin tablespace_name:='dddd'; p_rebuild_all_index(tablespace_name); end; 109. oracle 會將SQL語句中 in 后面的東西生成一張內(nèi)存中的臨時表,。然后進(jìn)行查詢,。所以在相關(guān)字段上見索引比較重要。 110. 在Oracle中查看各個表,、表空間占用空間的大小 查看當(dāng)前用戶每個表占用空間的大?。?br> Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name 查看每個表空間占用空間的大小：
Select Tablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group By Tablespace_Name 111. 格式化2個時間相減 SELECT EXTRACT (DAY FROM interval)
|| '天' || EXTRACT (HOUR FROM interval) || '小時' || EXTRACT (MINUTE FROM interval) || '分鐘' || EXTRACT (SECOND FROM interval) || '秒' 間隔 FROM (SELECT NUMTODSINTERVAL (callbegin - callend, 'DAY') interval FROM tbilllog12 t WHERE callbegin = TO_DATE ('2009-12-1 0:00:58', 'YYYY-MM-DD HH24:MI:SS')) 間隔
----------------------------------------------- 0天0小時0分鐘-24秒 1 row selected. 112. 相關(guān)定義 ORACLE_SID：操作系統(tǒng)環(huán)境變量ORACLE_SID用于和操作系統(tǒng)交互,。也就是說,，在操作系統(tǒng)中要想得到實(shí)例名,，就必須使用ORACLE_SID，在操作系統(tǒng)級別唯一識別oracle instance.
LD_LIBRARY_PATH ：你的系統(tǒng)用到oracle共享庫存在于需要指定的路徑,。 ORACLE_TERM：是ORACLE在XWINDOW圖形界面安裝時要使用的變量,必須正確設(shè)置,，否則安裝程序無法在xwindow中啟動。 ORACLE_OWNER ：對該文件具有訪問特權(quán)的用戶,；通常是創(chuàng)建該文件的用戶,。 113. 修改系統(tǒng)時間格式： alter session set nls_date_format='YYYY-MM-DD' 修改默認(rèn)的時間格式：
1、windows下,，在注冊表中 HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE下增加一字符串：NLS_DATE_FORMAT,把其值設(shè)成：YYYY-MM-DD 2,、Unix下，在用戶的.profile文件中增加以下內(nèi)容： NLS_DATE_FORMAT=YYYY-MM-DD export NLS_DATE_FORMAT Nls_lang 是Linux 系統(tǒng)的環(huán)境變量; Nls_language 是數(shù)據(jù)庫的參數(shù),。 如果數(shù)據(jù)庫字符集沒有問題,，而查詢出來的卻是亂碼，可以檢查下系統(tǒng)的環(huán)境變量,。
export NLS_LANG="simplified chinese_china.zhs16gbk" 修改系統(tǒng)時間格式： SQL>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; 或者在系統(tǒng) export NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS 或者在pro_file里面 添加一個變量 nls_date_format,， 這樣就不用每次都需要設(shè)置時間了。 114. ORACLE9.2.0.8 不支持,，not in()中帶union的SQL. 115. recover database using backup controlfile和recover database using backup controlfile until cancel 前者是利用backup controlfile完全恢復(fù) 后者是利用backup controlfile不完全恢復(fù),。 using backup controlfile 告訴Oracle不要使用control file中的scn
using backup controlfile until cancel 用于redo log file丟失時使用，如redo log sequence#1,2,3,4,5,6,，丟失4,，5，會恢復(fù)到3,。 兩個連用告訴Oracle恢復(fù)的時候恢復(fù)到最后一個可用的redo log file,，不管控制文件中的scn是多少
116. 一般來說 ORACLE實(shí)例內(nèi)存=物理內(nèi)存*80% 對于OLTP系統(tǒng):
PGA=實(shí)例內(nèi)存*20% SGA=實(shí)例內(nèi)存*80% 對于OLAP系統(tǒng):
PGA=實(shí)例內(nèi)存*50% SGA=實(shí)例內(nèi)存*50% 混合型系統(tǒng)在二者之間
確定內(nèi)存容量后，
對于PGA：使用WORKAREA_SIZE_POLICY設(shè)置為AUT,，表示PGA自動管理 用PGA_AGREGGATE_TARGET參數(shù)分配PGA 用SGA_TARGET參數(shù)分配SGA目標(biāo)值
用SGA_MAX_SIZE分配SGA最大值 本文來自CSDN博客,，轉(zhuǎn)載請標(biāo)明出處：http://blog.csdn.net/tianlesoftware/archive/2010/05/25/5622268.aspx |
|
|
