![Ora-03297 File Contains Used Data Beyond Tt Ora-03297 File Contains Used Data Beyond Tt](http://4.bp.blogspot.com/_fXRVHxVyES0/TRIi93Eki9I/AAAAAAAAA-c/Ngyt3HUT82I/s1600/vb4_03.jpg)
>What command, Can I executed for reduze the dbf.? > NONE! As already stated above you can NOT release space that is being used. You have to first MOVE the objects that occupy that space.
ORA- 0. 32. 97 - Oracle Practicals.. ORA- 0. 32. 97. Let's see how to troubleshoot and understand the issue ORA- 0. RESIZE value. Introduction. Every other DBA knows the actual information is stored/resides in data files at OS level. During the course of administration of. В databases we might saw that size of data files would have gone increased huge over a period time.
В and some of the DBA's would thought of. В resizing the data file. Since, due to huge number of inserts/deletes across the segments, there would a free chunks of memory fragmented across and which going wasted. During the resizing of data files usually most of the dba's would came across the above issue. В First let us try to undersand the issue.
ORA-03297: file contains used data beyond requested RESIZE value. Answer: First. The Oracle documentation notes the following about ORA-03297: ORA-03297: file contains used data beyond requested RESIZE value. Cause. Every Oracle Database must have a way of maintaining information that is used to rollback changes to the database. This information consists of records of the actions of transactions, primarily before they are committed. All. This is because the high water mark on the tablespace extends beyond 12 GB on the tablespace. If you were running Oracle 10g and had Automatic Storage Management on the tablespace and row movement enabled on the tables, you.
В in very much insight of it and let. В us try to resolve the. В issue. Systematic Approach. Understanding the Issuelet us understand the error message. ORA- 0. 32. 97 file contains < num> blocks of data beyond requested RESIZE value.
В There exists some of blocks beyond the size when we tried to resize the data file below that size the issue raised. Let us check and find out what would be the actual value of the resize of particular tablespace/data files. Calcualte the Size of Datafile/ Extents.
The below query provides the information regarding the size of data files. SQL> select tablespace_name,bytes/1. Size_MB, bytes Size_bytes. В 2. В from dba_data_files.
В 3. В where file_id=4. В 4. В ; TABLESPACE_NAMEВ В В В В В В В В В В В В В В В В В SIZE_MB SIZE_BYTES- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - - -- -- -- -- - - -- -- -- -- -USERS1. В В В В В В В В В В В В В В В В В В В В В В В В 2.
Oracle Practicals. Search this site. Home. About me. Linux. Kernel Parameters. Shell Tutorials. Oracle. Let's see how to troubleshoot and understand the issue ORA-03297 file contains used data beyond requested RESIZE.
В 2. 28. 64. 69. Get Blocks details with the help of below query. SQL> select f. В 2. В from dba_data_files f,В 3. В dba_tablespaces t. В 4. В where f.
В 5. В and t. status = 'ONLINE'В 6. В and t. tablespace_name='USERS1'В 7. В order by f. tablespace_name,f.
TABLESPACE_NAME- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -FILE_NAME- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -В В FILE_IDВ В В В BLOCKS BLOCK_SIZE- -- -- -- -- - - -- -- -- -- - - -- -- -- -- -USERS1. D: \APP\PAVAN\ORADATA\ORCL\USERS0.
DBFВ В В В В В В В 4. В В В В В 2. 79. В В В В В В 8. 19. В Blocks - 2. 79. В - Size of the file in Oracle blocks. Check the maximum block id from the extents, which provides actual information regarding the objects space with with in dafa files.
Check the below query which provides the information. SQL> select a. В 2. В from dba_extents a, (В 3. В select file_id, max(block_id) max_id. В 4. В from dba_extents. В 5. В where tablespace_name = 'USERS1'В 6. В group by file_id) b.
В 7. В where a. USERS1'В 8. В and a. В 9. В and a. block_id = b.
В 1. 0В ; В В FILE_ID TABLESPACE_NAMEВ В В В В В В В В В В В В В В В В BLOCK_ID BLOCKS+BLOCK_ID- 1- -- -- -- -- - - -- -- -- -- -- -- -- -- -- -- -- -- -- -- - - -- -- -- -- - - -- -- -- -- -- -- -- -- В В В В В В В В 4 USERS1. В В В В В В В В В В В В В В В В В В В В В В В В В В В В В 2. В В В В В В В В В В В В 2. Now, we got the information in terms of blocks. From the above query we got the information about block limit usage with in data file (2. Now, just. В mutiple the things to sizes by utilizing the db_block_size value.
The above value is equal to the result provided from dba_data_files. Now, if you find any difference between the two results sets then that would be space reclaim from that particular data file. I had created script which provides the resize value based on above calculations.
C: \spool> sqlplus / as sysdba. SQL*Plus: Release 1. Production on Tue Dec 7 1. Copyright (c) 1. 98. Oracle. В All rights reserved. Connected to: Oracle Database 1. Enterprise Edition Release 1.
Production. With the Partitioning, OLAP, Data Mining and Real Application Testing options. SQL> @tabspace_resize_val. USERS1 8. 19. 2В В В В В В В В В В В В В В В В В В В В Tablespace Resize Report - Starts Here. File. В В В В В В В В В В В В В В В В В В В В В В В В В В В В В В В В В В В В В В В В В Size. В В В High Water. В В В В Space. В Name.
В В В В В В В В В В В В В В В В В В В В В В В В В В В В В В В В В В В В В В (Bytes) Mark (Bytes)В В В Reclaim- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - - -- -- -- -- - - -- -- -- -- -- - - -- -- -- -- -D: \APP\PAVAN\ORADATA\ORCL\USERS0. DBFВ В В В 2. 28. В В В 2. 28. 64. В В В В В В В В В 0.
В В В В В В В В В В В В В В В В В В В В В Tablespace Resize Report - Ends Here. В Now, we can resize the data file as below. В ALTER DATABASE DATAFILE D: \APP\PAVAN\ORADATA\ORCL\USERS0. DBF RESIZE TO 2. 28.
High Water. В Mark. Other References.