For this type of analysis you'll be using SDO_JOIN and/or SDO_OVERLAPS.
Roughly how many rows are in each table? If the tables aren't too large, then I would probably just bung all the data into one table and then look for overlaps via a SDO_JOIN self-join. If this isn't practical, then you'd have to check each table against each other table and build up a list of overlapping features.
John is correct about running everything against everything else to determine what overlaps what. As John mentioned you don't really explain the relationship between the five tables. I would guess you want one or more "flattened" feature classes (no one overlaps no one else) so then next step would be to identify and divvy up the overlap in some manner. You could well have three or even more polygons all overlapping to one degree or another in places. How will you identify and process each overlap?
I'd say this is tacking very close to topology and that might be the easier route in the end. Just slam it all into a topology and then see which faces are shared by which topology geometries. That face is the overlap, all identified and sliced up ready to deal with. You could edit the topology to resolve the overlaps (yeah I know that's easy to say) and then when its all nice and clean export the layer back to SDO_GEOMETRY.
Just a thought. it would save you having to write all the code to do the relating and intersecting. And you'd learn a lot about Oracle topology in the process.
Depending on what is in the different tables, it may also be worthwhile to generate the aggregate boundary of every table, and then compare those five areas to see if they overlap. But it depends on what kind of data it is, and what you actually try to achieve.
Thanks to all.
Every table contains the polygon geometry and all together there would be more than 100,000 (cumulative of all the tables) rows. I am trying to identify the polygons which are overlapping with each other and i would have to list down the ordinates of part of the polygon edge which is overlapping with other.
If i use the option SDO_OVERLAPS, i might get the list of polygons overlapping with neighboring polygons, but i would not know where exactly is the overlap. Say, the polygon's perimeter is few thousand meters, it would be difficult to find the overlap, if it is too small (less than 1 meter).
Solution to that is not that difficult: create a new table, and whenever your routine finds an overlap, insert the SDO_INTERSECTION together with the ID's in the new table. That way you can easily find where the overlaps are.
Do you have any visualization tool available? Visually these kinds of things are usually fairly easy to spot, and if the number of overlaps is not too large you could even manually edit them if necessary.
Hmm. If you want a free one, there is an addon for SQL Developer that can visualize geometry (GeoRaptor I think it's called). If you want a full-blown GIS, all the big boys can use oracle Spatial directly these days (ESRI, Intergraph, MapInfo, Bentley Map) but usually cost quite a bit in licensing. Oracle's own MapViewer is pretty good too for visualization. Or GeoServer, but that takes a bit of work because you'll need a webviewer too (OpenLayers would be the suggested one).
These are the ones I'm more or less familiar with and can say anything about. There may be others as well, but I've never had the need to search for anything.