In the previous tutorial we have seen how to create permanent, temporary and undo tablespace using the traditional way where we have to write the whole Create Tablespace statement along with all the configuration/ setting options, some of which are often hard to remember which lands us in errors. In this situation graphic user tools always come handy where they do all the dirty work and we do not have to remember anything.

Oracle Database SQL Developer is one of the best and most reliable open source tools. Below are the steps of creating a permanent tablespace using SQL Developer.

Step 1:

To create a tablespace first you have to go to the DBA panel. You can find DBA panel in View Menu. Then you have to add a connection. It’s recommended that connection which you are adding in DBA panel must be with privileged user such as sys.

Suggested Reading: How to create a SQL Developer connection with SYS user

create tablespace using sql developer by manish sharma

Step 2: Create New Tablespace

Go to storage then tablespace and right click. After that choose create new.

create tablespace using sql developer by manish sharma

Step 3: Name and Type of tablespace

Give name and specify type of tablespace you want to create.

create tablespace using sql developer by manish sharma

Step 4: File specification

Use file specification tab to add data files to your tablespace.

create tablespace using sql developer by manish sharma

Step 5: Properties

Use property tab to set the properties of your tablespace.

create tablespace using sql developer by manish sharma

Tablespace Offline mode

Offline Normal: A tablespace can be taken offline normally if no error conditions exist for any of the datafiles of the tablespace. No datafile in the tablespace can be currently offline as the result of a write error. When you specify OFFLINE NORMAL, the database creates a checkpoint for all datafiles of the tablespace as it takes them offline. NORMAL is the default.

Offline Temporary: A tablespace can be taken offline temporarily, even if there are error conditions for one or more files of the tablespace. When you specify OFFLINE TEMPORARY, the database takes offline the datafiles that are not already offline, check pointing them as it does so. If no files are offline, but you use the temporary clause then media recovery is not required to bring the tablespace back online. However, if one or more files of the tablespace are offline because of write errors, and you take it offline temporarily, then the tablespace requires recovery before you can bring it back online.

Offline Immediate: A tablespace can be taken offline immediately, without the database taking a checkpoint on any of the datafiles. When you specify OFFLINE IMMEDIATE, then the media recovery for the tablespace is required before the tablespace can be brought online. You cannot take a tablespace offline immediately if the database is running in NOARCHIVELOG mode.

If you absolutely have to take a tablespace offline then you should use the NORMAL clause (default) if possible. This ensures that in case of an incomplete recovery while resetting the redo log sequence using an ALTER DATABASE OPEN RESETLOGS statement, the tablespace does not require recovery to be online. TEMPORARY must be specified only if it is impossible to take the tablespace offline normally. In this case, only those files that are taken offline due to errors are need to be recovered before the tablespace can be brought online. Also IMMEDIATE can only be specified after both the normal and the temporary settings have been tried.

Step 6: Default parameter

In case you are creating a temporary and undo tablespace then this step is not necessary as it involves compression of tablespace which can only be done in permanent tablespace unlike undo or temporary tablespace.

create tablespace using sql developer by manish sharma

Step 7: Check your DDL

This is the last step in the creation of tablespace using SQL developer. Here you can see the CREATE TABLESPACE statement with all the settings done by you using SQL Developer gui.

create tablespace using sql developer by manish sharma

Next you just have to hit the OK button as doing so will create your tablespace.

That’s all on How to create tablespace using SQL developer. Hope you liked what you read. Kindly please share it on your social network and help me reach out to more people. Thanks & have a nice day!