.::: How to Create Tablespace & Check Capacity, Add datafile On existing tablespace, Change Autoextend :::.

A tablespace is a container for segments (tables, indexes, etc). A database consists of one or more tablespaces, each made up of one or more data files. Tables and indexes are created within a particular tablespace. Oracle has a limit of 64,000 data files per database.

1. Create new tablespace, create user, grant previledge

[oracle@TeguhLabOracle ~]$ more Teguhdatatgh.sql

CREATE TABLESPACE Teguhdata DATAFILE '$ORACLE_BASE/oradata/tgh/Teguhdata.dbf' size 100M AUTOEXTEND ON;

CREATE USER Teguhdata IDENTIFIED BY Teguhdata DEFAULT TABLESPACE Teguhdata TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON Teguhdata;

GRANT CONNECT,RESOURCE, CREATE ANY VIEW TO Teguhdata;

GRANT CREATE JOB TO Teguhdata;

[oracle@TeguhLabOracle ~]$ sqlplus sys/oracle@tgh as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 11 14:15:52 2018

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @Teguhdatatgh.sql

Tablespace created.

User created.

Grant succeeded.

Grant succeeded.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@TeguhLabOracle ~]$

2. Check table space

SELECT TABLESPACE_NAME, FILE_NAME, BYTES FROM DBA_DATA_FILES;

select tablespace_name, file_name, bytes ,maxbytes, status, autoextensible, increment_by, online_status from DBA_DATA_FILES;

select tablespace_name, file_name, bytes/1024/1024/1024 Size_GB, maxbytes/1024/1024/1024 Maxsize_GB, status, autoextensible, increment_by, online_status from DBA_DATA_FILES;

3. Additional datafile on existing tablespace

alter TABLESPACE  Teguhdata add DATAFILE '/u01/app/oracle/oradata/tgh/Teguhdata01.dbf' SIZE 100M ;

alter TABLESPACE  Teguhdata add DATAFILE '/u01/app/oracle/oradata/tgh/Teguhdata02.dbf' SIZE 100M ;

alter TABLESPACE  Teguhdata add DATAFILE '/u01/app/oracle/oradata/tgh/Teguhdata03.dbf' SIZE 100M AUTOEXTEND ON ;

4. Show Max Capacity

SELECT TABLESPACE_NAME, FILE_NAME, BYTES FROM DBA_DATA_FILES;

select tablespace_name, file_name, bytes ,maxbytes, status, autoextensible, increment_by, online_status from DBA_DATA_FILES;

select tablespace_name, file_name, bytes/1024/1024/1024 Size_GB, maxbytes/1024/1024/1024 Maxsize_GB, status, autoextensible, increment_by, online_status from DBA_DATA_FILES;

5. Change datafile to autoextend, Show Max Capacity

alter database DATAFILE '/u01/app/oracle/oradata/tgh/Teguhdata02.dbf'  AUTOEXTEND ON ;

6. Show allocation and free space big data

col tablespace_name format a16;

col file_name format a36;

SELECT a.tablespace_name, a.file_name, a.bytes allocated_bytes,  b.free_bytes  FROM dba_data_files a,  (SELECT file_id, SUM(bytes) free_bytes FROM dba_free_space b GROUP BY file_id) b WHERE a.file_id=b.file_id ORDER BY a.tablespace_name;

7. Show percentage Teguhdata

select df.tablespace_name "Tablespace",totalusedspace "Used MB", round(((totalusedspace* 100)/df.totalspace)) pct_used,

(CASE WHEN round(((totalusedspace* 100)/df.totalspace)) > 90 THEN 'FAILED' ELSE 'SUCCEED' END) STATUS

from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace from dba_data_files group by tablespace_name) df,

(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name from dba_segments group by tablespace_name) tu

where df.tablespace_name = tu.tablespace_name and df.tablespace_name = 'Teguhdata';