Oracle 小知识点总结

1. Oracle利用现有的表创建一张新表，只要表结构相同 ­

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

­

2. 循环插入数据 ­

declare i integer; ­

     begin ­

         for i in 1..100000 loop ­

         insert into test values(i); ­

    end loop; ­

    commit; ­

  end; ­

­

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

  Select * From User_Role_Privs ­

  Select * From User_Sys_Privs ­

­

4. 看数据文件大小,单位是M ­

select  round(bytes/(1024*1024),0) total_space ­

from dba_data_files ­

­

select  sum(bytes/(1024*1024)) total_space ­

from dba_data_files ­

­

5 控制文件大小 ­

select   sum( block_size*file_size_blks )/1024/1024 ­

from v$controlfile ­

­

6.建立表空间 ­

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 ­

­

7. select tablespace_name, file_name, sum(bytes)/1024/1024 table_size from dba_data_files group by tablespace_name,file_name; ­

­

8. 有没有被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#' ­

­

9. 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; ­

­

­

10. 查看表上是否存在的索引 ­

        select   *   from   user_indexes   where   table_name   =   'yourtablename' ­

        create index IX_Tablename_column on tablename(column) ­

­

11.   select id, id2, round((id/id2)*100,2) || '%' percent from test; ­

­

12.   查询表的行数­

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

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

­

13. 用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 ­

­

­

14. select case when(a>b) then a else b end from TableA; ­

    select greatest(a,b) from tb ­

­

15. TRUNCATE TABLE  Table_name ­

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

­

16. select substrb('大小abc',1,6) from dual; ­

17. 表中的数据如下图所示 ­

a                      b                c ­

1           PP41982             SO90029 ­

2           PP41982            SO90029 ­

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

­

select b,c from t group by b,c having(count(b) <2)­

­

17. 查询锁的情况­

  1). insert into test values(1);­

  2). select userenv('sid') from dual;­

  3). select * from v$lock where sid=''­

sid 在v$session 中有这个列，可以结合v$session 查询更多的信息­

18.  删除重复行：­

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

已删除 1 行。­

SQL> select * from a;­

NAME                         ID ORDER_TITLE­

-------------------- ---------- --------------------­

中国科学技术大学              1 科学­

中国科学技术大学              1 科学­

SQL> ­

­

19. 查看索引信息­

­

  --查看索引名称­

SELECT * FROM USER_INDEXES;­

-- 查看索引列名­

SELECT * FROM DBA_IND_COLUMNS;­

SELECT * FROM USER_IND_COLUMNS;­

SELECT * FROM ALL_IND_COLUMNS;­

­

20.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记。­

­

21. local是局部有序，整体无序，global是有序的，所以local可能会比global慢，得看你的sql语句怎么写的，需求是什么样的­

global索引->自己想怎么玩就怎么玩­

local索引->表怎么玩它就怎么玩­

 

22， 查看磁盘物理读写情况：

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

 

23. 从表中筛选出所有能被5整除的value值数据
 
select * from table where mod(datavalue,5) = 0

24.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;

25.查看看到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的执行结果拷贝出来执行即可

26.  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语句的执行计划。

 

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

28.更新表被锁，KILL spid后，select * from tabname for update 独占资源。
      v$locked_object  dba_objects 联合可以知道锁表的session

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

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

需要用序列来实现

CREATESEQUENCE sid
INCREMENT BY 1
START WITH 1
MAXVALUE 99999999

select sid.nextval, --取下一个序列
      sid.currval --取当前序列
fromdual;
~IMP/EXP 的buffer达到1M以后，性能的提升并不大，5M或者10M的足够用

如果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的，效率高。
~linux 挂在windows 共享的盘
 
1. 启动nfs服务:   service nfs start
2.mount -o username=user,password=123456 //10.85.2.194/share /mnt
~

 

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

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

~truncate 只是删除了表中的记录，并不会改变表的结构及依赖约束

所以truncate表后表的索引依然存在,但是表和索引所占用的空间会恢复到初始大小

 

这是看高速缓存命中率小于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;

 

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#||''';'

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

 

sqlplus '/ as sysdba' <<eof
{
shutdown immediate;
startup force dba pfile=$ORACLE_HOME/dbs/init.ora;
shutdown immediate;
}
exit;
eof

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

 

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

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

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

 

约束名从表user_constraints表中找
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME='FJ5W_FZ_JMD_S'

 

Oracle确实没有convert函数，只有to_char() 和 to_date()...

sql: CONVERT(VARCHAR(10),GETDATE(),111)
oracle: to_char(sysdate,'yyyy/mm/dd')

 

V$视图 是由catalog.sql 脚本创建的..

所以升级系统后要执行这个脚本..

 

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

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

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

你的数据库是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的话，即是。

order by t.tm_error desc

必须是 group by里的字符，或者是统计字段。

 

大量更新表时：

1.关掉tableb 的所有触发器，这个一定要关掉，moving data的时候一定要全部关掉，不然批量操作的时候卡死你Y的。
  alter system tableb disable all triggers;
  执行完毕之后，启动触发器
  alter system tableb enable all triggers;

2，除了主键索引之外，tableb表剩余的索引全部删除掉。等执行完毕之后，重建索引(索引重建很快，我的800万数据的表的6个索引重建才花了2分钟而已)

 

访问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';

 

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

SQL> SET LONG 2000000
SQL>select text from dba_views where viewname='DBA_IND_PARTITIONS';

 

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

 

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

把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 时候能够成功

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

Oracle9i 官方下载地址
http://www.oracle.com/technology/global/cn/software/products/oracle9i/index.html

10G和11G的
http://www.oracle.com/technology/software/products/database/index.html