Re: But there really isn't anything in the tablespace

Doug,

You are missing the point of my previous replies.

Your query of dba_extents returns no rows because this view only shows extents which are allocated to segments.

However, this does NOT mean that the tablespace is one, big mass of freespace. If you would query dba_freespace, you would see that the tablespace is fragmented into many freespace extents. In fact, when you dropped the rollback segments, every allocated extent was converted to a freespace extent.

When you create a new segment (rollback, table, index), Oracle first searches the freespace extents to find an EXACT match for the initial extent. If no EXACT match is found, Oracle lookes for a freespace extent that is larger than the request and breaks off an extent of requested size. As a last resort, Oracle will coalesce adjacent freespace extents into larger extents and then repeat searches 1 and 2.

So, in your case, new extents were created beyond the old freespace extents. The new segments were NOT created at the beginning of the file, so they extend farther into the file than you expected. THAT is why you cannot shrink the file as much as you wanted.

Here's what you can do:

1) Drop the segments
2) ALTER TABLE ____ COALASCE;
3) Re-create your segments
4) Now you can shrink the file

The COALASCE step causes Oracle to merge all the freespace extents into one, big freespace extent. Then Oracle can break off the size requested at the start of the file.

Doug Cowles wrote:

> Sorry - I'm reposting this in the hopes someone will be interested.
> Hoping it doesn't turn out to be something silly.
>
> - Dc.
>
> I understand your responses - but as near as I can figure, there really
> isn't anything else in the tablespace. See the following
> spool file generated when I do this. I query dba_Rollback_segs,
> dba_data_files, and dba_extents to show that rollback
> segment RBSBIG2 resides in a tablespace, which has a corresponding data
> file. Since you cannot have more than one
> tablespace in a datafile there can't be anything else in that datafile
> but this particular tablespace. I query the dba_Extents view
> to show that after dropping the rollback segment, nothing appears to
> belong to that tablespace. I then re-create the tablespace,
> attempt to shrink the datafile, and get an error. Don't understand.
>
> SQL> @rollsize;
>
> NAME EXTENTS RSSIZE OPTSIZE
> HWMSIZE
> ------------------------------ ---------- ---------- ----------
> ----------
> XACTS STATUS
> ---------- ---------------
> SYSTEM 13 835584
> 835584
> 0 ONLINE
>
> RBS10 2 20955136 20971520
> 62898176
> 0 ONLINE
>
> RBSBIG1 2 366985216 367001600
> 366985216
> 0 ONLINE
>
> NAME EXTENTS RSSIZE OPTSIZE
> HWMSIZE
> ------------------------------ ---------- ---------- ----------
> ----------
> XACTS STATUS
> ---------- ---------------
> RBSBIG3 2 366985216 367001600
> 366985216
> 0 ONLINE
>
> SQL> alter rollback segment RBSBIG2 online;
>
> Rollback segment altered.
>
> SQL> @rollsize
>
> NAME EXTENTS RSSIZE OPTSIZE
> HWMSIZE
> ------------------------------ ---------- ---------- ----------
> ----------
> XACTS STATUS
> ---------- ---------------
> SYSTEM 13 835584
> 835584
> 0 ONLINE
>
> RBS10 2 20955136 20971520
> 62898176
> 0 ONLINE
>
> RBSBIG1 2 366985216 367001600
> 366985216
> 0 ONLINE
>
> RBSBIG2 2 366985216 367001600
> 366985216
> 0 ONLINE
>
> RBSBIG3 2 366985216 367001600
> 366985216
> 0 ONLINE
>
> SQL> alter rollback segment RBSBIG2 offline;
>
> Rollback segment altered.
>
> SQL> select * from dba_data_files where tablespace_name = 'RBSBIG2';
>
> FILE_NAME
> --------------------------------------------------------------------------------
>
> FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
> ---------- ------------------------------ ---------- ----------
> ---------
> RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY
> ------------ --- ---------- ---------- ------------
> /mmsoradata05/oradata/MMSPMART/bigrbstwo01.dbf
> 28 RBSBIG2 1572864000 96000
> AVAILABLE
> 28 NO 0 0 0
>
> SQL> alter database datafile
> 2 '/mmsoradata05/oradata/MMSPMART/bigrbstwo01.dbf' resize 600M;
> alter database datafile
> *
> ERROR at line 1:
> ORA-03297: file contains 1281 blocks of data beyond requested RESIZE
> value
>
> SQL> select segment_name from dba_extents where tablespace_name =
> 'RBSBIG2';
>
> SEGMENT_NAME
> --------------------------------------------------------------------------------
>
> RBSBIG2
> RBSBIG2
>
> SQL> select * from dba_Rollback_segs where segment_name = 'RBSBIG2';
>
> SEGMENT_NAME OWNER TABLESPACE_NAME
> SEGMENT_ID
> ------------------------------ ------ ------------------------------
> ----------
> FILE_ID BLOCK_ID INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS
>
> ---------- ---------- -------------- ----------- ----------- -----------
>
> PCT_INCREASE STATUS INSTANCE_NUM
> ------------ ---------------- ----------------------------------------
> RELATIVE_FNO
> ------------
> RBSBIG2 SYS
> RBSBIG2 13
> 28 20482 314572800 52428800 2 2147483645
>
> 0 OFFLINE
> 28
>
> SQL> select * from dba_data_files where tablespace_name = 'RBSBIG2';
>
> FILE_NAME
> --------------------------------------------------------------------------------
>
> FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
> ---------- ------------------------------ ---------- ----------
> ---------
> RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY
> ------------ --- ---------- ---------- ------------
> /mmsoradata05/oradata/MMSPMART/bigrbstwo01.dbf
> 28 RBSBIG2 1572864000 96000
> AVAILABLE
> 28 NO 0 0 0
>
> SQL> drop rollback segment RBSBIG2;
>
> Rollback segment dropped.
>
> SQL> select * from dba_extents where tablespace_name = 'RBSBIG2';
>
> no rows selected
>
> SQL> create rollback segment RBSBIG2
> 2 tablespace RBSBIG2
> 3 storage (initial 300M next 50M maxextents unlimited optimal 350M);
>
> Rollback segment created.
>
> SQL> alter database datafile
> 2 '/mmsoradata05/oradata/MMSPMART/bigrbstwo01.dbf' resize 600M;
> alter database datafile
> *
> ERROR at line 1:
> ORA-03297: file contains 1281 blocks of data beyond requested RESIZE
> value
>
> SQL> alter rollback segment RBSBIG2 online;
>
> Rollback segment altered.
>
> SQL> spool off
>
> Doug Cowles wrote:
>
> I could be just tired - but somthing's bugging me. I have three
> rollbacks,
> intitially sized at 70M on datafiles of 1.5 GB each. One tablespace
> per
>
> datafile, and one rollback segment for each tablespace. Nothing else
> in
>
> the tablespaces but their respective rollbacks.
>
> I wanted to change the intial size of the three rollbacks to 300M, and
>
> then
> shrink the datafiles to 600M. I dropped the three rollbacks, (which I
>
> thought
> would clear out the datafiles completely), and then recreated them
> with
> the new
> storage parameters. When I try to shrink the datafiles to 600M, it
> tells me
> , ORA-03297: file contains 641 blocks of data beyond the requested
> RESIZE value.
>
> Now, it's Friday, and I'm leaving this one, but instinct tells me if I
>
> re-create the
> tablespace, everything will be dandy.. but how can this happen? If
> I've
> dropped any
> segments that are in that tablespace, corresponding to a rollback
> segment whose
> high water mark never exceeded 300M, why would there be data or
> allocated extents
> floating around the 600M mark??
>
> Thanks in advance,
> Dc.
Received on Fri Jun 11 1999 - 08:52:07 CDT