oracle小技巧

1、oracle分页

      1）select * from (select a.*, rownum rn from (select * from tabel order by XX) a where rownum<5) where rn>2;

             注：在oracle中用rownum来记录数据的顺序，这个语句的意思是，按某个字段排序并查找出表中所有数据，根据数据的顺

                    序，先找出数据排序号小于5的数据，然后再找到大于2的数据。

        2）select * from emp where rowid in (select rid from (select rownum rn,rid from (select rowid rid from table order by XX) a 

              where  rownum<=5)  where rn>=2) 

             注：这里是根据rowid来查找数据，由于这个语句子查询查找的字段很少，故有人认为他的速度比较快。但这里有一个小问

                    题，就是当这个语 句和上面的语句按相同列排序，两条sql查出的数据并不相同，具体什么原因我也不清楚，请知道的

                    大神告知一下。

2、delete和truncate的区别

         delete是DML（数据操作语言），该操作会放在rollback segement中，提交了事物才生效，如果用相应的trigger，执行时将

     会被 触发。truncate是DDL（数据定义语言），该操作执行时立即生效，原数据并没放在rollback segement中，没有回滚一说，

     也不 触发trigger。

     注：当批量删除数据时，建议用truncate，速度较快，但也存在一定的安全隐患

3、oracel表解锁

       1）找到被锁定表的session

            SELECT object_name, machine, s.sid, s.serial# 

                   FROM gv$locked_object l, dba_objects o, gv$session s 

               WHERE l.object_id= o.object_id 

                     AND l.session_id = s.sid; 

        2）释放session，进行解锁

            ALTER system kill session 'XX, XXXXX'; 

4、表空间

   1）作用：控制数据库所占的磁盘空间；分类管理，提供效率；备份和恢复数据

     2）创建表空间

           create tablespace 表空间名 datafile ‘D:\xxx.dbf’ size 20m;

           注：xxx.dbf为数据文件名，size最大值为500m

     3）扩大表空间

           a、添加数据文件：alert tablespace 表空间名 add datafile ‘D:\xxx2.dbf’ size 20m;

           b、增加数据文件大小：alert tablespace 表空间名 ‘D:\xxx .dbf’ resize 20m;

            注：当你不知道对应的数据文件在哪个位置，可通过一下SQL查询出对应的位置：

            select f.* from dba_data_files f where f.tablespace_name='表空间名'

           c、设置文件自动增长：alert tablespace 表空间名 ‘D:\xxx.dbf’autoextend on next 10m  maxsize  500m;

     4）改变表空间状态

           联机：alert tablespace 表空间名 online

           脱机：alert tablespace 表空间名 offline

          只读：alert  tablespace 表空间名 read only

          读写：alert  tablespace 表空间名 read write

      5）移动表空间数据文件

            a、确定数据文件的表空间： select tablespace_name from dba_data_files where file_name=’d:\XXX.dbf’;

            b、使表空间脱机： alert  tablespace 表空间名 offline

            c、移动数据文件到制定位置：host move ‘d:\xxx.dbf’ ‘c:\xxx.dbf’

            d、逻辑修改数据文件：alert  tablespace 表空间名 rename datafile ‘d:\xxx.dbf’ to ‘c:\xxx.dbf’

            e、使表空间联机：alert tablespace 表空间名 online 

5、查找某个字段在数据库中的哪些表出现

              select  *  from user_tab_columns uc,user_tables ut

         where ut.table_name=uc.TABLE_NAME 

            and column_name =upper('BOX_NO') ;

6、数据库字符串分割

             1）定义返回类型

                   CREATE OR REPLACE TYPE split_type IS TABLE OF VARCHAR2 (4000);

             2）实现分割方法

                  CREATE OR REPLACE FUNCTION split (

                             p_str       IN VARCHAR2,

                             p_delimiter IN VARCHAR2 default(','))

                             RETURN split_type

                  IS

                             j INT := 0;

                             i INT := 1;

                             len INT := 0;

                             len1 INT := 0;

                             str VARCHAR2 (4000);

                             my_split split_type := split_type ();

                  BEGIN

                           len := LENGTH (p_str);

                           len1 := LENGTH (p_delimiter);

                   

                          WHILE j < len  LOOP

                                 j := INSTR (p_str, p_delimiter, i);

                      

                                IF j = 0  THEN

                                    j := len;

                                   str := SUBSTR (p_str, i);

                                  my_split.EXTEND;

                                  my_split (my_split.COUNT) := str; 

                                  IF i >= len THEN

                                        EXIT;

                                  END IF;

                             ELSE

                                   str := SUBSTR (p_str, i, j - i);

                                   i := j + len1;

                                   my_split.EXTEND;

                                   my_split (my_split.COUNT) := str;

                             END IF;

                      END LOOP;

                      RETURN my_split;

                  END split;

           3）循环获取分割内容

                 for m in ( select * from table(split(strLabelLNoList,'|')) ) loop

                       .......

                 end loop;