Problem solve Get help with specific problems with your technologies, process and projects.

Troubleshooting shrinking default tablespace availability

I don't understand the reason my 8.1.7 default tablespace availablity has shrunk from 2500M to 1000M in a month. I import weekly three or four user instances of databases where the dump size is typically under 10 MB.

Why does:

 drop user SCOTT cascade;
 alter tablespace USER_DATA coalesce;
not make more free space available in USER_DATA?

I normally drop user SCOTT, coalesce, create user SCOTT again, then import a dump into SCOTT. (I have five-to-ten user instances available at any time.) What other database parameters should I adjust in order to avoid adding datafiles to USER_DATA?

Please note that imported database user instances seldom have more than a hundred rows of table growth during the course of a week or two active lifespan.

Are you using Locally Managed Tablespaces or Dictionary Managed Tablespaces (the default). I'm going to assume that you are using Dictionary Managed Tablespaces.

First, check to ensure that the default PCT_INCREASE for your tablespace is non-zero. This is done by issuing the following:

SELECT pct_increase FROM dba_tablespaces
WHERE tablespace_name='USER_DATA';
If it is equal to zero, then modify it a non-zero value, typically 1. This is done by the following command:
(pct_increase 1);
There is a process in the database called the System Monitor, or SMON. Part of SMON's job is to coalesce free space in the tablespace. It will only do this automatically for those tablespaces who's default PCT_INCREASE is non-zero. And if there is a lot of free space to coalesce, SMON will do some, then go back to sleep, then pick up at a later time. Over enough time, all free space will be coalesced. If you want to ensure that free space is being coalesced, issue the following query:
SELECT count(*) AS num_free_chunks
FROM sys.ts$ t, sys.fet$ f
WHERE t.ts# = f.ts#
AND t.name='USER_DATA';
Note the number given. Then repeat the query 10 or 15 minutes later. If free space is being coalesced, then you will see this number drop. If the number stays constant, then no free space is being coalesced.

There have also been some bugs due to this issue. Search Metalink for more information.

For More Information

  • What do you think about this answer? E-mail the editors at editor@searchDatabase.com with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.