Everyone of us are familiar with the simple re-org command - alter table (table_name) move. 'Move' command can also help us migrate a table from one tablespace to another. But sometimes you might get and error:
ORA-01652: unable to extend temp segment by 8192 in tablespace (tablespace_name)
For example :
I have a huge table 'employees' which holds 1.5 GB in TS_TIGER. I re-org this table every week as the table undergoes a lot of inserts, updates and deletes. Last time when I ran the 'move' comm it failed:
alter table employees move;
ORA-01652: unable to extend temp segment by 8192 in tablespace TS_TIGER.
TS_TIGER had 1.5 GB of freespace and TEMP tablespace was not utilized either. Then why this error? The reason is Oracle uses temporary extents to build the table initially. Once the process is completed successfully, it converts the temporary segments to permanent one. This is just a dictionary update.
Oracle follows this methodology, in case the re-org fails (due to some error) , SMON would identify the temporary extents and just clean them up. The ORA-01652 error here simply means that the tablespace is short of space and you need to resize a datafile or add a datafile.
No comments:
Post a Comment