7 Replies Latest reply: Jul 24, 2012 8:21 AM by riedelme RSS

    Trace in SQL plus

    951116
      I tried to use SQL plus to trace what happens in the database (what queries are executed and such) when a certain function is performed in our information system...
      But i cant get it working...

      I cant seem to log in using our sysdba account...
      probably im doing something wrong...

      When i start the SQL plus
      I login using a password (not the SYSDBA password)

      I use the following syntax
      Username: username@database

      So for example for our test database
      i use test@database

      then the password...
      And i get connected

      But then when i try to find out the specific id for a specific user online at the moment i get errors (sorry for the bad explanation)...

      Is there any way to monitor ALL what happens in the database?
      for a period of time...
      (since i am the only one online at the test database, then i wont have to filter anyhing...

      any help is appreciated:)
      /Niklas
        • 1. Re: Trace in SQL plus
          riedelme
          948113 wrote:
          I tried to use SQL plus to trace what happens in the database (what queries are executed and such) when a certain function is performed in our information system...
          But i cant get it working...

          I cant seem to log in using our sysdba account...
          probably im doing something wrong...

          When i start the SQL plus
          I login using a password (not the SYSDBA password)

          I use the following syntax
          Username: username@database

          So for example for our test database
          i use test@database

          then the password...
          And i get connected

          But then when i try to find out the specific id for a specific user online at the moment i get errors (sorry for the bad explanation)...

          Is there any way to monitor ALL what happens in the database?
          for a period of time...
          (since i am the only one online at the test database, then i wont have to filter anyhing...

          any help is appreciated:)
          /Niklas
          Post the command you are executing, the error you are getting, and the version of Oracle you are using
          • 2. Re: Trace in SQL plus
            951116
            Thanks for your answer.
            Ill try to post everything that you might need...

            To start with the version, i believe its the 11g version

            And when I first open the sqlplus
            I get a "Enter username" prompt
            and i enter "test@databasename"
            then i enter the password

            Then i get a SQL> prompt

            Then i follow the guide here http://www.petefinnigan.com/ramblings/how_to_set_trace.htm
            (that someone on this forum adviced about)
            But i get an error at the first line(the thing about finding out where the dump files will be located works)

            but when i try this one things stop working:-p

            connect system/manager@sans as sysdba

            I get an error saying "ORA-12154 TNS: Could not resolve the connect identifier specified

            I have tried changing the "system/manager to either system OR manager and the @sans to @databasename but nothing is working
            /Niklas
            • 3. Re: Trace in SQL plus
              riedelme
              948113 wrote:
              but when i try this one things stop working:-p

              connect system/manager@sans as sysdba

              I get an error saying "ORA-12154 TNS: Could not resolve the connect identifier specified
              Check your tnsnames.ora file to see if "sans" is defined there - I suspect not, or perhaps "sans" is not quite the correct spelling
              • 4. Re: Trace in SQL plus
                951116
                I suspect that sans = the database name?

                If i change and write this

                connect system@databasename as sysdba
                and then enter the sysdba password
                then i get insufficent priviligies...

                (the sysdba account is used in other places and there it works just fine...)
                • 5. Re: Trace in SQL plus
                  riedelme
                  948113 wrote:
                  I suspect that sans = the database name?

                  If i change and write this

                  connect system@databasename as sysdba
                  and then enter the sysdba password
                  then i get insufficent priviligies...

                  (the sysdba account is used in other places and there it works just fine...)
                  Better. Make sure the login account has connect and the other usual privileges too
                  • 6. Re: Trace in SQL plus
                    951116
                    Oh that might be good, how do I check if the SYSDBA account has the privilegies?

                    :)
                    /Niklas
                    • 7. Re: Trace in SQL plus
                      riedelme
                      948113 wrote:
                      how do I check if the SYSDBA account has the privilegies?
                      Check the DBA_ROLE_PRIVS or ALL_ROLE_PRIVS data dictionary view for the user you are trying to log in as