You cant resize the initial extent

(Comments below may not apply to Oracle 8i / 8.1.5 (6) )

You can't resize the initial extent once the object has been created (nor any other extents starting below the High Water Mark).

To solve your specific problem, you'll need to ensure that the table is created with a simall INITIAL extent.

Now this can be specified explicitly in a CREATE TABLE DDL statement. If it is, fine - just change to e.g. INITIAL 5K

but if the INITIAL extent size is not stated explicitly, Oracle looks at the tablespace in which the object is about to be created and uses the default INITIAL ... for the tablespace.

So you'll need to find out whether the tablespace is explicitly defined in the CREATE TABLE or not. If not, the object will be created in the default tablespace for the username of the session issuing the DDL. (This may all be done using a script so look for a CONNECT fred/bloggs_at_your_database.world - fred is the user / schema).

Thus you may need to find out into whose schema the object will be being created.

Thus you may need (possibly) to go right through these links to eventually get to the user / schema.

To recap; unless Oracle sees an explicit storage clause parameter, it uses the default one for the tablespace which will own the object. The CREATE TABLE etc. may not explicitly state this, in which case Oracle uses the user's default tablespace (so determine the user).

Your best solution is to manually edit the DDL statements for only the objects which you want to keep small. Another idea would be to set their NEXT and MAXEXTENTS parameters to be small. This means that you will be enforcing a small size of the object, even if anything performs some INSERTS into it. (The size of the index depends on the corresponding column(s) of course. - You don't insert into the index!)

Happy administrating,

David P.

Syltrem wrote:
>
> Hello!
>
> I'm trying to see if any of my tables/index use up more space than they
> should.
>
> For example, I have tables with 0 records in them but the initial_extent
> size = 2M. That is 2MB of space used up for nothing. I could reduce that to
> 5K or something if I know for sure I will not use the table (the table must
> still exist, this is a package we bought).
>
> I tried the following and am not sure the results are what I want. For
> example, some tables use only 50% of the space where I set the PCTFREE=10. I
> would expect such a table to use up 90% of the extents allowed to it. If I
> am wrong in this stamenet or in the SQL below, please correct me
>
 

> Thanks!
>
> select segment_name, sum(a.bytes/1024/1024), sum(b.bytes/1024/1024),
> count(*)
> from dba_free_space a, dba_extents b
> where a.BLOCK_ID=b.BLOCK_ID and owner='JOSEETEST'
> and SEGMENT_TYPE <> 'ROLLBACK'
> group by segment_name;
>
> PCF1 84.4023438 122.714844 61 --> 68% of
> room is used, not 90%
> PCF2 8.046875 10.0585938 5
> EPF 2.00195313 2.00195313 1 --> Emply. 2MB
> for nothing.
> EPIC 1.00585938 2.00195313 1 --> Here I could
> release another 1MB
> PACK 1.03515625 4.00390625 2 --> Why did it
> use 2 extents where it could fit into one?
>
> ALL extents are 2MB in size, PCTFREE=10 for all tables
>
> (this message also posted to comp.database.oracle, no answer from there
> though)
Received on Wed Mar 08 2000 - 00:00:00 CST