cancel
Showing results for 
Search instead for 
Did you mean: 

How to Delete Datafiles?

Former Member
0 Kudos

Dear All,

I did online reorganisation on xi server. Ror this I added 10 data files. Now I get a huge space by doing this. I want to delete 7 data fiels form psapsr3 table space. What is procesure to performe this action?

regards,

pramod

Accepted Solutions (1)

Accepted Solutions (1)

former_member204746
Active Contributor
0 Kudos

you cannot delete datafiles like this. once they are created, the only way to get rid of them, is to reorg the full tablespace as per SAP note 646681.

but if you problem is space being used, you can resize your datafile on the fly:

brspace -c -u / -f dfalter

choose option 5 - Resize data file

chosoe the wanted datafile to resize. try to resize to 64 with option 6.

if space was not used and high watermark is not exceeded, it will allow you to resize. if this does not work, use a higher value than 64.

do this for all datafiles that you want to shrink.

stefan_koehler
Active Contributor
0 Kudos

Hello Eric,

> you cannot delete datafiles like this. once they are created, the only way to get rid of them, is to reorg the full tablespace as per SAP note 646681.

That is not correct - if you have Oracle 10g you are able to drop datafiles with some restrictions:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_3002.htm#i2154246

Restrictions on Dropping Files To drop a datafile or tempfile, the datafile or tempfile:

- Must be empty.

- Cannot be the first file that was created in the tablespace. In such cases, drop the tablespace instead.

- Cannot be in a read-only tablespace.

@ Pramod: Which oracle version are you using?

Regards

Stefan

former_member204746
Active Contributor
0 Kudos

Stefan you are right, but he did not state his Oracle version.

and a only a newbie would create so many datafiles without knowing what he really did. so, I do not see how he will be able to check pre-requisites for drop datafiles.

Former Member
0 Kudos

Dear All,

I have Oracle 10.2 g.

Opearting system is windows 2003. this is my XI production system.

I have 5 Drive.i.e C,D,E,F,G.

Each drive now running low space.

Each drive having 1,2 or 3 GB space in OS level.

I have created data file of PSAPSR3 tablespace in each drive for reorganisation.

PSAPSR3 table space having size of 152 GB and free is 85 GB. I reorganize this tablespace.

Now I want to delete data file of C drive which I created latest.

If there is any procesure by which I can drop data files of C drive.

How I can verify if any data in those datafiles.

Regards,

Pramod.

former_member204746
Active Contributor
0 Kudos

if you created 10 datafiles and then reorganized this tablesapce, you will have contents in each of your 10 datafiles. this is the way it usually works with Oracle 10g. do, you won`t be able to delete your datafiles.

a few workaround:

1. reorg this tablespace again

2. resize your datafile to smaller values with the following command:

brspace -c -u / -f dfalter

good luck.

Former Member
0 Kudos

Hi Eric

Do we use BRTOOL to manage tablespace i.e shrinking the tablespace because many time SAP recommends to use BRTOOL to manage the tablespace.

Waiting for your reply.

Regards

Sukrut S

Former Member
0 Kudos

Dear All,

Thank you for you valuable reply.

One more question. After resizing can i move datafiles from C drive to any other dive because i don't want any data files in c:.

warm regards,

Pramod

stefan_koehler
Active Contributor
0 Kudos

Hello Pramod,

yes you can move your datafiles.

> shell> sqlplus "/ as sysdba"

> SQL> shutdown immediate;

> SQL> exit

> shell> move your files to your other drive

> shell> sqlplus "/ as sysdba"

> SQL> startup mount;

> SQL> ALTER DATABASE RENAME FILE '<OLD_PATH>' TO '<NEW_PATH>';

For more examples - take a look at here:

http://www.jlcomp.demon.co.uk/faq/rename_datafile.html

Regards

Stefan

Answers (3)

Answers (3)

Former Member
0 Kudos

Hi pramod,

Go to Oracle Administrative Tools and Log in as sys user. Then delete the datafiles.

Regards..

Santanu

Former Member
0 Kudos

Hello Pramod,

do NOT follow Ganesh's advice:

1) 'alter data file' will not allow you to remove a data file.

2) Dropping a tablespace will delete all of its contents. I suppose this is not want you want to do now, as you just have reorganised your data.

What is you version of Oracle?

For Oracle version up to 9i there is no other choice than reorganising again.

For Oracle 10g you may drop data files, but first make sure that no data are stored in it. How can you know that 7 of your data files are not needed?

Be careful, and take a backup before trying anybody's advice!

Former Member
0 Kudos

Dear Pramod,

Use BRTOOLS for the same.

1) select Space management

2)Alter data file and remove the data file that you have added in excess.

or

1) select space mangement

2) drop table space- this will drop whole tablespace.

3) then create a new one with limited file system.

Best Regards,

Ganesh