Oracle日常监控及日常运维常用SQL大全

----------------------性能监控语句--------------
1、查看前一天所有表空间的增长量
select C.tablespace_name,
D.“Total(MB)”,
D.“Used(MB)” - C.“Used(MB)” AS “Increment(MB)”,
to_char(trunc(sysdate - 30),‘yyyy/mm/dd’) “TIME”
from (select B.name tablespace_name,
case when B.name not like ‘UNDO%’ then round(A.tablespace_size * 8 / 1024)
when B.name like ‘UNDO%’ then round(A.tablespace_size * 8 / 1024 / 2)
END as “Total(MB)”,
round(A.tablespace_usedsize*8 / 1024) “Used(MB)”,
A.rtime
from DBA_HIST_TBSPC_SPACE_USAGE A, vKaTeX parse error: Expected 'EOF', got '#' at position 51: …space_id = B.TS#̲ and…tablespace B
where A.tablespace_id = B.TS#
and to_char(to_date(replace(rtime, ‘/’, null),
‘mmddyyyy hh24:mi:ss’),
‘yyyymmdd hh24:mi’) =
to_char(trunc(sysdate), ‘yyyymmdd hh24:mi’)) D
where C.tablespace_name = D.tablespace_name;

2、-每日增量统计
with tmp as
(select rtime,
sum(tablespace_usedsize_kb) tablespace_usedsize_kb,
sum(tablespace_size_kb) tablespace_size_kb
from (select rtime,
e.tablespace_id,
(e.tablespace_usedsize) * (f.block_size) / 1024 tablespace_usedsize_kb,
(e.tablespace_size) * (f.block_size) / 1024 tablespace_size_kb
from dba_hist_tbspc_space_usage e,
dba_tablespaces f,
v$tablespace g
where e.tablespace_id = g.TS#
and f.tablespace_name = g.NAME
and f.contents not in (‘TEMPORARY’, ‘UNDO’))
group by rtime)
select tmp.rtime,
tablespace_usedsize_kb,
tablespace_size_kb,
(tablespace_usedsize_kb - LAG(tablespace_usedsize_kb, 1, NULL)
OVER(ORDER BY tmp.rtime)) AS DIFF_KB
from tmp,
(select max(rtime) rtime from tmp group by substr(rtime, 1, 10)) t2
where t2.rtime = tmp.rtime;

