Re: Tablespace problem
Date: 1996/07/19
Message-ID: <4so2jj$77k_at_crissy.ge.com>#1/1
dogbert_at_lamar.ColoState.EDU (Steven Rorabaugh) wrote:
>... Could anyone please explain to me what ORACLE version 6
>really does when trying to create a table. For example: A database that has
>11,145 blocks of free space (not continuous), and a user tries to create a
>table of 6,000 blocks. ORACLE displays an error 'ORA-01547: FAILED TO
>ALLOCATE EXTENT OF SIZE 6,000 IN TABLESPACE 'tablespace_name''. According to
>the manuals, ORACLE will go out and get non-contiguous free space if one
>continuous free space is not available. That does not seem to be what is
>happening.
(** Meditating to call up old V6 knowledge **) An extent must be contiguous storage. That is true in O7 & V6 (thus your experience). You may simply not have enough space in your tablespace. In the mean time, set initial extent size for the table to a smaller size, and make sure your next extent size is set to a good sized chunk as well. Once the table is created, alter the table's next extent size to what you want. [Scripts for checking free space & space used at the end of this post.]
>... We have exported, recreated the tables, and imported the data, but
>it does not decrease the fragmentation. It only eliminated the various
>extents.
That is all export will do, compress extents; that is what defragging is. What do you think fragmentation is?
>... We tried to drop the tablespace ...
Why? The only reason I can think of would be for OS fragmentation of the tablespace files. I'm not sure that's even a good reason. That's why we have sysadmin's!
>I would appreciate any comments or suggestions that anyone might have
>regarding this problem.
>
>Thank you,
>
>Shari Bishop
>Database Analyst
>
>(Direct replies to sbishop_at_tasc.usda.gov)
This scripts are not particularly clever, but they are useful . And it's free code!!
This tells you your free space & how many chunks it is broken into. This can be misleading since these chunks may be contiguous. Further research may be necessary.
COL "KBYTES FREE" FOR 9,999,999
SELECT TABLESPACE_NAME, COUNT(*) CHUNKS, SUM(BYTES)/1000 "KBYTES FREE"
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME
/
This tells you your total space used by database objects.
COL "KBYTES USED" FOR 9,999,999
SELECT TABLESPACE_NAME, COUNT(*) "OBJECTS", SUM(BYTES)/1000 "KBYTES USED"
FROM SYS.DBA_SEGMENTS
GROUP BY TABLESPACE_NAME
/
Experiment with these tables; they are vital to managing your space requirements.
Hope this helps.
DL Kray Received on Fri Jul 19 1996 - 00:00:00 CEST