總結幾個ORACLE資料庫日常運維常用的命令（持續更新）

1、重建DBLINK不知道使用者密碼，不用知道密碼，密文在SYS.LINK$存著呢，以sysdba的許可權才能取出來

 

然後透過SQL建立DBLINK

 

CREATE DATABASE LINK "DBLINK_wwwwwwwww"

   CONNECT TO "username_zzzzzzzzzzz" IDENTIFIED BY VALUES '05994DC6A7D4DCD81243536F3A00B85032'  --------密文

   USING '(DESCRIPTION =

   (ADDRESS_LIST =

     (ADDRESS = (PROTOCOL = TCP)(HOST = xxxxx)(PORT = 1521))

  )

  (CONNECT_DATA =

    (SERVICE_NAME = yyyyyy)

   )

)'

 

注意：資料庫使用者遷移後，job要重新定義建立 前面需加 declare  job number。。。。，儲存過程要重新編譯，DBLINK要重新建立

 

2、oracle密碼失效解決辦法：

登入成功以後查詢DBA使用者狀態 
SQL->select username,account_status from dba_users; 
檢視其中常用的使用者狀態是否是EXPIRED（失效） 還是LOCKED 

如果sysman狀態是過期，修改密碼方法：

語句查詢密碼的有效期設定，

SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';

LIMIT欄位是密碼有效天數。在密碼將要過期或已經過期時可透過

ALTER USER 使用者名稱 IDENTIFIED BY 密碼 ;

語句進行修改密碼，密碼修改後該使用者可正常連線資料庫。

長久對應可透過

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED

語句將口令有效期預設值180天修改成“無限制”。出於資料庫安全性考慮，不建議將PASSWORD_LIFE_TIME值設定成UNLIMITED，

再試下SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';  看密碼有效期是否為unlimit

 

3、生成AWR報告

Sqlplus / as sysdba

@?/rdbms/admin/awrrpt.sql

預設html回車、檢視的天數選1天、選擇開始和結束時間點用Snap Id、選擇名字預設回車、生成至oracle家目錄

 

4、檢視作業系統使用CPU程對應的正在執行的SQL

SELECT   /*+ ORDERED */

         sql_text

    FROM v$sqltext a

   WHERE (a.hash_value, a.address) IN (

            SELECT DECODE (sql_hash_value,

                           0, prev_hash_value,

                           sql_hash_value

                          ),

                   DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)

              FROM v$session b

             WHERE b.paddr = (SELECT addr

                                FROM v$process c

                               WHERE c.spid = '&pid'))

ORDER BY piece ASC

/

 

寫入PID

 

5、根據SQL ID查詢SQL中繫結變數傳參值

select name,t.VALUE_STRING from v$sql_bind_capture t where t.SQL_ID=''

 

6、檢視oracle字符集

select userenv('language') from dual

 

7、查詢一個表的主鍵被哪些表欄位的外來鍵引用，外來鍵約束（被引用的值主表不能刪除，附表新增的值必須在主表中存在，附表值可以隨意刪除）

先查詢主鍵約束名：

select * from user_constraints a  where  a.table_name = '表名'

然後

select b.table_name,b.column_name from user_constraints a  inner join user_cons_columns b on a.constraint_name = b.constraint_name where a.r_constraint_name='主鍵約束名'

 

8、批次殺死非活躍會話

select

'Alter system kill session ''' || se.sid ||',' || se.serial# || ''';',

sid,

serial#

--select *

from v$session se WHERE se.status in ('INACTIVE','KILLED') and se.USERNAME='TSM'

複製出來執行即可，如果利用上面的命令殺死一個程式後，Oracle程式狀態被置為 "killed", 但是鎖定的資源很長時間沒有被釋放，那麼可以在os一級再殺死相應的程式（執行緒），首先獲得程式（執行緒）號（注意要排除掉主機名是本身的程式，以免殺死oracle的後臺程式）：

select

'kill -9 '|| p.SPID ||''

 from v$session s, v$process p

 where s.paddr = p.addr and s.STATUS in ('INACTIVE','KILLED') and s.MACHINE not in ('CNBPMD101')

 

