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

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

前言

本章目标：掌握本章核心知识点 前置要求：完成前序章节学习 预计时长：60 分钟

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

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

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

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

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

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

数据库版本确认

检查数据库版本：

SQL

1SQL> select banner from v$version;
2
3BANNER
4--------------------------------------------------------------------------------
5Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

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

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

📌 官方文档参考：Database Licensing Information

组件安装状态检查

查看已安装组件

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

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

参考如下查询结果：

SQL

1COL comp_id FORMAT A12
2COL version FORMAT A10
3COL comp_name FORMAT A40
4COL status FORMAT A10
5SET pages 30
6SELECT SUBSTR(comp_id,1,12) AS comp_id,
7       status,
8       SUBSTR(version,1,10) AS version,
9       SUBSTR(comp_name,1,40) AS comp_name
10FROM dba_registry
11ORDER BY 1, 2;
12
13COMP_ID      STATUS     VERSION    COMP_NAME
14------------ ---------- ---------- ----------------------------------------
15APS          VALID      19.0.0.0.0 OLAP Analytic Workspace
16CATALOG      VALID      19.0.0.0.0 Oracle Database Catalog Views
17CATJAVA      VALID      19.0.0.0.0 Oracle Database Java Packages
18CATPROC      VALID      19.0.0.0.0 Oracle Database Packages and Types
19CONTEXT      VALID      19.0.0.0.0 Oracle Text
20DV           VALID      19.0.0.0.0 Oracle Database Vault
21JAVAVM       VALID      19.0.0.0.0 JServer JAVA Virtual Machine
22OLS          VALID      19.0.0.0.0 Oracle Label Security
23ORDIM        VALID      19.0.0.0.0 Oracle Multimedia
24OWM          VALID      19.0.0.0.0 Oracle Workspace Manager
25RAC          OPTION OFF 19.0.0.0.0 Oracle Real Application Clusters
26SDO          VALID      19.0.0.0.0 Spatial
27XDB          VALID      19.0.0.0.0 Oracle XML Database
28XML          VALID      19.0.0.0.0 Oracle XDK
29XOQ          VALID      19.0.0.0.0 Oracle OLAP API

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

功能使用情况监控

DBA_FEATURE_USAGE_STATISTICS

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

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

SQL

1SQL> ALTER SESSION SET nls_date_format = 'dd-mon-yy hh24:mi:ss';
2SET lines 2222 pages 1000
3COL name FOR a60
4COL currently_used FOR a5
5COL last_usage_date FOR a20
6
7SELECT name, currently_used, last_usage_date, description
8FROM dba_feature_usage_statistics
9WHERE currently_used = 'TRUE';

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

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

MMON 的采样机制解析

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

SQL

1SQL> BEGIN
2  dbms_monitor.serv_mod_act_trace_enable(
3    service_name => 'SYS$BACKGROUND',
4    module_name  => 'MMON_SLAVE',
5    action_name  => 'Auto-DBFUS Action');
6END;
7/
8
9SQL> ALTER SESSION SET "_swrf_test_action" = 28;
10
11SQL> ALTER SESSION SET EVENTS 'immediate trace name mmon_test level 6';

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

Bash

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

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

SQL

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

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

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

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

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

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

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

组件使用分析

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

Oracle Text

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

SQL

1SQL> SET pages 999 lines 120 trimout ON trimspool ON
2COL objdep format a55 hea "Object Name (Type)"
3COL refr format a35 hea "Object Reference (Type)"
4
5SELECT OWNER||'.'||name||' ('||decode(TYPE, 'MATERIALIZED VIEW', 'MV', 'DIMENSION', 'DIM', 'EVALUATION CONTXT', 'EVALCTXT', 'PACKAGE BODY', 'PKGBDY', 'CUBE.DIMENSION', 'CUBE.DIM', TYPE)||')' objdep,
6       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
7FROM dba_dependencies
8WHERE referenced_OWNER ='CTXSYS'
9  AND OWNER NOT IN ('PUBLIC', 'CTXSYS', 'SYS')
10ORDER BY 1;

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

SQL

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

Oracle Workspace Manager

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

SQL

1SQL> REM srdc_owm_usage.sql - collect Oracle Workspace Manager usage
2define SRDCNAME='OWM_USAGE'
3SET MARKUP HTML ON PREFORMAT ON
4set TERMOUT off FEEDBACK off VERIFY off TRIMSPOOL on HEADING off
5COLUMN SRDCSPOOLNAME NOPRINT NEW_VALUE SRDCSPOOLNAME
6select 'SRDC_'||upper('&&SRDCNAME')||'_'||upper(instance_name)||'_'||
7        to_char(sysdate,'YYYYMMDD_HH24MISS') SRDCSPOOLNAME from v$instance;
8set TERMOUT on MARKUP html preformat on
9REM
10spool &&SRDCSPOOLNAME..htm
11select '+----------------------------------------------------+' from dual
12union all
13select '| Diagnostic-Name: '||'&&SRDCNAME' from dual
14union all
15select '| Timestamp:       '||
16        to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS TZH:TZM') from dual
17union all
18select '| Machine:         '||host_name from v$instance
19union all
20select '| Version:         '||version from v$instance
21union all
22select '| DBName:          '||name from v$database
23union all
24select '| Instance:        '||instance_name from v$instance
25union all
26select '+----------------------------------------------------+' from dual
27/
28set HEADING on MARKUP html preformat off
29REM === -- end of standard header -- ===
30select comp_name,version,status from dba_registry where comp_id = 'OWM' ;
31select table_name,owner,state from DBA_WM_VERSIONED_TABLES;
32
33select workspace, parent_workspace, owner, freeze_status, resolve_status  from dba_workspaces;
34select version, parent_version, workspace from ALL_VERSION_HVIEW;
35spool off;
36exit

执行结果：

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

卸载 Workspace Manager 组件：

SQL

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

Spatial

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

SQL

1SQL> col owner format a12
2col table_name format a35
3col column_name format a25
4select owner, table_name, column_name
5from dba_tab_columns
6where data_type = 'SDO_GEOMETRY'
7and owner != 'MDSYS';
8
9no rows selected

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

卸载 Spatial 的步骤如下：

SQL

