RE: Tablespace (datafile reducing)

From: Suhen Pather <Suhen.Pather_at_strandbags.com.au>
Date: Thu, 13 Sep 2001 17:04:14 -0700
Message-ID: <F001.0038DFCE.20010913165517@fatcity.com>

!! Please do not post Off Topic to this List !!

Kishore,

Having too many datafiles is not good for performance, updating datafile headers during checkpointing.

If you are on 8i you can create a 2GB tablespace, and use the alter table move command to move all the tables in that tablespace to the new 2GB tablespace. All associated indexes will become unusable, so make sure you alter index rebuild ...

If the tablespace has indexes rather than tables then you could do a alter index rebuild ... to move them to the new tablespace.

If you are not on 8i then you can use export/ import. Export all tables in the tablespace,
Drop the tables,
Drop the tablespace,
Recreate new tablespace (same name) with single datafile, Import all tables

Regards
Suhen

!! Please do not post Off Topic to this List !!

Hello DBAs

I have a tablespace , who has 10 datafiles of 200MB each, it was here before I even joined. I was thinking for the contention and want to combine them all to one single large datafile of 2gig.

How do I get rid off all those datafiles and ghet them in one large datafiles??

Any help in this regard

Thank you very much

Kishore

---

Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Kishore
  INET: mcgoracle_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Suhen Pather
  INET: Suhen.Pather_at_strandbags.com.au

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Sep 13 2001 - 19:04:14 CDT