0

I have a Oracle 11g database with block size = 8192. So, if I'm correct maximum datafile size will be 32GB.

I have a huge table containing around 10 million records. Data in this table will be purged often. For purging we chose CTAS as a better option as we are going to delete greater portion of the data.

As we'll be dropping the old table after CTAS, the old tables are not releasing the space for new tables. I understand that a tablespace has AUTOEXTEND option but no AUTOSHRINK. But the space occupied by old tables should be available for new tables, which is not happening in this case.

I'm getting an Exception saying

ORA-01652: unable to extend temp segment by 8192 in tablespace

FYI the only operation happening all the time is CTAS + Dropping the old table. Nothing else. First time this is working fine, but when the same operation is done the second time, exception arises.

I tried adding an additional datafile to the tablespace, but after few more purge operations on the table, this is also getting full to 32GB and the issue continues.

Shiva Mothkuri
  • 307
  • 3
  • 12
  • 1
    How are you dropping the old table? Specifically, are you supplying the `purge` option? Otherwise it will hang around in [the recycle bin](http://docs.oracle.com/cd/E24693_01/server.11203/e17120/tables011.htm) until you purge that. Does `show recyclebin` (or querying `user_recyclebin`) show lots of objects with original name matching your dropped tables? – Alex Poole Feb 09 '16 at 19:04
  • I'm using the 'purge' option while dropping and there are no records in the recycle bin matching the dropped tables as I was using purge option while dropping. – Shiva Mothkuri Feb 10 '16 at 06:49
  • The tablespace is the same where my table is residing. Before doing the purge operation it's size was just 13GB. After 1st purge, it's almost getting doubled. The second time, it's throwing Error as it can't cross the 32 GB limit. – Shiva Mothkuri Feb 10 '16 at 06:51
  • Have you looked in dba_segments to see what (if anything) is using the space, and dba_free_space to see what that thnks? How much space do the original and new CTAS table actually use? – Alex Poole Feb 10 '16 at 08:09

0 Answers0