Error stopping Profiler Error

[jwstadle]

I am using version 5.1.4.728 of PL/SQL Developer and I am having problems with the profiler. After running my test script, I receive the following error:

Error stopping Profiler
Result = 2

The DBMS_PROFILER package has been installed on the server and my account has access to it. I was prompted to create the tables and that worked just fine.

The error only occurs on a particular Oracle8i (Enterprise Edition Release 8.1.7.0.0) server.

I have used the profiler before on other servers and I have had no problems. The error message above is not enough information for my DBA to diagnose the problem. Any help would be appreciated!

 

[Marco Kalter]

According to the Oracle documentation, error 2 has the following meaning:

2 = error_io
data flush operation failed. check to see if the profiler tables have been created and there is adequate space.

There are also similar reports when profiling PL/SQL that accesses a database link.

 

[jwstadle]

I am referencing a table via a database link. It seems there is a problem with using the profiler (in PL/SQL Developer) while executing a prodedure that initiates a distrubuted transaction. The autonomous transaction issued by the profiler is not allowed. If I create a script where I start the profiler, execute my code, rollback the transaction, and then stop the profiler, it seems OK. It sounds like the way PL/SQL developer allows you to commit/rollback the execution of the procedure is causing problems with the profiler. It is not a bug, but an incompatibility with using the profiler in PL/SQL Developer when the code being executed in the test window involves a distributed transaction.

 

[Marco Kalter]

Does it work correctly from SQL*Plus?

 

[jwstadle]

It works fine if I execute the procedure in SQL Plus. It also works if I execute the following code in a command window in PL/SQL Devloper:

EXEC DBMS_PROFILER.START_PROFILER(run_comment => 'Test');

EXEC MyProcedureUsingDistributedTransaction();

ROLLBACK;

EXEC DBMS_PROFILER.STOP_PROFILER();

 

[Marco Kalter]

What happens if you remove the rollback, or if you use a commit instead?

 

[Worker]

I just had this exact problem, running a test script that (via many function calls) went across a database link. Adding an explicit COMMIT or ROLLBACK at the end of my test script resolved the problem.

 

[Felipe Moreno]

I had the same problem and it was due to lack of grants directly to the user who was running the profiler. We had created public synonyms for the profiler tables and that's what we did to solve this problem.