【文章內(nèi)容簡介】 T TO_CHAR(SYSDATE，39。DD39。) FROM DUAL；122. 取時(shí)間點(diǎn)的時(shí)的寫法：SELECT TO_CHAR(SYSDATE，39。HH2439。) FROM DUAL；123. 取時(shí)間點(diǎn)的分的寫法：SELECT TO_CHAR(SYSDATE，39。MI39。) FROM DUAL；124. 取時(shí)間點(diǎn)的秒的寫法：SELECT TO_CHAR(SYSDATE，39。SS39。) FROM DUAL；125. 取時(shí)間點(diǎn)的日期的寫法：SELECT TRUNC(SYSDATE) FROM DUAL；126. 取時(shí)間點(diǎn)的時(shí)間的寫法：SELECT TO_CHAR(SYSDATE，39。HH24：MI：SS39。) FROM DUAL；127. 日期，時(shí)間形態(tài)變?yōu)樽址螒B(tài)：SELECT TO_CHAR(SYSDATE) FROM DUAL；128. 將字符串轉(zhuǎn)換成日期或時(shí)間形態(tài)：SELECT TO_DATE(39。2003/08/0139。) FROM DUAL；129. 返回參數(shù)的星期幾的寫法：SELECT TO_CHAR(SYSDATE，39。D39。) FROM DUAL；130. 返回參數(shù)一年中的第幾天的寫法：SELECT TO_CHAR(SYSDATE，39。DDD39。) FROM DUAL；131. 返回午夜和參數(shù)中指定的時(shí)間值之間的秒數(shù)的寫法：SELECT TO_CHAR(SYSDATE，39。SSSSS39。) FROM DUAL；132. 返回參數(shù)中一年的第幾周的寫法：SELECT TO_CHAR(SYSDATE，39。WW39。) FROM DUAL；虛擬字段133. CURRVAL 和 nextval為表創(chuàng)建序列CREATE SEQUENCE EMPSEQ ... ；SELECT FROM DUAL ；自動(dòng)插入序列的數(shù)值INSERT INTO empVALUES (, 39。LEWIS39。, 39。CLERK39。,7902, SYSDATE, 1200, NULL, 20) ；134. ROWNUM按設(shè)定排序的行的序號SELECT * FROM emp WHERE ROWNUM 10 ；135. ROWID返回行的物理地址SELECT ROWID, ename FROM emp WHERE deptno = 20 ；136. 將N秒轉(zhuǎn)換為時(shí)分秒格式？set serverout ondeclareN number ：= 1000000；ret varchar2(100)；beginret ：= trunc(n/3600) || 39。小時(shí)39。 || to_char(to_date(mod(n,3600),39。sssss39。),39。fmmi分 ss秒39。) ；(ret)；end；137. 如何查詢做比較大的排序的進(jìn)程？SELECT , , , , , , , ,FROM v$session a,v$sort_usage bWHERE = ORDER BY , , , ；138. 如何查詢做比較大的排序的進(jìn)程的SQL語句？select /*+ ORDERED */ sql_text from v$sqltext awhere = (select sql_hash_value from v$session bwhere = amp。sid and = amp。serial)order by piece asc ；139. 如何查找重復(fù)記錄？SELECT * FROM TABLE_NAMEWHERE ROWID!=(SELECT MAX(ROWID) FROM TABLE_NAME DWHERE = AND =)；140. 如何刪除重復(fù)記錄？DELETE FROM TABLE_NAMEWHERE ROWID!=(SELECT MAX(ROWID) FROM TABLE_NAME DWHERE = AND =)；141. 如何快速編譯所有視圖？SQL SPOOL SQL SELECT ‘ALTER VIEW ‘||TNAME||’COMPILE；’ FROM TAB；SQL SPOOL OFF。SQL @；142. ORA01555 SNAPSHOT TOO OLD的解決辦法增加MINEXTENTS的值，增加區(qū)的大小，設(shè)置一個(gè)高的OPTIMAL值。143. 事務(wù)要求的回滾段空間不夠，表現(xiàn)為表空間用滿（ORA01560錯(cuò)誤），回滾段擴(kuò)展到達(dá)參數(shù) MAXEXTENTS的值（ORA01628）的解決辦法。向回滾段表空間添加文件或使已有的文件變大；增加MAXEXTENTS的值。144. 如何加密ORACLE的存儲過程？create or replace procedure testCCB(i in number) asbegin(39。輸入?yún)?shù)是39。||to_char(i))；end；SQLwrap iname=；PL/SQL Wrapper： Release Production on Tue Nov 27 22：26：48 2001Copyright (c) Oracle Corporation 1993, 2000. All Rights Reserved。Processing to SQL @ ；145. 如何監(jiān)控事例的等待？select event,sum(decode(wait_Time,0,0,1)) Prev,sum(decode(wait_Time,0,1,0)) Curr,count(*) Totfrom v$session_Waitgroup by event order by 4；146. 如何回滾段的爭用情況？select name, waits, gets, waits/gets Ratiofrom v$rollstat C, v$rollname Dwhere = ；147. 如何監(jiān)控表空間的 I/O 比例？select name, file, pyr, pbr, pyw, pbwfrom v$filestat A, dba_data_files Bwhere = order by ；148. 如何監(jiān)控文件系統(tǒng)的 I/O 比例？select substr(,1,2) , substr(,1,30) Name, , , ,from v$datafile C, v$filestat Dwhere = ；149. 如何在某個(gè)用戶下找所有的索引？select , ,uniqueness, column_namefrom user_ind_columns, user_indexeswhere = and = order by , , column_position；150. 如何監(jiān)控 SGA 的命中率？select + logical_reads, phys_reads,round(100 * ((+)) / (+)) BUFFER HIT RATIOfrom v$sysstat a, v$sysstat b, v$sysstat cwhere = 38 and = 39and = 40；151. 如何監(jiān)控 SGA 中字典緩沖區(qū)的命中率？select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 miss ratio,(1(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 Hit ratiofrom v$rowcachewhere gets+getmisses 0group by parameter, gets, getmisses；152. 如何監(jiān)控 SGA 中共享緩存區(qū)的命中率，應(yīng)該小于1% ？select sum(pins) Total Pins, sum(reloads) Total Reloads,sum(reloads)/sum(pins) *100 libcachefrom v$librarycache；select sum(pinhitsreloads)/sum(pins) hit radio,sum(reloads)/sum(pins) reload percentfrom v$librarycache；153. 如何顯示所有數(shù)據(jù)庫對象的類別和大?。縮elect count(name) num_instances ,type ,sum(source_size) source_size ,sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size) error_size,sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_requiredfrom dba_object_sizegroup by type order by 2；154. 監(jiān)控 SGA 中重做日志緩存區(qū)的命中率，應(yīng)該小于1%SELECT name, gets, misses, immediate_gets, immediate_misses,Decode(gets,0,0,misses/gets*100) ratio1,Decode(immediate_gets+immediate_misses,0,0,immediate_misses/(immediate_gets+immediate_misses)*100) ratio2FROM v$latch WHERE name IN (39。redo allocation39。, 39。redo copy39。)；155. 監(jiān)控內(nèi)存和硬盤的排序比率，最好使它小于 .10，增加 sort_area_sizeSELECT name, value FROM v$sysstat WHERE name IN (39。sorts (memory)39。, 39。sorts(disk)39。)；156. 如何監(jiān)控當(dāng)前數(shù)據(jù)庫誰在運(yùn)行什么SQL語句？SELECT osuser, username, sql_text from v$session a, v$sqltext bwhere = order by address, piece；157. 如何監(jiān)控字典緩沖區(qū)？SELECT (SUM(PINS RELOADS)) / SUM(PINS) LIB CACHE FROM V$LIBRARYCACHE；SELECT (SUM(GETS GETMISSES USAGE FIXED)) / SUM(GETS) ROW CACHE FROM V$ROWCACHE；SELECT SUM(PINS) EXECUTIONS, SUM(RELOADS) CACHE MISSES WHILE EXECUTING FROM V$LIBRARYCACHE；后者除以前者,此比率小于1%,接近0%為好。SELECT SUM(GETS) DICTIONARY GETS,SUM(GETMISSES) DICTIONARY CACHE GET MISSESFROM V$ROWCACHE158. 監(jiān)控 MTSselect busy/(busy+idle) shared servers busy from v$dispatcher；，參數(shù)需加大select sum(wait)/sum(totalq) dispatcher waits from v$queue where type=39。dispatcher39。；select count(*) from v$dispatcher；select servers_highwater from v$mts；servers_highwater接近mts_max_servers時(shí)，參數(shù)需加大159. 如何知道當(dāng)前用戶的ID號?SQLSHOW USER；或 SQLselect user from dual；160. 如何查看碎片程度高的表?SELECT segment_name table_name , COUNT(*) extentsFROM dba_segments WHERE owner NOT IN (39。SYS39。, 39。SYSTEM39。) GROUP BY segment_nameHAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name)；162. 如何知道表在表空間中的存儲情況?select segment_name,sum(bytes),count(*) ext_quan from dba_extents wheretablespace_name=39。amp。tablespace_name39。 and segment_type=39。TABLE39。 group by tablespace_name,segment_name；163. 如何知道索引在表空間中的存儲情況?select segment_name,count(*) from dba_extents where segment_type=39。INDEX39。 and owner=39。amp。owner39。group by segment_name；16如何知道使用CPU多的用戶session?11是cpu used by this sessionselect ,spid,status,substr(,1,40) p