Error Ora-3297
Contents |
when resizing adatafile Posted on December 2, 2009 by Carlos Acosta ORA 3297 when resizing a datafile December 2, 2009 · Leave a Comment · Edit This Sometimes we want to shrink a datafile and we ora 03297 file contains used data beyond requested resize value temp get the ORA-3297 error, what? but if there is enough space!! Well ora-3214 , there is an explanation for that, the problem is that there is at least one segment’s extent that alter tablespace fred coalesce; resides in blocks that are beyond the target size. I will do an example with the tablespace users, it is empty and it has only one datafile of 50MB with autoextend how to shrink datafiles in oracle 11g on (my datafiles are ASM, but the example works with regular files too) Now I will create and populate 2 tables, the second much bigger than the first: SQL> create table map1 (col1 number, col2 varchar2(100 char)) tablespace users; Table created. begin for i in 1..20000 loop insert into map1 values ( i,rpad('X',100,'X')); end loop; commit; end; / SQL> create table map2 (col1
Oracle Purge Recycle Bin
number, col2 varchar2(4000 char),col3 varchar2(4000 char)) tablespace users; Table created. begin for i in 1..10000 loop insert into map2 values ( i,rpad('X',4000,'X'),rpad('X',4000,'X')); end loop; commit; end; / Now I create an index on the table MAP1. SQL> create index idx_map1_col1 on map1(col1) tablespace users; Index created. lets check the sizes of our segments: col segment_name format a30 set lines 120 SQL> select segment_name,segment_type, bytes/1024/1024 MB from user_segments; SEGMENT_NAME SEGMENT_TYPE MB ------------------------------ ------------------ ---------- MAP2 TABLE 160 MAP1 TABLE 3 IDX_MAP1_COL1 INDEX .4375 Note: The tablespace has grown according to the new segments on it. I have modified for this example a query from Tom Kyte (original query here) that shows a tablespace map so we can see the segments’ extents and their location in the file. SQL Plus is going to ask for the datafile ID and the block size of it, lets find it out. SQL> show parameter db_block_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_block_size integer 8192 SQL> select file_id from dba_data_files where file_name ='+DATA/rac/datafile/users.267.681841043'; FILE_ID ---------- 5 For this example, block size is 8 and the file_id is 5 Note: If you wan
PROBLEM状态: PUBLISHED内容类型: TEXT/X-HTML创建日期: 20-DEC-2000上次修订日期: 10-JUN-2004Problem Description-------------------In a
Shrink Tablespace Oracle 11gr2
number of cases it is desired to shrink purge recyclebin oracle 11g an oversized datafile. Theproblem is then up to which size can the datafile oracle resize datafile be shrinked? Using the trialand error approach will certainly give:SQL> alter database datafile '/ots3/oradata/v817/oradata/v817/temp01.dbf'2 resize 433000;alter database datafile '/ots3/oradata/v817/oradata/v817/temp01.dbf'*ERROR at https://oracleexamples.wordpress.com/2009/12/02/ora-3297-when-resizing-a-datafile/ line 1:ORA-03297: file contains used data beyond requested RESIZE valueSolution Description--------------------Two options will be described to find the highwatermark of a datafile.1. The first one will use the contents of dba_free_space and has been provento be fastest:rem Subject: Calculation of http://oraclenote.blogspot.com/2005/12/how-to-resolve-ora-03297-when-resizing.html HighwaterMark of datafilesremrem Remarks: minimal size of a datafile is 2 Oracle blocksrem resizing should always be a multiple of Oracle blocksremrem Requirements: select on sys.dba_data_filesrem select on sys.dba_free_spacerem select on sys.v_$parameterremrem --------------------------------------------------------------------set serveroutput onexecute dbms_output.enable(2000000);declarecursor c_dbfile isselect tablespace_name,file_name,file_id,bytesfrom sys.dba_data_fileswhere status !='INVALID'order by tablespace_name,file_id;cursor c_space(v_file_id in number) isselect block_id,blocksfrom sys.dba_free_spacewhere file_id=v_file_idorder by block_id desc;blocksize binary_integer;filesize binary_integer;extsize binary_integer;begin/* get the blocksize of the database, needed to calculate the startaddress */select valueinto blocksizefrom v$parameterwhere name = 'db_block_size';/* retrieve all datafiles */for c_rec1 in c_dbfileloopfilesize := c_rec1.bytes;<
30, 2016 - 3:39 am UTC Category: Database – Version: 11g2 Latest Followup You Asked First of all HAVE A NICE DAY to YOUR TEAM. I resize https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:9527657900346977145 my undo tablespace so i getting following error. ORA-3297 error: file contains http://www.idevelopment.info/data/Oracle/DBA_tips/Tablespaces/TBS_3.shtml used data beyond the requested RESIZE value. what is way to decrease the space of the undo tablespace? How can i see what's data or object on the undo tablespace? and we said... Lets say a tablespace is 100meg in size (and is empty). I then use oracle 11g up 96meg of it with tables...and then drop all the tables except one of them. Yes, the tablespace is nearly empty again ... but that table might be at the very "front" of the file... or it might be right at the "end". Where is sits determines how much I can resize the file downwards. You can run this to in oracle 11g see your potential savings: SQL> select file_name, 2 ceil( (nvl(hwm,1)*8192)/1024/1024 ) smallest, 3 ceil( blocks*8192/1024/1024) currsize, 4 ceil( blocks*8192/1024/1024) - 5 ceil( (nvl(hwm,1)*8192)/1024/1024 ) savings 6 from dba_data_files a, 7 ( select file_id, max(block_id+blocks-1) hwm 8 from dba_extents 9 group by file_id ) b 10 where a.file_id = b.file_id(+) 11 order by 1 12 / FILE_NAME SMALLEST CURRSIZE SAVINGS ------------------------------------------------------------ ---------- ---------- ---------- C:\ORACLE\ORADATA\NP12\SOE.DBF 2468 2600 132 C:\ORACLE\ORADATA\NP12\SYSAUX01.DBF 1596 1670 74 C:\ORACLE\ORADATA\NP12\SYSTEM01.DBF 915 940 25 C:\ORACLE\ORADATA\NP12\UNDOTS01.DBF 63 2387 2324 C:\ORACLE\ORADATA\NP12\USERS01.DBF 7379 7379 0 To resize your undo tablespace, you probably need to create a new one and drop the old one. Some info on that here http://neeraj-dba.blogspot.com.au/2011/05/how-to-drop-undo-tablespace.html Write a Review Set Screen Reader Mode On Integrated Cloud Applications and Platform Services About Oracle Contact Us Legal Notices Terms of Use Your Privacy Rights All information and materials provided here are provided "as-is"; Oracle disclaims all express and implied warranties, including, the implied warranties of merchantability or fitness for a particular use. Oracle shall not be liable for any damages, including, direct, indirect, incidental, special or consequen
Author Introduction In many database configurations, the DBA will choose to allow their temporary tablespace (actually the tempfile(s) for the temporary tablespace) to autoextend. A runaway query or sort can easily chew up valuable space on the disk as the tempfiles(s) extends to accommodate the request for space. If the increase in size of the temporary tablespace (the tempfiles) gets exceedingly large because of a particular anomaly, the DBA will often want to resize the temporary tablespace to a more reasonable size in order to reclaim that extra space. The obvious action would be to resize the tempfiles using the following statement: SQL> alter database tempfile '/u02/oradata/TESTDB/temp01.dbf' resize 250M; alter database tempfile '/u02/oradata/TESTDB/temp01.dbf' resize 250M * ERROR at line 1: ORA-03297: file contains used data beyond requested RESIZE value Ouch. You next bounce the database and attempt the same statement only to be greeted with the same error! Several methods exist to reclaim the used space used for a larger than normal temporary tablespace depending on which release of Oracle you are running. The method that exists for all releases of Oracle is to simply drop and recreate the temporary tablespace back to its original (or another reasonable) size. If you are using Oracle9i or higher, you can apply another method which is to drop the large tempfile (which will drop the tempfile from the data dictionary AND the O/S file system) using the alter database tempfile '