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?
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.
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.