Re: Extents - Full or Max?

Thanks to all who responded to my message, here and in e-mail. The problem turned out to be...[cue drum roll]...

SMON! Turns out, when you drop and re-add a schema/user with the same name, repeatedly, SMON doesn't totally clean up after. So eventually, you run out of space, even though it appears you should have enough contiguous space. The fix is simple - just shut down and restart the database.

Unfortunately, by the time I was told this, I couldn't even run dbshut.

So cold-booting hp/ux fixed the problem.

jg

In article <dischner-1001951252170001_at_gkc12a.klch.med.uni-muenchen.de> dischner_at_med.uni-muenchen.de (Anton Dischner) writes:
>Hi Garry,
>
>here is what i do when my tablespaces are fragmented.
>
>Export of the user/tablespace.
>Drop all tables/indexes.
>(Yoy will find this script handy:
>
> Warning !!! This will delete all your tables !!!
> spool delete_all.sql
> select 'drop table '||table_name||';' from user_tables;
> spool off
> start delete_all
> Warning !!! This will delete all your tables !!!
>
>Create a table which allocates all free (fragmented) memory.
>I use server manager to see amount of free space.
>Drop this table.
>
>Import your tables.
>
>Now you should have one extend which holds all the free space.
>(If it is not broken apart due to drive spanning)
>
>I hope this helps,
>best regards,
>
>Toni
>

Toni:

You place much, much more faith in exp/imp than my experience will let me. You also seem to miss my point that the scripts to manage and perform dba tasks wouldn't work because the database was all messed up. If none of that stuff works, why should I expect exp/imp to work? If exp/imp doesn't use the ordinary db mechanisms (and I am uncertain whether it does), why should I think it is more bug free than db stuff that is pounded on continuously? What do you do when the import blows up? Export requires the database to be running - how do you tell if it is running if the dba scripts don't respond? How do you even tell if the db is fragmented in that case?

Thanks for your help, hope you have better luck with exp/imp than I have had over the last 7 years. When I had to deal with production systems, I would use SQL select into flat files in addition to exp/imp as a matter of course. It saved my butt a number of times.

jg

>In article <1995Jan9.221232.3151_at_rossinc.com>, joelga_at_rossinc.com (Joel
>Garry) wrote:
>
>> I have a situation where I am unable to add any more to a tablespace.
>> ORA-01547: failed to allocate extent of size 125 in tablespace XXX
>> (not it's real name).
>>
>> OK, fine, either I'm actually out of space or I've run into the extent
>> limit. This was a little used test database, until the test started to
>> be add and delete a bunch of schemas/users. 100M datafile on oracle7
>> 7.0.16.4 on hp-ux 9.0 created with
>>
>
>
>+--------------------------------------------------------------------+
>| Anton Dischner, DBA and system-programmer Phone: +49 89 70953202|
>| Institut fuer Klinische Chemie Fax : +49 89 70958888|
>| Klinikum Grosshadern Home : +49 89 6254060 |
>| Ludwig Maximilians Universitaet Muenchen Handy: +49 172 8388880|
>| 81366 Muenchen |
>| Marchioninistr. 15 |
>| Germany Mail: dischner_at_med.uni-muenchen.de|
>+--------------------------------------------------------------------+

-- 
Joel Garry           joelga_at_amber.rossinc.com            Compuserve 70661,1534
These are my opinions, not necessarily those of Ross Systems, Inc.
%DCL-W-SOFTONEDGEDONTPUSH, Software On Edge - Don't Push.  
panic: ifree: freeing free inodes...