Oracle小知识总结

2022-10-14 13:09:36

1. 每天的8：00到23：00每隔5分钟执行一个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等命令手工的删除备份文件，这个动作不会同步到控制文件，造成不一致，这种不一致会导致使用rman时报错。可以使用delete 删除这些过期的记录，在用就不会报错了。

RMAN>crosscheck copy;

RMAN>list copy;

RMAN>delete expired copy;

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写到四个地方去。

三个地方于control file内，一个在datafile header。

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

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

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

LOG_CHECKPOINT_INTERVAL,

LOG_CHECKPOINT_TIMEOUT ,

FAST_START_IO_TARGET 强制时；

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

ALter system checkpoint;

alter tablespace ... offline;

4. 每次日志切换时;

alter system switch logfile

注意：

1. alter system switch logfile也将触发完全检查点的发生。

2. alter database datafile ... offline 不会触发检查点进程。

7. RECOVER DATABASE UNTIL CANCEL 和 RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE; 区别

1) RECOVER DATABASE UNTIL CANCEL ==> OPEN DATABASE RESETLOG

==> DATAFILE HEADER SCN一定会小于CONTROLFILE的DATAFILE SCN

如果你有进行RESTORE DATAFILE，则该RESTORE的DATAFILE HEADER SCN一定会小于目前CONTROLFILE的DATAFILE SCN，此时会无法开启数据库，必须进行media recovery。重做archive log直到该datafile header的SCN=current scn

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

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

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 作用

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

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

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

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

oracle9i或以下,单个数据文件最大32G(对于8K的数据块),整个数据库最多有64K个数据文件.单个表空间的数据文件数量没有具体的限制,也应该是在64K以下. oracle10g以上,引入了bigfile tablespace,bigfile tablespace只有一个数据文件,最大为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利用现有的表创建一张新表，只要表结构相同

create table david as select * from all_users where 1<>1;

12. 循环插入数据

declare i integer;

begin

for i in 1..100000 loop

insert into test values(i);

end loop;

commit;

end;

13. 开发人员通常习惯赋予所有用户DBA权限，查看权限

Select * From User_Role_Privs

Select * From User_Sys_Privs

14. 看数据文件大小,单位是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; #指定区尺寸为128k,如不指定，区尺寸默认为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. 查询表的行数

select count(*) from table_name; 全表扫描，会自已找表有索引列并且该列为非空的(因为只有非空才能确保记录数是全的),走INDEX_FFS.

select count(1) from table_name; 不走索引，效率要高，但在表中有非空索引时也是走 INDEX_FFS 的

22. 用function来查看当前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;

运行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，但是系统不做Log。清空表的数据，仅保留类别结构，被删除的数据不能ROLLBACK，这点是与DELETE的主要差别.不能Rollback也就是会删除log文件.在SQL Server里面如果你用Truncate，自动增长的行又会从1开始

25. select substrb(‘大小abc‘,1,6) from dual;

26. 表中的数据如下图所示

a b c

1 PP41982 SO90029

2 PP41982 SO90029

只取出字段b，c不重复的字段，

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 中有这个列，可以结合v$session 查询更多的信息

28. 删除重复行：

SQL> DELETE FROM a WHERE ROWID IN( SELECT MAX(ROWID) FROM a);

已删除 1 行。

SQL> select * from a;

NAME ID ORDER_TITLE

-------------------- ---------- --------------------

中国科学技术大学 1 科学

SQL>

29. 查看索引信息

--查看索引名称

SELECT * FROM USER_INDEXES;

-- 查看索引列名

SELECT * FROM DBA_IND_COLUMNS;

SELECT * FROM USER_IND_COLUMNS;

SELECT * FROM ALL_IND_COLUMNS;

30. oracle trunc()函数用法

1.TRUNC(for dates)

TRUNC函数为指定元素而截去的日期值。

其具体的语法格式如下：

TRUNC（date[,fmt]）

其中：

date 一个日期值

fmt 日期格式，该日期将由指定的元素格式所截去。忽略它则由最近的日期截去

下面是该函数的使用情况：

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函数返回处理后的数值，其工作机制与ROUND函数极为类似，只是该函数不对指定小数前或后的部分做相应舍入选择处理，而统统截去。

