oracle日常運維
釋出時間：2019-02-25 21:41,
浏覽次數：358
, 标簽：
oracle
1.解鎖使用者 select username,account_status from dba_users b where
b.username=upper(''); alter user account unlock; --解鎖
alter user identified by sh;--解開EXPIRED alter user
identified by '&pw'; 2.授權 alter user grant connect through
; 3.create package 報錯，追蹤sql ORA-04021:等待鎖定對象時發生逾時 select
object_name,s.sid,s.serial#,p.spid from v$locked_object l, dba_objects o ,
v$session s , v$process p where l.object_id=o.object_id and l.session_id=s.sid
and s.paddr=p.addr; ddl鎖？？？？？？？？？？？？？？？ 最先持有這個鎖的人？？？？？？？ 擷取spid > ps -ef|grep
18399 背景kill可能會down > kill 18399 4. SQL卡死，追蹤sql select
q.SQL_TEXT,q.SQL_ID,s.SID,s.SERIAL# from v$session s ,v$locked_object o,v$sql q
where o.SESSION_ID=s.SID and s.SQL_ID=q.SQL_ID and q.SQL_ID='3pa2v9fvthfc5' ;
select
q.SQL_TEXT,q.SQL_ID,s.SID,s.SERIAL#,do.object_name,s.USERNAME,s.SQL_EXEC_START
from v$session s ,v$locked_object o,v$sql q,dba_objects do where
o.SESSION_ID=s.SID and s.SQL_ID=q.SQL_ID and do.object_id=o.OBJECT_ID; alter
system kill session 'sid.serial#'; 5. 資料庫連接配接問題 域----vip----主機 lsnrctl 執行個體名
nslookup 域名 show parameter local srvctl (RAC) 監聽是否打開： lsnrctl status 執行個體名
lsnrctl start 執行個體名 資料庫是否啟動： ps -ef|grep pmon 若沒有，startup 6.檢視剩餘表空間的大小： 普通表空間
SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 AS "FREE SPACE(M)" FROM
DBA_FREE_SPACE WHERE TABLESPACE_NAME = 'USERS' GROUP BY TABLESPACE_NAME;
檢視臨時表空間使用量/空餘量： select TABLESPACE_NAME, BYTES_USED/1024/1024
used_MB,BYTES_FREE/1024/1024 free_MB from V$TEMP_SPACE_HEADER; 7.檢視是哪個磁盤組:
普通表空間：select * from dba_data_files where tablespace_name='';
添加表空間： select 'create tablespace ' ||TABLESPACE_NAME|| ' datafile ''''
size 500m autoextend on;' from dba_tablespaces; 8.如果是asm管理檢視磁盤剩餘空間 select
name,free_mb/1024/1024,total_mb from v$asm_diskgroup_stat where
name=''; select * from v$asm_diskgroup; 9.如果磁盤組夠用，增加資料檔案 ALTER
TABLESPACE &tablespace_name ADD DATAFILE '' SIZE 500m AUTOEXTEND ON ;
alter tablespace temp add tempfile '' size 500m autoextend on ; 10.
如果是檔案系統，則使用下面的語句： ALTER TABLESPACE &tablespace_name ADD DATAFILE
'&datafile_name' size 100m autoextend on next 100m MAXSIZE 10000M; 11.檢視復原時間
select usn, state, undoblocksdone, undoblockstotal, CPUTIME, pid,xid,
rcvservers from v$fast_start_transactions; 12. 檢視連接配接數 select count(*) from
v$session; 13.抓10046 SQL> oradebug setospid 5489 Oracle pid: 56, Unix process
pid: 5489, image: [email protected]主機名 (TNS V1-V3) SQL> oradebug event 10046 trace name
context forever , level 12 Statement processed. SQL> oradebug event 10046 trace
name context off; Statement processed. SQL> oradebug tracefile_name
&trace_dir/paolap_ora_5489.trc SQL> host; 14. 重新開機服務名，檢視活動的服務名 select name from
dba_services; exec dbms_service.start_service(''); 15.awr報告
SELECT * FROM dba_hist_snapshot t ORDER BY t.begin_interval_time DESC; SELECT
OUTPUT FROM
TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(704877203,1,17568,17570)); 16.
編譯packagepackage select * from v$access m where m.TYPE='PACKAGE' and
m.OBJECT='';