1conn / as sysdba
2
3-- 检查组件是否被使用
4set pages 200
5col owner for a20
6col table_name for a30
7col column_name for a25
8select owner, table_name, column_name
9from dba_tab_columns
10where data_type = 'SDO_GEOMETRY'
11and owner != 'MDSYS'
12order by 1,2,3;
13
14-- 如果被使用，则需要先清理对象
15-- drop Spatial indexes
16set pagesize 0
17spool DropIndexes.sql
18select 'drop index ' || owner || '.' || index_name ||';'
19from dba_indexes where ityp_name = 'SPATIAL_INDEX';
20spool off
21
22@DropIndexes.sql
23
24-- drop Spatial tables
25set pagesize 0
26spool DropTables.sql
27select 'drop table '|| owner ||'.'|| table_name||';'
28from dba_tab_columns
29where data_type = 'SDO_GEOMETRY'
30and owner != 'MDSYS';
31spool off
32
33@DropTables.sql
34
35-- 清空回收站
36purge dba_recyclebin;
37
38-- remove Semantic support
39@?/md/admin/semremov.sql
40
41-- drop MDSYS user
42drop user MDSYS cascade;
43
44-- 一键卸载(多租户环境下也可以)，oracle 用户下执行，不在 sqlplus 执行：
45[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
46catcon::set_log_file_base_path: ALL catcon-related output will be written to [/home/oracle/SDO_deinst_catcon_2719568.lst]
47
48catcon::set_log_file_base_path: catcon: See [/home/oracle/SDO_deinst*.log] files for output generated by scripts
49
50catcon::set_log_file_base_path: catcon: See [/home/oracle/SDO_deinst_*.lst] files for spool files, if any
51
52Enter Password: 
53catcon.pl: completed successfully
54
55-- drop all remaining public synonyms created for Spatial
56set pagesize 0
57set feed off lines 120 trimout on trimspool on
58spool dropsyn.sql
59select 'drop public synonym "' || synonym_name || '";' from dba_synonyms where table_owner='MDSYS';
60spool off;
61@dropsyn.sql
62
63-- drop related user
64drop user mddata cascade;
65-- Only created as of release 11g:
66drop user spatial_csw_admin_usr cascade;
67drop 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

1SQL> define SRDCNAME='XDB_USAGE_CHECK'
2set pagesize 200 verify off sqlprompt "" term off entmap off echo off
3set markup html on spool on
4COLUMN SRDCSPOOLNAME NOPRINT NEW_VALUE SRDCSPOOLNAME
5select 'SRDC_'||upper('&&SRDCNAME')||'_'||upper(instance_name)||'_'||
6       to_char(sysdate,'YYYYMMDD_HH24MISS') SRDCSPOOLNAME from v$instance;
7spool &&SRDCSPOOLNAME..htm
8select 'Diagnostic-Name: ' || '&&SRDCNAME'  as "SRDC COLLECTION HEADER"  from dual
9union all
10select 'Time: ' || to_char(systimestamp, 'YYYY-MM-DD HH24MISS TZHTZM' ) from dual
11union all
12select 'Machine: ' || host_name from v$instance
13union all
14select 'Version: '|| version from v$instance
15union all
16select 'DBName: '||name from v$database
17union all
18select 'Instance: '||instance_name from v$instance
19/
20set serveroutput on
21alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'
22/
23
24define LOWTHRESHOLD=10
25define MIDTHRESHOLD=62
26define VERBOSE=TRUE
27
28set veri off;
29set feedback off;
30REM === -- end of standard header -- ===
31set lines 150 trimspool on pages 50000 long 100000 tab off
32set serveroutput on
33set HEADING on MARKUP html preformat off
34declare
35  --define cursors
36  --check for version
37  cursor c_ver is select version from v$instance;
38  --check for invalids owned by XDB
39  cursor c_inval is select * from dba_objects where status='INVALID' and OWNER in ('SYS','XDB');
40  -- Check status of other database features
41  cursor c_feat is select comp_name,status,version from dba_registry;
42  --check for xml type tables
43  cursor c_xml_tabs is select owner,storage_type,count(*) "TOTAL" from dba_xml_tables group by owner,storage_type;
44  --check for xml type colmns
45  cursor c_xml_tab_cols is select owner,storage_type,count(*) "TOTAL" from dba_xml_tab_cols group by owner,storage_type;
46  --check for xml type views
47  cursor c_xml_vw is select owner,count(*) "TOTAL" from dba_xml_views group by owner;
48  --check for xml type Indexes
49  cursor c_xml_idx is select index_owner,type,count(*) "TOTAL" from dba_xml_indexes group by index_owner,type;
50  --check for API's bbuilt with XML API's
51  cursor c_api is select owner,name,type from dba_dependencies where referenced_name in
52    (select object_name from dba_objects
53     where object_name like 'DBMS_XML%' or object_name like 'DBMS_XSL%')
54       and TYPE !='SYNONYM' and owner !='SYS';
55  --check for registered Schemas
56  cursor c_xml_schemas is select owner,count(*) "TOTAL" from dba_xml_schemas group by owner;
57  --check for user defined resources in the repository
58  cursor c_res is select distinct (a.username) "USER",count (r.xmldata) "TOTAL"
59    from dba_users a, xdb.xdb$resource r
60    where sys_op_rawtonum (extractvalue (value(r),'/Resource/OwnerID/text()')) =a.USER_ID group by a.username;
61  -- check xdbconfig.xml values
62  cursor c_config is select value(x).GETROOTELEMENT() NODENAME, extractValue(value(x),'/*') NODEVALUE
63    from table(xmlsequence(extract(xdburitype('/xdbconfig.xml').getXML(),'//*[text()]'))) x;
64  --check for Network ACLs
65  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;
66  --define variables for fetching data from cursors
67  v_ver c_ver%ROWTYPE;
68  v_inval c_inval%ROWTYPE;
69  v_feat c_feat%ROWTYPE;
70  v_xml_tabs c_xml_tabs%ROWTYPE;
71  v_xml_tab_cols c_xml_tab_cols%ROWTYPE;
72  v_xml_vw c_xml_vw%rowtype;
73  v_xml_idx c_xml_idx%rowtype;
74  v_api c_api%rowtype;
75  v_c_net_acls c_net_acls%rowtype;
76  v_xml_schemas c_xml_schemas%rowtype;
77  v_res c_res%ROWTYPE;
78  v_config c_config%rowtype;
79  -- Static variables
80  v_errcode NUMBER := 0;
81  v_errmsg varchar2(50) := ' ';
82  l_dad_names DBMS_EPG.varchar2_table;
83  --stylesheet for xdbconfig.xml reading
84  v_style clob :='';
85begin
86  open c_ver;
87 fetch c_ver into v_ver;
88 --check minimum XDB requirements
89 if v_ver.version like '9.%' or v_ver.version like '10.%' then
90  DBMS_OUTPUT.PUT_LINE('!!!!!!!!!!!!! UNSUPPORTED VERSION !!!!!!!!!!!!!');
91  DBMS_OUTPUT.PUT_LINE('Minimun version is 11.2.0.4. actual version is: '||v_ver.version);
92 end if;
93 DBMS_OUTPUT.PUT_LINE('############# Status/Version #############');
94 DBMS_OUTPUT.PUT_LINE('XDB Status is: '||dbms_registry.status('XDB')||' at version '||dbms_registry.version('XDB'));
95 if v_ver.version != dbms_registry.version('XDB') then
96  DBMS_OUTPUT.PUT_LINE('Database is at version '||v_ver.version||' XDB is at version '||dbms_registry.version('XDB'));
97 end if;
98 --Check Status. If invalid, gather invalid objects list and check for usage. If valid, simply check for usage
99 if dbms_registry.status('XDB') != 'VALID' then
100  DBMS_OUTPUT.PUT_LINE('############# Invalid Objects #############');
101  open c_inval;
102  loop
103   fetch c_inval into v_inval;
104   DBMS_OUTPUT.PUT_LINE('Type: '||v_inval.object_type||' '||v_inval.owner||'.'||v_inval.object_name);
105   exit when c_inval%NOTFOUND;
106  end loop;
107  close c_inval;
108 end if;
109 -- Check XDBCONFIG.XML paramareters
110 DBMS_OUTPUT.PUT_LINE('############# OTHER DATABASE FEATURES #############');
111 open c_feat;
112  loop
113   fetch c_feat into v_feat;
114   exit when c_feat%NOTFOUND;
115   if c_feat%rowcount >0 then
116    DBMS_OUTPUT.PUT_LINE(v_feat.comp_name||' is '||v_feat.status||' at version '||v_feat.version);
117   else DBMS_OUTPUT.PUT_LINE('No Data Found');
118   end if;
119  end loop;
120 close c_feat;
121 -- Check XDBCONFIG.XML paramareters
122 DBMS_OUTPUT.PUT_LINE('############# XDBCONFIG INFORMATION #############');
123 open c_config;
124  loop
125   fetch c_config into v_config;
126   exit when c_config%NOTFOUND;
127   if c_config%rowcount >0 then
128    DBMS_OUTPUT.PUT_LINE(v_config.NODENAME||'= = = '||v_config.NODEVALUE);
129   else DBMS_OUTPUT.PUT_LINE('No Data Found');
130   end if;
131  end loop;
132 close c_config;
133 -- Check if they have any xmltype tables or columns and if they are schema based, clob or binary
134 DBMS_OUTPUT.PUT_LINE('############# XMLTYPE Tables #############');
135 open c_xml_tabs;
136  loop
137   fetch c_xml_tabs into v_xml_tabs;
138   exit when c_xml_tabs%NOTFOUND;
139   DBMS_OUTPUT.PUT_LINE(v_xml_tabs.owner||' has '||v_xml_tabs.TOTAL||' XMLTYPE TABLES stored as '||v_xml_tabs.storage_type);
140  end loop;
141 close c_xml_tabs;
142 DBMS_OUTPUT.PUT_LINE('############# XMLTYPE Columns #############');
143 open c_xml_tab_cols;
144  loop
145   fetch c_xml_tab_cols into v_xml_tab_cols;
146   exit when c_xml_tab_cols%NOTFOUND;
147   if c_xml_tab_cols%rowcount > 0 then
148    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);
149   else DBMS_OUTPUT.PUT_LINE('No Data Found');
150   end if;
151  end loop;
152 close c_xml_tab_cols;
153 DBMS_OUTPUT.PUT_LINE('############# XMLTYPE Views #############');
154 open c_xml_vw;
155  loop
156   fetch c_xml_vw into v_xml_vw;
157   exit when c_xml_vw%NOTFOUND;
158   if c_xml_vw%rowcount > 0 then
159    DBMS_OUTPUT.PUT_LINE(v_xml_vw.owner||' has '||v_xml_vw.TOTAL||' XMLTYPE Views');
160   else DBMS_OUTPUT.PUT_LINE('No Data Found');
161   end if;
162  end loop;
163 close c_xml_vw;
164 DBMS_OUTPUT.PUT_LINE('############# XMLTYPE INDEXES #############');
165 open c_xml_idx;
166  loop
167   fetch c_xml_idx into v_xml_idx;
168   exit when c_xml_idx%NOTFOUND;
169   if c_xml_idx%rowcount > 0 then
170    DBMS_OUTPUT.PUT_LINE(v_xml_idx.index_owner||' has '||v_xml_idx.TOTAL||' XMLTYPE Indexes of type '||v_xml_idx.type);
171   else DBMS_OUTPUT.PUT_LINE('No Data Found');
172   end if;
173  end loop;
174 close c_xml_idx;
175 DBMS_OUTPUT.PUT_LINE('############# Items built with XML API''s #############');
176 open c_api;
177  loop
178   fetch c_api into v_api;
179   exit when c_api%NOTFOUND;
180   if c_api%rowcount > 0 then
181    DBMS_OUTPUT.PUT_LINE(v_api.type||' '||v_api.owner||'.'||v_api.name);
182   else DBMS_OUTPUT.PUT_LINE('No Data Found');
183   end if;
184  end loop;
185 close c_api;
186 DBMS_OUTPUT.PUT_LINE('############# XML SCHEMAS #############');
187 open c_xml_schemas;
188  loop
189   fetch c_xml_schemas into v_xml_schemas;
190   exit when c_xml_schemas%NOTFOUND;
191   if c_xml_schemas%rowcount >0 then
192    DBMS_OUTPUT.PUT_LINE(v_xml_schemas.owner||' has '||v_xml_schemas.TOTAL||' registered.');
193   else DBMS_OUTPUT.PUT_LINE('No Data Found');
194   end if;
195  end loop;
196 close c_xml_schemas;
197 -- Check for repository resources
198 DBMS_OUTPUT.PUT_LINE('############# Repository Resources #############');
199 open c_res;
200  loop
201   fetch c_res into v_res;
202   exit when c_res%NOTFOUND;
203   if c_res%rowcount >0 then
204    DBMS_OUTPUT.PUT_LINE(v_res.USER||' has '||v_res.TOTAL||' resources.');
205   else DBMS_OUTPUT.PUT_LINE('No Data Found');
206   end if;
207  end loop;
208 close c_res;
209 -- Check Network ACLS
210 DBMS_OUTPUT.PUT_LINE('############# Network ACLs Configured #############');
211 open c_net_acls;
212  loop
213   fetch c_net_acls into v_c_net_acls;
214   if c_net_acls%rowcount >0 then
215    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);
216   else DBMS_OUTPUT.PUT_LINE('No Data Found');
217   end if;
218   exit when c_net_acls%NOTFOUND;
219  end loop;
220 close c_net_acls;
221 --Check DAD configuration to see if DBMS_EPG is being used
222 DBMS_OUTPUT.put_line('############# DBMS_EPG DAD USAGE #############');
223 DBMS_EPG.GET_DAD_LIST (l_dad_names);
224 FOR i IN 1 .. l_dad_names.count LOOP
225  DBMS_OUTPUT.put_line(l_dad_names(i));
226 END LOOP;
227 close c_ver;
228EXCEPTION
229WHEN no_data_found THEN
230 DBMS_OUTPUT.PUT_LINE('No Data Found');
231WHEN others THEN
232 v_errcode := sqlcode;
233 v_errmsg := SUBSTR(sqlerrm, 1, 50);
234 DBMS_OUTPUT.PUT_LINE('ERROR: '||v_errcode||': ' || v_errmsg);
235end;
236/
237--End XDB health and usage check
238SET SERVEROUTPUT OFF
239Rem===========================================================================================================================================
240spool off
241set markup html off spool off
242set sqlprompt "SQL> " term on  echo off
243PROMPT
244PROMPT
245PROMPT REPORT GENERATED : &SRDCSPOOLNAME..htm
246set verify on echo on
247Rem===========================================================================================================================================
248exit;

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

