Author Marco Gralike

Marco Gralike, working for AMIS Services BV as a Principal Oracle Database Consultant in the Netherlands, has experience as a DBA since 1994 (Oracle 6). Marco is also eager and skillful in other fields, like Operating System Administration and Application Servers, mainly to find working, performing solutions. Marco has been specializing in Oracle XMLDB, since 2003, focusing on his old love, database administration and performance.He is an Oracle XMLDB enthusiast ever since. He is also a dedicated contributor of the Oracle User Group community, helping people with their steep XMLDB learning curve. To this purpose, Marco also devoted his personal blog site to XMLDB and other Oracle issues. Marco is a member of the OakTable network and an Oracle ACE Director (specialization Oracle XMLDB).

1 OERR – In search of Error Messages/Events

Sometimes you want to search for Oracle database messages, their meaning or you are in search of specific database event settings.

For instance, a long time ago i was searching for an event that would trace when an datafile would expand / autoextend. I encountered latching problems on a Siebel system based on an new Oracle Failsafe environment (Windows 2000 cluster). I wanted to be sure that these latching problems weren’t introduced by the newly introduced datafile AUTOEXTEND feature. So i put in a TAR on metalink with the request if there was a way to trace, eq. an event setting, when a datafile extended. This way i could cross-reference this timestamp to the timestamps i was registering my latch problems. The reponse took a long time. I was in need for an answer, so i created a workaround via a PL/SQL procedure. This procedure was executed via the database job scheduler. Every time a datafile extended, the timestamp, SCN, extend growth etc. was picked up by this procedure, and the data was stored in a table.

Lately i am in search of XML DB knowledge, more specific, DBA/database specific XML DB knowledge (object performance/sizing/XML Schema tuning, etc. stuff). In the new Oracle 10g Release 2 manuals, i came across the event setting 31098: “Internal event to turn on XDB tracing”. So i wondered if there were more of these settings. Apparently this setting was already applicable in Oracle 10g Release 1.

I have a small script called OERR, like the Oracle message utility under UNIX. It does the following:

SQL> @oerr 31098

Error 31098 is: ORA-31098: Internal event to turn on XDB tracing

The SQL code for this script:

DBA Oracle
0 TimesTen

Oracle aquired TimesTen. See here for more details. By the way “fusion middleware” is the new name for application server,…

5 Mastering XML DB – COUNT and SUM

Working for years with Oracle’s relational environment, XML DB is a completely different kind of beast. You’re confronted with all the languages and techniques the XML environment has to offer – XML, XML Schema, XSLT, Xpath, DTD, DOM, SAX, etc,etc,etc – plus the ones you know from Oracle’s (object) relational (database) world – SQL, PL/SQL, java, OCI, java, etc.

I am currently working for a customer, who implemented the XML DB functionality in a new system. The total amount of XML data is roundabout 200 Gb. The XML data is split-up in 4 tables, 2 tables containing an XMLType column (XML Schema based, CLOB storage) and 2 XMLType tables (XML Schema based, Object Relational storage).

Some XML documents are more than 200 pages printed data and fairly complex in their structure , sometimes as big as 3 to 4 Mb. So it’s a real challenge to get the data you want, in a decent time period. Even simple questions like “give me a count of…�? or “summerize the following numbers…�? can be tricky if you are dealing with complextype constructions/elements. Usage of COUNT and SUM can be tricky in an XML environment.

The following will give you more insight in Oracle SQL/XPath solutions and the need for a clear understanding of your XML Schema’s and the solutions presented by the Oracle database.

5 Registering non-default XMLDB HTTP/WebDAV and FTP ports on a non-default Oracle Listener port

Yesterday, I had to install XMLDB on an already very heavy used system. On this UNIX system, an old Oracle IAS is installed, OAS 10g (version 9.0.4) and an Oracle database. So in terms of used port numbers, it is a very crowded system. The Oracle listener was split-up by me in two different listeners. I defined a version 9 environment and a version 10 environment. I setup a TNS directory via setting the TNS_ADMIN variable in every profile. It makes stuff more complex, in the context of configuring, but it also centralizes all the SQL*Net information and configuration files on a default spot. By the way, I don’t make use of Oracle Names.

2 Just before X-mas

On the 7th of December, I, a database administrator, had a seminar from Mr. Chris Date about “Relational Remodeled” (into…

1 7 8 9