3、查看哪些会话被阻塞
select c.terminal||’ (‘’‘||a.sid||’,‘||c.serial#||’‘’) is blocking ‘||b.sid
||’,'||d.serial# block_msg, a.block
from v$locka,v$lock b,v$sessionc,v$session d
where a.id1=b.id1
and a.id2=b.id2
and a.block>0
and a.sid <>b.sid
and a.sid=c.sid
and b.sid=d.SID

4、查看某个会话正在执行的SQL
select sql_text from v$sqltex{t}_{w}it{h}_{n}ewlineswhere(has{h}_{v}alue,address)in(selectsq{l}_{h}as{h}_{v}alue,sq{l}_{a}ddressfromv$session where sid=145) order by address,piece;

5、查看数据库当前连接数及最大连接数
select count(0) from v$process;---当前的连接数selectvaluefromv$parameter where name=‘processes’ --数据库允许的最大连接数

6、组装批量杀进程语句
select b.username,‘alter system kill session ‘||’’‘’||b.sid||‘,’||b.serial#||‘’‘’||‘;’,logon_time
from v$locke{d}_{o}bjecta,v$session b
where a.session_id = b.sid order by b.logon_time;
----------------------运维常用SQL语句--------

1、查看表空间使用率
set line 220
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 order by used_pct desc;
2、查询单个表空间使用率
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 where tablespace_name=‘TBL_SPACE’ group by tablespace_name) free,
(select tablespace_name,sum(bytes) 1024 1024 as MB from dba_data_files where tablespace_name=‘TBL_SPACE’ group by tablespace_name) total
where free.tablespace_name = total.tablespace_name order by used_pct desc;
3、查看临时表空间数据文件位置，大小，及是否自动扩展
select tablespace_name,file_name,bytes/1024/1024 mb ,autoextensible from dba_data_files where tablespace_name in (‘’) order by tablespace_name;
select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;
–查看所有临时表空间大小
SELECT D.TABLESPACE_NAME,SPACE “SUM_SPACE(M)”,BLOCKS SUM_BLOCKS,
USED_SPACE “USED_SPACE(M)”,ROUND(NVL(USED_SPACE,0)/SPACE100,2) “USED_RATE(%)”,
NVL(FREE_SPACE,0) “FREE_SPACE(M)”
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(10241024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(10241024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(10241024),2) FREE_SPACE
FROM V$TEM{P}_{S}PAC{E}_{H}EADERGROUPBYTABLESPAC{E}_{N}AME)FWHERED.TABLESPAC{E}_{N}AME=F.TABLESPAC{E}_{N}AME(+);4、查看ASM磁盘空间selectname,state,type,fre{e}_{m}b,tota{l}_{m}b,usabl{e}_{f}il{e}_{m}bfromv$asm_diskgroup;
5、查询oracle的连接数
select count(*) from v$session;6、查看不同用户的连接数selectusername,count(username)fromv$session where username is not null group by username;
7、查看回收站
show recyclebin
8、清空回收站
PURGE recyclebin
9、删除表，但不进入回收站
drop table tableName purge;
10、查询用户下所有创建表的语句
select
‘select dbms_metadata.get_ddl(’||‘’‘’||‘TABLE’||‘’‘’||‘,’||‘’‘’||table_name||‘’‘’||‘) from dual;’||chr(10)||‘select ‘||’’‘’||‘/’||‘’‘’|| ’ from dual;’
from user_tables;
11、查询当时创建用户的语句
select dbms_metadata.get_ddl(‘USER’,‘USERNAME’) from dual;
12、查询普通用户语句
select username from dba_users where account_status=‘OPEN’;
13、修改数据文件大小
alter database datafile ‘&path_name’ resize 10G;
alter database datafile &{file_id} resize 10G;
14、添加数据文件
alter tablespace &tablespace_name ADD datafile ‘&datafile_name’ SIZE xxx;
15、临时表空间扩容、添加临时表空间数据文件
ALTER TABLESPACE &tablespace_name ADD TEMPFILE ‘&datafile_name’ SIZE xxx;
15.大文件表空间扩容
ALTER TABLESPACE &tablespace_name RESIZE xxx;
16、大文件表空间扩容

ALTER TABLESPACE &tablespace_name RESIZE xxx
17、查询告警日志文件位置
show parameter dump
select * from vKaTeX parse error: Expected 'EOF', got '#' at position 190: …,b.sid,b.serial#̲,logon_time fro…lock_object a,v$sessionbwherea.sessio{n}_{i}d=b.sidorderbyb.logo{n}_{t}ime22、查询数据库中所有用户下占用物理空间内存大小selectowner,sum(bytes)/1024/1024MBfromdb{a}_{s}egmentsgroupbyowner;23、日志切换altersystemswitchlogfile;24、查看归档是否开启archiveloglist;selectlo{g}_{m}odefromv$database;
25、开启归档
alter system set log_archive_dest_1=‘LOCATION=+ARCH’ scope=both sid=‘*’;
shu immediate
startup mount
alter database archivelog
alter database open
26、监听注册
alter system set LOCAL_LISTENER=‘(ADDRESS = (PROTOCOL = TCP)(HOST = JiekeXu)(PORT = 1522))’;
alter system set LOCAL_LISTENER=‘(ADDRESS = (PROTOCOL = TCP)(HOST = 10.X.X.6)(PORT = 1521))’ SID=‘JiekeDBR2’ scope=both;
27、数据库注册监听
alter system register;
28、创建DBLINK
create public database link HO
connect to SKDATA identified by oracle
using ‘(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.X.X.6)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = JiekeXuDB)
)
)’;
29、删除DBLINK
drop public database link &dblinkname;
30、查询DBLINK
select * from dba_db_links
31、查询本某个地用户登陆的进程
ps -ef|grep LOCAL=NO
32、批量杀进程执行 ，注意检查oracle_sid
ps -ef|grep $ORACLE_SID|grep -v ora_|grep LOCAL=NO|awk '{print KaTeX parse error: Expected 'EOF', got '}' at position 2: 2}̲'|xargs kill -9…database;
34、查询控制文件中数据文件的SCN命令
select name,file#,checkpoint_change# from vKaTeX parse error: Expected 'EOF', got '#' at position 53: …ame,last_change#̲ from vdatafile;
36.解锁用户命令
alter user 用户名 account unlock; ----这个只会解锁账号，而不会取消密码过期。
37.设置密码无期限
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; 将概要文件(如default)的密码有效期由默认的180天修改成“无限制”（修改之后不需要重启动数据库，会立即生效）
38.查询字符集命令

