Oracle常用语句

1、查看某个字段在哪张表

select owner, table_name

 from dba_tab_columns

where lower(column_name)=‘字段名‘;

2、导出序列

SELECT ‘ create sequence username.‘ || SEQUENCE_NAME ||   
       ‘ minvalue ‘ || MIN_VALUE ||   
      ‘ maxvalue ‘ || MAX_VALUE ||   
      ‘ start with ‘ || LAST_NUMBER ||   
      ‘ increment by ‘ || INCREMENT_BY ||   
      ‘ cache ‘ || CACHE_SIZE || ‘ ;‘  
 FROM USER_SEQUENCES;

3、批量增加序列

declare

  VC_SQL   varchar2(200);

  N_NUM NUMBER;

begin

  FOR I IN 1 .. 100 LOOP

    for a in (select * from user_sequences t where t.sequence_name like ‘SEQ%‘ ) loop

      VC_SQL := ‘select ‘ || a.sequence_name || ‘.NEXTVAL from dual ‘;

      execute immediate VC_SQL

        INTO N_NUM;

    end loop;

  END LOOP ;

  end;

4、修改数据库用户密码有效期

数据库用户密码默认有效期是180天，所以差不多半年左右会出现，为了防止以后再出现，

建议把密码设为永久性的，步骤如下：

cmd   sqlplu / as  sysdba

(1)查看用户的proifle是哪个，一般是default： sql>SELECT username,PROFILE FROM dba_users; 

(2)查看指定概要文件（如default）的密码有效期设置： 

sql>SELECT * FROM dba_profiles s WHERE s.profile=‘DEFAULT‘ AND resource_name=‘PASSWORD_LIFE_TIME‘; 

(3)将密码有效期由默认的180天修改成“无限制”： 

sql>ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; 

修改之后不需要重启动数据库，会立即生效。

?5、创建dblink

create database link linkname
　　connect to linknameidentified by pwd
　　using ’(DESCRIPTION = 
　　(ADDRESS_LIST = 
　　(ADDRESS = (PROTOCOL = TCP)(HOST = ipaddress)(PORT = 1521)) 
　　) 
　　(CONNECT_DATA = 
　　(SERVICE_NAME = orcl) 
　　) 
　　)’;

6、oracle定时器（Job）各时间段写法汇总

1)、 每分钟执行

Interval => TRUNC(sysdate,‘mi‘) + 1 / (24*60) 2、 每天定时执行

例如：每天的凌晨2点执行

Interval => TRUNC(sysdate) + 1 +2 / (24)

2)、 每半个小时执行

sysdate+30/1440

3)、 每个小时的第15分钟执行

例如：8:15，9:15，10:15...：

trunc(sysdate,‘hh‘)+75/1440。

4)、 每周定时执行

例如：每周一凌晨2点执行

Interval => TRUNC(next_day(sysdate,2))+2/24 --星期一,一周的第二天

5)、 每个星期日执行

next_day(trunc(sysdate),‘SUNDAY‘)

6)、 每月定时执行

例如：每月1日凌晨2点执行

Interval =>TRUNC(LAST_DAY(SYSDATE))+1+2/24

7)、 每季度定时执行

例如每季度的第一天凌晨2点执行

Interval => TRUNC(ADD_MONTHS(SYSDATE,3),‘Q‘) + 2/24

8)、 每半年定时执行

例如：每年7月1日和1月1日凌晨2点

Interval => ADD_MONTHS(trunc(sysdate,‘yyyy‘),6)+2/24

9)、 每年定时执行

例如：每年1月1日凌晨2点执行

Interval =>ADD_MONTHS(trunc(sysdate,‘yyyy‘),12)+2/24

10)、 每天下午20点执行

trunc(SYSDATE,‘dd‘) + 20/24或trunc(sysdate+1)+ 20/24

11)、 每周五下午 11:10:20执行

trunc(next_day(sysdate,‘星期五‘))+23/24+10/1440+20/86400

12)、 每月15日下午 11:10:20执行

last_day(add_months(TRUNC(SYSDATE,‘MM‘),-1))+15 +23/24+10/1440+20/86400

13)、 每年6月5日下午 11:10:20执行

trunc(to_date(to_char(sysdate,‘yyyy‘)+1||‘0605‘,‘yyyy-mm-dd‘))+23/24+10/1440+20/86400 

7、查看连接数

select * from gv$license;

8、死锁查询

/*查找死锁*/ SELECT /*+ rule */ s.SID||‘,‘||s.SERIAL#,s.username,s.client_info, decode(l.type,‘TM‘,‘TABLE LOCK‘,‘TX‘,‘ROW LOCK‘,NULL) LOCK_LEVEL, o.owner,o.object_name,o.object_type, s.terminal,s.machine,s.program,s.osuser,a.SQL_TEXT FROM v$session s,v$lock l,dba_objects o,v$sqlarea a WHERE l.sid = s.sid AND l.id1 = o.object_id(+) and s.PREV_SQL_ADDR = a.ADDRESS AND s.username is NOT NULL order by s.CLIENT_INFO

 找到谁持有锁不释放 SELECT /*+ rule */  s.username,  decode(l.type, ‘TM‘, ‘TABLE LOCK‘,‘TX‘, ‘ROW LOCK‘,NULL) LOCK_LEVEL,　　  o.object_name,　　  s.sid,  l.block　  FROM v$session s,v$lock l,dba_objects o　　  WHERE l.sid = s.sid　　  AND l.id1 = o.object_id(+)　　  AND s.username is NOT NULL order by s.username; 如果block列为1，说明对应的sid会话正持有object_name锁，其它和object_name有关的锁在等待sid提交或回滚。

/*杀死锁*/ alter system disconnect session ‘935,28622‘ immediate;