Oracle 11g Streams Synchronous Capture

Have you ever wanted just to maintain a table in two different databases? In two different schemas? Something like replicating, keeping a copy of a table in sync in another database, not in real-time, but almost real time? There is an easy way, use Oracle Streams Synchronous Capture!

Many times in development I get this request, where a developer in a team will come with a request like "Can we get a copy of that table in our schema in some sort of sync please?". Alternative ways of providing the developer with this table would be backup/restore, data-pump, both (more...)

Web services

I have been going around websites/wikis to find a definition for, what is a webservice? Especially I was interested to find out the kind of web services, which of course, have to do with databases, Oracle in particular. I spend the whole day looking at certain websites and here is my DIY one-liner definition:

Web services is all about give and take data over HTTP. When you give data you are a 'provider' of a web service and when you take data you are a 'consumer' of a web service.

Oracle 11g is doing both.

You can turn your (more...)

CONNECT_BY_ROOT with XMLAGG

This is a post about Oracle hierarchical queries. The other day I was after a solution which would display the hierarchy in the execution of some PL/SQL procedures. That is, there was  a main procedure which would call several dozens of other procedures in a predefined order. This order was pre-recorded in a table. What I was after was the ancestral relationship in the order of executions. To demonstrate simply, say there is table called steps, which looks like this:



MAIN_ID                DEPENDANT_ID           
---------------------- ----------------------
1 2
1 3
2 4
2 3

Process MAIN_ID 1 depends on the execution of (more...)

Materialized View Refresh and the ATOMIC_REFRESH parameter

When refreshing big materialized views in large data warehouses it is always good to check the parameter options available in the DBMS_MVIEW.REFRESH procedure.

Oracle changes the default parameters of its DBMS packages from release to release. I remember back at the times of Oracle 9i a complete refresh would truncate the materialized view, thus the only work that the database was actually doing in a complete refresh, was just an INSERT after the TRUNCATE.

Now in Oracle 10g and Oracle 11g parameters have changed. When there is a COMPLETE materialized view refresh, for the purposes of data preservation, a (more...)

Migrate your Oracle 10g database to ASM

This blog entry is about migrating your Oracle 10g database to ASM using OEM Database Control on ubuntu 9.10 Karmic Koala. To be able to do this you must have:
  • CSS service running on your ubuntu box
  • ASM instance running
  • OEM Database control running
To resolve your problems of starting the Oracle CSS daemon and ASM instance running on Ubuntu Karmic Koala, follow the resources I give below.

You will have to do some hocus-pocus with some parameters to adjust memory, improvise disks with dd (cause I only had one) and CSS script startup hick-ups. Insist with the resources excession. (more...)