Oracle 小知识 总结(一)

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

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

1.每天的8：00到23：00每隔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记录的SCN，DBWR触发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.RECOVERDATABASEUNTILCANCEL和RECOVERDATABASEUNTILCANCELUSINGBACKUPCONTROLFILE;区别

1)RECOVERDATABASEUNTILCANCEL==>OPENDATABASERESETLOG

==>DATAFILEHEADERSCN一定会小于CONTROLFILE的DATAFILESCN

如果你有进行RESTOREDATAFILE，则该RESTORE的DATAFILEHEADERSCN一定会小于目前CONTROLFILE的DATAFILESCN，此时会无法开启数据库，必须进行mediarecovery。重做archivelog直到该datafileheader的SCN=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(2的22次方）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，可以通过这2张dynamicview来确定：
v$locked_object,V$session

可以把该session杀掉。
selectsid,serial#fromv$sessionwhereusername='XXXX'
把得到的sid，serial#号替换到下面的语句中：
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来查看当前session的trace文件的文件名
如下
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
只取出字段b，c不重复的字段，

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

27.查询锁的情况
1).insertintotestvalues(1);
2).selectuserenv('sid')fromdual;
3).select*fromv$lockwheresid=''
sid在v$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函数为指定元素而截去的日期值。
其具体的语法格式如下：
TRUNC（date[,fmt]）
其中：
date一个日期值
fmt日期格式，该日期将由指定的元素格式所截去。忽略它则由最近的日期截去
下面是该函数的使用情况：
TRUNC（TO_DATE(’24-Nov-199908:00pm’,’dd-mon-yyyyhh:miam’)）
=’24-Nov-199912:00:00am’
TRUNC（TO_DATE(’24-Nov-199908:37pm’,’dd-mon-yyyyhh:miam’,’hh’)）=’24-Nov-199908:00:00am’

2.TRUNC(fornumber); font-siz

分享到：

Oracle 数据类型分享 | 你的第一个Javascript服务器端程序(一)

• 2010-05-25 11:30
• 浏览 831
• 评论(0)
• 查看更多