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 ENABLED CHECKPOINT BYTES CREATE_BYT NAME
------ ------- ---------- ---------- ---------- ---------- ----------------
5 ONLINE READ WRITE 7450 2097152 102400
/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_NAME FILE_ID BLOCK_ID BYTES BLOCKS
------------------ ---------- ---------- ---------- ----------
TEMP 4 2 102400 50
TEMP 4 55 96256 47
TEMP 4 102 1890304 923
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: data file <number>: '<full_path_name>'
ORA-09971: sfsfs: write error, unable to write header block.
If you check V$DATAFILE, you will see that the file size does not change
unless
the operation is successful.
Finally, if using multiple database writers (db_writers > 1), you might run
into [BUG:311905]. This appears as ORA-7374 errors when accessing the
datafile
after it has been resized. The workaround is to shutdown and restart the
database after resizing a datafile (a convenient time to take a backup).
This
will cause the new datafile size information to be refreshed to all the dbwr
slave processes. This bug is fixed in RDBMS version 7.3.3.
MANUAL versus AUTOMATIC extension:
Be careful giving datafiles the AUTOEXTEND attribute, certainly specify the
NEXT and MAXSIZE parameters, set appropriate values for the default storage
parameters on tablespace level and MAXEXTENTS; for objects in these
tablespaces
specify explicitly INITIAL and NEXT extent size as well as MAXEXTENTS; avoid
UNLIMITED sizes and extents to prevent objects with a very high number of
extents will be created; this causes not only a huge number of records in
the
dictionary tables, but dropping them will take a very long time while smon
is
consuming all cpu resources it can get.
FINDEXT.SQL
Script to find database object locations for a given datafile.
-----------CUT-----------------CUT-----------------CUT--------
REM findext.sql
REM
REM This script prompts user for a datafile ID number, and
REM then lists all the segments contained in that datafile,
REM the blockid where it starts, and how many blocks the
REM segment contains. It shows the owner, segment name, and
REM segment type.
REM
REM Janet Robinson Stern April 2, 1997
REM variation on Cary Millsap's script
REM
SET ECHO OFF
ttitle -
center 'Segment Extent Summary' skip 2
col ownr format a8 heading 'Owner' justify c
col type format a8 heading 'Type' justify c trunc
col name format a28 heading 'Segment Name' justify c
col exid format 990 heading 'Extent#' justify c
col fiid format 9990 heading 'File#' justify c
col blid format 99990 heading 'Block#' justify c
col blks format 999,990 heading 'Blocks' justify c
select
owner ownr,
segment_name name,
segment_type type,
extent_id exid,
file_id fiid,
block_id blid,
blocks blks
from
dba_extents
where
file_id = &file_id
order by
block_id
/
-----------------CUT--------------CUT---------------CUT------
Example Output:
SQL> @findext.sql
Enter value for file_id: 5
old 12: file_id = &file_id
new 12: file_id = 5
Segment Extent Summary
Owner Segment Name Type Extent# File# Block# Blocks
-------- ------------ -------- ------- ------ ------- --------
USER EMP TABLE 0 5 2 5
USER TAB3 TABLE 0 5 108 5
USER TEST TABLE 0 5 348 5
USER PK_EMP INDEX 0 5 483 5
USER EMP TABLE 1 5 433 5
USER EMP TABLE 2 5 438 10
USER PK_EMP INDEX 1 5 488 10
References:
===========
[BUG:311905] RESIZING DATAFILE - NEWSIZE NOT REFRESHED FOR SLAVES IF USING
MULTIPLE DBWRITERS
Search Words:
=============
RESIZE SHRINK FILE DATAFILE ALTER ORA-923 ORA-3297 ORA-1237 ORA-1110
ORA-9971
.
----------------------------------------------------------------------
end note
Best Regards,
Nabila Mekkaoui
DBA Oracle
-----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: INF/MEKKAOUI
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).
