Re: Table Extents Question

On Sat, 02 Feb 2002 14:20:57 GMT, "Pamela Samuels" <xyz_at_hotmail.com> 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@<no-spam>hotmail.com   Received on Sat Feb 02 2002 - 11:53:17 CST