DBA 必看：Oracle 许可合规性检查终极指南

大家好，这里是 DBA 学习之路，专注于提升数据库运维效率。

目录

• 前言
• 数据库版本确认
• 组件安装状态检查
• • 查看已安装组件
• 功能使用情况监控
• • DBA_FEATURE_USAGE_STATISTICS
  • MMON 的采样机制解析
• 组件使用分析
• • Oracle Text
  • Oracle Workspace Manager
  • Spatial
  • XML Database (XDB)
  • OLAP
  • OJVM
  • APEX
• 总结

前言

最近有朋友问，如何查看 Oracle 数据库当前安装了哪些功能组件，以及这些功能的特性是否被实际使用了？乍一听，这个问题不简单。于是我在 MOS 上查找了一些资料，整理出一套完整的方法，希望对大家有所帮助。

作为DBA，您是否遇到过这些困惑：

• 数据库安装了哪些功能组件？
• 这些组件是否真的被业务使用？
• 如何避免为未使用的功能支付不必要的许可证费用？

本文将带您全面掌握 Oracle 功能组件的检查方法，助你实现：

• ✅ 精准掌握组件安装状态
• ✅ 深度分析功能使用情况
• ✅ 合规规避许可证风险
• ✅ 优化数据库性能配置

这些方法可以帮助 DBA 全面了解 Oracle 数据库中功能组件的安装及使用情况，尤其在许可证合规性检查中非常有用。

数据库版本确认

检查数据库版本：

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

企业版与标准版功能差异显著，建议先确认版本：

• 企业版：包含全部高级功能（OLAP、Data Guard 等）
• 标准版：功能受限（无分区表、高级压缩等）

📌 官方文档参考：Database Licensing Information

组件安装状态检查

查看已安装组件

可以通过 DBA_REGISTRY 视图查看当前数据库中已安装的功能组件：

• VALID：组件已安装且正常；
• INVALID：组件异常需修复；
• OPTION OFF：组件已禁用；

参考如下查询结果：

COL comp_id FORMAT A12
COL version FORMAT A10
COL comp_name FORMAT A40
COL status FORMAT A10
SET pages 30
SELECT SUBSTR(comp_id,1,12) AS comp_id,
       status,
       SUBSTR(version,1,10) AS version,
       SUBSTR(comp_name,1,40) AS comp_name
FROM dba_registry
ORDER BY 1, 2;

COMP_ID      STATUS     VERSION    COMP_NAME
------------ ---------- ---------- ----------------------------------------
APS          VALID      19.0.0.0.0 OLAP Analytic Workspace
CATALOG      VALID      19.0.0.0.0 Oracle Database Catalog Views
CATJAVA      VALID      19.0.0.0.0 Oracle Database Java Packages
CATPROC      VALID      19.0.0.0.0 Oracle Database Packages and Types
CONTEXT      VALID      19.0.0.0.0 Oracle Text
DV           VALID      19.0.0.0.0 Oracle Database Vault
JAVAVM       VALID      19.0.0.0.0 JServer JAVA Virtual Machine
OLS          VALID      19.0.0.0.0 Oracle Label Security
ORDIM        VALID      19.0.0.0.0 Oracle Multimedia
OWM          VALID      19.0.0.0.0 Oracle Workspace Manager
RAC          OPTION OFF 19.0.0.0.0 Oracle Real Application Clusters
SDO          VALID      19.0.0.0.0 Spatial
XDB          VALID      19.0.0.0.0 Oracle XML Database
XML          VALID      19.0.0.0.0 Oracle XDK
XOQ          VALID      19.0.0.0.0 Oracle OLAP API

这些组件是在数据库安装之初进行选择安装的，无法禁用，与是否使用也无关，非必要情况下也不建议去卸载。

功能使用情况监控

DBA_FEATURE_USAGE_STATISTICS

DBA_FEATURE_USAGE_STATISTICS 是 Oracle 提供的关键数据字典视图，用于跟踪数据库功能使用情况（从数据库上次启动开始，每 7 天采样一次），非常适用于许可证合规性管理和容量规划。

