Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: how to shrink data file
The only way I know (for ORACLE 7.3/8.0.5, at least) is
export everything in this datafile -> drop everything in this datafile ->
SQL> alter tablespace <the_tablespace_with_this_file> coalesce;
SQL> alter database datafile <your_datafile> resize 800M;
-> import everything back
ORACLE scatters data blocks all about the datafile, that's why you cannot
simply "cut" the empty tail, and ORACLE cannot cut out free pieces of a
datafile.
-- A.M. Andreyev, ORACLE DBA "zhang liang" <novazl_at_263.net> wrote in message news:95avs7$1n6p$1_at_news.cz.js.cn...Received on Thu Feb 08 2001 - 09:17:27 CST
> After checking dba_free_space, I found that "system" tablespace has 1.67G
> free bytes. Since the data file for "system" tablespace is about 2.1G, I
try
> to use the following command to resize file system01.rbf to about 800M:
>
> alter database datafile '/oracle/u01/oradata/wapdb/system01.rbf' resize
> 800M;
>
> But the following error message was returned:
>
> ORA-03297: file contains used data beyond requested RESIZE value
>
>
> The same kind of error also occurs when I try to shrink file "rbs01.dbf",
> which holds tablespace "rbs".
>
>
>
>
>