free up datafile space problem ???
Hi all, I have a data file of 2G and the free bytes is 1,413,079, the used bytes is only 683,829. I like to resize it. When I issue: alter database datafile '/orafs02/oradata/PV_A0725/ld_data05.dbf' resize 1000M, I got: ERROR at line 1: ORA-03297: file contains used data beyond requested RESIZE value But apparently, the file does NOT contain data beyond 1000M. Am I missing something? Thank you. Janet __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janet Linsy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: free up datafile space problem ???
its like the high water mark in a table but its at the datafile side instead. if any block in the datafile has ever had data in it, you can't shrink it below that point. hth, joe [EMAIL PROTECTED] 08/07/01 02:40PM Hi all,I have a data file of 2G and the free bytes is 1,413,079, the used bytes is only 683,829. I like toresize it. When I issue:alter database datafile'/orafs02/oradata/PV_A0725/ld_data05.dbf' resize1000M, I got:ERROR at line 1:ORA-03297: file contains used data beyond requestedRESIZE valueBut apparently, the file does NOT contain data beyond1000M. Am I missing something?Thank you.Janet__Do You Yahoo!?Make international calls for as low as $.04/minute with Yahoo! Messengerhttp://phonecard.yahoo.com/-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Janet Linsy INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: free up datafile space problem ???
Janet, Possibly there were other tables in the tablespace using space in the datafile and then some tables were deleted leaving the tablespace fragmented. To completely clean the tablespace and free the space you have to export the contents of the tablespace( all tables), drop the tables, coalesce the tablespace, then recreate the tables, import the data and check the grants on the tables, Your tablespace should be contiguous and allow you to shrink the datafile to a smaller space. ROR mª¿ªm [EMAIL PROTECTED] 08/07/01 02:40PM Hi all, I have a data file of 2G and the free bytes is 1,413,079, the used bytes is only 683,829. I like to resize it. When I issue: alter database datafile '/orafs02/oradata/PV_A0725/ld_data05.dbf' resize 1000M, I got: ERROR at line 1: ORA-03297: file contains used data beyond requested RESIZE value But apparently, the file does NOT contain data beyond 1000M. Am I missing something? Thank you. Janet __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janet Linsy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: free up datafile space problem ???
I just sent this to the other list... Here is a quick script to list the tablespace/datafiles and their higwater marks, as well as generate the resize commands; --begin script col blksz new_value blksz noprint; col tablespace_name for a20 heading Tablespace|Name; col file_name for a50 heading File|Name; col file_id for 999 heading File|Id; col file_size for 999,999.99 heading File|Size MB; col file_hw for 999,999.99 heading HighWater|Size MB; set linesize 132; set verify off; select value blksz from v$parameter where lower(name) = 'db_block_size'; select a.tablespace_name, b.file_name, b.file_id, b.bytes/1024/1024 file_size, ((c.max_blockid+a.blocks) * blksz)/1024/1024 file_hw from dba_extents a, dba_data_files b , (select file_id, max(block_id) max_blockid from dba_extents group by file_id) c where a.file_id=b.file_id and a.file_id=c.file_id and a.block_id = c.max_blockid order by 1,2; -- Create resize file prompt Listing resize commands... prompt set pagesize 0; select 'alter database datafile '||||b.file_name||||' resize '||round(((c.max_blockid+a.blocks) * blksz)/1024/1024)||'M ;' from dba_extents a, dba_data_files b , (select file_id, max(block_id) max_blockid from dba_extents group by file_id) c where a.file_id=b.file_id and a.file_id=c.file_id and a.block_id = c.max_blockid; quit --end script -Original Message- Sent: Tuesday, August 07, 2001 2:41 PM To: Multiple recipients of list ORACLE-L Hi all, I have a data file of 2G and the free bytes is 1,413,079, the used bytes is only 683,829. I like to resize it. When I issue: alter database datafile '/orafs02/oradata/PV_A0725/ld_data05.dbf' resize 1000M, I got: ERROR at line 1: ORA-03297: file contains used data beyond requested RESIZE value But apparently, the file does NOT contain data beyond 1000M. Am I missing something? Thank you. Janet __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janet Linsy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Travis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: free up datafile space problem ???
Janet, You can only reduce the datafile by the amount of contiguous free space at the end of the datafile. Back up your DB before you try any of this. I never play with tablespace and datafiles without a current backup. Use alter tablespace coalesce, then query dba_free_space, dba_data_files and dba_segments to see how much reduction you can get. If you've got a bunch of free space sandwiched in between used space, you're going to have to do an export/import to reorg the tablespace and significantly reduce the datafile size(s). David A. Barbour Oracle DBA, OCP AISD 512-414-1002 Janet Linsy janetlinsy@ya To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] hoo.com cc: Sent by: Subject: free up datafile space problem ??? [EMAIL PROTECTED] om 08/07/2001 01:40 PM Please respond to ORACLE-L Hi all, I have a data file of 2G and the free bytes is 1,413,079, the used bytes is only 683,829. I like to resize it. When I issue: alter database datafile '/orafs02/oradata/PV_A0725/ld_data05.dbf' resize 1000M, I got: ERROR at line 1: ORA-03297: file contains used data beyond requested RESIZE value But apparently, the file does NOT contain data beyond 1000M. Am I missing something? Thank you. Janet __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janet Linsy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: free up datafile space problem ???
I think you may find that your 'hwm' comment is a version dependent thing, and a restriction on RMAN which always seems to back up to the tablespace 'hwm'. Certainly from 8.0.5 onwards, I have reduced file sizes before setting tablespaces readonly by fiddling about with: alter table move alter index rebuild and then resizing data files downwards. Jonathan Lewis Seminars on getting the best out of Oracle Last few places available for Sept 10th/11th See http://www.jlcomp.demon.co.uk/seminar.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 07 August 2001 21:50 its like the high water mark in a table but its at the datafile side instead. if any block in the datafile has ever had data in it, you can't shrink it below that point. hth, joe -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
