topic Re: DBTUNE XML_COLUMN_STORAGE parameter settings in Data Management Questions

DBTUNE XML_COLUMN_STORAGE parameter settings

WesKing — Thu, 30 Jan 2014 00:23:07 GMT

Hi Everyone:
Thanks in advance for any help. I'm posting this question for a co-worker to try and help with some questions we have.

Reference ESRI link:
http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/XML_columns_in_a_geodatabase_stored_in_Oracle/002n00000071000000/
Oracle 11.2.0.3 64-bit running on Windows 2008R2

On our Oracle database, we have two DBTUNE tables, one in SDE schema and one
in an SDE managed geo-database schema.

Both DBTUNE tables have the XML_COLUMN_STORAGE parameter set to DEFAULT, but
the reference article states the setting default is SDE_XML (not DEFAULT)
and mentions "Oracle XML" as the only other setting. This leaves one with
the assumption that ORACLE_XML would be the other allowed parameter setting.

Problem: Our Oracle XML package has become invalid and exports of the SDE
managed schema state "Export terminated unsuccessfully" with references to
two invalid Oracle XML packages: XDB.DBMS_XDBUTIL_INT and SYS.DBMS_CUBE_EXP
(which depends on the XDB package being valid)

Question: If DEFAULT is also an allowed setting, does this allow both
SDE_XML and ORACLE_XML to be used in an SDE-Managed geo-database schema?

Thanks again,
Wes

Re: DBTUNE XML_COLUMN_STORAGE parameter settings

EmadAl-Mousa — Thu, 30 Jan 2014 13:34:31 GMT

hi,

i think your first objective is to fix your oracle xml component issue.

first, please provide the output of this query:
select COMP_NAME,VERSION,STATUS from dba_registry;

* this will list all of your oracle components and will show if XML component is "invalid".

Then, try to know the root cause of why XDB invalidation, through this query:
select owner, object_type, object_name
from dba_objects where status <>'VALID' and owner='XDB';

* it will list all (packages,procedures,views,...etc) that are having the issue under 'XDB' schema.

then, next step is to find out what is the issue with each (package,procedure,...etc) you got from the previous query. You can do
that either through sql developer or TOAD. by going under the xdb schema and trying to compile these objects and
check the "error" tab it will show what is causing this issue (it could be a permission issue for 'xdb' schema).

if you don't have Toad or sql developer then, use the following query:

select text from dba_errors where name = â??[OBJECT_NAME]â?? and owner = â??XDBâ??;

Where [OBJECT_NAME] is then name of Database object which is having the problem.

Try to export again after fixing the issue.

Hope This helps.

Regards,
Emad Al-Mousa

Re: DBTUNE XML_COLUMN_STORAGE parameter settings

EmadAl-Mousa — Thu, 30 Jan 2014 13:39:18 GMT

i forgot to tell you that you need also to re-validate all oracle objects again using (utilrip) after fixing xdb issue:

in sqlplus, execute the following as 'sys' user:

@$ORACLE_HOME/rdbms/admin/utlrp.sql

it could time in execution depending on the number of objects in your database and your server performance.

after executing utlrp.sql

execute the following query:

select COMP_NAME,VERSION,STATUS from dba_registry;

it should show that XML component is valid.

Re: DBTUNE XML_COLUMN_STORAGE parameter settings

WesKing — Sat, 08 Mar 2014 14:03:38 GMT

Emad,
Sorry my response has taken so long. Our network security blocks posting on certain forums (I believe because of Google Analytics???) so I only post when working at home. So I kind of forgot to reply until now.

Anyway, thank you so much for your reply, and especially for going into such detail. I sent my co-worker to this posting link and I believe he ran through everything you posted. As I mentioned this was for someone else, and it's not my area of expertise so I don't know his results. I'll update him on your second posting, as I don't think he saw it when he ran his tests.

I can say that we are up-and-running again so I believe your posts were very helpful, or at least pointed him in the right direction.

Thanks again for taking the time to share your knowledge.

Wes