Re: drop user user_name cascade - question

On Sun, 13 Oct 2002 15:55:59 +0200, "Tom Miskiewicz" <miskiewicz2_at_no.spam.yahoo.com> wrote:

>> Database configuration, which means
>
>> - how much memory in the system
>2GB
>
>> - settings of db_block_buffers
>db_block_buffers = 75941
>
>> - shared_pool_size
>shared_pool_size = 207370444
>
>> - any other default init.ora setting that should always be changed
>> (there are too many to mention them all)
>where can I read about it then?
>
>> - number of disk drives used.
>2
>
>> - contents of the alert_<sid>.log
>
>Fri Oct 11 11:43:11 2002
>Errors in file
>/export/home/oracle/admin/arstest1/bdump/arstest1_ora_4490.trc:
>ORA-07445: exception encountered: core dump [kcfcbkq()+156] [SIGSEGV]
>[Address not mapped to object]
> [2972260692] [] []
>
>As long as I'm deleting e.g. indexes one by one it's going. Slow, but
>working.
>As soon as I select about 40 I can wait forever. Nothing happens. Help
>please!
>

Depending on the block size of the database (parameter db_block_size, usually 8k) your setting of db_block_buffers looks way too high (some 560 M) and you also have some 200M shared pool. You must be running out of virtual memory, and this suspicision is underpinned by the core dumps you are getting. The guideline of Oracle is to limit the cache to one third of *physical* RAM, your case some 600M You may also suffer from an insufficient log_buffer parameter, an insufficient sort_area_size parameter (the default is 64k, which is just, ahem, ridiculous), and incorrectly sized online redolog files (in this particular case you need to check for 'checkpoint not complete' and 'cannot allocate log, archival required' messages in your alert.

Are the affected objects in a locally managed tablespace or a dictionary managed tablespace? In the latter case, you will be getting so-called space transactions (which involves updating sys.uet$ and sys.fet$) using the ST lock, of which there can be only one.

Is there any chance you can blow away the tablespace instead of the user (after exporting of course), ie using 'drop tablespace including contents'. This won't look at the schema level and 'just do it'

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sun Oct 13 2002 - 09:21:54 CDT