Oracle常用語句

標籤：des   style   io   color   ar   os   for   sp   檔案   

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;

 

Oracle常用語句