Reporting Database Object Increase On Oracle - Page 2May 30, 2003 Object Increase Reporting Using Export UtilityAs 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. ConclusionDBAs 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! |
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