11GR2 卸载 XDB 步骤：

SQL

1-- 卸载 XDB
2SQL> spool xdb_removal.log
3set echo on;
4
5connect / as sysdba
6shutdown immediate;
7startup
8@?/rdbms/admin/catnoqm.sql
9@?/rdbms/admin/catproc.sql
10@?/rdbms/admin/utlrp.sql
11
12set pagesize 1000
13col owner format a8
14col object_name format a35
15
16select owner, object_name, object_type, status
17from dba_objects
18where status = 'INVALID' and owner = 'SYS';
19
20spool off;
21
22-- 清理 XDB 相关的无效对象
23SQL> connect / as sysdba
24
25-- Make XDB Dummy views
26start ?/rdbms/admin/catxdbdv.sql
27
28-- update Data Pump related objects and KU$_ views
29
30start ?/rdbms/admin/dbmsmeta.sql
31start ?/rdbms/admin/dbmsmeti.sql
32start ?/rdbms/admin/dbmsmetu.sql
33start ?/rdbms/admin/dbmsmetb.sql
34start ?/rdbms/admin/dbmsmetd.sql
35start ?/rdbms/admin/dbmsmet2.sql
36start ?/rdbms/admin/catmeta.sql
37start ?/rdbms/admin/prvtmeta.plb
38start ?/rdbms/admin/prvtmeti.plb
39start ?/rdbms/admin/prvtmetu.plb
40start ?/rdbms/admin/prvtmetb.plb
41start ?/rdbms/admin/prvtmetd.plb
42start ?/rdbms/admin/prvtmet2.plb
43start ?/rdbms/admin/catmet2.sql
44
45REM Check to verify that all components are valid
46select COMP_ID, COMP_NAME, VERSION, STATUS from dba_registry;
47
48COMP_ID COMP_NAME                            VERSION      STATUS
49------- ------------------------------------ ------------ --------
50CONTEXT Oracle Text                          11.2.0.2.0   VALID
51EXF     Oracle Expression Filter             11.2.0.2.0   VALID
52OWM     Oracle Workspace Manager             11.2.0.2.0   VALID
53CATALOG Oracle Database Catalog Views        11.2.0.2.0   VALID
54CATPROC Oracle Database Packages and Types   11.2.0.2.0   VALID
55RAC     Oracle Real Application Clusters     11.2.0.2.0   VALID
56JAVAVM  JServer JAVA Virtual Machine         11.2.0.2.0   VALID
57XML     Oracle XDK                           11.2.0.2.0   VALID
58CATJAVA Oracle Database Java Packages        11.2.0.2.0   VALID
59
609 rows selected.
61
62select count(*) from dba_objects where status = 'INVALID'; -- no rows
63
64  COUNT(*)
65---------
66        0

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

