Tags

,


Applies to:
Oracle Server – Enterprise Edition – Version: 11.1.0.6 and later   [Release: 11.1 and later ]
Oracle Server – Personal Edition – Version: 11.1.0.6 and later    [Release: 11.1 and later]
Information in this document applies to any platform.
Checked for relevance on 12-Nov-2010
Purpose
This script will provide output to show which features of XML Database (XDB) are currently being utilized in the database.

 

There are times where this script may not run due to XDB repository  items being invalid or corrupt .
if you encounter exceptions running this script you can try the script form note: 790812.1 instead. 
Even though it is a 10g note it will still give support a base idea of which items are being
used as they offer repair/recovery recommendations.

Software Requirements/Prerequisites
Use SQLPlus.  This script is for 11g.

Configuring the Script
Run the script as the SYS user.

Running the Script
1.  Log in to the DB using SQLPlus

2.  Connect as the SYS user

3.  Copy the script below into a text document and save it as xdbusagecheck.sql

4.  Run the xdbusagecheck.sql script

5.  Review the output and provide to support as necessary. 
     (see sample output below)

Caution
This script is provided for educational purposes only and not supported by Oracle Support Services.
It has been tested internally, however, and works as documented.
We do not guarantee that it will work for you,
so be sure to test it in your environment before relying on it.
Proofread this script before using it! Due to the differences in the way text editors,
e-mail packages and operating systems handle text formatting (spaces, tabs and carriage returns),
this script may not be in an executable state when you first receive it.
Check over the script to ensure that errors of this type are corrected.

— begin script
set pagesize 0
set serveroutput on–Begin XDB health and usage check
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 * 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 dbms_registry.version('XDB') in ('9.2.0.1.0','9.2.0.2.0') then 
DBMS_OUTPUT.PUT_LINE('!!!!!!!!!!!!! UNSUPPORTED VERSION !!!!!!!!!!!!!'); 
DBMS_OUTPUT.PUT_LINE('Minimun version is 9.2.0.3.0. actual version is: '||dbms_registry.version('XDB')); 
end if; 
if v_ver.version like '11.%' then DBMS_OUTPUT.PUT_LINE(' Doing '||v_ver.version||' checks'); 
— Print XDB status 
DBMS_OUTPUT.PUT_LINE('############# Status/Version #############'); 
DBMS_OUTPUT.PUT_LINE('XDB Status is: '||dbms_registry.status('XDB')||' at version '||dbms_registry.version('XDB')); 
end if; 
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.lower_port||' through '|| v_c_net_acls.upper_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

Advertisements