Sql error ora 01653

ORA-01653: Unable to Extend Table APPLSYS.WF_DEFERRED Error when Trying to Refer Applicant (Doc ID 1449649.1) Last updated on JANUARY 30, 2022 Applies to: Symptoms When attempting to refer an applicant, the following error occurs: ERROR————————Error PageYou have encountered an unexpected error. Please contact your System Administrator for assistance. Click here for exception details. oracle.apps.fnd.framework.OAException: […]

Содержание

  1. ORA-01653: Unable to Extend Table APPLSYS.WF_DEFERRED Error when Trying to Refer Applicant (Doc ID 1449649.1)
  2. Applies to:
  3. Symptoms
  4. Changes
  5. Cause
  6. To view full details, sign in with your My Oracle Support account.
  7. Don’t have a My Oracle Support account? Click to get started!
  8. How to Resolve ORA-01653 Unable to Extend Table in Tablespace
  9. ORA-01653
  10. Check Space Usage
  11. When Tablespace is Not Full
  12. Coalesce
  13. Purge the recycle bin
  14. Defragmentation
  15. Enlarge NEXT extent size
  16. When Tablespace is Nearly Full
  17. Drop Unused Tables for Good
  18. Move Segments to Another Tablespace
  19. Resize Datafile
  20. Enable AUTOEXTEND
  21. Add a Data File
  22. Considerations about Adding Data Files
  23. Conclusion
  24. ORA-01653: unable to extend table SYS.AUD$ by 8192 in tablespace SYSTEM
  25. Sql error ora 01653
  26. About Starting Oracle Database in Upgrade Mode
  27. Running DBUA with Different ORACLE_HOME Owner

ORA-01653: Unable to Extend Table APPLSYS.WF_DEFERRED Error when Trying to Refer Applicant (Doc ID 1449649.1)

Last updated on JANUARY 30, 2022

Applies to:

Symptoms

When attempting to refer an applicant, the following error occurs:

ERROR
————————
Error Page
You have encountered an unexpected error. Please contact your System Administrator for assistance. Click here for exception details.

oracle.apps.fnd.framework.OAException: java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table APPLSYS.WF_DEFERRED by 16 in tablespace APPS_TS_QUEUES
ORA-06512: at «APPS.WF_NOTIFICATION», line 4054
ORA-06512: at «APPS.IRC_NOTIFICATION_HELPER_PKG», line 155
ORA-06512: at «APPS.IRC_NOTIFICATION_HELPER_PKG», line 302
ORA-06512: at line 1

at oracle.apps.fnd.framework.OAException.wrapperInvocationTargetException(OAException.java:996)
at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(OAUtility.java:211)
at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(OAUtility.java:153)
(..)

## Detail 0 ##
java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table APPLSYS.WF_DEFERRED by 16 in tablespace APPS_TS_QUEUES
ORA-06512: at «APPS.WF_NOTIFICATION», line 4054
ORA-06512: at «APPS.IRC_NOTIFICATION_HELPER_PKG», line 155
ORA-06512: at «APPS.IRC_NOTIFICATION_HELPER_PKG», line 302
ORA-06512: at line 1

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
(..)

STEPS
————————
The issue can be reproduced at will with the following steps:
1. Responsibility: iRecruitment Recruiter.
2. Navigation: iRecruitment Home — Vacancies tab.
3. Search for vacancy.
4. Select vacancy.
5. Press View Applicants button.
6. Select applicant.
7. Press Refer button.
8. Fill in details.
9. Press Send button.
10. Get error message.

BUSINESS IMPACT
————————
The issue has the following business impact:
Due to this issue, users cannot refer applicants to other recruiters or managers.

Changes

Cause

To view full details, sign in with your My Oracle Support account.

Don’t have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