OLAP

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

SQL

1SQL> select VALUE from v$OPTION where PARAMETER = 'OLAP';
2
3VALUE
4----------------------------------------------------------------
5TRUE
6
7SQL> col owner format a10
8
9SQL> col aw_name format a20
10
11SQL> select owner, aw_name from dba_aws;
12
13OWNER                AW_NAME
14-------------------- ------------------------------
15SYS                  AWREPORT
16SYS                  AWXML
17SYS                  AWMD
18SYS                  AWCREATE10G
19SYS                  EXPRESS
20SYS                  AWCREATE

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

卸载 OLAP：

Bash

1[oracle@emcc:/home/oracle]$ chopt disable olap
2
3Writing to /u01/app/oracle/product/19.3.0/db/install/disable_olap_2025-06-04_16-11-48PM.log...
4/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
5/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

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

OJVM

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

SQL

1SQL> SELECT version, status FROM dba_registry WHERE comp_id='JAVAVM';
2
3VERSION                        STATUS
4------------------------------ --------------------------------------------
519.0.0.0.0                     VALID
6
7SQL> select * from gv$java_patching_status;
8
9no rows selected
10
11SQL> select count(*) from x$kglob where KGLOBTYP = 29 OR KGLOBTYP = 56;
12
13  COUNT(*)
14----------
15      1313
16
17SQL> select inst_id, name, con_id from gv$java_services order by name, con_id, inst_id;
18
19no rows selected
20
21SQL> col service_name format a20
22col username format a20
23col program format a20
24set num 8
25select sess.service_name, sess.username,sess.program, count(*)
26from
27v$session sess,
28dba_users usr,
29x$kgllk lk,
30x$kglob
31where kgllkuse=saddr
32and kgllkhdl=kglhdadr
33and kglobtyp in (29,56)
34and sess.user# = usr.user_id
35and usr.oracle_maintained = 'N'
36group by sess.service_name, sess.username, sess.program
37order by sess.service_name, sess.username, sess.program;
38
39no rows selected