一键查看当前已启用特性：

SQL> ALTER SESSION SET nls_date_format = 'dd-mon-yy hh24:mi:ss';
SET lines 2222 pages 1000
COL name FOR a60
COL currently_used FOR a5
COL last_usage_date FOR a20

SELECT name, currently_used, last_usage_date, description
FROM dba_feature_usage_statistics
WHERE currently_used = 'TRUE';

这个视图的数据更新机制如下：

• 自动采样：由 MMON 进程每周自动采样；
• 手动刷新：可调用 DBMS_FEATURE_USAGE_INTERNAL 包进行强制刷新；
• 数据保留：一般保留约一年；
• 注意延迟：新启用的功能可能需要数日才会出现在视图中；

MMON 的采样机制解析

可通过以下方式 Trace MMON 的行为，深入了解 Oracle 的采样逻辑：

SQL> BEGIN
  dbms_monitor.serv_mod_act_trace_enable(
    service_name => 'SYS$BACKGROUND',
    module_name  => 'MMON_SLAVE',
    action_name  => 'Auto-DBFUS Action');
END;
/

SQL> ALTER SESSION SET "_swrf_test_action" = 28;

SQL> ALTER SESSION SET EVENTS 'immediate trace name mmon_test level 6';

然后通过命令提取 trace 文件中的 SQL：

grep -A 1 "sqlid='" $ORACLE_BASE/diag/rdbms/<db_name>/<instance>/trace/<file>.trc | grep -v "sqlid='"

Trace 中会显示大量底层采样 SQL，例如：

SELECT name, inst_chk_method, inst_chk_logic, usg_det_method, usg_det_logic
FROM wri$_dbu_feature_metadata mt, wri$_dbu_feature_usage fu
WHERE mt.name = fu.name
  AND fu.dbid = :dbid
  AND fu.version = :version;

通过这些采样 SQL，Oracle 能够自动判断哪些功能被使用，例如：

• Data Guard 是否启用
• 是否使用 SecureFiles
• 是否进行过表恢复、跨平台迁移等 RMAN 操作
• 分区表是否存在
• 是否使用并行执行、Adaptive Plan 等

上述这些组件的收费情况可以参考文章：

Oracle授权如何购买？多少钱？如何计算？

比如其中部分功能特性的收费情况：

大家可以根据自己的数据库版本以及功能特性使用情况，完全可以大致预估出来费用了。

组件使用分析

很多人关心数据库组件的使用情况，通过上述方式可能无法精确查询，针对这个问题，我也整理了一些检查组件是否被使用的方法。

Oracle Text

检查当前数据库是否使用 Text 组件：

SQL> SET pages 999 lines 120 trimout ON trimspool ON
COL objdep format a55 hea "Object Name (Type)"
COL refr format a35 hea "Object Reference (Type)"

SELECT OWNER||'.'||name||' ('||decode(TYPE, 'MATERIALIZED VIEW', 'MV', 'DIMENSION', 'DIM', 'EVALUATION CONTXT', 'EVALCTXT', 'PACKAGE BODY', 'PKGBDY', 'CUBE.DIMENSION', 'CUBE.DIM', TYPE)||')' objdep,
       referenced_owner||'.'||referenced_name||' ('||decode(referenced_type, 'EVALUATION CONTXT', 'EVALCTXT', 'NON-EXISTENT CONTXT', 'NO-EXIST', 'PACKAGE BODY', 'PKGBDY', 'CUBE.DIMENSION', 'CUBE.DIM', referenced_type)||')' refr
FROM dba_dependencies
WHERE referenced_OWNER ='CTXSYS'
  AND OWNER NOT IN ('PUBLIC', 'CTXSYS', 'SYS')
ORDER BY 1;

如果需要卸载 Oracle Text，可以参考下方步骤：

conn / as sysdba
@?/ctx/admin/catnoctx.sql
drop procedure sys.validate_context;

Oracle Workspace Manager

检查当前数据库是否使用 Workspace Manager：

