IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
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.
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 )
