Re: Space

Hi Michael,

Apparently the tablespace is fragmented. You can verify this by statements like

select * from dba_extents, and select * from dba_free_space. If this is a data tablespace, export the tables, drop the user and import again, and the fragementation will be over. You can then safely resize the tablespace.
If this is an index tablespace it is the only issue is dropping and recreating the indexes.
If you don't have the create index statements, you can export the data, import with the following command
imp80 (probably) username/password@<service> file=<name of your export file> full=y INDEXFILE=<some filename>.
This will generate a text file with create index statements. Nothing is actually imported.

Hope this helps,

Sybrand Bakker, Oracle DBA

Michael Reeves wrote:

> We are using Oracle 8.05 on windows NT. I have a tablespace that I needs to
> be smaller. Is there a relatively painless (easy) way to resize this
> datafile?
>
> I have tried to resize the file by issuing the command:
> alter database datafile 'f:\datafile.ora' resize 800m;
>
> This produces the following error:
> ORA 03297 File contains 11450 Blocks of data beyond requested resize
> value.
>
> Oracle Storage Manager shows that only 534m of space is being used. This is
> much smaller than the amount of space specified in the resize file command.
> I want to resize a 2G datafile to 800m, where only 534m of space is being
> used.
>
> What is it that I am missing?
>
> Thanks in advance for any help.
Received on Fri Sep 04 1998 - 13:47:58 CDT