Quản lý tablespace Oracle Database

Các câu lệnh thường sử dụng để quản trị tablespace:


--1. CHECK

--Hiển thị dung lượng trống của tablespace
SELECT  a.tablespace_name,100 - ROUND ( (NVL (b.bytes_free, 0) / a.bytes_alloc) * 100) "%Usage",
    ROUND (a.bytes_alloc / 1024 / 1024) "Size MB",
    ROUND (a.bytes_alloc / 1024 / 1024)- ROUND (NVL (b.bytes_free, 0) / 1024 / 1024) "Used MB",
    ROUND (NVL (b.bytes_free, 0) / 1024 / 1024) "Free MB",
    --ROUND ( (NVL (b.bytes_free, 0) / a.bytes_alloc) * 100) "%Free",
    ROUND (maxbytes / 1048576)  "Max MB",
    round(maxbytes/1048576-(ROUND (a.bytes_alloc / 1024 / 1024)- ROUND (NVL (b.bytes_free, 0) / 1024 / 1024)),0) "Free_MB_Max",
    ROUND (ROUND ( (a.bytes_alloc - NVL (b.bytes_free, 0)) / 1024 / 1024)/  ROUND (maxbytes / 1048576) * 100) "%Used of Max"
    FROM (SELECT f.tablespace_name, SUM (f.bytes) bytes_alloc,  SUM (DECODE (f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes
            FROM dba_data_files f
            GROUP BY tablespace_name) a,
        (SELECT f.tablespace_name, SUM (f.bytes) bytes_free  FROM dba_free_space f  GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name(+)  and  (a.tablespace_name in ('TEMP1','DATA201511','DATA2016','INDX','INDX2016'))
 order by "%Used of Max" desc;

 --Dung luong của từng data files
SELECT  FILE_NAME, BLOCKS, round(bytes/1024/1024/1024,2), TABLESPACE_NAME
FROM DBA_DATA_FILES
WHERE file_name like '%data2008_01.dbf%' or file_name like '%data2008_04.dbf%';
order by tablespace_name;

--Dung lượng DB
SELECT ROUND(SUM(BYTES)/1024/1024/1024,2) FROM DBA_DATA_FILES;

--Dung lượng schema
select owner,round(sum(bytes/1024/1024/1024),3) from dba_segments
group by owner order by owner;

-- Dung luong tablespace READ ONLY: 7,15GB
select sum(bytes)/1024/1024/1024 "GB" from dba_data_files;

--2,92TB
select sum(bytes)/1024/1024/1024 "GB" from dba_data_files where tablespace_name in
(select tablespace_name from dba_tablespaces where 
status='READ ONLY');

-- Check duong dan cua datafile trong tablesapce
select * from dba_data_files where tablespace_name='DUMP_DATA';
select * from dba_data_files where file_name like '%DUMP_DATA%';

--Check trạng thái của datafile, tablespace
--Offline
select file_name,tablespace_name,online_status from dba_data_files where tablespace_name='DATA201008'

-- Read Only (co the online hoac offline), Online
select tablespace_name, status from dba_tablespaces where
--tablespace_name like '%201208%' and
status='READ ONLY'
order by tablespace_name;

-- Tim kiem volume cho tablespace
SELECT
A.TABLESPACE_NAME,
TOTAL_SPACE,
TOTAL_SPACE-FREE_SPACE USED,
FREE_SPACE
FROM
(SELECT SUM(BYTES/1024/1024) TOTAL_SPACE, TABLESPACE_NAME FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A,
(SELECT SUM(BYTES/1024/1024) FREE_SPACE, TABLESPACE_NAME FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) B
WHERE
A.TABLESPACE_NAME=B.TABLESPACE_NAME(+)
AND A.TABLESPACE_NAME like 'DATA200910'
ORDER BY FREE_SPACE ASC;

-- Thống kê dung lượng tăng theo tháng

select to_char(creation_time,'yyyy/mm'), round(sum(bytes)/1024/1024/1024) "GB" from v$datafile 

group by to_char(creation_time,'yyyy/mm')

order by 1;

(tháng 2021/07 tạo 16.3TB, tháng 08/2021 tạo 10.2TB, tháng 09/2021 dến hôm  nay là 09/09/2021 tạo 3.6TB, cần tạo khoảng > 7TB nữa để duy trì hết tháng)

--2. CREATE TABLESPACE

CREATE TABLESPACE data201102 DATAFILE '/u02/oradata/ORCL/datafile/DATA201102_01.DBF' SIZE 6G;
CREATE TABLESPACE data201102 DATAFILE '/u02/oradata/ORCL/datafile/DATA201102_01.DBF' SIZE 100M;
CREATE TABLESPACE data201103 DATAFILE 
'/u02/oradata/ORCL/datafile/DATA201103_01.DBF' SIZE 50M AUTOEXTEND ON NEXT 100M MAXSIZE 2048M,
'/u02/oradata/ORCL/datafile/DATA201103_02.DBF' SIZE 50M AUTOEXTEND ON NEXT 100M MAXSIZE 2048M,
'/u02/oradata/ORCL/datafile/DATA201103_03.DBF' SIZE 50M AUTOEXTEND ON NEXT 100M MAXSIZE 2048M;

CREATE TABLESPACE /*ASM*/ DUMP_DATA_RECO   datafile '+RECO_GOLD' size 1G autoextend on next 100M ;
   
--3.ALTER TABLESPACE

--ADD DATAIFILE

--+ Check dung luong diskgroup trong

select * from gv$asm_diskgroup;

--+ Check duong dan chua datafile

 

select * from dba_data_files where tablespace_name='DUMP_DATA';

--+ Add datafile với file system
    ALTER TABLESPACE DUMP_DATA ADD DATAFILE '/u02/oracle/oradata/datafile/DUMP_DATA_04.dbf' SIZE 2G AUTOEXTEND ON NEXT 200M MAXSIZE 10G;

    ALTER TABLESPACE DUMP_DATA ADD DATAFILE '/u02/oracle/oradata/datafile/DUMP_DATA_04.dbf' SIZE 2G AUTOEXTEND ON NEXT 200M; --unlimited 

    ALTER TABLESPACE lmtemp ADD TEMPFILE '/u02/oracle/data/lmtemp02.dbf' SIZE 18M REUSE;
   
--+ Add datafile trong ASM:
    alter tablespace DATA_ add datafile '+DATA' size 1G autoextend on next 200M maxsize 10G;
    ALTER TABLESPACE DATA201206 ADD datafile size 512M autoextend on next 200M maxsize 8g
   
-- Drop datafile
    ALTER TABLESPACE DATA201302 DROP DATAFILE '/u03/oradata/ORCL/DATA201302_0004.dbf';
   
--  Rename datafile
    ALTER TABLESPACE users
    RENAME DATAFILE '/u02/oracle/ORCL/user1.dbf',
                    '/u02/oracle/ORCL/user2.dbf'
                 TO '/u02/oracle/ORCL/users01.dbf',
                    '/u02/oracle/ORCL/users02.dbf';
   
-- ONLINE, OFFLINE
    ALTER TABLESPACE users ONLINE;
ALTER TABLESPACE users OFFLINE [force | normal];
   
-- Read write, read only:
    ALTER TABLESPACE DATA201101 READ WRITE;
    ALTER TABLESPACE DATA201101 READ ONLY;

-- Renaming Tablespaces
    ALTER TABLESPACE my_space RENAME TO your_space; 

--4.DROP TABLESPACE

DROP TABLESPACE DATA201207 INCLUDING CONTENTS AND DATAFILES;

   
@ Trần Văn Bình - Founder of "Oracle DBA Việt Nam" #OracleTutorial #OracleDBA #OracleDatabaseAdministration #học oracle database #oca #ocp #oce #ocm