常用查询

1. oralce常用的查询

目录

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;

img

![img](file:///d:/Documents/WXWork/1688853079754486/Cache/Image/2021-04/企业微信截图_16182090641263.png)

$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版本：
v$instace

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
posted @ 2023-06-21 15:43  数据库小白（专注）  阅读(14)  评论(0)  编辑  收藏  举报