?
Solved

Trace

Posted on 2005-04-13
11
Medium Priority
?
2,534 Views
Last Modified: 2012-05-05
Hi can some one write down the steps in setting up a Oracle trace for a perticular schema on an instance.

Thanks!
0
Comment
Question by:crishna1
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 25

Expert Comment

by:jrb1
ID: 13778801
Are you talking about tracing table changes for a complete schema?  Or are you thinking something else?
0
 
LVL 11

Expert Comment

by:sujit_kumar
ID: 13778868
0
 
LVL 5

Expert Comment

by:helpneed
ID: 13779052
hi

Oracle Trace was introduced in Oracle7 release: 7.3.2.1      I
t is enabled by default (when installed) in 7.3.2 onwards

You can use Oracle Trace to collect server performance data for a specific database session or for the entire instance. You can also select the server event set for which you want to collect

i think with Oracle Expert Methodology u can trace schema level as well...

please follow

http://www-rohan.sdsu.edu/doc/oracle/oem140/A53696_01/evt.htm 
http://www.csee.umbc.edu/help/oracle8/server.815/a67775/ch15_otr.htm
http://www.proligence.com/pr_oracle_trace.pdf.

these links

regards



0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:crishna1
ID: 13782303
i had already looked at the above stuff, can some one just give me steps to set up a trace at schema/user level, do not refere 10 page documents please.

appreaciate your help!

Thanks.
0
 
LVL 25

Expert Comment

by:jrb1
ID: 13783541
I would if you'd clarify what you want traced.  What do you mean by the schema/user level?
0
 
LVL 5

Expert Comment

by:helpneed
ID: 13788622
hi

if u r not goin to refer the documents "boy"... immm ...i think will be difficuilt and
i  dont think everytime we will get all the answers by reading 1 page(or even 100 page)
here its not very much clear that schema/user level means?
and in the first reply itself  someone pointing it ..and till now its not cleared

regards
0
 

Author Comment

by:crishna1
ID: 13809085
jrb1,

appreciate your responce, we have an instance ABC and we would like to trace the activity on one schema on that instance.
is it possible? Hope this makes sense.

Thanks.
0
 
LVL 25

Expert Comment

by:jrb1
ID: 13809611
Do you mean all selects, changes, etc.?  Only data changes?  What about creating or deleting objects in the schema (new tables, procedures, triggers, etc.)?
0
 

Author Comment

by:crishna1
ID: 13809700
any changes from the application.
0
 
LVL 25

Accepted Solution

by:
jrb1 earned 375 total points
ID: 13810378
About the best I could recommend is a procedure to turn auditing on for all tables in the schema:

DECLARE
   owner          dba_objects.owner%type;
   object_name dba_objects.object_name%type;
   cursor c1 is select owner, object_name
                         from dba_objects
                         where object_owner = '{schemaname}'
                         and object_type in ('TABLE','VIEW','SYNONYM');

BEGIN
   open c1;
   fetch c1 into owner, object_name;
   loop
      exit when c1%notfound;
      execute immediate 'audit insert,update,delete on ' || owner || '.' || object_name;
   end loop;
END;
/

That should turn on auditing for any data changes in a schema.  You'll see audits written to:

SYS.AUD$

You can read more at

http://www.oracledbaexpert.com/oracle/databaseAuditing.html
0
 
LVL 5

Expert Comment

by:helpneed
ID: 13812846
hi

yes if u want to do it for sqls and all the best method is auditing ...but mind u it will take some space and may make ur db slow

regards
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses
Course of the Month14 days, 11 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question