APEX

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

SQL

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

卸载 APEX 步骤：

SQL

1SQL> @?/apex/apxremov.sql

总结

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

参考资料：

SQL

1SQL> select banner from v$version;
2
3BANNER
4--------------------------------------------------------------------------------
5Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL

1COL comp_id FORMAT A12
2COL version FORMAT A10
3COL comp_name FORMAT A40
4COL status FORMAT A10
5SET pages 30
6SELECT SUBSTR(comp_id,1,12) AS comp_id,
7       status,
8       SUBSTR(version,1,10) AS version,
9       SUBSTR(comp_name,1,40) AS comp_name
10FROM dba_registry
11ORDER BY 1, 2;
12
13COMP_ID      STATUS     VERSION    COMP_NAME
14------------ ---------- ---------- ----------------------------------------
15APS          VALID      19.0.0.0.0 OLAP Analytic Workspace
16CATALOG      VALID      19.0.0.0.0 Oracle Database Catalog Views
17CATJAVA      VALID      19.0.0.0.0 Oracle Database Java Packages
18CATPROC      VALID      19.0.0.0.0 Oracle Database Packages and Types
19CONTEXT      VALID      19.0.0.0.0 Oracle Text
20DV           VALID      19.0.0.0.0 Oracle Database Vault
21JAVAVM       VALID      19.0.0.0.0 JServer JAVA Virtual Machine
22OLS          VALID      19.0.0.0.0 Oracle Label Security
23ORDIM        VALID      19.0.0.0.0 Oracle Multimedia
24OWM          VALID      19.0.0.0.0 Oracle Workspace Manager
25RAC          OPTION OFF 19.0.0.0.0 Oracle Real Application Clusters
26SDO          VALID      19.0.0.0.0 Spatial
27XDB          VALID      19.0.0.0.0 Oracle XML Database
28XML          VALID      19.0.0.0.0 Oracle XDK
29XOQ          VALID      19.0.0.0.0 Oracle OLAP API

SQL

1SQL> ALTER SESSION SET nls_date_format = 'dd-mon-yy hh24:mi:ss';
2SET lines 2222 pages 1000
3COL name FOR a60
4COL currently_used FOR a5
5COL last_usage_date FOR a20
6
7SELECT name, currently_used, last_usage_date, description
8FROM dba_feature_usage_statistics
9WHERE currently_used = 'TRUE';

SQL

1SQL> BEGIN
2  dbms_monitor.serv_mod_act_trace_enable(
3    service_name => 'SYS$BACKGROUND',
4    module_name  => 'MMON_SLAVE',
5    action_name  => 'Auto-DBFUS Action');
6END;
7/
8
9SQL> ALTER SESSION SET "_swrf_test_action" = 28;
10
11SQL> ALTER SESSION SET EVENTS 'immediate trace name mmon_test level 6';

Bash

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

SQL

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

SQL

1SQL> SET pages 999 lines 120 trimout ON trimspool ON
2COL objdep format a55 hea "Object Name (Type)"
3COL refr format a35 hea "Object Reference (Type)"
4
5SELECT OWNER||'.'||name||' ('||decode(TYPE, 'MATERIALIZED VIEW', 'MV', 'DIMENSION', 'DIM', 'EVALUATION CONTXT', 'EVALCTXT', 'PACKAGE BODY', 'PKGBDY', 'CUBE.DIMENSION', 'CUBE.DIM', TYPE)||')' objdep,
6       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
7FROM dba_dependencies
8WHERE referenced_OWNER ='CTXSYS'
9  AND OWNER NOT IN ('PUBLIC', 'CTXSYS', 'SYS')
10ORDER BY 1;

SQL

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

SQL

1SQL> REM srdc_owm_usage.sql - collect Oracle Workspace Manager usage
2define SRDCNAME='OWM_USAGE'
3SET MARKUP HTML ON PREFORMAT ON
4set TERMOUT off FEEDBACK off VERIFY off TRIMSPOOL on HEADING off
5COLUMN SRDCSPOOLNAME NOPRINT NEW_VALUE SRDCSPOOLNAME
6select 'SRDC_'||upper('&&SRDCNAME')||'_'||upper(instance_name)||'_'||
7        to_char(sysdate,'YYYYMMDD_HH24MISS') SRDCSPOOLNAME from v$instance;
8set TERMOUT on MARKUP html preformat on
9REM
10spool &&SRDCSPOOLNAME..htm
11select '+----------------------------------------------------+' from dual
12union all
13select '| Diagnostic-Name: '||'&&SRDCNAME' from dual
14union all
15select '| Timestamp:       '||
16        to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS TZH:TZM') from dual
17union all
18select '| Machine:         '||host_name from v$instance
19union all
20select '| Version:         '||version from v$instance
21union all
22select '| DBName:          '||name from v$database
23union all
24select '| Instance:        '||instance_name from v$instance
25union all
26select '+----------------------------------------------------+' from dual
27/
28set HEADING on MARKUP html preformat off
29REM === -- end of standard header -- ===
30select comp_name,version,status from dba_registry where comp_id = 'OWM' ;
31select table_name,owner,state from DBA_WM_VERSIONED_TABLES;
32
33select workspace, parent_workspace, owner, freeze_status, resolve_status  from dba_workspaces;
34select version, parent_version, workspace from ALL_VERSION_HVIEW;
35spool off;
36exit

SQL

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

SQL

1SQL> col owner format a12
2col table_name format a35
3col column_name format a25
4select owner, table_name, column_name
5from dba_tab_columns
6where data_type = 'SDO_GEOMETRY'
7and owner != 'MDSYS';
8
9no rows selected

SQL

