Extending Tablespaces made Efficient

Extending Tablespaces made Efficient and Simple!!
Extending your tablespaces is amongst the most common tasks many of database administrators have to accomplish. Nothing is impossible; you just have to find the way doing it. Some people make even a simple task cumbersome by following a complex approach whereas others do complex things in a simple way. I belong to the second category and try to make my tasks as simpler as possible and today I will discuss a simple approach that will help you grow your tablespace quickly and efficiently.
Oracle allows us to extend the table and tablespaces whenever needed. This has made the life of database administrators easy by eliminating the need to monitor table extents and the free up space within each tablespace constantly. If you put some limits on the tablespace then if someone attempts to add a significant amount of data then you can get alarms that you are running out of table space.
Extending Tablespaces (Solution - I):
One approach to extend your tablespaces is to use the auto-extend tablespaces feature. However you should be aware that if you auto-extend tablespace in a production environment then it can not only cause performance problems but also puts you at a risk of running out diskspace that can eventually crash your database.
You can automatically extend a data file by using below command.
ALTER DATABASE DATAFILE ‘/UOR/ORADATA/PROD/MY_TABLESPACE.DBF' RESIZE 4M;
You can use below syntax to make a tablespace extend indefinitely but in such case you must make sure that your file system does not become full.
CREATE TABLESPACE MY_TABLESPACE_TS DATAFILE ‘/U01/ORADATA/PROD/MY_TABLESPACE.DBF'' SIZE 300M AUTOEXTEND ON NEXT 30M MAXSIZE UNLIMITED;
Extending Tablespaces (Solution - II):
Identifying Tablespace:
First of all we will run below script to find out the tablespace that is running out of space.
SELECT A.MY_TABLESPACE_TS, A.BYTES BYTES_USED, B.BYTES BYTES_FREE, B.LARGEST, ROUND(((A.BYTES-B.BYTES)/A.BYTES)*100,2) PERCENT_USED
FROM ( SELECT MY_TABLESPACE_TS, SUM(BYTES) BYTES FROM DBA_DATA_FILES GROUP BY MY_TABLESPACE_TS )
A, ( SELECT MY_TABLESPACE_TS, SUM(BYTES) BYTES , MAX(BYTES) LARGEST FROM DBA_FREE_SPACE GROUP BY MY_TABLESPACE_TS ) B
WHERE A.MY_TABLESPACE_TS=B.MY_TABLESPACE_TS ORDER BY ((A.BYTES-B.BYTES)/A.BYTES) DESC
View Tablespace:
Suppose our tablespace MY_TABLESPACE_EXTEND is running out of space. Now we will view this tablespace so as to decide if a new data file should be added or you should grow an existing data file in the tablespace.
SELECT * FROM DBA_DATA_FILES WHERE MY_TABLESPACE_TS= 'MY_TABLESPACE_EXTEND'
Extend existing data file in the tablespace:
Run below command to extend existing data file in the tablespace.
ALTER DATABASE DATAFILE '/VOLORADATA/ORA6410G/ORADATA/DEV/MY_TABLESPACE_EXTEND_01.DBF' RESIZE 268435456;
Add new data file in the tablespace:
You can run below command to add a new data file in the tablespace.
ALTER TABLESPACE MY_TABLESPACE_EXTEND ADD DATAFILE ‘/VOLORADATA/ORA6410G/ORADATA/DEV/MY_TABLESPACE_EXTEND_02.DBF' SIZE 4M AUTOEXTEND OFF;

Conclusion:
Remember it is not a good approach to create a large datafile at once. You may end up with a crashed database with corrupted file created. I advise you to always create a small datafile first. If your datafile is successfully created then grow it in to the desired size.