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

From: Doug Cowles <dcowles_at_bigfoot.com>
Date: Thu, 10 Jun 1999 19:14:58 -0400
Message-ID: <376046F2.98D6747A@bigfoot.com>

Sorry - I'm reposting this in the hopes someone will be interested. Hoping it doesn't turn out to be something silly.

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 Thu Jun 10 1999 - 18:14:58 CDT