Tablespace and datafile management in oracle

In this article we will learnView tablespace in oracle,create Tablespace in oracle and create more than one datafile in Tablespace,rename Tablespace in oracle,rename datafile and online and offline mode of Tablespace,resize datafile in existing tablespace,extend Tablespace in Oracle database,drop Tablespace in oracle,drop datafile in oracle Tablespace.

View tablespace in oracle:

We can find all tablespace name and their corresponding datafile name using the dba_data_files Data dictionary.
SQL> select tablespace_name,file_name,bytes/1024/1024 from dba_data_files;

TABLESPACE_NAME                                    FILE_NAME                                                    BYTES/1024/1024
-------------------------------------------------- ------------------------------------------------------------ ---------------
USERS                                              /u01/app/oracle/oradata/orcl/users01.dbf                                   5
UNDOTBS1                                      /u01/app/oracle/oradata/orcl/undotbs01.dbf                            105
SYSAUX                                             /u01/app/oracle/oradata/orcl/sysaux01.dbf                                520
SYSTEM                                             /u01/app/oracle/oradata/orcl/system01.dbf                               680
EXAMPLE                                            /u01/app/oracle/oradata/orcl/example01.dbf                           100

Create tablespace in oracle:

Now we are going to create tablespace named  MYTBS and add one datafile named myfile1 with size 2 mb  with this tablespace. To create the tablespace we execute the below command.

SQL> create tablespace MYTBS datafile '/u01/app/oracle/oradata/orcl/myfile1.dbf' size 2m;

 Add more than one data file in the tablespace: 

We can create more than one datafile in the specified tablespace. Here we create another file named myfile2 with size 3mb  on the MYTBS tablespace using the below command.
SQL> alter tablespace MYTBS add datafile '/u01/app/oracle/oradata/orcl/myfile2.dbf' size 3m;

Rename the tablespace:

We can rename the tablespace using the below command. Here we want to rename MYTBS to MYTBS1.

SQL> alter tablespace MYTBS rename to MYTBS1;
Tablespace altered.

Rename the datafile :

We can also rename the datafile which is located in the tablespace. To do that we need to change the tablespace mode from online to offline . After changing the mode we can rename our datafile and after doing this we will change the tablespace mode again from offline to online to access the data.
Suppose we have myfile1.dbf file . we want to change it to myfile3.dbf. here the below steps is required.
1. SQL> alter tablespace MYTBS1 offline;
2. Now we need to change the filename at the os level using the below command
 $ mv myfile1.dbf myfile3.dbf
3. SQL> alter database rename file '/u01/app/oracle/oradata/orcl/myfile1.dbf' to '/u01/app/oracle/oradata/orcl/myfile3.dbf';
4. SQL> alter tablespace MYTBS1 online;

we need to extend the tablespace when we required more space to avoid the data insert failure.
Suppose we insert 2000000 rows into the mytbs1 tablespace.  We got this error message.

To avoid it we need to on the auto extend feature and resize feature using the below steps.

Resize the datafile  in oracle tablespace:

We can resize the datafile in case more space required the in the tablespace to avoid data insert failure.
To resize the datafile we can execute the below command.
Suppose we want to resize the datafile ehich named myfile1 to 4mb we can execute this command.
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/myfile1.dbf' resize 4m;

Extend tablespace in oracle database:

We can also on the auto extend feature with adding datafile  on the tablespace.
SQL> alter tablespace MYTBS1 add datafile '/u01/app/oracle/oradata/orcl/myfile4.dbf' size 10m autoextend on next 1m maxsize 250m;
Here we created one datafile named myfile4.dbf with size 10mb after excced it oracle assigns 1mb upto 250mb. Now we execute the insert script.

Drop Tablespace in oracle:

In this tutorial we will learn how to drop oracle tablespace with all contents and data files and also know what will be happened if we miss the keyword  including contents and datafiles.

Drop tablespace without datafile and with datafile

We can drop tablespace without all contents and datafiles using the below command.
SQL> drop tablespace MYTBS;
All datafiles are present into the disks. These data files are not deleted. But the tablespace was dropped.

Now we are going to drop tablespace with all the datafiles we are using the below command.
SQL> drop tablespace MYTBS including contents and datafiles;
Tablespace dropped.

Drop  the datafile  in oracle tablespace:

We can drop one data file among many data files  using the below command. Suppose we want to drop myfile2.dbf file.
SQL> alter tablespace MYTBS drop datafile '/u01/app/oracle/oradata/orcl/myfile2.dbf'

In this tutorial we have learned
1. View tablespace in oracle
2. Create Tablespace in oracle and create more than one datafile in Tablespace.
3.Rename Tablespace in oracle
4.Rename datafile and online and offline mode of Tablespace
5.Resize datafile in existing tablespace
6.Extend Tablespace in Oracle database
7.Drop Tablespace in oracle
8. Drop datafile in oracle Tablespace.

Stay with us .

SHARE

Admin

• 
• 
• 
• 
•