常用查询
1. oralce常用的查询
目录
- 1. oralce常用的查询
- 1.1.1. 1 查询所有表空间使用率
- 1.1.2. 2 查询当前连接到数据库的用户和主机信息
- 1.1.3. 3 查询一个数据库总空间大小
- 1.1.4. 查看某个用户的大小
- 1.1.5. 4 常用查询之-查询数据库下多个表的数据量大小(单位MB)：
- 1.1.6. 5 查询一个表占多少空间：
- 1.1.7. 6 查询表空间文件名
- 1.1.8. 7 查询存储剩余空间(ASM磁盘),非ASM磁盘存储，直接查看数据文件的目录（du）
- 1.1.9. 8 添加表空间
- 1.1.10. 9 查看用户使用的profiles
- 1.1.11. 10 查看表的最近一次修改时间
- 1.1.12. 查看表的数据最新更新时间
- 1.1.13. 11 查询失效对象
- 1.1.14. 12 查看除了系统用户的其他业务用户
- 1.1.15. 13 查询当前正在执行的会话和对应的sql
- 1.1.16. 14 查看当前活动的会话中的阻塞信息
- 1.1.17. 15 查看当前会话的主机和IP信息
- 1.1.18. 16 查询锁表的ip，锁表的SQL等信息。
- 1.1.19. 17 常用查询之-查询数据库下多个表的数据量大小(单位MB)：
- 1.1.20. 18 查看数据库总的大小（单位GB）：
- 1.1.21. 19 查询当前数据库是否存在会话：
- 1.1.22. 20 获取消耗指定时间范围的会话
- 1.1.23. 21 查看PGA和SGA的使用情况
- 1.1.24. 22 查看PGA、SGA、Shared pool、java pool等的使用情况的使用情况
- 1.1.25. 查询第 n到到M条记录
- 1.1.26. 多列数据分组统计
- 1.1.27. 多表分组统计
- 1.1.28. 23.查看oracle数据库的状态
- 1.1.29. 查看缓冲区命中率是否需要调优
- 1.1.30. 将查询的数据输出到文件中：
- 1.1.31. 查看并修改undo_retention：
- 1.1.32. 查看最近执行过的sql
- 1.1.33. 查询当前数据库中的TX锁，并按照锁定的关系显示：
- 1.2. 查询重复数据
- 1.3. 查看表空间的磁盘碎片情况
- 1.4. ORACLE查看并发数
- 1.5. oracle 手动锁表
- 1.6. 通过触发器更新修改时间
- 1.7. 查询用户连接，并拼接杀会话的语句
- 1.8. 系统时格式设置（会话级别）
- 1.9. 查看日志文件位置
1.1.1. 1 查询所有表空间使用率
set linesize 300
set pagesize 999
SELECT a.tablespace_name,
ROUND (a.total_size) "total_size(GB)",
ROUND (a.total_size) - ROUND (b.free_size, 2) "used_size(GB)",
ROUND (b.free_size, 2) "free_size(GB)",
ROUND (b.free_size / total_size * 100, 2) || '%' free_rate
FROM ( SELECT tablespace_name, SUM (bytes) /1024 /1024/1024total_size
FROM dba_data_files
GROUP BY tablespace_name) a,
( SELECT tablespace_name, SUM (bytes) /1024/1024/1024 free_size
FROM dba_free_space
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+);
查看最大分配
SELECT * FROM (
select t.tablespace_name 表空间名称,
substr(t.contents, 1, 1) 表空间类型,
trunc((d.tbs_size-nvl(s.free_space, 0))/1024/1024/1024) 已使用GB,
trunc(d.tbs_size/1024/1024/1024) 已分配GB,
trunc(d.tbs_maxsize/1024/1024/1024) 最大可用GB,
trunc(nvl(s.free_space, 0)/1024/1024/1024) 当前剩余GB,
trunc((d.tbs_maxsize - d.tbs_size + nvl(s.free_space, 0))/1024/1024/1024) 最大剩余GB,
decode(d.tbs_maxsize, 0, 0, trunc((d.tbs_size-nvl(s.free_space, 0))*100/d.tbs_maxsize))||'%' 磁盘使用百分比
from
( select SUM(bytes) tbs_size,
SUM(decode(sign(maxbytes - bytes), -1, bytes, maxbytes)) tbs_maxsize, tablespace_name tablespace
from ( select nvl(bytes, 0) bytes, nvl(maxbytes, 0) maxbytes, tablespace_name
from dba_data_files
union all
select nvl(bytes, 0) bytes, nvl(maxbytes, 0) maxbytes, tablespace_name
from dba_temp_files
)
group by tablespace_name
) d,
( select SUM(bytes) free_space,
tablespace_name tablespace
from dba_free_space
group by tablespace_name
) s,
dba_tablespaces t
where t.tablespace_name = d.tablespace(+) and
t.tablespace_name = s.tablespace(+)
order by 8)
1.1.1.1. 查看用户和用户使用的表空间：
select username,default_tablespace from dba_users order by username;
删除表空间：
drop user TBSP cascade;
drop tablespace TBSP_1 including contents and datafiles cascade constraint;
1.1.1.2. 查看表空间创建语句：
SELECT dbms_lob.substr(DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name))
FROM DBA_TABLESPACES TS where tablespace_name='USERS';
1.1.1.3. 查看建表语句：
select to_char(dbms_metadata.get_ddl('TABLE','EMP')) as t from dual;
select to_char(dbms_metadata.get_ddl('TABLE','EMP'，'user')) as t from dual;
eg:(表名和用户名都需要大写，否者将找不到对象)
查看scott用户下的TD_TICKET_PDA_CONFIG表的结构
set pagesize 999
select to_char(dbms_metadata.get_ddl('TABLE','TD_TICKET_PDA_CONFIG','SCOTT')) as t from dual;
1.1.2. 2 查询当前连接到数据库的用户和主机信息
select osuser,a.username,cpu_time/executions/1000000||'s',sql_fulltext,machine
from v$session a,v$sqlarea b
where a.sql_address = b.address
order by cpu_time/executions desc;
1.1.3. 3 查询一个数据库总空间大小
SELECT ROUND(SUM(BYTES)/1024/1024/1024,2)||'GB' FROM dba_extents;
以dba_segments查询出的结果为标准（单位GB）：
select ROUND(sum(bytes)/1024/1024/1024,2)||'GB' from dba_segments;
1.1.4. 查看某个用户的大小
select ROUND(sum(bytes)/1024/1024/1024)||'G' from dba_segments where OWNER='SN0113';
1.1.5. 4 常用查询之-查询数据库下多个表的数据量大小(单位MB)：
SELECT ROUND(SUM(BYTES)/1024/1024,2)||'MB' FROM dba_segments where OWNER in ('NEWWEB') and SEGMENT_NAME in ('table1','table2','table3','.......');
select segment_name,bytes/1024/1024 MB,tablespace_name,bytes from dba_segments where OWNER='SCOTT';
select segment_name,ROUND(bytes/1024/1024,2) ||' MB' from dba_segments where OWNER='GJNC65' ORDER BY ROUND(bytes/1024/1024,2) DESC;
1.1.6. 5 查询一个表占多少空间：
select bytes/1024/1024 MB, segment_name,tablespace_name,bytes from dba_segments where segment_name='B'and owner='SYSTEM';
select segment_name,ROUND(bytes/1024/1024,2) ||' MB' from dba_segments where OWNER='SCOTT';
select segment_name,ROUND(bytes/1024/1024,2) ||' MB' from dba_segments where OWNER='GJNC65' ORDER BY ROUND(bytes/1024/1024,2) DESC;
1.1.7. 6 查询表空间文件名
1.查询对应表空间文件数据文件数量：
select count(*) from dba_data_files where tablespace_name='USERS';
2.查看表空间数据文件路径：
select * from dba_data_files where tablespace_name='USERS';
3.按照数据文件名字匹配：
select * from dba_data_files where tablespace_name='USERS'and file_name like '%2017%
1.1.8. 7 查询存储剩余空间(ASM磁盘),非ASM磁盘存储，直接查看数据文件的目录（du）
select group_number,name,total_mb/1024 total_gb,trunc(free_mb/1024) free_gb from v$asm_diskgroup;
1.1.9. 8 添加表空间
1.ASM方式添加数据文件：
alter tablespace TBS_NAME add datafile '+DATADG_/tbs_zba_dma_20170829_001.dbf' size 131070m autoextend off;
2.FS文件系统方式添加数据文件
alter tablespace UNDOTBS1 add datafile '/vgodba02/odba/undotbs1_3.dbf' size 131070m autoextend off;
3.通过改变数据文件的大小改变表空间的大小
重新设置已经存在的表空间的大小，如果指定的文件的大小低于当前已用的数据文件的大小，那么就会报错。
alter database datafile '/u01/app/oracle/t1.dbf' resize 200M;
1.1.10. 9 查看用户使用的profiles
select * from dba_profiles;
$ORACLE_HOME/RDBMS/admin/utlpwdmg.sql
查看用户过期时间：
select username,profile,EXPIRY_DATE
from dba_users
where 1=1
and username='SCOTT'
1.1.11. 10 查看表的最近一次修改时间
select uat.table_name as table_name,(select to_char(last_ddl_time,'YYYY-MM-DD HH24:MI:SS') from user_objects where object_name = uat.table_name ) as last_ddl_time from user_all_tables uat;
1.1.12. 查看表的数据最新更新时间
SELECT
DB_SYS_CONF.*,
VERSIONS_STARTTIME,
VERSIONS_ENDTIME
FROM
DB_SYS_CONF
VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
ORDER BY
VERSIONS_STARTTIME DESC;
1.1.13. 11 查询失效对象
统计各个用户下失效对象的数目：
select owner, object_type, status, count(*)
from dba_objects
where status='INVALID'
group by owner, object_type, status
order by owner, object_type;
1、检查无效的数据库对象：
SELECT owner, object_name, object_type,status
FROM dba_objects
WHERE status = 'INVALID';
2、检查不起作用的约束：
SELECT owner, constraint_name, table_name, constraint_type, status
FROM dba_constraints
WHERE status = 'DISABLED';
3、检查无效的触发器：
SELECT owner, trigger_name, table_name, status
FROM dba_triggers
WHERE status = 'DISABLED';
4、检查失效的索引：
select index_name,table_name,tablespace_name,status
From dba_indexes
Where status<>'VALID';
5、批量重新编译
select 'alter '||object_type||' '||owner ||'.'||object_name||' COMPILE;' from dba_objects
WHERE status = 'INVALID' and user<>'SYS';
1.1.14. 12 查看除了系统用户的其他业务用户
set lin240 pages999;
select username,account_status from dba_users where username not in
(
'OUTLN',
'MGMT_VIEW',
'DBSNMP',
'ANONYMOUS',
'CTXSYS',
'SYSMAN',
'MDSYS',
'ORDPLUGINS',
'SI_INFORMTN_SCHEMA',
'OLAPSYS',
'DMSYS',
'XDB',
'EXFSYS',
'ORDSYS',
'WMSYS',
'SCOTT',
'ORACLE_OCM',
'DIP',
'TSMSYS',
'MDDATA',
'APEX_030200',
'APPQOSSYS',
'OWBSYS',
'ORDDATA',
'FLOWS_FILES',
'OWBSYS_AUDIT',
'SPATIAL_WFS_ADMIN_USR',
'XS$NULL',
'APEX_PUBLIC_USER',
'SPATIAL_CSW_ADMIN_USR',
'LBACSYS',
'DBSFWUSER',
'GGSYS',
'DVSYS',
'DVF',
'GSMADMIN_INTERNAL',
'GSMCATUSER',
'SYSBACKUP',
'REMOTE_SCHEDULER_AGENT',
'PDBADMIN',
'GSMUSER',
'SYSRAC',
'OJVMSYS',
'AUDSYS',
'SYSKM',
'SYS$UMF',
'SYSDG'
) order by username;
另外如果安装了示例库，那么还可能存在如下锁定的用户：
BI，HR，IX，OE，PM，SH
set pagesize 1000;
set linesize 1000;
select username,account_status,to_char(created,'yyyy-MM-dd HH24:mi:ss') as created from dba_users where username='governance' order by created asc;
如果有PDB,那么需要切换到对应的PDB下查询：
alter session set container=test_pdb;
col NAME format a20;
col USERNAME format a20;
col PASSWORD format a20;
col PASSWORD format a20;
select a.name,b.username, b.password,b.created
from v$pdbs a
left join cdb_users b on a.con_id = b.con_id
where a.con_id in(3,4,5) and to_char(b.created,'yyyymmdd')!='20140911'
order by 1;
1.1.15. 13 查询当前正在执行的会话和对应的sql
select t.BLOCKING_SESSION,
t.SQL_ID,
t.SID,
t.SERIAL#,
t.MACHINE,
t.PROGRAM,
t.ACTION,
t.LOGON_TIME , --登录时间
trunc((sysdate - t.LOGON_TIME) * 24 * 60 * 60) || 's', --登录时长
trunc(nvl(s.ELAPSED_TIME / decode(s.EXECUTIONS, 0, 1, s.EXECUTIONS) /1000000,0),2) || 's', --当前SQL每次执行平均耗时
t.EVENT#,
t.EVENT,
t.WAIT_CLASS,
t.P1TEXT,
t.p1,
s.sql_text,
s.sql_fulltext
FROM gv$session t
left join v$sql s
on s.sql_id = t.sql_id
and s.CHILD_NUMBER = t.SQL_CHILD_NUMBER
WHERE t.STATUS = 'ACTIVE'
and t.WAIT_CLASS <> 'Idle'
order by t.SID, t.MACHINE;
简洁版
set linesize 300
col ACTION format a10
col SQL_ID format a20
col LOGINTIME format a20
col EXETIME format a20
col SQL_FULLTEXT format a40
select t.BLOCKING_SESSION,
t.SQL_ID,
t.SID,
t.SERIAL#,
t.ACTION,
t.LOGON_TIME , --登录时间
trunc((sysdate - t.LOGON_TIME) * 24 * 60 * 60) || 's' as logintime, --登录时长
trunc(nvl(s.ELAPSED_TIME / decode(s.EXECUTIONS, 0, 1, s.EXECUTIONS) /1000000,0),2) || 's' as exetime, --当前SQL每次执行平均耗时
s.sql_fulltext
FROM gv$session t
left join v$sql s
on s.sql_id = t.sql_id
and s.CHILD_NUMBER = t.SQL_CHILD_NUMBER
WHERE t.STATUS = 'ACTIVE'
and t.WAIT_CLASS <> 'Idle'
order by t.SID, t.MACHINE;
col SPID format a10
col SERIAL# format a10
col OS_PID format a10
col SQL_ID format a15
col EVENT format a20
select t2.sid, t1.spid, t2.SERIAL#, t1.SPID OS_PID,
t3.SQL_ID, t2.EVENT,
t3.SQL_FULLTEXT
from v$process t1, v$session t2, v$sql t3
where t1.ADDR = t2.PADDR
and t2.STATUS = 'ACTIVE' -- 'INACTIVE'
and t2.SQL_ID = t3.SQL_ID ;
查询出对应的sql之后，查看当前sql是本地连接还是远程的连接；
ps -ef|grep oracle|grep 进程ID（spid）
ps -ef|grep oracle|grep 25430
查杀恢复：
alter system kill session 'sid,SERIAL#';
或者
kill -9 spid
1.1.16. 14 查看当前活动的会话中的阻塞信息
单机：
SELECT LPAD(' ',5*(LEVEL-1))||S."USERNAME" AS user_name ,
LPAD(' ',5*(LEVEL-1))||S."SID" AS session_id,
S."SERIAL#",
S."SQL_ID", S."ROW_WAIT_OBJ#",
S."WAIT_CLASS",
S."EVENT",
S."P1",
S."P2",
S."P3",
S."SECONDS_IN_WAIT"
FROM V$SESSION S
WHERE S."BLOCKING_SESSION" IS NOT NULL
OR S.SID IN(SELECT DISTINCT BLOCKING_SESSION FROM V$SESSION)
START WITH S."BLOCKING_SESSION" IS NULL
CONNECT BY PRIOR S."SID" = S."BLOCKING_SESSION";
rac或者单机：
SELECT
LPAD(' ',5*(LEVEL-1))||S."USERNAME" ,
LPAD(' ',5*(LEVEL-1))||S."INST_ID"||','||S."SID" ,
S.sid,
S."SERIAL#" ,
t2.spid,
S."SQL_ID",
S."ROW_WAIT_OBJ#",
S."WAIT_CLASS",
S."EVENT",
l.locked_mode,
S."P1",
S."P2",
S."P3",
S."SECONDS_IN_WAIT",
s."BLOCKING_INSTANCE"||','||s."BLOCKING_SESSION",
s.machine,
s.terminal,
a.sql_text,
a.action,
l.oracle_username,
s.user#,
s.status
FROM GV$SESSION S
join v$sqlarea a on S.prev_sql_addr = a.address
join v$locked_object l on l.session_id = S.sid
join gv$process t2 on S.paddr=t2.addr
WHERE S."BLOCKING_SESSION" IS NOT NULL
OR (S."INST_ID"||','||S."SID") IN(SELECT DISTINCT BLOCKING_INSTANCE||','||BLOCKING_SESSION FROM GV$SESSION)
START WITH (s."BLOCKING_INSTANCE"||','||s."BLOCKING_SESSION") = ','
CONNECT BY PRIOR (S."INST_ID"||','||S."SID") = (s."BLOCKING_INSTANCE"||','||s."BLOCKING_SESSION");
以上表示第一个会话阻塞了下面的会话，第二个会话在争夺一个TX锁，也就是等待第一个会话释放这个锁（TX - row lock contention）。
ALTER SYSTEM KILL SESSION '24,201';
或者
kill -9 spid
kill -9 13771
1.1.17. 15 查看当前会话的主机和IP信息
1.创建获取访问数据库IP的触发器
create or replace trigger on_logon_trigger after logon on database
begin
dbms_application_info.set_client_info(sys_context('userenv','ip_address'));
end;
/
2.查看语句对应的IP：
set linesize 300
set pagesize 999
select sid,serial#,username,sql_id,program,machine,client_info
from v$session
where username is not null
order by username,program,machine;
1.1.18. 16 查询锁表的ip，锁表的SQL等信息。
SELECT
l.session_id
, s.serial#
, s.machine
, o.object_name
, s.logon_time
, SYS_CONTEXT('USERENV','IP_ADDRESS') as ip
, v.TYPE
, t.name
, t.DESCRIPTION
, a.SQL_FULLTEXT
FROM
v$locked_object l
, dba_objects o
, v$session s
, v$lock v
, v$lock_type t
, v$sqlarea a
WHERE
l.object_id= o.object_id
AND l.session_id= s.sid
and l.session_id=v.sid
and v.type=t.type
and s.prev_sql_addr = a.address
ORDER BY
l.session_id
,s.serial#;
1.1.19. 17 常用查询之-查询数据库下多个表的数据量大小(单位MB)：
SELECT ROUND(SUM(BYTES)/1024/1024,2)||'M' FROM dba_segments where OWNER in ('NEWWEB') and SEGMENT_NAME in ('table1','table2','table3','.......');
查询每张表的数据量
select t.table_name,t.num_rows from dba_tables t where OWNER in ('PCARD') and t.TABLE_NAME in('ACTION_MANAGEMENT_INFO','DEVICE_ID_CONFIG');
select segment_name,sum(bytes/1024/1024)
from dba_segments
where owner='PCARD'
group by segment_name order by sum(bytes/1024/1024);
注意：这里的表名称需要大写，否者查询的结果为空。
1.1.20. 18 查看数据库总的大小（单位GB）：
select ROUND(sum(bytes)/1024/1024/1024)||'G' from dba_segments;
1.1.21. 19 查询当前数据库是否存在会话：
dba_objects
v$session
1.1.22. 20 获取消耗指定时间范围的会话
set line 200 pages 999
col DATE_TIME for a35
WITH sysstat AS
(SELECT sn.begin_interval_time,
sn.end_interval_time,
ss.stat_name,
ss.value e_value,
LAG(ss.value, 1) OVER(ORDER BY ss.snap_id) b_value,
sn.snap_id
FROM dba_hist_sys_time_model SS, dba_hist_snapshot sn
WHERE ss.snap_id = sn.snap_id
AND ss.dbid = sn.dbid
AND ss.instance_number = sn.instance_number
AND ss.dbid = (SELECT dbid
FROM v$database)
AND ss.instance_number =
(SELECT instance_number
FROM gv$instance
WHERE instance_number = &inst_num)
AND TRUNC(sn.begin_interval_time) >= SYSDATE - 7
AND ss.stat_name = 'DB time'),
sysstat_for AS
(SELECT snap_id,
to_char(begin_interval_time, 'yyyy-mm-dd hh24:mi') ||
to_char(end_interval_time, '-hh24:mi') date_time,
stat_name,
round((e_value - nvl(b_value, 0)) / 60 / 1000000, 2) db_time,
round((EXTRACT(DAY FROM(end_interval_time - begin_interval_time)) * 24 * 60 * 60 +
EXTRACT(HOUR FROM(end_interval_time - begin_interval_time)) * 60 * 60 +
EXTRACT(MINUTE FROM(end_interval_time - begin_interval_time)) * 60 +
EXTRACT(SECOND
FROM((end_interval_time - begin_interval_time)))) / 60,
2) elapsed
FROM sysstat
WHERE (e_value - NVL(b_value, 0)) > 0
AND NVL(b_value, 0) > 0)
SELECT syf.snap_id - 1 bef_snap_id,
syf.snap_id,
syf.date_time,
syf.elapsed,
syf.db_time,
round(syf.db_time / syf.elapsed, 1) "DB_TIME/ELAPSED"
FROM sysstat_for syf order by 5;
查询锁信息：
查询当前活动的事务：
set linesize240 pages999;
select inst_id as instance
,event
,sum(decode(wait_time, 0, 1, 0)) "Curr"
,sum(decode(wait_time, 0, 0, 1)) "Prev"
,count(*) "Total"
from gv$session_wait
where event not in ('smon timer',
'pmon timer',
'rdbms ipc message',
'SQL*Net message from client',
'gcs remote message')
and event not like '%idle%'
and event not like '%Idle%'
and event not like '%Streams AQ%'
group by inst_id, event
order by inst_id, count(*) desc;
通过事务类型，查询对应的会话的信息：
set linesize 300 pages 999
col USERNAME for a12
col MACHINE for a20
col PROGRAM for a20
col PREV_SQL_ID for a13
col STATUS for a7
col SQL_ID for a13
col EVENT for a30
col osuser for a15
col SPID for a8
SELECT a.INST_ID
,a.SID
,a.SERIAL#
,b.spid
,a.osuser
,a.USERNAME
,a.PREV_SQL_ID
,a.SQL_ID
,a.EVENT#
,a.EVENT
,a.STATUS
,a.BLOCKING_INSTANCE AS blk_in
,a.BLOCKING_SESSION AS blk_sid
FROM gv$session a
,gv$process b
WHERE a.PADDR = b.ADDR
AND a.INST_ID = b.INST_ID
AND a.EVENT ='&event';
set linesize 300 pages 999
col USERNAME for a12
col MACHINE for a20
col PROGRAM for a20
col PREV_SQL_ID for a13
col STATUS for a7
col SQL_ID for a13
col EVENT for a30
col osuser for a15
col SPID for a8
col P123 for a30;
SELECT S.SID
,S.SERIAL#
,P.SPID
,S.USERNAME
,S.MACHINE
,s.osuser
,S.STATUS
,p.PGA_USED_MEM / 1024 / 1024 pga_use_MB
,trunc(p.PGA_ALLOC_MEM / 1024 / 1024) PGA_ALLOC_MB
,S.WAIT_TIME WT
,NVL(SQL_ID, S.PREV_SQL_ID) SQL_ID
,s.BLOCKING_INSTANCE
,s.BLOCKING_SESSION blk_sid
FROM V$PROCESS P
,V$SESSION S
WHERE P.ADDR = S.PADDR
AND P.BACKGROUND IS NULL
and S.SID=&sid
ORDER BY event;
通过sql_id查看对应的sql
select SQL_TEXT from v$sql where sql_id='65agvq7dd1j43';
1.1.23. 21 查看PGA和SGA的使用情况
--sga
select name,
trunc(total) as total_mb,
round(total - free, 2) used_mb,
round(free, 2) free_mb,
round((total - free) / total * 100, 2) pctused
from (select 'SGA' name,
(select sum(value / 1024 / 1024) from v$sga) total,
(select sum(bytes / 1024 / 1024) from v$sgastat where name = 'free memory') free from dual
)
union
--pga
select name,
trunc(total) as total_mb,
round(used, 2) used_mb,
round(total - used, 2) free_mb,
round(used / total * 100, 2) pctused
from (select 'PGA' name,
(select value / 1024 / 1024 total from v$pgastat where name = 'aggregate PGA target parameter') total,
(select value / 1024 / 1024 used from v$pgastat where name = 'total PGA allocated') used from dual
);
1.1.24. 22 查看PGA、SGA、Shared pool、java pool等的使用情况的使用情况
select component,current_size/1024/1024/1024 ||' G',min_size/1024/1024/1024 ||' G',max_size/1024/1024/1024 ||' G' from v$memory_dynamic_components;
select name,trunc(total) as total,round(total-free,2) used, round(free,2) free,round((total-free)/total*100,2) pctused from
(select 'SGA' name,(select sum(value/1024/1024) from v$sga) total,
(select sum(bytes/1024/1024) from v$sgastat where name='free memory')free from dual)
union
select name,total,round(used,2)used,round(total-used,2)free,round(used/total*100,2)pctused from (
select 'PGA' name,(select value/1024/1024 total from v$pgastat where name='aggregate PGA target parameter')total,
(select value/1024/1024 used from v$pgastat where name='total PGA allocated')used from dual)
union
select name,round(total,2) total,round((total-free),2) used,round(free,2) free,round((total-free)/total*100,2) pctused from (
select 'Shared pool' name,(select sum(bytes/1024/1024) from v$sgastat where pool='shared pool')total,
(select bytes/1024/1024 from v$sgastat where name='free memory' and pool='shared pool') free from dual)
union
select name,round(total,2)total,round(total-free,2) used,round(free,2) free,round((total-free)/total,2) pctused from (
select 'Default pool' name,( select a.cnum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 total from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=(select value from v$parameter where name='db_block_size')) total,
(select a.anum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 free from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=(select value from v$parameter where name='db_block_size')) free from dual)
union
select name,nvl(round(total,2),0)total,nvl(round(total-free,2),0) used,nvl(round(free,2),0) free,nvl(round((total-free)/total,2),0) pctused from (
select 'KEEP pool' name,(select a.cnum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 total from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='KEEP' and p.block_size=(select value from v$parameter where name='db_block_size')) total,
(select a.anum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 free from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='KEEP' and p.block_size=(select value from v$parameter where name='db_block_size')) free from dual)
union
select name,nvl(round(total,2),0)total,nvl(round(total-free,2),0) used,nvl(round(free,2),0) free,nvl(round((total-free)/total,2),0) pctused from (
select 'RECYCLE pool' name,( select a.cnum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 total from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='RECYCLE' and p.block_size=(select value from v$parameter where name='db_block_size')) total,
(select a.anum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 free from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='RECYCLE' and p.block_size=(select value from v$parameter where name='db_block_size')) free from dual)
union
select name,nvl(round(total,2),0)total,nvl(round(total-free,2),0) used,nvl(round(free,2),0) free,nvl(round((total-free)/total,2),0) pctused from(
select 'DEFAULT 16K buffer cache' name,(select a.cnum_repl*16/1024 total from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=16384) total,
(select a.anum_repl*16/1024 free from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=16384) free from dual)
union
select name,nvl(round(total,2),0)total,nvl(round(total-free,2),0) used,nvl(round(free,2),0) free,nvl(round((total-free)/total,2),0) pctused from(
select 'DEFAULT 32K buffer cache' name,(select a.cnum_repl*32/1024 total from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=32768) total,
(select a.anum_repl*32/1024 free from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=32768) free from dual)
union
select name,total,total-free used,free, (total-free)/total*100 pctused from (
select 'Java Pool' name,(select sum(bytes/1024/1024) total from v$sgastat where pool='java pool' group by pool)total,
( select bytes/1024/1024 free from v$sgastat where pool='java pool' and name='free memory')free from dual)
union
select name,Round(total,2),round(total-free,2) used,round(free,2) free, round((total-free)/total*100,2) pctused from (
select 'Large Pool' name,(select sum(bytes/1024/1024) total from v$sgastat where pool='large pool' group by pool)total,
( select bytes/1024/1024 free from v$sgastat where pool='large pool' and name='free memory')free from dual)
order by pctused desc;
1.1.25. 查询第 n到到M条记录
select * from (
select rownum rn , t.* from table table t
) where rn between n and m
1.1.26. 多列数据分组统计
SELECT *，SUM(字段1*字段2) AS (新字段1) FROM 表名 GROUP BY 字段 ORDER BY 新字段1 DESC
SELECT id,name,SUM(price*num) AS sumprice FROM tb_price GROUP BY pid ORDER BY sumprice DESC;
1.1.27. 多表分组统计
SELECT a.name,AVG(a.price),b.name,AVG(b.price) FROM tb_demo058 AS a,tb_demo058_1 AS b WHERE a.id=b.id GROUP BY b.type;
1.1.28. 23.查看oracle数据库的状态
V$INSTANCE
displays the state of the current instance.
select * from v$instance;
11g:
select INSTANCE_NUMBER,INSTANCE_NAME,VERSION,to_char(STARTUP_TIME,'YYYY-MM-DD hh24:mi:ss'),STATUS,THREAD#, LOGINS,DATABASE_STATUS,INSTANCE_ROLE,ACTIVE_STATE from v$instance;
12C
select INSTANCE_NUMBER,INSTANCE_NAME,VERSION,to_char(STARTUP_TIME,'YYYY-MM-DD hh24:mi:ss'),STATUS,THREAD#, LOGINS,DATABASE_STATUS,INSTANCE_ROLE,ACTIVE_STATE,INSTANCE_MODE,DATABASE_TYPE from v$instance;
以下视图为12c版本：
1.1.29. 查看缓冲区命中率是否需要调优
select 1 - ((physical.value - direct.value - lobs.value) / logical.value) "Buffer Cache Hit Ratio"
from v$sysstat physical,v$sysstat direct,v$sysstat lobs,v$sysstat logical
where physical.name = 'physical reads'
and direct.name='physical reads direct'
and lobs.name='physical reads direct (lob)'
and logical.name='session logical reads';
当命中率>90%说明命中率很高了
获取推荐值(单位为M)
size_for_estimate表示预计的数据库高速缓冲区的大小（即：db_cache_size的值）。
ESTD_PHYSICAL_READS 表示预计的物理读。
当增加数据库高速缓冲区的的时候，物理读（ESTD_PHYSICAL_READS）越少，说明命中率越高。优化缓冲区大小、提高服务器的命中率
select name,size_for_estimate,v$db_cache_advice.ESTD_PHYSICAL_READS from v$db_cache_advice where block_size='8192' and advice_status='ON';
NAME SIZE_FOR_ESTIMATE ESTD_PHYSICAL_READS
-------------------- ----------------- -------------------
DEFAULT 24 5463
DEFAULT 48 5269
DEFAULT 72 5269
DEFAULT 96 5269
DEFAULT 120 5269
DEFAULT 144 5269
DEFAULT 168 5269
或者：
select size_for_estimate, buffers_for_estimate ,ESTD_PHYSICAL_READ_factor,ESTD_PHYSICAL_READS from v$db_cache_advice where block_size='8192' and advice_status='ON';
可以发现当SIZE_FOR_ESTIMATE = 48M 之后 ESTD_PHYSICAL_READS固定不再发生变化,所以我们设置DB_CHACHE_SIZE的值为48M
修改DB_CHACHE_SIZE为48M
分析：
SIZE_FOR_ESTIMATE M 为单位：
当SIZE_FOR_ESTIMATE=80M 的时候 ESTD_PHYSICAL_READS=1436311200
当SIZE_FOR_ESTIMATE=1120M 的时候 ESTD_PHYSICAL_READS=9552903
当SIZE_FOR_ESTIMATE=1280M 的时候 ESTD_PHYSICAL_READS=7438186
之后ESTD_PHYSICAL_READS固定了
所以应该过大db_cache_size=1120M的值使得
alter system set db_cache_size=48M;
重启数据库
关于physical reads ，db block gets 和consistent gets这三个参数之间有一个换算公式：
数据缓冲区的使用命中率=1 - ( physical reads / (db block gets + consistent gets) )。
用以下语句可以查看数据缓冲区的命中率：
SELECT name, value FROM v$sysstat WHERE name IN ('db block gets', 'consistent gets','physical reads');
4.修改发现DB_cache_size太大了。过大SGA区域解决
alter system set db_cache_size=1120M
--sga设置太小了导致
SQL> alter system set db_cache_size=1120M;
alter system set db_cache_size=1120M
*
ERROR 位于第 1 行:
ORA-02097: 无法修改参数，因为指定的值无效
ORA-00384: 没有足够的内存来增加高速缓存的大小
1.1.30. 将查询的数据输出到文件中：
spool E:\log.txt;
select id,name from users;
spool off;
1.1.31. 查看并修改undo_retention：
show parameter undo_retention;
alter system set undo_retention=36000 scope=both;
如果查询慢了，可以收集表的统计信息
按照百分比采样分析
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'MS50_FLOW',tabname=>'FLOW_GRAPH_DATA',ESTIMATE_PERCENT=>100,method_opt=>'for all columns size auto',cascade=>true,force=>true,degree=>8);
grant select on MS50_FLOW.FLOW_GRAPH_DATA to system;
revoke select on MS50_FLOW.FLOW_GRAPH_DATA from system;
1.1.32. 查看最近执行过的sql
1.1.32.1. 按照时间查看
SELECT b.SQL_TEXT,b.FIRST_LOAD_TIME,b.SQL_FULLTEXT
FROM v$sqlarea b
WHERE b.FIRST_LOAD_TIME between '1999-01-15/09:24:47' and
'2021-12-15/09:24:47' order by b.FIRST_LOAD_TIME
1.1.32.2. 查看当前会话中正在执行的sql：
SELECT a.username, a.sid,b.SQL_TEXT, b.SQL_FULLTEXT
FROM v$session a, v$sqlarea b
where a.sql_address = b.address;
SELECT b.sql_text, --content of SQL
a.machine, --which machine run this code
a.username, a.module, -- the method to run this SQL
c.sofar / totalwork * 100, --conplete percent
c.elapsed_seconds, --run time
c.time_remaining --remain to run time
FROM v$session a, v$sqlarea b, v$session_longops c
WHERE a.sql_hash_value = b.hash_value(+) AND a.SID = c.SID(+)
AND a.serial# = c.serial#(+)
--AND a.sid=13
and a.username='SCOTT';
1.1.32.3. 查看最近执行的sql
SELECT sql_text, last_load_time
FROM v$sql
WHERE last_load_time IS NOT NULL
ORDER BY last_load_time DESC
SELECT sql_text, last_load_time FROM v$sql WHERE last_load_time IS NOT NULL and sql_text like 'select%' ORDER BY last_load_time DESC;
SELECT sql_text, last_load_time FROM v$sql WHERE last_load_time IS NOT NULL and last_load_time like' 14-06-09%' ORDER BY last_load_time DESC;
1.1.32.4. 查找前十条性能差的sql
SELECT * FROM (SELECT PARSING_USER_ID,EXECUTIONS,SORTS,
COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea
ORDER BY disk_reads DESC )WHERE ROWNUM<10 ;
1.1.32.5. 查看占io较大的正在运行的session
SELECT se.sid,se.serial#,pr.SPID,se.username,se.status,
se.terminal,se.program,se.MODULE,se.sql_address,st.event,st.
p1text,si.physical_reads,
si.block_changes FROM v$session se,v$session_wait st,
v$sess_io si,v$process pr WHERE st.sid=se.sid AND st.
sid=si.sid AND se.PADDR=pr.ADDR AND se.sid>6 AND st.
wait_time=0 AND st.event NOT LIKE '%SQL%' ORDER BY physical_reads DESC;
1.1.32.6. 查询表的DDL时间
select OWNER,OBJECT_NAME,OBJECT_TYPE,TO_CHAR(LAST_DDL_TIME,'YYY-MM-DD HH24:MI:SS') from dba_objects where LAST_DDL_TIME > sysdate-131 and OWNER='PCARD' and OBJECT_TYPE='TABLE';
1.1.33. 查询当前数据库中的TX锁，并按照锁定的关系显示：
set lin240 pages999;
col LEVEL1 for a10;
col EVENT for a40;
select lpad('-',level,'-')||'>'
level1,sid,SERIAL#,EVENT,BLOCKING_SESSION,SECONDS_IN_WAIT
from v$session
start with event like 'enq: TX%'
connect by sid = prior blocking_session;
1.2. 查询重复数据
使用下面的语句查看重复的数据
select uniscid,count(uniscid) from YZYM_E_CONTACT group by uniscid having count(uniscid)>1;
使用rowid删除重复值：
DELETE FROM dept_bak WHERE ROWID NOT IN( SELECT MIN(ROWID) FROM dept_bak GROUP BY DEPTNO);
1.3. 查看表空间的磁盘碎片情况
如果磁盘的碎片过多 ，我们可以通过命令整理磁盘
set linesize 300
set pagesize 900
select tablespace_name,count(*),max(bytes/1024/1024) max_chunk
from dba_free_space
group by tablespace_name;
碎片整理：alter tablespace 表空间名称 coalesce
如果发现碎片数量没有减少的话 那将会严重影响数据库的运行
我们就可以考虑对该表空间进行重建
ORA-1652/ORA-1653/ORA-1654，的错误信息，DBA应该及时对表空间进行扩充，以避免发生这些错误
表碎片查看
SELECT table_name, ROUND ((blocks * 8), 2)/1024 "高水位空间 MB",
ROUND ((num_rows * avg_row_len / 1024), 2)/1024 "真实使用空间 MB",
ROUND ((blocks * 10 / 100) * 8, 2)/1024 "预留空间(pctfree) MB",
ROUND (( blocks * 8
- (num_rows * avg_row_len / 1024)
- blocks * 8 * 10 / 100
),
2
)/1024 "浪费空间 MB"
FROM dba_tables
WHERE table_name in ('PRM_PRICEFORM_P',
'PRM_PRICEFORM_B_P') order by "浪费空间 MB" desc;
1.4. ORACLE查看并发数
1.4.1. ORACLE查看最大并发数和当前并发数查看
查看oracle的最大并发数限制，可是查看v$license视图
里面记录了Oracle最大的并发数以及当前用户的连接数，
官方文档有如下描述：
This view contains information about license limits.
其实，该试图中的SESSIONS_CURRENT就等于
select count(*) from v$session where TYPE = 'USER';
查看最大并发数
show parameter license_max_sessions //查看最大并发数，如果是0，则默认是无限制，但如果在 在初始化文件里说明了，就以初始化文件为主
1.5. oracle 手动锁表
手工锁表：
lock table tbl_t1 in row share mode nowait; --2
lock table tbl_t1 in share update mode nowait; --2
lock table tbl_t1 in row exclusive mode nowait; --3
lock table tbl_t1 in share mode nowait; --4
lock table tbl_t1 in share row exclusive mode nowait; --5
lock table tbl_t1 in exclusive mode nowait; --6
0：none
1：null 空
2：Row-S 行共享(RS)：共享表锁
3：Row-X 行专用(RX)：用于行的修改
4：Share 共享锁(S)：阻止其他DML操作
5：S/Row-X 共享行专用(SRX)：阻止其他事务操作
6：exclusive 专用(X)：独立访问使用
手工解锁：
rollback/commit
1.6. 通过触发器更新修改时间
在mysql里，我们通常在创建表时会设置一个创建时间（create_time）和一个修改时间（update_time），然后给创建时间设置默认值CURRENT_TIMESTAMP，给修改时间设置默认值CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP，这样就能让创建时间和修改时间在插入数据时自动插入当前时间，而在修改数据时，自动更新修改时间。
而在Oracle中我们可以通过给创建时间和修改时间设置默认值为sysdate，但是，却不能给修改时间设置为在修改数据时自动更新修改时间。
create or replace trigger trig_students_auto_update_time
before
update on students
for each row
begin
select sysdate into :NEW.update_time from dual;
end;
1.7. 查询用户连接，并拼接杀会话的语句
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program,
s.paddr,
s.STATUS,
'ALTER SYSTEM KILL SESSION ''' || s.sid || ', ' || s.serial# || '''' || ';' AS KILL
FROM gv$session s
JOIN gv$process p
ON p.addr = s.paddr
AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND'
AND S.USERNAME = 'GJNC65';
1.8. 系统时格式设置（会话级别）
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
1.9. 查看日志文件位置
show parameter dump_dest
