DAY 06 – TABLESPACE

byShohanur Rahaman -


Ü  STORAGE MANAGEMENT

q  HOW IS THE DATA STORED IN THE DATABASE?

v  LOGICALLY STORE IN THE TABLESPACE AND PHYSICALLY WE HAVE DATA FILES

q  TABLESPACE

v  COLLECTION OF SEGMENTS

q  SEGMENT

v  COLLECTION OF EXTENTS

q  EXTENT

v  COLLECTION OF BLOCKS

q  BLOCK

v  DATA BLOCK IS THE SMALLEST UNIT

q  INITIALLY 8 BLOCKS ARE ALLOCATED BY DEFAULT TOTAL SIZE [8KBx8] 64KB

q  ONE TABLE WILL HAVE A NUMBER OF EXTENTS

q  ONE BLOCK HAS 5 PARTS

q  COMMON AND VARIABLE HEADER

v  BLOCK ADDRESS [EX: DATA OR INDEX]

q  TABLE DIRECTORY

v  IN THIS BLOCK WHICH TABLE DATA STORE

q  ROW DIRECTORY

v  WHICH ROW STORE IN THIS BLOCK

q  FREE SPACE

v  ALLOCATED FOR INSERTION OF NEW ROWS

q  ROW DATA

 

Ü  TABLESPACE

q  SMALL FILE TABLESPACE

v  IN A TABLESPACE 1024 DATA FILES YOU CAN ADD

v  ORACLE RECOMMENDS ONE DATA FILES SIZE MAX 32 GB

q  BIGFILE TABLESPACE

v  LIMIT SIZE 32TB [OS & ORACLE LEVEL]

v  ONLY ADD ONE DATA FILE

v  SHOULD ONLY BE USED WITH ASM OR WITH OTHER LOGICAL VOLUME MANAGERS 

q  SYSTEM TABLESPACE

v  CONTAINS DATA DICTIONARY TABLES [META DATA]

q  SYSAUX TABLESPACE

v  CONTAINS EMBEDDED ENTERPRISE MANAGER[EM] REPOSITORY, LOGMINER, STATSPACK, AWR

q  UNDO TABLESPACE

v  ROLL BACK TRANSACTIONS        

q  USER TABLESPACE

v  ACTUAL USER DATA

q  TEPORARY TABLESPACE

v  FOR SORT OPERATIONS PURPOSES


q  TABLESPACE TWO TYPES

q  LOCALLY MANAGED TABLESPACE

v  USED FOR DML OPERATIONAL [INSERT, UPDATE, DELETE WITH DATA GETTING MODIFIED]

q  DICTIONARY MANAGED TABLESPACE

v  STATIC TABLE [MASTER TABLE]

v  WE DO NOT MODIFY DATA

v  SYSTEM AND SYSAUX ARE IN INCLUDE THIS TABLESPACE

 

q  EXTENT MANAGEMENT HAS TWO TYPES

q  AUTOALLOCATE

v  EXTENT SIZES ARE MANAGED BY ORACLE AUTOMATIC

q  UNIFORM

v  EXTENT ALLOCATION IN THE TABLESPACE IS IN A FIXED UNIFORM SIZE


>>>>>>>>>>>>>>>>>>>>>>>>>>

