Autoextend Tablespace in Oracle

I will explain Autoextend Tablespace in Oracle in this post.

 

If you want to learn more details about Tablespace or how to create tablespace in Oracle, read the following post.

What is the Tablespace and How to Create Permanent, Undo and Temp Tablespace in Oracle

 

 

 

 

 

Autoextend Tablespace in Oracle

Oracle DBA should use the Autoextend On feauture for tablespaces to set a tablespace to automatically extend itself by a specified amount when it reaches its maximum size limit.  If tablespaces’ autoextend is not enabled, then you are alerted when the tablespace reaches its critical or warning threshold size.

 

 

How to check autoextend on tablespace in oracle

You can check the autoextensible of datafiles using the following script.

select file_name,tablespace_name,maxbytes from dba_data_files where autoextensible='NO';

 

If the autoextensible of datafile is NO, autoextensible feature is not enabled for the related datafile, and datafile will not auto extend.

 

You can check the autoextensible datafiles using the following script.

select file_name,tablespace_name,maxbytes from dba_data_files where autoextensible='YES';

 

You can enable the autoextend for the existing datafiles as follows.

alter database datafile '+DATAC1/MSDB/DATAFILE/myDataTBS.476.928368179' autoextend on maxsize unlimited;

 

 

You can use the following script to Enable all datafiles autoextensible which of autoextensible are not enabled.
select 'alter database datafile '|| file_name|| ' '|| ' autoextend on maxsize unlimited;'
from dba_data_files where autoextensible='NO';

Create tablespace autoextend in Oracle

If you are using file system for Oracle Storage, then you can use it following script.

 

CREATE TABLESPACE NEW_TBS_TEST
DATAFILE
'/oracle/oradata/TEST/NEW_TBS_TEST01.dbf' SIZE 4G AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED,
'/oracle/oradata/TEST/NEW_TBS_TEST02.dbf' SIZE 4G AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

 

If you are using the Oracle ASM , then you can use it following script.

 

CREATE TABLESPACE NEW_TBS_TEST
DATAFILE
'+DATA' SIZE 4G AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;





Add Autoextend datafile to Tablespace

You can add new datafile to the tablespace , If Oracle ASM is used, you can use the following script.

ALTER TABLESPACE NEW_TBS_TEST ADD DATAFILE '+DATA' SIZE 15M AUTOEXTEND ON MAXSIZE UNLIMITED;

 

You can add new datafile to the tablespace , If File System is used, you can use the following script.

ALTER TABLESPACE NEW_TBS_TEST ADD DATAFILE '/oracle/oradata/TEST/NEW_TBS_TEST03.dbf' SIZE 15M AUTOEXTEND ON MAXSIZE UNLIMITED;



Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

 

 1,261 views last month,  2 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply