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