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