.::: Create tablespace & add new tablespace in schema Oracle 19C :::.

 

Berikut adalah langkah-langkah untuk membuat tablespace bernama teguhthspace dan membuat tabel pada schema teguhth menggunakan tablespace tersebut:

1. Buat Tablespace teguhthspace

CREATE TABLESPACE teguhthspace
DATAFILE '/u01/tgh/teguhthspace_1.dbf'
SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;

Penjelasan:

SIZE 100M: Ukuran awal file data adalah 100 MB.
AUTOEXTEND ON NEXT 10M: File akan otomatis bertambah 10 MB setiap kali diperlukan.
MAXSIZE UNLIMITED: Tidak ada batas ukuran maksimum untuk file data.
EXTENT MANAGEMENT LOCAL: Tablespace dikelola secara lokal, yang lebih efisien.
  

2. Buat Schema teguhth

Jika schema teguhth belum ada, Anda perlu membuat user yang berfungsi sebagai schema:

CREATE USER teguhth IDENTIFIED BY teguhth
DEFAULT TABLESPACE teguhthspace
QUOTA UNLIMITED ON teguhthspace;

GRANT CONNECT, RESOURCE,oem_monitor to teguhth;
GRANT UNLIMITED TABLESPACE TO teguhth;
 

3. Buat Tabel pada Schema teguhth
Login sebagai user teguhth atau tambahkan schema_name jika Anda login sebagai user lain:

CREATE TABLE teguhth.my_table (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    created_at DATE DEFAULT SYSDATE
) TABLESPACE teguhthspace;


CREATE TABLE teguhth.my_table2 (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    created_at DATE DEFAULT SYSDATE
) TABLESPACE teguhthspace;

Penjelasan:

TABLESPACE teguhthspace: Tabel akan disimpan dalam tablespace teguhthspace.
 

4. Verifikasi
Cek Tablespace:

SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 AS SIZE_MB, AUTOEXTENSIBLE
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = 'TEGUHTHSPACE';

Cek Tabel:

SELECT TABLE_NAME, TABLESPACE_NAME
FROM ALL_TABLES
WHERE OWNER = 'TEGUHTH';
 

Jika sudah berhasil, Anda dapat mulai menggunakan tabel di schema teguhth. Apakah ada hal lain yang ingin Anda tambahkan atau modifikasi?

5. add table space
## add datafile


ALTER TABLESPACE teguhthspace
ADD DATAFILE '/u01/tgh/teguhthspace_2.dbf' SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;

## but recomendation
ALTER TABLESPACE teguhthspace
ADD DATAFILE '/u01/tgh/teguhthspace_2.dbf' SIZE 32G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;

6. jika drop

ALTER TABLESPACE teguhthspace
DROP DATAFILE '/u01/tgh/teguhthspace_2.dbf';

 

7. aditional check

 

 SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 AS SIZE_MB, AUTOEXTENSIBLE
FROM DBA_DATA_FILES;
 

---size table space

SELECT
    df.tablespace_name AS "Tablespace",
    ts.contents AS "Contents",
    ts.status AS "Status",
    ROUND(df.bytes / 1024 / 1024) AS "Size (MB)",
    ROUND((df.bytes - NVL(fs.bytes, 0)) / 1024 / 1024) AS "Used (MB)",
    ROUND(NVL(fs.bytes, 0) / 1024 / 1024) AS "Free (MB)",
    ROUND(((df.bytes - NVL(fs.bytes, 0)) / df.bytes) * 100, 2) AS "% Used",
    ROUND(df.maxbytes / 1024 / 1024) AS "Max Size (MB)",
    ROUND(((df.bytes - NVL(fs.bytes, 0)) / df.maxbytes) * 100, 2) AS "% Used of Max",
    ts.bigfile AS "BigFile",
    ts.extent_management AS "Extent Management",
    ts.allocation_type AS "Allocation Type",
    ts.plugged_in AS "Plugged In",
    ts.segment_space_management AS "Segment Space Management"
FROM
    dba_tablespaces ts
JOIN
    (SELECT tablespace_name,
            SUM(bytes) AS bytes,
            SUM(maxbytes) AS maxbytes
     FROM dba_data_files
     GROUP BY tablespace_name) df
ON ts.tablespace_name = df.tablespace_name
LEFT JOIN
    (SELECT tablespace_name,
            SUM(bytes) AS bytes
     FROM dba_free_space
     GROUP BY tablespace_name) fs
ON df.tablespace_name = fs.tablespace_name
ORDER BY df.tablespace_name;
 

SELECT t.name AS tablespace_name, f.block_size
FROM v$tablespace t
JOIN v$datafile f ON t.ts# = f.ts#
GROUP BY t.name, f.block_size;

--usage table space 

SELECT
    df.file_name AS "Datafile Name",
    df.file_id AS "File ID",
    ROUND(((df.bytes - NVL(fs.free_bytes, 0)) / df.bytes) * 100, 2) AS "Usage (%)",
    ROUND(df.bytes / 1024 / 1024) AS "Size (MB)",
    ROUND(NVL(fs.free_bytes, 0) / 1024 / 1024) AS "Free (MB)",
    ROUND((df.bytes - NVL(fs.free_bytes, 0)) / 1024 / 1024) AS "Used (MB)",
    df.blocks AS "Blocks",
    df.autoextensible AS "Auto Extend",
    df.increment_by AS "Increment By (Blocks)",
    ROUND(df.maxbytes / 1024 / 1024) AS "Max Size (MB)",
    df.status AS "Status"
FROM
    dba_data_files df
LEFT JOIN
    (SELECT file_id, SUM(bytes) AS free_bytes
     FROM dba_free_space
     GROUP BY file_id) fs
ON df.file_id = fs.file_id
ORDER BY df.file_name;