Path: news.easynews.com!easynews!news-out.cwix.com!newsfeed.cwix.com!opentransit.net!kinglear.mobilixnet.dk!news.orangenet.dk.POSTED!not-for-mail From: kennethkoenraadt@hotmail.com (Kenneth Koenraadt) Newsgroups: comp.databases.oracle.server Subject: Re: Table Extents Question Message-ID: <3c5c251e.3788747@news.mobilixnet.dk> References: X-Newsreader: Forte Free Agent 1.11/32.235 Lines: 44 Date: Sat, 02 Feb 2002 17:53:17 GMT NNTP-Posting-Host: 212.97.241.100 X-Complaints-To: news@orange.dk X-Trace: news.orangenet.dk 1012672301 212.97.241.100 (Sat, 02 Feb 2002 18:51:41 CET) NNTP-Posting-Date: Sat, 02 Feb 2002 18:51:41 CET Organization: Orange DK Xref: easynews comp.databases.oracle.server:133687 X-Received-Date: Sat, 02 Feb 2002 10:50:31 MST (news.easynews.com) On Sat, 02 Feb 2002 14:20:57 GMT, "Pamela Samuels" wrote: >Please tell me if my logic has a fallacy. We have a PeopleSoft >application that requires the existence of thousands of tables that >are used temporarily during the execution of a batch process (App >Engine). For our installation, many of the tables are not used, but >we are required to create them anyway. > >As the program runs, it loads data into them, processes it, then >truncates the table. > >To conserve space, especially since may are never used, but also to >minimize dynamic extent allocation, I have created all these thousands >of tables with an initial extent of 16k, and a next of 1m. The >thinking is that all tables will be created tightly packed with small >extents, and as extents are allocated and then given back with the >truncate, having a uniform 1m next will keep the tablespace >unfragmented. > >Is there a better way of managing this? We don't have the option of >using Oracle temporary tables, we have to do it this way. > > You should consider sorting the tables by their max. size and then spreading them over multiple locally managed tablespaces with difference extent sizes. Doing this properly requires you to know the max. size of each table when the application is running. If you really want to put them all in one tablespace, you should consider truncating them with the "keep storage" option. That way they keep their max size constant and you will avoid dynamic extent allocation. Regards, Kenneth Koenraadt Systems Consultant Oracle Certified Professional - DBA plovmand@hotmail.com