garden2.jpg
Turn ON and OFF all database data files autoextend


Disclaimer: Apply to a TEST environment first. Use on production systems is at DBAs own risk.

Related ORA errors:
If you get the following errors then this script, if used appropriately, would help you resolve ORA- errors such as the following:
ORA-03232: Unable to allocate an extent of
ORA-1652: Unable to extent TEMP
ORA-01654: Unable to extend index
ORA-01632: Max extents reached in index

Turn ON and OFF data files autoextend:

Syntax sample:

On new datafile:

ALTER TABLESPACE IDBASOL_APPSX ADD DATAFILE '/u15/oradata/testdata/appsx04.dbf' SIZE 500M AUTOEXTEND ON NEXT 500M MAXSIZE 2000M;

On existing datafile:

Alter database datafile '/u16/oradata/testdata/appsd02.dbf' AUTOEXTEND ON NEXT 500 M MAXSIZE 2000 M ;

 

This script is especially useful in an environment like Oracle

applications where you have many tablespaces.

This script turns autoextend on or off for all datafiles

except for datafiles belonging to (’RBS’,’SYSTEM’,’TEMP’).

If the autoextend is being turned on then the NEXT is set to 100 MB

and the MAXSIZE is set to 2000 MB.

”” represent a single ‘ when executed.

p_datafile_set_autoextend.sql

17-AUG-2000

idbasolutions.com

 

First set:

SET SERVEROUTPUT ON SIZE 100000

Run this proceudre from SQL prompt:

EX: exec p_datafile_set_autoextend(’ON’) to turn autoextend ON.

exec p_datafile_set_autoextend(’OFF’) to turn autoextend OFF.

 

 

 

The code for the script:

create or replace procedure
p_datafile_set_autoextend (on_off IN dba_data_files.AUTOEXTENSIBLE%type)
as
v_cursorid integer;
status integer;
cursor c_dba_data_files is
select FILE_NAME
from dba_data_files
where TABLESPACE_NAME not in (’RBS’,’SYSTEM’,’TEMP’)
and STATUS = ‘AVAILABLE’;
v_dba_data_files c_dba_data_files%rowtype;
begin
open c_dba_data_files;
v_cursorid:=dbms_sql.open_cursor;
fetch c_dba_data_files into v_dba_data_files;
if (upper(on_off)=’ON’) then
while ( c_dba_data_files%found ) loop
dbms_sql.parse(v_cursorid,
‘ALTER database datafile ‘|| ””
||v_dba_data_files.FILE_NAME||””||
‘ AUTOEXTEND ON NEXT 100 M MAXSIZE 2000 M ‘,
dbms_sql.native);
status:=dbms_sql.execute(v_cursorid);
dbms_output.put_line(’Datafile: ‘||
v_dba_data_files.FILE_NAME ||
‘ AUTOEXTENT turned on NEXT is 100 M MAXSIZE is 2000 M’);
fetch c_dba_data_files into v_dba_data_files;
end loop;
end if;
if (upper(on_off)=’OFF’) then
while ( c_dba_data_files%found ) loop
dbms_sql.parse(v_cursorid,
‘ALTER database datafile ‘|| ””
||v_dba_data_files.FILE_NAME||””||
‘ AUTOEXTEND OFF ‘,
dbms_sql.native);
status:=dbms_sql.execute(v_cursorid);
dbms_output.put_line(’Datafile: ‘||
v_dba_data_files.FILE_NAME ||
‘ AUTOEXTENT turned off \’);
fetch c_dba_data_files into v_dba_data_files;
end loop;
end if;
close c_dba_data_files;
dbms_sql.close_cursor(v_cursorid);
exception
when others then
dbms_output.put_line(’Error…… ‘);
dbms_sql.close_cursor(v_cursorid);
raise;
end p_datafile_set_autoextend;

 

 

 

Latest Blog Items

email

Thank you for your interest in idbasolutions.

Contact us using this email: contact.us@idbasolutions.com