SQL> REM srdc_owm_usage.sql - collect Oracle Workspace Manager usage
define SRDCNAME='OWM_USAGE'
SET MARKUP HTML ON PREFORMAT ON
set TERMOUT off FEEDBACK off VERIFY off TRIMSPOOL on HEADING off
COLUMN SRDCSPOOLNAME NOPRINT NEW_VALUE SRDCSPOOLNAME
select 'SRDC_'||upper('&&SRDCNAME')||'_'||upper(instance_name)||'_'||
        to_char(sysdate,'YYYYMMDD_HH24MISS') SRDCSPOOLNAME from v$instance;
set TERMOUT on MARKUP html preformat on
REM
spool &&SRDCSPOOLNAME..htm
select '+----------------------------------------------------+' from dual
union all
select '| Diagnostic-Name: '||'&&SRDCNAME' from dual
union all
select '| Timestamp:       '||
        to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS TZH:TZM') from dual
union all
select '| Machine:         '||host_name from v$instance
union all
select '| Version:         '||version from v$instance
union all
select '| DBName:          '||name from v$database
union all
select '| Instance:        '||instance_name from v$instance
union all
select '+----------------------------------------------------+' from dual
/
set HEADING on MARKUP html preformat off
REM === -- end of standard header -- ===
select comp_name,version,status from dba_registry where comp_id = 'OWM' ;
select table_name,owner,state from DBA_WM_VERSIONED_TABLES;

select workspace, parent_workspace, owner, freeze_status, resolve_status  from dba_workspaces;
select version, parent_version, workspace from ALL_VERSION_HVIEW;
spool off;
exit

执行结果：

如果是安装未使用则显示以上结果。

卸载 Workspace Manager 组件：

-- sys 用户执行
SQL> @$ORACLE_HOME/rdbms/admin/owmuinst.plb

Spatial

检查当前数据库是否使用 Spatial：

SQL> col owner format a12
col table_name format a35
col column_name format a25
select owner, table_name, column_name
from dba_tab_columns
where data_type = 'SDO_GEOMETRY'
and owner != 'MDSYS';

no rows selected

如果数据库中不存在包含 SDO_GEOMETRY 列的表，则表示该数据库未使用 Spatial 功能。

卸载 Spatial 的步骤如下：

conn / as sysdba

-- 检查组件是否被使用
set pages 200
col owner for a20
col table_name for a30
col column_name for a25
select owner, table_name, column_name
from dba_tab_columns
where data_type = 'SDO_GEOMETRY'
and owner != 'MDSYS'
order by 1,2,3;

-- 如果被使用，则需要先清理对象
-- drop Spatial indexes
set pagesize 0
spool DropIndexes.sql
select 'drop index ' || owner || '.' || index_name ||';'
from dba_indexes where ityp_name = 'SPATIAL_INDEX';
spool off

@DropIndexes.sql

-- drop Spatial tables
set pagesize 0
spool DropTables.sql
select 'drop table '|| owner ||'.'|| table_name||';'
from dba_tab_columns
where data_type = 'SDO_GEOMETRY'
and owner != 'MDSYS';
spool off

@DropTables.sql

-- 清空回收站
purge dba_recyclebin;

-- remove Semantic support
@?/md/admin/semremov.sql

-- drop MDSYS user
drop user MDSYS cascade;

-- 一键卸载(多租户环境下也可以)，oracle 用户下执行，不在 sqlplus 执行：
[oracle@emcc:/home/oracle]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -e -u SYS -r -b SDO_deinst -d $ORACLE_HOME/md/admin deinssdo.sql
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/home/oracle/SDO_deinst_catcon_2719568.lst]

catcon::set_log_file_base_path: catcon: See [/home/oracle/SDO_deinst*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/home/oracle/SDO_deinst_*.lst] files for spool files, if any

Enter Password: 
catcon.pl: completed successfully

-- drop all remaining public synonyms created for Spatial
set pagesize 0
set feed off lines 120 trimout on trimspool on
spool dropsyn.sql
select 'drop public synonym "' || synonym_name || '";' from dba_synonyms where table_owner='MDSYS';
spool off;
@dropsyn.sql

