Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

Delete or reduce a file in oracle

Ignacio Rodríguez Arrós
Frequent Advisor

Delete or reduce a file in oracle

How can i delete or reduce the size of a file in a tablespace in oracle if it is not totaly empty but i dont care the data on it
9 REPLIES
Christian Gebhardt
Honored Contributor

Re: Delete or reduce a file in oracle

Hi

ALTER DATABASE DATAFILE '' RESIZE ;

this expands or reduces the file to the new size if possible.
If the file contains data you have to drop or remove this objects.

If you dont care about the data drop the tablespace and recreate it with new size.

Chris
Andreas D. Skjervold
Honored Contributor

Re: Delete or reduce a file in oracle

Hi

Use
DROP TABLESPACE your_ts INCLUDING CONTENTS;

Andreas
Only by ignoring what everyone think is important, can you be aware of what everyone ignores!
Andreas D. Skjervold
Honored Contributor

Re: Delete or reduce a file in oracle

and the you'll have to go outside svrmgrl and issue an OS command to delete the file itself, as the DROP command don't do this.

Andreas
Only by ignoring what everyone think is important, can you be aware of what everyone ignores!
Ian Lochray
Respected Contributor

Re: Delete or reduce a file in oracle

If you are using Oracle 9i you could add the "and datafiles" clause to the drop tablespace command and it will delete the data files at the O/S level.
Ignacio Rodríguez Arrós
Frequent Advisor

Re: Delete or reduce a file in oracle

First thanks all for the answers, but i Know i can drop the tablespace and recreate it, but what i want to discover is how to delete one especific file in the ts (or reduce it) without touch the others, when this file it??s no empty. ??How can i make it empty (If it??s possible)?
Brian Crabtree
Honored Contributor

Re: Delete or reduce a file in oracle

Most likely, it is not possible to reduce a datafile past the size where there are used blocks in it. You could shrink the file down to the smallest possible, and then rebuild the objects in the datafile, which would force them into another datafile, and you could continue to reduce after that.

To do something like this, you would need to find the objects in the datafile with a command like the following:

select segment_name from dba_extents where block_id = (select max(block_id) from dba_extents where file_id = ;

You will need to get the file number from dba_data_files for the file you are trying to reduce. This would give you the object that you need to move next. Once this object is rebuilt to another datafile, you would need to reduce the datafile again, and find the next object to move.

Brian
Ignacio Rodríguez Arrós
Frequent Advisor

Re: Delete or reduce a file in oracle

I want to tanks all of you for your answers althougt like i thougth there is no way to do exatly what i pretend.
R. Allan Hicks
Trusted Contributor

Re: Delete or reduce a file in oracle

As the other responds have suggested, you can drop the tablespace including contents and (with Oracle 9i) datafiles.

What you may want to do is

* export the tablespace
* drop the tablespace and remove the files.
* recreate the tablespace with the file(s) in the desired location and with the desired size.
* import the tablespace
"Only he who attempts the absurd is capable of achieving the impossible
Ignacio Rodríguez Arrós
Frequent Advisor

Re: Delete or reduce a file in oracle

I??m sorry for the points, i??m trying to put them but althougth i do all the process when i return to the message the points i have assigned have desapear, it happend me other times and i don??t know how to solve it.