Question: I need to know the data dictionary
query to find the size of each partition in a partitioned table.
I tried dba_tab_partitions but it does not provide the
partition sizing.
Will running this script give me the partition size?
dbms_stats.gather_table_stats('schema_name,'table_name,'partition_name')
Answer: There are several ways to get a
partition “size”:
- You can look at the size of the physical data files
within the partition.
- You can count-up all of the extents in the partition.
- You can get the size of data within all segments of the
partition.
The dbms_stats does not give partition sizing
information, it only recomputes optimizer statistics. This could be
dangerous because it will change SQL execution plans.
However, dbms_stats will populate columns
dba_tables, num_rows and avg_row_len which
can be used to estimate the amount of row data within a partition.
You can get partition sizing information from
dba_segments:
select
sum(bytes/1024/1024)
from
dba_segments
where
segment_name='MYTABLE';
select
owner,
segment_name,
partition_name,
segment_type,
bytes / 1024/1024 "MB"
from
dba_segments
where
see code depot for full script
segment_name in
('SEG1','SEG2','SEG3');
select
owner,
segment_name,
partition_name,segment_type,bytes/1024/1024 "MB"
from
dba_segments
where
see code depot for full script
segment_type = 'TABLE PARTITION';
There are MANY other ways of seeing a partition size.
You can also use the
dbms_space.space_usage procedure to get the number of blocks in
a partition:
set serveroutput on
declare
l_fs1_bytes number;
l_fs2_bytes number;
l_fs3_bytes number;
l_fs4_bytes number;
l_fs1_blocks number;
l_fs2_blocks
number;
l_fs3_blocks number;
l_fs4_blocks number;
l_full_bytes number;
l_full_blocks number;
l_unformatted_bytes number;
l_unformatted_blocks number;
v_segname varchar2(500);
begin
see code depot for full script
dbms_space.space_usage(
segment_owner => 'SCOTT,
segment_name => 'EMP,
segment_type => 'TABLE',
fs1_bytes =>
l_fs1_bytes,
fs1_blocks => l_fs1_blocks,
fs2_bytes =>
l_fs2_bytes,
fs2_blocks => l_fs2_blocks,
fs3_bytes =>
l_fs3_bytes,
fs3_blocks => l_fs3_blocks,
fs4_bytes =>
l_fs4_bytes,
fs4_blocks => l_fs4_blocks,
full_bytes =>
l_full_bytes,
full_blocks => l_full_blocks,
unformatted_blocks => l_unformatted_blocks,
unformatted_bytes =>
l_unformatted_bytes
);
dbms_output.enable;
dbms_output.put_line('=============================================');
dbms_output.put_line('total blocks = '||to_char(l_fs1_blocks +
l_fs2_blocks + l_fs3_blocks + l_fs4_blocks + l_full_blocks)|| ' ||
total bytes = '|| to_char(l_fs1_bytes + l_fs2_bytes + l_fs3_bytes +
l_fs4_bytes + l_full_bytes));
end;
/
You can also use
dbms_space.object_growth_trend and run a trend for a growing
partition size.
There are many other ways to compute the size of an Oracle
partition. If you don't want to write your own scripts, see the
Oracle script
collection.
|
|
 |
|
Guarantee your Success!
Oracle is the
world's most complex, robust and flexible database, considered
impossible to master without a mentor.
That's why all BC
Oracle trainers are working professionals, experts in Oracle who
share their tips and secrets. |
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright ? 1996 - 2012
All rights reserved.
Oracle ?
is the registered trademark of Oracle Corporation.
|
|