Источник

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.

  1. Check the tablespace space usage to see whether the space is really full or not. SQL> SELECT tablespace_name, SUM(BYTES)/1024/1024 «Free Space (MB)» FROM dba_free_space GROUP BY tablespace_name;
  2. Check the segment NEXT_EXTENT and PCT_INCREASE to see how much space of an extent will need by the segment: SQL> SELECT next_extent, pct_increase FROM dba_segments WHERE segment_name=’TABLENAME’ AND segment_type =’TABLE’ AND owner =’USER’;

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:

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;

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;

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.

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:

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;

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;

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;

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;

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:

  1. If there is any standby database, make sure there is enough space at the same path on standby database before adding a datafile.
  2. As I mentioned above, for 8i, after adding a datafile, a DBA have extra works to do, he should create a new standby controlfile for standby database and copy the new datafile to the right path on standby server. Then, mount the standby database with the new standby controlfile.
  3. If the raw devices are under datafiles directly, you should make symbolic links carefully to point to available raw devices both on primary and standby server on OS-level before adding a datafile on database-level, if any symbolic link is missing or two symbolic links point to one raw device, someone will be in trouble.
  4. Resizing a datafile may not be an ideal solution when it links to a raw device, it depends on the room between the raw device size and the datafile definition. In real world, we usually define the size of a datafile with a little less than the size of the raw device, so there is no room to resize.

Conclusion

My suggestion is to distinguish the scenario first, then take the proper actions subsequently.

Источник

ORA-01653: unable to extend table SYS.AUD$ by 8192 in tablespace SYSTEM

When using sqlplus user/password@truth to login database, this error occurred:

ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS.AUD$ by 8192 in tablespace SYSTEM
ORA-02002: error while writing to audit trail
ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS.AUD$ by 8192 in tablespace SYSTEM

ORA-00604: error occurred at recursive SQL level 1 means internal SQL statement executed failed
ORA-01653: unable to extend table SYS.AUD$ by 8192 in tablespace SYSTEM means the table space is full

Login database with sqlplus / as sysdba
First, using the following SQL statement to check table space

select tablespace_name,(bytes/1024/1024) M from dba_data_files;

Two methods to deal with the table space is full.
(1) change the system tablespace data file SYSTEM.dbf to enlarge space

alter database datafile ‘/u04/oradata/truth/system01.dbf’ resize 5524M;

(2) add another data file for the system table space
(3) mover the tables in system table space to non system tablespace

Check whether there are other non system tables in the system tablespace,
If any, move to a non system tablespace

alter table move tablespace tablespace_name

Oracle 11g database startup audit function by default, leading to sys.aud$ table and related index increasing;
If the tablespace is full, ORA-1654: unable to extend index SYS. error will be reported in the alert log.
If you do not need audit function, it is recommended to close it.
Process flows:
1, with Oracle user to login database server, execute:

alter system set audit_trail=none scope=spfile;

3, and then restart the database.

shutdown immediate;
sqlplus / as sysdba
startup;

4, delete the audit data:

sqlplus / as sysdba
truncate table SYS.AUD$;

Posted by iibull in Oracle at Feb 25, 2017 — 6:49 PM

Источник

Sql error ora 01653

Use these troubleshooting tips to address errors or issues that you may encounter while upgrading your database.

