90 - [Oracle 19C] Types of Segments

 

• Managing Storage Space

Types of Segments

 

Managing Storage Space
Types of Segments

 A segment is a set of extents allocated for a certain logical structure.

 The different types of segments include:
  Table and cluster
    Note: For a partitioned table, each partition has a data segment
  Index
    Note: For a partitioned index, each partition has an index segment.
  Undo
    Oracle Database maintains information to reverse changes made to the database.
    This information consists of records of the actions of transactions, 
      collectively known as undo.
    Undo is stored in undo segments in an undo tablespace

  Temporary
    A temporary segment is created by the Oracle Database server when a SQL statement needs 
    a temporary database area to complete execution. When the statement finishes execution, 
    the extents in the temporary segment are returned to the system for future use

 

 

 

-- 149. Availability and Optimization of Free Space in a Data Block

show user
/*
USER is "PDB10_ADMIN"
*/


show con_name
/*
CON_NAME 
------------------------------
PDB10
*/

 

 

 

create table dept (deptno NUMBER(2), dept_name varchar2(100 ) );
/*
Table DEPT created.
*/



select owner, table_name, tablespace_name from dba_tables where table_name='DEPT';
/*
OWNER           TABLE_NAME      TABLESPACE_NAME
--------------- --------------- ---------------
PDB10_ADMIN     DEPT            USERS 
*/



# 테이블에 데이터가 없어서 USER_SEGMENTS 테이블에서 조회가 되지 않는다
SELECT segment_name, bytes, blocks, extents, tablespace_name FROM USER_SEGMENTS
WHERE SEGMENT_NAME='DEPT';
/*
no rows selected
*/

 

 

 

INSERT into dept values ( 1, 'IT Dept' );
INSERT into dept values ( 2, 'FINANCE' );
commit;
/*
1 row inserted.
1 row inserted.
Commit complete.
*/



# 테이블에 데이터를 삽입하면 USER_SEGMENTS 테이블에서 조회가 된다.
SELECT segment_name, tablespace_name, bytes, blocks, extents FROM USER_SEGMENTS
WHERE SEGMENT_NAME='DEPT';
/*
SEGMENT_NAME    TABLESPACE_NAME      BYTES     BLOCKS    EXTENTS
--------------- --------------- ---------- ---------- ----------
DEPT            USERS                65536          8          1
*/


create unique index dept_inx on DEPT(deptno);
/*
Index DEPT_INX created.
*/


SELECT segment_name, segment_type, tablespace_name, bytes, blocks, extents FROM USER_SEGMENTS;
/*
SEGMENT_NAME    SEGMENT_TYPE       TABLESPACE_NAME      BYTES     BLOCKS    EXTENTS
--------------- ------------------ --------------- ---------- ---------- ----------
DEPT            TABLE              USERS                65536          8          1
DEPT_INX        INDEX              USERS                65536          8          1
*/

 

 

 

update dept set dept_name='XXXX'
/*
2 rows updated.
*/



SELECT owner, segment_name, bytes, blocks, extents, header_file, header_block, tablespace_name FROM DBA_SEGMENTS
where owner='PDB10_ADMIN'
/*
OWNER           SEGMENT_NAME         BYTES     BLOCKS    EXTENTS HEADER_FILE HEADER_BLOCK TABLESPACE_NAME
--------------- --------------- ---------- ---------- ---------- ----------- ------------ ---------------
PDB10_ADMIN     DEPT                 65536          8          1          49          138 USERS          
PDB10_ADMIN     DEPT_INX             65536          8          1          49          146 USERS          
*/



# The information related with UNDO

select a.sid, a.serial#, a.username, b.used_urec, b.used_ublk
from   v$session a,
       v$transaction b
where  a.saddr = b.ses_addr
order by b.used_ublk desc;
--USED_UREC = Number of undo records used
--USED_UBLK = Number of undo blocks used 
/*
       SID    SERIAL# USERNAME         USED_UREC  USED_UBLK
---------- ---------- --------------- ---------- ----------
        14      36690 PDB10_ADMIN              2          1
*/



ROLLBACK;
/*
Rollback complete.
*/



# 롤백을 해서 UNDO 데이터가 존재하지 않는다
select a.sid, a.serial#, a.username, b.used_urec, b.used_ublk
from   v$session a,
       v$transaction b
where  a.saddr = b.ses_addr
order by b.used_ublk desc;
/*
no rows selected
*/