Cannot delete space!

[trogen]

I loaded up a 450M data file into the database and found out that it was taking too much hard drive space. So when I did a delete from tablea; the data got deleted but the hard drive showed the same amount of space being occupied by the database and no less.
What could be the problem?
Im running oracle in a Linux box and do a df to find out the space remaining.
Thanks.

[kuckoo]

you have deleted Data of that table.Not the datafile that stores data in perticular tablespace.
If u want to freeup space delete that tablespace (datafile on disk) which stores data of the table.

kuckoo

[Dave_A]

Do *not* just delete the data file otherwise Oracle will detect the corruption that occurred as a result of losing an on-line data file. If there are other objects in the datafile, resize the datafile to a smaller size using
alter database datafile 'data_file_name' resize nnnn; (nnnn is the new size). If the tablespace holds no other objects, drop the tablespace in Oracle before deleting the operating system file with the command drop tablespace tablespace_name;


HTH

David.

[trogen]

But that would be deleting the rest of the data in the table. I dont want to delete that data.
Just the 450M of data that I loaded previously.
And that too seems to be deleted as it wont show that particular data if I do a select statement.
But the hard drive still shows the same amount of space being occupied as it was when I loaded the 450M data file.

Also if I decide to delete the data file, how can I do that?
Thanks.

[Dave_A]

Try using the command
alter table table_name deallocate unused;

to return any unused space that is allocated to the table. Then
try
alter database datafile 'datafile_name' resize nnnn;

to shrink the datafile to the desired size. This may not succeed because some of the data may be distributed all over the datafile (you can only shrink the data file by getting rid of the end part of the file so long as it is not occupied by data). In this case, you will probably have to export the data, drop the data, resize the datafile and re-import the data.

If you do decide to delete the datafile, assuming that the tablespace only has this one datafile, first drop the tablespace as in my previous post, then use operating system commands to delete the datafile.

[trogen]

OK, so I tried the resize command but it wont let me get rid of any considerable space, just a little amount.

I dont think I can get rid of the entire tablespace because the tables I created are under the SYSTEM tablespace and getting rid of the SYSTEM tablespace might not be a good idea.

I even tried getting rid of the entire table that I tried loading the 450M data into, and even that didnt help.

What else can I try?

[Dave_A]

Obviously, you have found one of the reasons why you should not create objects in the system tablespace :-( (this tablespace is managed by Oracle, and is optimised to work with system objects). You should always assign a default tablespace, and temporary tablespace to users so that they do not create objects in the system tablespace.

It is not possible to drop the system tablespace and retain a working database.

The only reasonable suggestion that I can offer is to export the tables in question (presumably, all tables in the system tablespace that are not owned by SYS or SYSTEM?), drop the tables, re-create the tables in a sensible tablespace, and re-import the data with the ignore=y flag set. If there are only one or two tables, and you do not have things like constraints on them to worry about, you could create a copy of the tables in a sensible place using

create table copy_tab as select * from original_tab storage(storage_clause) tablespace new_tablespace_name;

then drop the original, then rename the copy to the original name with

rename copy_tab to original_tab;

This may then allow you to resize the data file for the system tablespace to a smaller value.

[trogen]

Ok, so this is what Im going to do.
Ill get rid of the tables that I created in SYS, and create them in another tablespace and see what happens.
Now under what schema do I create the new table and what tablespace should I use?

[Dave_A]

What schema and tablespace you choose depends largely on the usage of the tables when they are created - if they are likely to have high transaction rates against them, then it is probably a good idea to create a dedicated tablespace for them, preferably on a seperate disk (the usual recommendations for seperating data and indexes apply here as well). If they are likely to have a low rate of transactions against them, it is safe enough to put them in a tablespace with other data. (It is still a good idea to seperate data and indexes into different tablespaces, with the datafiles for each on different disks).

The user that is used to create the tables is entirely up to you. You may want to create a new user as a fairly simple means of keeping the data easily distinguishable from other tables (e.g. you can export an entire user, if you have a user dedicated to owning this data, that can make it easy to export only this data). If you do create a new user, be sure to specify a default tablespace and temporary tablespace. If you do not, the default place for object creation and temporary sort segments is the system tablespace. This is a bad thing.