How to Create/Drop/Rename Tablespace in Oracle | Karan Rajpoot

                                                           How to Add/Drop/Rename Tablespace in Oracle 


Tablespace: -

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
for users.

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: - The user tablespace stores the actual data of the users, Which is owned by different schemas.

Examples: -

Status of Tablespace: -

1) Check how many tablespaces are present with the help of below command.

Command: - select ts#, name from v$tablespace;

How many tablespaces are present

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;

Creation of tablespace

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;

Renaming of tablespace

Now I can see from above output tablespace name has been changed from Karan to Karan01.

Deletion of Tablespace

4) Now I will drop my Karan01 tablespace.

 Command: - drop tablespace karan01;


Deletion of tablespace

Now I can check from above output my Karan01 tablespace has been dropped successfully.

 Thank you!!

Comments

Post a Comment

Popular posts from this blog

How to resolve "No protocol specified" error

Image
Error 1) [oracle@prod ~]$ dbca No protocol specified Error 2) [oracle@prod ~]$ netca Oracle Net Services Configuration: No protocol specified Error: null Check the trace file for details: /u01/app/oracle/cfgtoollogs/netca/trace_OraDb11g_home1-1901166PM3102.log Oracle Net Services configuration failed.  The exit code is 1 Solution Step 1) login as root user Step 2) run command xhost + this command allows you to use any graphical tool Example: - [root@prod Desktop]# xhost + access control disabled, clients can connect from any host Step 3) Now again run graphical tool utility command, that issue will get resolved now. [oracle@prod ~]$ dbca
Read more

How to do "Multiplexing of Control Files" through spfile ( server parameter file) in Oracle 11g

Image
Step 1)  To perform "Multiplexing of Control Files" through spfile ( server parameter file)" first we need to create spfile from pfile. Example to create spfile from pfile: -                                       Step 2) Copy the control files in physical location from 'cp' command. Note: - At present, I have 2 control files,  I will add one more Control files in the same location. Example: -  Now you can see from above output control04.ctl file has been physically added successfully under /u01/app/oracle/oradata/prod/control04.ctl location. Step 3)  Now you need to set the same through alter command. a)  first shut down the database                                          b)  Startup (Start the database through spfile)                                                                         Step 4) Now through SQL prompt alter the changes. Example: - Step 5) Now shutdown and start the database again and check
Read more

How to Add, Resize, Drop and Rename of a datafile in Tablespace

Image
                                       How to Add, Resize, Drop and Rename a datafile in Tablespace In order to create a data file first, need to create a tablespace, To know how to create a tablespace  Click here To demonstrate, I will use USERS tablespace to Add,  Resize, Drop and Rename a data file. First, we can check how many data files are present under USERS tablespace through below command. Command: - select name from v$datafile; We can see from above output only a single data file is present under USERS tablespace. Addition of Datafile: - Command to add a datafile: - alter tablespace users add datafile '/u01/app/oracle/oradata/prod/users02.dbf' size 50m; The new data file has been added to my USERS tablespace. Resize of a Datafile: - Command to resize the datafile: - alter database datafile '/u01/app/oracle/oradata/prod/users02.dbf' resize 20m; Renaming of a Datafile: - We have a few steps to rename a data file: -
Read more