Here's a Quick List of 5 Most Common Tablespace Operations

A Tablespaces is a logical unit of Data storage in database, all objects like tables, indexes are stored into tablespaces. A tablespace is a logical representation of data storage while actual data is stored into datafiles of the database. A tablespace can have multiple datafile, while a datafile can be a part of only one tablespace.

Every time when you create a database object like table, index, materialized view etc DBA has to specify tablespace name in which this object will store, if not given this will go to default tablespace.

Here's a Quick List of 5 Most Common Tablespace Operations

1. Find Datafile Location and Size for a Tablespace: As I have explained, Each tablespace has corresponding datafiles to store data which are stored at OS Storage device, when storage device utilization goes high. Then DBA has to either resize the datafiles of tablespace or to move them from one place to another place. To achive this Database Administrator must know the exact location and size of data files.
SQL> select tablespace_name,file_name,bytes/(1024*1024) size_in_mb from dba_data_files;
TABLESPACE_NAME                FILE_NAME                                            SIZE_IN_MB
------------------------------ -------------------------------------------------- ------------

SYSTEM                         /opt/oracle/datafile/system.dbf                    700
SYSAUX                         /opt/oracle/datafile/sysaux.dbf                        740
UNDOTBS1                       /opt/oracle/datafile/undotbs1.dbf                     225
UNDOTBS2                       /opt/oracle/datafile/undotbs2.dbf                  200
USERS                          /opt/oracle/datafile/users.dbf                       5
SIZETEST                       /opt/oracle/sizetest1.dbf                                   240
TEST                           /opt/oracle/test.dbf                                        100
7 rows selected.
Here, Tablespace_name shows name of tablespaces in database, File_name is actual file name at OS Location and Size of file is shown as SIZE_IN_MB. Since, Temp files are not covered in this view. Command to fine temp tablespace datafile.
SQL> select tablespace_name,file_name,bytes/(1024*1024) size_in_mb from dba_temp_files;
TABLESPACE_NAME                FILE_NAME                                            SIZE_IN_MB
------------------------------ -------------------------------------------------- ------------
TEMP                           /opt/oracle/tempfile/temp.dbf                     22

2. Creating and Resizing Tablespace: Usually, Database Administrator two types of tablespaces Data and Index tablespaces, though there is no difference in physical and logical structure of tablespaces but this is normally done to achieve better performance. Suppose DBA wants to make a tablespaces of size 1G at "/opt/oracle" location, before making tablespaces make sure "/opt/oracle" location has enough free spaces to keep datafile of size 1G for tablespace.

SQL> CREATE TABLESPACE lmtbsb DATAFILE '/opt/oracle/lmtbsb01.dbf' SIZE 50M;

In above query I have making a tablespace with name "lmtbsb" at location "/opt/oracle/" with data file name "lmtbsb01.dbf" having size 50 MB. After completion of this script DBA can cross check tablespaces creation using #1 in this post.

When tablespaces is full and doesn't have free space to occupy newly added data, In that case Database Admin can face "ORA-1653 unable to extend table %s.%s by # in tablespace %s".I would suggest to use below query to keep an eye on tablespace utilization.
SQL> SELECT b.tablespace_name, b.tablespace_size_mb, sum(nvl(fs.bytes,0))/1024/1024 free_size_mb,
     (sum(nvl(fs.bytes,0))/1024/1024/b.tablespace_size_mb *100) free_percent
     FROM dba_free_space fs,
          (SELECT tablespace_name, sum(bytes)/1024/1024 tablespace_size_mb FROM dba_data_files
           GROUP BY tablespace_name
          ) b
    where fs.tablespace_name = b.tablespace_name
    group by b.tablespace_name, b.tablespace_size_mb;

TABLESPACE_NAME                TABLESPACE_SIZE_MB FREE_SIZE_MB FREE_PERCENT
------------------------------ ------------------ ------------ ------------
SYSAUX                                        740      38.1875   5.16047297
SIZETEST                                    30720        30717   99.9902344
UNDOTBS2                                      200       183.75       91.875
USERS                                           5           .5           10
SYSTEM                                        700          308           44
UNDOTBS1                                      225       206.75   91.8888889
TEST                                          100           99           99
 
7 rows selected.
If tablespace is above 90% utilized, I would suggest to increase tablespace size using either adding datafile to tablespace or resizing existing datafiles.

Resize of datafile. Here I am resizing ASM datafile. Use same command to resize Non ASM datafile.
SQL> alter database datafile '+DATA/odadata/datafile/users.262.817709621' resize 10M;
Database altered.

