Adding column definition sizes, a record shall not have over 750 bytes. According to a simple calculation the data in the table is around 200 MB, and a reserved space of 750 MB.
You are using a MyISAM table and the space required for the table exceeds what is permitted by the internal pointer size. MyISAM permits data and index files to grow up to 256TB by default, but this limit can be changed up to the maximum permissible size of 65,536TB (2567 − 1 bytes).
You might be interested in this query. It tells you how much space is allocated for each table taking into account the indexes and any LOBs on the table. Often you are interested to know "How much spaces the the Purchase Order table take, including any indexes" rather than just the table itself. You can always delve into the details. Note that this requires access to the DBA_* views.
COLUMN TABLE_NAME FORMAT A32
COLUMN OBJECT_NAME FORMAT A32
COLUMN OWNER FORMAT A10
SELECT
owner,
table_name,
TRUNC(sum(bytes)/1024/1024) Meg,
ROUND( ratio_to_report( sum(bytes) ) over () * 100) Percent
FROM
(SELECT segment_name table_name, owner, bytes
FROM dba_segments
WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
UNION ALL
SELECT i.table_name, i.owner, s.bytes
FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name
AND s.owner = i.owner
AND s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type IN ('LOBSEGMENT', 'LOB PARTITION')
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND s.owner = l.owner
AND s.segment_type = 'LOBINDEX')
WHERE owner in UPPER('&owner')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 10 /* Ignore really small tables */
ORDER BY SUM(bytes) desc
;
-- Tables + Size MB
select owner, table_name, round((num_rows*avg_row_len)/(1024*1024)) MB
from all_tables
where owner not like 'SYS%' -- Exclude system tables.
and num_rows > 0 -- Ignore empty Tables.
order by MB desc -- Biggest first.
;
--Tables + Rows
select owner, table_name, num_rows
from all_tables
where owner not like 'SYS%' -- Exclude system tables.
and num_rows > 0 -- Ignore empty Tables.
order by num_rows desc -- Biggest first.
;
Note: These are estimates, made more accurate with gather statistics:
exec dbms_utility.analyze_schema(user,'COMPUTE');
First off, I would generally caution that gathering table statistics in order to do space analysis is a potentially dangerous thing to do. Gathering statistics may change query plans, particularly if the DBA has configured a statistics gathering job that uses non-default parameters that your call is not using, and will cause Oracle to re-parse queries that utilize the table in question which can be a performance hit. If the DBA has intentionally left some tables without statistics (common if your OPTIMIZER_MODE
is CHOOSE), gathering statistics can cause Oracle to stop using the rule-based optimizer and start using the cost-based optimizer for a set of queries which can be a major performance headache if it is done unexpectedly in production. If your statistics are accurate, you can query USER_TABLES
(or ALL_TABLES
or DBA_TABLES
) directly without calling GATHER_TABLE_STATS
. If your statistics are not accurate, there is probably a reason for that and you don't want to disturb the status quo.
Second, the closest equivalent to the SQL Server sp_spaceused
procedure is likely Oracle's DBMS_SPACE
package. Tom Kyte has a nice show_space
procedure that provides a simple interface to this package and prints out information similar to what sp_spaceused
prints out.
First, gather optimiser stats on the table (if you haven't already):
begin
dbms_stats.gather_table_stats('MYSCHEMA','MYTABLE');
end;
/
WARNING: As Justin says in his answer, gathering optimiser stats affects query optimisation and should not be done without due care and consideration!
Then find the number of blocks occupied by the table from the generated stats:
select blocks, empty_blocks, num_freelist_blocks
from all_tables
where owner = 'MYSCHEMA'
and table_name = 'MYTABLE';
The total number of blocks allocated to the table is blocks + empty_blocks + num_freelist_blocks.
blocks is the number of blocks that actually contain data.
Multiply the number of blocks by the block size in use (usually 8KB) to get the space consumed - e.g. 17 blocks x 8KB = 136KB.
To do this for all tables in a schema at once:
begin
dbms_stats.gather_schema_stats ('MYSCHEMA');
end;
/
select table_name, blocks, empty_blocks, num_freelist_blocks
from user_tables;
Note: Changes made to the above after reading this AskTom thread
I modified the WW's query to provide more detailed information:
SELECT * FROM (
SELECT
owner, object_name, object_type, table_name, ROUND(bytes)/1024/1024 AS meg,
tablespace_name, extents, initial_extent,
ROUND(Sum(bytes/1024/1024) OVER (PARTITION BY table_name)) AS total_table_meg
FROM (
-- Tables
SELECT owner, segment_name AS object_name, 'TABLE' AS object_type,
segment_name AS table_name, bytes,
tablespace_name, extents, initial_extent
FROM dba_segments
WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
UNION ALL
-- Indexes
SELECT i.owner, i.index_name AS object_name, 'INDEX' AS object_type,
i.table_name, s.bytes,
s.tablespace_name, s.extents, s.initial_extent
FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name
AND s.owner = i.owner
AND s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
-- LOB Segments
UNION ALL
SELECT l.owner, l.column_name AS object_name, 'LOB_COLUMN' AS object_type,
l.table_name, s.bytes,
s.tablespace_name, s.extents, s.initial_extent
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type = 'LOBSEGMENT'
-- LOB Indexes
UNION ALL
SELECT l.owner, l.column_name AS object_name, 'LOB_INDEX' AS object_type,
l.table_name, s.bytes,
s.tablespace_name, s.extents, s.initial_extent
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND s.owner = l.owner
AND s.segment_type = 'LOBINDEX'
)
WHERE owner = UPPER('&owner')
)
WHERE total_table_meg > 10
ORDER BY total_table_meg DESC, meg DESC
/
IIRC the tables you need are DBA_TABLES, DBA_EXTENTS or DBA_SEGMENTS and DBA_DATA_FILES. There are also USER_ and ALL_ versions of these for tables you can see if you don't have administration permissions on the machine.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With