Also review the related links to learn about changes that affect this release, which may be related to errors you receive, and to see how to rerun the upgrade after you resolve errors.

  • About Starting Oracle Database in Upgrade Mode
    When you start Oracle Database in upgrade mode, you can only run queries on fixed views. If you attempt to run other views or PL/SQL, then you receive the errors described here.
  • Running DBUA with Different ORACLE_HOME Owner
    Review this topic if your Oracle Database homes are owned by different operating system user accounts, or you encounter an upgrade.xml not found error.
  • Invalid Object Warnings and DBA Registry Errors
    Before you start your upgrade, Oracle strongly recommends that you run the preupgrade information tool ( preupgrd.jar ).
  • Invalid Objects and Premature Use of Postupgrade Tool
    Never run the postupgrade status tool for the new Oracle Database release ( utlu122s.sql ) until after you complete the upgrade.
  • Resolving Oracle Database Upgrade Script Termination Errors
    Review this section if you encounter ORA-00942, ORA-00904, or ORA-01722 errors.
  • Troubleshooting Causes of Resource Limits Errors while Upgrading Oracle Database
    Review this section if you encounter ORA-01650, ORA-01651, ORA-01652, ORA-01653, ORA-01654, ORA-01655, ORA-0431, ORA-01562, ORA-19815, or other errors that suggest resource limit errors.
  • Resolving SQL*Plus Edition Session Startup Error for Oracle Database
    Use this section to understand and resolve SP2–1540: «Oracle Database cannot startup in an Edition session.»
  • Error ORA-00020 Maximum Number of Processes Exceeded When Running utlrp.sql
    This error may indicate that your Oracle configuration does not have sufficient number of processes available for the recompile.
  • Fixing ORA-01822 with DBMS_DST Package After Upgrades
    Use this procedure to fix ORA-01822 and ORA-16512 errors involving time zone file mismatches.
  • Fixing ORA-28365: Wallet Is Not Open Error
    If you use Oracle wallet with Transparent Data Encryption (TDE), and you use Database Upgrade Assistant (DBUA) to upgrade the database, then you can encounter an ORA-28365 «wallet is not open» error.
  • Resolving issues with view CDB_JAVA_POLICY
    If the view CDB_JAVA_POLICY becomes invalid, then use this procedure.
  • Continuing Upgrades After Server Restarts (ADVM/ACFS Driver Error)
    On Windows platforms, an error may occur related to ADVM or ACFS drivers if a server restarts during an upgrade.
  • Component Status and Upgrades
    Component status settings are affected both by the components that you previously installed, and by the support of those components for upgrades.
  • Standard Edition Starter Database and Components with Status OPTION OFF
    Starting in Oracle Database 18c (18.1), all OPTION OFF components are upgraded to the new release, but these options are disabled for Oracle Database Standard Edition (SE) remain OPTION OFF.
  • Adjusting Oracle ASM Password File Location After Upgrade
    You must create a new password file for Oracle ASM after an Oracle Grid Infrastructure upgrade.
  • Fixing «Warning XDB Now Invalid» Errors with Pluggable Database Upgrades
    Review this topic if you encounter «Warning: XDB now invalid, invalid objects found” errors when upgrading pluggable databases (PDBs).
  • Fixing ORA-27248: sys.dra_reevaluate_open_failures is running
    Use this procedure to identify DRA_REEVALUATE_OPEN_FAILURES jobs that block upgrades.
  • Fixing ORA-22288: File or LOB Operation FILEOPEN Failed Soft Link in Path
    ORA-22288 occurs when symbolic links are in directory object paths or filenames when opening BFILES.
  • Fixing Oracle Database Enterprise User Security, OLS-OID, and Provisioning Profile Error
    Review to understand ORA-16000: database open for read-only access errors.
  • Fixing 32K Migration Error with utl32k.sql and MAX_STRING_SIZE
    Use this procedure to fix ORA-01722: invalid number upgrade errors.
  • Recovering from a CRS Shutdown and Oracle ASM Losing Rolling Migration
    A Cluster Ready Services (CRS) shutdown on all cluster member nodes can place the cluster in a heterogeneous state. Use this procedure to recover from that problem.
  • Data Type Versioning Can Cause Cross-Version Replication (ORA-26656)
    Review the user-defined object types affected by versioning.
  • Referenced Symbol Count is Undefined Error libclntsh.so.11.1
    Review this topic if you encounter errors that reference libclntsh.so.11.1 with «referenced symbol count is undefined», «cannot open shared object file», or similar errors.
  • Resolving Timestamp Errors Due to ISO 8601 Timestamps
    Review this topic if you encounter timestamp errors with applications after upgrading to Oracle Database 12 c , 18c, or later releases.
  • Fixing Failed Upgrades Where Only Datapatch Fails
    If only datapatch fails during an upgrade, then rerun datapatch directly.
  • Error «Timezone Datafiles May Not Be Downgraded To a Lower Version»
    You encounter a time zone error that prohibits the upgrade if the daylight savings time (DST) version in the database you are upgrading is newer than the DST in the database release to which you are upgrading.
  • Fixing Failures to Complete Registration of Listeners with DBUA
    On the Database Upgrade Assistant Progress step, a window appears with this warning: «Unable to create database entry in the directory service. No Listeners configured.»

