Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Resizing Datafile
You have allocated space near the end of the datafile. In order to get rid
of it, you can do the following;
first : create a new tablespace the size you need + a little growing room.
for each index issue the following
alter index rebuild tablespace my_new_ts;
after all are moved to the new tablespace, you can now shrink the old ts. once resized (remember to leave some free space) alter index rebuild tablespace my_old_ts; You can now drop the newly created tablespace. Remember to delete the datafile also;
You can use a sql statement to create the script to move the indexes back
and forth;
select 'alter index '||index_name||' rebuild tablespace my_new_ts;' from
all_indexes where tablespace_name = 'MY_OLD_TS';
spool that out ot a file with a .sql extension; When you want to move it back just swap OLD and NEW ts names.
--
Bob Fazio
Remove no.spam from my email to reply
Daniel Wiechczyński <D.Wiechczynski_at_um.zielona-gora.pl> wrote in message
news:X%eN3.7884$%3.136077_at_news.tpnet.pl...
> Hello !
>
> I have problem with resizing a datafile. It belongs to tablespace,
that
> contains indexes. Thanks to informations in view DBA_FREE_SPACE I know
that
> it contains
> free space and I want to make it a little smaller.
>
> But when I'm executing command:
>
> ALTER DATABASE DATAFILE 'filename' RESIZE new size
>
> where new size, is not smaller than (current size) minus (free space in
> file)
>
> i receive message:
>
> ORA-03297:file contains 22875 blocks of data beyond the requested RESIZE
> value
>
>
> What is important, sum of this value (22875) and my requested rezize value
> is equal to current
> file size. In this moment I understand that a free space in file means
> nothing for this operation.
>
> It is possible to do so ? What schould I do ?
>
> I thank in advance for a responce
>
> Versions:
> RDB Oracle Workgroup Server 7.3.2.3 fot WIN NT
>
>
Received on Thu Oct 14 1999 - 22:52:54 CDT