-- drop related user
drop user mddata cascade;
-- Only created as of release 11g:
drop user spatial_csw_admin_usr cascade;
drop user spatial_wfs_admin_usr cascade;

IMPORTANT NOTE: Spatial will still appear in V$OPTION but this is expected behavior as explained in Note:273573.1 - Removed Spatial Option But Spatial Still Appears In V$Option

XML Database (XDB)

检查当前数据库是否使用 XDB：

SQL> define SRDCNAME='XDB_USAGE_CHECK'
set pagesize 200 verify off sqlprompt "" term off entmap off echo off
set markup html on spool on
COLUMN SRDCSPOOLNAME NOPRINT NEW_VALUE SRDCSPOOLNAME
select 'SRDC_'||upper('&&SRDCNAME')||'_'||upper(instance_name)||'_'||
       to_char(sysdate,'YYYYMMDD_HH24MISS') SRDCSPOOLNAME from v$instance;
spool &&SRDCSPOOLNAME..htm
select 'Diagnostic-Name: ' || '&&SRDCNAME'  as "SRDC COLLECTION HEADER"  from dual
union all
select 'Time: ' || to_char(systimestamp, 'YYYY-MM-DD HH24MISS TZHTZM' ) from dual
union all
select 'Machine: ' || host_name from v$instance
union all
select 'Version: '|| version from v$instance
union all
select 'DBName: '||name from v$database
union all
select 'Instance: '||instance_name from v$instance
/
set serveroutput on
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'
/

define LOWTHRESHOLD=10
define MIDTHRESHOLD=62
define VERBOSE=TRUE

set veri off;
set feedback off;
REM === -- end of standard header -- ===
set lines 150 trimspool on pages 50000 long 100000 tab off
set serveroutput on
set HEADING on MARKUP html preformat off
declare
  --define cursors
  --check for version
  cursor c_ver is select version from v$instance;
  --check for invalids owned by XDB
  cursor c_inval is select * from dba_objects where status='INVALID' and OWNER in ('SYS','XDB');
  -- Check status of other database features
  cursor c_feat is select comp_name,status,version from dba_registry;
  --check for xml type tables
  cursor c_xml_tabs is select owner,storage_type,count(*) "TOTAL" from dba_xml_tables group by owner,storage_type;
  --check for xml type colmns
  cursor c_xml_tab_cols is select owner,storage_type,count(*) "TOTAL" from dba_xml_tab_cols group by owner,storage_type;
  --check for xml type views
  cursor c_xml_vw is select owner,count(*) "TOTAL" from dba_xml_views group by owner;
  --check for xml type Indexes
  cursor c_xml_idx is select index_owner,type,count(*) "TOTAL" from dba_xml_indexes group by index_owner,type;
  --check for API's bbuilt with XML API's
  cursor c_api is select owner,name,type from dba_dependencies where referenced_name in
    (select object_name from dba_objects
     where object_name like 'DBMS_XML%' or object_name like 'DBMS_XSL%')
       and TYPE !='SYNONYM' and owner !='SYS';
  --check for registered Schemas
  cursor c_xml_schemas is select owner,count(*) "TOTAL" from dba_xml_schemas group by owner;
  --check for user defined resources in the repository
  cursor c_res is select distinct (a.username) "USER",count (r.xmldata) "TOTAL"
    from dba_users a, xdb.xdb$resource r
    where sys_op_rawtonum (extractvalue (value(r),'/Resource/OwnerID/text()')) =a.USER_ID group by a.username;
  -- check xdbconfig.xml values
  cursor c_config is select value(x).GETROOTELEMENT() NODENAME, extractValue(value(x),'/*') NODEVALUE
    from table(xmlsequence(extract(xdburitype('/xdbconfig.xml').getXML(),'//*[text()]'))) x;
  --check for Network ACLs
  cursor c_net_acls is select host, nvl(trim(lower_port),'NULL') l_port, nvl(trim(upper_port),'NULL') u_port from dba_network_acls;
  --define variables for fetching data from cursors
  v_ver c_ver%ROWTYPE;
  v_inval c_inval%ROWTYPE;
  v_feat c_feat%ROWTYPE;
  v_xml_tabs c_xml_tabs%ROWTYPE;
  v_xml_tab_cols c_xml_tab_cols%ROWTYPE;
  v_xml_vw c_xml_vw%rowtype;
  v_xml_idx c_xml_idx%rowtype;
  v_api c_api%rowtype;
  v_c_net_acls c_net_acls%rowtype;
  v_xml_schemas c_xml_schemas%rowtype;
  v_res c_res%ROWTYPE;
  v_config c_config%rowtype;
  -- Static variables
  v_errcode NUMBER := 0;
  v_errmsg varchar2(50) := ' ';
  l_dad_names DBMS_EPG.varchar2_table;
  --stylesheet for xdbconfig.xml reading
  v_style clob :='';
