`
ijavagos
  • 浏览: 1183136 次
  • 性别: Icon_minigender_2
  • 来自: 北京
文章分类
社区版块
存档分类
最新评论

Oracle 小知识 总结(一)

 
阅读更多

Oracle是一个庞大的系统,里面的知识点很多,在学习的时候,看到一些知识点,就贴了下来,尽不知中贴了这么多,就先做个小结吧。里面有的知识点已经单独拿出来写成单篇的blog(http://blog.csdn.net/tianlesoftware)了。有的还没有进行整理。好记性不如烂笔头,不过我用的不是笔。^_^...

这里面大部分内容都是我泡CSDN论坛的时候整理的,现在要忙其他的事,就很少去了。泡论坛的那段时间,是我进步最快的时间,所以如果想在Oracle上有所发展的朋友,建议也可以去论坛泡泡。看到自己会的知识点,就帮别人解答,不会的就自己研究学习,一段时间下来,会有很大的进步。而且还能认识很多朋友。实在是一举两得的事。

1.每天的8002300每隔5分钟执行一个sql语句的JOB

--建立一个存储过程

CREATEORREPLACEPROCEDUREp_jobtestIS

v_hhVARCHAR2(2);

BEGIN

v_hh:=to_char(SYSDATE,'hh24');

IFv_hh>='08'ANDv_hh<='22'THEN

--你的sql语句

NULL;

ENDIF;

END;

/

--提交一个JOB

DECLARE

v_jobnoNUMBER;

BEGIN

dbms_job.submit(v_jobno,

'p_jobtest;',

trunc(SYSDATE,'mi')+1/1440,

'trunc(SYSDATE,''mi'')+5/1440');

END;

/

2.RMAN中的list命令显示的信息是从控制文件里获取的,如果使用rm等命令手工的删除备份文件,这个动作不会同步到控制文件,造成不一致,这种不一致会导致使用rman时报错。可以使用delete删除这些过期的记录,在用就不会报错了。

RMAN>crosscheckcopy;

RMAN>listcopy;

RMAN>deleteexpiredcopy;

3.触发LGWR进程的条件有:

1.用户提交

2.1/3重做日志缓冲区未被写入磁盘

3.有大于1M的重做日志缓冲区未被写入磁盘

4.3秒超时

5.DBWR需要写入的数据的SCN大于LGWR记录的SCNDBWR触发LGWR写入。

4.触发DBWR进程的条件有:

1.DBWR超时,大约3

2.系统中没有多余的空缓冲区来存放数据

3.CKPT进程触发DBWR

5.每隔3秒钟ckpt会去更新控制文件和数据文件,记录checkpoint执行的情况。

当发生checkpoint时,会把SCN写到四个地方去。

三个地方于controlfile内,一个在datafileheader

6.触发CheckPoint(检查点)条件有很多,比如:

1.通过正常事务处理或者立即选项关闭例程时(shutdownimmediate或者Shutdownnormal),

2.当通过设置初始化参数:

LOG_CHECKPOINT_INTERVAL,

LOG_CHECKPOINT_TIMEOUT,

FAST_START_IO_TARGET强制时;

3.当数据库管理员手动请求时:

ALtersystemcheckpoint;

altertablespace...offline;

4.每次日志切换时;

altersystemswitchlogfile

注意:

1.altersystemswitchlogfile也将触发完全检查点的发生。

2.alterdatabasedatafile...offline不会触发检查点进程。

7.RECOVERDATABASEUNTILCANCELRECOVERDATABASEUNTILCANCELUSINGBACKUPCONTROLFILE;区别

1)RECOVERDATABASEUNTILCANCEL==>OPENDATABASERESETLOG

==>DATAFILEHEADERSCN一定会小于CONTROLFILEDATAFILESCN

如果你有进行RESTOREDATAFILE,则该RESTOREDATAFILEHEADERSCN一定会小于目前CONTROLFILEDATAFILESCN,此时会无法开启数据库,必须进行mediarecovery。重做archivelog直到该datafileheaderSCN=currentscn

8.建表前判断表是否存在的存储过程。

Oracle中没有droptable...ifexists语法。所以我们可以在创建表之前用如下存储过程来判断。

createorreplaceprocedureproc_dropifexists(

p_tableinvarchar2

)is

v_countnumber(10);

begin

selectcount(*)

intov_count

fromuser_objects

whereobject_name=upper(p_table);

ifv_count>0then

executeimmediate'droptable'||p_table||'purge';

endif;

end;

9.表属性中pctused,pctfree作用

表示数据块什么时候移入和移出freelist

pctused:如果数据块的使用率小于pctused的值,则该数据块重新加入到fresslist中。

pctfree:如果数据块的使用率高于pctfree的值,则该数据块从freelist中移出。

10.oracle表空间大小没有限制,根存储空间而定。

oracle9i或以下,单个数据文件最大32G(对于8K的数据块),整个数据库最多有64K个数据文件.单个表空间的数据文件数量没有具体的限制,也应该是在64K以下.oracle10g以上,引入了bigfiletablespace,bigfiletablespace只有一个数据文件,最大为4G*8k=32Tdatabasefilesize:Operatingsystemdependent.Limitedbymaximumoperatingsystemfilesize;typically222(222次方)or4Mblocks

11.Oracle利用现有的表创建一张新表,只要表结构相同
createtabledavidasselect*fromall_userswhere1<>1;

12.循环插入数据
declareiinteger;
begin
foriin1..100000loop
insertintotestvalues(i);
endloop;
commit;
end;

13.开发人员通常习惯赋予所有用户DBA权限,查看权限
Select*FromUser_Role_Privs
Select*FromUser_Sys_Privs

14.看数据文件大小,单位是M
selectround(bytes/(1024*1024),0)total_spacefromdba_data_files
selectsum(bytes/(1024*1024))total_spacefromdba_data_files

15控制文件大小
selectsum(block_size*file_size_blks)/1024/1024fromv$controlfile

16.建立表空间
CREATETABLESPACEdata01
DATAFILE'/oracle/oradata/db/DATA01.dbf'SIZE500M
UNIFORMSIZE128k;#指定区尺寸为128k,如不指定,区尺寸默认为64k删除表空间
DROPTABLESPACEdata01INCLUDINGCONTENTSANDDATAFILES;
修改表空间大小
alterdatabasedatafile'/path/NADDate05.dbf'resize100M

查看表空间:

selecttablespace_name,file_name,sum(bytes)/1024/1024table_sizefromdba_data_filesgroupbytablespace_name,file_name;

17.有没有被lock,可以通过这2dynamicview来确定:
v$locked_object,V$session

可以把该session杀掉。
selectsid,serial#fromv$sessionwhereusername='XXXX'
把得到的sidserial#号替换到下面的语句中:
altersystemkillsession'SID,SERIAL#'

18.PL/SQLoracle查询前10条信息
SELECT*FROMtableWHEREROWNUM<11
select*from(select*fromtableorderbydesc)whererownum<=5
==selecttop5*fromtable;

19.查看表上是否存在的索引
select*fromuser_indexeswheretable_name='yourtablename'
createindexIX_Tablename_columnontablename(column)

20.selectid,id2,round((id/id2)*100,2)||'%'percentfromtest;

21.查询表的行数
selectcount(*)fromtable_name;全表扫描,会自已找表有索引列并且该列为非空的(因为只有非空才能确保记录数是全的),INDEX_FFS.
selectcount(1)fromtable_name;不走索引,效率要高,但在表中有非空索引时也是走INDEX_FFS

22.function来查看当前sessiontrace文件的文件名
如下
createorreplacefunctiongettracenamereturnvarchar2is
v_resultvarchar2(200);
begin
SELECTd.VALUE
||'/'
||LOWER(RTRIM(i.INSTANCE,CHR(0)))
||'_ora_'
||p.spid
||'.trc'intov_result
FROM(SELECTp.spid
FROMv$mystatm,v$sessions,v$processp
WHEREm.statistic#=1ANDs.SID=m.SIDANDp.addr=s.paddr)p,
(SELECTt.INSTANCE
FROMv$threadt,v$parameterv
WHEREv.NAME='thread'
AND(v.VALUE=0ORt.thread#=TO_NUMBER(v.VALUE)))i,
(SELECTVALUE
FROMv$parameter
WHERENAME='user_dump_dest')d;

returnv_result;
endgettracename;

运行SQL>selectgettracename()fromdual;即可
GETTRACENAME()
-----------------------------------------------------------------------

F:/DEVELOPER/ORACLE/PRODUCT/10.2.0/ADMIN/ORCL/UDUMP/orcl_ora_3800.trc


23.selectcasewhen(a>b)thenaelsebendfromTableA;
selectgreatest(a,b)fromtb

24.TRUNCATETABLETable_name
使用truncate时,速度比delete,但是系统不做Log。清空表的数据,仅保留类别结构,被删除的数据不能ROLLBACK,这点是与DELETE的主要差别.不能Rollback也就是会删除log文件.SQLServer里面如果你用Truncate,自动增长的行又会从1开始

25.selectsubstrb('大小abc',1,6)fromdual;


26.表中的数据如下图所示
abc
1PP41982SO90029
2PP41982SO90029
只取出字段bc不重复的字段,

selectb,cfromtgroupbyb,chaving(count(b)<2)

27.查询锁的情况
1).insertintotestvalues(1);
2).selectuserenv('sid')fromdual;
3).select*fromv$lockwheresid=''
sidv$session中有这个列,可以结合v$session查询更多的信息

28.删除重复行:
SQL>DELETEFROMaWHEREROWIDIN(SELECTMAX(ROWID)FROMa);
已删除1行。
SQL>select*froma;
NAMEIDORDER_TITLE
--------------------------------------------------
中国科学技术大学1科学
中国科学技术大学1科学
SQL>

29.查看索引信息

--查看索引名称
SELECT*FROMUSER_INDEXES;
--查看索引列名
SELECT*FROMDBA_IND_COLUMNS;
SELECT*FROMUSER_IND_COLUMNS;
SELECT*FROMALL_IND_COLUMNS;

30.oracletrunc()函数用法


1.TRUNC(fordates)
TRUNC函数为指定元素而截去的日期值。
其具体的语法格式如下:
TRUNCdate[,fmt]
其中:
date一个日期值
fmt日期格式,该日期将由指定的元素格式所截去。忽略它则由最近的日期截去
下面是该函数的使用情况:
TRUNCTO_DATE(’24-Nov-199908:00pm’,’dd-mon-yyyyhh:miam’)
=’24-Nov-199912:00:00am’
TRUNCTO_DATE(’24-Nov-199908:37pm’,’dd-mon-yyyyhh:miam’,’hh’)=’24-Nov-199908:00:00am’

2.TRUNC(fornumber)
TRUNC函数返回处理后的数值,其工作机制与ROUND函数极为类似,只是该函数不对指定小数前或后的部分做相应舍入选择处理,而统统截去。
其具体的语法格式如下
TRUNCnumber[,decimals]
其中:
number待做截取处理的数值
decimals指明需保留小数点后面的位数。可选项,忽略它则截去所有的小数部分
下面是该函数的使用情况:
TRUNC89.9852=89.98
TRUNC89.985=89
TRUNC89.985-1=80
注意:第二个参数可以为负数,表示为小数点左边指定位数后面的部分截去,即均以0记。

31.local是局部有序,整体无序,global是有序的,所以local可能会比global慢,得看你的sql语句怎么写的,需求是什么样的
global索引->自己想怎么玩就怎么玩
local索引->表怎么玩它就怎么玩

32,查看磁盘物理读写情况:
SELECTNAME,phyrds,phywrts,readtim,writetimFROMv$filestata,v$datafilebWHEREa.FILE#=b.FILE#
ORDERBYreadtimDESC;

33.从表中筛选出所有能被5整除的value值数据

select*fromtablewheremod(datavalue,5)=0;

34.UnionUnionAll的区别
如果我们需要将两个select语句的结果作为一个整体显示出来,我们就需要用到union或者unionall关键字。union(或称为联合)的作用是将多个结果合并在一起显示出来。
unionunionall的区别是,union会自动压缩多个结果集合中的重复结果,而unionall则将所有的结果全部显示出来,不管是不是重复。
Union对两个结果集进行并集操作,不包括重复行,同时进行默认规则的排序;
UnionAll对两个结果集进行并集操作,包括重复行,不进行排序;

selectempno,enamefromemp
union
selectdeptno,dnamefromdept

我们没有必要在每一个select结果集中使用orderby子句来进行排序,我们可以在最后使用一条orderby来对整个结果进行排序。例如:
selectempno,enamefromemp
union
selectdeptno,dnamefromdept
orderbyename;

35.查看看到A用户下的所有数据量>100万的表的信息
select*fromuser_all_tablesa
wherea.num_rows>1000000

前提是a用户下所有表的统计信息都是最新的。
保险的办法是所有表都count一遍:
select'select'||''''||table_name||''','||'count(*)from'||table_namefromuser_all_tables;
把上面这段sql的执行结果拷贝出来执行即可

36.SQLPLUS里执行EXPLAINPLAN

SQL>EXPLAINPLANFOR你的sql语句;

SQL>EXPLAINPLANFORSELECT*FROMEMPWHEREEMPNO=7369;
然后
SELECTplan_table_output
FROMTABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
查看结果就是前面SQL语句的执行计划。

37.nvarchar2(2000)不区别汉字和字母
varchar2(4000)只能存储2000个汉字

nvarchar2最大2000
varchar2最大4000

38.更新表被锁,KILLspid后,select*fromtabnameforupdate独占资源。
v$locked_objectdba_objects联合可以知道锁表的session

39.PGAsort_area_size大小不够时,用到临时表空间。

40.oracle没有标识列,自动增长的这个概念

需要用序列来实现
CREATESEQUENCEsid
INCREMENTBY1
STARTWITH1
MAXVALUE99999999

selectsid.nextval,--取下一个序列
sid.currval--取当前序列
fromdual;

41.IMP/EXPbuffer达到1M以后,性能的提升并不大,5M或者10M的足够用

42.如果null参与聚集运算,则除count(*)之外其它聚集函数都忽略null.
如:
IDDD
1e
2null
selectcount(*)fromtable--结果是2
selectcount(DD)fromtable---结果是1

count(1)count(主键)这两个只扫描主键Index就可以得到数据,
count(*)是扫描表的。
所以count(1)count(主键)这两个效率高。
还有一种写法是countROWID)这也是只扫描Index的,效率高。

43.linux挂在windows共享的盘

1.启动nfs服务:servicenfsstart
2.mount-ousername=user,password=123456//10.85.2.194/share/mnt

44.Killsession并使状态直接变成killed

EXECUTEIMMEDIATE''ALTERSYSTEMKILLSESSION'''':sid,:serial#''''IMMEDIATE''
EXECUTEIMMEDIATE''ALTERSYSTEMKILLSESSION'''':sid,:serial#''''

immediate是立即kill,不会有status会变成killed状态的,清楚了在v$session里的信息

45.truncate只是删除了表中的记录,并不会改变表的结构及依赖约束所以truncate表后表的索引依然存在,但是表和索引所占用的空间会恢复到初始大小

46.这是看高速缓存命中率小于80%SQL
SELECTEXECUTIONS,DISK_READS,BUFFER_GETS,
  round((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2)Hit_radio,
  ROUND(DISK_READS/EXECUTIONS,2)Reads_per_run,
  SQL_TEXT
  FROMV$SQLAREA
  WHEREEXECUTIONS>0
  ANDBUFFER_GETS>0
  AND(BUFFER_GETS-DISK_READS)/BUFFER_GETS<0.8
  ORDERBY2desc,4DESC;

47.数据库在主备库切换之后要手动的用SQL来检查检查有没有死锁,如果有,kill就可以了..

SQL>select'altersystemkillsession'''||sid||','||serial#||''';'fromv$sessionwheresidin(selectsidfromv$lockwhereblock=1);
'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||''';'

48.EOF是标记控制字符开始,到结束,随便什么字符都可以用的

sqlplus'/assysdba'<<eof
{
shutdownimmediate;
startupforcedbapfile=$ORACLE_HOME/dbs/init.ora;
shutdownimmediate;
}
exit;
eof

49.parallel(table,4)并行度为4
parallel(table)如果使用parallel但未指定并行度,则DOP要通过初始化参数CPU_countParallel_THREADS_PER_CPU计算得到,

并行度为4的程序,最多可以分配或创建9个并行执行服务器来满足这个事务操作,所以并行操作速度有很大提高,但对CPU占用比较多

并行操作增加了事务操作的性能,但会连续的记录重做日志,并且造成瓶颈,所以可以使用nologging模式来避免瓶颈
sql>altertabletable_nameNOLOGGING;

50.约束名从表user_constraints表中找
SELECT*FROMUSER_CONSTRAINTSWHERETABLE_NAME='FJ5W_FZ_JMD_S'

51.Oracle确实没有convert函数,只有to_char()to_date()...
sql:CONVERT(VARCHAR(10),GETDATE(),111)
oracle:to_char(sysdate,'yyyy/mm/dd')

52.V$视图是由catalog.sql脚本创建的.所以升级系统后要执行这个脚本..

53.Oracleorapwd命令
orapwdfile=D:/PWDorcl.orapassword=adminentries=5;
windowsoracle默认的位置是db_1/database目录,默认的文件名是pwdSID.ora,对于别的文件名是不认的。linuxoracle默认的位置是$ORACLE_HOME/dbs目录,默认的文件名是orapwSID,对于别的文件名是不认的。
其中参数entries的含义是表示口令文件中可以存放的最大用户数,对应于允许以SYSDBA/SYSOPER权限登录数据库的最大用户数,如果用户数超过这个值只能重建口令文件,增大entries

54.oracle表空间大小没有限制,根存储空间而定。
oracle9i或以下,单个数据文件最大32G(对于8K的数据块),整个数据库最多有64K个数据文件.单个表空间的数据文件数量没有具体的限制,也应该是在64K以下.
oracle10g以上,引入了bigfiletablespace,bigfiletablespace只有一个数据文件,最大为4G*8k=32T
databasefilesize:
Operatingsystemdependent.Limitedbymaximumoperatingsystemfilesize;typically222(222次方)or4Mblocks

55.ROWNUM是查询时的一个记录号,是一个伪列

rownum只和最终输出结果orderby之前的顺序一致

selectrownum,t.*fromuser_tablest;
selectrownum,t.*from(select*fromuser_tablesorderbytable_name)t;
select*from(selectrownum,t.*fromuser_tablestorderbytable_name);

56.你的数据库是dedicated还是shared模式Oracle数据库服务器,根据客户端请求后process的调度方式,分为dedicated(专用)模式和shared(共享)模式。
专用模式意味着每个客户端的连接后,Oracle都会分配一个新的process和自进行交互。而共享模式是,通过一个调度程序,来分配process,有可能是以前客户端已经处理过的空闲的process,由于process的模式不是专用的,所以相对来说,节省资源。
那么我们如何来查看我们的oracle是哪种模式呢?
Window系统下,通过任务管理列表里,查看Oracle.exe进程所占用的线程数,如果没有这个指标,可以通过view->selectcolumns->checkedThreadcount.即可。
sqlplus连接成功以后,threadcount如果会增加,即为专用模式,来一个增加一个,走一个减一个。反之,共享模式。
Linux下,通过psoracle查看oracle的进程个数。和window的判断方法一样,不过命令不同而已。
我们也可以查看数据库的parameter,如果shared_servers的数目大于0的话,即是。

57.orderbyt.tm_errordesc必须是groupby里的字符,或者是统计字段。

58.大量更新表时:
1.关掉tableb的所有触发器,这个一定要关掉,movingdata的时候一定要全部关掉,不然批量操作的时候卡死你Y的。
altersystemtablebdisablealltriggers;
执行完毕之后,启动触发器
altersystemtablebenablealltriggers;
2,除了主键索引之外,tableb表剩余的索引全部删除掉。等执行完毕之后,重建索引(索引重建很快,我的800万数据的表的6个索引重建才花了2分钟而已)

59.访问V$FIXED_VIEW_DEFINITION视图可以获取组成V$视图的底层X$表的所有信息

selectcount(*)fromv$fixed_tablewherenamelike'V%';
selectview_namefromV$fixed_view_definition;
selectcount(*)fromv$fixed_view_definition;
selectview_definitionfromV$FIXED_VIEW_DEFINITIONWHEREview_name='V$PX_SESSION';

60.dba_views是从Oracle底层数据库的表中得到的,不是从X$表或者v$视图。

SQL>SETLONG2000000
SQL>selecttextfromdba_viewswhereviewname='DBA_IND_PARTITIONS';

61.Oracle10.2.0.1中有613X$表,9i394张。X$表包含了特定实例的各方面的信息,如当前的配置信息,连接到实例的会话,以及丰富而有价值的性能信息。X$表并不是驻留在数据库文件的永久表或临时表。X$表仅仅驻留在内存中,当实例启动时,他们就创建了,在内存中进行实时的维护。它们中的大多数至少需要装载或已经打开的数据库。X$表为SYS用户所拥有,并且是只读的。不能进行DML(更新,插入,删除)

62.ParseCPUtoParseElapsd%:127.27%Non-ParseCPU:97.12
parsecpu=amountofcputimeusedtoparse
elapsedtimeparsing=amountoftimeonthewallclockspentparsing.
100*parsetimecpu/parsetimeelapsed=ParseCPUtoParseElapsd%
inaperfectworld,withnocontention--parsecpu=parseelapsed.
ratio=100%
inabadworld,ittakeslongertoparse(elapsed)thencputimeused
(contention).ratio<100%
inyourcase,whatthisissayingistheCPUexceededtheelapsed,which
technicallyisnotpossible--buthappensduetotheway"smallfastthings"
aremeasuredoncomputers.Itishardtomeasurethingsthathappenvery
rapidlyaccurately.So,thisratio,when>100%,isthesameas"100%"forall
intentsandpurposes

63.sqlnet.ora文件里的内容注释掉,在重启下lsnrctl,应该就可以:
#SQLNET.AUTHENTICATION_SERVICES=(NTS)
EasyConnect指的是使用connscott/tiger@hostname(orip)port/globaldatabasename的方式连接数据库,这种方法不需要tnsnames.ora文件的任何内容.
sqlnet.ora中需要声明你使用的命名方法。需要注意的是defaultdomain,如果你声明了,那么在tnsnames.ora中必须在netservicename后面把域名附加上。这样才能保证你在connscott/tiger@netsvname时候能够成功

64.sqlnet.ora文件决定找数据库服务器别名的方式
  默认的参数有
  NAMES.DEFAULT_DOMAIN=WORLD
  NAMES.DIRECTORY_PATH=(TNSNAMES,ONAMES,HOSTNAME)
  
  如果你的ORACLE客户端和服务器默认的域名不一样,需要用#号注释第一行
  #NAMES.DEFAULT_DOMAIN=WORLD
  使它不起作用。
  NAMES.DIRECTORY_PATH指定找服务器别名的顺序(本地的tnsnames.ora文件,命名服务器,主机名方式)

65.在日文操作系统下用plsql开发
要求只能输入半角,用Length(a)LengthB(a)可以判断出是否是半角。
但是还有个要求是不能输入日本语,也就是要怎么判断是'半角片假名'呢?

TO_SINGLE_BYTE()函数转成半角在插入

66.SQL>lsnrctlsetlog_statusoff;
!在SQL里面表示执行非SQL的语句
如:
SQL>fdisk-l
window下是$,linux下是!

67.schema的交叉型triggerexp/imp时会丢失,因为所依赖的基表在exp/imp时断开了。除非你把这些shema全都导出。

68.Number的数据声明如下:
表示作用说明
Number(p,s)声明一个定点数p(precision)为精度,s(scale)表示小数点右边的数字个数,精度最大值为38scale的取值范围为-84127
Number(p)声明一个整数相当于Number(p,0)
Number声明一个浮点数其精度为38,要注意的是scale的值没有应用,也就是说scale的指不能简单的理解为0,或者其他的数。

定点数的精度(p)和刻度(s)遵循以下规则:
?当一个数的整数部分的长度>p-s时,Oracle就会报错
?当一个数的小数部分的长度>s时,Oracle就会舍入。
?s(scale)为负数时,Oracle就对小数点左边的s个数字进行舍入。
?s>p,p表示小数点后第s位向左最多可以有多少位数字,如果大于pOracle报错,小数点后s位向右的数字被舍入

69.oracleupdate多表关联
UPDATEa
SET(ID,NAME)=(SELECTb.ID,b.NAME
FROMb
WHEREa.ID=b.ID)
WHEREEXISTS(SELECT1
FROMb
WHEREa.ID=b.ID)

70.查看SCN:
SELECTdbms_flashback.get_system_change_numberFROMdual;
SELECTCURRENT_SCNFROMV$DATABASE;

71.注意理解系统时间标记与scn的每5分钟匹配一次这句话举个例子,比如scn:339988,339989分别匹配08-05-3013:52:002008-13:57:00,则当你通过asoftimestamp查询08-05-3013:52:0008-05-3013:56:59这段时间点内的时间时,oracle都会将其匹配为scn:339988undo表空间中查找,也就说在这个时间内,不管你指定的时间点是什么,查询返回的都将是08-05-3013:52:00这个时刻的数据。

查看SCNtimestamp之间的对应关系:
selectscn,to_char(time_dp,'yyyy-mm-ddhh24:mi:ss')fromsys.smon_scn_time;

72.当查询的记录的结果集大于总记录的20%时,一定要使用全表扫描

73.AWR显示,占用资源较多的SQL是类似时,这是对这些SQL就应该使用绑定变量来减少硬解析.

74.selectname,value,ISSYS_MODIFIABLEfromv$parameter
如果ISSYS_MODIFIABLE返回的是false,说明该参数无法用altersystem语句动态修改,需要重启数据库

75.oracle子查询中能使用orderby
from子句后面的内联视图是可以使用orderby子句进行排序的。
然而,其它视图或子查询是不能用orderby进行排序的
如果你要用选择前几条的话,需要在套一层变成from后面的内联视图。
比如
select*fromdepta
2wherea.deptnoin
3(
selectdeptonfrom(
4selectb.deptnofromdeptb
5orderbyb.dname
6)[whererownum<5])

76.修改temp表空间自动增长:
alterdatabasetempfile'D:/ORACLE/ORADATA/DBA/TEMP01.DBF'autoextendonnext20m;
修改表空间自动增长:
alterdatabasedatefile'D:/ORACLE/ORADATA/DBA/user01.DBF'autoextendonnext20m;

77.alterindexrebuildalterindexrebuildonline的区别
online时可以在该索引的基表上执行DML,在在对基表操作的同时可以REBUILDINDEX,但是不能执行DDL语句,所以他们的锁机制是不样的。
创建索引时通常会对该表设置一个表级共享(DML),如果设置ONLINE,
如果是非ONLINE方式,通常会对该表设置一个表级共享(DML)锁,那么就对DML语句冲突,如果设置ONLINE,(会使用临时日志IOT表来记录中间改变的数据),但要使用两倍于传统方法的空间.表会变成行级共享锁,在创建索引或者ALTER完成后,对临时日志表与基表进行MERGE

注意并行处理,DDL,位图索引不能使用ONLINE

78.colb,字符串大对象,存的是长字符串数据
blob,二进制大对象,存的是二进制型,比如图像、音频数据

79.SQLPLUS默认不是自动提交的.
自动提交命令:
SQL>setautocommiton

退出SQLPLUS时会自动提交
指定DDL,如CREATE,ALTER,DROP,会自动提交
执行DCL,如GRANT,REVOKE,会自动提交

80.临时表空间不能脱机。system,和正在使用(有活动sessiontransaction)的也不能。

81.查询正在执行的sql
selectOSUSER,PROGRAM,USERNAME,SCHEMANAME,B.Cpu_Time,STATUS,B.SQL_TEXT
fromV$SESSIONA
LEFTJOINV$SQLBONA.SQL_ADDRESS=B.ADDRESS
ANDA.SQL_HASH_VALUE=B.HASH_VALUE
whereb.SQL_TEXTisnotnull
orderbyb.cpu_timedesc

82.Oracleto_char()函数在计算一年中第几周是从该年的11日开始的。

83.正在连接的用户不能删除,确实要删除的话,如下
1selectsid,serial#,usernamefromv$sessionwhereuser='USERNAME';
2altersystemkillsession'sid,serial#';
3dropuserusernamecascade;

84.在排除索引限制的条件下,如果索引还是没有被引用,可以检查下参数。
optimizer_index_cost_adj100,该参数影响优化器选择索引还是全表扫描的倾向,将其修改为35.

85.Oracle中,要获得日期中的年份,例如把sysdate中的年份取出来,并不是一件难事。
常用的方法是:Selectto_number(to_char(sysdate,''yyyy''))fromdual
而实际上,oracle本身有更好的方法,那就是使用Extract函数,
使用方法是:SelectExtract(yearfromsysdate)fromdual,这种方法省掉了类型转换,看上去更加简洁。
相应的,要取得月份或日,可以用selectextract(monthfromsysdate)fromdualselectextract(dayfromsysdate)fromdual
此方法获得的结果,是数值型的,大家可以设置一个方法测试一下。
selectEXTRACT(yearFROMto_date('2009-11-10','yyyy-mm-dd'))yearfromdual;

86.查询数据库默认的表空间类型:
SQL>selectproperty_name,property_valuefromdatabase_propertieswhereproperty_name='DEFAULT_TBS_TYPE';
PROPERTY_NAMEPROPERTY_VALUE
------------------------------------
DEFAULT_TBS_TYPEBIGFILE

87.10g中,有一个特性,就是bigfiletablespace,这种类型的表空间只能有一个数据文件,且该数据文件允许有4G的数据快,即如果db_block_size=8k的话,最大容量为4G*8K=32T,当然,这个还要看操作系统的限制了。

修改数据库默认的表空间类型为smallfile,就可以为表空间创建多个数据文件了。
SQL>alterdatabasesetdefaultsmallfiletablespace;
Databasealtered.

也可以在创建表空间时,指定表空间类型:createsmallfile/bigfiletablespace....

88.exp失败执行的脚本:

Catexp.sql这个脚本是用于生成exp命令执行时所需要的一些表和视图,在执行exp命令出现找不到什么什么表,什么什么视图时使用。
Catmeta.sql这个脚本是在升级后执行exp命令出现错误时运行,说是因为升级不成功,运行这个脚本可以重新创建系统表。

89.日期一般就用to_date(str,format)格式转换'yyyy-mm-dd'这样的可以直接用date'xxxx'简化

selectdate'2009-11-11'asdfromdual;
select*fromtwheret.day=date'2009-11-11';

90.sqlplus命令save可以把sql语句保存到文件中,可是默认的存放路径是$ORACLE_HOME/bin,即sqlplus可执行文件存放的位置,当然有的人说在文件名前加绝对路径即可:saved:/oracle/admin/oradb/emp.sql
可是这种写法未免太麻烦,特别是使用get命令,也得使用绝对路径,因此如果能够修改save的默认位置,那么save/get就好写的多了。

91.查询某一对象的类型,比如查询'v$datafile'是同义词还是视图?
select*fromall_objectswhereobject_name=upper('v$datafile')

92.http://download.csdn.net/source/1841831
我上传了一个包,可以获得汉字的拼音或者首字母,oracle汉字转拼音

93.IndexENABLEDISABLE适用于FUNCTION-BASEDINDEX
如果普通索引的话你就用unusable而不是disable
ENABLEDISABLE只针对函数索引。

ENABLEappliesonlytoafunction-basedindexthathasbeendisabledbecauseauser-definedfunctionusedbytheindexwasdroppedorreplaced.Thisclauseenablessuchanindexiftheseconditionsaretrue:

*Thefunctioniscurrentlyvalid
*Thesignatureofthecurrentfunctionmatchesthesignatureofthefunctionwhentheindexwascreated
*ThefunctioniscurrentlymarkedasDETERMINISTIC
RestrictiononEnablingFunction-basedIndexes
YoucannotspecifyanyotherclausesofALTERINDEXinthesamestatementwithENABLE.

DISABLEClause

DISABLEappliesonlytoafunction-basedindex.Thisclauseletsyoudisabletheuseofafunction-basedindex.Youmightwanttodoso,forexample,whileworkingonthebodyofthefunction.AfterwardyoucaneitherrebuildtheindexorspecifyanotherALTERINDEXstatementwiththeENABLEkeyword.

楼主试试:
alterindexxxunusable;

UNUSABLEClauseSpecifyUNUSABLEtomarktheindexorindexpartition(s)orindexsubpartition(s)UNUSABLE.Anunusableindexmustberebuilt,ordroppedandre-created,beforeitcanbeused.WhileonepartitionismarkedUNUSABLE,theotherpartitionsoftheindexarestillvalid.Youcanexecutestatementsthatrequiretheindexifthestatementsdonotaccesstheunusablepartition.Youcanalsosplitorrenametheunusablepartitionbeforerebuildingit.

94.如何清除inactivesession
1.方法一
(1)UNIX的方法
Asql>selectusename,sid,paddr,status
fromv$session
whereusename='USERNAME'
ANDSTATUS='INACTIVE';
Bsql>SELECTSPIDFROMV$PROCESSWHEREADDR=上一步查出的PADDR
C$KILLSPID

2WINDOWSnt/2000的方法
c:/>orakillSIDSPID

2,方法二
selectA.SID,B.SPID,A.SERIAL#,a.lockwait,A.USERNAME,A.OSUSER,a.logon_time,a.last_call_et/3600LAST_HOUR,A.STATUS,
'orakill'||sid||''||spidHOST_COMMAND,
'altersystemkillsession'''||A.sid||','||A.SERIAL#||''''SQL_COMMAND
fromv$sessionA,V$PROCESSBwhereA.PADDR=B.ADDRANDSID>6

95.sqlnet.ora文件中配置sqlnet.expire_time参数,DeadConnectionDetection在服务器端使用。每当一个客户端的连接建立时,SQL*NET读取此参数,以决定多长时间发送包给连接的客户端,侦测连接是否还有效,如果无效,则通知操作系统释放该会话持有的资源。防止因网络的异常中断导致会话长期持有资源不释放。设置为0应该是不启用DCD
sqlnet.expire_time=10,则表示10分钟
sqlnet.expire_time的单位为分钟.

96.EXP/IMP可以使用参数文件。mypar.par,内容就是你要指定的参数,
owner=scott
file=mydump.dmp
log=mydump.log
direct=y

在用exp时用parfile参数指定这个文件就可以了
expmyname/mypass@mydbparfile=mypar.par

97.分页一般用到两种办法:
1,利用rownum
2,分析函数row_number()over()
1.
selectfrom(
selectt.*,rownumrnfrom(
select*froma
orderbycol1)t
wherernbetween101and200)
2.
select*
from(selectt.*,row_number()over(orderbycol1)rn
fromt)
wherernbetween101and200

98.row_number()ROWNUM是看起来相似但概念完全不同的东西,
row_number()是一个分析函数(AnalyticFunction),它返回的是基于over()参数的行号。
rownumoracle特别提供的一个伪列,它只作用于查询的结果集,根据结果集输出的先后次序给每个纪录顺次编号。
row_number()要比rownum高很多.对一大表测试时,row_number()用时6srownum用时17s.

99.listener主要是侦听从客户端发来的对数据库的连接请求。
如果你在服务器端用sqlplus进行连接,监听没有启动也是可以连上的,但是从远程来访问数据库,或者用PL/SQLdev或者TOAD等进行连接,就必须启动监听。

100.这个命令可以查看建表的SQL语句..
selectdbms_metadata.get_ddl('TABLE','&tname')fromdual;

101.查询视图可以通过
select*fromall_views
索引:all_indexes,索引和列的关系all_ind_columnstable_name即索引所在的表
如果只想查询当前用户下的,将上面数据字典的all改成user

102.char最大长度是2000.
SQL>createtabletest(v2char(2001));
createtabletest(cchar(2001))
ERRORatline1:
ORA-00910:specifiedlengthtoolongforitsdatatype
SQL>createtabletest1(cchar(2000));
Tablecreated.

103.Oralce快照是Oralce7时候的叫法吧,8i之后改名物化视图

104,一个小触发器
createtablet_temp
(
idvarchar2(10)primarykey,
len1number(6,0),
len2number(6,0),
lennumber(7,0)
)
update某一行的len1len2值后,则修改该行len的值(len=len1+len2)
或者insert一条新的记录后,修改len=len1+len2
哪位帮我用触发器实现上面的功能
createtriggertri
beforinsertorupdateont_temp
foreachrow
begin
:NEW.len:=:NEW.len1+:NEW.len2;
end;

createorreplacetriggertgtemp
beforeinsertorupdateoflen1,len2
ont_temp
foreachrow
begin
:new.len:=:new.len1+:new.len2;
end;

oracle不允许触发器修改它正在触发的表,故用before可以,after不行..

105.udump下的trc文件可以通过配置不让产生,利用命令
altersystemsetsql_trace=false;
其他的不能修改,只能手动的启动trace,手动的关闭trace.

比如:
altersessionsetevents'immediatetracenamelibrary_cache|controlf|systemstate|processstate|file_hdrs|REDOHDRlevel10';
altersessionsetevents'immediatetracenameoff';

altersessionsetevents'10046tracenamecontextforever,level12';
altersessionsetevents'10046tracenamecontextoff';

altersystemsetevents'10046tracenamecontextforever,level12';
altersystemsetevents'10046tracenamecontextoff';

106.重复数据只显示一条:
selectmin(id)id,b,cfromtbgroupbyb,c

107.删除重复数据:
deletefromtbwhererowidnotin(selectmin(rowid)fromtbgroupbyb,c);

108.oracle批量重建索引
createorreplaceprocedurep_rebuild_all_index
(tablespace_nameinvarchar2)
as
sqltvarchar(200);
begin
foridxin(selectindex_name,tablespace_name,statusfromuser_indexeswheretablespace_name=tablespace_nameandstatus='VALID'andtemporary='N')loop
begin
sqlt:='alterindex'||idx.index_name||'rebuild';
dbms_output.put_line(idx.index_name);
dbms_output.put_line(sqlt);
EXECUTEIMMEDIATEsqlt;
--错误后循环继续执行。
EXCEPTION
WHENOTHERSTHEN
dbms_output.put_line(SQLERRM);
end;
endloop;
end;
oracle存储过程批量重建索引。
测试方法
declare
--表空间名称
tablespace_namevarchar2(100);
begin
tablespace_name:='dddd';
p_rebuild_all_index(tablespace_name);
end;

109.oracle会将SQL语句中in后面的东西生成一张内存中的临时表。然后进行查询。所以在相关字段上见索引比较重要。

110.Oracle中查看各个表、表空间占用空间的大小查看当前用户每个表占用空间的大小:
  SelectSegment_Name,Sum(bytes)/1024/1024FromUser_ExtentsGroupBySegment_Name

查看每个表空间占用空间的大小:
  SelectTablespace_Name,Sum(bytes)/1024/1024FromDba_SegmentsGroupByTablespace_Name

111.格式化2个时间相减

SELECTEXTRACT(DAYFROMinterval)
||''
||EXTRACT(HOURFROMinterval)
||'小时'
||EXTRACT(MINUTEFROMinterval)
||'分钟'
||EXTRACT(SECONDFROMinterval)
||''
间隔
FROM(SELECTNUMTODSINTERVAL(callbegin-callend,'DAY')interval
FROMtbilllog12t
WHEREcallbegin=
TO_DATE('2009-12-10:00:58','YYYY-MM-DDHH24:MI:SS'))

间隔
-----------------------------------------------
00小时0分钟-24
1rowselected.

112.相关定义

ORACLE_SID:操作系统环境变量ORACLE_SID用于和操作系统交互。也就是说,在操作系统中要想得到实例名,就必须使用ORACLE_SID,在操作系统级别唯一识别oracleinstance.
LD_LIBRARY_PATH:你的系统用到oracle共享库存在于需要指定的路径。
ORACLE_TERM:是ORACLEXWINDOW图形界面安装时要使用的变量,必须正确设置,否则安装程序无法在xwindow中启动。
ORACLE_OWNER:对该文件具有访问特权的用户;通常是创建该文件的用户。

113.修改系统时间格式:
altersessionsetnls_date_format='YYYY-MM-DD'

修改默认的时间格式:
1windows,在注册表中HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE下增加一字符串:NLS_DATE_FORMAT,把其值设成:YYYY-MM-DD
2Unix下,在用户的.profile文件中增加以下内容:
NLS_DATE_FORMAT=YYYY-MM-DD
exportNLS_DATE_FORMAT

Nls_langLinux系统的环境变量;
Nls_language是数据库的参数。

如果数据库字符集没有问题,而查询出来的却是乱码,可以检查下系统的环境变量。
exportNLS_LANG="simplifiedchinese_china.zhs16gbk"

修改系统时间格式:
SQL>altersessionsetnls_date_format='yyyy-mm-ddhh24:mi:ss';
或者在系统exportNLS_DATE_FORMAT=YYYY-MM-DDHH24:MI:SS
或者在pro_file里面添加一个变量nls_date_format,这样就不用每次都需要设置时间了。

114.ORACLE9.2.0.8不支持,notin()中带unionSQL.

115.recoverdatabaseusingbackupcontrolfilerecoverdatabaseusingbackupcontrolfileuntilcancel
前者是利用backupcontrolfile完全恢复
后者是利用backupcontrolfile不完全恢复。

usingbackupcontrolfile告诉Oracle不要使用controlfile中的scn
usingbackupcontrolfileuntilcancel用于redologfile丢失时使用,如redologsequence#1,2,3,4,5,6,丢失45,会恢复到3

两个连用告诉Oracle恢复的时候恢复到最后一个可用的redologfile,不管控制文件中的scn是多少

116.一般来说ORACLE实例内存=物理内存*80%

对于OLTP系统:
PGA=实例内存*20%
SGA=实例内存*80%

对于OLAP系统:
PGA=实例内存*50%
SGA=实例内存*50%

混合型系统在二者之间

确定内存容量后,
对于PGA:使用WORKAREA_SIZE_POLICY设置为AUT,表示PGA自动管理
PGA_AGREGGATE_TARGET参数分配PGA

SGA_TARGET参数分配SGA目标值
SGA_MAX_SIZE分配SGA最大值

------------------------------------------------------------------------------
Blog: http://blog.csdn.net/tianlesoftware
网上资源: http://tianlesoftware.download.csdn.net
相关视频:http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
DBA1 群:62697716(满); DBA2 群:62697977
<!--EndFragment-->
分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics