EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials Oracle Tutorial Oracle Tablespace
Secondary Sidebar
Oracle Tutorial
  • Advanced
    • Timestamp to Date in Oracle
    • Oracle Golden Gate
    • Oracle Virtual Machine
    • Oracle Describe Table
    • Oracle Clauses
    • Oracle Having Clause
    • Oracle?Primavera
    • Oracle FOREIGN Key
    • PIVOT in Oracle
    • Oracle Alter Table
    • Oracle Queries
    • Oracle Views
    • Oracle Window Functions
    • Oracle String Functions
    • Oracle Date Functions
    • Oracle Analytic Functions
    • Oracle Aggregate Functions
    • Select in Oracle
    • INSERT in Oracle
    • DISTINCT in Oracle
    • Function in Oracle
    • Oracle GROUP_CONCAT
    • Oracle INSTR()
    • Oracle CONVERT
    • Oracle LENGTH()
    • Oracle EXISTS
    • Oracle REPLACE()
    • Oracle MERGE
    • Oracle LEAD()
    • Oracle EXTRACT()
    • Oracle LISTAGG()
    • Oracle SYS_CONTEXT()
    • Oracle COALESCE
    • Oracle NVL()
    • Oracle SYSDATE()
    • Oracle?Date Format
    • Oracle SYS_GUID()
    • Oracle WILDCARDS
    • Oracle Synonyms
    • Oracle Subquery
    • BETWEEN in Oracle
    • FETCH in Oracle
    • Oracle Index
    • Oracle Function-based Index
    • Oracle UNIQUE Index
    • Oracle Bitmap Index
    • Oracle Column
    • Oracle Triggers
    • Oracle Procedures
    • Sample Database for Oracle
    • Oracle LIKE Operator
    • ORDER BY in Oracle
    • Oracle ORDER BY DESC
    • GROUP BY in Oracle
    • Oracle GROUP BY HAVING
    • Oracle Aliases
    • Table in Oracle
    • Oracle Temporary Table
    • Oracle? Table Partition
    • Oracle rename table
    • Oracle CTE
    • Cursor in Oracle
    • Oracle LOCK TABLE
    • Oracle Tablespace
    • Oracle CARDINALITY
    • Oracle REGEXP
    • Oracle REGEXP_REPLACE
    • Oracle to_date
    • JSON in Oracle
    • Oracle COMMIT
    • Oracle GRANT
    • Oracle MD5
    • Oracle ROLLBACK
    • Oracle Users
    • Oracle TIMESTAMP
    • IF THEN ELSE in Oracle
    • Oracle While Loop
    • Oracle Clone Database
    • Oracle Backup Database
    • Oracle? XML
    • Oracle XMLAGG
    • Oracle XMLTABLE
    • Oracle Performance Tuning
    • Oracle B Tree Index
    • Oracle fusion
    • Oracle ebs
    • Oracle GRC
    • Oracle ERP
    • Oracle ASM
    • Oracle Cloud
    • Oracle HCM Cloud
    • Oracle Integration Cloud
    • Oracle Jinitiator
    • Oracle pathfinder
    • Oracle VirtualBox
    • Oracle Weblogic Server
    • Oracle decode
    • Oracle Exadata
    • Oracle ZFS
    • Oracle? utilities
    • JDBC Driver for Oracle
    • Oracle? DBA Versions
    • Oracle DBA Salary
  • Basic
    • Oracle Marketing Cloud
    • What is Oracle?
    • Career in Oracle
    • How to Install Oracle
    • Oracle Versions
    • What Is Oracle Database
    • Oracle Data Warehousing
    • Oracle Warehouse Builder
    • Career In Oracle Database Administrator
    • Career In Oracle DBA
    • What is Oracle RAC
    • Oracle DBA
    • Oracle? Vanderbilt
    • What is RMAN Oracle
    • Oracle Database Administration
    • Oracle Operators
    • Oracle Constraints
    • Oracle number
    • Oracle Data Types
    • Oracle UNIQUE Constraint
    • Oracle Check Constraint
  • Joins
    • Joins in Oracle
    • Inner Join in Oracle
    • Oracle Cross Join
    • Left Join in Oracle
    • OUTER Join in Oracle
    • Oracle Full Outer Join
    • Natural Join in Oracle
    • Oracle Self Join
    • Oracle hash join
    • Oracle? Update with Join
  • Oracle SET Operators
    • UNION in Oracle
    • Oracle UNION ALL
    • INTERSECT in Oracle
    • MINUS in Oracle
  • Interview Questions
    • Oracle Interview Questions
    • Oracle Apps Interview Questions
    • Oracle Apps Technical Interview Questions
    • Oracle Database Interview Questions
    • Oracle Forms Interview Questions
    • Oracle PL/SQL Interview Questions
    • Oracle RAC Interview Questions
    • Oracle SOA Interview Questions

Related Courses

Oracle Course Training

Oracle DBA Certification Course

MongoDB Certification Training

Oracle Tablespace

By Priya PedamkarPriya Pedamkar

Oracle Tablespace

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.

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.

CREATE TABLESPACE tablespace_name
DATAFILE ‘datafile_path’
SIZE [size_tablespace];

Parameters

Below are the respective parameters mentioned:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

  • 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.

  1. PERMANENT TABLESPACE: It is a type of TABLESPACE that contains persistent objects that are stored in DATAFILE.
  2. TEMPORARY TABLESPACE: It is a type of TABLESPACE that schema objects are stored in temporary files that exist only for a session.
  3. 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 how to create a TABLESPACE in Oracle.

So, as we have already discussed earlier that there are basic three 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 had discussed the definition of the 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:

CREATE TABLESPACE tbs_perm_01
DATAFILE 'tbs_perm.df'
SIZE 5M;

Output: 

default

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:

CREATE BIGFILE TABLESPACE tbs_perm_bigfile
DATAFILE 'tbs_bigfile.df'
SIZE 10M;

Output:

big file

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.

Code:

CREATE SMALLFILE TABLESPACE tbs_perm_smallfile
DATAFILE 'tbs_smallfile.df'
SIZE 1M;

Output:

small file

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:

CREATE TABLESPACE tbs_perm_ext
DATAFILE 'tbs_perm_ext.df'
SIZE 10M
REUSE
AUTOEXTEND ON NEXT 10M MAXSIZE 20M;

Output:

Permanenttablespace

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.

Code:

CREATE TEMPORARY TABLESPACE tbs_temp_ext
TEMPFILE 'tbs_temp.df'
SIZE 4M
AUTOEXTEND ON;

Output:

Temporary tablespace

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 the 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:

CREATE UNDO TABLESPACE tbs_undo_ext
DATAFILE 'tbs_undo.df'
SIZE 4M
AUTOEXTEND ON
RETENTION GUARANTEE;

Output:

Undo tablespace

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.

Recommended Articles

This is a guide to Oracle Tablespace. Here we discuss an introduction to Oracle Tablespace, syntax, parameters, how to create a tablespace, and examples. You can also go through our other related articles to learn more –

  1. Oracle LOCK TABLE
  2. Oracle REGEXP
  3. Oracle Self Join
  4. Oracle CARDINALITY
Popular Course in this category
Oracle Training (14 Courses, 8+ Projects)
  14 Online Courses |  8 Hands-on Projects |  120+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

Oracle DBA Database Management System Training (2 Courses)4.9
All in One Financial Analyst Bundle- 250+ Courses, 40+ Projects4.8
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more