oracle日常运维 - 好文

1.解锁用户 select username,account_status from dba_users b where
b.username=upper('<username>'); alter user <username> account unlock; --解锁
alter user <username> identified by sh;--解开EXPIRED alter user <username>
identified by '&pw'; 2.授权 alter user <username1> grant connect through
<username2>; 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='<tablespace_name>';
添加表空间： select 'create tablespace ' ||TABLESPACE_NAME|| ' datafile ''<DATA_DG>''
size 500m autoextend on;' from dba_tablespaces; 8.如果是asm管理查看磁盘剩余空间 select
name,free_mb/1024/1024,total_mb from v$asm_diskgroup_stat where
name='<DATA_DG>'; select * from v$asm_diskgroup; 9.如果磁盘组够用，增加数据文件 ALTER
TABLESPACE &tablespace_name ADD DATAFILE '<DATA_DG>' SIZE 500m AUTOEXTEND ON ;
alter tablespace temp add tempfile '<DATA_DG>' 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: oracle@主机名 (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('<dba_services_name>'); 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='<package_name>';

热门工具换一换