Trending September 2023 # How To Create Oracle Tablespace With Examples # Suggested October 2023 # Top 12 Popular

You are reading the article How To Create Oracle Tablespace With Examples updated in September 2023 on the website Uyenanhthammy.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 How To Create Oracle Tablespace With Examples

Introduction to Oracle Tablespace

Oracle Tablespace can be defined as a logical storage unit (Oracle database can consist of one or more such logical units) consisting of DATAFILES, which stores data objects (Data Objects can be tabled present inside the schema) tables present in the database and all of them collectively storing all databases data, the logical data is stored in the TABLESPACES and the physical data of the oracle database is stored in the DATAFILES which are associated with each TABLESPACES present in the database.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax and Parameters

Let us now look at the basic syntax of the Oracle TABLESPACE so that we get to know the structure of the create TABLESPACE.

SIZE [size_tablespace];

Parameters

tablespace_name: This is the first parameter that refers to the name of the TABLESPACE which we want to create. It is written after the CREATE TABLESPACE statement.

datafile_path: This parameter refers to the path of the data file of the TABLESPACE in the DATAFILE clause. We can use the full path, and it is written after the DATAFILE clause.

size_tablespace: This parameter refers to the size of the TABLESPACE in the SIZE clause.

Explanation: One Important point to note is that we can create both SMALL FILE and BIGFILE TABLESPACES. SMALL FILE TABLESPACES means it contains 1,022 data files or temporary files, whereas in the case of BIGFILE TABLESPACES it contains only one DATAFILE or TEMPFILE. In case we omit BIGFILE or SMALL FILE then Oracle will take the DEFAULT TABLESPACE type.

How to Create Oracle Tablespace?

We discussed the definition of Oracle TABLESPACE, and now we will look into how we can create an Oracle TABLESPACE in the database. In Oracle, we can have three kinds of TABLESPACE Oracle. They are as follows.

PERMANENT TABLESPACE: It is a type of TABLESPACE that contains persistent objects that are stored in DATAFILE.

TEMPORARY TABLESPACE: It is a type of TABLESPACE in that schema objects are stored in temporary files that exist only for a session.

UNDO TABLESPACE: It is a type of TABLESPACE created when the Oracle database runs in automatic undo management mode to manage the undo data.

Examples to Implement Oracle Tablespace

Let us now look at a few examples of creating a TABLESPACE in Oracle.

So, as we have already discussed earlier, there are three basic types of TABLESPACES; we will now look into the various examples for those types of TABLESPACES to understand them better.

Examples #1

PERMANENT TABLESPACE: In the earlier section, we discussed the definition of PERMANENT TABLESPACE. In the first example under this section, we are going to create a default permanent TABLESPACE, which will have a DATAFILE of size 5MB. Let us look at the query for the same.

Code:

SIZE 5M;

Output:

Explanation: As we can see in the above screenshot, a TABLESPACE with one DATAFILE has been created.

Examples #2

In the second example, we are going to create a big file PERMANENT TABLESPACE, which will also have a DATAFILE size of 10MB. Let us now look at the query for the same.

Code:

SIZE 10M;

Output:

Explanation: In the above screenshot displays that the BIGFILE TABLESPACE named tbs_perm_bigfile with a size of 10M has been created.

Examples #3

In the third example, we are going to create a SMALL FILE PERMANENT TABLESPACE, which will have a DATAFILE size of 1MB. Let us now look at the query for the same.

SIZE 1M;

Output:

Explanation: In the above screenshot displays that the SMALL FILE TABLESPACE named tbs_perm_smallfile with a size of 1M has been created.

Examples #4

In the fourth example, we are going to create a PERMANENT TABLESPACE with AUTO EXTENSION. Let us create a query for the same.

Code:

AUTOEXTEND ON NEXT 10M MAXSIZE 20M;

Output:

Explanation: As we can see in the screenshot, the output shows that the TABLESPACE has been successfully created.

Examples #5

TEMPORARY TABLESPACE: In the earlier section, we had discussed the definition of the TEMPORARY TABLESPACE. At this point, we will discuss creating a TEMPORARY TABLESPACE in the Oracle database. For example, we will try to create a TEMPORARY TABLESPACE with a size of 4MB with the AUTOEXTEND as ON. Let us now look at the query.

AUTOEXTEND ON;

Output:

Explanation: As per the screenshot, we can see that the TEMPORARY TABLESPACE has been created successfully.

Examples #6

UNDO TABLESPACE: In the previous section, we discussed the definition of UNDO TABLESPACE. At this point, we are going to discuss creating an UNDO TABLESPACE in the Oracle database. For example, we will try to create an UNDO TABLESPACE with a size of 4MB with the AUTOEXTEND as ON as well with RETENTION GUARANTEE. Let us now look at the query below.

Code:

RETENTION GUARANTEE;

Output:

Explanation: As per the screenshot, we can see that the UNDO TABLESPACE has been created successfully.

Conclusion

In this article, we discussed at the beginning of the article the definition of the ORACLE TABLESPACE. In the subsequent section, we discussed the basic syntax of the TABLESPACE along with the definition of various types of ORACLE TABLESPACES. Later on, we discussed them with the help of examples.