How to Increase the Size of the Datafile of oracle database

-

-----------------------------------------------------------------------------------------------------------------------------

To increase the size limit of the Datafile, use the following SQL command:

Increasing the Size Limit

ALTER DATABASE DATAFILE '/U01/ORADATA/SANGA.DBF' AUTOEXTEND ON MAXSIZE 4096M;

In this command, we are adjusting the maximum size of the Datafile to 4096MB (4GB). This change ensures that the Datafile can automatically extend its size up to the specified limit when it reaches capacity.

 

Removing the Size Limit

In certain scenarios, you may want to remove the size limit altogether to allow the Datafile to grow without any restrictions. To achieve this, use the following SQL command in Oracle Database Enterprise Manager:

ALTER DATABASE DATAFILE ‘/U01/ORADATA/SANGA.DBF’ AUTOEXTEND ON MAXSIZE UNLIMITED;

With this command, the size limit on the Datafile is set to "UNLIMITED," allowing it to grow dynamically without any predefined restrictions.

 

Important Note:

When removing the size limit by setting it to "UNLIMITED," exercise caution and ensure that your storage infrastructure can accommodate the potential growth of the Datafile. Unrestricted growth may lead to disk space issues if not monitored and managed effectively.

 

 

 

Comments

Database With SanGa said…
please share your comments with this.
October 5, 2023 at 11:34 PM
Post a Comment

Popular posts from this blog

[FATAL] [DBT-10503] Template file is not specified

-
I faced an error [FATAL] [DBT-10503] Template file is not specified and terminated operation when I was going to create 12c instance in silent mode .   ERROR: [FATAL] [DBT-10503] Template file is not specified.   REASON: This is due to the you are not set the “templateName” in response file.   SOLUTION: Set templateName=General_Purpose.dbc and execute the dbca again   ACTION: 1.        Open the dbca.rsp and set templateName as General_Purpose.dbc 2.        Save the dbca.rsp 3.        Execute the instance create statement again (dbca)
Read more

Resolving ORA-10635 and ORA-39171 Errors During BLOB Tablespace Maintenance

-
Recently, I came across an interesting situation while performing a space management task on one of our databases. The challenge involved a table that contained BLOB data distributed across multiple tablespaces . What seemed like a simple shrink operation ended up turning into a deeper learning experience that I’d like to share. 🧩 Background In this environment, the main table BINARY_ARCHIVE was stored in the MAIN_DATA tablespace. Its BLOB segment was placed separately in the ATTACHMENTS_DATA tablespace to improve performance and manage large object storage efficiently. Additionally, another table called BINARY_OBJECT_LINK maintained a foreign key reference to the BLOB data and resided in the LINK_DATA tablespace. When I attempted to reclaim space from the BLOB segment using the SHRINK SPACE command, Oracle returned the following error. ⚠️ The Error ORA -10635 : Invalid segment or tablespace type 10635. 00000 - " Invalid segment or tablespace type " *Ca...
Read more

How to resolve RMAN-06035: wrong version of recover.bsq, expecting 11.2.0.4, found 11.2.0.2

-
In my case there are two oracle homes. so when I schedule a cron job for archive clearing following error was raised. Recovery Manager: Release 11.2.0.4.0 - Production on Wed May 17 13:41:49 2023 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00554: initialization of internal recovery manager package failed RMAN-03000: recovery manager compiler component initialization failed RMAN-06035: wrong version of recover.bsq, expecting 11.2.0.4, found 11.2.0.2 SOLUTION: I have added following line to the oraenv file.  export PATH=$ORACLE_HOME/bin:$PATH
Read more