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

Gotcha, since I re-created the rollback segment with an intial extent of 300M, a bad idea I've since discovered, the only free space was at the end of the previously used area. I've since re-orged, but thanks for the help...

• Dc...

John Higgins wrote:

> 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 Sat Jun 12 1999 - 20:41:04 CDT