RE: Expanding a table
Date: Thu, 21 Dec 2017 12:36:39 +0000
Message-ID: <AM5PR0701MB1810C02CFC2652E95E96893BE60D0_at_AM5PR0701MB1810.eurprd07.prod.outlook.com>
Thanks, that is exactly what I want.
From: Harel Safra [mailto:harel.safra_at_gmail.com]
Sent: 21 December 2017 12:14
To: Paul Houghton <Paul.Houghton_at_uis.cam.ac.uk>
Cc: Oracle-L Freelists <oracle-l_at_freelists.org>
Subject: Re: Expanding a table
You can allocate an empty extent with alter table allocate extent: https://docs.oracle.com/cloud/latest/db112/SQLRF/statements_3001.htm#i2082029 allocate_extent_clause
Use the allocate_extent_clause to explicitly allocate a new extent for the table, the partition or subpartition, the overflow data segment, the LOB data segment, or the LOB index.
Harel
On Thu, Dec 21, 2017 at 2:05 PM, Paul Houghton <Paul.Houghton_at_uis.cam.ac.uk<mailto:Paul.Houghton_at_uis.cam.ac.uk>> wrote: We are finding ourselves in a situation where a number of sessions conflict on HW Contention (50 minutes per hour) on a number of tables.
This is a data conversion running in parallel. The problem stems from a decision to use APIs designed for interactive programs, so we have a slow row by row conversion which we are attempting to speed up by running it in parallel. I can't change this decision - I tried!.
I suspect if the table were created at the correct size to start with this would no longer be an option.
Unfortunately the tables are created by an application (PeopleSoft) which doesn't allow the initial extent to be specified per table. You can't change the initial extent once the table has been created, even if the segment hasn't been created (We have deferred segment creation).
I am thinking I need to try to do something clever with dbms_metadata.get_ddl to get the definitions, alter them, then drop and recreate the tables.
Can you think of another (easier) way to increase the size of a tables? There are hundreds of them, so I don't really want to insert loads of rows then delete them.
Thanks
PaulH
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Dec 21 2017 - 13:36:39 CET