Oracle Tablespace

# CREATE a NEW TABLESPACE named tbs1 WITH SIZE 1MB
# DATAFILE bisa ditambah PATH pada FILE dbf: /opt/oracle/product/12.2.0.1/dbhome_1/dbs
# bisa diganti /opt/oracle/oradata/ORCLCDB/ORCLPDB1/
CREATE TABLESPACE tbs1
   DATAFILE 'tbs1_data.dbf'
   SIZE 1m;

# find its information BY querying data FROM the dba_data_files VIEW
SELECT
   tablespace_name,
   file_name,
   bytes / 1024/ 1024  MB
FROM
   dba_data_files;

# resize the TABLESPACE
ALTER DATABASE
   DATAFILE 'tbs1.dbf'
   RESIZE 10m;

# ADD datafile IN TABLESPACE
ALTER TABLESPACE
   users
ADD DATAFILE
   '/u01/app/oracle/oradata/EE/tbs1.dbf'
SIZE 100m

# The SECOND way TO avoid this issue, WHEN creating a NEW TABLESPACE
CREATE TABLESPACE tbs1
   DATAFILE 'tbs1.dbf'
   SIZE 1m
   AUTOEXTEND ON NEXT 20m;

# hapus TABLESPACE kosongan
DROP TABLESPACE tbs1;

# hapus tabelspace yang ada isi
DROP TABLESPACE tbs2
    INCLUDING CONTENTS;
# DROP sekaligus datafilenya
DROP TABLESPACE tbs2 including contents AND datafiles;
# jika udah hapus TABLESPACE lupa hapus datafile, bisa dengan cara CREATE TABLE SPACE datafile existing, kemudian DROP sekaligus datafilenya
# perintah CREATE dengan existing datafile, sama dengan di atas tapi tanpa menyebut SIZE 100m

# lihat free SIZE TABLESPACE
SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024/1024 "FREE SPACE(GB)"
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;

# alternatif lihat free SIZE TABLESPACE
--Size of All Table Space

--1. Used Space
SELECT TABLESPACE_NAME,TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99') AS "USED SPACE(IN GB)" FROM USER_SEGMENTS GROUP BY TABLESPACE_NAME;
--2. Free Space
SELECT TABLESPACE_NAME,TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99') AS "FREE SPACE(IN GB)" FROM   USER_FREE_SPACE GROUP BY TABLESPACE_NAME;

--3. Both Free & Used
SELECT USED.TABLESPACE_NAME, USED.USED_BYTES AS "USED SPACE(IN GB)",  FREE.FREE_BYTES AS "FREE SPACE(IN GB)"
FROM
(SELECT TABLESPACE_NAME,TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99') AS USED_BYTES FROM USER_SEGMENTS GROUP BY TABLESPACE_NAME) USED
INNER JOIN
(SELECT TABLESPACE_NAME,TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99') AS FREE_BYTES FROM  USER_FREE_SPACE GROUP BY TABLESPACE_NAME) FREE
ON (USED.TABLESPACE_NAME = FREE.TABLESPACE_NAME);

# lihat datafile dalam tabelspace
  SELECT SUBSTR (df.tablespace_name, 1, 20)
             "Tablespace Name",
         SUBSTR (df.file_name, 1, 80)
             "File Name",
         ROUND (df.bytes / 1024 / 1024, 0)
             "Size (M)",
         DECODE (e.used_bytes, NULL, 0, ROUND (e.used_bytes / 1024 / 1024, 0))
             "Used (M)",
         DECODE (f.free_bytes, NULL, 0, ROUND (f.free_bytes / 1024 / 1024, 0))
             "Free (M)",
         DECODE (e.used_bytes,
                 NULL, 0,
                 ROUND ((e.used_bytes / df.bytes) * 100, 0))
             "% Used"
    FROM dba_data_files df,
         (  SELECT file_id, SUM (bytes) used_bytes
              FROM dba_extents
          GROUP BY file_id) E,
         (  SELECT MAX (bytes) free_bytes, file_id
              FROM dba_free_space
          GROUP BY file_id) f
   WHERE e.file_id(+) = df.file_id AND df.file_id = f.file_id(+)
ORDER BY df.tablespace_name, df.file_name;