How to Create Tablespace using SQL Developer?
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/ settings options, some of which are often hard to remember which lands in errors. In this situation graphic user tools always comes 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.
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.
Step 2: Create New Tablespace
Go to storage then tablespace and right click after that choose create new
Step 3: Name and Type of tablespace
Give name and specify type of tablespace you want to create
Step 4: File specification
Use file specification tab to add data files to your tablespace
Step 5: Properties
Use property tab to set the properties of your tablespace
Tablespace Offline mode
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.
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.
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.
Step 7: See 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.
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 networking and help me reach out to more people. Thanks & have a nice day!
SQL Script and Presentation used
You can DOWNLOAD SQL script and presentation used in the Video and in this article.
SQL Script used in Video and in this article
I have used copy cloud to share these resourses. You can also join copy cloud and get 15GB free cloud storage for lifetime. If you will use this referal link https://copy.com?r=j7eYO7
You will get 5GB extra free cloud storage means total 20GB free cloud storage.