Related Topics

About Starting Oracle Database in Upgrade Mode

When you start Oracle Database in upgrade mode, you can only run queries on fixed views. If you attempt to run other views or PL/SQL, then you receive the errors described here.

When the database is started in upgrade mode, only queries on fixed views execute without errors until after you run the Parallel Upgrade Utility ( catctl.pl directly, or with the dbupgrade script). Before running an upgrade script, queries on any other view or the use of PL/SQL returns an error.

Errors described in this section can occur when you attempt to start the new Oracle Database release. Some of these errors write to the alert log, and not to your session. If you receive any of these errors, then issue the SHUTDOWN ABORT command to shut down the database and correct the problem.

ORA-00401: the value for parameter compatible is not supported by this release

The COMPATIBLE initialization parameter is set to a value less than 11.2.0 .

ORA-39701: database must be mounted EXCLUSIVE for UPGRADE or DOWNGRADE

The CLUSTER_DATABASE initialization parameter is set to TRUE instead of FALSE .

ORA-39700: database must be opened with UPGRADE option

The STARTUP command was issued without the UPGRADE keyword.

Ora-00704: bootstrap failure

The path variable can be pointing to the earlier release Oracle home.

ORA-00336: log file size xxxx blocks is less than minimum 8192 blocks

A redo log file size is less than 4 MB.

If errors appear listing desupported initialization parameters, then make a note of the desupported initialization parameters and continue with the upgrade. Remove the desupported initialization parameters the next time you shut down the database.

Related Topics

Running DBUA with Different ORACLE_HOME Owner

Review this topic if your Oracle Database homes are owned by different operating system user accounts, or you encounter an upgrade.xml not found error.

DBUA upgrades by default assume that both the source (release earlier than Oracle Database 12 c ) Oracle home and the destination (new 12 c ) Oracle home are owned by the same user. If each Oracle home is not owned by the same user, then you must change to database file permissions and pass additional parameters to DBUA. If you do not do this, then during upgrade, the DBUA Prerequisite Checks page reports upgrade.xml not found errors. You are not permitted to proceed with the upgrade until this error is corrected.

All Oracle Database installation owners should have the group that you designate as the OINSTALL group (or Oracle Inventory group) as their primary group. Ensure all database files (data files, the redo file, control files, archive log destination, recovery area, SPFILE, and password file) are readable and writable by both the new 12 c and pre-12 c binary owners. If this is not the case, then confirm that each installation owner has the same group as their primary group, and ensure that members of the OINSTALL group have read/write access to all the earlier release and later release Oracle Database files and directories.

Run DBUA by specifying the -logdir command line option, and provide a directory to which both the new release and earlier release binary owners can write. For example: /tmp . DBUA uses the directory you designate with the logdir parameter to store the output from the Pre-upgrade Information Tool, and to store any DBUA log files generated during the upgrade. You run the Pre-Upgrade Information tool from the earlier release Oracle Database instance as the earlier release Oracle Database installation owner user account.

Источник

ORA-01653: unable to extend table <SCHEMA_NAME>.<SEGMENT_NAME> by 8192 in tablespace <TABLESPACE_NAME>

This error is quite common and it clearly means oracle was not able to allocate space to a segment and returned this error. Alert log file will show
error similar to the following.

Wed May 31 17:15:28 2013

ORA-1653: unable to extend table SCOTT.TEST by 8192 in tablespace DATA_TBS

In this error message, “by 8192” means that a try of allocating 8192 blocks was made which failed. Actual size of the extent can be calculated by multiplying the number of blocks with the block size – if block size is 8192 (8k), the extent size would be 8192×8192 which is 67108864 bytes or 64 MB. If ALLOCATION_TYPE for the locally managed tablespace was AUTO (SYSTEM), this could be “by 1024” or “by 8” or could also be some other value; because it is internally decided by Oracle on how many blocks should be used to allocate next extent.

