Wednesday 17 July 2013

Tablespace shrinking

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

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


Hi,

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

 
     BYTES

 
/.../dbsGNX.dbf
 419430400
2.TEST.SQL>ALTER DATABASE DATAFILE '/.../dbsGNX.dbf' RESIZE 390M;
Database altered.
Or Error
ORA-03297: file contains <number> blocks of data beyond requested
RESIZE value
3.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:
 
4.TEST.SQL>SELECT FILE_ID,FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='SYSTEM';
 
   FILE_ID

 
FILE_NAME

 
         1
/.../dbsGNX.dbf

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

      MBID

 
     25129
 
6.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
 
7.TEST.SQL>SHOW PARAMETER BLOCK_SIZE
 
NAME                                 TYPE                             VALUE

 
--------------------------------

 
db_block_size                        integer                          8192
8.TEST.SQL>SELECT 8192*25129 FROM DUAL;
 
8192*25129

 
 205856768

about 200M.

No comments:

Post a Comment