Oracle小知识总结

1. 每天的8002300每隔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‘) 1440,

                  ‘trunc(SYSDATE, ‘‘mi‘‘) 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记录的SCNDBWR 触发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一定会小于CONTROLFILEDATAFILE SCN

如果你有进行RESTORE DATAFILE,则该RESTOREDATAFILE HEADER SCN一定会小于目前CONTROLFILEDATAFILE SCN,此时会无法开启数据库,必须进行media recovery。 重做archive log直到该datafile headerSCN=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 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(222次方) or 4M blocks 

11. Oracle利用现有的表创建一张新表,只要表结构相同

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

 

12. 循环插入数据

declare integer;

     begin

         for 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,可以通过这2dynamic view来确定:

v$locked_object,V$session

 

可以把该 session杀掉。

select sid,serial# from v$session where username =‘XXXX‘

把得到的sidserial#号替换到下面的语句中:

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 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来查看当前sessiontrace文件的文件名

如下

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# 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 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 else 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. 表中的数据如下图所示

                                    c

          PP41982             SO90029

          PP41982            SO90029

只取出字段bc不重复的字段,

 

select b,c from 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 WHERE ROWID IN( SELECT MAX(ROWID) FROM a);

已删除 行。

SQL> select from a;

NAME                         ID ORDER_TITLE

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

中国科学技术大学              科学

中国科学技术大学              科学

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函数为指定元素而截去的日期值。

其具体的语法格式如下:

TRUNCdate[,fmt]

其中:

date 一个日期值

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

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

TRUNCTO_DATE(24-Nov-1999 08:00 pm,dd-mon-yyyy hh:mi am)

=24-Nov-1999 12:00:00 am’

TRUNCTO_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函数极为类似,只是该函数不对指定小数前或后的部分做相应舍入选择处理,而统统截去。

其具体的语法格式如下

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, 查看磁盘物理读写情况:

SELECT NAME,phyrds, phywrts,readtim,writetim FROM v$filestat a, v$datafile WHERE a.FILE#=b.FILE#

ORDER BY readtim DESC; 

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

 

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

34. UnionUnion All的区别

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

unionunion 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.  PGAsort_area_size大小不够时,用到临时表空间。

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

 

 需要用序列来实现

 CREATE SEQUENCE sid

 INCREMENT BY 

START WITH 

MAXVALUE 99999999 

 

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

      sid.currval --取当前序列

from dual;

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

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

 如:

     ID    DD

          e

        null

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

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

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

 count(*)是扫描表的。

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

 还有一种写法是countROWID)这也是只扫描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 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.  Oracleorapwd 命令

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

windowsoracle默认的位置是db_1/database目录,默认的文件名是pwdSID.ora,对于别的文件名是不认的。linuxoracle默认的位置是$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(222次方) 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 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 中有613X$ 表,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 perfect world, with no contention -- parse cpu parse elapsed.

ratio 100%

in 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的交叉型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就会报错

       当一个数的小数部分的长度 时,Oracle就会舍入。

       s(scale)为负数时,Oracle就对小数点左边的s个数字进行舍入。

       s , p表示小数点后第s位向左最多可以有多少位数字,如果大于pOracle报错,小数点后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:002008-13:57:00,则当你通过as of timestamp查询08-05-30 13:52:0008-05-30 13:56:59这段时间点内的时间时,oracle都会将其匹配为scn:339988undo表空间中查找,也就说在这个时间内,不管你指定的时间点是什么,查询返回的都将是08-05-30 13:52:00这个时刻的数据。

 

查看SCNtimestamp之间的对应关系:

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

    where a.deptno in

    (

 select depton from (

    select b.deptno from dept b

    order by b.dname

    [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 rebuildalter 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,和正在使用(有活动sessiontransaction)的也不能。

81. 查询正在执行的sql

 select OSUSER, PROGRAM, USERNAME, SCHEMANAME, B.Cpu_Time, STATUS, B.SQL_TEXT  

  from V$SESSION  

  LEFT JOIN V$SQL 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. Oracleto_char()函数在计算一年中第几周是从该年的11日开始的。

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

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

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

 3drop user username cascade;

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

optimizer_index_cost_adj100,该参数影响优化器选择索引还是全表扫描的倾向,将其修改为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 dualselect 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 fromdual;

select from 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 ENABLEDISABLE适用于FUNCTION-BASED INDEX

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

ENABLEDISABLE只针对函数索引。

 

 ENABLE applies only to function-based index that has been disabled because 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 function-based index. This clause lets you disable the use of 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. 如何清除inactivesession

1.方法一

(1)UNIX的方法

Asql>select usename,sid,paddr,status

 from v$session

where usename=‘USERNAME‘

AND STATUS=‘INACTIVE‘;

Bsql>SELECT SPID FROM V$PROCESS WHERE ADDR=上一步查出的PADDR

C$KILL SPID

 

2WINDOWSnt/2000的方法

c:/>orakill SID SPID

 

,方法二

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 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()

 1.

select from(

   select t.*,rownum rn from(

     select from a

     order by col1)t

   where rn between 101 and 200)

 2.

   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()参数的行号。  

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

row_number() 要比rownum 高很多. 对一大表测试时, row_number() 用时6srownum 用时 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_columnstable_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某一行的len1len2值后,则修改该行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 library_cache|controlf|systemstate|processstate|file_hdrs|REDOHDR level 10‘;

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)

as

   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‘))

 

间隔                                                                            

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

00小时0分钟-24秒                                                              

row selected.

112. 相关定义 

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

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

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

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

113. 修改系统时间格式:

alter   session   set   nls_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  

   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()中带unionSQL.

115. recover database using backup controlfilerecover 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,丢失45,会恢复到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小知识总结

上一篇:华为认证eNSP环境布置:解决软件兼容问题,端口号冲突问题,附下载。(鸽了,但又没完全鸽嘻嘻嘻)


下一篇:Oracle表空间、段、区和块简述