1conn / as sysdba
2
3-- 检查组件是否被使用
4set pages 200
5col owner for a20
6col table_name for a30
7col column_name for a25
8select owner, table_name, column_name
9from dba_tab_columns
10where data_type = 'SDO_GEOMETRY'
11and owner != 'MDSYS'
12order by 1,2,3;
13
14-- 如果被使用，则需要先清理对象
15-- drop Spatial indexes
16set pagesize 0
17spool DropIndexes.sql
18select 'drop index ' || owner || '.' || index_name ||';'
19from dba_indexes where ityp_name = 'SPATIAL_INDEX';
20spool off
21
22@DropIndexes.sql
23
24-- drop Spatial tables
25set pagesize 0
26spool DropTables.sql
27select 'drop table '|| owner ||'.'|| table_name||';'
28from dba_tab_columns
29where data_type = 'SDO_GEOMETRY'
30and owner != 'MDSYS';
31spool off
32
33@DropTables.sql
34
35-- 清空回收站
36purge dba_recyclebin;
37
38-- remove Semantic support
39@?/md/admin/semremov.sql
40
41-- drop MDSYS user
42drop user MDSYS cascade;
43
44-- 一键卸载(多租户环境下也可以)，oracle 用户下执行，不在 sqlplus 执行：
45[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
46catcon::set_log_file_base_path: ALL catcon-related output will be written to [/home/oracle/SDO_deinst_catcon_2719568.lst]
47
48catcon::set_log_file_base_path: catcon: See [/home/oracle/SDO_deinst*.log] files for output generated by scripts
49
50catcon::set_log_file_base_path: catcon: See [/home/oracle/SDO_deinst_*.lst] files for spool files, if any
51
52Enter Password: 
53catcon.pl: completed successfully
54
55-- drop all remaining public synonyms created for Spatial
56set pagesize 0
57set feed off lines 120 trimout on trimspool on
58spool dropsyn.sql
59select 'drop public synonym "' || synonym_name || '";' from dba_synonyms where table_owner='MDSYS';
60spool off;
61@dropsyn.sql
62
63-- drop related user
64drop user mddata cascade;
65-- Only created as of release 11g:
66drop user spatial_csw_admin_usr cascade;
67drop user spatial_wfs_admin_usr cascade;

SQL

1SQL> define SRDCNAME='XDB_USAGE_CHECK'
2set pagesize 200 verify off sqlprompt "" term off entmap off echo off
3set markup html on spool on
4COLUMN SRDCSPOOLNAME NOPRINT NEW_VALUE SRDCSPOOLNAME
5select 'SRDC_'||upper('&&SRDCNAME')||'_'||upper(instance_name)||'_'||
6       to_char(sysdate,'YYYYMMDD_HH24MISS') SRDCSPOOLNAME from v$instance;
7spool &&SRDCSPOOLNAME..htm
8select 'Diagnostic-Name: ' || '&&SRDCNAME'  as "SRDC COLLECTION HEADER"  from dual
9union all
10select 'Time: ' || to_char(systimestamp, 'YYYY-MM-DD HH24MISS TZHTZM' ) from dual
11union all
12select 'Machine: ' || host_name from v$instance
13union all
14select 'Version: '|| version from v$instance
15union all
16select 'DBName: '||name from v$database
17union all
18select 'Instance: '||instance_name from v$instance
19/
20set serveroutput on
21alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'
22/
23
24define LOWTHRESHOLD=10
25define MIDTHRESHOLD=62
26define VERBOSE=TRUE
27
28set veri off;
29set feedback off;
30REM === -- end of standard header -- ===
31set lines 150 trimspool on pages 50000 long 100000 tab off
32set serveroutput on
33set HEADING on MARKUP html preformat off
34declare
35  --define cursors
36  --check for version
37  cursor c_ver is select version from v$instance;
38  --check for invalids owned by XDB
39  cursor c_inval is select * from dba_objects where status='INVALID' and OWNER in ('SYS','XDB');
40  -- Check status of other database features
41  cursor c_feat is select comp_name,status,version from dba_registry;
42  --check for xml type tables
43  cursor c_xml_tabs is select owner,storage_type,count(*) "TOTAL" from dba_xml_tables group by owner,storage_type;
44  --check for xml type colmns
45  cursor c_xml_tab_cols is select owner,storage_type,count(*) "TOTAL" from dba_xml_tab_cols group by owner,storage_type;
46  --check for xml type views
47  cursor c_xml_vw is select owner,count(*) "TOTAL" from dba_xml_views group by owner;
48  --check for xml type Indexes
49  cursor c_xml_idx is select index_owner,type,count(*) "TOTAL" from dba_xml_indexes group by index_owner,type;
50  --check for API's bbuilt with XML API's
51  cursor c_api is select owner,name,type from dba_dependencies where referenced_name in
52    (select object_name from dba_objects
53     where object_name like 'DBMS_XML%' or object_name like 'DBMS_XSL%')
54       and TYPE !='SYNONYM' and owner !='SYS';
55  --check for registered Schemas
56  cursor c_xml_schemas is select owner,count(*) "TOTAL" from dba_xml_schemas group by owner;
57  --check for user defined resources in the repository
58  cursor c_res is select distinct (a.username) "USER",count (r.xmldata) "TOTAL"
59    from dba_users a, xdb.xdb$resource r
60    where sys_op_rawtonum (extractvalue (value(r),'/Resource/OwnerID/text()')) =a.USER_ID group by a.username;
61  -- check xdbconfig.xml values
62  cursor c_config is select value(x).GETROOTELEMENT() NODENAME, extractValue(value(x),'/*') NODEVALUE
63    from table(xmlsequence(extract(xdburitype('/xdbconfig.xml').getXML(),'//*[text()]'))) x;
64  --check for Network ACLs
65  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;
66  --define variables for fetching data from cursors
67  v_ver c_ver%ROWTYPE;
68  v_inval c_inval%ROWTYPE;
69  v_feat c_feat%ROWTYPE;
70  v_xml_tabs c_xml_tabs%ROWTYPE;
71  v_xml_tab_cols c_xml_tab_cols%ROWTYPE;
72  v_xml_vw c_xml_vw%rowtype;
73  v_xml_idx c_xml_idx%rowtype;
74  v_api c_api%rowtype;
75  v_c_net_acls c_net_acls%rowtype;
76  v_xml_schemas c_xml_schemas%rowtype;
77  v_res c_res%ROWTYPE;
78  v_config c_config%rowtype;
79  -- Static variables
80  v_errcode NUMBER := 0;
81  v_errmsg varchar2(50) := ' ';
82  l_dad_names DBMS_EPG.varchar2_table;
83  --stylesheet for xdbconfig.xml reading
84  v_style clob :='';
85begin
86  open c_ver;
87 fetch c_ver into v_ver;
88 --check minimum XDB requirements
89 if v_ver.version like '9.%' or v_ver.version like '10.%' then
90  DBMS_OUTPUT.PUT_LINE('!!!!!!!!!!!!! UNSUPPORTED VERSION !!!!!!!!!!!!!');
91  DBMS_OUTPUT.PUT_LINE('Minimun version is 11.2.0.4. actual version is: '||v_ver.version);
92 end if;
93 DBMS_OUTPUT.PUT_LINE('############# Status/Version #############');
94 DBMS_OUTPUT.PUT_LINE('XDB Status is: '||dbms_registry.status('XDB')||' at version '||dbms_registry.version('XDB'));
95 if v_ver.version != dbms_registry.version('XDB') then
96  DBMS_OUTPUT.PUT_LINE('Database is at version '||v_ver.version||' XDB is at version '||dbms_registry.version('XDB'));
97 end if;
98 --Check Status. If invalid, gather invalid objects list and check for usage. If valid, simply check for usage
99 if dbms_registry.status('XDB') != 'VALID' then
100  DBMS_OUTPUT.PUT_LINE('############# Invalid Objects #############');
101  open c_inval;
102  loop
103   fetch c_inval into v_inval;
104   DBMS_OUTPUT.PUT_LINE('Type: '||v_inval.object_type||' '||v_inval.owner||'.'||v_inval.object_name);
105   exit when c_inval%NOTFOUND;
106  end loop;
107  close c_inval;
108 end if;
109 -- Check XDBCONFIG.XML paramareters
110 DBMS_OUTPUT.PUT_LINE('############# OTHER DATABASE FEATURES #############');
111 open c_feat;
112  loop
113   fetch c_feat into v_feat;
114   exit when c_feat%NOTFOUND;
115   if c_feat%rowcount >0 then
116    DBMS_OUTPUT.PUT_LINE(v_feat.comp_name||' is '||v_feat.status||' at version '||v_feat.version);
117   else DBMS_OUTPUT.PUT_LINE('No Data Found');
118   end if;
119  end loop;
120 close c_feat;
121 -- Check XDBCONFIG.XML paramareters
122 DBMS_OUTPUT.PUT_LINE('############# XDBCONFIG INFORMATION #############');
123 open c_config;
124  loop
125   fetch c_config into v_config;
126   exit when c_config%NOTFOUND;
127   if c_config%rowcount >0 then
128    DBMS_OUTPUT.PUT_LINE(v_config.NODENAME||'= = = '||v_config.NODEVALUE);
129   else DBMS_OUTPUT.PUT_LINE('No Data Found');
130   end if;
131  end loop;
132 close c_config;
133 -- Check if they have any xmltype tables or columns and if they are schema based, clob or binary
134 DBMS_OUTPUT.PUT_LINE('############# XMLTYPE Tables #############');
135 open c_xml_tabs;
136  loop
137   fetch c_xml_tabs into v_xml_tabs;
138   exit when c_xml_tabs%NOTFOUND;
139   DBMS_OUTPUT.PUT_LINE(v_xml_tabs.owner||' has '||v_xml_tabs.TOTAL||' XMLTYPE TABLES stored as '||v_xml_tabs.storage_type);
140  end loop;
141 close c_xml_tabs;
142 DBMS_OUTPUT.PUT_LINE('############# XMLTYPE Columns #############');
143 open c_xml_tab_cols;
144  loop
145   fetch c_xml_tab_cols into v_xml_tab_cols;
146   exit when c_xml_tab_cols%NOTFOUND;
147   if c_xml_tab_cols%rowcount > 0 then
148    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);
149   else DBMS_OUTPUT.PUT_LINE('No Data Found');
150   end if;
151  end loop;
152 close c_xml_tab_cols;
153 DBMS_OUTPUT.PUT_LINE('############# XMLTYPE Views #############');
154 open c_xml_vw;
155  loop
156   fetch c_xml_vw into v_xml_vw;
157   exit when c_xml_vw%NOTFOUND;
158   if c_xml_vw%rowcount > 0 then
159    DBMS_OUTPUT.PUT_LINE(v_xml_vw.owner||' has '||v_xml_vw.TOTAL||' XMLTYPE Views');
160   else DBMS_OUTPUT.PUT_LINE('No Data Found');
161   end if;
162  end loop;
163 close c_xml_vw;
164 DBMS_OUTPUT.PUT_LINE('############# XMLTYPE INDEXES #############');
165 open c_xml_idx;
166  loop
167   fetch c_xml_idx into v_xml_idx;
168   exit when c_xml_idx%NOTFOUND;
169   if c_xml_idx%rowcount > 0 then
170    DBMS_OUTPUT.PUT_LINE(v_xml_idx.index_owner||' has '||v_xml_idx.TOTAL||' XMLTYPE Indexes of type '||v_xml_idx.type);
171   else DBMS_OUTPUT.PUT_LINE('No Data Found');
172   end if;
173  end loop;
174 close c_xml_idx;
175 DBMS_OUTPUT.PUT_LINE('############# Items built with XML API''s #############');
176 open c_api;
177  loop
178   fetch c_api into v_api;
179   exit when c_api%NOTFOUND;
180   if c_api%rowcount > 0 then
181    DBMS_OUTPUT.PUT_LINE(v_api.type||' '||v_api.owner||'.'||v_api.name);
182   else DBMS_OUTPUT.PUT_LINE('No Data Found');
183   end if;
184  end loop;
185 close c_api;
186 DBMS_OUTPUT.PUT_LINE('############# XML SCHEMAS #############');
187 open c_xml_schemas;
188  loop
189   fetch c_xml_schemas into v_xml_schemas;
190   exit when c_xml_schemas%NOTFOUND;
191   if c_xml_schemas%rowcount >0 then
192    DBMS_OUTPUT.PUT_LINE(v_xml_schemas.owner||' has '||v_xml_schemas.TOTAL||' registered.');
193   else DBMS_OUTPUT.PUT_LINE('No Data Found');
194   end if;
195  end loop;
196 close c_xml_schemas;
197 -- Check for repository resources
198 DBMS_OUTPUT.PUT_LINE('############# Repository Resources #############');
199 open c_res;
200  loop
201   fetch c_res into v_res;
202   exit when c_res%NOTFOUND;
203   if c_res%rowcount >0 then
204    DBMS_OUTPUT.PUT_LINE(v_res.USER||' has '||v_res.TOTAL||' resources.');
205   else DBMS_OUTPUT.PUT_LINE('No Data Found');
206   end if;
207  end loop;
208 close c_res;
209 -- Check Network ACLS
210 DBMS_OUTPUT.PUT_LINE('############# Network ACLs Configured #############');
211 open c_net_acls;
212  loop
213   fetch c_net_acls into v_c_net_acls;
214   if c_net_acls%rowcount >0 then
215    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);
216   else DBMS_OUTPUT.PUT_LINE('No Data Found');
217   end if;
218   exit when c_net_acls%NOTFOUND;
219  end loop;
220 close c_net_acls;
221 --Check DAD configuration to see if DBMS_EPG is being used
222 DBMS_OUTPUT.put_line('############# DBMS_EPG DAD USAGE #############');
223 DBMS_EPG.GET_DAD_LIST (l_dad_names);
224 FOR i IN 1 .. l_dad_names.count LOOP
225  DBMS_OUTPUT.put_line(l_dad_names(i));
226 END LOOP;
227 close c_ver;
228EXCEPTION
229WHEN no_data_found THEN
230 DBMS_OUTPUT.PUT_LINE('No Data Found');
231WHEN others THEN
232 v_errcode := sqlcode;
233 v_errmsg := SUBSTR(sqlerrm, 1, 50);
234 DBMS_OUTPUT.PUT_LINE('ERROR: '||v_errcode||': ' || v_errmsg);
235end;
236/
237--End XDB health and usage check
238SET SERVEROUTPUT OFF
239Rem===========================================================================================================================================
240spool off
241set markup html off spool off
242set sqlprompt "SQL> " term on  echo off
243PROMPT
244PROMPT
245PROMPT REPORT GENERATED : &SRDCSPOOLNAME..htm
246set verify on echo on
247Rem===========================================================================================================================================
248exit;