begin
  open c_ver;
 fetch c_ver into v_ver;
 --check minimum XDB requirements
 if v_ver.version like '9.%' or v_ver.version like '10.%' then
  DBMS_OUTPUT.PUT_LINE('!!!!!!!!!!!!! UNSUPPORTED VERSION !!!!!!!!!!!!!');
  DBMS_OUTPUT.PUT_LINE('Minimun version is 11.2.0.4. actual version is: '||v_ver.version);
 end if;
 DBMS_OUTPUT.PUT_LINE('############# Status/Version #############');
 DBMS_OUTPUT.PUT_LINE('XDB Status is: '||dbms_registry.status('XDB')||' at version '||dbms_registry.version('XDB'));
 if v_ver.version != dbms_registry.version('XDB') then
  DBMS_OUTPUT.PUT_LINE('Database is at version '||v_ver.version||' XDB is at version '||dbms_registry.version('XDB'));
 end if;
 --Check Status. If invalid, gather invalid objects list and check for usage. If valid, simply check for usage
 if dbms_registry.status('XDB') != 'VALID' then
  DBMS_OUTPUT.PUT_LINE('############# Invalid Objects #############');
  open c_inval;
  loop
   fetch c_inval into v_inval;
   DBMS_OUTPUT.PUT_LINE('Type: '||v_inval.object_type||' '||v_inval.owner||'.'||v_inval.object_name);
   exit when c_inval%NOTFOUND;
  end loop;
  close c_inval;
 end if;
 -- Check XDBCONFIG.XML paramareters
 DBMS_OUTPUT.PUT_LINE('############# OTHER DATABASE FEATURES #############');
 open c_feat;
  loop
   fetch c_feat into v_feat;
   exit when c_feat%NOTFOUND;
   if c_feat%rowcount >0 then
    DBMS_OUTPUT.PUT_LINE(v_feat.comp_name||' is '||v_feat.status||' at version '||v_feat.version);
   else DBMS_OUTPUT.PUT_LINE('No Data Found');
   end if;
  end loop;
 close c_feat;
 -- Check XDBCONFIG.XML paramareters
 DBMS_OUTPUT.PUT_LINE('############# XDBCONFIG INFORMATION #############');
 open c_config;
  loop
   fetch c_config into v_config;
   exit when c_config%NOTFOUND;
   if c_config%rowcount >0 then
    DBMS_OUTPUT.PUT_LINE(v_config.NODENAME||'= = = '||v_config.NODEVALUE);
   else DBMS_OUTPUT.PUT_LINE('No Data Found');
   end if;
  end loop;
 close c_config;
 -- Check if they have any xmltype tables or columns and if they are schema based, clob or binary
 DBMS_OUTPUT.PUT_LINE('############# XMLTYPE Tables #############');
 open c_xml_tabs;
  loop
   fetch c_xml_tabs into v_xml_tabs;
   exit when c_xml_tabs%NOTFOUND;
   DBMS_OUTPUT.PUT_LINE(v_xml_tabs.owner||' has '||v_xml_tabs.TOTAL||' XMLTYPE TABLES stored as '||v_xml_tabs.storage_type);
  end loop;
 close c_xml_tabs;
 DBMS_OUTPUT.PUT_LINE('############# XMLTYPE Columns #############');
 open c_xml_tab_cols;
  loop
   fetch c_xml_tab_cols into v_xml_tab_cols;
   exit when c_xml_tab_cols%NOTFOUND;
   if c_xml_tab_cols%rowcount > 0 then
    DBMS_OUTPUT.PUT_LINE(v_xml_tab_cols.owner||' has '||v_xml_tab_cols.TOTAL||' XMLTYPE Columns stored as ' ||v_xml_tab_cols.storage_type);
   else DBMS_OUTPUT.PUT_LINE('No Data Found');
   end if;
  end loop;
 close c_xml_tab_cols;
 DBMS_OUTPUT.PUT_LINE('############# XMLTYPE Views #############');
 open c_xml_vw;
  loop
   fetch c_xml_vw into v_xml_vw;
   exit when c_xml_vw%NOTFOUND;
   if c_xml_vw%rowcount > 0 then
    DBMS_OUTPUT.PUT_LINE(v_xml_vw.owner||' has '||v_xml_vw.TOTAL||' XMLTYPE Views');
   else DBMS_OUTPUT.PUT_LINE('No Data Found');
   end if;
  end loop;
 close c_xml_vw;
 DBMS_OUTPUT.PUT_LINE('############# XMLTYPE INDEXES #############');
 open c_xml_idx;
  loop
   fetch c_xml_idx into v_xml_idx;
   exit when c_xml_idx%NOTFOUND;
   if c_xml_idx%rowcount > 0 then
    DBMS_OUTPUT.PUT_LINE(v_xml_idx.index_owner||' has '||v_xml_idx.TOTAL||' XMLTYPE Indexes of type '||v_xml_idx.type);
   else DBMS_OUTPUT.PUT_LINE('No Data Found');
   end if;
  end loop;
 close c_xml_idx;
 DBMS_OUTPUT.PUT_LINE('############# Items built with XML API''s #############');
 open c_api;
  loop
   fetch c_api into v_api;
   exit when c_api%NOTFOUND;
   if c_api%rowcount > 0 then
    DBMS_OUTPUT.PUT_LINE(v_api.type||' '||v_api.owner||'.'||v_api.name);
   else DBMS_OUTPUT.PUT_LINE('No Data Found');
   end if;
  end loop;
 close c_api;
 DBMS_OUTPUT.PUT_LINE('############# XML SCHEMAS #############');
 open c_xml_schemas;
  loop
   fetch c_xml_schemas into v_xml_schemas;
   exit when c_xml_schemas%NOTFOUND;
   if c_xml_schemas%rowcount >0 then
    DBMS_OUTPUT.PUT_LINE(v_xml_schemas.owner||' has '||v_xml_schemas.TOTAL||' registered.');
   else DBMS_OUTPUT.PUT_LINE('No Data Found');
   end if;
  end loop;
 close c_xml_schemas;
 -- Check for repository resources
 DBMS_OUTPUT.PUT_LINE('############# Repository Resources #############');
 open c_res;
  loop
   fetch c_res into v_res;
   exit when c_res%NOTFOUND;
   if c_res%rowcount >0 then
    DBMS_OUTPUT.PUT_LINE(v_res.USER||' has '||v_res.TOTAL||' resources.');
   else DBMS_OUTPUT.PUT_LINE('No Data Found');
   end if;
  end loop;
 close c_res;
 -- Check Network ACLS
 DBMS_OUTPUT.PUT_LINE('############# Network ACLs Configured #############');
 open c_net_acls;
  loop
   fetch c_net_acls into v_c_net_acls;
   if c_net_acls%rowcount >0 then
    DBMS_OUTPUT.PUT_LINE(v_c_net_acls.host||' has network acls configured for ports '||v_c_net_acls.l_port||' through '|| v_c_net_acls.u_port);
   else DBMS_OUTPUT.PUT_LINE('No Data Found');
   end if;
   exit when c_net_acls%NOTFOUND;
  end loop;
 close c_net_acls;
 --Check DAD configuration to see if DBMS_EPG is being used
 DBMS_OUTPUT.put_line('############# DBMS_EPG DAD USAGE #############');
 DBMS_EPG.GET_DAD_LIST (l_dad_names);
 FOR i IN 1 .. l_dad_names.count LOOP
  DBMS_OUTPUT.put_line(l_dad_names(i));
 END LOOP;
 close c_ver;
