Question: I am trying to restore to a backup instance
on a backup server. When I try to recreate the tables I keep getting
a ORA-01659 error message. The tablespaces and
datafiles on both servers show as the same size in OEM.
I have dropped all tables and OEM shows tablespaces are empty. Then
I run a script to recreate all tables. Most of the tables don't get
created because their TS is full. After the script to recreate all
tables runs, the main tablespaces are full, more full than on the
production machine. I have also tried:
ALTER TABLESPACE xxx
COALESCE;
on each tablespace right after dropping all tables and
before recreating them to reclaim free space.
Why is it full?
I?ve
only dropped and created the tables, there shouldn't be any data in
them yet. Here's the error message:
ORA-01659: unable to
allocate MINEXTENTS beyond 2 in tablespace PLUS_TS
The backup instance was already there, all I did was drop the
tables. Here's what I ran on prod to build a script to recreate the
tables on backup server.
SELECT
DBMS_METADATA.GET_DDL('TABLE',u.table_name) ||'; '
FROM USER_TABLES u;
What do you think; how can I fix this?
Answer: To diagnose any error, you start by using the
oerr utility to
fully display the ORA-01659 error:
ORA-01659: unable to
allocate MINEXTENTS beyond string in tablespace string
Cause: Failed to find sufficient contiguous space to allocate
MINEXTENTS for the segment being created.
Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to
the tablespace or retry with smaller value for MINEXTENTS, NEXT or
PCTINCREASE
MINEXTENTS specifies the number of initial extents for the object. Generally,
except for rollback segments, it is set to 1. If a large amount of space is
required and there is not enough contiguous space for the table, setting a
smaller extent size and specifying several extents may solve the problem. The
values for INITIAL, NEXT, and PCTINCREASE are used when calculating the extent
sizes for the number of extents requested. Also, make sure that you define
all tables with "maxextents unlimited" and set tablespaces with "autoextend on":
You need to troubleshoot the ORA-01659 error by KNOWING the fragmentation within
the tablespace! I would try these things:
- Try setting a smaller extent size and specifying several extents for each
table.
- Check all contiguous free space in the tablespace.
- Run a tablespace mapper script to see exactly what is in the tablespace.
- Try removing all storage clauses from the table DDL and use the default
MINEXTENTS for each tablespace
- Try an alter tablespace xxx coalesce; command.
Regarding tablespaces and OEM, OEM is not nearly as powerful or flexible as writing your
own tablespace monitoring scripts.
You need to map the tablespace and see exactly why Oracle thinks that it is
full.
Start with this
handy Oracle free space script.
The query to map a tablespace is complex, but you can download some great
tablespace management scripts with the BC Oracle scripts collection with Oracle
scripts for tuning and monitoring, a professional download of over 600
Oracle scripts.
If your goal is to always prevent these types of errors, I have monitoring
scripts that will alert you when the tablespace is nearly full, and it will send
you an e-mail before you get the ORA-01659 error. These can be found in my
Oracle Tuning book.