其具体的语法格式如下

TRUNC（number[,decimals]）

其中：

number 待做截取处理的数值

decimals 指明需保留小数点后面的位数。可选项，忽略它则截去所有的小数部分

下面是该函数的使用情况：

TRUNC（89.985，2）=89.98

TRUNC（89.985）=89

TRUNC（89.985，-1）=80

注意：第二个参数可以为负数，表示为小数点左边指定位数后面的部分截去，即均以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值数据

select * from table where mod(datavalue,5) = 0;

34. Union与Union All的区别

如果我们需要将两个select语句的结果作为一个整体显示出来，我们就需要用到union或者union all关键字。union(或称为联合)的作用是将多个结果合并在一起显示出来。

union和union all的区别是,union会自动压缩多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复。

Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

Union All：对两个结果集进行并集操作，包括重复行，不进行排序；

select empno,ename from emp

union

select deptno,dname from dept

我们没有必要在每一个select结果集中使用order by子句来进行排序，我们可以在最后使用一条order by来对整个结果进行排序。例如：

select empno,ename from emp

union

select deptno,dname from dept

order by ename;

35. 查看看到A用户下的所有数据量>100万的表的信息

select * from user_all_tables a

where a.num_rows>1000000

前提是a用户下所有表的统计信息都是最新的。

保险的办法是所有表都count一遍:

select ‘select ‘||‘‘‘‘||table_name ||‘‘‘,‘||‘count(*) from ‘||table_name from user_all_tables ;

把上面这段sql的执行结果拷贝出来执行即可

36. SQLPLUS 里执行 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‘));

查看结果就是前面SQL语句的执行计划。

37. nvarchar2(2000) 不区别汉字和字母

varchar2(4000) 只能存储2000 个汉字

nvarchar2最大2000

varchar2 最大4000

38. 更新表被锁，KILL spid后，select * from tabname for update 独占资源。

v$locked_object dba_objects 联合可以知道锁表的session

39. PGA中sort_area_size大小不够时，用到临时表空间。

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

需要用序列来实现

CREATE SEQUENCE sid

INCREMENT BY 1

START WITH 1

MAXVALUE 99999999

select sid.nextval, --取下一个序列

sid.currval --取当前序列

from dual;

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

42. 如果null参与聚集运算，则除count(*)之外其它聚集函数都忽略null.

如：

ID DD

1 e

2 null

select count(*) from table --结果是2

select count(DD) from table ---结果是1

count(1)和count(主键) 这两个只扫描主键Index就可以得到数据，

count(*)是扫描表的。

所以count(1)和count(主键)这两个效率高。

还有一种写法是count（ROWID）这也是只扫描Index的，效率高。

43. linux 挂在windows 共享的盘

1. 启动nfs服务: service nfs start

2.mount -o username=user,password=123456 //10.85.2.194/share /mnt

44. Kill session 并使状态直接变成killed

EXECUTE IMMEDIATE ‘‘ALTER SYSTEM KILL SESSION ‘‘‘‘:sid,:serial#‘‘‘‘ IMMEDIATE‘‘

EXECUTE IMMEDIATE ‘‘ALTER SYSTEM KILL SESSION ‘‘‘‘:sid,:serial#‘‘‘‘

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

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

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. 数据库在主备库切换之后要手动的用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是标记控制字符开始，到结束，随便什么字符都可以用的

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要通过初始化参数CPU_count 和Parallel_THREADS_PER_CPU计算得到，

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

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

sql> alter table table_name NOLOGGING;

50. 约束名从表user_constraints表中找

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_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. 用Oracle的orapwd 命令

orapwd file=D:/PWDorcl.ora password=admin entries=5;

windows下oracle默认的位置是db_1/database目录，默认的文件名是pwdSID.ora，对于别的文件名是不认的。linux下oracle默认的位置是$ORACLE_HOME/dbs目录，默认的文件名是orapwSID，对于别的文件名是不认的。

其中参数entries的含义是表示口令文件中可以存放的最大用户数，对应于允许以SYSDBA/SYSOPER权限登录数据库的最大用户数，如果用户数超过这个值只能重建口令文件，增大entries。

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