As stated above, mostly this error comes when tablespace is full and when DBAs see this error, they immediately add space to the tablespace (by adding a new datafile or extending existing datafiles), and problem is resolved most of the time.

Contiguous Space
It would be pertinent to mention that oracle needs contiguous blocks to allocate an extent. For example, if oracle needs to allocate an extent of 64 MB by allocating 8192 blocks (8K block size), these 8192 blocks should be contiguous.  

Dictionary Managed versus Locally Managed
After introduction of locally managed tablespaces, fragmentation is no longer an issue for the DBAs, but if someone is still using dictionary managed tablespaces, he can still face ORA-01653 because of fragmentation which may cause unavailability of contiguous free space in the datafile(s) to be used for new extents allocation and it would mean that ORA-01653 can still some even if you have a lot of free space available in a dictionary managed tablespace datafiles.

A Personal Experience

I would like to share my personal experience here where I faced same error for one of my tables where table size was above 400GB, tablespace (locally managed) size was around 1.5TB and free space in the tablespace was around 10GB. I was perplexed that why oracle was not able to allocate a chunk of 64 MB for my table regardless of still having 10GB free space in my tablespace. After investigation, I found that there were more than 100 datafiles in this tablespace; out of which many were 100% full and rest of them had less than 64 MB space available in them, but total free space added together in all datafiles was around 10G. So this made me realize that even if our tablespace has a lot of datafiles in it, we should make sure that there is always a few GB space available in at least one the datafiles  (by extending datafile, or adding new datafile) where large chunks of contiguous blocks remain available to fulfill the large extent allocation requirement.


Checking Contiguous Space

We can query DBA_FREE_SPACE to find out the free contiguous
space available in the datafiles of a tablespace. For example, I have 3 chunks
of contiguous space available in my TESTTBS which should be sufficient for me
to avoid ORA-01653 because of unavailability of contiguous free space chunk.
First query returns all files free available contiguous space. Second query
shows if I have at least one file in TESTDBS which has free contiguous space
less than 500 MB. Third query will show how many total chunks of what size I have per datafile.

SQL> select file_id,blocks,bytes/1024/1024 from
dba_free_space where tablespace_name=’TESTTBS’;

  
FILE_ID     BLOCKS
BYTES/1024/1024

———- ———- —————

        
5      16256             127

        
5      58264        455.1875

        
5        768               6

SQL> select file_id,max(bytes/1024/1024) from
dba_free_space where tablespace_name=’TESTTBS’ having max(bytes/1024/1024)
< 500  group by file_id;

   FILE_ID
MAX(BYTES/1024/1024)

———- ———————

        
5             455.1875



SQL> select file_id,round(bytes/1024/1024,2) «Free Chunk Size MB»,count(*) «Total Chunks» from dba_free_space where tablespace_name=’TESTTBS’ group by file_id,bytes order by 2;

Popular Posts — All Times

  • This error means that you are trying to perform some operation in the database which requires encryption wallet to be open, but wallet is …

  • Finding space usage of tablespaces and database is what many DBAs want to find. In this article I will explain how to find out space usage …

  • ORA-01653: unable to extend table <SCHEMA_NAME>.<SEGMENT_NAME> by 8192 in tablespace <TABLESPACE_NAME> This error is q…

  • You may also want to see this article about the ORA-12899 which is returned if a value larger than column’s width is inserted in the col…

  • This document explains how to start and stop an Oracle cluster. To start and stop Grid Infrastructure services for a standalone insta…

  • If you want to know how we upgrade an 11g database to 12c using DBUA,   click here .  For upgrading 12.1.0.1 to 12.1.0.2 using DBUA,   …

  • If database server CPU usage is showing 100%, or high 90%, DBA needs to find out which session is hogging the CPU(s) and take appropriate …

  • By default AWR snapshot interval is set to 60 minutes and retention of snapshots is set to 8 days. For better and precise investigation of…

  • SWAP space recommendation from Oracle corp. for Oracle 11g Release 2 If RAM is between 1 GB and 2 GB, SAWP should be 1.5 times the s…

  • This article explains how to install a 2 nodes Oracle 12cR1 Real Application Cluster (RAC) on Oracle Linux 7. I did this installation on O…

