ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [转]ORACLE常用傻瓜问题1000问-02

[转]ORACLE常用傻瓜问题1000问-02

原创 Linux操作系统 作者:31597359 时间:2019-07-21 11:36:02 0 删除 编辑
ORACLE常用傻瓜问题1000问

日期的各部分的常用的的写法

119. 取时间点的年份的写法:

Select TO_CHAR(SYSDATE'YYYY') FROM DUAL

120. 取时间点的月份的写法:

Select TO_CHAR(SYSDATE'MM') FROM DUAL

121. 取时间点的日的写法:

Select TO_CHAR(SYSDATE'DD') FROM DUAL

122. 取时间点的时的写法:

Select TO_CHAR(SYSDATE'HH24') FROM DUAL

123. 取时间点的分的写法:

Select TO_CHAR(SYSDATE'MI') FROM DUAL

124. 取时间点的秒的写法:

Select TO_CHAR(SYSDATE'SS') FROM DUAL

125. 取时间点的日期的写法:

Select TRUNC(SYSDATE) FROM DUAL

126. 取时间点的时间的写法:

Select TO_CHAR(SYSDATE'HH24MISS') FROM DUAL

127. 日期,时间形态变为字符形态:

Select TO_CHAR(SYSDATE) FROM DUAL

128. 将字符串转换成日期或时间形态:

Select TO_DATE('2003/08/01') FROM DUAL

129. 返回参数的星期几的写法:

Select TO_CHAR(SYSDATE'D') FROM DUAL

130. 返回参数一年中的第几天的写法:

Select TO_CHAR(SYSDATE'DDD') FROM DUAL

131. 返回午夜和参数中指定的时间值之间的秒数的写法:

Select TO_CHAR(SYSDATE'SSSSS') FROM DUAL

132. 返回参数中一年的第几周的写法:

Select TO_CHAR(SYSDATE'WW') FROM DUAL

虚拟字段

133. CURRVAL nextval

为表创建序列

Create SEQUENCE EMPSEQ ...

Select empseq.currval FROM DUAL

自动插入序列的数值

Insert INTO emp

VALUES (empseq.nextval, 'LEWIS', 'CLERK',

7902, SYSDATE, 1200, NULL, 20)

134. ROWNUM

按设定排序的行的序号

Select * FROM emp Where ROWNUM < 10

135. ROWID

返回行的物理地址

Select ROWID, ename FROM emp Where deptno = 20

136. N秒转换为时分秒格式?

set serverout on

declare

N number = 1000000

ret varchar2(100)

begin

ret = trunc(n/3600) || '小时' || to_char(to_date(mod(n,3600),'sssss'),'fmmi" "ss""')

dbms_output.put_line(ret)

end

137. 如何查询做比较大的排序的进程?

Select b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#, a.username, a.osuser,

a.status

FROM v$session a,v$sort_usage b

Where a.saddr = b.session_addr

orDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks

138. 如何查询做比较大的排序的进程的SQL语句?

select /*+ orDERED */ sql_text from v$sqltext a