select userenv (‘language’) from dual;
39.查询数据库里某个表空间里所占用的大小：
select OWNER,SEGMENT_TYPE,TABLESPACE_NAME,BYTES/1024/1024 as MB from dba_segments where TABLESPACE_NAME=‘AT01’ order by 4 desc;
40关于DG认清主备库。
select d.NAME,d.LOG_MODE,d.OPEN_MODE,d.PROTECTION_MODE,d.DATABASE_ROLE from v$databased;名字，日志模式，开启模式，保护模式，数据库角色。其中数据库角色就是看主备库的.PRIMARY为主库PHYSICALSTANDBY物理备库,LOGICALSTANDBY逻辑备库.在备库中查询：showparameterfa{l}_{s}erver得出的值在与tns中找相匹配的serve{r}_{n}ame然后在寻找对应的IP地址。开启恢复SQL>recovermanagedstandbydatabasedisconnectfromsessionusingcurrentlogfile;ADG相关命令：selectname,d{b}_{u}niqu{e}_{n}ame,databas{e}_{r}ole,protectio{n}_{m}ode,ope{n}_{m}odefromv$database; --查看数据库状态
select process,status,sequence# from v$manage{d}_{s}tandby;--查看相关进程recovermanagedstandbydatabasecancel;--关闭mrp功能recovermanagedstandbydatabasedisconnectfromsessionusingcurrentlogfile;--开启当前日志应用alterdatabaserecovermanagedstandbydatabasedisconnectfromsession;---开启不同步当前日志应用selectdes{t}_{i}d,error,status,lo{g}_{s}equence,applie{d}_{s}cnfromv$archive_dest;–查看归档路径状态
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;—查询传输至备库的日志是否应用。