EXCEPTION
WHEN no_data_found THEN
 DBMS_OUTPUT.PUT_LINE('No Data Found');
WHEN others THEN
 v_errcode := sqlcode;
 v_errmsg := SUBSTR(sqlerrm, 1, 50);
 DBMS_OUTPUT.PUT_LINE('ERROR: '||v_errcode||': ' || v_errmsg);
end;
/
--End XDB health and usage check
SET SERVEROUTPUT OFF
Rem===========================================================================================================================================
spool off
set markup html off spool off
set sqlprompt "SQL> " term on  echo off
PROMPT
PROMPT
PROMPT REPORT GENERATED : &SRDCSPOOLNAME..htm
set verify on echo on
Rem===========================================================================================================================================
exit;

检查结果很长，这里截取一段：

11GR2 卸载 XDB 步骤：

-- 卸载 XDB
SQL> spool xdb_removal.log
set echo on;

connect / as sysdba
shutdown immediate;
startup
@?/rdbms/admin/catnoqm.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/utlrp.sql

set pagesize 1000
col owner format a8
col object_name format a35

select owner, object_name, object_type, status
from dba_objects
where status = 'INVALID' and owner = 'SYS';

spool off;

-- 清理 XDB 相关的无效对象
SQL> connect / as sysdba

-- Make XDB Dummy views
start ?/rdbms/admin/catxdbdv.sql

