Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted May 30, 2003

Reporting Database Object Increase On Oracle - Page 2

By Marin Komadina

Object Increase Reporting Using Export Utility

As an example, let's assume we have an Oracle database with one application user, ARTIST. We will generate a table increase report for this user's schema tables. A daily export has been scheduled on the system and we are checking one of the daily export log files:

$ cat exp_artist_20030526_1930.log

Connected to: Oracle8i Enterprise Edition Release - Production
With the Partitioning option
JServer Release - Production
Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user ARTIST
. exporting object type definitions for user ARTIST
About to export ARTIST's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export ARTIST's tables via Conventional Path ...
. . exporting table               DATA_INPUT            16675 rows exported
. . exporting table               DATA_OUTPUT           16979 rows exported
. . exporting table               APP_MESSAGES        3272087 rows exported
. . exporting table               DELTA_MONITOR       1983707 rows exported
. . exporting table               HISTORY_USER         474029 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting snapshots
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

In this export file, we can find the actual number of records for every table.

On the designated directory, we will have export log files (log) and export data files (dmp):

$ pwd
$ ls -lrt 
-rw-r--r--   1 oracle   dba        16726 Feb 10 00:56 exp_artist_20030524_1930.log
-rw-r--r--   1 oracle   dba        32605 Feb 11 06:54 exp_artist_20030525_1930.log
-rw-r--r--   1 oracle   dba      14077308595 Feb 11 06:54 exp_artist_20030525_1930.dmp.Z

We have exported files from 25.05.2003 and log files from 24/25.05.2003.

The previous export file has been deleted, and the log file is still there for comparison.

The UNIX script compare_artist_exports.ksh is used for comparing two export log files.

Resulting report generated by compare_artist_exports.ksh shell script:

Daily Database Grow, 20030525_1930

Old logfile : /backup_data/export/exp_artist_20030524_1930.log
New logfile:  /backup_data/export/exp_artist_20030525_1930.log

Table                       Old Records          New Records          Daily Increase  
DATA_INPUT                  Old: 16675            New: 16685          Delta: 10             
DATA_OUTPUT                 Old: 16979            New: 16979             
APP_MESSAGES                Old: 3272087          New: 3282999        Delta: 10912
DELTA_MONITOR               Old: 5                New: 0              Delta: 5 
HISTORY_USER                Old: 474029           New: 475050         Delta: 1021  

We can easily see the difference in the number or records for user tables. This method is very effective and without additional overhead. While this method will not provide detailed information about actual updates, deletes and inserts, it will give summary information. Information from the export log file can be transformed to disk storage information or used as a source for generating graphical charts.


DBAs can sometimes be stuck between user requests and the technology provided by database and database tools. An appropriate solution does not always need to be a hi-tech solution. Keep it simple!

» See All Articles by Columnist Marin Komadina

Oracle Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM