Home » RDBMS Server » Server Administration » Space used by a table
|
|
Re: Space used by a table [message #150812 is a reply to message #150807] |
Sat, 10 December 2005 04:07 |
tarundua
Messages: 1080 Registered: June 2005 Location: India
|
Senior Member |
|
|
-->>How can i check the total used space ?
SQL> select segment_name , bytes from
2 dba_segments
3 where segment_name='EMP' and owner='SCOTT';
SEGMENT_NAME
---------------------------------------
BYTES
----------
EMP
65536
-->>the free space in bytes for a table??
What do you want to know here? A table can acquire extents until the tablespace is free.
[ BTW -- And always search as mahesh said ]
regards,
tarun
[Updated on: Sat, 10 December 2005 04:34] Report message to a moderator
|
|
|
Re: Space used by a table [message #150815 is a reply to message #150807] |
Sat, 10 December 2005 04:35 |
reena_ch30
Messages: 100 Registered: December 2005
|
Senior Member |
|
|
Hi Tarun and Mahesh,
First of all, thanks Mahesh for the piece of advice. But, my question is different.I wish to know the total actual space consumed by rows in bytes on a table. The bytes as in dba_segments would give me the total no. of bytes (total of extents) allocated to a table till now. But allocation does not mean used. An extent could be avialable for future insertions/updations.
Case :the allocated extent for a table xyz is 1 with size 66536, i wish to know ,how much space is being consumed by rows till now and how much is free for future insertions/updates???
Regards,
reena
|
|
|
Re: Space used by a table [message #150823 is a reply to message #150815] |
Sat, 10 December 2005 07:32 |
|
Mahesh Rajendran
Messages: 10707 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
I took the regular DEPT table.
Did a little modifications so as to 'see' the results easily.
scott@9i > desc dept
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
scott@9i > alter table dept modify (dname char(2000), loc char(2000));
Table altered.
--
-- Let us check
--
scott@9i > @ddl
Enter value for table_name: DEPT
CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" CHAR(2000),
"LOC" CHAR(2000)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
;
--
-- Collect statistics
--
scott@9i > analyze table dept compute statistics;
Table analyzed.
--
- Current allocations
--
scott@9i > get get_ext
1 select extent_id, bytes, blocks
2 from user_extents
3 where segment_name = 'DEPT'
4* and segment_type = 'TABLE'
scott@9i > /
EXTENT_ID BYTES BLOCKS
---------- ---------- ----------
0 65536 8
--
-- I am inserting some data, in a loop and collect stats again.
--
scott@9i > get dept_loopinsert
1 begin
2 for mag in 1..5 loop
3 insert into dept (select * from dept);
4 end loop;
5 commit;
6* end;
scott@9i > /
PL/SQL procedure successfully completed.
scott@9i > analyze table dept compute statistics;
Table analyzed.
--
-- You can see now, many extents are allocated, many blocks used.
-- Total bytes allocated and total blocks allocated are
-- report at the end.
--
scott@9i > get get_ext
1 select extent_id, bytes, blocks
2 from user_extents
3 where segment_name = 'DEPT'
4* and segment_type = 'TABLE'
scott@9i > compute sum of BYTES BLOCKS on report
scott@9i > break on report
scott@9i > /
EXTENT_ID BYTES BLOCKS
---------- ---------- ----------
0 65536 8
1 65536 8
2 65536 8
3 65536 8
4 65536 8
5 65536 8
6 65536 8
7 65536 8
8 65536 8
9 65536 8
10 65536 8
11 65536 8
12 65536 8
13 65536 8
14 65536 8
15 65536 8
16 1048576 128
---------- ----------
sum 2097152 256
17 rows selected.
--
-- So the allocated size is 2097152 bytes
-- Let us cross check that.
-- NOTE: heading is size_in_megs. ignore and read it as size_in_bytes.
--
scott@9i > get size
1 column segment_name format a15
2* Select segment_name,bytes size_in_megs from user_segments where segment_name='DEPT';
scott@9i > @size
SEGMENT_NAME SIZE_IN_MEGS
--------------- ------------
DEPT 2097152
--
-- Now using DBMS_SPACE builtin-package as mentioned here
-- http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_space2.htm#1000756
-- i have little pl/sql that does a few things for us.
-- I used UNUSED_SPACE to find the unused space .
-- I also used the same to cross-check above.
scott@9i > get get_space_u_dbms
1 DECLARE
2 total_blocks number;
3 total_bytes number;
4 unused_blocks number;
5 unused_bytes number;
6 last_used_extent_file_id number;
7 last_used_extent_block_id number;
8 last_used_block number;
9 begin
10 DBMS_SPACE.UNUSED_SPACE('SCOTT', 'DEPT', 'TABLE', total_blocks => total_blocks ,
11 total_bytes => total_bytes,
12 unused_blocks =>unused_blocks,
13 unused_bytes => unused_bytes,
14 last_used_extent_file_id => last_used_extent_file_id,
15 last_used_extent_block_id =>last_used_extent_block_id ,
16 last_used_block => last_used_block );
17 DBMS_OUTPUT.PUT_LINE ( 'total_blocks '|| total_blocks);
18 DBMS_OUTPUT.PUT_LINE ( 'total_bytest '|| total_bytes);
19 DBMS_OUTPUT.PUT_LINE ( 'unused_block '|| unused_blocks);
20 DBMS_OUTPUT.PUT_LINE ( 'unused_bytes '|| unused_bytes);
21* end;
scott@9i > /
total_blocks 256
total_bytest 2097152
unused_block 64
unused_bytes 524288
PL/SQL procedure successfully completed.
--
--Let us dellocate the unused space
--
scott@9i > alter table dept deallocate unused;
Table altered.
scott@9i > @get_space_u_dbms
total_blocks 192
total_bytest 1572864
unused_block 0
unused_bytes 0
PL/SQL procedure successfully completed.
-- crosscheck?
--
PL/SQL procedure successfully completed.
scott@9i > @size
SEGMENT_NAME SIZE_IN_MEGS
--------------- ------------
DEPT 1572864
scott@9i > compute sum of BYTES BLOCKS on report
scott@9i > break on report
scott@9i > @get_ext
EXTENT_ID BYTES BLOCKS
---------- ---------- ----------
0 65536 8
1 65536 8
2 65536 8
3 65536 8
4 65536 8
5 65536 8
6 65536 8
7 65536 8
8 65536 8
9 65536 8
10 65536 8
11 65536 8
12 65536 8
13 65536 8
14 65536 8
15 65536 8
16 524288 64
---------- ----------
sum 1572864 192
17 rows selected.
|
|
|
Re: Space used by a table [message #150850 is a reply to message #150807] |
Sun, 11 December 2005 02:04 |
reena_ch30
Messages: 100 Registered: December 2005
|
Senior Member |
|
|
Thanx Mahesh,
That was very informative.just 1 more question on same..can i get the unused space in a particular extent without using built in packages??
Thanx and Regards,
reena
|
|
|
Re: Space used by a table [message #150879 is a reply to message #150850] |
Sun, 11 December 2005 12:52 |
|
Mahesh Rajendran
Messages: 10707 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
I don't know. May be one such is available.
If i can hack into v$ tables, i can do that (The information is extract from block header.
So somewhere it is stored. )
But why re-invent the wheel?
All you need is, create function to display the same information using a query~.
And
Forgot to mention something in previous post.
continuing from where we left before
scott@9i > @size
SEGMENT_NAME SIZE_IN_BYTES
--------------- -------------
DEPT 2097152
scott@9i > @get_ext
EXTENT_ID BYTES BLOCKS
---------- ---------- ----------
0 65536 8
1 65536 8
2 65536 8
3 65536 8
4 65536 8
5 65536 8
6 65536 8
7 65536 8
8 65536 8
9 65536 8
10 65536 8
11 65536 8
12 65536 8
13 65536 8
14 65536 8
15 65536 8
16 1048576 128
---------- ----------
sum 2097152 256
17 rows selected.
scott@9i > get avg_space
1* select avg_space, blocks, empty_blocks from user_tables where table_name ='DEPT';
--
-- Here EMPTY_BLOCKS are the ones that are never used and above HWM.
-- BLOCKS are ones that are used(1 block for header information). It may or may not be completely used.
-- AVG_SPACE is the average space per block.
-- DEALLOCATE UNSED will release the space above HWM.
-- If we do table re-org, we can see the actual space (avg_space) will vary.
-- after any such operation, collect statistics.
--
scott@9i > @avg_space
AVG_SPACE BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
4937 180 76
-- Total blocks is sum of BLOCKS+EMPTY_BLOCKS from above and from get_ext.sql
-- Total Bytes is sum BYTES from get_ext.sql
--
-- First we do a re-org. Export /import or a simple move to the same tablespace as it was will do the job.
-- You can AVG_SPACE per block is lesser after the re-org. Also you save a bunch in BLOCKS.
-- And Your EMPTY blocks that were never used is Incremented.
--
scott@9i > alter table dept move tablespace users;
Table altered.
scott@9i > get ana_dept
1 analyze table dept delete statistics;
2* analyze table dept compute statistics;
scott@9i > @ana_dept
Table analyzed.
Table analyzed.
scott@9i > @avg_space
AVG_SPACE BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
4032 140 116
scott@9i > @get_space_u_dbms
total_blocks 256
total_bytest 2097152
unused_block 116
unused_bytes 950272
PL/SQL procedure successfully completed.
--
-- When you issue deallocate unused, anything above HWM is released.
--
scott@9i > alter table dept deallocate unused
2 ;
Table altered.
scott@9i > @ana_dept
Table analyzed.
Table analyzed.
scott@9i > @avg_space
AVG_SPACE BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
4032 140 4
scott@9i > @get_space_u_dbms
total_blocks 144
total_bytest 1179648
unused_block 4
unused_bytes 32768
PL/SQL procedure successfully completed.
scott@9i > @get_ext
EXTENT_ID BYTES BLOCKS
---------- ---------- ----------
0 65536 8
1 65536 8
2 65536 8
3 65536 8
4 65536 8
5 65536 8
6 65536 8
7 65536 8
8 65536 8
9 65536 8
10 65536 8
11 65536 8
12 65536 8
13 65536 8
14 65536 8
15 65536 8
16 131072 16
---------- ----------
sum 1179648 144
17 rows selected.
[Updated on: Sun, 11 December 2005 12:53] Report message to a moderator
|
|
|
|
Re: Space used by a table [message #206410 is a reply to message #150807] |
Wed, 29 November 2006 23:25 |
Brayan
Messages: 315 Registered: June 2002
|
Senior Member |
|
|
Hi Mahesh,
According to the previous posting. The output from "user_tables"....
-- EMPTY_BLOCKS are the ones that are never used and above HWM.
-- BLOCKS are ones that are used(1 block for header information). It may or may not be completely used.
-- AVG_SPACE is the average space per block.
-- DEALLOCATE UNSED will release the space above HWM.
-- If we do table re-org, we can see the actual space (avg_space) will vary.
-- after any such operation, collect statistics.
Please help me to understand the following..
I gather stats using following..This is a very big table.
dbms_stats.GATHER_TABLE_STATS(ownname=>'TEST',
tabname=>'SAVINGS',
degree=>16,
estimate_percent => dbms_stats.auto_sample_size
);
select blocks from user_segments where segment_name = 'SAVINGS' ;
Blocks -> 5244870
select blocks, empty_blocks, blocks+empty_blocks from user_tables where table_name = 'SAVINGS';
Blocks -> 4537586
Empty_blocks -> 706211
Blocks+Empty_Blocks -> 5243797
Why there is a difference between user_segments.blocks and users_tables.blocks+user_tables.empty_blocks
Brian.
|
|
|
Re: Space used by a table [message #206614 is a reply to message #206410] |
Thu, 30 November 2006 17:52 |
|
Mahesh Rajendran
Messages: 10707 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
user_segments.blocks is what that is allocated (for table & data).
user_tables.blocks is what that is used (for data).
It is quite easy to demonstrate with a demo table.
Now, Instead of dbms_stats, use analyze table compute statistics and compare.
--
-- Base data
-- they match
scott@9i > analyze table emp compute statistics;
Table analyzed.
scott@9i > select segment_name,blocks from user_segments where segment_name='EMP';
SEGMENT_ BLOCKS
-------- ----------
EMP 768
scott@9i > select table_name,num_rows,blocks,empty_blocks, blocks+empty_blocks from user_tables where table_name='EMP';
TABL NUM_ROWS BLOCKS EMPTY_BLOCKS BLOCKS+EMPTY_BLOCKS
---- ---------- ---------- ------------ -------------------
EMP 114688 684 84 768
--
-- Now let us deallocate unused and do the same.
-- Still, they match
scott@9i > alter table emp deallocate unused;
Table altered.
scott@9i > analyze table emp compute statistics;
Table analyzed.
scott@9i > select segment_name,blocks from user_segments where segment_name='EMP';
SEGMENT_ BLOCKS
-------- ----------
EMP 704
scott@9i > select table_name,num_rows,blocks,empty_blocks, blocks+empty_blocks from user_tables where table_name='EMP';
TABL NUM_ROWS BLOCKS EMPTY_BLOCKS BLOCKS+EMPTY_BLOCKS
---- ---------- ---------- ------------ -------------------
EMP 114688 684 20 704
--
-- Let us reset the HWM by moving to same tablespace.
-- They still match
scott@9i > alter table emp move tablespace users;
Table altered.
scott@9i > analyze table emp compute statistics;
Table analyzed.
scott@9i > select segment_name,blocks from user_segments where segment_name='EMP';
SEGMENT_ BLOCKS
-------- ----------
EMP 768
scott@9i > select table_name,num_rows,blocks,empty_blocks, blocks+empty_blocks from user_tables where table_name='EMP';
TABL NUM_ROWS BLOCKS EMPTY_BLOCKS BLOCKS+EMPTY_BLOCKS
---- ---------- ---------- ------------ -------------------
EMP 114688 696 72 768
|
|
|
Re: Space used by a table [message #206647 is a reply to message #150807] |
Fri, 01 December 2006 00:07 |
Brayan
Messages: 315 Registered: June 2002
|
Senior Member |
|
|
Hi Mahesh,
Thanks very much.
Could you please guide me in the following..
1. In the following which is better
Analyze table SAVINGS compute statistics
OR
dbms_stats.GATHER_TABLE_STATS(ownname=>'TEST',
tabname=>'SAVINGS',
degree=>16,
estimate_percent => dbms_stats.auto_sample_size );
2. In SAVINGS table I've around 10000000+ records, to gather entire table stats it takes around 4Hrs.
While gathering statistics, "estimate_percent => dbms_stats.auto_sample_size" is sufficient or entire table I should do.
Regards,
Ronald.
|
|
|
Re: Space used by a table [message #206732 is a reply to message #206647] |
Fri, 01 December 2006 05:09 |
|
Mahesh Rajendran
Messages: 10707 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
1.)
Metalink note 236935.1.
Use DBMS_STATS for CBO. DBMS_STATS does more work for CBO.
Analyze is done in much lower level.
2.)
May or may not be.
In most cases, auto sample would work.
It depends on distribution, load etc.
10,000,000 records is not generallly considered as a 'high'.
You need to collect stats only if the data is changed a lot
and you collect only the stale stats. You have'nt mentioned your vesion yet.
>> degree=>16,
Did you try with a smaller setting?
And there is something i failed to give importance for.
Earlier i said
>>BLOCKS are ones that are used(1 block for header information). It may or may not be completely used.
So
Ideally BLOCKS+EMPTY_BLOCKS should be one block lesser than user_segments.blocks. The one additional block is reserved for header information. The earlier case is not showing that information because the way in which i created the tablespace.
In other words,
user_segments.blocks=user_tables.block + user_tables.empty_blocks + 1;
Please see this session;
scott@9i > create table mytable (c1 number);
Table created.
scott@9i > insert into mytable values (23333333333333333333333333333333333333333);
1 row created.
scott@9i > begin
2 for mag in 1..12 loop
3 insert into mytable (select * from mytable);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
scott@9i > analyze table mytable compute statistics;
Table analyzed.
scott@9i > column segment_name format a10
scott@9i > select segment_name,blocks from user_segments where segment_name='MYTABLE';
SEGMENT_NA BLOCKS
---------- ----------
MYTABLE 18
scott@9i > select table_name,num_rows,blocks,empty_blocks, blocks+empty_blocks from user_tables where table_name='MYTABLE';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS BLOCKS+EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------ -------------------
MYTABLE 4096 17 0 17
scott@9i > alter table mytable move tablespace users;
Table altered.
scott@9i > analyze table mytable compute statistics;
Table analyzed.
scott@9i > select segment_name,blocks from user_segments where segment_name='MYTABLE';
SEGMENT_NA BLOCKS
---------- ----------
MYTABLE 18
scott@9i > select table_name,num_rows,blocks,empty_blocks, blocks+empty_blocks from user_tables where table_name='MYTABLE';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS BLOCKS+EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------ -------------------
MYTABLE 4096 16 1 17
|
|
|
Re: Space used by a table [message #206748 is a reply to message #150807] |
Fri, 01 December 2006 06:07 |
Brayan
Messages: 315 Registered: June 2002
|
Senior Member |
|
|
Hi Mahesh,
Thanks very much. Infact your note was very helpful.
One doubt, After Analyzing on what basis I should re-organize the table(export/import).
Regards,
Ronald.
|
|
|
|
Re: Space used by a table [message #207112 is a reply to message #150807] |
Mon, 04 December 2006 03:19 |
Brayan
Messages: 315 Registered: June 2002
|
Senior Member |
|
|
Hi,
Database 9.2. Extent Management Local with uniform extent Size.
My question was, Suppose there was lot of DML operations performed(mainly delete)on a table, and If I want to re-organize the table what is the base for re-organizing. I mean, I need to judge myself, whether re-org is required or not.
Brian.
|
|
|
|
Re: Space used by a table [message #207291 is a reply to message #150807] |
Mon, 04 December 2006 23:33 |
Brayan
Messages: 315 Registered: June 2002
|
Senior Member |
|
|
Table SAVINGS does not have partiotions or sub-partitions.
1. dbms_stats.GATHER_TABLE_STATS(ownname=>'TEST',
tabname=>'SAVINGS',
granularity => 'DEFAULT'
);
Ref: Metalink note 236935.1, the granularity Default = Table + Partition .
SQL> select blocks from user_segments where segment_name = 'SAVINGS' and segment_type='TABLE';
BLOCKS
---------
408775
SQL> select blocks, empty_blocks, blocks + empty_blocks from user_tables where table_name = 'SAVINGS' ;
BLOCKS EMPTY_BLOCKS BLOCKS+EMPTY_BLOCKS
--------- ------------ -------------------
408694 0 408694
2. alter table SAVINGS compute statistics;
Ref: ANALYZE command collects statistics only at the lowest level and derives higher level statistics by aggregation.
Here Lowest level is TABLE.
SQL> select blocks from user_segments where segment_name = 'SAVINGS' and segment_type='TABLE';
BLOCKS
---------
408775
SQL> select blocks, empty_blocks, blocks + empty_blocks from user_tables where table_name = 'SAVINGS' ;
BLOCKS EMPTY_BLOCKS BLOCKS+EMPTY_BLOCKS
--------- ------------ -------------------
408694 0 408694
If, user_segments.blocks=user_tables.block + user_tables.empty_blocks + 1;
I do not understand why there is a difference even after Analyzing.
Brian
[Updated on: Tue, 05 December 2006 07:30] by Moderator Report message to a moderator
|
|
|
Re: Space used by a table [message #207300 is a reply to message #150807] |
Tue, 05 December 2006 00:04 |
Brayan
Messages: 315 Registered: June 2002
|
Senior Member |
|
|
Hi,
A correction in the above post, the above scenario works fine for Test tables and most of the tables.
i.e.
user_segments.blocks=user_tables.block + user_tables.empty_blocks + 1
Brian.
|
|
|
|
Goto Forum:
Current Time: Fri Sep 20 05:31:57 CDT 2024
|