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

From: Doug Cowles <dcowles_at_bigfoot.com>
Date: Tue, 01 Jun 1999 12:04:19 -0400
Message-ID: <37540482.A789605A@bigfoot.com>

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 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 Tue Jun 01 1999 - 11:04:19 CDT