Troubleshooting ORA-01658: Unable to Create INITIAL Extent for Segment in Tablespace

When encountering the ORA-01658 error in Oracle Database, it signifies a failure to allocate an initial extent for a segment in a specified tablespace. This issue typically arises due to insufficient space or exceeded file size limits within the tablespace. Below is a comprehensive guide to troubleshoot and resolve this error.

1. Identify the Tablespace: First, identify the tablespace that needs to be extended. In this case, it’s the “USERS” tablespace.

2. Check Current Size: Before extending, it’s good to check the current size of the datafile associated with the tablespace.

   SELECT file_name, bytes / 1024 / 1024 AS size_mb
   FROM dba_data_files
   WHERE tablespace_name = 'USERS';
   

3. Extend the Tablespace: You can extend the tablespace by adding another datafile or increasing the size of an existing datafile. Here’s how to increase the size of an existing datafile:

   ALTER DATABASE DATAFILE '/path/to/users01.dbf' RESIZE 500M;
   

   This command increases the size of the datafile named ‘users01.dbf’ to 500MB. Adjust the size according to your requirements.

4. Verify Extension: After executing the above command, verify if the datafile size has been extended successfully:

   SELECT file_name, bytes / 1024 / 1024 AS size_mb
   FROM dba_data_files
   WHERE tablespace_name = 'USERS';
   

5. Check Tablespace Usage: Monitor the tablespace usage to ensure that the extension has resolved the space issue:

   SELECT tablespace_name,
          SUM(bytes) / 1024 / 1024 AS total_space_mb,
          SUM(bytes - NVL(free_space, 0)) / 1024 / 1024 AS used_space_mb,
          SUM(NVL(free_space, 0)) / 1024 / 1024 AS free_space_mb
   FROM dba_data_files
   JOIN (SELECT file_id, SUM(bytes) free_space
         FROM dba_free_space
         GROUP BY file_id) ON file_id = file_id
   WHERE tablespace_name = 'USERS'
   GROUP BY tablespace_name;
   

By following these steps, you can extend a tablespace in Oracle Database, addressing the ORA-01658 error and ensuring sufficient space for your database objects. Adjust the commands and sizes according to your specific requirements and environment.