Used Space

[ac]

Hello,

I queried the Oracle 8i (8.1.6) database and found out that we've used 90% of the space available. The CEO believes that the space was claimed because of the allocated space during creation but the database does not contain enough data to fill up this space.

Can anyone out there throw more light on this in case there is a way to distinguish the weed from the corn.

Please help!!!

Ac

[denevge]

You can make an estimate of how many blocks are used for data in a table.

select count(distinct(dbms_rowid.rowid_block_number(row_id))) from MyTable;
gives you the number of blocks in MyTable that are used for storing data.

Hope this helps
Gert

[ac]

Gert,

Thanks for your reply.
I ran the query on a table "CUSTOMER" and got the following error.

SELECT count(distinct(dbms_rowid.rowid_block_number(customer_id))) FROM customer;

ORA-06553: PLS-306: Wrong number or types of arguments in call to 'ROWID_BLOCK_NUMBER'.

What I'm I doing wrong?

[denevge]

Sorry, my mistake

I meant rowid and not row_id

so the statement must be

SELECT count(distinct(dbms_rowid.rowid_block_number(ROWID))) FROM customer;

Hope this helps
Gert

[ac]

Gert,

Thanks! It works. I queried one of the tables and got 4 blocks. The block size is 8192.
Does this mean that there are 4 x 8192 bytes and if so what's taking up the rest of the space and how do Isafely reclaim the space.
At the moment the above-mentioned table is taking up 440,401,920 bytes. What's hapenning.

Ac

[denevge]

Yes that's what it means.

You also have to take in account that migrated/chained rows can occupy some extract blocks.

Also if you have LONG/LOB/BLOB objects in your table, those can take a lot of extra blocks you don't see using this query.

But if no chained/migrated rows, or (B)LOB's, it's just a fact that the table was created with an initial extent that was too big.

You can do a "move table" ( since you in 8.1.6 ) and specify new storage parameters.

Hope this helps
Gert

[tycho]

Hi ac,

Can you tell me the result of these queries?

select tablespace_name, sum(bytes) free_bytes
from dba_free_space
group by tablespace_name
/

select d.tablespace_name, sum(d.bytes) total_size
from dba_data_files d
group by d.tablespace_name
/

When you create a datafile on disk the space for the datafile is allocated at once on discWhen new no objects are placed in this datafile so in a way it is empty.

Hope this helps.

tycho

[ac]

Hello Tycho,

Thanks 4 the suggestion.

I ran the queries and got the following results:

Total Bytes: 2097152000
Free Bytes: 203022336

What's your opinion and how do I get the realistically used space.

Ac