oracle9i或以下,单个数据文件最大32G(对于8K的数据块),整个数据库最多有64K个数据文件.单个表空间的数据文件数量没有具体的限制,也应该是在64K以下.

oracle10g以上,引入了bigfile tablespace,bigfile tablespace只有一个数据文件,最大为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只和最终输出结果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. 你的数据库是dedicated还是shared模式Oracle数据库服务器，根据客户端请求后process的调度方式，分为dedicated（专用）模式和shared（共享）模式。

专用模式意味着每个客户端的连接后，Oracle都会分配一个新的process和自进行交互。而共享模式是，通过一个调度程序，来分配process，有可能是以前客户端已经处理过的空闲的process，由于process的模式不是专用的，所以相对来说，节省资源。

那么我们如何来查看我们的oracle是哪种模式呢?

Window系统下，通过任务管理列表里，查看Oracle.exe进程所占用的线程数，如果没有这个指标，可以通过view->select columns->checked Thread count.即可。

用sqlplus连接成功以后，thread count如果会增加，即为专用模式，来一个增加一个，走一个减一个。反之，共享模式。

Linux下，通过ps oracle查看oracle的进程个数。和window的判断方法一样，不过命令不同而已。

我们也可以查看数据库的parameter，如果shared_servers的数目大于0的话，即是。

57. order by t.tm_error desc，必须是 group by里的字符，或者是统计字段。

58. 大量更新表时：

1.关掉tableb 的所有触发器，这个一定要关掉，moving data的时候一定要全部关掉，不然批量操作的时候卡死你Y的。

alter system tableb disable all triggers;

执行完毕之后，启动触发器

alter system tableb enable all triggers;

