Automatically add datafile to a tablespace running out of space
This post is a modified version of previous post by using AFTER SUSPEND trigger to automatically add new datafile to a tablespace that is running out of space.
========================================================
Note: 1/3/11
The trigger didn't work at all, even though it compiled without any errors, but the procedure version worked well.
========================================================
CREATE OR REPLACE TRIGGER auto_add_datafile
AFTER SUSPEND
ON DATABASE
DECLARE
CRLF CHAR (2) := CHR (10) || CHR (39);
tbs VARCHAR2 (50);
datafileCount NUMBER;
addDatafileNo NUMBER;
sql_stmt VARCHAR2 (100);
fileLoc VARCHAR2 (50);
ERROR_TYPE VARCHAR2 (50);
object_type VARCHAR2 (50);
object_owner VARCHAR2 (50);
table_space_name VARCHAR2 (50);
object_name VARCHAR2 (50);
sub_object_name VARCHAR2 (50);
db_file_name VARCHAR2 (100);
v_file_name VARCHAR2 (100);
v_datafileNo VARCHAR2(10);
-- l_ret_value boolean;
BEGIN
DBMS_OUTPUT.PUT_LINE ('Test================='||table_space_name);
FOR i IN
(
select distinct trim(substr(error_msg,instr(error_msg,'tablespace')+10)) table_space_name from dba_resumable where error_msg is not null
)
LOOP
DBMS_OUTPUT.PUT_LINE ('1.'||i.table_space_name);
--Get current number of datafiles in the tablespace who is experiencing space shortage
SELECT COUNT (file_name)
INTO datafileCount
FROM dba_data_files
WHERE tablespace_name = i.table_space_name;
--Increase the file number count by 1, the new number will be used to add a new datafile to the tablespace
addDatafileNo := datafileCount + 1;
IF addDatafileNo < 9 THEN
v_datafileNo := '0'||addDatafileNo;
ELSE
v_datafileNo := addDatafileNo;
END IF;
--For tablespace ('ACXI','LNI','SRC2D','SRC2I','SRCD','SRCI') whose datafiles were created under /tms/oradata/tigger, the sql statement used to add new datafile will be like
--"alter tablespace xxx add datafile '/tms/oradata/tigger/xxx.dbf' size 1g autoextend on;
-- FOR tbs (
SELECT file_name
INTO v_file_name
FROM dba_data_files
WHERE tablespace_name = i.table_space_name and rownum=1;
DBMS_OUTPUT.PUT_LINE(v_file_name);
IF i.table_space_name IN ('ACXI', 'LNI', 'SRC2D', 'SRC2I', 'SRCD', 'SRCI')
THEN
db_file_name := SUBSTR (v_file_name, 1, 20);
ELSE
db_file_name := SUBSTR (v_file_name, 1, 27);
END IF;
DBMS_OUTPUT.PUT_LINE(db_file_name);
--For tablespace not in ('ACXI','LNI','SRC2D','SRC2I','SRCD','SRCI'), the sql statement to add new datafile will be like
--"alter tablespace xxx add datafile '/opt/oracle/oradata/tigger/xxx.dbf' size 1g autoexetnd on;
sql_stmt :=
'alter tablespace '
|| i.table_space_name
|| ' add datafile '
|| chr(39)
|| db_file_name
|| i.table_space_name
|| v_datafileNo
|| '.dbf'
|| chr(39)
|| ' size 1g autoextend on';
DBMS_OUTPUT.PUT_LINE(sql_stmt);
EXECUTE IMMEDIATE sql_stmt;
UTL_MAIL.SEND(
sender => 'tigger@hostname.com'
,recipients => 'dba@hostname.com'
,cc => NULL
,bcc => NULL
,subject => 'New datafile added to a tablespace'
,message => 'New datafile added to a tablespace'
,mime_type => 'text/plain; charset=us-ascii'
,priority => 1
);
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
-- dbms_output.put_line('Fehler');
raise_application_error (
-20001,
'The following error has occured: ' || SQLERRM
);
END;
/
Below is a modified procedure version:
CREATE OR REPLACE PROCEDURE test
IS
CRLF CHAR (2) := CHR (10) || CHR (39);
tbs VARCHAR2 (50);
datafileCount NUMBER;
addDatafileNo NUMBER;
sql_stmt VARCHAR2 (100);
fileLoc VARCHAR2 (50);
ERROR_TYPE VARCHAR2 (50);
object_type VARCHAR2 (50);
object_owner VARCHAR2 (50);
table_space_name VARCHAR2 (50);
object_name VARCHAR2 (50);
sub_object_name VARCHAR2 (50);
db_file_name VARCHAR2 (100);
v_file_name VARCHAR2 (100);
v_datafileNo VARCHAR2(10);
-- l_ret_value boolean;
BEGIN
DBMS_OUTPUT.PUT_LINE ('Test================='||table_space_name);
FOR i IN
(
select distinct trim(substr(error_msg,instr(error_msg,'tablespace')+10)) table_space_name from dba_resumable where error_msg is not null
)
LOOP
DBMS_OUTPUT.PUT_LINE ('1.'||i.table_space_name);
--Get current number of datafiles in the tablespace who is experiencing space shortage
SELECT COUNT (file_name)
INTO datafileCount
FROM dba_data_files
WHERE tablespace_name = i.table_space_name;
--Increase the file number count by 1, the new number will be used to add a new datafile to the tablespace
addDatafileNo := datafileCount + 1;
IF addDatafileNo < 9 THEN
v_datafileNo := '0'||addDatafileNo;
ELSE
v_datafileNo := addDatafileNo;
END IF;
--For tablespace ('ACXI','LNI','SRC2D','SRC2I','SRCD','SRCI') whose datafiles were created under /tms/oradata/tigger, the sql statement used to add new datafile will be like
--"alter tablespace xxx add datafile '/tms/oradata/tigger/xxx.dbf' size 1g autoextend on;
-- FOR tbs (
SELECT file_name
INTO v_file_name
FROM dba_data_files
WHERE tablespace_name = i.table_space_name and rownum=1;
DBMS_OUTPUT.PUT_LINE(v_file_name);
IF i.table_space_name IN ('ACXI', 'LNI', 'SRC2D', 'SRC2I', 'SRCD', 'SRCI')
THEN
db_file_name := SUBSTR (v_file_name, 1, 20);
ELSE
db_file_name := SUBSTR (v_file_name, 1, 27);
END IF;
DBMS_OUTPUT.PUT_LINE(db_file_name);
--For tablespace not in ('ACXI','LNI','SRC2D','SRC2I','SRCD','SRCI'), the sql statement to add new datafile will be like
--"alter tablespace xxx add datafile '/opt/oracle/oradata/tigger/xxx.dbf' size 1g autoexetnd on;
sql_stmt :=
'alter tablespace '
|| i.table_space_name
|| ' add datafile '
|| chr(39)
|| db_file_name
|| i.table_space_name
|| v_datafileNo
|| '.dbf'
|| chr(39)
|| ' size 1g autoextend on';
DBMS_OUTPUT.PUT_LINE(sql_stmt);
EXECUTE IMMEDIATE sql_stmt;
UTL_MAIL.SEND(
sender => 'tigger@hostname.com'
,recipients => 'dba@hostname.com'
,cc => NULL
,bcc => NULL
,subject => 'New datafile added to a tablespace'
,message => 'New datafile added to a tablespace'
,mime_type => 'text/plain; charset=us-ascii'
,priority => 1
);
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
-- dbms_output.put_line('Fehler');
raise_application_error (
-20001,
'The following error has occured: ' || SQLERRM
);
END;
/
posted by linyi @ 4:55 PM
0 Comments
0 Comments:
Post a Comment
Subscribe to Post Comments [Atom]
<< Home