EXTENTS FOR OBJECTS (tablespace, table, index, materialized view)
===================
INITIAL    - [size] - "size" of "the first" extent of the object
NEXT       - [size] - "size" of "the next"  extent to be allocated to the object
MINEXTENTS - [#]    - "number" of extents to allocate "when" the object "is created"
MAXEXTENTS - [#]    - "number" of extents that Oracle will allocate for the object (in the future).
                       When object reaches MAXEXTENTS new allocation request will be baned by oracle
                       for this object
PCTINCREASE - [%] - percent by which the third and subsequent extents grow over the preceding extent
                      
[size]...storage unit (byte, kB and so on)
[#]......number expression without unit
[%]......percentage expression
------------------------------------------------------


-- tablespacy
==============

-- kolik je mista?

col tablespace_name for a20
col free_MB for 9999999.9
col used_MB for 9999999.9
col "FREE_%" for 99.9
select t.tablespace_name, f.free_MB  free_MB, (f.free_MB/(t.size_MB/100))  "FREE_%",  t.size_MB, (t.size_MB - f.free_MB) used_MB
from (select tablespace_name, sum(bytes)/1024/1024 free_MB from dba_free_space group by tablespace_name) f,
     (select tablespace_name, sum(bytes)/1024/1024 size_MB from dba_data_files group by tablespace_name) t
where f.tablespace_name=t.tablespace_name
/
set pages 999
set linesize 200
col tablespace_name for a30
col extent_management for a20
col  segment_space_management for a20


-- jaky je management?
select tablespace_name, INITIAL_EXTENT, NEXT_EXTENT , MAX_EXTENTS, EXTENT_MANAGEMENT, allocation_type, segment_space_management 
from dba_tablespaces order by 1;

-- znam jmeno segmentu? prectu aktualni maxextent a navrhnu vetsi
select segment_name , extents, max_extents  from dba_segments 
where  SEGMENT_NAME=''

-- na urovni objektu lze nadefinovat hranici alokovanych extentu (pocet)
ALTER INDEX PP_ADMIN.IDX_LT_A_VT_AUFTRAGS_POS_IK STORAGE ( MAXEXTENTS 8192 );
ALTER INDEX PP_ADMIN.IDX_LT_A_VT_AUFTRAGS_POS_IK STORAGE ( MAXEXTENTS 8192 );



Free space deficit
==================
free space deficit - neexistuje dostatecne mnozstvi volnych extentu, 
oracle je alokuje spojite zasebou, bud doslo misto v TBS nebo diry volnych
extentu nepokryvaji pozadavek na alokaci

-- odhad ktere tablespace to asi budou?
select tablespace_name, segment_name , extents, max_extents  from dba_segments 
where  (extents + 100) > max_extents


-- najdi nejvetsi extenty v TBS pokud neni velikost "NEXT extentu" UNIFORM
==========================================================================
col owner for a10
col segment_name for a30
col segment_type for a5
col extents for 99999999999
col max_extents for 99999999999
col tablespace_name for a10


select * from (
select s.OWNER, s.segment_name, s.segment_type, ROUND(s.extents/(s.max_extents/100),0) EXTENT_PCT_USED, s.extents, s.max_extents,(e.extent_size/1024/1024) next_extent_MB 
from dba_segments s, (select segment_name, max(bytes) extent_size
		    from dba_extents
		    where  tablespace_name = 'TBS_NAME'
		   group by segment_name) e
where tablespace_name = 'TBS_NAME' and s.segment_name = e.segment_name 
)
where  EXTENT_PCT_USED > 60
order by EXTENT_PCT_USED, next_extent_MB, extents



-- zjisti distribuci volnych extentu --
=======================================
(1.)
select count(*) num#, bytes/1024/1024 extent_size_MB  from dba_free_space 
where tablespace_name='TBS_NAME' group by bytes/1024/1024 ;


-- kolik zbyva extentu pro nejvetsi dosud dosazeny NEXT EXTENT?--
=================================================================

(2.)
select sum(floor(bytes/64/1024/1024)) from dba_free_space where tablespace_name='TBS_NAME';
                       ^ 64kb nalezeny (1.) 
-----------------------|
 

                             3