Use Errorstack event to solve problems

error| Solve | The problem
Today, field personnel reported that Exp was not a sufficient right to report the error.
is the user-level exp with the current user, which is supposed to be a problem with no permissions.
The test was done according to the situation on the spot and it really reproduced the problem.

D:\temp>exp Parfile=exp.par

Export:release 9.2.0.5.0-production on Tue Dec 21 22:27:13 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Exp-00008:oracle Error 1031 encountered
Ora-01031:insufficient Privileges
Exp-00000:export terminated unsuccessfully

The contents of Exp.par are as follows:
Userid=scott/tiger
File=. Cott.dmp
Indexes=y
Grants=y
Rows=y
Constraints=y
Owner=scott
Direct=n
Compress=n
feedback=10000
Resumable=y
resumable_timeout=7200
Log=.\export.log

There was no problem, so I wanted to make a trace, so I use Errorstack event, because exp is executed directly on the command line, so you can only trace the entire database system, because you just want to know why the ORA-01031 error occurred, use the following statement:
Alter system set Events ' 1031 trace name errorstack level 3 ';

Then rerun exp, still error, this time in the Udump directory generated trace file, check the contents of the file, found:
Session ID: (15.210) 2004-12-21 22:22:36.704
2004-12-21 22:22:36.704
Ksedmp:internal or fatal error
Ora-01031:insufficient Privileges
Current SQL statement for this session:
ALTER session ENABLE resumable TIMEOUT 7200

The original problem is in the Enable Resumable section, which is a new 9i feature that suspends the entire operation to wait for the operator to solve the space problem when there is insufficient space.

As long as the problem is confirmed, it is very good to solve, in fact, if you read the Adminitrator document in detail, it will not make this error, the document inside enabling and disabling resumable space The allocation section clearly says that if you want to use the Resumbale feature, you must have resumable system privileges.

Check the site user's permissions and find only Connect and resource permissions. So:
Grant resumable to username;

Of course, if, as in some applications, users always have a DBA role, this error will never occur because the DBA role would have esumable system privileges. For security reasons, however, users in our application will not have DBA authority.

The solution to the problem is very simple, there is nothing to say, this article would like to introduce the means of finding the problem, that is, the use of the set events method. This method often becomes the beacon when we are puzzled by a mistake.

Author Introduction:

Zhang, net name Kamus
Itpub Oracle Certified version of the moderator, the current Itpub Oracle Management edition Moderator.
Currently serving in a large software company in Beijing, the chief DBA, is mainly responsible for the securities industry's national core trading system database management and maintenance work.
Keen attention to Oracle technology and other related technologies, haunt the major database technology forums, is currently China's largest Oracle technology forum www.itpub.net Database management version of the moderator.

Read more technical articles and essays that can be logged into my personal blog.
Http://blog.dbform.com.