2，除了主键索引之外，tableb表剩余的索引全部删除掉。等执行完毕之后，重建索引(索引重建很快，我的800万数据的表的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底层数据库的表中得到的，不是从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$表包含了特定实例的各方面的信息，如当前的配置信息，连接到实例的会话，以及丰富而有价值的性能信息。 X$表并不是驻留在数据库文件的永久表或临时表。X$表仅仅驻留在内存中，当实例启动时，他们就创建了，在内存中进行实时的维护。它们中的大多数至少需要装载或已经打开的数据库。X$表为SYS用户所拥有，并且是只读的。不能进行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文件里的内容注释掉，在重启下lsnrctl，应该就可以：

#SQLNET.AUTHENTICATION_SERVICES = (NTS)

Easy Connect指的是使用conn scott/tiger@hostname (or ip)：port/global database name的方式连接数据库，这种方法不需要tnsnames.ora文件的任何内容．

在sqlnet.ora中需要声明你使用的命名方法。需要注意的是default domain，如果你声明了，那么在tnsnames.ora中必须在net service name后面把域名附加上。这样才能保证你在conn scott/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. 在日文操作系统下用pl sql开发

要求只能输入半角，用Length(a)，LengthB(a)可以判断出是否是半角。

但是还有个要求是不能输入日本语，也就是要怎么判断是‘半角片假名‘呢？

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

66. SQL> ！lsnrctl set log_status off;

！在SQL里面表示执行非SQL的语句

如：

SQL> ！fdisk -l

window下是$,linux下是!

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

68. Number的数据声明如下：

表示作用说明

Number(p, s) 声明一个定点数 p(precision)为精度，s(scale)表示小数点右边的数字个数，精度最大值为38，scale的取值范围为-84到127

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位向左最多可以有多少位数字，如果大于p则Oracle报错，小数点后s位向右的数字被舍入

69. oracle update 多表关联

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

查看SCN和timestamp之间的对应关系：

select scn,to_char(time_dp,‘yyyy-mm-dd hh24:mi:ss‘)from sys.smon_scn_time;

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

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

74. select name,value ,ISSYS_MODIFIABLE from v$parameter

如果ISSYS_MODIFIABLE 返回的是false，说明该参数无法用alter system语句动态修改,需要重启数据库

75. oracle子查询中能使用order by

from 子句后面的内联视图是可以使用order by子句进行排序的。

然而，其它视图或子查询是不能用order by进行排序的

如果你要用选择前几条的话，需要在套一层变成from后面的内联视图。

比如

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的区别

online时可以在该索引的基表上执行DML，在在对基表操作的同时可以REBUILD INDEX，但是不能执行DDL语句，所以他们的锁机制是不样的。

创建索引时通常会对该表设置一个表级共享(DML)锁,如果设置ONLINE ,

如果是非ONLINE方式，通常会对该表设置一个表级共享(DML)锁，那么就对DML语句冲突，如果设置ONLINE ,(会使用临时日志IOT表来记录中间改变的数据)，但要使用两倍于传统方法的空间.表会变成行级共享锁,在创建索引或者ALTER完成后,对临时日志表与基表进行MERGE

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

78. colb，字符串大对象，存的是长字符串数据

blob，二进制大对象，存的是二进制型，比如图像、音频数据

79. SQLPLUS 默认不是自动提交的.

自动提交命令：

SQL>set autocommit on

退出SQLPLUS 时会自动提交

指定DDL，如CREATE , ALTER, DROP ，会自动提交

执行DCL，如GRANT,REVOKE，会自动提交

80. 临时表空间不能脱机。system,和正在使用（有活动session或transaction）的也不能。

81. 查询正在执行的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()函数在计算一年中第几周是从该年的1月1日开始的。

83. 正在连接的用户不能删除，确实要删除的话，如下

1、select sid,serial#,username from v$session where user=‘USERNAME‘;

2、alter system kill session ‘sid,serial#‘;

3、drop user username cascade;

84. 在排除索引限制的条件下，如果索引还是没有被引用，可以检查下参数。

optimizer_index_cost_adj为100,该参数影响优化器选择索引还是全表扫描的倾向,将其修改为35.

85. 在Oracle中，要获得日期中的年份，例如把sysdate中的年份取出来，并不是一件难事。

常用的方法是：Select to_number(to_char(sysdate,‘‘yyyy‘‘)) from dual，

而实际上，oracle本身有更好的方法，那就是使用Extract函数，

使用方法是：Select Extract(year from sysdate) from dual，这种方法省掉了类型转换，看上去更加简洁。

相应的，要取得月份或日，可以用select extract (month from sysdate) from dual和select extract (day from sysdate) from dual。

此方法获得的结果，是数值型的，大家可以设置一个方法测试一下。

select EXTRACT(year FROM to_date(‘2009-11-10‘,‘yyyy-mm-dd‘)) year from dual;

86. 查询数据库默认的表空间类型：

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，这种类型的表空间只能有一个数据文件，且该数据文件允许有4G的数据快，即如果db_block_size=8k的话，最大容量为4G*8K=32T，当然，这个还要看操作系统的限制了。

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

SQL> alter database set default smallfile tablespace;

Database altered.

也可以在创建表空间时，指定表空间类型：create smallfile/bigfile tablespace ....

88. exp 失败执行的脚本：

Catexp.sql ：这个脚本是用于生成exp命令执行时所需要的一些表和视图，在执行exp命令出现找不到什么什么表，什么什么视图时使用。

Catmeta.sql ：这个脚本是在升级后执行exp命令出现错误时运行，说是因为升级不成功，运行这个脚本可以重新创建系统表。

89. 日期一般就用to_date(str,format)格式转换，像‘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语句保存到文件中，可是默认的存放路径是$ORACLE_HOME/bin，即sqlplus可执行文件存放的位置，当然有的人说在文件名前加绝对路径即可：save d:/oracle/admin/oradb/emp.sql

可是这种写法未免太麻烦，特别是使用get命令，也得使用绝对路径，因此如果能够修改save的默认位置，那么save/get就好写的多了。

91. 查询某一对象的类型，比如查询‘v$datafile‘是同义词还是视图？

select * from all_objects where object_name=upper(‘v$datafile‘)

92. http://download.csdn.net/source/1841831

我上传了一个包，可以获得汉字的拼音或者首字母，oracle汉字转拼音

93. Index ENABLE和DISABLE适用于FUNCTION-BASED INDEX

如果普通索引的话，你就用unusable 而不是disable

ENABLE和DISABLE只针对函数索引。

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参数，Dead Connection Detection 在服务器端使用。每当一个客户端的连接建立时，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参数指定这个文件就可以了

exp myname/mypass@mydb parfile=mypar.par

97. 分页一般用到两种办法：

1，利用rownum

2，分析函数row_number()over()

select from(

select t.*,rownum rn from(

select * from a

order by col1)t

where rn between 101 and 200)

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()是一个分析函数（Analytic Function），它返回的是基于over()参数的行号。

rownum是oracle特别提供的一个伪列，它只作用于查询的结果集，根据结果集输出的先后次序给每个纪录顺次编号。

row_number() 要比rownum 高很多. 对一大表测试时， row_number() 用时6s，rownum 用时 17s.

99. listener 主要是侦听从客户端发来的对数据库的连接请求。

如果你在服务器端用sqlplus 进行连接，监听没有启动也是可以连上的，但是从远程来访问数据库，或者用PL/SQL dev 或者TOAD等进行连接，就必须启动监听。

100. 这个命令可以查看建表的SQL语句..

select dbms_metadata.get_ddl(‘TABLE‘,‘&tname‘) from dual;

101. 查询视图可以通过

select * from all_views

索引:all_indexes, 索引和列的关系 all_ind_columns。table_name即索引所在的表

如果只想查询当前用户下的，将上面数据字典的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, 一个小触发器

create table t_temp

(

id varchar2(10) primary key,

len1 number(6,0),

len2 number(6,0),

len number(7,0)

)

当update某一行的len1或len2值后，则修改该行len的值(len = len1 + len2)，

或者insert 一条新的记录后，修改len = len1 + len2

哪位帮我用触发器实现上面的功能

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 不允许触发器修改它正在触发的表，故用before 可以，after不行..

105. udump下的trc文件可以通过配置不让产生,利用命令

alter system set sql_trace=false;

其他的不能修改，只能手动的启动trace，手动的关闭trace.

比如：

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. 重复数据只显示一条：

select min(id) id,b,c from tb group by b,c

107. 删除重复数据：

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)

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;

