How to Create Tablespace ( Bigfile, Permanent, Undo and Temp ) in Oracle

I will explain How to Create Tablespace ( Bigfile, Permanent, Undo and Temp ) in Oracle in this post.

 

 

 

 

Create Tablespace ( Permanent, Undo and Temp ) in Oracle

 

 

You should read the following articles, if you don’t know What is the Tablespace in Oracle.

What is the Tablespace in Oracle

 

 

 

Create Permanent Tablespace in Oracle

 

If you are using file system for Oracle Storage, then you can use it following script.

 

CREATE TABLESPACE NEW_TBS_TEST
DATAFILE
'/oracle/oradata/TEST/NEW_TBS_TEST01.dbf' SIZE 4G AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

 

 

If you are using the Oracle ASM , then you can use it following script.

 

CREATE TABLESPACE NEW_TBS_TEST
DATAFILE
'+DATA' SIZE 4G AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

 

 

Create Temporary ( Temp ) Tablespace in Oracle

 

If you want to create temporary (temp) tablespace, you can create it as follows, if you use Oracle ASM.

CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE 
'+DATAC1' SIZE 100M AUTOEXTEND ON NEXT 1024M MAXSIZE UNLIMITED
TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

 

 

If you use File system, you can create the temp tablespace as follows.

CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE 
''/oracle/oradata/TEST/TEMP02.dbf' SIZE 100M AUTOEXTEND ON NEXT 1024M MAXSIZE UNLIMITED
TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

 

 

 

Create Undo Tablespace in Oracle

If you want to create undo tablespace, you can create it as follows, if you use Oracle ASM.

CREATE UNDO TABLESPACE UNDO2 DATAFILE 
'+DATAC1' SIZE 100M AUTOEXTEND ON NEXT 1024M MAXSIZE UNLIMITED
RETENTION NOGUARANTEE
BLOCKSIZE 8K
FLASHBACK ON;

 

 

If you use File system, you can create the undo tablespace as follows.

CREATE UNDO TABLESPACE UNDO2 DATAFILE 
'/oracle/oradata/TEST/UNDO02.dbf' SIZE 100M AUTOEXTEND ON NEXT 1024M MAXSIZE UNLIMITED
TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

 

 

Create Bigfile Tablespace in Oracle

If you want to create bigfile tablespace, you can create it as follows, if you use Oracle ASM.

CREATE BIGFILE TABLESPACE BIGTABLESPACE DATAFILE 
'+DATAC1' SIZE 100M AUTOEXTEND ON NEXT 1024M MAXSIZE 34359738344K
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

 

 

 

Add Datafile to Tablespace

You can add new datafile to the tablespace , If Oracle ASM is used, you can use the following script.

ALTER TABLESPACE NEW_TBS_TEST ADD DATAFILE '+DATA' SIZE 15M AUTOEXTEND ON MAXSIZE UNLIMITED;

 

You can add new datafile to the tablespace , If File System is used, you can use the following script.

ALTER TABLESPACE NEW_TBS_TEST ADD DATAFILE '/oracle/oradata/TEST/NEW_TBS_TEST03.dbf' SIZE 15M AUTOEXTEND ON MAXSIZE UNLIMITED;

 

 

 

Tablespace usage percentage in Oracle

 

You can check and query the usage of tablespaces with the following script.

 

set pagesize 1000 linesize 180
tti 'Tablespace Usage Status'
col "TOTAL(MB)" for 99,999,999.999
col "USAGE(MB)" for 99,999,999.999
col "FREE(MB)" for 99,999,999.999
col "EXTENSIBLE(MB)" for 99,999,999.999
col "FREE PCT %" for 999.99
col "USED PCT OF MAX %" for 999.99
col "NOTO" for 9999
col "OTO" for 999
select d.tablespace_name "ADI",
d.contents "TIPI",
nvl(a.bytes /1024/1024,0) "TOTAL(MB)",
nvl(a.bytes - nvl(f.bytes,0),0)/1024/1024 "USAGE(MB)",
nvl(f.bytes,0)/1024/1024 "FREE(MB)",
nvl((a.bytes - nvl(f.bytes,0))/a.bytes * 100,0) "FREE PCT %",
nvl(a.ARTACAK,0)/1024/1024 "EXTENSIBLE(MB)",
nvl((a.bytes - nvl(f.bytes,0))/ (a.bytes + nvl(a.ARTACAK,0)) * 100,0) "USED PCT OF MAX %",
a.NOTO, a.OTO
from sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes,
sum(decode(autoextensible,'YES',MAXbytes - bytes,0 )) ARTACAK,
count(decode(autoextensible,'NO',0)) NOTO,
count(decode(autoextensible,'YES',0)) OTO
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes
from dba_free_space
group by tablespace_name) f
where d.tablespace_name = a.tablespace_name(+)
and d.tablespace_name = f.tablespace_name(+)
and NOT (d.extent_management like 'LOCAL'and d.contents like 'TEMPORARY')
UNION ALL
select d.tablespace_name "ADI",
d.contents "TIPI",
nvl(a.bytes /1024/1024,0) "TOTAL(MB)",
nvl(t.bytes,0)/1024/1024 "USAGE(MB)",
nvl(a.bytes - nvl(t.bytes,0),0)/1024/1024 "FREE(MB)",
nvl(t.bytes/a.bytes * 100,0) "FREE PCT %",
nvl(a.ARTACAK,0)/1024/1024 "EXTENSIBLE(MB)",
nvl(t.bytes/(a.bytes + nvl(a.ARTACAK,0)) * 100,0) "USED PCT OF MAX %", a.NOTO, a.OTO
from sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes,
sum(decode(autoextensible,'YES',MAXbytes - bytes,0 )) ARTACAK,
count(decode(autoextensible,'NO',0)) NOTO,
count(decode(autoextensible,'YES',0)) OTO
from dba_temp_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes_used) bytes
from v$temp_extent_pool
group by tablespace_name) t
where d.tablespace_name = a.tablespace_name(+)
and d.tablespace_name = t.tablespace_name(+)
and d.extent_management like 'LOCAL'
and d.contents like 'TEMPORARY%'
order by 3 desc;
exit;

 

The result of script is as follows.

                                                    Tablespace Usage Status

NAME                           TYPE            TOTAL(MB)       USAGE(MB)        FREE(MB) FREE PCT %  EXTENSIBLE(MB) USED PCT OF MAX %
------------------------------ --------- --------------- --------------- --------------- ---------- --------------- -----------------
UNDOTBS1                       UNDO           32,767.984         489.984      32,278.000       1.50            .000              1.50
STATSPACK                      PERMANENT      30,510.000       8,876.750      21,633.250      29.09       2,257.984             27.09
SYSTEM                         PERMANENT      30,360.000       7,488.000      22,872.000      24.66       2,407.984             22.85
TEMP                           TEMPORARY      13,110.000          42.000      13,068.000        .32      19,657.984               .13
SYSAUX                         PERMANENT       1,320.000       1,251.125          68.875      94.78      31,447.984              3.82
USERS                          PERMANENT           5.000           1.313           3.688      26.25      32,762.984               .00

 

 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

 

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *