Re: Find Empty Tables

From: Marc Blum <marc_at_marcblum.de>
Date: Sat, 31 May 2003 16:49:03 +0200
Message-ID: <25ghdv08jp9ksd05hoira2783t9akv3e7n@4ax.com>

On Thu, 29 May 2003 21:19:11 -0400, "Charles Davis" <cdavis10717_at_comcast.net> wrote:

>Hi, all!
>
>I manage several SAP R/3 databases that each contain 25,000 tables and
>exceed 1TB in size. They are of both 8i and 9i versions.
>
>Does anyone have a process or query that scales up well and that can find
>tables that are empty without doing a SELECT count(*) on each of the tables?
>
>Thx.
>
>charles

a SQL*Plus-script will do the job

SET pages 0
SET head OFF
SET feed OFF
SET TRIMSPOOL ON
SET lines 1000
SET DEFINE OFF
SET TERMOUT OFF
SPOOL tmp.SQL
SELECT 'SELECT '''||table_name||''' as tab FROM '||table_name||'
WHERE ROWNUM = 1;'
FROM user_tables;
SPOOL OFF
SET TERMOUT ON
@@tmp.SQL

---

Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Sat May 31 2003 - 09:49:03 CDT