[oracle@node1 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 10 14:00:56 2025
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SQL> alter session set container=maxpdb;
 
Session altered.
 
SQL> select file_name from dba_data_files;
 
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/MAXCDB/maxpdb/system01.dbf
/u01/app/oracle/oradata/MAXCDB/maxpdb/sysaux01.dbf
/u01/app/oracle/oradata/MAXCDB/maxpdb/undotbs01.dbf
/u01/app/oracle/oradata/MAXCDB/maxpdb/users01.dbf
/u01/app/oracle/oradata/MAXCDB/maxpdb/users02.dbf
/u01/app/oracle/oradata/MAXCDB/maxpdb/apex01.dbf
 
6 rows selected
 
 DROP TABLESPACE TS_HR1 INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE TS_HR1 DATAFILE '/u01/app/oracle/oradata/MAXCDB/maxpdb/ts_hr1.dbf' size 10M uniform size 200K;
 
DROP USER HR1 CASCADE;
CREATE USER HR1 IDENTIFIED BY abc DEFAULT TABLESPACE TS_HR1 TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;
GRANT CONNECT, RESOURCE TO HR1;
ALTER USER HR1 DEFAULT ROLE ALL;
GRANT CREATE TABLE TO HR1;
ALTER USER HR1 QUOTA UNLIMITED ON TS_HR1;
 
CREATE TABLE HR1.TAB1(
               EMPNO                NUMBER(4) NOT NULL,
               ENAME                VARCHAR2(10),
               JOB        VARCHAR2(10),
               MGR      NUMBER(4),
               HIDATE DATE,
               SAL        NUMBER(7,2),
               COMM NUMBER(2),
               DEPTNO              NUMBER(2))
tablespace TS_HR1; --Optional
 
set lin 150
col SEGMENT_NAME format A20
col TABLESPACE_NAME format A20
select segment_type || ' - ' || segment_name segment_name, tablespace_name, extent_id, file_id, block_id, blocks from dba_extents where segment_name = 'TAB1';
 
insert into HR1.TAB1 values (1000, 'SMITH', 'CLERK', 7902, TO_DATE('13-SEP-07', 'DD-MON-RR'), 80, NULL,20);
commit;
 
set lin 150
col SEGMENT_NAME format A20
col TABLESPACE_NAME format A20
select segment_type || ' - ' || segment_name segment_name, tablespace_name, extent_id, file_id, block_id, blocks from dba_extents where segment_name = 'TAB1';
 
begin
insert into HR1.TAB1 values (1000, 'SMITH', 'CLERK', 7902, TO_DATE('13-SEP-07', 'DD-MON-RR'), 80, NULL,20);
insert into HR1.TAB1 values (1000, 'SMITH', 'CLERK', 7902, TO_DATE('13-SEP-07', 'DD-MON-RR'), 80, NULL,20);
insert into HR1.TAB1 values (1000, 'SMITH', 'CLERK', 7902, TO_DATE('13-SEP-07', 'DD-MON-RR'), 80, NULL,20);
insert into HR1.TAB1 values (1000, 'SMITH', 'CLERK', 7902, TO_DATE('13-SEP-07', 'DD-MON-RR'), 80, NULL,20);
insert into HR1.TAB1 values (1000, 'SMITH', 'CLERK', 7902, TO_DATE('13-SEP-07', 'DD-MON-RR'), 80, NULL,20);
insert into HR1.TAB1 values (1000, 'SMITH', 'CLERK', 7902, TO_DATE('13-SEP-07', 'DD-MON-RR'), 80, NULL,20);
insert into HR1.TAB1 values (1000, 'SMITH', 'CLERK', 7902, TO_DATE('13-SEP-07', 'DD-MON-RR'), 80, NULL,20);
insert into HR1.TAB1 values (1000, 'SMITH', 'CLERK', 7902, TO_DATE('13-SEP-07', 'DD-MON-RR'), 80, NULL,20);
insert into HR1.TAB1 values (1000, 'SMITH', 'CLERK', 7902, TO_DATE('13-SEP-07', 'DD-MON-RR'), 80, NULL,20);
insert into HR1.TAB1 values (1000, 'SMITH', 'CLERK', 7902, TO_DATE('13-SEP-07', 'DD-MON-RR'), 80, NULL,20);
insert into HR1.TAB1 values (1000, 'SMITH', 'CLERK', 7902, TO_DATE('13-SEP-07', 'DD-MON-RR'), 80, NULL,20);
insert into HR1.TAB1 values (1000, 'SMITH', 'CLERK', 7902, TO_DATE('13-SEP-07', 'DD-MON-RR'), 80, NULL,20);
insert into HR1.TAB1 values (1000, 'SMITH', 'CLERK', 7902, TO_DATE('13-SEP-07', 'DD-MON-RR'), 80, NULL,20);
insert into HR1.TAB1 values (1000, 'SMITH', 'CLERK', 7902, TO_DATE('13-SEP-07', 'DD-MON-RR'), 80, NULL,20);
insert into HR1.TAB1 values (1000, 'SMITH', 'CLERK', 7902, TO_DATE('13-SEP-07', 'DD-MON-RR'), 80, NULL,20);
insert into HR1.TAB1 values (1000, 'SMITH', 'CLERK', 7902, TO_DATE('13-SEP-07', 'DD-MON-RR'), 80, NULL,20);
insert into HR1.TAB1 values (1000, 'SMITH', 'CLERK', 7902, TO_DATE('13-SEP-07', 'DD-MON-RR'), 80, NULL,20);
insert into HR1.TAB1 values (1000, 'SMITH', 'CLERK', 7902, TO_DATE('13-SEP-07', 'DD-MON-RR'), 80, NULL,20);
insert into HR1.TAB1 values (1000, 'SMITH', 'CLERK', 7902, TO_DATE('13-SEP-07', 'DD-MON-RR'), 80, NULL,20);
insert into HR1.TAB1 values (1000, 'SMITH', 'CLERK', 7902, TO_DATE('13-SEP-07', 'DD-MON-RR'), 80, NULL,20);
insert into HR1.TAB1 values (1000, 'SMITH', 'CLERK', 7902, TO_DATE('13-SEP-07', 'DD-MON-RR'), 80, NULL,20);
insert into HR1.TAB1 values (1000, 'SMITH', 'CLERK', 7902, TO_DATE('13-SEP-07', 'DD-MON-RR'), 80, NULL,20);
insert into HR1.TAB1 values (1000, 'SMITH', 'CLERK', 7902, TO_DATE('13-SEP-07', 'DD-MON-RR'), 80, NULL,20);
insert into HR1.TAB1 values (1000, 'SMITH', 'CLERK', 7902, TO_DATE('13-SEP-07', 'DD-MON-RR'), 80, NULL,20);
commit;
end;
/
 
Insert more data in database-
 
begin
               for i in 1..8 loop
               insert into HR1.TAB1 select * from HR1.TAB1;
               end loop;
               commit;
end;
/
 
set lin 150
set pages 0
col SEGMENT_NAME format A20
col TABLESPACE_NAME format A20
select segment_type || ' - ' || segment_name segment_name, tablespace_name, extent_id, file_id, block_id, blocks from dba_extents where segment_name = 'TAB1';
 
SELECT
    tablespace_name,
    file_name,
    bytes / 1024 / 1024 AS total_mb,
    (bytes - free_bytes) / 1024 / 1024 AS used_mb,
    free_bytes / 1024 / 1024 AS free_mb,
    ROUND((bytes - free_bytes) / bytes * 100, 2) AS used_percent
FROM (
    SELECT
        df.tablespace_name,
        df.file_name,
        df.bytes,
        (df.bytes - NVL(fs.free_bytes, 0)) AS used_bytes,
        NVL(fs.free_bytes, 0) AS free_bytes
    FROM dba_data_files df
    LEFT JOIN (
        SELECT tablespace_name, file_id, SUM(bytes) AS free_bytes
        FROM dba_free_space
        GROUP BY tablespace_name, file_id
    ) fs
    ON df.file_id = fs.file_id
)
ORDER BY used_percent DESC;
 
alter tablespace TS_HR1 add datafile '/u01/app/oracle/oradata/MAXCDB/maxpdb/ts_hr2.dbf' size 10m;
 
set lin 150
col FILE_NAME format A80
col BYTES format 9999999999
col TABLESPACE_NAME format A20
select tablespace_name, file_name, bytes from dba_data_files where tablespace_name= 'TS_HR1';

Tags

You may like these posts

Previous Post Next Post

نموذج الاتصال