13 Replies Latest reply on Jun 14, 2013 6:32 AM by 1011308

    How to view logs for execution of query?

    1011308

      I am new to oracle database. I would like to view the history of queries executed by a client application or other users, and the errors occurred during query execution. How to view the logs?

        • 1. Re: How to view logs for execution of query?
          Mahir M. Quluzade

          Hi,

           

           


          You can view SQL logs from  V$SQL, V$SQLAREA views.

          If error  occurs on execute query, then this query is not logging because, query is not executed.

           

           

           

          Regards

          Mahir M. Quluzade

          • 2. Re: How to view logs for execution of query?
            Karan

            If there are any errors first you should look at alert log else SQL Repair advisor is also there, Best is to have 10046 trace and see whats going on for the query, also see http://docs.oracle.com/cd/B28359_01/server.111/b28310/diag008.htm

             

            Also http://docs.oracle.com/cd/B28359_01/server.111/b28310/diag003.htm#CHDBCHID

             

            For DDL statements the following parameter is licensed under Oracle Configuration Management Pack ENABLE_DDL_LOGGING: when set to TRUE (default: FALSE)

            • 3. Re: How to view logs for execution of query?
              Karan

              Also i would suggest you to audit the statements through statement level auditing specially unsuccessful statement auditing , Specify WHENEVER NOT SUCCESSFUL to audit only statements and operations that fail or result in errors.

              http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_4007.htm#SQLRF01107

              • 4. Re: How to view logs for execution of query?
                Karan

                Auditing is also a good solution, Specify WHENEVER NOT SUCCESSFUL to audit only statements and operations that fail or result in errors.

                 

                http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_4007.htm#SQLRF01107

                • 5. Re: How to view logs for execution of query?
                  Mahir M. Quluzade

                  Hi Karan!


                  Yes, I  agree with you, it is good idea for audit unsuccessful statements with WHENEVER NOT SUCCESSFUL

                   

                  Regards

                  Mahir M. Quluzade

                  • 6. Re: How to view logs for execution of query?
                    rahulras

                    Which logs are you refering here? Oracle do not generate any log for "queries run by given user".

                    If you want to keep track of SQLs run by given user session, you need to do something for that. Auditing is an option, but it adds lot of resource overheads database.

                    You need to generate some log from your application.

                    • 7. Re: How to view logs for execution of query?
                      1011308

                      It is not my application. It is an application made by third-party. The application does not detect the error (like permission denied error, etc) occurred during query execution. Instead of waiting for the application developer to solve this, I prefer to look at the logs on oracle database then solve the problem myself, as it is very urgent and the issues occurred may affect my employer's business.

                      • 8. Re: How to view logs for execution of query?
                        sb92075

                        1011308 wrote:

                         

                        It is not my application. It is an application made by third-party. The application does not detect the error (like permission denied error, etc) occurred during query execution. Instead of waiting for the application developer to solve this, I prefer to look at the logs on oracle database then solve the problem myself, as it is very urgent and the issues occurred may affect my employer's business.

                         

                        Oracle RDBMS does not know or care about any application error and therefore does NOT log them.

                        If the error is important to the application, then it is the responsibility of the application to log it.

                         

                        I will give you a simple example.

                        A remote client initiates a new connection to the DB but provides an incorrect username/password combination.

                        The listener logs the connection request as successful; since it was correctly passed onto the database itself.

                        Oracle RDBMS does NOT log the the "ORA-01017: invalid username/password; logon denied" error & expects the application to handle this situation.

                         

                        In other words, You're On Your Own

                        • 9. Re: How to view logs for execution of query?
                          1011308

                          I no need Oracle to log the application error. I just need oracle to log all queries including unsuccessful queries and the error occurred during query execution, such as permission denied error, trigger error, etc, and of course, the user who execute the queries. With these information, I may be able to temporary solve this problem without relying on the application developer. We will report the related application bugs to the developers. But, we are in a rush, so no time for us to wait for the developers to solve the bugs.

                          • 10. Re: How to view logs for execution of query?
                            sb92075

                            1011308 wrote:

                             

                            I no need Oracle to log the application error. I just need oracle to log all queries including unsuccessful queries and the error occurred during query execution, such as permission denied error, trigger error, etc, and of course, the user who execute the queries. With these information, I may be able to temporary solve this problem without relying on the application developer. We are in a rush, so no time to wait for the application developers to solve the related bugs.

                             

                            Oracle does NOT log any SQL or any related details.

                            You could enable SQL_TRACE, but the resultant trace file does not contain any error message & will consume a LARGE amount of disk space.

                            It sounds as though this application was deployed into Production with inadequate testing.

                            • 11. Re: How to view logs for execution of query?
                              sb92075

                              1011308 wrote:

                               

                              I no need Oracle to log the application error. I just need oracle to log all queries including unsuccessful queries and the error occurred during query execution, such as permission denied error, trigger error, etc, and of course, the user who execute the queries. With these information, I may be able to temporary solve this problem without relying on the application developer. We will report the related application bugs to the developers. But, we are in a rush, so no time for us to wait for the developers to solve the bugs.

                               

                              How did the application get into Production without adequate testing?

                              • 12. Re: How to view logs for execution of query?
                                1011308

                                I also don't know. I am not the developer. I am just the new staff in my company. And, I am the only I.T. related staff in my company. That third-party application was deployed before I start working in my company.

                                 

                                And, the most important thing now is to solve the current problems as soon as possible. Blaming the developers at this time has no help in the issue. We need to do something instead of wasting time blaming the developers.

                                • 13. Re: How to view logs for execution of query?
                                  1011308

                                  I want to check whether auditing is enabled. I run the command "show parameter audit_trail" in Oracle SQL Developer. The following are the result:

                                   

                                  NAME                                               TYPE        VALUE                                                                                               

                                  -------------------------------------------------- ----------- ----------------------------------------------------------------------------------------------------

                                  audit_trail                                        string      DB  

                                   

                                  What does it means? Does it means the auditing has been enabled? I never run any audit command.