Solved

Deleting Datafiles to Reclaim Space

Posted on 2007-04-11
2
428 Views
Last Modified: 2013-12-19
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

System: Microsoft Windows Server 2003 Enterprise Edition
----------------------------------------------------------
Hello how are you today?

We have just implemented SAP in our environment. We were finding out that one of our tablespaces on our XI database kept on growing. To accommodate the growth we kept on adding datafiles. We went from 4 datafiles to 20 datafiles on drive G:\ORACLE\PXI\SAPDATA1 and 2 datafiles on N:\ORACLE\PXI\SAPDATA2. Our database is now at 150,000.00 Mb
Just recently we were able to run some delete jobs supplied from SAP. We were able to get all our datafiles under 20% used.

We now would like to delete all our datafiles and reclaim all our disk space.
What is the best way of doing this??

Can you please provide procedures on suggested ideas.

Thank you for your time.
0
Comment
Question by:algotube
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 

Author Comment

by:algotube
ID: 18892774
We now would like to delete all our datafiles and reclaim all our disk space.

Sorry what I was trying to say is delete the extra datafiles added accept for the 4 original ones.

Regards
0
 
LVL 3

Accepted Solution

by:
IanStuart earned 250 total points
ID: 18897812
I'd suggest to drop the tablespace including the datafiles and then recreate it.
drop tablespace TABLESPACE_NAME including contents and datafiles;
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question