Adding Datafile to Tablespace
SQL> ALTER TABLESPACE lmtb ADD DATAFILE '/u02/oracle/data/lmtbsb02.dbf' SIZE 100M;

Above command will add 100M of datafile to tablespace lmtb. There are few more options to solve this issue  "ORA-1653 unable to extend table %s.%s by # in tablespace %s"

3. Renaming and Relocating Tablespace Datafiles: Renaming and Relocating Tablespace Datafiles doesn't have so much difference. In renaming we just rename the datafile, while in relocating we move datafile from one place to another place. There are many situation, where DBA has to move or rename tablespace datafiles. few are like.

1. Datafile name is not correct.
2. Disk space is full, So DBA has to move datafile from one mount point to another mount point.

We will do renaming in three steps:

    A. Offline candidate tablespace containing datafile to be renamed.
    B. move datafile using OS Command.
    C. Rename datafile using alter command.
SQL> alter tablespace TEST offline;
Tablespace altered.

SQL> select FILE_NAME,DT.TABLESPACE_NAME,DT.STATUS from dba_tablespaces dt, dba_data_files df where dt.tablespace_name like 'TEST' and dt.tablespace_name=df.tablespace_name;
FILE_NAME                                          TABLESPACE_NAME                STATUS
-------------------------------------------------- ------------------------------ ---------
/etc/oracle/oradata/orcl/example02.dbf             TEST                           OFFLINE

SQL> !

[oracle@database ~]$ cd /etc/oracle/oradata/orcl
[oracle@database orcl]$ mv example02.dbf example03.dbf
[oracle@database orcl]$ ls example*
example01.dbf  example03.dbf
[oracle@database orcl]$ exit;
SQL> alter tablespace TEST rename datafile '/etc/oracle/oradata/orcl/example02.dbf' to '/etc/oracle/oradata/orcl/example03.dbf';      
Tablespace altered.

SQL> alter tablespace TEST online;
Tablespace altered.

SQL> select FILE_NAME,DT.TABLESPACE_NAME,DT.STATUS from dba_tablespaces dt, dba_data_files df where dt.tablespace_name like 'TEST' and dt.tablespace_name=df.tablespace_name;
FILE_NAME                                          TABLESPACE_NAME                STATUS
-------------------------------------------------- ------------------------------ ---------
/etc/oracle/oradata/orcl/example03.dbf             TEST                           ONLINE

Rename of datafile is sucessfully done.

Relocation of tablespaces datafile has a small change, Instead of renaming datafile we will move datafile from one place to another place. I am only showing command which is different in Relocation here.

    A. Offline candidate tablespace containing datafile to be renamed (same as rename datafile).
    B. copy datafile using OS Command from source to destination location (show below).
    C. Rename datafile using alter command (same as rename datafile).

Suppose DBA wants to move datafile "/etc/oracle/oradata/orcl/example03.dbf" to "/etc/oracle/oradata" location. He should use below command to move datafile rest of the command are same as Renaming a datafile.

[oracle@database orcl]$ cp /etc/oracle/oradata/orcl/example03.dbf /etc/oracle/oradata/example03.dbf

4. Read Only Tablespace: In organizations, We have some data which change occassionaly or doen't change at all. To keep this kind of data, Database Administrator should use Read only tablespace. These are nomal tablespaces but changed to read only mode, which means writing on these tablespaces is not allowed.

When tablespaces is in read write mode it will show status as "online", when you change it to read only this will display status as "READ ONLY" in DBA_TABLESPACES view.
SQL> alter tablespace test read only;       
Tablespace altered.

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces where tablespace_name like 'TEST';
TABLESPACE_NAME                STATUS
------------------------------ ---------
TEST                           READ ONLY

To make it Write enable use below command.

SQL> alter tablespace test read write;

Tablespace altered.

5. Dropping a Datafile or Tablespace: When Database Administrator doen't need content of a tablespaces or datafile, he can drop the them. DBA can drop only data file from tablespace or whole tablespace. To drop a datafile, datafile must be empty, datafile should not be the only datafile into tablespace and it should not be first datafile of tablespace.

SQL> alter tablespace test drop datafile '/etc/oracle/oradata/orcl/example04.dbf';
Tablespace altered.

DBA can also drop whole tablespaces with it's datafile using below command.

SQL> drop tablespace test including contents and datafiles;
Tablespace dropped.

This is usually done, when you need some free space at OS level, This is a critical activity, so make sure you don't need data from this tablespace.

Please share your views about this article !!

No comments:

Post a Comment