-----Original Message-----
From: bret.higginbotham@nationsbank.com [mailto:bret.higginbotham@nationsbank.com]
Sent: Tuesday, July 28, 1998 10:53 AM
To: - (052)akaplan(a)interaccess.com
Subject:
Ari Kaplan,
Scenario:
I have a database containing a few records and I need to change storage
allocation parameters so I drop and recreate all objects in the schema. After
recreating the objects with new storage parameters and before any data is
inserted, I go into OEM and check the tablespaces and datafiles in data storage
manager. The "used" column in data storage manager shows 42 MB used for the
primary data TS and there's no data in the tables.
Questions:
Are the extents pre-allocated, causing 42MB of datafile usage to show ?
If so why does the usage go down to 37MB after the initial data is inserted
into the database ?
I ran analyze on all tables in the schema and queried the DBA_TABLES view to
get another picture of data usage. Looking at this information, I saw that
blocks were only allocated to tables actually containing data so I was even
more confused by the initial reading of 42MB used immediately after the storage
parameters had been changed.
Any ideas, your feedback is greatly appreciated,
Thanks,
Bret Higginbotham
NationsBank
(formerly of Grant Thornton and painful VBA participant).
Bret.Higginbotham@NationsBank.com
------ Reply ------
Hi Bret,
As for your question, Oracle pre-allocates extents, meaning that it grabs chunks
of database blocks from the free space for a tablespace, and then fills in the
allocated blocks with data. When all blocks are filled, it allocates another
extent.
Why the usage goes down is a mystery. It probably is due to reading the wrong
data dictionary tables. Look at:
SELECT *
FROM DBA_EXTENTS
WHERE SEGMENT_NAME = 'table_x';
This will tell you how much is allocated.
SELECT SUM(BYTES)/1024/1024 Megs
FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME = 'tablespace_x';
This will tell you how much is free in the tablespace.
Best of luck,
-Ari Kaplan
www.arikaplan.com
Back to Ari Kaplan's Home Page