where a.hash_value = (

select sql_hash_value from v$session b

where b.sid = &sid and b.serial# = &serial)

order by piece asc

139. 如何查找重复记录?

Select * FROM TABLE_NAME

Where ROWID!=(Select MAX(ROWID) FROM TABLE_NAME D

Where TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2)

140. 如何删除重复记录?

Delete FROM TABLE_NAME

Where ROWID!=(Select MAX(ROWID) FROM TABLE_NAME D

Where TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2)

141. 如何快速编译所有视图?

SQL >SPOOL VIEW1.SQL

SQL >Select ‘Alter VIEW ‘||TNAME||’

COMPILE;’ FROM TAB

SQL >SPOOL OFF

然后执行VIEW1.SQL即可。

SQL >@VIEW1.SQL

142. orA-01555 SNAPSHOT TOO OLD的解决办法

增加MINEXTENTS的值,增加区的大小,设置一个高的OPTIMAL值。

143. 事务要求的回滚段空间不够,表现为表空间用满(ORA-01560错误),回滚段扩展到达参数 MAXEXTENTS

值(ORA-01628)的解决办法。

向回滚段表空间添加文件或使已有的文件变大;增加MAXEXTENTS的值。

144. 如何加密ORACLE的存储过程?

下列存储过程内容放在AA.SQL文件中

create or replace procedure testCCB(i in number) as

begin

dbms_output.put_line('输入参数是'||to_char(i))

end

SQL>wrap iname=a.sql

PL/SQL Wrapper Release 8.1.7.0.0 - Production on Tue Nov 27 222648 2001

Copyright (c) oracle Corporation 1993, 2000. All Rights Reserved

Processing AA.sql to AA.plb

运行AA.plb

SQL> @AA.plb

145. 如何监控事例的等待?

select event,sum(decode(wait_Time,0,0,1)) "Prev",

sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot"

from v$session_Wait

group by event order by 4

146. 如何回滚段的争用情况?

select name, waits, gets, waits/gets "Ratio"

from v$rollstat C, v$rollname D

where C.usn = D.usn

147. 如何监控表空间的 I/O 比例?

select B.tablespace_name name, B.file_name "file", A.phyrds pyr, A.phyblkrd pbr, A.phywrts pyw,

A.phyblkwrt pbw

from v$filestat A, dba_data_files B

where A.file# = B.file_id

order by B.tablespace_name

148. 如何监控文件系统的 I/O 比例?

select substr(C.file#,1,2) "#", substr(C.name,1,30) "Name", C.status, C.bytes, D.phyrds,

D.phywrts

from v$datafile C, v$filestat D

where C.file# = D.file#

149. 如何在某个用户下找所有的索引?

select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name

from user_ind_columns, user_indexes

where user_ind_columns.index_name = user_indexes.index_name

and user_ind_columns.table_name = user_indexes.table_name

order by user_indexes.table_type, user_indexes.table_name,

user_indexes.index_name, column_position

150. 如何监控 SGA 的命中率?

select a.value + b.value "logical_reads", c.value "phys_reads",

round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"

from v$sysstat a, v$sysstat b, v$sysstat c

where a.statistic# = 38 and b.statistic# = 39

and c.statistic# = 40

151. 如何监控 SGA 中字典缓冲区的命中率?

select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio",

(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"

from v$rowcache

where gets+getmisses <>0

group by parameter, gets, getmisses

152. 如何监控 SGA 中共享缓存区的命中率,应该小于1%

select sum(pins) "Total Pins", sum(reloads) "Total Reloads",

sum(reloads)/sum(pins) *100 libcache

from v$librarycache

select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent"

from v$librarycache

153. 如何显示所有数据库对象的类别和大小?

select 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_required

from dba_object_size

group by type order by 2

154. 监控 SGA 中重做日志缓存区的命中率,应该小于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) ratio2

FROM v$latch Where name IN ('redo allocation', 'redo copy')

155. 监控内存和硬盘的排序比率,最好使它小于 .10,增加 sort_area_size

Select name, value FROM v$sysstat Where name IN ('sorts (memory)', 'sorts(disk)')

156. 如何监控当前数据库谁在运行什么SQL语句?

Select osuser, username, sql_text from v$session a, v$sqltext b

where a.sql_address =b.address order by address, piece

157. 如何监控字典缓冲区?

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 MISSES"

FROM V$ROWCACHE

158. 监控 MTS

select busy/(busy+idle) "shared servers busy" from v$dispatcher

此值大于0.5时,参数需加大

select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where type='dispatcher'

select count(*) from v$dispatcher

select servers_highwater from v$mts

servers_highwater接近mts_max_servers时,参数需加大

159. 如何知道当前用户的ID?

SQL>SHOW USER

SQL>select user from dual

160. 如何查看碎片程度高的表?

Select segment_name table_name , COUNT(*) extents

FROM dba_segments Where owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name

HAVING COUNT(*) = (Select MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name)

162. 如何知道表在表空间中的存储情况?

select segment_name,sum(bytes),count(*) ext_quan from dba_extents where

tablespace_name='&tablespace_name' and segment_type='TABLE' group by tablespace_name,segment_name

163. 如何知道索引在表空间中的存储情况?

select segment_name,count(*) from dba_extents where segment_type='INDEX' and owner='&owner'

group by segment_name

164、如何知道使用CPU多的用户session?

11cpu used by this session

select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value

from v$session a,v$process b,v$sesstat c

where c.statistic#=11 and c.sid=a.sid and a.paddr=b.addr order by value desc

165. 如何知道监听器日志文件?

8I为例

$ORACLE_HOME/NETWORK/LOG/LISTENER.LOG

166. 如何知道监听器参数文件?

8I为例

$ORACLE_HOME/NETWORK/ADMIN/LISTENER.ORA

167. 如何知道TNS 连接文件?

8I为例

$ORACLE_HOME/NETWORK/ADMIN/TNSNAMES.ORA

168. 如何知道Sql*Net 环境文件?

8I为例

$ORACLE_HOME/NETWORK/ADMIN/SQLNET.ORA

169. 如何知道警告日志文件?

8I为例

$ORACLE_HOME/ADMIN/SID/BDUMP/SIDALRT.LOG

170. 如何知道基本结构?

8I为例

$ORACLE_HOME/RDBMS/ADMIN/STANDARD.SQL

171. 如何知道建立数据字典视图?

8I为例

$ORACLE_HOME/RDBMS/ADMIN/CATALOG.SQL

172. 如何知道建立审计用数据字典视图?

8I为例

$ORACLE_HOME/RDBMS/ADMIN/CATAUDIT.SQL

173. 如何知道建立快照用数据字典视图?

8I为例

$ORACLE_HOME/RDBMS/ADMIN/CATSNAP.SQL

SQL语句的优化方法

174. /*+ALL_ROWS*/

表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化。

例如:

Select /*+ALL+_ROWS*/ EMP_NOEMP_NAMDAT_IN FROM BSEMPMS Where EMP_NO='CCBZZP'

175. /*+FIRST_ROWS*/

表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化。

例如:

Select /*+FIRST_ROWS*/ EMP_NOEMP_NAMDAT_IN FROM BSEMPMS Where EMP_NO='CCBZZP'

176. /*+CHOOSE*/

表明如果数据字典中有访问表的统计信息,将基于开销的优化方法,并获得最佳的吞吐量;

表明如果数据字典中没有访问表的统计信息,将基于规则开销的优化方法;

例如:

Select /*+CHOOSE*/ EMP_NOEMP_NAMDAT_IN FROM BSEMPMS Where EMP_NO='CCBZZP'

177. /*+RULE*/

表明对语句块选择基于规则的优化方法。

例如:

Select /*+ RULE */ EMP_NOEMP_NAMDAT_IN FROM BSEMPMS Where EMP_NO='CCBZZP'

178. /*+FULL(TABLE)*/

表明对表选择全局扫描的方法。

例如:

Select /*+FULL(A)*/ EMP_NOEMP_NAM FROM BSEMPMS A Where EMP_NO='CCBZZP'

179. /*+ROWID(TABLE)*/

提示明确表明对指定表根据ROWID进行访问。

例如:

Select /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS Where ROWID>='AAAAAAAAAAAAAA'

AND EMP_NO='CCBZZP'

180. /*+CLUSTER(TABLE)*/

提示明确表明对指定表选择簇扫描的访问方法,它只对簇对象有效。

例如:

Select /*+CLUSTER */ BSEMPMS.EMP_NODPT_NO FROM BSEMPMSBSDPTMS

Where DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO

181. /*+INDEX(TABLE INDEX_NAME)*/

表明对表选择索引的扫描方法。

例如:

Select /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM

BSEMPMS Where SEX='M'

182. /*+INDEX_ASC(TABLE INDEX_NAME)*/

表明对表选择索引升序的扫描方法。

例如:

Select /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS Where DPT_NO='CCBZZP'

183. /*+INDEX_COMBINE*/

为指定表选择位图访问路经,如果INDEX_COMBINE中没有提供作为参数的索引,将选择出位图索引的

布尔组合方式。

例如:

Select /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * FROM BSEMPMS

Where SAL<5000000 AND HIREDATE

184. /*+INDEX_JOIN(TABLE INDEX_NAME)*/

提示明确命令优化器使用索引作为访问路径。

例如:

Select /*+INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI)*/ SALHIREDATE

FROM BSEMPMS Where SAL<60000

185. /*+INDEX_DESC(TABLE INDEX_NAME)*/

表明对表选择索引降序的扫描方法。

例如:

Select /*+INDEX_DESC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS Where DPT_NO='CCBZZP'

186. /*+INDEX_FFS(TABLE INDEX_NAME)*/

对指定的表执行快速全索引扫描,而不是全表扫描的办法。

例如:

Select /*+INDEX_FFS(BSEMPMS IN_EMPNAM)*/ * FROM BSEMPMS Where DPT_NO='TEC305'

187. /*+ADD_EQUAL TABLE INDEX_NAM1INDEX_NAM2...*/

提示明确进行执行规划的选择,将几个单列索引的扫描合起来。

例如:

Select /*+INDEX_FFS(BSEMPMS IN_DPTNOIN_EMPNOIN_SEX)*/ * FROM BSEMPMS Where EMP_NO='CCBZZP'

AND DPT_NO='TDC306'

188. /*+USE_CONCAT*/

对查询中的Where后面的OR条件进行转换为UNION ALL的组合查询。

例如:

Select /*+USE_CONCAT*/ * FROM BSEMPMS Where DPT_NO='TDC506' AND SEX='M'

189. /*+NO_EXPAND*/

对于Where后面的OR 或者IN-LIST的查询语句,NO_EXPAND将阻止其基于优化器对其进行扩展。

例如:

Select /*+NO_EXPAND*/ * FROM BSEMPMS Where DPT_NO='TDC506' AND SEX='M'

190. /*+NOWRITE*/

禁止对查询块的查询重写操作。

191. /*+REWRITE*/

可以将视图作为参数。

192. /*+MERGE(TABLE)*/

能够对视图的各个查询进行相应的合并。

例如:

Select /*+MERGE(V) */ A.EMP_NOA.EMP_NAMB.DPT_NO FROM BSEMPMS A (SELET DPT_NOAVG(SAL) AS

AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V Where A.DPT_NO=V.DPT_NO AND A.SAL>V.AVG_SAL

193. /*+NO_MERGE(TABLE)*/

对于有可合并的视图不再合并。

例如:

Select /*+NO_MERGE(V) */ A.EMP_NOA.EMP_NAMB.DPT_NO FROM BSEMPMS A (SELET DPT_NOAVG(SAL) AS

AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V Where A.DPT_NO=V.DPT_NO AND A.SAL>V.AVG_SAL

194. /*+ORDERED*/

根据表出现在FROM中的顺序,ORDERED使ORACLE依此顺序对其连接。

例如:

Select /*+ORDERED*/ A.COL1B.COL2C.COL3 FROM TABLE1 ATABLE2 BTABLE3 C Where A.COL1=B.COL1

AND B.COL1=C.COL1

195. /*+USE_NL(TABLE)*/

将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表。

例如:

Select /*+ORDERED USE_NL(BSEMPMS)*/ BSDPTMS.DPT_NOBSEMPMS.EMP_NOBSEMPMS.EMP_NAM FROM BSEMPMS

BSDPTMS Where BSEMPMS.DPT_NO=BSDPTMS.DPT_NO

196. /*+USE_MERGE(TABLE)*/

将指定的表与其它行源通过合并排序连接方式连接起来。

例如:

Select /*+USE_MERGE(BSEMPMSBSDPTMS)*/ * FROM BSEMPMSBSDPTMS Where

BSEMPMS.DPT_NO=BSDPTMS.DPT_NO

197. /*+USE_HASH(TABLE)*/

将指定的表与其它行源通过哈希连接方式连接起来。

例如:

Select /*+USE_HASH(BSEMPMSBSDPTMS)*/ * FROM BSEMPMSBSDPTMS Where

BSEMPMS.DPT_NO=BSDPTMS.DPT_NO

198. /*+DRIVING_SITE(TABLE)*/

强制与ORACLE所选择的位置不同的表进行查询执行。

例如:

Select /*+DRIVING_SITE(DEPT)*/ * FROM BSEMPMSDEPT@BSDPTMS Where BSEMPMS.DPT_NO=DEPT.DPT_NO

199. /*+LEADING(TABLE)*/

将指定的表作为连接次序中的首表。

200. /*+CACHE(TABLE)*/

当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端

例如:

Select /*+FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8000572/viewspace-62306/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2006-10-07

  • 博文量
    120
  • 访问量
    77807