41.添加ASM磁盘所需命令
在AIX系统中查询新加入的盘符 lspv
在AIX系统中查询盘空间大小：getconf DISK_SIZE /dev/hdisk1
在数据库中查询ASM的中是否使用了此硬盘：col PATH for a44
select GROUP_NUMBER,DISK_NUMBER,TOTAL_MB/1024,FREE_MB/1024,NAME,FAILGROUP,PATH,FAILGROUP_TYPE from v$as{m}_{d}isk;42.使用轻松连接模式远程登录数据库sqlplussjglt/sjglt@1X.1XX.1X6.16:1521/orcl43.更改备份保存时间altersystemsetcontro{l}_{f}il{e}_{r}ecor{d}_{k}ee{p}_{t}ime=39;45.查询oracle连接最大值命令：selectresourc{e}_{n}ame,MA{X}_{U}TILIZATION,LIMI{T}_{V}ALUEfromv$resource_limit where resource_name in (‘processes’,‘sessions’);
MAX_UTILIZATION代表数据库自上次启动以来达到的最大值，LIMIT_VALUE代表设置的最大值。
46.查询锁SQL:
select ‘blocker(’||lb.sid||‘:’||sb.username||‘)-sql:’|| qb.sql_text blockers,
‘waiter (’||lw.sid||‘:’||sw.username||‘)-sql:’|| qw.sql_text
waiters
from v$locklb,v$lock lw,
v$sessionsb,v$session sw,
v$sqlqb,v$sql qw
where lb.sid=sb.sid
and lw.sid=sw.sid
and sb.prev_sql_addr=qb.address
and sw.sql_address=qw.address
and lb.id1=lw.id1
and sw.lockwait is not null
and sb.lockwait is null
and lb.block=1 ;
47.查询数据库数据量
select sum（bytes）/1024/1024/1024 total_gb from dba_segment
48.查看sql的执行计划
select * from vKaTeX parse error: Expected 'EOF', got '&' at position 24: … where sql_id='&̲sql_id'; 50.查看绑…sql_bind_capture sbc where sql_id=‘&sql_id’;
51.关于rman归档问题
rman> crosscheck archivelog all;
rman> delete noprompt expired archivelog all;
rman> delete force noprompt archivelog until time ‘sysdate - 30’;
单节点：
delete noprompt archivelog until sequence ${Seq};
rac（thread后面加节点数）:
delete noprompt archivelog until sequence 1 thread $CurThread;删除7天前的归档：DELETEARCHIVELOGALLCOMPLETEDBEFOR{E}^{\prime }SYSDATE-7^{\prime };按时间删除归档：deleteforcearchivelogallcompletedbefor{e}^{\prime }SYSDATA-1/24^{\prime };查看归档每天切换频率以及大小selectmax(firs{t}_{t}ime)ma{x}_{f}irs{t}_{t}ime,t{o}_{c}har(firs{t}_{t}ime{,}^{\prime }yyyy-mm-d{d}^{\prime })day,count(recid)coun{t}_{n}umber,count(recid)\ast 200siz{e}_{m}bfromv$log_history
group by to_char (first_time, ‘yyyy-mm-dd’)
order by 1;
按天计算日志生成量
set pages 9999
select to_char(first_time,‘yyyymmdd’),count(*) from vKaTeX parse error: Expected 'EOF', got '#' at position 141: …A.SID, A.SERIAL#̲, A.STATUS, C.P…SESSION A,
V$SQLTEXTCWHEREA.SIDIN(SELECTDISTINCTT2.SIDFROMV$LOCKED_OBJECT T1,
V$SESSIONT2WHERET1.SESSIO{N}_{I}D=T2.SID)ANDA.SQ{L}_{A}DDRESS=C.ADDRESS(+)ORDERBYC.PIECE;53.查询DB负载情况SELECT\ast FROM(SELECTA.INSTANC{E}_{N}UMBER,A.SNA{P}_{I}D,B.BEGI{N}_{I}NTERVA{L}_{T}IME+0BEGI{N}_{T}IME,B.EN{D}_{I}NTERVA{L}_{T}IME+0EN{D}_{T}IME,ROUND(VALUE-LAG(VALUE,1{,}^{\prime }{0}^{\prime })OVER(ORDERBYA.INSTANC{E}_{N}UMBER,A.SNA{P}_{I}D))"DBTIME"FROM(SELECTB.SNA{P}_{I}D,INSTANC{E}_{N}UMBER,SUM(VALUE)/1000000/60VALUEFROMDB{A}_{H}IS{T}_{S}Y{S}_{T}IM{E}_{M}ODELBWHEREB.DBID=(SELECTDBIDFROMV$DATABASE)
AND UPPER (B.STAT_NAME) IN UPPER((‘DB TIME’ ))
GROUP BY B.SNAP_ID, INSTANCE_NUMBER) A,
DBA_HIST_SNAPSHOT B
WHERE A.SNAP_ID = B.SNAP_ID
AND B.DBID = (SELECT DBID FROM V$DATABASE)
AND B.INSTANCE_NUMBER = A.INSTANCE_NUMBER)
WHERE TO_CHAR(BEGIN_TIME, ‘YYYY-MM-DD’) = TO_CHAR(SYSDATE , ‘YYYY-MM-DD’)
ORDER BY BEGIN_TIME;

