--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'))
--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;
--2. CREATE TABLESPACE
CREATE TABLESPACE data201102 DATAFILE '/u02/oradata/DBAViet/datafile/DATA201102_01.DBF' SIZE 6G;
CREATE TABLESPACE data201102 DATAFILE '/u02/oradata/DBAViet/datafile/DATA201102_01.DBF' SIZE 100M;
CREATE TABLESPACE data201103 DATAFILE
'/u02/oradata/DBAViet/datafile/DATA201103_01.DBF' SIZE 50M AUTOEXTEND ON NEXT 100M MAXSIZE 2048M,
'/u02/oradata/DBAViet/datafile/DATA201103_02.DBF' SIZE 50M AUTOEXTEND ON NEXT 100M MAXSIZE 2048M,
'/u02/oradata/DBAViet/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 datafile
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;
-- 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/pne/DATA201302_0004.dbf';
-- Rename datafile
ALTER TABLESPACE users
RENAME DATAFILE '/u02/oracle/rbdb1/user1.dbf',
'/u02/oracle/rbdb1/user2.dbf'
TO '/u02/oracle/rbdb1/users01.dbf',
'/u02/oracle/rbdb1/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;
