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 ORDERBY1, 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
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';
SQL>ALTER SESSION SET EVENTS 'immediate trace name mmon_test level 6';
然后通过命令提取 trace 文件中的 SQL：
1
grep -A 1 "sqlid='"$ORACLE_BASE/diag/rdbms/<db_name>/<instance>/trace/<file>.trc | grep -v "sqlid='"
Trace 中会显示大量底层采样 SQL，例如：
1 2 3 4 5
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> 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 unionall select'| Diagnostic-Name: '||'&&SRDCNAME'from dual unionall select'| Timestamp: '|| to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS TZH:TZM') from dual unionall select'| Machine: '||host_name from v$instance unionall select'| Version: '||version from v$instance unionall select'| DBName: '||name from v$database unionall select'| Instance: '||instance_name from v$instance unionall 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
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';
-- 检查组件是否被使用 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' orderby1,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 dropuser 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 dropuser mddata cascade; -- Only created as of release 11g: dropuser spatial_csw_admin_usr cascade; dropuser 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
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 unionall select'Time: '|| to_char(systimestamp, 'YYYY-MM-DD HH24MISS TZHTZM' ) from dual unionall select'Machine: '|| host_name from v$instance unionall select'Version: '|| version from v$instance unionall select'DBName: '||name from v$database unionall select'Instance: '||instance_name from v$instance / set serveroutput on alter session set nls_date_format ='DD-MON-YYYY HH24:MI:SS' /
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 isselect version from v$instance; --check for invalids owned by XDB cursor c_inval isselect*from dba_objects where status='INVALID'and OWNER in ('SYS','XDB'); -- Check status of other database features cursor c_feat isselect comp_name,status,version from dba_registry; --check for xml type tables cursor c_xml_tabs isselect owner,storage_type,count(*) "TOTAL" from dba_xml_tables groupby owner,storage_type; --check for xml type colmns cursor c_xml_tab_cols isselect owner,storage_type,count(*) "TOTAL" from dba_xml_tab_cols groupby owner,storage_type; --check for xml type views cursor c_xml_vw isselect owner,count(*) "TOTAL" from dba_xml_views groupby owner; --check for xml type Indexes cursor c_xml_idx isselect index_owner,type,count(*) "TOTAL" from dba_xml_indexes groupby index_owner,type; --check for API's bbuilt with XML API's cursor c_api isselect 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 isselect owner,count(*) "TOTAL" from dba_xml_schemas groupby owner; --check for user defined resources in the repository cursor c_res isselectdistinct (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 groupby a.username; -- check xdbconfig.xml values cursor c_config isselectvalue(x).GETROOTELEMENT() NODENAME, extractValue(value(x),'/*') NODEVALUE fromtable(xmlsequence(extract(xdburitype('/xdbconfig.xml').getXML(),'//*[text()]'))) x; --check for Network ACLs cursor c_net_acls isselect 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 >0then 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 >0then 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 >0then 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 >0then 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 >0then 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 >0then 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 >0then 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 >0then 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 >0then 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 IN1 .. 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;
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;
norows selected
SQL>selectcount(*) from x$kglob where KGLOBTYP =29OR KGLOBTYP =56;
COUNT(*) ---------- 1313
SQL>select inst_id, name, con_id from gv$java_services orderby name, con_id, inst_id;
norows 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' groupby sess.service_name, sess.username, sess.program orderby sess.service_name, sess.username, sess.program;
norows selected
APEX
检查当前数据库是否使用 APEX：
1 2
SQL>select workspace, workspace_id from apex_workspaces; select workspace, view_date, seconds_ago from apex_workspace_activity_log