54.杀应用连接
ps -ef |grep xxx
kill -9 pid
55.查询某个时间点的scn
select timestamp_to_scn(to_timestamp(‘2021-01-06 10:00:00’,‘yyyy-mm-dd hh24:mi:ss’)) from dual;
56.闪回到某个scn点：（闪回前请先进行备份）
alter table tb_flash1 enable row movement
flashback table tb_flash1 to 115398523;
57.查询数据库最近一次重启时间
alter session set nls_date_format=‘yyyy-mm-dd hh24:mi:ss’;
select startup_time from v$instance;58.查看日志每小时文件切换频率columnh0format999columnh1format999columnh2format999columnh3format999columnh4format999columnh5format999columnh6format999columnh7format999columnh8format999columnh9format999columnavgformat999.99columndayformata6SELECTTRUNC(firs{t}_{t}ime)"Date",T{O}_{C}HAR(firs{t}_{t}ime{,}^{\prime }D{y}^{\prime })"Day",COUNT(1)"Total",SUM(DECODE(T{O}_{C}HAR(firs{t}_{t}ime{,}^{\prime }hh24^{\prime }){,}^{\prime }00^{\prime },1,0))h0,SUM(DECODE(T{O}_{C}HAR(firs{t}_{t}ime{,}^{\prime }hh24^{\prime }){,}^{\prime }01^{\prime },1,0))"h1",SUM(DECODE(T{O}_{C}HAR(firs{t}_{t}ime{,}^{\prime }hh24^{\prime }){,}^{\prime }02^{\prime },1,0))"h2",SUM(DECODE(T{O}_{C}HAR(firs{t}_{t}ime{,}^{\prime }hh24^{\prime }){,}^{\prime }03^{\prime },1,0))"h3",t{o}_{c}har(ROUND(COUNT(1)/24,2){,}^{\prime }fm99999999990.00^{\prime })"Avg"FROMgv$log_history
WHERE first_time >= trunc(SYSDATE) - 30
and thread# = inst_id
GROUP BY TRUNC (first_time), TO_CHAR (first_time, ‘Dy’)
ORDER BY 1 DESC;
59.更改会话时间显示格式
alter session set nls_date_format=‘yyyy-mm-dd hh24:mi:ss’;
60.查阻塞
col username for a15
col program for a30
col event for a30
select sid,serial#,username,blocking_session,last_call_et,prev_sql_id,final_blocking_session,event,machine,program,WAIT_TIME
from gvKaTeX parse error: Expected 'EOF', got '&' at position 291: …e table_name='&̲1'; 62.根据PID查登陆…session s where s.SID in(
select sid from v$sessionwherepaddrin(selectaddrfromv$process where spid in (
sipd1,
spid2,
spid3,
)
63，查看表空间属性
SELECT * FROM dba_tablespace wheretablespace_name = ‘表空间名’;
64,修改表空间状态、名字
alter tablespace 表空间名 表空间状态;
alter tablespace 表空间名 1 rename to 表空间名 2;
65,修改表空间对应的数据文件大小
alter database
datafile ‘表空间文件路径名称’
resize 大小
66、查询堵塞别的会话超过30分钟且自身是不活动的会话
select username,sid,serial#,status,seconds_in_wait,LAST_CALL_ET from v$sessionwheresidin(selectFINA{L}_{B}LOCKIN{G}_{S}ESSIONfromv$session
where state=‘WAITING’ and BLOCKING_SESSION_STATUS=‘VALID’ and FINAL_BLOCKING_SESSION_STATUS=‘VALID’) and status=‘INACTIVE’
and sql_id is null and seconds_in_wait>1800;
67、查询正在执行的 dbms_job
select job,b.sid,b.SERIAL#,b.username,spid from DBA_JOBS_RUNNING a ,v$sessionb,v$process
where a.sid=b.sid and paddr=addr;

68、查询当前正在消耗临时空间的 SQL 语句
Select distinct se.username,se.sid,
su.blocks * to_number(rtrim(p.value))/1024/1024 as space_G,
su.tablespace,sql_text
from V$TEMPSE{G}_{U}SAGEsu,v$parameter p, v$sessionse,v$sql s
where p.name = ‘db_block_size’ and su.session_addr=se.saddr
and su.sqlhash=s.hash_value and su.sqladdr=s.address and se.STATUS=‘ACTIVE’;

69、统计每个用户使用表空间率
SELECT c.owner “用户”,
a.tablespace_name “表空间名”,
total/1024/1024 “表空间大小M”,
free/1024/1024 “表空间剩余大小M”,
( total - free )/1024/1024 “表空间使用大小M”,
Round(( total - free ) / total, 4) * 100 “表空间总计使用率 %”,
c.schemas_use/1024/1024 “用户使用表空间大小M”,
round((schemas_use)/total,4)*100 “用户使用表空间率 %”
FROM (SELECT tablespace_name,
Sum(bytes) free
FROM DBA_FREE_SPACE
GROUP BY tablespace_name) a,
(SELECT tablespace_name,
Sum(bytes) total
FROM DBA_DATA_FILES
GROUP BY tablespace_name) b,
(Select owner ,Tablespace_Name,
Sum(bytes) schemas_use
From Dba_Segments
Group By owner,Tablespace_Name) c
WHERE a.tablespace_name = b.tablespace_name
and a.tablespace_name =c.Tablespace_Name
order by “用户”,“表空间名”;