SQL

1-- 卸载 XDB
2SQL> spool xdb_removal.log
3set echo on;
4
5connect / as sysdba
6shutdown immediate;
7startup
8@?/rdbms/admin/catnoqm.sql
9@?/rdbms/admin/catproc.sql
10@?/rdbms/admin/utlrp.sql
11
12set pagesize 1000
13col owner format a8
14col object_name format a35
15
16select owner, object_name, object_type, status
17from dba_objects
18where status = 'INVALID' and owner = 'SYS';
19
20spool off;
21
22-- 清理 XDB 相关的无效对象
23SQL> connect / as sysdba
24
25-- Make XDB Dummy views
26start ?/rdbms/admin/catxdbdv.sql
27
28-- update Data Pump related objects and KU$_ views
29
30start ?/rdbms/admin/dbmsmeta.sql
31start ?/rdbms/admin/dbmsmeti.sql
32start ?/rdbms/admin/dbmsmetu.sql
33start ?/rdbms/admin/dbmsmetb.sql
34start ?/rdbms/admin/dbmsmetd.sql
35start ?/rdbms/admin/dbmsmet2.sql
36start ?/rdbms/admin/catmeta.sql
37start ?/rdbms/admin/prvtmeta.plb
38start ?/rdbms/admin/prvtmeti.plb
39start ?/rdbms/admin/prvtmetu.plb
40start ?/rdbms/admin/prvtmetb.plb
41start ?/rdbms/admin/prvtmetd.plb
42start ?/rdbms/admin/prvtmet2.plb
43start ?/rdbms/admin/catmet2.sql
44
45REM Check to verify that all components are valid
46select COMP_ID, COMP_NAME, VERSION, STATUS from dba_registry;
47
48COMP_ID COMP_NAME                            VERSION      STATUS
49------- ------------------------------------ ------------ --------
50CONTEXT Oracle Text                          11.2.0.2.0   VALID
51EXF     Oracle Expression Filter             11.2.0.2.0   VALID
52OWM     Oracle Workspace Manager             11.2.0.2.0   VALID
53CATALOG Oracle Database Catalog Views        11.2.0.2.0   VALID
54CATPROC Oracle Database Packages and Types   11.2.0.2.0   VALID
55RAC     Oracle Real Application Clusters     11.2.0.2.0   VALID
56JAVAVM  JServer JAVA Virtual Machine         11.2.0.2.0   VALID
57XML     Oracle XDK                           11.2.0.2.0   VALID
58CATJAVA Oracle Database Java Packages        11.2.0.2.0   VALID
59
609 rows selected.
61
62select count(*) from dba_objects where status = 'INVALID'; -- no rows
63
64  COUNT(*)
65---------
66        0

