5 Replies Latest reply: Sep 15, 2005 4:49 AM by Nicolas.Gasparotto RSS

    How to shrink the system tablespace datafile Size

    418937
      iam using oracle 9i R2 and i want to reduce my datafile size but it's show's that error when i try to resize it. ORA-03297
        • 1. Re: How to shrink the system tablespace datafile Size
          65070
          If you try to resize a datafile to a size smaller than is needed to contain all
          the database objects in that datafile, you will get an error:

          ORA-03297: file contains <number> blocks of data beyond requested
          RESIZE value
          • 2. Re: How to shrink the system tablespace datafile Size
            Nicolas.Gasparotto
            bilal,

            To reduce system tablespace size, you can export full database, recreate database and import full.

            For a non system tablespace, the way to reduce tbs size is :
            1. create a new tbs1
            2. move all segments from old tbs0 to new tbs1
            3. recreate the tbs0 with the good size
            4. move all segments from tbs1 to tbs0
            5. drop working tbs tbs1
            Note if you are in oracle 10g, you can rename tbs.

            I think that is not very prudent to make this way for system tbs.

            Nicolas.
            • 3. Re: How to shrink the system tablespace datafile Size
              418937
              so we can't shirink the system tablespace in Oracle 9i.
              • 4. Re: How to shrink the system tablespace datafile Size
                376714
                Hi,

                We can directly resize datafiles
                TEST.SQL>SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='SYSTEM';

                FILE_NAME
                ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                     BYTES
                ----------
                /.../dbsGNX.dbf
                419430400


                TEST.SQL>ALTER DATABASE DATAFILE '/.../dbsGNX.dbf' RESIZE 390M;

                Database altered.

                TEST.SQL>SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='SYSTEM';

                FILE_NAME
                ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                     BYTES
                ----------
                /.../dbsGNX.dbf
                408944640
                But the minimum file size is the size of the extend the furthest in the datafile:
                TEST.SQL>SELECT FILE_ID,FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='SYSTEM';

                   FILE_ID
                ----------
                FILE_NAME
                ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                         1
                /.../dbsGNX.dbf


                TEST.SQL>SELECT MAX(BLOCK_ID) MBID FROM DBA_EXTENTS WHERE FILE_ID=1;

                      MBID
                ----------
                     25129

                TEST.SQL>SELECT SEGMENT_NAME,OWNER,SEGMENT_TYPE FROM DBA_EXTENTS WHERE FILE_ID=1 AND BLOCK_ID=25129;

                SEGMENT_NAME                                                                      OWNER                          SEGMENT_TYPE
                --------------------------------------------------------------------------------- ------------------------------ ------------------
                I_OBJAUTH2                                                                        SYS                            INDEX

                TEST.SQL>SHOW PARAMETER BLOCK_SIZE

                NAME                                 TYPE                             VALUE
                ------------------------------------ -------------------------------- ------------------------------
                db_block_size                        integer                          8192
                TEST.SQL>SELECT 8192*25129 FROM DUAL;

                8192*25129
                ----------
                205856768
                about 200M.

                Regards,

                Yoann.
                • 5. Re: How to shrink the system tablespace datafile Size
                  Nicolas.Gasparotto
                  But the minimum file size is the size of the extend the furthest in the datafile:
                  Yoann is right, if you want to reduce size under the occupations spaces, even if of all obviousnesses there are holes, you will receive ORA-03297.

                  Nicolas.