Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: A tablespace with 400,000 free extents

Re: A tablespace with 400,000 free extents

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Sun, 21 Jul 2002 00:11:26 +1000
Message-ID: <j5e_8.39467$Hj3.119354@newsfeeds.bigpond.com>


Hi Vsevolod,

IF you are not planning to effectively reuse all these extents in the nearish future and IF you only have a few tables AND assuming it's a dictionary managed tablespace, I would recommend:

  1. Create a locally managed tablespace (assuming 8i DB)
  2. Either export/import tables (simple to use but potentially slow) or 'alter table .. move tablespace new' (locks table and needs index to be rebuild but only moves the data in one step) or perform an online redefinition of the tables via dbms_redefinition package (no locking of table but requires 9i).
  3. DROP TABLESPACE now_empty;

You've got to keep yourself busy somehow :)

Good Luck

Richard
"Vsevolod Afanassiev" <vafanassiev_at_aapt.com.au> wrote in message news:4f7d504c.0207200257.ef5396b_at_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 - 09:11:26 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US