Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Resizing Datafile

Re: Resizing Datafile

From: Bob Fazio <bob_fazio_at_hotmail.com.no.spam>
Date: Fri, 15 Oct 1999 03:52:54 GMT
Message-ID: <qyxN3.2839$yp.13788@news.rdc1.pa.home.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US