Object Increase Reporting
Using Export Utility
As
an example, let's assume we have an Oracle database 8.1.7.3 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 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - 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
/backup_data/export
$ 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.
Conclusion
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!
Previous
Prev [1] [2]