|
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 SASCO_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;
|
