Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: tkprof and security?

Re: tkprof and security?

From: Rachel Carmichael <carmichr_at_hotmail.com>
Date: Wed, 09 May 2001 06:33:08 -0700
Message-ID: <F001.002FDA17.20010509052557@fatcity.com> 






That's what I love about this list.... before I can answer, someone has 
provided scripts to do things :)



Thanks Tim, I'm adding these to my scripts library



Rachel




>From: "Tim Sawmiller" <sawmillert_at_state.mi.us>

>Reply-To: ORACLE-L_at_fatcity.com

>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>

>Subject: Re: tkprof and security?

>Date: Wed, 09 May 2001 04:40:22 -0800

>

>trace_on.sql

>

>REM Script to turn TRACE MODE on for a selected SID.

>set serveroutput ON size 2000;

>set verify OFF;

>DECLARE


>   r_sid               NUMBER;

>   v_sid               NUMBER;

>   v_serial    NUMBER;

>   v_username  VARCHAR(30);

>   v_status    VARCHAR2(8);

>   v_server    VARCHAR2(9);

>   p_server_pid        VARCHAR2(9);

>

>BEGIN


>--  Accept r_sid Number 'What SID do you wish to trace? - '

>   r_sid := &SID;

>   SELECT s.sid, s.serial#, s.status, s.server, p.spid

>     INTO v_sid, v_serial, v_status, v_server, p_server_pid

>     FROM v$session s, v$process p

>    WHERE s.sid = r_sid

>      AND s.paddr = p.addr;

>   DBMS_OUTPUT.PUT_LINE ('Sid ' || TO_CHAR(v_sid) ||

>                        '  Serial# ' || TO_CHAR(v_serial) ||

>                        '  Username ' || v_username ||

>                        '  Status ' || v_status ||

>                        '  Server ' || v_server );

>   IF v_server != 'DEDICATED' THEN

>     DBMS_OUTPUT.PUT_LINE


