First: The 30-Second Check
Before you panic, make sure the tablespace really is full. I've seen a hundred times where someone assumed it was, but the datafile was just sitting there with plenty of room and the problem was something else entirely.
Run this to see the actual usage:
SELECT tablespace_name, file_name, bytes/1024/1024 AS size_mb, maxbytes/1024/1024 AS max_mb, autoextensible FROM dba_data_files WHERE tablespace_name = 'USERS';If AUTOEXTENSIBLE is NO and the file is at its max size, you've found the culprit. The quickest fix that takes less than a minute: add a new datafile to the tablespace.
Here's the command – just change the path to match your environment:
ALTER TABLESPACE USERS ADD DATAFILE '/u01/oradata/ORCL/users02.dbf' SIZE 100M AUTOEXTEND ON NEXT 50M MAXSIZE 4G;That's it. You're done. The table can now extend into the new file. Had a client last month whose entire order entry system froze because they ran out of space at 4 PM on a Friday. This command saved their weekend. If you're comfortable with that, stop here. If you want to make sure it doesn't happen again, move to the next step.
Moderate Fix: Turn On Autoextend (5 Minutes)
If you've got room to grow but the datafile wasn't set to autoextend, that's your real problem. Oracle will happily grow the file if you tell it to. But it's off by default in some setups, and nobody noticed until it blew up.
Check the current setting first:
SELECT file_name, autoextensible, increment_by, maxbytes FROM dba_data_files WHERE tablespace_name = 'USERS';If it's NO, run this on every datafile in the tablespace:
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf' AUTOEXTEND ON NEXT 128M MAXSIZE 8G;I set a decent increment (128M) so Oracle doesn't have to churn every few minutes. And always set a MAXSIZE. Without it, Oracle will eat the entire disk if you're not watching, and then you get ORA-01114 or worse – a downed database. I once saw a test box fill the filesystem because someone left MAXSIZE unlimited. Not fun.
If you've got multiple files, you can loop through them or just do it one by one. Five minutes, tops. You can stop here if this works for you. But if you're already tight on disk, read on.
Advanced Fix: Reclaim Space and Reorganize (15+ Minutes)
So autoextend is on, but the disk itself is full. Or you added a file and it's already full again because the table is genuinely massive. Time to clean house.
Step 1: Find the Space Hogs
Run this to see which segments are eating your tablespace:
SELECT owner, segment_name, segment_type, bytes/1024/1024 AS size_mb FROM dba_segments WHERE tablespace_name = 'USERS' ORDER BY bytes DESC FETCH FIRST 10 ROWS ONLY;You're looking for huge tables or indexes. You might find that an old archive table you forgot about is taking 90% of the space. Or a temporary table that grew out of control due to a bad query – had that happen once, a developer left a cross join running all night, filled 200GB.
Step 2: Get Rid of the Garbage
If you find obsolete data, drop it or move it to another tablespace. For example:
DROP TABLE old_archive PURGE;Or if it's an index you can rebuild smaller:
ALTER INDEX idx_name REBUILD TABLESPACE USERS;Step 3: Shrink and Reorganize (if needed)
Dropping things frees space inside the datafile, but the file stays big. To actually shrink it back, you need to move objects or use segment shrink. For tables in a locally managed tablespace with automatic segment space management (which is the default these days), you can do:
ALTER TABLE big_table SHRINK SPACE CASCADE;But that only works if you have row movement enabled. If not, you'll get ORA-10635. In that case, you can export, drop, and reimport – but that's a bigger job. Honestly, if you're at this point, the easiest practical move is often to just add a bigger datafile and move the biggest segment to a dedicated tablespace if you can. That way, the USERS tablespace stays clean for everyday stuff.
Step 4: Prevent Future Pain
Set up monitoring. I'm not a fan of fancy tools – a simple cron job that checks free space and emails you when it's below 10% works wonders. Here's a quick query:
SELECT tablespace_name, sum(bytes)/1024/1024/1024 AS gb_free FROM dba_free_space GROUP BY tablespace_name;Stick that in a script, run it daily. 30 minutes of setup saves you from a 3 AM page.
Real talk: 90% of ORA-01653 fixes are the first command I gave you. The other 10% are people who ignored the warning signs and now have to clean up the mess. Don't be that person.
Still Stuck? Here's Why
If none of this worked, you might not actually be hitting a space limit. Check if you hit the MAXSIZE on the file, or if there's a quota on the user:
SELECT * FROM dba_ts_quotas WHERE tablespace_name = 'USERS';And sometimes it's not the tablespace at all – the error might come from a temporary tablespace or an undo tablespace, but the message says USERS. Trust the message. But also check the alert log for ORA-1653 details – it'll tell you which object and which file.
If you're still stuck, post the exact output of the queries above to a forum. But you won't need to. The fix is usually the first command. Go run it.