In this article, you will get to know the step by step process of how we can Create/Drop/Rename Tablespace in Oracle.
Table of Contents
Tablespaces are the first level of logical origination in your database. Data for Oracle tables, indexes, etc is stored in data files, but never when an object is defined, the object is associated with a file directly. All the time the Oracle objects are located in the tablespaces. The tablespaces are logical concepts and each tablespace is in relation with one or more physical file. So, when an object is created in a tablespace, the data will be stored automatically in the files associated with that tablespace.
In our database, we have several tablespaces which store our data: –
1) System Tablespace: – This tablespace is created automatically when the database is created. The SYSTEM tablespace is always online when the database is open.
2) Sysaux Tablespace: – Sysaux tablespace is also a mandatory tablespace and it is also created at the time of database creation. In this tablespace, some Oracle tools are stored and if this tablespace goes down then it does not affect the core functionality but some features will affect which uses this tablespace.
3) Undo Tablespace: –The undo tablespace will always hold the before-image of table data
There are the various purpose of undo tablespace: –
- Providing read consistency for SQL queries
- Rolling back unwanted active transactions
- Recovering terminated transactions
- Analyzing older data by using Flashback Query
- Recovering from logical corruptions using the Flashback features
4) Temp Tablespace: – The temp tablespace is used to manage space for sorting purpose.
5) User Tablespace: –
To Create/Drop/Rename Tablespace in Oracle we need to follow below examples: –
Status of Tablespace: –
Command: – select ts#, name from v$tablespace;
Creation of tablespace
2) Now I will Create one more tablespace with the help of below command.
Command: – Create tablespace karan datafile ‘/u01/app/oracle/oradata/karan01.dbf’ size 50m;
Now I can check from the above output, I have a new tablespace called Karan.
Renaming of Tablespace
3) Now I will rename my Karan tablespace to Karan01.
Command: – alter tablespace karan rename to karan01;
Now I can see from above output tablespace name has been changed from Karan to Karan01.
Deletion of Tablespace
Command: – drop tablespace karan01;
Now I can check from above output my Karan01 tablespace has been dropped successfully.
Hope this blog will help you!!
If this article is useful for you please comment, share with your friends.
In case of any issues please send your query to below comment section.
For more articles please do visit below links: –
If you want to download Oracle virtual box or VMware Workstation Player, Please click on below links:-