Why Oracle is giving error : "no more tables permitted in this cluster"?

Question

I am getting error "no more tables permitted in this cluster" when trying to create a clustered table.

Oracle's documentation about the cluster feature says:

A cluster can contain a maximum of 32 tables.

But at the time of the error, the cluster contains only 18 tables as per following query:

select * from user_tables where cluster_name='MY_CLUSTER';

I suspect that the tables that were once part of the cluster but were later dropped are still being counted towards that maximum allowed limit.

Is there a way to check the above hypothesis?

Answer 1

Check the view USER_CLU_COLUMNS. If you've dropped a table it may still be listed here but with an internal name not its original.

select count(distinct (TABLE_NAME))
  from USER_CLU_COLUMNS
 where CLUSTER_NAME = 'MY_CLUSTER';

This may be because your database has a recycle bin. Check this:

select *
  from RECYCLEBIN
 where ORIGINAL_NAME = '<your table>';

Check this link out for more information on the recycle bin.