These days I made some experiments with the new
table compression feature in Oracle11g. And I wanted (of course) to know about
the effect of compressing a table - so I had to determine the sizes of a compressed and
a non-compressed table as well. While I worked trough that I realized that this is not
that easy task as I assumed - derminining the size of a table (particular for tables
containing lobs or partitioned tables) requires some effort ...

The USER_SEGMENTS view furthermore just returns the size of the
allocated extents. There is no information about how they are
filled up with data. A table having one extend of 64MB allocated and
containing only one row shows up as segment of 64MB size - which is
correct, since USER_SEGMENTS returns the allocated size
of a segment. But in reality the table might be much smaller ...

We therefore have to determine the actual segment usage - and for this
purpose the PL/SQL package DBMS_SPACE is present. Its procedure UNUSED_SPACE
analyzes table, cluster and index segments and returns
information about unused and used blocks and bytes. For LOB segments
there is another procedure: SPACE_USAGE which is overloaded for
Basicfile lobs as well as for securefile lobs. For Securefile
Lobs the returned numbers are much more detailed.
is

With those thoughts in mind I wrote the following PL/SQL table function GET_SPACE_INFO.
For a given table it looks up all dependent segments and analyzes them with
DBMS_SPACE. The code is written for Oracle11g and this version
does not run in 10g due to the code handling securefile LOBs. The run
it in 10g the securefile LOB code has to be removed.

Another nice example is the SALES table in the SH sample schema. This shows
nicely that a partitioned table contains many segments (each partition is a segment). The
following example shows a table with a spatial data column (SDO_GEOMETRY). The
spatial coordinates are stored within a VARRAY and this is stored as a LOB - which gives
a LOB segment.

The first question arising is what deferred means: The answer is simple. Deferred means that the particular constraint is checked after the transaction is finished with a COMMIT. The "normal" behaviour is immediate checking just after the DML command was executed.

Imagine two tables: KUNDEN (for Customers) and and ADRESSEN (for addresses). One customer can have multiple addresses but one of these addresses must be marked as "primary" address. So the tables are being created as follows ...

... which means that you cannot insert any row. The foreign keys model a circular reference - to create a customer you need the primary address. But you need the customer in order to create an address. So you're chasing your own tail.

But there is a solution. Since Oracle8i there are deferrable constraints - the constraint is created in that manner that it's checking is being performed after the commit. This is being achieved in two steps: First the constraint must be marked as deferrable and then the check setting must be changed from immediate to deferred. The following constraint definitions do both:

The set constraint [[Name]|all] [deferred|immediate] command switches the checking behaviour for one or all constraints between immediate and deferred. It's only valid for constraints created as deferrable.

Last October I published a posting about viewing tracefiles with SQL and PL/SQL but this was only "supported" for Oracle11g. I promised to post a version for Oracle9i and Oracle10g as well - here it is ...

Sometimes there are situations where developers have to look into the database's tracefiles. Here are two examples ...

If you activate a SQL trace to analyze a particular SQL command then this information is being written into a tracefile

If a java stored procedure (java in the database) throws an exception the java error stack is by default written to a tracefile

... and this means that you have to log into the operating system of the database machine, change to the tracefile directory, lookup the file, process it with tkprof (when necessary) and finally view the results. This costs at least time and in some cases you don't even have credentials for the operating system.

I started using the SQL query in this blog posting from Pas Apicalla. I extended it a bit so that it returns the correct filename also for shared server sessions. Make sure that you have SELECT privileges on the here used V$-Views.

The function GET_SESSION_TRACE_FILE reads just the tracefile content and returns it as a CLOB. This is helpful when java stored procedures throw exceptions - you then see the java error stack in the returning CLOB.

The function GET_SESSION_TKPROF_TRACE executes tkprof on the session's tracefile and returns the tkprof output as a CLOB. During this process a temporary file with the tkprof output is being created, its contents are then being copied into a CLOB and finally it's being deleted. The directory into which this temporary file is placed, is determined by the PL/SQL constant C_OUTFILE_PREFIX - so you might want to adjust this according to your environment. The directory and the filename prefix can also be adjusted by the procedure SET_OUTPUT_TEMPFILE_PREFIX.

The procedure SET_OUTPUT_TEMPFILE_PREFIX is used to set the directory and filename prefix for the temporary file generated by tkprof.

Your database schema needs some privileges in order to use the package. Just run the following script
as the SYS user and change the TRCTEST user to the database user you're working with.

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle Corporation or Oracle Germany. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine.