k_murli_krishna
asked on
insufficient privileges, tracing and parallel export/import
I have Oracle 8.1.6 enterprise edition on windows NT.
I have 3 questions:
1) I am unable to shutdown immediate my database.
C:\> sqlplus /nolog
SQL> connect / as sysdba
ERROR:
ORA-01031: insufficient privileges
SQL> connect sys/mypwd@mydb OR connect system/mypwd@mydb
SQL> shutdown immediate
ERROR:
ORA-01031: insufficient privileges
So, I recreated the password file since I changed password of SYS and restarted service and listener services in control panel services. Now:
SQL> connect / as sysdba
ERROR:
ORA-12571: TNS:packet writer failure
SQL> connect / as sysdba
ERROR:
ORA-01031: insufficient privileges
2) The documentation tells me to turn tracing on. How do I do this?
3) Is parallel export/import possible i.e. of 2 or more tables in different windows of same schema OR 2 or more schemas in different windows of same database OR 2 or more databases in different windows of same oracle server. Can you give me syntax for export/import of full database.
--- k_murli_krishna
I have 3 questions:
1) I am unable to shutdown immediate my database.
C:\> sqlplus /nolog
SQL> connect / as sysdba
ERROR:
ORA-01031: insufficient privileges
SQL> connect sys/mypwd@mydb OR connect system/mypwd@mydb
SQL> shutdown immediate
ERROR:
ORA-01031: insufficient privileges
So, I recreated the password file since I changed password of SYS and restarted service and listener services in control panel services. Now:
SQL> connect / as sysdba
ERROR:
ORA-12571: TNS:packet writer failure
SQL> connect / as sysdba
ERROR:
ORA-01031: insufficient privileges
2) The documentation tells me to turn tracing on. How do I do this?
3) Is parallel export/import possible i.e. of 2 or more tables in different windows of same schema OR 2 or more schemas in different windows of same database OR 2 or more databases in different windows of same oracle server. Can you give me syntax for export/import of full database.
--- k_murli_krishna
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
schwertner:
1) One can run the import and export utilities interactively, using command line parameters, or using parameter files. How using parameter files?
2) I think export is okay, but can we import in 2 or more windows simultaneously into 2 schemas of same database and schemas of different databases. What are the privileges and roles the schemas should have to prevent intersection during import. Once I was exporting/importing from a schema and started deletion of the other database and both crashed.
Set the REMOTE_LOGIN_PASSWORDFILE parameter to NONE.
dbaora:
set REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE
Both of you are contradicting each other. Please clarify.
--- k_murli_krishna
1) One can run the import and export utilities interactively, using command line parameters, or using parameter files. How using parameter files?
2) I think export is okay, but can we import in 2 or more windows simultaneously into 2 schemas of same database and schemas of different databases. What are the privileges and roles the schemas should have to prevent intersection during import. Once I was exporting/importing from a schema and started deletion of the other database and both crashed.
Set the REMOTE_LOGIN_PASSWORDFILE parameter to NONE.
dbaora:
set REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE
Both of you are contradicting each other. Please clarify.
--- k_murli_krishna
An example of a parameter file, exp_par.txt:
USERID=scott/tiger
TABLES=(emp,dept)
FILE=exp_one.dmp
DIRECT=Y
To execute the parameter from the operating system prompt:
$ exp parfile=exp_param.txt
The same parameter PARFILE exists for import utility.
USERID=scott/tiger
TABLES=(emp,dept)
FILE=exp_one.dmp
DIRECT=Y
To execute the parameter from the operating system prompt:
$ exp parfile=exp_param.txt
The same parameter PARFILE exists for import utility.
Import/Export are serios operations. Both Rollback Segments
and Online Redo Logs are involved. To do other operations at the same time is dangerous. In some case Oracle recommends to do bulk imports in RESTRICTED mode and immediately after that to initiate full DB backup. To make backup prior import is also a good idea to prevent Incomplete Point In Time Recovery if mistake occurs.
and Online Redo Logs are involved. To do other operations at the same time is dangerous. In some case Oracle recommends to do bulk imports in RESTRICTED mode and immediately after that to initiate full DB backup. To make backup prior import is also a good idea to prevent Incomplete Point In Time Recovery if mistake occurs.
ASKER
schwertner:
1 Create a new local Windows NT users’ group called ORA_<SID>_DBA and
ORA_<SID>_OPER that is specific to an instance, or ORA_DBA and
ORA_OPER that is not specific to an instance.
2 Add a Windows NT operating system user to that group. Once you access this
domain, you are automatically validated as an authorized DBA. This has worked specific and not specific to an instance. Thanx. Can you tell me how?
I kept the following in my init.ora file:
background_dump_destinatio n = C:\Tracing
Now I did connect / as sysdba & shutdown immediate
On startup, I get
LRM-00101: unknown parameter name 'background_dump_destinati on'
ORA-01078: failure in processing system parameters
I tried keeping the destination in single and double quotes. Is init.ora parameter file case sensitive and does it matter in what order we keep parameters.
--- k_murli_krishna
1 Create a new local Windows NT users’ group called ORA_<SID>_DBA and
ORA_<SID>_OPER that is specific to an instance, or ORA_DBA and
ORA_OPER that is not specific to an instance.
2 Add a Windows NT operating system user to that group. Once you access this
domain, you are automatically validated as an authorized DBA. This has worked specific and not specific to an instance. Thanx. Can you tell me how?
I kept the following in my init.ora file:
background_dump_destinatio
Now I did connect / as sysdba & shutdown immediate
On startup, I get
LRM-00101: unknown parameter name 'background_dump_destinati
ORA-01078: failure in processing system parameters
I tried keeping the destination in single and double quotes. Is init.ora parameter file case sensitive and does it matter in what order we keep parameters.
--- k_murli_krishna
Just copied from my computer (Oracle 8i, 8.1.6):
background_dump_dest = D:\816orant\admin\REKS816\ bdump
user_dump_dest = D:\816orant\admin\REKS816\ udump
and it works. Seems to be version dependent. Look at your system docs. I guess it has to be in the Oracle home directory, but not sure. In my case D:\816orant\ is the Home directory of the instalation.
For the other issues you have to ask your DBA.
I would like politly to explain you that the DBA stuff is very complex and need many time and efforts to be learned.
background_dump_dest = D:\816orant\admin\REKS816\
user_dump_dest = D:\816orant\admin\REKS816\
and it works. Seems to be version dependent. Look at your system docs. I guess it has to be in the Oracle home directory, but not sure. In my case D:\816orant\ is the Home directory of the instalation.
For the other issues you have to ask your DBA.
I would like politly to explain you that the DBA stuff is very complex and need many time and efforts to be learned.
ASKER
schwertner: one last question. When we work at command prompt/SQL prompt and execute SQL*Plus commands, if there is a error, there is accompanying message, turn on tracing to see accompanying errors. Will user_dump_dest be enough for this. Also, when we look up an error/exception in ErrorMessages.pdf of 8i => Action: Further diagnostic information should be in the error stack. How do we see this error stack.
--- k_murli_krishna
--- k_murli_krishna
I think user_dump_dest will give you information. But also
please look DBMS_UTILITY.FORMAT_ERROR_ STACK and
DBMS_UTILITY. FORMAT_CALL_STACK as indicated below for PL/SQL errors.
This article has attachments which implement ErrorPkg, a generic error
handler for PL/SQL. This package uses DBMS_UTILITY.FORMAT_ERROR_ STACK and
DBMS_UTILITY. FORMAT_CALL_STACK to report details on errors and the PL/SQL
call stack when they occur.
To install this package, first run the error_tables.sql script to create
the database tables, then the error_pkg SQL script to create the package
itself.
Use of the ErrorPkg
-------------------
The error_test.sql file illustrates how to use this package:
SQL> @error_test
SQL> DROP TABLE ttt;
Table dropped.
SQL>
SQL> CREATE TABLE ttt (f1 number);
Table created.
SQL>
SQL> CREATE OR REPLACE TRIGGER ttt_insert
2 BEFORE INSERT ON ttt
3 BEGIN
4 RAISE ZERO_DIVIDE;
5 END ttt_insert;
6 /
Trigger created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE C AS
2 BEGIN
3 INSERT INTO ttt VALUES (7);
4 EXCEPTION
5 WHEN OTHERS THEN
6 ErrorPkg.HandleAll(FALSE);
7 RAISE;
8 END C;
9 /
Procedure created.
SQL> show errors
No errors.
SQL>
SQL> CREATE OR REPLACE PROCEDURE B AS
2 BEGIN
3 C;
4 EXCEPTION
5 WHEN OTHERS THEN
6 ErrorPkg.HandleAll(FALSE);
7 RAISE;
8
8 END B;
9 /
Procedure created.
SQL> show errors
No errors.
SQL>
SQL> CREATE OR REPLACE PROCEDURE A AS
2 v_ErrorSeq NUMBER;
3 BEGIN
4 B;
5 EXCEPTION
6 WHEN OTHERS THEN
7 ErrorPkg.HandleAll(TRUE);
8 ErrorPkg.StoreStacks('Scot t', v_ErrorSeq, TRUE);
9 ErrorPkg.PrintStacks('Scot t', v_ErrorSeq);
10 END A;
11 /
Procedure created.
SQL> show errors
No errors.
SQL> exec a
17-JAN-97 11:07:46 Module: Scott Error #5: ORA-1476: divisor is equal to zero
Complete Call Stack:
Object Handle Line Number Object Name
------------- ----------- -----------
e02a5a4c 6 procedure SCOTT.C
e02cb4f4 3 procedure SCOTT.B
e029c7ec 4 procedure SCOTT.A
e02cad38 1 anonymous block
Complete Error Stack:
ORA-1476: divisor is equal to zero
ORA-6512: at "SCOTT.TTT_INSERT", line 2
ORA-4088: error during execution of trigger 'SCOTT.TTT_INSERT'
PL/SQL procedure successfully completed.
Call HandleAll(FALSE) at the lowest level of procedure nesting that you
want to track errors, then reraise the error. The error is popped off
the PL/SQL call stack, so call HandleAll(FALSE) each time, since it is in
procedures C and B.
At the topmost level (procedure A), HandleAll(TRUE) should be called,
followed by StoreStacks. StoreStacks records the error information in the
database tables, and returns the sequence number used to record the
information. PrintStacks echoes out the error information from the tables
to the screen using DBMS_OUTPUT.
Attachments
-----------
-------------------------- ---------- ---------- ---------- ---------- ---------- --
error_tables.sql
-------------------------- ---------- ---------- ---------- ---------- ---------- --
DROP TABLE errors CASCADE CONSTRAINTS;
CREATE TABLE errors (
module VARCHAR2(50),
seq_number NUMBER,
error_number NUMBER,
error_mesg VARCHAR2(100),
error_stack VARCHAR2(2000),
call_stack VARCHAR2(2000),
timestamp DATE,
PRIMARY KEY (module, seq_number));
DROP TABLE call_stacks;
CREATE TABLE call_stacks (
module VARCHAR2(50),
seq_number NUMBER,
call_order NUMBER,
object_handle VARCHAR2(10),
line_num NUMBER,
object_name VARCHAR2(80),
PRIMARY KEY (module, seq_number, call_order),
FOREIGN KEY (module, seq_number) REFERENCES errors ON DELETE CASCADE);
DROP TABLE error_stacks;
CREATE TABLE error_stacks (
module VARCHAR2(50),
seq_number NUMBER,
error_order NUMBER,
facility CHAR(3),
error_number NUMBER(5),
error_mesg VARCHAR2(100),
PRIMARY KEY (module, seq_number, error_order),
FOREIGN KEY (module, seq_number) REFERENCES errors ON DELETE CASCADE);
DROP SEQUENCE error_seq;
CREATE SEQUENCE error_seq
START WITH 1
INCREMENT BY 1;
-------------------------- ---------- ---------- ---------- ---------- ---------- --
error_pkg.sql
-------------------------- ---------- ---------- ---------- ---------- ---------- --
CREATE OR REPLACE PACKAGE ErrorPkg AS
/* Generic error handling package, using DBMS_UTILITY.FORMAT_ERROR_ STACK
and DBMS_UTILITY.FORMAT_CALL_S TACK. This package stores general
error information in the errors table, with detailed call stack and
error stack information in the call_stacks and error_stacks tables,
respectively. */
-- Entry point for handling errors. HandleAll should be called from all
-- exception handlers where you want the error to be logged. p_Top should be
-- TRUE only at the topmost level of procedure nesting. It should be FALSE
-- at other levels. See error_readme.txt for details on usage.
PROCEDURE HandleAll(p_Top BOOLEAN);
-- Prints the error and call stacks (using DBMS_OUTPUT) for the given
-- module and sequence number.
PROCEDURE PrintStacks(p_Module IN errors.module%TYPE,
p_SeqNum IN errors.seq_number%TYPE);
-- Unwinds the call and error stacks, and stores them in the errors and
-- call_stacks tables. Returns the sequence number under which the
-- error is stored.
-- If p_CommitFlag is TRUE, then the inserts are committed.
-- In order to use StoreStacks, an error must have been handled. Thus
-- HandleAll should have been called with p_Top = TRUE.
PROCEDURE StoreStacks(p_Module IN errors.module%TYPE,
p_SeqNum OUT errors.seq_number%TYPE,
p_CommitFlag BOOLEAN DEFAULT FALSE);
END ErrorPkg;
/
show errors
CREATE OR REPLACE PACKAGE BODY ErrorPkg AS
v_NewLine CONSTANT CHAR(1) := CHR(10);
v_Handled BOOLEAN := FALSE;
v_ErrorStack VARCHAR2(2000);
v_CallStack VARCHAR2(2000);
PROCEDURE HandleAll(p_Top BOOLEAN) IS
BEGIN
IF p_Top THEN
v_Handled := FALSE;
ELSIF NOT v_Handled THEN
v_Handled := TRUE;
v_ErrorStack := DBMS_UTILITY.FORMAT_ERROR_ STACK;
v_CallStack := DBMS_UTILITY.FORMAT_CALL_S TACK;
END IF;
END HandleAll;
PROCEDURE PrintStacks(p_Module IN errors.module%TYPE,
p_SeqNum IN errors.seq_number%TYPE) IS
v_TimeStamp errors.timestamp%TYPE;
v_ErrorMsg errors.error_mesg%TYPE;
CURSOR c_CallCur IS
SELECT object_handle, line_num, object_name
FROM call_stacks
WHERE module = p_Module
AND seq_number = p_SeqNum
ORDER BY call_order;
CURSOR c_ErrorCur IS
SELECT facility, error_number, error_mesg
FROM error_stacks
WHERE module = p_Module
AND seq_number = p_SeqNum
ORDER BY error_order;
BEGIN
SELECT timestamp, error_mesg
INTO v_TimeStamp, v_ErrorMsg
FROM errors
WHERE module = p_Module
AND seq_number = p_SeqNum;
-- Output general error information.
DBMS_OUTPUT.PUT(TO_CHAR(v_ TimeStamp, 'DD-MON-YY HH24:MI:SS'));
DBMS_OUTPUT.PUT(' Module: ' || p_Module);
DBMS_OUTPUT.PUT(' Error #' || p_SeqNum || ': ');
DBMS_OUTPUT.PUT_LINE(v_Err orMsg);
-- Output the call stack.
DBMS_OUTPUT.PUT_LINE('Comp lete Call Stack:');
DBMS_OUTPUT.PUT_LINE(' Object Handle Line Number Object Name');
DBMS_OUTPUT.PUT_LINE(' ------------- ----------- -----------');
FOR v_CallRec in c_CallCur LOOP
DBMS_OUTPUT.PUT(RPAD(' ' || v_CallRec.object_handle, 15));
DBMS_OUTPUT.PUT(RPAD(' ' || TO_CHAR(v_CallRec.line_num ), 13));
DBMS_OUTPUT.PUT_LINE(' ' || v_CallRec.object_name);
END LOOP;
-- Output the error stack.
DBMS_OUTPUT.PUT_LINE('Comp lete Error Stack:');
FOR v_ErrorRec in c_ErrorCur LOOP
DBMS_OUTPUT.PUT(' ' || v_ErrorRec.facility || '-');
DBMS_OUTPUT.PUT(TO_CHAR(v_ ErrorRec.e rror_numbe r) || ': ');
DBMS_OUTPUT.PUT_LINE(v_Err orRec.erro r_mesg);
END LOOP;
END PrintStacks;
PROCEDURE StoreStacks(p_Module IN errors.module%TYPE,
p_SeqNum OUT errors.seq_number%TYPE,
p_CommitFlag BOOLEAN DEFAULT FALSE) IS
v_SeqNum NUMBER;
v_Index NUMBER;
v_Length NUMBER;
v_End NUMBER;
v_Call VARCHAR2(100);
v_CallOrder NUMBER := 1;
v_Handle call_stacks.object_handle% TYPE;
v_LineNum call_stacks.line_num%TYPE;
v_ObjectName call_stacks.object_name%TY PE;
v_Error VARCHAR2(120);
v_ErrorOrder NUMBER := 1;
v_Facility error_stacks.facility%TYPE ;
v_ErrNum error_stacks.error_number% TYPE;
v_ErrMsg error_stacks.error_mesg%TY PE;
v_FirstErrNum errors.error_number%TYPE;
v_FirstErrMsg errors.error_mesg%TYPE;
BEGIN
-- First get the error sequence number.
SELECT error_seq.nextval
INTO v_SeqNum
FROM dual;
p_SeqNum := v_SeqNum;
-- Insert the first part of the header information into the errors table.
INSERT INTO errors
(module, seq_number, error_stack, call_stack, timestamp)
VALUES
(p_Module, v_SeqNum, v_ErrorStack, v_CallStack, SYSDATE);
-- Unwind the error stack to get each error out. We do this by scanning
-- the error stack string. Start with the index at the beginning of the
-- string.
v_Index := 1;
-- Loop through the string, finding each newline. A newline ends each
-- error on the stack.
WHILE v_Index < LENGTH(v_ErrorStack) LOOP
-- v_End is the position of the newline.
v_End := INSTR(v_ErrorStack, v_NewLine, v_Index);
-- Thus, the error is between the current index and the newline.
v_Error := SUBSTR(v_ErrorStack, v_Index, v_End - v_Index);
-- Skip over the current error, for the next iteration.
v_Index := v_Index + LENGTH(v_Error) + 1;
-- An error looks like 'facility-number: mesg'. We need to get each
-- piece out for insertion.
-- First, the facility is the first 3 characters of the error.
v_Facility := SUBSTR(v_Error, 1, 3);
-- Remove the facility and the dash (always 4 characters).
v_Error := SUBSTR(v_Error, 5);
-- Now we can get the error number.
v_ErrNum := TO_NUMBER(SUBSTR(v_Error, 1, INSTR(v_Error, ':') - 1));
-- Remove the error number, colon and space (always 7 characters).
v_Error := SUBSTR(v_Error, 8);
-- What's left is the error message.
v_ErrMsg := v_Error;
-- Insert the errors, and grab the first error number and message
-- while we're at it.
INSERT INTO error_stacks
(module, seq_number, error_order, facility, error_number, error_mesg)
VALUES
(p_Module, p_SeqNum, v_ErrorOrder, v_Facility, v_ErrNum, v_ErrMsg);
IF v_ErrorOrder = 1 THEN
v_FirstErrNum := v_ErrNum;
v_FirstErrMsg := v_Facility || '-' || TO_NUMBER(v_ErrNum) ||
': ' || v_ErrMsg;
END IF;
v_ErrorOrder := v_ErrorOrder + 1;
END LOOP;
-- Update the errors table with the message and code.
UPDATE errors
SET error_number = v_FirstErrNum,
error_mesg = v_FirstErrMsg
WHERE module = p_Module
AND seq_number = v_SeqNum;
-- Now we need to unwind the call stack, to get each call out. We do this
-- be scanning the call stack string. Start with the index after the
-- first call on the stack. This will be after the first occurrence of
-- 'name' and the newline.
v_Index := INSTR(v_CallStack, 'name') + 5;
-- Loop through the string, finding each newline. A newline ends each
-- call on the stack.
WHILE v_Index < LENGTH(v_CallStack) LOOP
-- v_End is the position of the newline.
v_End := INSTR(v_CallStack, v_NewLine, v_Index);
-- Thus, the call is between the current index and the newline.
v_Call := SUBSTR(v_CallStack, v_Index, v_End - v_Index);
-- Skip over the current call, for the next iteration.
v_Index := v_Index + LENGTH(v_Call) + 1;
-- Within a call, we have the object handle, then the line number, then
-- the object name, separated by spaces. We need to separate them
-- out for insertion.
-- Trim white space from the call first.
v_Call := LTRIM(v_Call);
-- First get the object handle.
v_Handle := SUBSTR(v_Call, 1, INSTR(v_Call, ' '));
-- Now, remove the object handle, then the white space from the call.
v_Call := SUBSTR(v_Call, LENGTH(v_Handle) + 1);
v_Call := LTRIM(v_Call);
-- Now we can get the line number.
v_LineNum := TO_NUMBER(SUBSTR(v_Call, 1, INSTR(v_Call, ' ')));
-- Remove the line number, and white space.
v_Call := SUBSTR(v_Call, LENGTH(v_LineNum) + 1);
v_Call := LTRIM(v_Call);
-- What is left is the object name.
v_ObjectName := v_Call;
-- Insert all calls except the call for ErrorPkg.
IF v_CallOrder > 1 THEN
INSERT INTO call_stacks
(module, seq_number, call_order, object_handle, line_num, object_name)
VALUES
(p_Module, v_SeqNum, v_CallOrder, v_Handle, v_LineNum, v_ObjectName);
END IF;
v_Callorder := v_CallOrder + 1;
END LOOP;
IF p_CommitFlag THEN
commit;
END IF;
END StoreStacks;
END ErrorPkg;
/
show errors
-------------------------- ---------- ---------- ---------- ---------- ---------- --
error_test.sql
-------------------------- ---------- ---------- ---------- ---------- ---------- --
DROP TABLE ttt;
CREATE TABLE ttt (f1 number);
CREATE OR REPLACE TRIGGER ttt_insert
BEFORE INSERT ON ttt
BEGIN
RAISE ZERO_DIVIDE;
END ttt_insert;
/
CREATE OR REPLACE PROCEDURE C AS
BEGIN
INSERT INTO ttt VALUES (7);
EXCEPTION
WHEN OTHERS THEN
ErrorPkg.HandleAll(FALSE);
RAISE;
END C;
/
show errors
CREATE OR REPLACE PROCEDURE B AS
BEGIN
C;
EXCEPTION
WHEN OTHERS THEN
ErrorPkg.HandleAll(FALSE);
RAISE;
END B;
/
show errors
CREATE OR REPLACE PROCEDURE A AS
v_ErrorSeq NUMBER;
BEGIN
B;
EXCEPTION
WHEN OTHERS THEN
ErrorPkg.HandleAll(TRUE);
ErrorPkg.StoreStacks('Scot t', v_ErrorSeq, TRUE);
ErrorPkg.PrintStacks('Scot t', v_ErrorSeq);
END A;
/
show errors
.
please look DBMS_UTILITY.FORMAT_ERROR_
DBMS_UTILITY. FORMAT_CALL_STACK as indicated below for PL/SQL errors.
This article has attachments which implement ErrorPkg, a generic error
handler for PL/SQL. This package uses DBMS_UTILITY.FORMAT_ERROR_
DBMS_UTILITY. FORMAT_CALL_STACK to report details on errors and the PL/SQL
call stack when they occur.
To install this package, first run the error_tables.sql script to create
the database tables, then the error_pkg SQL script to create the package
itself.
Use of the ErrorPkg
-------------------
The error_test.sql file illustrates how to use this package:
SQL> @error_test
SQL> DROP TABLE ttt;
Table dropped.
SQL>
SQL> CREATE TABLE ttt (f1 number);
Table created.
SQL>
SQL> CREATE OR REPLACE TRIGGER ttt_insert
2 BEFORE INSERT ON ttt
3 BEGIN
4 RAISE ZERO_DIVIDE;
5 END ttt_insert;
6 /
Trigger created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE C AS
2 BEGIN
3 INSERT INTO ttt VALUES (7);
4 EXCEPTION
5 WHEN OTHERS THEN
6 ErrorPkg.HandleAll(FALSE);
7 RAISE;
8 END C;
9 /
Procedure created.
SQL> show errors
No errors.
SQL>
SQL> CREATE OR REPLACE PROCEDURE B AS
2 BEGIN
3 C;
4 EXCEPTION
5 WHEN OTHERS THEN
6 ErrorPkg.HandleAll(FALSE);
7 RAISE;
8
8 END B;
9 /
Procedure created.
SQL> show errors
No errors.
SQL>
SQL> CREATE OR REPLACE PROCEDURE A AS
2 v_ErrorSeq NUMBER;
3 BEGIN
4 B;
5 EXCEPTION
6 WHEN OTHERS THEN
7 ErrorPkg.HandleAll(TRUE);
8 ErrorPkg.StoreStacks('Scot
9 ErrorPkg.PrintStacks('Scot
10 END A;
11 /
Procedure created.
SQL> show errors
No errors.
SQL> exec a
17-JAN-97 11:07:46 Module: Scott Error #5: ORA-1476: divisor is equal to zero
Complete Call Stack:
Object Handle Line Number Object Name
------------- ----------- -----------
e02a5a4c 6 procedure SCOTT.C
e02cb4f4 3 procedure SCOTT.B
e029c7ec 4 procedure SCOTT.A
e02cad38 1 anonymous block
Complete Error Stack:
ORA-1476: divisor is equal to zero
ORA-6512: at "SCOTT.TTT_INSERT", line 2
ORA-4088: error during execution of trigger 'SCOTT.TTT_INSERT'
PL/SQL procedure successfully completed.
Call HandleAll(FALSE) at the lowest level of procedure nesting that you
want to track errors, then reraise the error. The error is popped off
the PL/SQL call stack, so call HandleAll(FALSE) each time, since it is in
procedures C and B.
At the topmost level (procedure A), HandleAll(TRUE) should be called,
followed by StoreStacks. StoreStacks records the error information in the
database tables, and returns the sequence number used to record the
information. PrintStacks echoes out the error information from the tables
to the screen using DBMS_OUTPUT.
Attachments
-----------
--------------------------
error_tables.sql
--------------------------
DROP TABLE errors CASCADE CONSTRAINTS;
CREATE TABLE errors (
module VARCHAR2(50),
seq_number NUMBER,
error_number NUMBER,
error_mesg VARCHAR2(100),
error_stack VARCHAR2(2000),
call_stack VARCHAR2(2000),
timestamp DATE,
PRIMARY KEY (module, seq_number));
DROP TABLE call_stacks;
CREATE TABLE call_stacks (
module VARCHAR2(50),
seq_number NUMBER,
call_order NUMBER,
object_handle VARCHAR2(10),
line_num NUMBER,
object_name VARCHAR2(80),
PRIMARY KEY (module, seq_number, call_order),
FOREIGN KEY (module, seq_number) REFERENCES errors ON DELETE CASCADE);
DROP TABLE error_stacks;
CREATE TABLE error_stacks (
module VARCHAR2(50),
seq_number NUMBER,
error_order NUMBER,
facility CHAR(3),
error_number NUMBER(5),
error_mesg VARCHAR2(100),
PRIMARY KEY (module, seq_number, error_order),
FOREIGN KEY (module, seq_number) REFERENCES errors ON DELETE CASCADE);
DROP SEQUENCE error_seq;
CREATE SEQUENCE error_seq
START WITH 1
INCREMENT BY 1;
--------------------------
error_pkg.sql
--------------------------
CREATE OR REPLACE PACKAGE ErrorPkg AS
/* Generic error handling package, using DBMS_UTILITY.FORMAT_ERROR_
and DBMS_UTILITY.FORMAT_CALL_S
error information in the errors table, with detailed call stack and
error stack information in the call_stacks and error_stacks tables,
respectively. */
-- Entry point for handling errors. HandleAll should be called from all
-- exception handlers where you want the error to be logged. p_Top should be
-- TRUE only at the topmost level of procedure nesting. It should be FALSE
-- at other levels. See error_readme.txt for details on usage.
PROCEDURE HandleAll(p_Top BOOLEAN);
-- Prints the error and call stacks (using DBMS_OUTPUT) for the given
-- module and sequence number.
PROCEDURE PrintStacks(p_Module IN errors.module%TYPE,
p_SeqNum IN errors.seq_number%TYPE);
-- Unwinds the call and error stacks, and stores them in the errors and
-- call_stacks tables. Returns the sequence number under which the
-- error is stored.
-- If p_CommitFlag is TRUE, then the inserts are committed.
-- In order to use StoreStacks, an error must have been handled. Thus
-- HandleAll should have been called with p_Top = TRUE.
PROCEDURE StoreStacks(p_Module IN errors.module%TYPE,
p_SeqNum OUT errors.seq_number%TYPE,
p_CommitFlag BOOLEAN DEFAULT FALSE);
END ErrorPkg;
/
show errors
CREATE OR REPLACE PACKAGE BODY ErrorPkg AS
v_NewLine CONSTANT CHAR(1) := CHR(10);
v_Handled BOOLEAN := FALSE;
v_ErrorStack VARCHAR2(2000);
v_CallStack VARCHAR2(2000);
PROCEDURE HandleAll(p_Top BOOLEAN) IS
BEGIN
IF p_Top THEN
v_Handled := FALSE;
ELSIF NOT v_Handled THEN
v_Handled := TRUE;
v_ErrorStack := DBMS_UTILITY.FORMAT_ERROR_
v_CallStack := DBMS_UTILITY.FORMAT_CALL_S
END IF;
END HandleAll;
PROCEDURE PrintStacks(p_Module IN errors.module%TYPE,
p_SeqNum IN errors.seq_number%TYPE) IS
v_TimeStamp errors.timestamp%TYPE;
v_ErrorMsg errors.error_mesg%TYPE;
CURSOR c_CallCur IS
SELECT object_handle, line_num, object_name
FROM call_stacks
WHERE module = p_Module
AND seq_number = p_SeqNum
ORDER BY call_order;
CURSOR c_ErrorCur IS
SELECT facility, error_number, error_mesg
FROM error_stacks
WHERE module = p_Module
AND seq_number = p_SeqNum
ORDER BY error_order;
BEGIN
SELECT timestamp, error_mesg
INTO v_TimeStamp, v_ErrorMsg
FROM errors
WHERE module = p_Module
AND seq_number = p_SeqNum;
-- Output general error information.
DBMS_OUTPUT.PUT(TO_CHAR(v_
DBMS_OUTPUT.PUT(' Module: ' || p_Module);
DBMS_OUTPUT.PUT(' Error #' || p_SeqNum || ': ');
DBMS_OUTPUT.PUT_LINE(v_Err
-- Output the call stack.
DBMS_OUTPUT.PUT_LINE('Comp
DBMS_OUTPUT.PUT_LINE(' Object Handle Line Number Object Name');
DBMS_OUTPUT.PUT_LINE(' ------------- ----------- -----------');
FOR v_CallRec in c_CallCur LOOP
DBMS_OUTPUT.PUT(RPAD(' ' || v_CallRec.object_handle, 15));
DBMS_OUTPUT.PUT(RPAD(' ' || TO_CHAR(v_CallRec.line_num
DBMS_OUTPUT.PUT_LINE(' ' || v_CallRec.object_name);
END LOOP;
-- Output the error stack.
DBMS_OUTPUT.PUT_LINE('Comp
FOR v_ErrorRec in c_ErrorCur LOOP
DBMS_OUTPUT.PUT(' ' || v_ErrorRec.facility || '-');
DBMS_OUTPUT.PUT(TO_CHAR(v_
DBMS_OUTPUT.PUT_LINE(v_Err
END LOOP;
END PrintStacks;
PROCEDURE StoreStacks(p_Module IN errors.module%TYPE,
p_SeqNum OUT errors.seq_number%TYPE,
p_CommitFlag BOOLEAN DEFAULT FALSE) IS
v_SeqNum NUMBER;
v_Index NUMBER;
v_Length NUMBER;
v_End NUMBER;
v_Call VARCHAR2(100);
v_CallOrder NUMBER := 1;
v_Handle call_stacks.object_handle%
v_LineNum call_stacks.line_num%TYPE;
v_ObjectName call_stacks.object_name%TY
v_Error VARCHAR2(120);
v_ErrorOrder NUMBER := 1;
v_Facility error_stacks.facility%TYPE
v_ErrNum error_stacks.error_number%
v_ErrMsg error_stacks.error_mesg%TY
v_FirstErrNum errors.error_number%TYPE;
v_FirstErrMsg errors.error_mesg%TYPE;
BEGIN
-- First get the error sequence number.
SELECT error_seq.nextval
INTO v_SeqNum
FROM dual;
p_SeqNum := v_SeqNum;
-- Insert the first part of the header information into the errors table.
INSERT INTO errors
(module, seq_number, error_stack, call_stack, timestamp)
VALUES
(p_Module, v_SeqNum, v_ErrorStack, v_CallStack, SYSDATE);
-- Unwind the error stack to get each error out. We do this by scanning
-- the error stack string. Start with the index at the beginning of the
-- string.
v_Index := 1;
-- Loop through the string, finding each newline. A newline ends each
-- error on the stack.
WHILE v_Index < LENGTH(v_ErrorStack) LOOP
-- v_End is the position of the newline.
v_End := INSTR(v_ErrorStack, v_NewLine, v_Index);
-- Thus, the error is between the current index and the newline.
v_Error := SUBSTR(v_ErrorStack, v_Index, v_End - v_Index);
-- Skip over the current error, for the next iteration.
v_Index := v_Index + LENGTH(v_Error) + 1;
-- An error looks like 'facility-number: mesg'. We need to get each
-- piece out for insertion.
-- First, the facility is the first 3 characters of the error.
v_Facility := SUBSTR(v_Error, 1, 3);
-- Remove the facility and the dash (always 4 characters).
v_Error := SUBSTR(v_Error, 5);
-- Now we can get the error number.
v_ErrNum := TO_NUMBER(SUBSTR(v_Error, 1, INSTR(v_Error, ':') - 1));
-- Remove the error number, colon and space (always 7 characters).
v_Error := SUBSTR(v_Error, 8);
-- What's left is the error message.
v_ErrMsg := v_Error;
-- Insert the errors, and grab the first error number and message
-- while we're at it.
INSERT INTO error_stacks
(module, seq_number, error_order, facility, error_number, error_mesg)
VALUES
(p_Module, p_SeqNum, v_ErrorOrder, v_Facility, v_ErrNum, v_ErrMsg);
IF v_ErrorOrder = 1 THEN
v_FirstErrNum := v_ErrNum;
v_FirstErrMsg := v_Facility || '-' || TO_NUMBER(v_ErrNum) ||
': ' || v_ErrMsg;
END IF;
v_ErrorOrder := v_ErrorOrder + 1;
END LOOP;
-- Update the errors table with the message and code.
UPDATE errors
SET error_number = v_FirstErrNum,
error_mesg = v_FirstErrMsg
WHERE module = p_Module
AND seq_number = v_SeqNum;
-- Now we need to unwind the call stack, to get each call out. We do this
-- be scanning the call stack string. Start with the index after the
-- first call on the stack. This will be after the first occurrence of
-- 'name' and the newline.
v_Index := INSTR(v_CallStack, 'name') + 5;
-- Loop through the string, finding each newline. A newline ends each
-- call on the stack.
WHILE v_Index < LENGTH(v_CallStack) LOOP
-- v_End is the position of the newline.
v_End := INSTR(v_CallStack, v_NewLine, v_Index);
-- Thus, the call is between the current index and the newline.
v_Call := SUBSTR(v_CallStack, v_Index, v_End - v_Index);
-- Skip over the current call, for the next iteration.
v_Index := v_Index + LENGTH(v_Call) + 1;
-- Within a call, we have the object handle, then the line number, then
-- the object name, separated by spaces. We need to separate them
-- out for insertion.
-- Trim white space from the call first.
v_Call := LTRIM(v_Call);
-- First get the object handle.
v_Handle := SUBSTR(v_Call, 1, INSTR(v_Call, ' '));
-- Now, remove the object handle, then the white space from the call.
v_Call := SUBSTR(v_Call, LENGTH(v_Handle) + 1);
v_Call := LTRIM(v_Call);
-- Now we can get the line number.
v_LineNum := TO_NUMBER(SUBSTR(v_Call, 1, INSTR(v_Call, ' ')));
-- Remove the line number, and white space.
v_Call := SUBSTR(v_Call, LENGTH(v_LineNum) + 1);
v_Call := LTRIM(v_Call);
-- What is left is the object name.
v_ObjectName := v_Call;
-- Insert all calls except the call for ErrorPkg.
IF v_CallOrder > 1 THEN
INSERT INTO call_stacks
(module, seq_number, call_order, object_handle, line_num, object_name)
VALUES
(p_Module, v_SeqNum, v_CallOrder, v_Handle, v_LineNum, v_ObjectName);
END IF;
v_Callorder := v_CallOrder + 1;
END LOOP;
IF p_CommitFlag THEN
commit;
END IF;
END StoreStacks;
END ErrorPkg;
/
show errors
--------------------------
error_test.sql
--------------------------
DROP TABLE ttt;
CREATE TABLE ttt (f1 number);
CREATE OR REPLACE TRIGGER ttt_insert
BEFORE INSERT ON ttt
BEGIN
RAISE ZERO_DIVIDE;
END ttt_insert;
/
CREATE OR REPLACE PROCEDURE C AS
BEGIN
INSERT INTO ttt VALUES (7);
EXCEPTION
WHEN OTHERS THEN
ErrorPkg.HandleAll(FALSE);
RAISE;
END C;
/
show errors
CREATE OR REPLACE PROCEDURE B AS
BEGIN
C;
EXCEPTION
WHEN OTHERS THEN
ErrorPkg.HandleAll(FALSE);
RAISE;
END B;
/
show errors
CREATE OR REPLACE PROCEDURE A AS
v_ErrorSeq NUMBER;
BEGIN
B;
EXCEPTION
WHEN OTHERS THEN
ErrorPkg.HandleAll(TRUE);
ErrorPkg.StoreStacks('Scot
ErrorPkg.PrintStacks('Scot
END A;
/
show errors
.
ASKER
Thanx a lot.
Regarding the passoword file, you need to recreate the password file, grant sysdba to the user you want to log on with and set REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE.
cheers,
dbaora.