-- update Data Pump related objects and KU$_ views

start ?/rdbms/admin/dbmsmeta.sql
start ?/rdbms/admin/dbmsmeti.sql
start ?/rdbms/admin/dbmsmetu.sql
start ?/rdbms/admin/dbmsmetb.sql
start ?/rdbms/admin/dbmsmetd.sql
start ?/rdbms/admin/dbmsmet2.sql
start ?/rdbms/admin/catmeta.sql
start ?/rdbms/admin/prvtmeta.plb
start ?/rdbms/admin/prvtmeti.plb
start ?/rdbms/admin/prvtmetu.plb
start ?/rdbms/admin/prvtmetb.plb
start ?/rdbms/admin/prvtmetd.plb
start ?/rdbms/admin/prvtmet2.plb
start ?/rdbms/admin/catmet2.sql

REM Check to verify that all components are valid
select COMP_ID, COMP_NAME, VERSION, STATUS from dba_registry;

COMP_ID COMP_NAME                            VERSION      STATUS
------- ------------------------------------ ------------ --------
CONTEXT Oracle Text                          11.2.0.2.0   VALID
EXF     Oracle Expression Filter             11.2.0.2.0   VALID
OWM     Oracle Workspace Manager             11.2.0.2.0   VALID
CATALOG Oracle Database Catalog Views        11.2.0.2.0   VALID
CATPROC Oracle Database Packages and Types   11.2.0.2.0   VALID
RAC     Oracle Real Application Clusters     11.2.0.2.0   VALID
JAVAVM  JServer JAVA Virtual Machine         11.2.0.2.0   VALID
XML     Oracle XDK                           11.2.0.2.0   VALID
CATJAVA Oracle Database Java Packages        11.2.0.2.0   VALID

9 rows selected.

select count(*) from dba_objects where status = 'INVALID'; -- no rows

  COUNT(*)
---------
        0

从 Oracle 12.1 版本开始，XDB 组件已经无法卸载，是必备组件。

OLAP

检查当前数据库是否使用 OLAP：

SQL> select VALUE from v$OPTION where PARAMETER = 'OLAP';

VALUE
----------------------------------------------------------------
TRUE

SQL> col owner format a10

SQL> col aw_name format a20

SQL> select owner, aw_name from dba_aws;

