ORA-01653: невозможно увеличить таблицу SUPERMAG.FFMAPREP до 1024 в разделе USERS : Супермаг Плюс (Супермаг 2000)
При расчете товародвижения выходит следующая ошибка:
ORA-01653: невозможно увеличить таблицу SUPERMAG.FFMAPREP до 1024 в разделе USERS
SQL*Loader-2026: загрузка была прервана, т.к. продолжение выполнения загрузчика SQL невозможно.
Задайте SKIP=5840674, затем продолжайте загрузку.
Были обработаны следующие индексы таблицы SUPERMAG.FFMAPREP:
индекс SUPERMAG.FFMAPREP_ARTICLE не может использоваться из-за:
ORA-01654: невозможно увеличить индекс SUPERMAG.FFMAPREP_ARTICLE до 128 в разделе USERS
индекс SUPERMAG.FFMAPREP_DOC не может использоваться из-за:
ORA-01654: невозможно увеличить индекс SUPERMAG.FFMAPREP_DOC до 128 в разделе USERS
индекс SUPERMAG.FFMAPREP_INCOMEDOC не может использоваться из-за:
ORA-01654: невозможно увеличить индекс SUPERMAG.FFMAPREP_INCOMEDOC до 128 в разделе USERS
индекс SUPERMAG.FFMAPREP_LOCFROM не может использоваться из-за:
ORA-01654: невозможно увеличить индекс SUPERMAG.FFMAPREP_LOCFROM до 128 в разделе USERS
индекс SUPERMAG.FFMAPREP_LOCTO не может использоваться из-за:
ORA-01654: невозможно увеличить индекс SUPERMAG.FFMAPREP_LOCTO до 128 в разделе USERS
индекс SUPERMAG.FFMAPREP_SALEDATE не может использоваться из-за:
ORA-01654: невозможно увеличить индекс SUPERMAG.FFMAPREP_SALEDATE до 128 в разделе USERS
индекс SUPERMAG.FFMAPREP_SUPPLIER не может использоваться из-за:
ORA-01654: невозможно увеличить индекс SUPERMAG.FFMAPREP_SUPPLIER до 128 в разделе USERS
вопрос: я так понимаю что не хватает места в таблице Users. как в этой версии Оракл увеличить таблицу? раньше пользовался DBA Studio в версии Оракл8, а сейчас такого не могу найти (перешли на новую версию, не хочется чего нибудь сотворить по незнанию)
How to Resolve ORA-01653 Unable to Extend Table in Tablespace
ORA-01653
The following two error messages are very similar. As a DBA, you’ll probably meet them someday in the future. ORA-01653 is for table, ORA-01654 is for index. Essentially, they are the same thing:
Error: ORA-01653
Text: unable to extend table %s.%s by %s in tablespace %s
——————————————————————————-
Cause: Failed to allocate an extent for table segment in tablespace.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
files to the tablespace indicated.
Error: ORA-01654
Text: unable to extend index %s.%s by %s in tablespace %s
——————————————————————————-
Cause: Failed to allocate extent for index segment in tablespace.
Action: Use the ALTER TABLESPACE ADD DATAFILE statement to add one or more
files to the specified tablespace.
When you are notified by ORA-01653, ORA-01654, don’t rush to follow «Action» to add a datafile, because it might not need to add. These errors has the same fact that the database failed to allocate a consecutive space for the segment to use.
Check Space Usage
Let’s check the related status before thinking alternatives beside adding a datafile.
When Tablespace is Not Full
If the tablespace is sufficient enough for users to operate, the errors are originated by fragmented data, database cannot allocate a large enough extent for the segment to use. Then, you can try to collect pieces of space into a consecutive one like the following actions:
1. Coalesce
It’s to coalesce adjacent free consecutive space to a bigger one. This is the fastest way to solve, but, there are not always have adjacent extents to coalesce. So the statement will succeed, but it may not help a lot. SQL> ALTER TABLESPACE TABLESPACE_NAME COALESCE;
2. Purge the recycle bin
There could be some dropped segments still occupy the space, so we check them first:
SQL> SELECT * FROM dba_recyclebin WHERE ts_name=’TABLESPACE_NAME’;
If any segment found and you are sure that they never come back, then purge them:
SQL> PURGE TABLE BIN$zFFO8kJsJQDgQKjACwBunA==$0;
Or purge with the same table name
SQL> PURGE TABLE tablename;
Or purge the whole tablespace
SQL> PURGE TABLESPACE TABLESPACE_NAME;
3. Defragmentation
It’s to reorganize the free space from pieces to a whole and very helpful when the tablespace still has enough space. The defragmentation may take a long time to reach a satisfying degree. In urgent situations, you should try the next option.
4. Enlarge NEXT extent size
Sometimes, 10MB or so of next extend size may not be large enough for big transactions to use of, you may need to raise the value to, e.g. 1024M (1G) for the size of AUTOEXTEND ON NEXT syntax on the data files in that tablespace. It’s a mitigation strategy.
When Tablespace is Nearly Full
If the tablespace is nearly full, then try the following actions:
1. Drop Unused Tables for Good
Drop the garbage definitely: When time goes by, there’re could be some garbage segments created by users on the tablespace, you can compare the segments with the production list on Configuration Management System to remove the unlisted tables out of the tablespace.
SQL> DROP TABLE TABLE_NAME PURGE;
2. Move Segments to Another Tablespace
Move some segments to other tablspace: If time is limited, move the smaller segments first, then the larger segments. (This is also one of defragmentation techniques)
SQL> ALTER TABLE tablename MOVE TABLESPACE another_tablespace;
3. Resize Datafile
Resizing is very convenient for a DBA to operate. For 8i DBA, add a datafile would involve more works to do on standby database, so resizing should be considered before adding.
SQL> ALTER DATABASE DATAFILE ‘/path_to_datafile/datafilename.dbf’ RESIZE 10G;
4. Enable AUTOEXTEND
If the server space is overall limited, you should put MAXSIZE to a proper value to secure the overall space.
SQL> ALTER DATABASE DATAFILE ‘/path_to_datafile/datafilename.dbf’ AUTOEXTEND ON MAXSIZE 10G;
5. Add a Data File
If the overall space has high capacity, you can go directly to this step. But sometimes adding a datafile becomes a troublesome and an error prone process if involving raw devices.
SQL> ALTER TABLESPACE tablespacename ADD DATAFILE ‘/path_to_datafile/datafilename.dbf’ SIZE 8G;
However, as a DBA, you’ll probably face other demanding environments in someday, to know all the solutions would be helpful.
Considerations about Adding Data Files
Some considerations about adding a datafile and resizeing:
Conclusion
My suggestion is to distinguish the scenario first, then take the proper actions subsequently.
How do I avoid «ORA-01653: unable to extend table SYS.AUD$ or FGA_LOG$ in tablespace SYSTEM» errors when using Amazon RDS for Oracle?
Last updated: 2020-06-24
I enabled the Oracle audit feature on my Amazon Relational Database Service (Amazon RDS) database to log events on the database audit tables. But the SYSTEM tablespace is growing more than I expected, or I received the following error message:
«ORA-01653: unable to extend table. » for SYS.AUD$ or FGA_LOG$ in the tablespace SYSTEM.»
How do I resolve this error?
Short description
The ORA-01653 error is caused by a lack of free space on the SYS.AUD table in the SYSTEM tablespace. This is where the Oracle audit tables AUD$ and FGA_LOG$ are defined, by default. The error occurs when the table attempts to auto-extend to accommodate additional data, but the table is prevented from expanding. For more information about the auditing actions that can cause full tablespace, see the Oracle documentation for configuring and administering auditing.
Note: AUDIT_SYS_OPERATIONS is enabled by default on Amazon RDS for Oracle.
Resolution
To resolve this issue, consider one or more of the following solutions:
Migrate the AUDIT tables to a dedicated tablespace
Audit tables grow indefinitely until either a manual or automated purging routine using DELETE statements cleans up the records. Because free space is reclaimed only to the affected segment, purging tables in the SYSTEM tablespace results in no functional change to the size of the AUD$ and FGA_LOG$ tables. This can cause fragmentation issues.
Migrating AUDIT tables from the SYSTEM tablespace to a different tablespace allows you more control over the size of your RDS DB instance’s audit tables. You can migrate the tables yourself, or if you’re running Oracle version 11g R2 or above on your RDS DB instance, you can use the DBMS_AUDIT_MGMT package.
To migrate the table manually, follow these steps:
1. Create a new tablespace by running a command similar to the following:
Note: Creating a tablespace with AUTOEXTENSIBLE segment space management (enabled by default for new tablespaces) allows you to easily resize the table later.
2. Migrate your AUD$ table to the new tablespace by running commands similar to the following:
3. Migrate your FGA_LOG$ table to the new tablespace by running commands similar to the following:
Note: Depending on the size of your table, this operation can take time to complete. If you have large or frequently accessed tables, you might also encounter deadlock errors. To resolve deadlock errors, temporarily set DB_AUDIT_TRAIL to NONE, restart the database, and try the operations again. You can revert DB_AUDIT_TRAIL after the process completes.
Follow best practices for auditing tables
To maintain manageable audit tables, mitigate the space consumed by the audit tables. Also, minimize the occurrence of the ORA-01653 error by auditing the minimum set of pertinent actions. Finally, archive and purge audit trail records on a regular basis. For more information, see the guidelines for auditing typical database activity in the Oracle Database documentation.
Turn AUTOEXTEND on for the SYSTEM tablespace
For RDS DB instances that run Oracle, AUTOEXTEND for the SYSTEM tablespace is set to ON by default. If you turn AUTOEXTEND off manually, then your database’s ability to provision additional space for the SYSTEM tablespace can be affected. You can enable this setting for the SYSTEM tablespace by running the ALTER TABLESPACE command:
Set MAXSIZE for the SYSTEM tablespace to a larger value
If the SYSTEM tablespace settings for MAXBYTES or MAXBLOCKS are set too low, set MAXSIZE for the SYSTEM tablespace to a larger value.
You can also remove limits on the maximum size of the SYSTEM tablespace by running a command similar to the following:
