oracle常用语句

一、查询语句

1、查询重复索引导致相关的表

问题：ORA-00001: unique constraint (SOA.SYS_C0031039) violated

SQL语句：select table_name from dba_constraints where constraint_name = 'SYS_C0031039';

2、查询表重复数据（联合主键）

SQL语句：select EMPID,ORGID from OM_EMPORG group by EMPID,ORGID having count(*) > 1

3、查询表中数据的插入时间（数据没有具体创建时间的字段）

SQL语句：select to_char(scn_to_timestamp(ORA_ROWSCN),'yyyy-mm-dd hh24:mi:ss') insert_time from yd_temp_sap_emp  where rownum <= '5'  

4、查询表的所有索引信息

SQL语句：select * from user_indexes where table_name=upper('YD_TEMP_POSITION');

5、查询表的结构信息

SQL语句：SELECT DBMS_METADATA.GET_DDL('TABLE','YD_TEMP_POSITION') FROM DUAL

6、查看当前登录的用户的表:

  select table_name from user_tables;

二、操作语句

1、修改表字段默认值

alter table OM_ORGANIZATION modify  LASTUPDATE DEFAULT sysdate

2、查询表索引

select* from all_indexes where table_name='YD_TEMP_EMP';

3、增加表索引

CREATE INDEX "SOA"."IDX01_YD_TEMP_MAJOR" ON "SOA"."YD_TEMP_EMP" ("MAJOR" ASC) NOLOGGING VISIBLE;

三、锁表相关

1、查询锁表超过1秒的进程

SELECT A.SID,A.SERIAL#,A.USERNAME,A.EVENT,A.WAIT_CLASS,machine, A.SECONDS_IN_WAIT,A.PREV_EXEC_START,b.LOCKED_MODE,C.OWNER,C.OBJECT_NAME,C.OBJECT_TYPE FROM V$SESSION A INNER JOIN V$LOCKED_OBJECT B ON A.SID=b.SESSION_ID INNER JOIN DBA_OBJECTS C ON B.OBJECT_ID=c.OBJECT_ID WHERE A.SECONDS_IN_WAIT>1

2、查询锁表的原因sql

 select l.session_id sid,  s.serial#,  l.locked_mode,  l.oracle_username,  s.user#,  l.os_user_name,  s.machine,  s.terminal,  s.logon_time, a.sql_text,  a.action  from v$sqlarea a, v$session s, v$locked_object l  where l.session_id = s.sid  and s.prev_sql_addr = a.address  order by sid, s.serial#;

3、删除锁表进程

 ALTER system KILL session 'SID,serial#';