OWNER                AW_NAME
-------------------- ------------------------------
SYS                  AWREPORT
SYS                  AWXML
SYS                  AWMD
SYS                  AWCREATE10G
SYS                  EXPRESS
SYS                  AWCREATE

如果都是 SYS 用户的对象，就说明没有使用 OLAP 组件。

卸载 OLAP：

[oracle@emcc:/home/oracle]$ chopt disable olap

Writing to /u01/app/oracle/product/19.3.0/db/install/disable_olap_2025-06-04_16-11-48PM.log...
/usr/bin/make -f /u01/app/oracle/product/19.3.0/db/rdbms/lib/ins_rdbms.mk olap_off ORACLE_HOME=/u01/app/oracle/product/19.3.0/db
/usr/bin/make -f /u01/app/oracle/product/19.3.0/db/rdbms/lib/ins_rdbms.mk ioracle ORACLE_HOME=/u01/app/oracle/product/19.3.0/db

删除 OLAPSYS 用户：

SQL> drop user OLAPSYS cascade;
@?/rdbms/admin/utlrp.sql

OJVM

检查当前数据库是否使用 OJVM：

SQL> SELECT version, status FROM dba_registry WHERE comp_id='JAVAVM';

VERSION                        STATUS
------------------------------ --------------------------------------------
19.0.0.0.0                     VALID

SQL> select * from gv$java_patching_status;

no rows selected

SQL> select count(*) from x$kglob where KGLOBTYP = 29 OR KGLOBTYP = 56;

  COUNT(*)
----------
      1313

SQL> select inst_id, name, con_id from gv$java_services order by name, con_id, inst_id;

no rows selected

SQL> col service_name format a20
col username format a20
col program format a20
set num 8
select sess.service_name, sess.username,sess.program, count(*)
from
v$session sess,
dba_users usr,
x$kgllk lk,
x$kglob
where kgllkuse=saddr
and kgllkhdl=kglhdadr
and kglobtyp in (29,56)
and sess.user# = usr.user_id
and usr.oracle_maintained = 'N'
group by sess.service_name, sess.username, sess.program
order by sess.service_name, sess.username, sess.program;

no rows selected

APEX

检查当前数据库是否使用 APEX：

SQL> select workspace, workspace_id from apex_workspaces;
select workspace, view_date, seconds_ago from apex_workspace_activity_log

卸载 APEX 步骤：

SQL> @?/apex/apxremov.sql

总结

如果你是 DBA，尤其在做合规性审核、数据库功能优化或迁移评估，这些内容一定对你有帮助。欢迎关注和分享！

---

参考资料：

• Information About DBA_FEATURE_USAGE_STATISTICS Table (Doc ID 2535290.1)
• DBA_FEATURE_USAGE_STATISTICS reports Oracle Spatial is used (Doc ID 1381022.1)
• DBA_FEATURE_USAGE_STATISTICS Does Not Show Accurate OLAP Usage (Doc ID 2729617.1)
• How Do We Determine if Oracle Text Component Is Being Used in the Database? (Doc ID 726932.1)
• How to Determine if Workspace Manager is Being Used? (Doc ID 727765.1)
• How to Install/Deinstall Oracle Workspace Manager (Doc ID 263428.1)
• How to Determine if Spatial is Being Used in the Database? (Doc ID 726929.1)
• Steps for Manual De-installation of Oracle Spatial/Locator (Doc ID 179472.1)
• How to Determine if XDB is Being Used in the Database? (Doc ID 733667.1)
• Primary Note for Oracle XML Database (XDB) Install / Deinstall (Doc ID 1292089.1)
• How To Find Out If OLAP Is Being Used (Doc ID 739032.1)
• How To Determine If OJVM Is Installed And is in use in your database (Doc ID 3012999.1)
• How to Install, Remove, Reload, Validate and Repair the JVM Component in an Oracle Database (Doc ID 2149019.1)
• How To Verify If APEX Is Being Used in an 11.x Database Installation When APEX Shows as INVALID in DBA_REGISTRY (Doc ID 1518046.1)