datafile usage trouble

[balla]

hi
j have a datafile(owned by tablespace users )
which size is 150M but only 50M are used.
Even if j save new data this datafile is no longer used.
So j wanna store the content of this datafile to another datafile and to delete this one
please help me
thanks

[padmam]

Hi Balla, 2nd Sep 2001 17:50 hrs chennai

1)You can relocate the datafile with "create controlfile" script. Simply modify the locations of the datafiles and run the script.

2)Create a datafile lost or deleted by mistake by a user by applying redo logs.

3)You can also use Transport TS method.

I have not come across copying a datafle contents alone .....

Ok how many datafile's you have on the TS you mention it please .If its going to be one there is plenty of solutions !!!

Awaiting your reply.

Cheers

Padmam

[pando]

you cannot move contents from one datafile to another, you can do it at tablespace level. If you want to move the contents of that datafile to another place you have some options

in Oracle 8i
You can do alter table XXX move tablespace ZZZ, this moves one table from one tablespace to tablespace ZZZ. Do this for all your tables and drop the old tablespace
For indexes you do alter index XXX rebuild ZZZ

in Oracle 8 you probaly have to do Export/Import for tables as for indexes you can still use above option

If what you want to to move one datafile from one directory to another you shutdown the database copy the file from one place to another then startup mount and do a rename of datafiles, I think ths syntax is

ALTER DATABASE
RENAME FILE '/u02/oracle/rbdb1/sort01.dbf',
'/u02/oracle/rbdb1/user3.dbf'
TO '/u02/oracle/rbdb1/temp01.dbf',
'/u02/oracle/rbdb1/users03.dbf;