Oracle19c常用查询SQL语句-01

查询数据库基本信息

检查database版本信息

select * from v$version;

数据库所在服务器的版本信息

select * from dba_registry_database;

数据库实例信息

select * from v$instance;

数据库是否存在CDB

select CDB from V$DATABASE;

数据库启动文件类型

SELECT 
  DECODE(
    COUNT(*), 
    1, 
    'spfile', 
    'pfile'
  ) 
FROM 
  V$SPPARAMETER 
WHERE 
  ROWNUM = 1 
  AND ISSPECIFIED = 'TRUE';

数据库安装项信息

SELECT PARAMETER,VALUE,CON_ID FROM V$OPTION;；

数据库组件安装信息

select * from dba_registry;

数据库等待事件统计与执行的sql信息

--查看等待事件统计
SELECT EVENT#,EVENT,COUNT(*) FROM V$SESSION GROUP BY EVENT#,EVENT ORDER BY 3;

--根据event#编号获取等待事件最多的sql_id
SELECT 
  SQL_ID, 
  USERNAME, 
  COUNT(*) 
FROM 
  GV$SESSION 
WHERE 
  EVENT#=7 GROUP BY SQL_ID,USERNAME ORDER BY 3;

--根据sql_id获取该sql的执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('211qaa18pkbyw',NULL));

数据表大小

SELECT
    segment_name,
    segment_type,
    tablespace_name,
    SUM(bytes) / 1024 / 1024 "USED_SPACE(MB)"
FROM
    dba_extents
WHERE
    1 = 1
    --AND TABLESPACE_NAME='表空间名'
 GROUP BY
    segment_name,
    segment_type,
    tablespace_name
ORDER BY
    "USED_SPACE(MB)" DESC;
 
 
SELECT
    owner,
    segment_name,
    segment_type,
    tablespace_name,
    SUM(bytes) / 1024 / 1024 "USED_SPACE(MB)"
FROM
    dba_segments
WHERE
    1 = 1
 --AND TABLESPACE_NAME='表空间名'
 GROUP BY
    owner,
    segment_name,
    segment_type,
    tablespace_name
ORDER BY
    "USED_SPACE(MB)" DESC;

检查无效的数据库对象

SELECT 
  OWNER, 
  OBJECT_NAME, 
  OBJECT_TYPE, 
  STATUS 
FROM 
  DBA_OBJECTS 
WHERE 
  STATUS = 'INVALID';

检查无效的约束

SELECT 
  OWNER, 
  CONSTRAINT_NAME, 
  TABLE_NAME, 
  CONSTRAINT_TYPE, 
  STATUS 
FROM 
  DBA_CONSTRAINTS 
WHERE 
  STATUS = 'DISABLED';

检查无效的触发器

SELECT 
  OWNER, 
  TRIGGER_NAME, 
  TABLE_NAME, 
  STATUS 
FROM 
  DBA_TRIGGERS 
WHERE 
  STATUS = 'DISABLED';

检查无效的索引

SELECT 
  INDEX_NAME, 
  TABLE_NAME, 
  TABLESPACE_NAME, 
  STATUS 
FROM 
  DBA_INDEXES 
WHERE 
  STATUS<>'VALID';