Introduction - datafile structure
Every tablespace is one or group of multiple datafiles. Datafile has 3 levels of configuration- File sizing - file size on hard disk, increasing size and max size.
- Extent management - size of extents etc. (automatic / uniform size)
- Segment management - automatic (common) / manual (PCT_USED, FREELISTS, GROUPS ... settings is necessary)
Every extent contains blocks (usual 4KiB or 8KiB) which contain rows.
 |
| Datafile structure |
Tutorial
For this tutorial, connect to db as system (sys as sysdba).Step 1 - create tablespace with one datafile
Parameters- Initial size 30MiB, next 20MiB if needed, maximum of size is 100MB
- Extent has uniform size 4MiB - it is good for tutorial. Usually use automatic extent management.
- ASSM - automatic segment space management
define ts = TBS_TEST_DATA---- Create tablespace--CREATE TABLESPACE "&ts."DATAFILE '/home/oracle/test_file01.dbf' SIZE 30M AUTOEXTEND ON NEXT 20M MAXSIZE 100MEXTENT MANAGEMENT LOCAL UNIFORM SIZE 4MSEGMENT SPACE MANAGEMENT AUTO; -- MANUAL (PCTUSED, FREELISTS, and FREELISTS GROUPS), do not use itStep 2 - create view TABLESPACE_MAP_VIEW
create view TABLESPACE_MAP_VIEW asSELECT 'free space' owner, ' ' OBJECT, dfs.file_id, dfs.block_id, dfs.blocks, dfs.blocks*8192/1024/1024 size_In_MB, ddf.FILE_NAME FROM dba_free_space dfs, DBA_DATA_FILES ddf WHERE dfs.tablespace_name = UPPER ('&ts.') and ddf.file_id = dfs.file_idUNIONSELECT SUBSTR (dext.owner, 1, 20), SUBSTR (dext.segment_name, 1, 32), dext.file_id, dext.block_id, dext.blocks, dext.blocks*8192/1024/1024 size_In_MB, ddf.FILE_NAME FROM dba_extents dext, DBA_DATA_FILES ddf WHERE dext.tablespace_name = UPPER ('&ts.') and dext.file_id = ddf.file_idORDER BY 3, 4;Step 3 - create table TEST_TABLE and check the datafile
First, check tablespace map: select * from TABLESPACE_MAP_VIEW;Result: 28MB of freespace (3584 blocks):
Now, create table with only one row but allocate initial 16MiB space:
--
-- create tmp table
--
create table TEST_TABLE tablespace TBS_TEST_DATA
storage (initial 16M NEXT 10M
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS UNLIMITED)
as
select rownum id, ao.* from ALL_OBJECTS ao where rownum<2 ; -- 0MB dat
Check the map: select * from TABLESPACE_MAP_VIEW;
You see, there will be allocated 16MB for test table (4 extents with uniform size 4MB). But table is almost empty! Contains only one row!
Step 4 - Insert some data
First, insert cca 13MB: ---- insert 13mb--insert into TEST_TABLE select rownum id, ao.* from ALL_OBJECTS ao; -- 13MB datcommit; Check it - nothing changed because of enough space was allocated before:
And now, insert next 7MB:
---- insert 7mb--insert into TEST_TABLE select rownum id, ao.* from ALL_OBJECTS ao where rownum<40000 ; -- 7MB datcommit;Check it - rest of freespace was used and next 4MB was allocated (why only 4MB and not 10MB? - db probably rounds sizes)
Step 5 - add second table (GREAT_TABLE)
Add some another table:---- great--create table GREAT_TABLEtablespace TBS_TEST_DATAstorage (initial 4M NEXT 4K PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS UNLIMITED)asselect rownum id, ao.* from ALL_OBJECTS ao; -- cca 14MB data
Check the tablespace map:
- Datafile was extended to 48MiB (add cca 20MiB)
- new table needs 16MiB space
Step 6 - Create "tablespace level" fragmentation for TEST_TABLE
Insert next 7 MB into table, delete second table:---- insert 7mb--insert into TEST_TABLEselect rownum id, ao.* from ALL_OBJECTS ao where rownum<40000 ; -- 7MB datcommit;-- make a holedrop table GREAT_TABLE;See the map:
Again insert:
insert into TEST_TABLEselect rownum id, ao.* from ALL_OBJECTS ao where rownum<40000 ; -- 7MB datcommit;
The hole will not be filled because of "segment level fragmentation"!!!. Instead new extend will be allocated:
After delete some space...
Shrinking table makes some data free:
Table is almost defragmented (one block is 50% to 75%)
After delete some space...
-- delete "random", no space will be freedelete from TEST_TABLE where id < 60000;commit;
...no space will be free:
Step 7 - Defragmentation of TEST_TABLE ("tablespace level" and also "segment level" fragmentation)
Check the table block usage:set serveroutput on;-- fragmentation in blocksdeclare v_unformatted_blocks number; v_unformatted_bytes number; v_fs1_blocks number; v_fs1_bytes number; v_fs2_blocks number; v_fs2_bytes number; v_fs3_blocks number; v_fs3_bytes number; v_fs4_blocks number; v_fs4_bytes number; v_full_blocks number; v_full_bytes number; begin dbms_space.space_usage('SYS', 'TEST_TABLE', 'table', v_unformatted_blocks, v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes, v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes); dbms_output.put_line('Unformatted Blocks = ' || v_unformatted_blocks); dbms_output.put_line('Unformatted Bytes = ' || v_unformatted_bytes); dbms_output.put_line('FS1 Bytes (at least 0 to 25% free space) = ' || v_fs1_bytes); dbms_output.put_line('FS1 Blocks(at least 0 to 25% free space) = ' || v_fs1_blocks); dbms_output.put_line('FS2 Bytes (at least 25 to 50% free space)= ' || v_fs2_bytes); dbms_output.put_line('FS2 Blocks(at least 25 to 50% free space)= ' || v_fs2_blocks); dbms_output.put_line('FS3 Bytes (at least 50 to 75% free space) = ' || v_fs3_bytes); dbms_output.put_line('FS3 Blocks(at least 50 to 75% free space) = ' || v_fs3_blocks); dbms_output.put_line('FS4 Bytes (at least 75 to 100% free space) = ' || v_fs4_bytes); dbms_output.put_line('FS4 Blocks(at least 75 to 100% free space)= ' || v_fs4_blocks); dbms_output.put_line('Full Blocks in segment = ' || v_full_blocks); dbms_output.put_line('Full Bytes in segment = ' || v_full_bytes); end;
Many blocks are fragmented:
- Low HWM and HWM is at the end => by adding new data will not be used free space and will be allocated a new extent (see Segment Space and the High Water Mark on page https://docs.oracle.com/cd/E11882_01/server.112/e40540/logical.htm)
- Wasting space - many blocks are filled 0-75%, only few are full.
Shrinking table makes some data free:
---- "release free space" - shrink--alter table TEST_TABLE enable row movement;alter table TEST_TABLE shrink space;Alter table TEST_TABLE shrink space cascade;alter table TEST_TABLE disable row movement; -- deallocate unused extentsALTER TABLE TEST_TABLE DEALLOCATE UNUSED;Table is almost defragmented (one block is 50% to 75%)
See the map:
Defragmentation by move storage. Init 16MiB for new extents:
-- "free space" - move to new extents--alter table TEST_TABLE movestorage (initial 16M NEXT 10M PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS UNLIMITED);
Result of block usages:
Move storage contains init size (in this case 16MB), therefore 4 extents are allocated. And also first 8MB are free because there was the table stored before move:
"Move storage" benefits:
- choose some parameters similar to table creation (init size etc...)
- better "block level" defragmentation
- should be fast
- less capacity (does not create new extends)
- in some cases better "extent level" defragmentation
Step 8 - cleanup
---- DROP--drop table TEST_TABLE;drop table GREAT_TABLE;DROP TABLESPACE "&ts." INCLUDING CONTENTS AND DATAFILES;