9、檢視鎖程式並查殺

select

 

'alter system kill session '''|| sess.sid ||''||','|| sess.serial# ||''';',

 

sess.sid,

 

    sess.serial#,

 

    lo.oracle_username,

 

    lo.os_user_name,

 

    ao.object_name,

 

    lo.locked_mode

 

    from v$locked_object lo,

 

    dba_objects ao,

 

    v$session sess

 

where ao.object_id = lo.object_id and lo.session_id = sess.sid;

 

10、檢視錶空間使用率

 

SELECT total.tablespace_name,

 

       Round(total.MB, 2)           AS Total_MB,

 

       Round(total.MB - free.MB, 2) AS Used_MB,

 

       Round(( 1 - free.MB / total.MB ) * 100, 2)

 

       || '%'                       AS Used_Pct

 

FROM   (SELECT tablespace_name,

 

               Sum(bytes) / 1024 / 1024 AS MB

 

        FROM   dba_free_space

 

        GROUP  BY tablespace_name) free,

 

       (SELECT tablespace_name,

 

               Sum(bytes) / 1024 / 1024 AS MB

 

        FROM   dba_data_files

 

        GROUP  BY tablespace_name) total

 

WHERE  free.tablespace_name = total.tablespace_name;

 

11、閃回表

先在閃回區檢視歷史時刻表資料

select * from t_cas_feetype AS OF TIMESTAMP to_timestamp('20150322 00:00:00','yyyymmdd hh24:mi:ss');

利用flashback table恢復表到過去某一時刻

alter table tab_test enable row movement;

flashback table tab_test to timestamp ('20140917 10:00:00','yyyymmdd hh24:mi:ss');

alter table tab_test disable row movement;

檢視過去某一段時間內對錶的操作，以確認需要恢復到的時間點

select SQL_TEXT,LAST_ACTIVE_TIME from v$sqlarea where LAST_ACTIVE_TIME >to_date('20140917 10:00:00','yyyymmdd hh24:mi:ss') and SQL_TEXT like '%tab_test%';

確認是否開啟資料庫閃回

select log_mode,flashback_on from v$database;

12、檢視錶大小排序

select table_name,blocks*8/1024

from user_tables

where owner not like '%SYS%' and table_name not like '%$%'

order by blocks desc;

 

14、Oracle中查找阻塞與被阻塞SID的方法

在Oracle中,會經常遇到阻塞與被阻塞的情況.

查詢阻塞與被阻塞的方法主要有下面幾種:
一.透過查詢v$lock和v$locked_object
這是最常用的也是最直接的方法

SQL> select sid,block from v$lock where block=1;

SID BLOCK
---------- ----------
252 1

SQL>
SQL> select object_id,session_id from v$locked_object where object_id in (select object_id from v$locked_object where session_id=252);

OBJECT_ID SESSION_ID
---------- ----------
63833 252
63833 269

SQL>

由上面第一條語句可以看出,SID=252的session阻塞了其他的session
由第二條語句可以得出,SID=252的session阻塞了SID=269的session

二.透過查詢dba_waiters和dba_blockers
SQL> select * from dba_blockers;

HOLDING_SESSION
---------------
252

SQL>
SQL>
SQL> select waiting_session,holding_session from dba_waiters;

WAITING_SESSION HOLDING_SESSION
--------------- ---------------
269 252

從dba_blockers檢視中,可以看到,SID=252的session阻塞了別的session,而從dba_waiters可以看到,HOLDING_SESSION為252,等待的WAITING_SESSION為269.

三.在Oracle 10G中可以透過v$session中的blocking_session欄位查詢
SQL> select sid,blocking_session from v$session where blocking_session is not null;

SID BLOCKING_SESSION
---------- ----------------
269 252

 

15、檢視當前活躍連線數正在執行的SQL

select s.sql_id,s.sql_text from v$sql s,gv$session g

where s.SQL_ID=g.SQL_ID and g.USERNAME='ECOLOGY'

and g.status='ACTIVE'

都是平時運維經常需要用到的SQL，後面再總結會持續更新進去