remove datafile from ts

[gatetec]

This was added in error and committed.

alter tablespace D_ORD add datafile '/dev/rdevl_0064_04' size 63M;

How do you remove rdevl_0064_04 from ts D_ORD completely?

thx much

 

[gatetec]

to make sure that no data is written to the datafile, I resized it so that I can drop the datafile safely.
ALTER DATABASE DATAFILE '/dev/rdevl_0064_04' resize 1k;

Pls advise. The datafiles I am using are raw devices.

 

[DrD123]

Hi gatec,

Is the dB in NOARCHIVE mode?

If so, this will take care of it:

ALTER DATABASE DATAFILE '/dev/rdevl_0064_04' OFFLINE DROP;
COMMIT;

Let us know how it worked out for you.

Good Luck
DrD

 

[DrD123]

I hit the submit button too quick while adding the CODE box...

If NOT in NOARCHIVE mode use this:

ALTER DATABASE '/dev/rdevl_0064_04' OFFLINE DROP;
COMMIT;

Good Luck
DrD

 

[gatetec]

I ran it, but if I ran a query below, '/dev/rdevl_0064_04' is still showing up. Is there no way to remove the datafile ('/dev/rdevl_0064_04') completely?

thx much

select
substr(tablespace_name,1,15) ts_name
,substr(file_name,6) fname
,d.file_id file_num
,decode(e.file#,null,d.bytes,(e.maxextend * blksize))/1024/1024 tb
,d.bytes/1024/1024 cb
,decode(d.bytes,0,0,d.bytes-nvl(freebytes,0))/1024/1024 used
,(decode(e.file#,null,d.bytes,(e.maxextend * blksize))
-decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)))/1024/1024 free
,trunc(((decode(e.file#,null,d.bytes,(e.maxextend * blksize))
-decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)))/
decode(e.file#,null,d.bytes,(e.maxextend * blksize))
),2) * 100 percentfree
,decode(e.file#,null,'No','Yes') autoext
from sys.dba_data_files d
,(select file_id,sum(bytes) freebytes
from sys.dba_free_space
group by file_id) f
,sys.filext$ e
,v$datafile v
,(select value blksize from v$parameter
where name = 'db_block_size') b
where d.file_id=f.file_id(+)
and d.file_id=e.file#(+)
and v.file#=d.file_id
order by tablespace_name,creation_time

 

[KenCunningham]

Have you bounced the database since the drop?

I want to be good, is that not enough?

 

[stefanhei]

Hi,

ALTER DATABASE ... OFFLINE DROP does not remove a datafile from the database. You have to use the drop-keyword to take a datafile offline if in noarchivelog mode - in archivelog mode the keyword is ignored.
If you really need to get rid of the datafile you have to drop the tablespace containing it and recreate without that one file.
Find the related doku here.

 

[DrD123]

Hi gatec,

yes it looks like stefanhei solution is the only way to remove it completely. as opposed to it just not being used

1st, export all data, via exp (since this is 9i?), from the tables residing in the offending table space.

Then drop that table space.

Then recreate it with the datafiles you require.

Good Luck
DrD

 

[gatetec]

thx much. I will try that.