Axis Computer Consulting Services, Inc.

I have uploaded a new version of my SQR orphan checker program [here] that now allows you to supress the output of the "0 orphan" record information on the printed report. Great when you are doing a full database scan.

At a client site, I ran this against their HR/SA reporting database that is around 100GB in size (Oracle 9), it contained 9955 physical tables of which 2664 had parent records defined in the tools table. The program completed in 41 minutes, 11 seconds (via SQRW on a Windows 2000 workstation) finding a total of 287,688 orphan rows.

Here is the head and tail end of the SQRW log file:

==============================================================================
accs-orphan-check,v1.1 - ACCS - Orphan Row Checker

Report Started At : 2003-12-17 10:15:41

Database Type : ORACLE
Database Name : xxxxxxx
Platform : WINDOWS-NT
SQR Version : SQR for PeopleSoft/8.19.09/PC/Windows NT 4.0/Oracle
8.0.6/Jul 15 2003

Enter the parent record name (BLANK for all):
Print Parent/Child information for 0 Orphan Counts (Y/n): N


Run Control Parameters
----------------------------

Begin Process Logging
---------------------------

Output Report : c:\accs\work\sqr\output\20031217-101547-xxxxxxx-accs-orphan-check.lis
Processing Began : 2003-12-17 10:15:47

<snip>

Processing Ended : 2003-12-17 10:56:52

Elapsed Time (sec) : 2,465
Elapsed Time : 00:41:05

Program Counts
--------------------

Parent Record : ALL

Total Record Count : 2,417
Total Orphan Count : 287,688


==============================================================================

Report Completed At : 2003-12-17 10:56:52

Elapsed Time (sec) : 2,471
Elapsed Time : 00:41:11

==============================================================================