SQL

1SQL> select VALUE from v$OPTION where PARAMETER = 'OLAP';
2
3VALUE
4----------------------------------------------------------------
5TRUE
6
7SQL> col owner format a10
8
9SQL> col aw_name format a20
10
11SQL> select owner, aw_name from dba_aws;
12
13OWNER                AW_NAME
14-------------------- ------------------------------
15SYS                  AWREPORT
16SYS                  AWXML
17SYS                  AWMD
18SYS                  AWCREATE10G
19SYS                  EXPRESS
20SYS                  AWCREATE

Bash

1[oracle@emcc:/home/oracle]$ chopt disable olap
2
3Writing to /u01/app/oracle/product/19.3.0/db/install/disable_olap_2025-06-04_16-11-48PM.log...
4/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
5/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

SQL

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

SQL

1SQL> SELECT version, status FROM dba_registry WHERE comp_id='JAVAVM';
2
3VERSION                        STATUS
4------------------------------ --------------------------------------------
519.0.0.0.0                     VALID
6
7SQL> select * from gv$java_patching_status;
8
9no rows selected
10
11SQL> select count(*) from x$kglob where KGLOBTYP = 29 OR KGLOBTYP = 56;
12
13  COUNT(*)
14----------
15      1313
16
17SQL> select inst_id, name, con_id from gv$java_services order by name, con_id, inst_id;
18
19no rows selected
20
21SQL> col service_name format a20
22col username format a20
23col program format a20
24set num 8
25select sess.service_name, sess.username,sess.program, count(*)
26from
27v$session sess,
28dba_users usr,
29x$kgllk lk,
30x$kglob
31where kgllkuse=saddr
32and kgllkhdl=kglhdadr
33and kglobtyp in (29,56)
34and sess.user# = usr.user_id
35and usr.oracle_maintained = 'N'
36group by sess.service_name, sess.username, sess.program
37order by sess.service_name, sess.username, sess.program;
38
39no rows selected

SQL

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

SQL

1SQL> @?/apex/apxremov.sql