Shouvik Basu's blog

If there is a task in Oracle for which the wheel has been reinvented many times, it is that of generating database object DDL. There are numerous scripts floating in different forums doing the same thing. Some of them work great, while others work only until a specific version. Sometimes the DBAs prefer to create the scripts themselves. Apart from the testing overhead, these scripts require substantial insight into the data dictionary. As new versions of the database are released, the scripts need to be modified to fit the new requirements.

Starting from Oracle 9i Release 1, the DBMS_METADATA package has put an official end to all such scripting effort. This article provides a tour of the reverse engineering features of the above package, with a focus on generating the creation DDL of existing database objects. The article also has a section covering the issue of finding object dependencies.

This article introduces Oracle XML DB features to the DBAs and Developers who are not actively working with XML. It offers a quick start to those who finds quite a lot of Oracle XML literature around, and who is not sure where to begin.

This article is the result of observations of the UNDO tablespace of Oracle 9i and Oracle 10g in various situations. We start with a simple query showing how to monitor the amount of undo generated in a session for a specific time. We investigate the creation, expansion, and resize of UNDO tablespace, and the issues that guide the reuse of UNDO segments. The impact of parameters like UNDO_RETENTION in Oracle 9i and UNDO_RETENTION and the GUARANTEE clause in CREATE UNDO statements is discussed using simple reproducible examples.

(OLAP reporting embedded in SQL)

Much of the OLAP reporting feature embedded in Oracle SQL is ignored. People turn to expensive OLAP reporting tools in the market - even for simple reporting needs. This article outlines some of the common OLAP reporting needs and shows how to meet them by using the enhanced aggregation features of Oracle SQL.

The article is divided in two sections. The first introduces the GROUP BY extensions of SQL, and the second uses them to generate some typical reports. A section at the end introduces the common OLAP terminologies.

This article provides a clear, thorough concept of analytic functions and its various options by a series of simple yet concept building examples. The article is intended for SQL coders, who for might be not be using analytic functions due to unfamiliarity with its cryptic syntax or uncertainty about its logic of operation. Often I see that people tend to reinvent the feature provided by analytic functions by native join and sub-query SQL. This article assumes familiarity with basic Oracle SQL, sub-query, join and group function from the reader. Based on that familiarity, it builds the concept of analytic functions through a series of examples.