This discussion is archived
7 Replies Latest reply: Jul 24, 2012 6:21 AM by riedelme RSS

Trace in SQL plus

951116 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Oh that might be good, how do I check if the SYSDBA account has the privilegies?

    :)
    /Niklas
  • 7. Re: Trace in SQL plus
    riedelme Expert
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points