A tablespace with 400,000 free extents

From: Vsevolod Afanassiev <vafanassiev_at_aapt.com.au>
Date: 20 Jul 2002 03:57:23 -0700
Message-ID: <4f7d504c.0207200257.ef5396b@posting.google.com>

I "inherited" a database that has about 400,000 free extents in users tablespace. It works just fine, but all queries that display space are very slow.

My first idea was to coalesce it (ALTER TABLEPACE users COALESCE). Unfortunately, this database has a daily batch job the involves plugging in some tablespaces (using IMP utility). For some strange reasons, IMP was "stuck" when coalesce was running, so I had to kill coalesce.

I am thinking about moving all tables (just a few) from this tablespace and then using ALTER DATABASE <name> DATAFILE OFFLINE DROP. Any suggestions?

Thanks

(Database - 8.1.6.3.0 on Sun Solaris 2.8) Received on Sat Jul 20 2002 - 05:57:23 CDT