Tablespace Management in Oracle

Back to: Oracle DBA Tutorials

Tablespace Management in Oracle

In this article, I am going to discuss Tablespace Management in Oracle with Examples. Please read our previous article where we discussed How to Create a Permanent Tablespace in Oracle.

Tablespace Management in Oracle

Now, we will try to learn about how to view tablespace information using SQL developer. We will learn to create a tablespace using SQL developer. Make sure the database and listener are up and running fine. Once the database and listener are up and running. Open SQL Developer and connect to the new pluggable database. The new pluggable database is PDBTS.

Step 1: Connect to the new pluggable database.

Connect to the pluggable database PDBTS using the admin user pdbts_Admin.

So, the connection is a success. Let’s go ahead and check the container name and the user name.

Step 2: View Tablespace Information

In SQL Developer, we can see the information about the tablespaces.

We can see there is pdbts database column. If we right-click on the pdbts database name then we can see multiple options for the database. Let us right-click on pdbts database.

Click on Manage database. If we click on manage database, we can see the graphical representation of the tablespaces.

We can see the tablespaces utilization. The tablespace utilization is red and green. Red indicates its almost full and green indicates free space.

We can see the columns like percent_used and percentage free. As we have created a tablespace named test. We can see the size allocated for the tablespace is 100. But we have used only 1 MB of space in 100 MB. This is how we can see the tablespace information in SQL developer.

Step 3: Connecting as DBA in SQL developer.

Below is the view of the entire SQL developer.

This is the entire view of SQL Developer. In the top left corner, we can see the view column. If we click on the view column, we can see multiple options. We have to select the dba option as we are currently learning about dba.

We can see the DBA option here. Just click on the DBA option. Then a new window in the left toolbar.

We can see the option of the + icon. This is used to create a new connection with dba privileges. Let’s go ahead and click on the + icon.

We can see a popup on select connection. We have to select the connection name and click ok. Once we click ok, we will get a connection name as below.

Let us go ahead and expand the pdbts connection.

We can see there are multiple options and multiple objects. We can go through each and every object and every resource. As we are learning about tablespaces, let us check the tablespaces. In the storage column, we can see the object tablespace. Let us open tablespaces.

We can see there are multiple tablespaces. Apart from TEST, all the other tablespaces were created during the tablespace creation. In the same way, we can see tablespace details below if we click on the tablespaces column.

Step 4: Create tablespace in SQL developer.

We can create a tablespace in SQL developer. There is an option in the above image which is actions. Here actions perform several tasks on the database. Let us see the actions option and explore that to create a tablespace.

If we click on the Actions button, we can see a new option called Create new. Let us go ahead and click on Create New.

We can see the below popup. In this below, we have to mention the tablespace name, tablespace type, and the datafile.

The first box indicates the tablespace name and tablespace type. We have mentioned the tablespace name as TEST_2 and the tablespace type as Permanent.

The column file specifications are present. This is where we have to mention the data file name. We are mentioning the data file name as TEST_2_01.DBF. The extension “.dbf” indicates the database file. We have to mention the directory where the data file is stored. In order to get the location of the data file. Go to the below path.

So, we are in the folder. Let us open the terminal from this path and use the command “pwd” to get the path. This path will be placed in the SQL developer.

So, we will be copying this path and pasting this path into SQL developer. Once the location is given, we have to mention the data file size. We are mentioning the data file size as 500 M.

So, we have mentioned the file size as 500M. So, the file information is done. Let us go ahead and provide the properties of the data file. Click on the properties column. Below is the properties column. This is an edited section. We will about each and every parameter in detail.

The File Type is consisting of two types big datafile and small datafile. We are choosing a small data file. As this is for practice, we are choosing a small data file. The Online status should as be online as this datafile needs to be available for the users. Block size we are leaving it as empty as oracle takes the default block size as 8kb.

The logging parameter will be taken as logging as whenever any data stored in the tablespace will be logged into the redo log files. The Segment Management will be taken as Auto as oracle manages the segment structure. The Extent Management Type is also set to Local as it is the default. Once all these parameters are set. Select the Auto Allocate option. As space needs to be allocated automatically in the datafile.

Once everything is set then select the Default Parameters option.

Here we have an option called Table Compression. We have to select the option “No Compress” as this option will not compress the table size.

This No compress does not compress the tablespace. Once this option is set then select the DDL option. This column shows the DDL generated by the options that we have mentioned in the previous columns.

So, this is the exact create statement that is used to create a tablespace. All the parameters that we have mentioned are present in the statement. We can just simply use this statement and run this in the SQL. We can click on the Ok option and the SQL developer will create the tablespace.

Clicking on Ok will take some time, and creating the tablespace. SQL developer will redirect to the tablespace page.

We can see the new tablespace TEST_2. So, we have created the new tablespace. We can see the used storage is 1 MB. This is allocated to dictionaries.

Step 5: Create a user and assign tablespace to the user.

Let us go ahead and create a user in the PDBTS database and assign the default tablespace as TEST_2.

Statement: create user xyz identified by goodluck default tablespace TEST_2;

We have created the user xyz. Let us try to grant some privileges to xyz user. We are going to grant create a session, create a table, and unlimited to TEST_2 tablespace.

Statement: grant create session, create table, unlimited tablespace to TEST_2;

So, we have granted some privileges to user xyz. Let us go ahead and create a table using the user xyz.

Statement: Create table xyz.test(n number);

This statement indicates creating a table test under xyz schema.

So, the table TEST is created. Let us go ahead and check the existence of the table TEST. As we have created the table TEST under the schema xyz, let us check the table under the same schema.

Query: select * from dba_tables where owner=’XYZ’;

From the above image we can see the table is assigned to the schema xyz and the default tablespace is TEST_2. Now, the if we add any data in the TEST table the data will be stored in the TEST_2 tablespace.

Step 6: Drop tablespace.

Let us go ahead and drop the tablespace TEST_2. The syntax for dropping the tablespace TEST_2 is as follows.

Statement: DROP TABLESAPCE TEST_2;

We got an error that indicates there is a table present in the TEST_2 tablespace. So, if we drop the tablespace which has contents then we will get an error. So, we have to use the option including contents in order to drop the contents in the tablespace also. So, let us try to use that option and drop the tablespace TEST_2.

Statement: DROP TABLESPACE TEST_2 INCLUDING CONTENTS;

Even though the tablespace is dropped the datafile will not be dropped. Let us go ahead and check the existence of the data file.

In order to drop the datafiles also, we have to mention the datafiles option in the drop statement. The statement will be as below.

Statement: drop tablespace TEST_2 including contents and datafiles;

So, if we try to check the tables under the schema xyz we will find them empty.

Query: select * from dba_tables where owner=’xyz’;

We did not find any tables under the user xyz. So, we understood how to create a tablespace and how to drop the tablespace. We have learned how to view the DBA role in the SQL developer.

In the next article, I am going to discuss How to Alter Tablespace and Datafile in Oracle. Here, in this article, I try to explain Tablespace Management in Oracle with Examples. I hope you enjoy this Tablespace Management in Oracle article.