Decrease / Increase / Resize tablespace in Oracle Database

Below is the list of commands you can use to increase / decrease / Resize your tablespace in Oracle Database. I run this commands in Oracle 11g database however these should also run fine in other versions of Oracle as well.

Adding more tablespace
Alter tablespace  add datafile 'datafile_location_where_file_will_be_added_without_file_name' size 1G;

Checking size of datafiles
Select name, bytes from v$datafile;


Decreasing Tablespace
Say your datafile is of size of 10 gb and you want to decrease it by 6 GB you can use below command.
Alter database datafile 'complete_path_to_datafile_goes_here' resize 4G;

Above command may not work it space is already utilized and data is alredy there in the file. Oracle may throw appropriate error message like below.

ORA-03297: file contains used data beyond requested RESIZE value

ORA-12516, TNS:listener could not find available handler with matching protocol stack

In one of my SOA project, we are running SOA Suite 11g - 11.1.1.4  on Weblogic 11g Cluster in Bladframe machine. Backend used here for SOA Schema database is Oracle 11g RAC database.

After couple of weeks of successfully execution in developement environment we hit this issue of  ORA-12516, TNS:listener. This error generally means there is no connection available to the database.We need to configure the database to allow more connection.

As suggested by Oracle, we should have number of processes setting to 200 for SOA Suite 11g. This setting is already in place and still we hit the ORA-12516, TNS:listener-issue. This happens mostly because of wrong Data sources connection settings, which allows to exapand its connection pool to hundreds of connections.

Even while restarting the weblogic server you will not be able to get the weblogic server in running mode , because all data sources will fail while trying to connect to database and you server state will go to Admin mode because of this.

Solution to ORA-12516, TNS:listener Error

1. Loging to one of the database node using sqlplus as sys or system (basically sysdba role required)
2. Increase the number processes and sessions by executing below commands
• ALTER SYSTEM SET PROCESSES=400 SCOPE=SPFILE;
• ALTER SYSTEM SET SESSIONS=400 SCOPE=SPFILE;
3. Bounce the database again to take these changes
• SHUTDOWN IMMEDIATE;
• STARTUP;
4. To Verify the settings are updated properly; execute below sql commands
1. SHOW PARAMETERS PROCESSES;
2. SHOW PARAMETERS SESSIONS;

If you are using Oracle RAC database you may want to perform these steps again on another node.