>          ('Tracing only produces valid results for a Dedicated 

>Connection');

>     DBMS_OUTPUT.PUT_LINE


>          ('Tracing *NOT* turned on.');

>     RETURN;


>   END IF;


>-- Time to Turn Trace ON

>    SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(v_sid, v_serial, TRUE);

>

>   DBMS_OUTPUT.PUT_LINE ('Trace Mode is ON for '||v_sid);

>   DBMS_OUTPUT.PUT_LINE ('Output sent to 

>udump/ora_'||p_server_pid||'.trc');

>

>EXCEPTION


>   WHEN NO_DATA_FOUND THEN


>     DBMS_OUTPUT.PUT_LINE ('Cannot find a session for SID 

>'||TO_CHAR(r_sid));

>   WHEN OTHERS THEN


>     DBMS_OUTPUT.PUT_LINE ('Error: '||SQLERRM);

>END;


>/

>

>traceoff.sql

>

>REM Script to turn TRACE MODE off for a selected SID.

>set serveroutput ON size 2000;

>set verify OFF;

>DECLARE


>   r_sid               NUMBER;

>   v_sid               NUMBER;

>   v_serial    NUMBER;

>   v_username  VARCHAR(30);

>   v_status    VARCHAR2(8);

>   v_server    VARCHAR2(9);

>   p_server_pid        VARCHAR2(9);

>

>BEGIN


>--  Accept r_sid Number 'What SID do you wish to trace? - '

>   r_sid := &SID;

>   SELECT s.sid, s.serial#, s.status, s.server, p.spid

>     INTO v_sid, v_serial, v_status, v_server, p_server_pid

>     FROM v$session s, v$process p

>    WHERE s.sid = r_sid

>      AND s.paddr = p.addr;

>   DBMS_OUTPUT.PUT_LINE ('Sid ' || TO_CHAR(v_sid) ||

>                        '  Serial# ' || TO_CHAR(v_serial) ||

>                        '  Username ' || v_username ||

>                        '  Status ' || v_status ||

>                        '  Server ' || v_server );

>-- Time to Turn Trace OFF

>    SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(v_sid, v_serial, FALSE);

>

>   DBMS_OUTPUT.PUT_LINE ('Trace Mode is OFF for '||v_sid);

>   DBMS_OUTPUT.PUT_LINE ('Output is in udump/ora_'||p_server_pid||'.ora');

>

>EXCEPTION


>   WHEN NO_DATA_FOUND THEN


>     DBMS_OUTPUT.PUT_LINE ('Cannot find a session for SID 

>'||TO_CHAR(r_sid));

>   WHEN OTHERS THEN


>     DBMS_OUTPUT.PUT_LINE ('Error: '||SQLERRM);

>END;


>/

>

>

>

> >>> hamcdc_at_yahoo.co.uk 05/09/01 04:30AM >>>

>On a development box, 'alter session' should not cause

>too much harm.  If you're concerned, write a package

>as SYS which turns on tracing for the developers

>session and just grant access on that...

>

>You may want to look at _trace_files_public parameter

>as well so they can see their trace files.

>

>hth

>connor

>

>--- Chris Rezek <crezek_at_dotclick.com> wrote: > I want

>to enable our developers to use tkprof for

> > their own tuning work

> > without giving them general ALTER SESSION

> > privileges.  What's the

> > minimal set of permissions do they need to use

> > explain plan and tkprof?

> >

> > Chris Rezek

> > Oracle DBA

> > --

> > Please see the official ORACLE-L FAQ:

> > http://www.orafaq.com

> > --

> > Author: Chris Rezek

> >   INET: crezek_at_dotclick.com

> >

> > Fat City Network Services    -- (858) 538-5051  FAX:

> > (858) 538-5051

> > San Diego, California        -- Public Internet

> > access / Mailing Lists

> >

>--------------------------------------------------------------------

> > To REMOVE yourself from this mailing list, send an

> > E-Mail message

> > to: ListGuru_at_fatcity.com (note EXACT spelling of

> > 'ListGuru') and in

> > the message BODY, include a line containing: UNSUB

> > ORACLE-L


> > (or the name of mailing list you want to be removed

> > from).  You may

> > also send the HELP command for other information

> > (like subscribing).

>

>

>=====

>Connor McDonald

>http://www.oracledba.co.uk (mirrored at

>http://www.oradba.freeserve.co.uk)

>

>"Some days you're the pigeon, some days you're the statue"

>

>____________________________________________________________

>Do You Yahoo!?

>Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk

>or your free @yahoo.ie address at http://mail.yahoo.ie

>--

>Please see the official ORACLE-L FAQ: http://www.orafaq.com

>--

>Author: =?iso-8859-1?q?Connor=20McDonald?=

>   INET: hamcdc_at_yahoo.co.uk

>

>Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

>San Diego, California        -- Public Internet access / Mailing Lists

>--------------------------------------------------------------------

>To REMOVE yourself from this mailing list, send an E-Mail message

>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in

>the message BODY, include a line containing: UNSUB ORACLE-L

>(or the name of mailing list you want to be removed from).  You may

>also send the HELP command for other information (like subscribing).

>--

>Please see the official ORACLE-L FAQ: http://www.orafaq.com

>--

>Author: Tim Sawmiller

>   INET: sawmillert_at_state.mi.us

>

>Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

>San Diego, California        -- Public Internet access / Mailing Lists

>--------------------------------------------------------------------

>To REMOVE yourself from this mailing list, send an E-Mail message

>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in

>the message BODY, include a line containing: UNSUB ORACLE-L

>(or the name of mailing list you want to be removed from).  You may

>also send the HELP command for other information (like subscribing).






Get your FREE download of MSN Explorer at http://explorer.msn.com


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rachel Carmichael
  INET: carmichr_at_hotmail.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


Received on Wed May 09 2001 - 08:33:08 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US