RE: resize in 7.3.4 urgent
Bonjour Theorically it's possible. The condition is that there is a free space at the end of the datafile. Best Regards, Kamel Benlatreche -Message d'origine- De : Djaroud Salim [mailto:[EMAIL PROTECTED]] Envoyé : mardi 17 juillet 2001 17:05 À : Multiple recipients of list ORACLE-L Objet : resize in 7.3.4 urgent hi, i want if any body try to resize a datafile to a lower value. i'm on oracle 7.3.4 on hpunix 10.0 thanks in advance -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Djaroud Salim 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: GL2Z/ INF DBA BENLATRECHE 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: resize in 7.3.4 urgent
see this note from Oracle but be careful . Doc ID: Note:1029252.6 Type: BULLETIN Status: PUBLISHED Content Type: TEXT/PLAIN Creation Date: 03-APR-1997 Last Revision Date: 27-APR-2001 PURPOSE This bulletin covers a method for resizing datafiles with the ALTER DATABASE DATAFILE RESIZE command. SCOPE APPLICATION Instructional. How to Resize a Datafile: = Datafile management has two sides to it: not enough room for existing datafiles, or not enough room IN existing datafiles. Typical solutions are to drop and recreate the tablespace with different sized datafiles, or to add more datafiles to a tablespace. To make solving these issues easier, Oracle has come up with a way to resize the datafiles for the database. Starting with RDBMS 7.2, you can use the new command for datafiles, called RESIZE. This option allows you to change the physical size of a datafile from what was specified during its creation. I. Increase Datafile Size II. Decrease Datafile Size III. Cautions and Warnings Attempting to use the RESIZE command on versions prior to 7.2 will receive the following error: ORA-00923: FROM keyword not found where expected I. INCREASE DATAFILE SIZE To increase the size of a datafile, you would use the command: ALTER DATABASE DATAFILE 'full_path_name' RESIZE integer [K|M]; where the size specified is larger than the existing file size. Check V$DATAFILE for current settings. The BYTES column shows the current size of the datafile, and the CREATE_BYTES column shows what the size was specified when the file was created. The size of the datafile will also be changed at the operating system level. For example: FILE# STATUS ENABLEDCHECKPOINT BYTES CREATE_BYT NAME -- --- -- -- -- -- 5 ONLINE READ WRITE 7450 2097152102400 /databases/oracle/test.dbf As you can see, the file was created with a size of 100K (CREATE_BYTES) and was increased to a size of 2MB (BYTES) with the RESIZE command. II. DECREASE DATAFILE SIZE To decrease the size of a datafile, you use the same command, but specify a size smaller than the existing datafile. For example, we could reduce the file above back to 1MB with the command: ALTER DATABASE DATAFILE '/databases/oracle/test.dbf' RESIZE 1MB; Downsizing a datafile is more complicated than increasing the size of a datafile. You cannot deallocate space from a datafile that is currently being used by database objects. To remove space from a datafile, you have to have contiguous space at the END of the datafile.Check the view DBA_FREE_SPACE to see how much space is not being used in a datafile. For the above file we get: SELECT * FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME=TEMP ORDER BY BLOCK_ID; TABLESPACE_NAMEFILE_IDBLOCK_ID BYTES BLOCKS -- -- -- -- -- TEMP4 2 102400 50 TEMP4 55 96256 47 TEMP41021890304923 As you can see, there are two large extents at the high end of the datafile (BLOCK_ID = 55 and contains 47 blocks, BLOCK_ID=102 and contains 923 blocks). This means there are 1986560 unused bytes at the end of our datafile, almost 2MB. We want to leave some room for growth in our datafile, and depending on how the objects in that datafile allocate new extents, we could remove easily up to 1.89MB of disk space from the datafile without damaging any objects in the tablespace. If you have a large extent in the middle of a datafile, and some object taking up room at the end of the datafile, you can use the query FINDEXT.SQL to find this object. If you export this object, then drop it, you should then free up contiguous space at the end of your datafile so you will be able to resize it smaller. Make sure you leave enough room in the datafile for importing the object back into the tablespace. III. CAUTIONS AND WARNINGS For safety reasons, you should take a backup of your database whenever you change its structure, which includes altering the size of datafiles. 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 The resize operation will fail at this point. If you try to resize a datafile larger than can be created, you will also get an error. For instance, in trying to create a file of 2GIG, without 2GIG of available disk space you will get something similar to: ORA-01237: cannot extend datafile number ORA-01110:
RE: resize in 7.3.4 urgent
If you want to see a more visual representation of what's in a tablespace
(including free space) use the mapper.sql from the old DBA Handbook (don't
have it right here in front of me so I don't know the author's name). This
is for Oracle 7.3.4.
--
rem
rem file: mapper.sql
rem parameters: the tablespace name being mapped
rem
rem Sample invocation:
rem @mapper SYSTEM
rem
rem This script generates a mapping of the space usage
rem (free space vs used) in a tablespace. It graphically
rem shows segment and free space fragmentation.
rem
set pagesize 66 linesize 132 verify off
ttitle 'Map of Tablespace ' 1 right datevar skip 1
column substr(file_id,1,4) heading File|Id
column bytes format 999,999,999,999
column today noprint new_value datevar
SELECT
to_char(sysdate, 'MM/DD/YY') today,
'free space' owner, /*owner of free space*/
' ' object, /*blank object name*/
substr(file_id,1,4), /*file ID for the extent header*/
block_id, /*block ID for the extent header*/
blocks, /*lengthof the extent in blocks*/
bytes /*length of the extent in bytes*/
FROM sys.dba_free_space
WHERE tablespace_name = upper('1')
UNION
SELECT
to_char(sysdate, 'MM/DD/YY') today,
substr(owner,1,10), /*owner name (first 20 chars)*/
substr(segment_name,1,27), /*segment name (first 26 chars)*/
substr(file_id,1,4), /*file ID for extent header*/
block_id, /*block ID for block header*/
blocks, /*length of the extent in blocks*/
bytes /*length of the extent in bytes*/
FROM sys.dba_extents
WHERE tablespace_name = upper('1')
ORDER BY 4,5
/
undefine 1
--
--
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: resize in 7.3.4 urgent
DBA Handbook is by Kevin Loney. as is Oracle8 DBA Handbook and Oracle8i DBA
Handbook (this last with Marlene Theriault as co-author). and the
to-be-published (no, I don't have a date, they have just started working on
it!) Oracle9i DBA Handbook
From: [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: resize in 7.3.4 urgent
Date: Wed, 18 Jul 2001 07:01:36 -0800
If you want to see a more visual representation of what's in a tablespace
(including free space) use the mapper.sql from the old DBA Handbook (don't
have it right here in front of me so I don't know the author's name). This
is for Oracle 7.3.4.
--
rem
rem file: mapper.sql
rem parameters: the tablespace name being mapped
rem
rem Sample invocation:
rem @mapper SYSTEM
rem
rem This script generates a mapping of the space usage
rem (free space vs used) in a tablespace. It graphically
rem shows segment and free space fragmentation.
rem
set pagesize 66 linesize 132 verify off
ttitle 'Map of Tablespace ' 1 right datevar skip 1
column substr(file_id,1,4) heading File|Id
column bytes format 999,999,999,999
column today noprint new_value datevar
SELECT
to_char(sysdate, 'MM/DD/YY') today,
'free space' owner, /*owner of free space*/
' ' object, /*blank object name*/
substr(file_id,1,4), /*file ID for the extent header*/
block_id, /*block ID for the extent header*/
blocks, /*lengthof the extent in blocks*/
bytes /*length of the extent in bytes*/
FROM sys.dba_free_space
WHERE tablespace_name = upper('1')
UNION
SELECT
to_char(sysdate, 'MM/DD/YY') today,
substr(owner,1,10), /*owner name (first 20 chars)*/
substr(segment_name,1,27), /*segment name (first 26 chars)*/
substr(file_id,1,4), /*file ID for extent header*/
block_id, /*block ID for block header*/
blocks, /*length of the extent in blocks*/
bytes /*length of the extent in bytes*/
FROM sys.dba_extents
WHERE tablespace_name = upper('1')
ORDER BY 4,5
/
undefine 1
--
--
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).
_
Get your FREE download of MSN Explorer at http://explorer.msn.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
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: resize in 7.3.4 urgent
Yep. That's the one. First book on my Oracle bookshelf.
Rachel
Carmichael To: Multiple recipients of list ORACLE-L
carmichr@hot[EMAIL PROTECTED]
mail.comcc:
Sent by: Subject: RE: resize in 7.3.4 urgent
root@fatcity.
com
07/18/2001
12:17 PM
Please
respond to
ORACLE-L
DBA Handbook is by Kevin Loney. as is Oracle8 DBA Handbook and Oracle8i DBA
Handbook (this last with Marlene Theriault as co-author). and the
to-be-published (no, I don't have a date, they have just started working on
it!) Oracle9i DBA Handbook
From: [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: resize in 7.3.4 urgent
Date: Wed, 18 Jul 2001 07:01:36 -0800
If you want to see a more visual representation of what's in a tablespace
(including free space) use the mapper.sql from the old DBA Handbook (don't
have it right here in front of me so I don't know the author's name).
This
is for Oracle 7.3.4.
--
rem
rem file: mapper.sql
rem parameters: the tablespace name being mapped
rem
rem Sample invocation:
rem @mapper SYSTEM
rem
rem This script generates a mapping of the space usage
rem (free space vs used) in a tablespace. It graphically
rem shows segment and free space fragmentation.
rem
set pagesize 66 linesize 132 verify off
ttitle 'Map of Tablespace ' 1 right datevar skip 1
column substr(file_id,1,4) heading File|Id
column bytes format 999,999,999,999
column today noprint new_value datevar
SELECT
to_char(sysdate, 'MM/DD/YY') today,
'free space' owner, /*owner of free space*/
' ' object, /*blank object name*/
substr(file_id,1,4), /*file ID for the extent header*/
block_id, /*block ID for the extent header*/
blocks, /*lengthof the extent in blocks*/
bytes /*length of the extent in bytes*/
FROM sys.dba_free_space
WHERE tablespace_name = upper('1')
UNION
SELECT
to_char(sysdate, 'MM/DD/YY') today,
substr(owner,1,10), /*owner name (first 20 chars)*/
substr(segment_name,1,27), /*segment name (first 26 chars)*/
substr(file_id,1,4), /*file ID for extent header*/
block_id, /*block ID for block header*/
blocks, /*length of the extent in blocks*/
bytes /*length of the extent in bytes*/
FROM sys.dba_extents
WHERE tablespace_name = upper('1')
ORDER BY 4,5
/
undefine 1
--
--
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).
_
Get your FREE download of MSN Explorer at http://explorer.msn.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
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
Re: resize in 7.3.4 urgent
as long as the tablespace "high water mark" is low enough, it should not be a problem. joe [EMAIL PROTECTED] 07/17/01 01:05PM hi,i want if any body try to resize a datafile to a lower value.i'm on oracle 7.3.4 on hpunix 10.0thanks in advance-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Djaroud Salim 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: resize in 7.3.4 urgent
I use the resize option quite often. If the developers give inaccuract data usage for a table space then a lot of space is wasted. For datafiles that only contain one large table you populate the table and then resize the datafile to fit with minimum waste. If you have a table space that spans multiple datafiles and contains multiple tables you can resize each datafile individually to reduce waste. One other side benefit is your backup size can be reduced if you are in the practice of doing a clod backup and copying the datafiles. ROR mª¿ªm [EMAIL PROTECTED] 07/17/01 01:05PM hi, i want if any body try to resize a datafile to a lower value. i'm on oracle 7.3.4 on hpunix 10.0 thanks in advance -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Djaroud Salim 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: resize in 7.3.4 urgent
You can issue the command: Alter database datafile '/u001/oradata/mydatafile01.dbf' resize 1MB Some caveats: 1. BACK UP THE DATABASE BEFORE YOU DO THIS 2. The space you are dropping must be contiguous free space at the end of the datafile. Check dba_free_space to see how much you can decrease and if colaesce might help you. 3. If you are using multiple dbwriters, you might have problems accessing a resized datafile. It's an Oracle bug I saw in one of my v7x databases (that I haven't had a problem with in 8i). You'll have to shiutdown and restart. 4. BACK UP THE DATABASE AFTER YOU DO THIS David A. Barbour Oracle DBA, OCP AISD 512-414-1002 Djaroud Salim SDJaroud@france-se To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cours.comcc: Sent by: Subject: resize in 7.3.4 urgent [EMAIL PROTECTED] 07/17/2001 12:05 PM Please respond to ORACLE-L hi, i want if any body try to resize a datafile to a lower value. i'm on oracle 7.3.4 on hpunix 10.0 thanks in advance -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Djaroud Salim 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).
