Oracle SQL – Create Tablespace

Hi everyone, Today i will show you how to create tablespace on Oracle Database. I installed Oracle database with GRID , so i created ASM disk that means data files are in the ASM disk. This is important, you will get the point soon.

We need to know the name of the ASM disk group in tablespace you want to add. You can list ASM groups with the following query

SELECT name, total_mb / 1024 AS total_gb, TRUNC (cold_used_mb / 1024) AS used_gb, ROUND (free_mb / 1024) AS free_gb FROM v$asm_diskgroup;

We learned about our ASM disk group, It looks like one, but there could be more than one. In this case, we must decide which group we want to add

 

CREATE TABLESPACE TS_EXAMPLE DATAFILE 
  '+ASM' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

When creating tablespace we can give multiple parameters at once. This depends on your expectations from the tablespace.

For example AUTOEXTEND ON NETX 100M, determines that tablespace size will increase by 100M based on occupancy.

Look at this ,

This is an image from oracle’s own site. You can get more information https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7003.htm

 

You can check the tablespace you created with the following query ,

 

SELECT  FILE_NAME, BLOCKS, TABLESPACE_NAME
   FROM DBA_DATA_FILES;

Everything looks successful, see you in the next article

Deniz Parlak

Hi, I'm a Computer Engineering student in Dumlupınar University.I will graduate next year. I did an internship TurkNet Telecominication Company in 2018 June- 2018 September. I had experience Linux/Unix system, SQL Server Management , SSRS, Mysql Database Management,Troubleshooting performance issues in Linux. I also interested Oracle database and technologies. I want to work as Oracle DBA after my University. I am practicing Unix, Exadata, Orace Cloud, Oracle RAC, Dataguard, EBS, Oracle BI and etc and i hope the articles will be helpful. Any questions, or support please write email deniz.parlak@yahoo.com

Leave a Reply

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