oracle常用查询

oracle常用查询

oracle常用经典sql查询

 

1、删除重复记录

delete from wr_recordformtable a

where rowid!=

(select max(rowid)

from wr_recordformtable t

where

t.pid=a.pid and t.tablename=a.tablename

)

2、获得视图执行的时间，精确获得时间

create table  test_view (viewname varchar2(32),d integer,cn integer);

create or replace procedure ppp is

  cursor c1 is

    select object_name

      from user_objects o

     where o.object_type = 'VIEW' and o.status = 'VALID';

  sqlstr varchar2(1000);

  d1     timestamp;

  d2     timestamp;

  d      integer;

  num    integer;

  dstr   varchar2(100);

 

begin

  delete from test_view;

  commit;

  for rr in c1 loop

    begin

      d1     := systimestamp;

      sqlstr := 'select count(*) from ' || rr.object_name;

      execute immediate sqlstr

        into num;

      d2 := systimestamp;

   

      dstr := to_char((d2 - d1), 'hh24:mi:ssx');

      dstr := substr(dstr, 9, length(dstr) - 9);

      d    := to_number(substr(dstr, 1, 2)) * 3600 * 1000 +

              to_number(substr(dstr, 4, 2)) * 60 * 1000 +

              to_number(substr(dstr, 7, 2)) * 1000 +

              to_number(substr(dstr, 10, 3));

      dbms_output.put_line(dstr || '--' || d);

   

      insert into test_view values (rr.object_name, d, num);

      commit;

    exception

      when others then

        dbms_output.put_line(sqlstr || ':' || sqlcode || ':' || sqlerrm);

     

    end;

 

  end loop;

3、返回rownum在 4-10之间的数据

 select rownum,month,sell from sale where rownum<10

 minus

  select rownum,month,sell from sale where rownum<5;

 

4、查询当前用户下所有表的记录数

select 'select '''||tname||''',count(*) from '||tname||';' from tab where tabtype='TABLE';

5、快速编译所有视图

----当在把数据库倒入到新的服务器上后(数据库重建)，需要将视图重新编译一遍，

----因为该表空间视图到其它表空间的表的连接会出现问题，可以利用PL/SQL的语言特性，快速编译。

 

SQL> SPOOL ON.SQL

SQL> SELECT'ALTER VIEW '||TNAME||' COMPILE;' FROM TAB;

SQL> SPOOL OFF

然后执行ON.SQL即可。

SQL> @ON.SQL

 

6、 如何删除表中的列？

alter table 表1 drop column 列1;

7、查询primary key和forgen key的关系表

select

a.owner 外键拥有者,

a.table_name 外键表,

c.column_name 外键列,

b.owner 主键拥有者,

b.table_name 主键表,

d.column_name 主键列

from

user_constraints a,

user_constraints b,

user_cons_columns c,

user_cons_columns d

where

    a.r_constraint_name=b.constraint_name

and a.constraint_type='R'

and b.constraint_type='P'

and a.r_owner=b.owner

and a.constraint_name=c.constraint_name

and b.constraint_name=d.constraint_name

and a.owner=c.owner

and a.table_name=c.table_name

and b.owner=d.owner

and b.table_name=d.table_name

/     

8、查询重复记录:

法一: 用Group by语句 此查找很快的

select count(num), max(name) from student --查找表中num列重复的，列出重复的记录数，并列出他的name属性

group by num

having count(num) >1 --按num分组后找出表中num列重复，即出现次数大于一次

 

delete from student(上面Select的)

这样的话就把所有重复的都删除了。-----慎重

 

法二:当表比较大(例如10万条以上)时,这个方法的效率之差令人无法忍受,需要另想办法:

---- 执行下面SQL语句后就可以显示所有DRAWING和DSNO相同且重复的记录

SELECT * FROM EM5_PIPE_divFAB

WHERE ROWID!=(SELECT MAX(ROWID) FROM EM5_PIPE_divFAB D --D相当于First,Second

WHERE EM5_PIPE_divFAB.DRAWING=D.DRAWING AND

EM5_PIPE_divFAB.DSNO=D.DSNO);

 

9、Oracle中如何实现某一字段自动增加1？

 

 

实现方法：

建立一个最小为1,最大为999999999的一个序列号会自动循环的序列

 

create sequence 序列名

increment by 1

start with 1

maxvalue 999999999

cycle;

 

当向表中插入数据时，SQL语句写法如下：

 

SQL> insert into 表名 values(序列名.nextval,列1值,列2值);

 

10、如何查看什么时间有哪些数据库对象结构被修改过？

 SELECT OBJECT_NAME,　　　　　　　　　　　　　　　　　　　　　　　　--对象名

　　　　　　OBJECT_TYPE,　　　　　　　　　　　　　　　　　　　　　　　　--对象类型

　　　　　　TO_CHAR(CREATED,       'YYYY-Mon-DD HH24:MI') CREATE_TIME,　--创建时间

　　　　　　TO_CHAR(LAST_DDL_TIME, 'YYYY-Mon-DD HH24:MI') MOD_TIME,　　 --修改时间

　　　　　　TIMESTAMP,　　　　　　　　　　　　　　　　　　　　　　　　　--时间戳

　　　　　　STATUS　　　　　　　　　　　　　　　　　　　　　　　　　　　--状态

 　　FROM   USER_OBJECTS

　 　WHERE  to_char(LAST_DDL_TIME,'yyyymmdd')>'20020101';

 11、获得一个表占的空间

 

 

 
select s.*,bytes/1024/1024 from user_segments s
一般blob占的空间比较大,通过如下表，可以获得LOBSEGMENT是哪个表的那个字段

select * from user_lobs

来源：http://shylhd.iteye.com/blog/445709