Space management used to be hard

Way back in the good old days…In fact, before I continue, rest assured that whenever a database person says “good old days” what they really mean is a time totally warped by their imagination where they have managed to block out all of the horror stories and problems they faced, to only remember the tiny nugget of positivity about some antiquated version of the database, so that they come to you and say … “Way back in the good old days” Smile. But I digress…

Way back in the good old days, things were actually not anywhere near as good as things are today when it came to tablespaces and datafiles. In the early versions of Oracle 7, you picked a size for your datafiles and that was pretty much it. If your tablespaces got close to full, you could manually resize the datafiles or add new ones, but that was 100% your job to keep an eye on things. As a consequence, every DBA of that generation had a script called something along the lines of “freespace.sql” which rang the alarm bell whenever a tablespace got above 80% full, because you didn’t want to be that person that forgot to allocate space in your database before going home for the evening.

Autoextend and automatic space management

Eventually we got autoextend datafiles which meant that as long as our datafiles were not close to their absolute limit, we had a much greater chance of getting an uninterrupted nights sleep! But even autoextend had its issues if it was the crutch that you were leaning on in terms of ensuring consistent application performance.

It obviously takes time to add tens or hundreds of a megabytes to a file, and thus, if your single row transaction was the unfortunate soul that took the datafile over its current size threshold, then you would sit there for precious seconds whilst your datafile grew to its new size.

At the time, this was often the cause of bluster and bravado from DBAs in the industry proudly beating their chest with: REAL admins do not need to autoextend. We just manage our databases properly”. Of course, this was complete tosh because not using the features available to you is less proper management and more revealing yourself to be a fool Smile 

Many Oracle practitioners still worry about this pause in throughput as a file needs to autoextend, but only because there was never not a lot of publicity around a feature that arrived in Oracle 9, namely the SMC0 process (Space Management Coordinator). It did precisely what DBAs were doing manually with their own scripts, namely,

  • keep an eye on the rate of growth of segments in the database,
  • based on that rate, initiate an increase the size of datafiles before they truly needed to autoextend.

If you’re unfamiliar with the SMC0 process, MOS Doc ID 743773.1 is good reference, or check the docs.

Empty tables are not always empty

But enough background…Even with autoextend, manual checks, and SMC0, if you have bursty or sporadic growth, there may come a moment when a datafile cannot grow any further and you hit the familiar “ORA-01653: unable to extend table .. in tablespace…” error when you were not expecting it. If you are in the situation where you have bursts of growth, it is important to realise that once you get one occurrence of ORA-01653, you might continue to get ORA-01653 errors, even if the transaction iniated the growth fails and rolls back.

Lets look at an example of this. I’ll create a 1GB tablespace and for the sake of covering the autoextending case as well, you can assume it could just as easily have started as a 500MB file and has since autoextended out to its maximum size of 1GB.


SQL> create tablespace demo datafile 'X:ORACLEORADATADB19PDB1DEMO.DBF' size 1000m;

Tablespace created.


SQL> create table t ( pk int, c varchar2(4000) ) tablespace demo  ;

Table created.

Now I am going to load up this table T with far too much data than the tablespace can accommodate.


SQL> insert /*+ APPEND*/ into t
  2  select rownum, rpad(rownum,4000)
  3  from dual
  4  connect by level <= 1000000;
insert /*+ APPEND*/ into t
*
ERROR at line 1:
ORA-01653: unable to extend table MISTER.T by 1024 in tablespace DEMO

So my table T failed to load any data, and thus is still empty. But let us now look at the state of the tablespace.


SQL> @free DEMO

TABLESPACE_NAME          TOT_SIZE                                 TOT_FREE               PCT_USED
------------------------ ---------------------------------------- -------------------- ----------
DEMO                          1000m                                       7m                   99

