how to delete a datafile

[lhugh]

* I created a tablespace userK and its datafile userk.dbf
* Took the tablespace offline
* drop the tablespace including contents

when I tried to delete the datafile, Windows gave me an error.

** Version of Oracle 8i personal
** Operating system Windows 98

 

[SantaMufasa]

LHugh,

Although you successfully dropped the tablespace, Windows (and other operating systems) retain the file "handles" until your shutdown Oracle itself. So, to be able to actually drop the file that resided under your former tablespace, I recommend your doing a shutdown (immediate) on your database (or stop your Oracle service under Windows), then you should be able to delete the datafile.

Please post your results,

Dave
Sandy, Utah, USA @ 20:42 GMT, 13:42 Mountain Time

 

[lhugh]

Thank you very much, your idea works.

I forgot to mention that I did a shutdown immdediate right after dropping the tablespace. And the problem that I have with Oracle 8i personal version is that the sys user doesn't seem to have enoug privileges???

Anyhow, I turned off the computer, restarted and was able to delete the file.

 

[SantaMufasa]

LHugh,

As far as you "SYS" user not having enough privileges, I believe your problem might actually be your Windows login user. To determine if that is the problem, navigate: Start..Settings..Control Panel..Users and Passwords, and see if your login user is in the group "ORA_DBA". If your Windows login is not a member of that group, that explains why even when you log in to Oracle as "SYS" you still don't have enoungh permission to start and stop the database. Either modify your Windows login to be an ORA_DBA group member, or log in to Windows using a username that is a member of the ORA_DBA group.

Please post your results once you use an ORA_DBA-group login.

Dave
Sandy, Utah, USA @ 22:08 GMT, 15:08 Mountain Time

 

[lhugh]

Thank you Dave

That might be the problem. I am using Oracle 8i personal version on my laptop and Win 98 doesn't have groups.

By the way, when you install Oracle, there are some users that come with the sample database. How do you enable them? For example, if user Scott is not active, how do you enable him?

Thanks

 

[SantaMufasa]

To enable pre-installed (disabled) user accounts, use this syntax:

&quot;alter user <name> account unlock;&quot;

Dave
Sandy, Utah, USA @ 19:51 GMT, 12:51 Mountain Time.

 

[Turkbear]

Hi,
With the database down and all Oracle-related services stopped ( using the windows control panel)and after rebooting, you should be able to delete any file as long as your Windows rights are sufficient since Oracle no longer is involved.
-click the datafile and check its properties (especially the security and owner)

 

[gunjan14]

Hi,

I am new to Oracle DBA but this problem is related to what I have propose to my client. I want to know whether it is really required to shut down the database to remove the datafiles after dropping the tablespace? And is there any workaround &quot;without dropping the tablespace and then deleting the datafiles&quot; to get back the space?

Any information will be great help.

 

[lhugh]

in Oracle 9i, if you use OMF, you can just drop the datafile and Oracle will delete the file for you. Prior to 9i, you must go in and manually delete the datafile even if you have drop it off the controlfile

 

[Turkbear]

Hi,
And you must shutdown the database to delete the file - Windows will refuse to delete it as long as the Oracle services are running...Look at the other set of postings in this group ( titled 'Deleting Data file' - [one day I am going to learn how to specify a thread by number ])

( You will get a 'file is in use' type error.)

 

[bi]

Turkbear, you just have to very carefully point but not touch the thread number. You need to get about a pixel or two away from where it starts or ends. then drag your mouse over, copy and then paste.