oracle日常运维

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>';