1 row selected.

It’s space is still completely exhausted. By looking at DBA_EXTENTS you can see why this is the case.


SQL> select count(*)
  2  from   dba_extents
  3  where  segment_name = 'T'
  4  and    owner = user;

  COUNT(*)
----------
       195

The table T, which failed to load any rows, still allocated extents as the load progressed. Those allocations are performed by recursive dictionary transactions, which you can think of as being synonymous with autonomous transactions in PL/SQL. They run “separately” from the main loading transaction and thus are not rolled back when the transaction fails. My table T is empty, but it is still 1GB in size. We can safely assume that any activity that requires space in this tablespace is now going to fail as well.

Since we were loading table T from an empty state, there is a simple fix here. I simply truncate the table to release all of its extents back to the free space for the tablespace.


SQL> truncate table t;

Table truncated.

SQL> @free DEMO

TABLESPACE_NAME          TOT_SIZE                                 TOT_FREE               PCT_USED
------------------------ ---------------------------------------- -------------------- ----------
DEMO                          1000m                                     999m                    0

1 row selected.

When truncate cannot be used

But what if the table was not empty before starting the failed load transaction? For example, what if I had already successfully loaded 100 rows into the table as below:


SQL> insert /*+ APPEND*/ into t
  2  select rownum, rpad(rownum,4000)
  3  from dual
  4  connect by level <= 100;

100 rows created.

SQL> commit;

Commit complete.

and then I’d attemped a much bigger load and hit the error.


SQL> insert /*+ APPEND*/ into t
  2  select rownum, rpad(rownum,4000)
  3  from dual
  4  connect by level <= 1000000;
insert /*+ APPEND*/ into t
*
ERROR at line 1:
ORA-01653: unable to extend table MISTER.T by 1024 in tablespace DEMO

Obviously a truncate command is off the table (… see what I did there? Smile). Do I need to perform a complete reorganisation of the table in order to reclaim the space? Fortunately not. A command that has long been available in the Oracle Database but often forgotten by DBAs is that we can prune extents from a table if those extents contain no data. The DEALLOCATE UNUSED will free up those extents that do not contain any data whilst not touching any extents that are in “genuine” use.


SQL> alter table t deallocate unused;

Table altered.

My table data is preserved, and I’ve now freed those extents back to the tablespace.


SQL> select count(*) from t;

  COUNT(*)
----------
       100

1 row selected.

SQL> @free DEMO

TABLESPACE_NAME          TOT_SIZE                                 TOT_FREE               PCT_USED
------------------------ ---------------------------------------- -------------------- ----------
DEMO                          1000m                                     998m                    0

1 row selected.

TL;DR: If you are doing data loads in bursts and hitting the ORA-01653 error, you might be able to reclaim space without expensive reorganisations by using the ALTER TABLE DEALLOCATE UNUSED command.

Here’s some related information from a video session I did on AUTOEXTEND

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:

CREATE TABLESPACE <name>;

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:

BEGIN
SYS.DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => '<name>');
END;
/

3.    Migrate your FGA_LOG$ table to the new tablespace by running commands similar to the following:

BEGIN
SYS.DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, audit_trail_location_value => '<name>');
END;
/

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:

ALTER TABLESPACE SYSTEM AUTOEXTEND ON;

For more information, see Creating and sizing tablespaces.

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:

ALTER TABLESPACE SYSTEM AUTOEXTEND ON MAXSIZE UNLIMITED;


Did this article help?


Do you need billing or technical support?

AWS support for Internet Explorer ends on 07/31/2022. Supported browsers are Chrome, Firefox, Edge, and Safari.
Learn more »

Понравилась статья? Поделить с друзьями:
  • Sql error ora 00942 table or view does not exist
  • Sql error ora 00936 missing expression
  • Sql error ora 00933 неверное завершение sql предложения 00933 00000 sql command not properly ended
  • Sql error ora 00917 missing comma
  • Sql error ora 00907 missing right parenthesis