Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> max number of handles 9.2, win32, partitioning

max number of handles 9.2, win32, partitioning

From: Paul Drake <>
Date: Thu, 29 Jul 2004 16:48:16 -0700 (PDT)
Message-ID: <>

keywords so I can find this later:

max number of filesystem handles under a single oracle.exe process win32 w2k

win32: w2k adv svr sp4
Oracle EE + partitioning
(might go soon)

What is the largest number of user sessions * datafiles = filesystem handles that you've encountered in win32 land?

I had the bright idea of partitioning several large tables at a client site that are frequently hit, that typically supports 250 simultaneous sessions. Normally, most users will only actively hit the most recent partition for those tables and their indexes, but I am somewhat concerned that if we managed to hit say 10,000 handles that I could be in for quite a surprise. That evaluation server went back to the manufacturer prior to me getting to test destructively
(the fun part).

I'm currently seeing around 6000 handles for 225 threads in a non-partitioned setup for a single oracle.exe process.

currently using 2 tablespaces for data (128KB and 8 MB extents, unform LMTs) 2 tablespaces for indexes (128 KB and 2 MB extents) with 8 datafiles each, as there are 4 RAID 10 mount points and been chunking them in 1 GB files.

I'm aware of the parameter session_max_open_files and will have to bump it up.

I did experience an episode of resource exhaustion when a mail server was down, and the mail proc was opening up handles repeatedly (by the thousands) which caused the instance to not be able to create any dedicated server processes - where I think it hit 15,000 handles.
that was quite awhile ago and I did not save the debugging info.

To sum it up, If I partition 42 tables into 42 partitions (months) that each have 42 indexes and 42 users simultaneously access the application, will it throw an ora-00942 error? :)

thanks in advance.


Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Thu Jul 29 2004 - 18:44:50 CDT

Original text of this message