--错误后循环继续执行。

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 后面的东西生成一张内存中的临时表。然后进行查询。所以在相关字段上见索引比较重要。

110. 在Oracle中查看各个表、表空间占用空间的大小查看当前用户每个表占用空间的大小：

　　 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. 相关定义

ORACLE_SID：操作系统环境变量ORACLE_SID用于和操作系统交互。也就是说，在操作系统中要想得到实例名，就必须使用ORACLE_SID，在操作系统级别唯一识别oracle instance.

LD_LIBRARY_PATH ：你的系统用到oracle共享库存在于需要指定的路径。

ORACLE_TERM：是ORACLE在XWINDOW图形界面安装时要使用的变量,必须正确设置，否则安装程序无法在xwindow中启动。

ORACLE_OWNER ：对该文件具有访问特权的用户；通常是创建该文件的用户。

113. 修改系统时间格式：

alter session set nls_date_format=‘YYYY-MM-DD‘

修改默认的时间格式：

1、windows下，在注册表中 HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE下增加一字符串：NLS_DATE_FORMAT,把其值设成：YYYY-MM-DD

2、Unix下，在用户的.profile文件中增加以下内容：

NLS_DATE_FORMAT=YYYY-MM-DD

export NLS_DATE_FORMAT

Nls_lang 是Linux 系统的环境变量;

Nls_language 是数据库的参数。

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

export NLS_LANG="simplified chinese_china.zhs16gbk"

修改系统时间格式：

SQL>alter session set nls_date_format=‘yyyy-mm-dd hh24:mi:ss‘;

或者在系统 export NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS

或者在pro_file里面添加一个变量 nls_date_format，这样就不用每次都需要设置时间了。

114. ORACLE9.2.0.8 不支持，not in()中带union的SQL.

115. recover database using backup controlfile和recover database using backup controlfile until cancel

前者是利用backup controlfile完全恢复

后者是利用backup controlfile不完全恢复。

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，会恢复到3。

两个连用告诉Oracle恢复的时候恢复到最后一个可用的redo log file，不管控制文件中的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最大值

Oracle小知识总结

码农公寓

相关文章