Grant Select On V$session And Gv$session Directly?
Aug 28, 2013Instead of giving permission directly to v$session and gv$session to a developer, Is there any other way to do the same?
View 3 Replies
ADVERTISEMENT
Security :: Grant To Select Session Details
Mar 13, 2006Developers they use Toad and in that they want to use session browser option so that they can see the SQL running and open Cursor,other session specific details...So what grant/ permissions can be given to them so that the Schema they logged on can access those information.
View 4 Replies View RelatedSQL & PL/SQL :: Security - Grant Roles To Session On Time Of Connection?
Jan 18, 2012When I use Forms_ddl('set role My_Role IDENTIFIED BY PWD'); in form's "When-New-From-Instance" to grant a role to a user.
What should I do for the same for a report?
Is there any way to grant roles to a session on the time of connection?
Application Express :: Kill Current Session When Another Session With Same User Starts
Nov 19, 2012I have an urgent requirement to kill an existing session if a new session starts for the same user. I have been reading lot of blogs and posts on the above topic, but could clearly tell me how to do it.
I thought of putting a process in 101 page when login button is pressed to catch this and kill the old session.
Application Express :: (X) Process Taking 60% CPU In Two Session (30% Each Session)
Oct 23, 2012We are using Apex 4.0/Oracle 10gR2/ Hp-Ux, We noticed that there is a process Running in Two Sessions that seems running since 3 Hours using 60% CPU,** 30% given below sql it executes in two Sessions!
declare function x return boolean is begin -- if instr(upper(:F4000_P4651_PLUG_SOURCE),'ORDER BY')>0 then
declare
l_position number := 0;
begin
loop
l_position := instr(upper(:F4000_P4651_PLUG_SOURCE),'ORDER BY ',l_position + 1) ;
exit when instr(upper(:F4000_P4651_PLUG_SOURCE),'ORDER BY ',l_position + 1) = 0 ;
[code]....
PL/SQL :: Alter Session In Select Statement?
Oct 15, 2012I'm using Oracle SQL Developer and querying on date fields. I use this "alter session" statement to set my default date time format to get my datetime values. My question is, is there a way I can incorporate the following statement into my "select" query so that Crystal can read it? I use the statement below before my select statement to query datetime differences. Problem is, Crystal doesn't like the statement below.
Using TO_CHAR( ) and formatting my date fields that way doesn't work when I have to take the difference between date fields to the minute/second level. That's why I use the alter session command below, but I cannot seem to incorporate it with my select statement as a Crystal data source.
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'
Server Administration :: Select A Table Cause Session Hang?
Aug 5, 2011when i modify a column of table tb_hxl_user,just as flowing:
Alter Table TB_HXL_USER Modify CREATE_DATE Not Null
now i select the table TB_HXL_USER,just as flowing:
select * from TB_HXL_USER;
it is hang,and i found the wait event is library cache lock in v$session_waits,why?how can i avoid this.
V$session And GV$session?
Jun 15, 2012explain v$session and gv$session ;
View 3 Replies View RelatedV$session And Jdbc?
Dec 23, 2009I am connecting to Oracle 9i using JDBC in my JSP/Servlets web pages. I am closing all my connections in Finally blocks.
Everything works great during the day with JDBC and Oracle.But after a Web crawler runs through our web server at 3 AM the next morning a v$session check shows several lines of JDBC Thin Client inactive status. find exactly what sql statement or program that could be causing the JDBC Thin Client to show up?
SQL & PL/SQL :: Nls-session-parameters?
Sep 26, 2011I just upgraded my version of my oracle from 9 to 11.2g.When I tried to execute some queries in PL/SQL i keep getting an
annoying error : "ORA-01843 not a valid month."
I realized that if I execute the following query:alter session set NLS_DATE_LANGUAGE=AMERICAN;before every query that I want to execute, then it's executed fine.
I preformed the next queries:
1. select * from nls_database_parameters;
2. select * from nls_instance_parameters;
3. select * from nls_session_parameters;
and I saw that the fields in the "nls_session_parameters" vieware not derived from "nls_database_parameters" "nls_instance_parameters".
How can I derive the parameters from the instancedatabase view into the session view?I want that the "nls_date_language" parameter will always be 'AMERICAN'.
Sql Trace Session
Aug 28, 2012i read some articals of how to trace oracle sessions and i have some question
i use database 10g
trace vs event ??? different between them ????
how to trace individual statement i hope to give me this point in ex: due to i can't get the sql_id ,how to know the sql_is of specify statement??
PL/SQL :: How To Lock Session
Jul 18, 2013i have application where user logs and perform manipulation operation such as insert and update on database table.some time its possible that same user login twice, i just want to hold one session until and unless dml operation perform by another session gets complete.how to accomplish this in database level .
View 6 Replies View RelatedSQL & PL/SQL :: Creating A New Session
Mar 14, 2013How to create a new session from a pl/sql. My requirement is that i will package which should run on new session and execute it.
BEGIN
A; --Calling package
B; --Calling package and this should run on new session.
C; --Calling Package
end;
Now when I will run the this plsql block, then A,C should execute will run in this package only but B will run separate session.Whole idea to have better performance. I heard we can use PRGMA AUTONOMOUS_TRANSACTION, but this is not working.
Selecting From Two Tables - How To Get Value From Session
Jan 31, 2013I have tables SUBJECT(subject_id, name, number) and PS(ps_id, subject_id, student_id). I need to select all from SUBJECT,subject_id and student_id from SP, joined by subject_id, where student_id needs to be read from session. I'm using asp.net with oracle database. How to get the value from the session.
View 2 Replies View RelatedSession Details For A User
Nov 22, 2010I have a user in my oracle database, I would like to know details such as, how many times and by using which tools same user has got logged for past one week.
How do I do that. I have sql which shows the current session either he is logged in, if so then which application or not logged in. But I required the information for past one week How do I get those details.
Getting Different Values For Session Report
Sep 3, 2010query for number of users connected to the database:
select count(*) "Number of users Connected" from v$session where type='USER'; it produces 125
The following query is used to describe where and when the users are connected to the database.
set pages 500
set linesize 750
column box format a20
column username format a7
column program format a20
column os_user format a20
[code]...
It should produce 125. But its showing only 119 users. so around 6 users missing in the list. find why 6 users are not listed..
Alter Session On NLS_DATE_FORMAT Being Ignored
Feb 24, 2011I'm issuing an alter session setting NLS_DATE_FORMAT in a C batch process right after the connection takes place. The format I specify is YYYYMMDDHH24MISS, this is the format used all over the process. In my development environment this works perfectly, but I've had problems in other environments.
CASE A development environment: The process works fine, $NLS_LANG and $NLS_DATE_FORMAT environment variables are not set.
CASE B Test envirnonment 1: The process failed.$NLS_LANG=American_America.WE8ISO8859P1 $NLS_DATE_FORMAT environment variable is not set.For some reason the $NLS_LANG variable seems to have more weight than the alter session command.. why? The process works fine after setting $NLS_DATE_FORMAT to the desired format.
CASE C Test envirnonment 2: The process failed. $NLS_LANG and $NLS_DATE_FORMAT environment variables are not set. Can't get it to work here. why?
Two Blocked Session - Not Getting Sqltext
Nov 24, 2011I have two blocked session
select 'SID ' || l1.sid ||' is blocking ' || l2.sid blocking from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2;
BLOCKING
--------------------------------------------------------------------------------------------------
SID 142 is blocking 241
SQL> select sid,SERIAL# from v$session where sid in (142,241);
SID SERIAL#
---------- ----------
142 40643
241 16500
Now if I am trying to find what exactly getting executed under those two sid I get info for only one.
SQL> select a.SQL_ID,a.SQL_TEXT from v$sqltext a, v$session b where a.ADDRESS=b.SQL_ADDRESS and a.HASH_VALUE=b.SQL_HASH_VALUE and b.sid=241;
SQL_ID SQL_TEXT
------------- ----------------------------------------------------------------
d11mmj6wj4046 DELETE FROM OTABATCHEPTTABLE WHERE EPT_ID=0001471142180000002
BUT FOR OTHER SID I DONT HAVE ANY ENTRY IN SQLTEXT
SQL> select a.SQL_ID,a.SQL_TEXT from v$sqltext a, v$session b where a.ADDRESS=b.SQL_ADDRESS and a.HASH_VALUE=b.SQL_HASH_VALUE and b.sid=142;
no rows selected
FIND OUT WHAT EXACTLY ITS RUNNING AND WHY I AM NOT GETTING THE SQLTEXT FOR A SID?
Security :: PL/SQL Compilation Session
Oct 18, 2010Is there any way to identify who has compiled a specific PL / SQL stored program?
View 3 Replies View RelatedSQL & PL/SQL :: Restricting Form To Only One Session?
Feb 6, 20121. i want that if a form is opened in one user session. it will not be available to other users.
2. if a user executes a procedure. it should not be available to other users to execute until the first execution completes.
SQL & PL/SQL :: Finding Session IDs Initiated By Particular Job
Feb 7, 2012I want to track the session id that has been initiated by a particular job (I have job ids with me). Which dictionary views I can get this result.
View 1 Replies View RelatedSQL & PL/SQL :: User Session Details
Sep 14, 2010How can I find out a user session/connection details like ( his last connection time, his activity etc)
Here is the issue, a old dba_user_id who physically no longer exists is not removed from the database. At some time, a session is opened under his user_id and some activity runs and closes the session. So, checked scheduled jobs under him and disabled them. But not sure whether the scheduled jobs are creating a session under his user_id or some thing else.
I checked in v$session, but since I am not sure at what time his session opens, so I am not able to get his session details.
Not Able To Find Blocking Session
Jan 10, 2013I am working on oracle 11g and AIX.We were facing some blocking issues.For more investigation on the blocking sesssion we checked active session history of the database.Like session 'A' was blocked by session 'B' ...Now if we check infromation about sessions 'A' it is showing that this session was blocked by session 'B'.
But when we checked for session 'B' informaiton ,it is not showing any thing...from where we can get infromation about session B and in what all scenarios active session history does not show information about blocking session....
PL/SQL :: Queries Executed By A Session
Jul 11, 2012I am trying to trace the SQL statements executed against a particular database user. I don't have AUDITING enabled and I am using Oracle 11g.I have the following query :
SELECT
S.MODULE,
SQL_TEXT ,
S.EXECUTIONS
FROM
[code].....
But if multiple users running the 'APP.EXE' are connected to the same db user, I am not able to understand which OS user executed which query. So I tried to join with V$SESSION view to get the user details.
SELECT
S.MODULE,SQL_TEXT ,SN.OSUSER, SN.MACHINE, S.EXECUTIONS
FROM
SYS.V_$SQL S,
SYS.ALL_USERS U,
V$SESSION SN
WHERE
S.PARSING_USER_ID=U.USER_ID
AND UPPER(U.USERNAME) IN ('USERNAME')
AND (UPPER(S.MODULE)='APP.EXE')
AND S.SQL_ID=SN.SQL_ID
ORDER BY S.LAST_LOAD_TIME
But this doesn't seems to be working(In my case it didn't return any rows) I have also tried the following
select S.SQL_TEXT,S.MODULE,S.EXECUTIONS,SN.OSUSER,SN.MACHINE,SN.SID, U.username,U.user_id from
SYS.V_$SQL S, v$open_cursor oc,SYS.ALL_USERS U,V$SESSION SN
where S.PARSING_USER_ID=U.USER_ID
AND UPPER(U.USERNAME) IN ('USERNAME')
AND (UPPER(S.MODULE)='APP.EXE')
and oc.SQL_ID=s.SQL_ID AND SN.SID=OC.SID AND SN.SADDR=OC.SADDR;
but I am not sure whether this is giving the right results. So, I have the following questions
1) How do I get the queries executed by each session?
2) The EXECUTIONS column of V_$SQL seems to the executions from all the sessions. How do I know the number of times a particular query is executed by a session?
3) How long a record about a query will be stored in V_$SQL? When do Oracle delete it from the view?
Session Hang Due To Foreign Key?
Jun 21, 2012Version 10.2.0.4
CREATE TABLE T1 (I INT PRIMARY KEY);
CREATE TABLE T2 (I INT PRIMARY KEY, J INT);
ALTER TABLE T2 ADD CONSTRAINT T2_FK FOREIGN KEY (J) REFERENCES T1(I);Session 1
INSERT INTO T1 VALUES (1);Session 2
INSERT INTO T2 VALUES (1,1);Session 2 hangs. Why ?
Configuration Duplicate Session ID
Oct 29, 2012I am getting a duplicate SID (ABC1 (2)) on UNIX. I have checked all the possibilities how to remove it but unable to find it. As you you see below:
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
ORATAB:
================================================
ABC1:/oraclehome/oracle10g:Y
================================================
/oraclehome/oracle10g/bin/tnslsnr LISTENER_ABC1 -inherit
================================================
SID_LIST_LISTENFCATDR =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /oraclehome/oracle10g)
[Code]...
Result:
ls -lad /orahome/ora10g/dbs/initABC1 (2).ora
ls -lad /orahome/ora10g/dbs/initABC1 (2).ora ; echo ;
/bin/sh: 0403-057 Syntax error: `(' is not expected.
--------------------------------------------------------------------------------------------------------------
what to need to do to avoid this duplication? Is it something wrong with Oracle Listener.ora as it have two listeners (ABC and ABC1) that might be causing duplication?
Oracle 11g R2 - Session ID For Database
Dec 1, 2012I am installing oracle 11g R2 (Oracle Database 11g Release 11.2.0.3.0 - 64bit Production) on linux box, by the time the SID provided was 9 characters like oimdbdevd. The completion of installation showing as oimdbdevd for sid. But the init.ora file and database table v$database are showing only oimdbdev.
It mean the SID will allow only 8 characters.
Server Administration :: Get Rid Of A Session?
Jun 9, 2010we have a software which keeps a check on v$session table so that a client can not have more than one sessions running simultaneously with the server. But if a client program terminates unexpectedl, it's session information stays in the v$session table, which prevents us from opening a new instance of the client program. Using
ALTER SYSTEM KILL SESSION 'SID, SERIAL#'
doesn't work since it takes ages to rollback and release the locks. And the session stays there with a 'KILLED' status.I want this session out of the v$session table and instantly.
Session Inactive State?
Jul 1, 2012want to know the following
1. when the oracle session changed from active to inactive?
2. what is the time for active session?
3. session is changed to inactive from active. but it is still showing in v$session.
4. in v$session, can i see the ipaddress of client machine ?
How To Find CPU Usage By Each User Session
Sep 7, 2011I can get "CPU used by this session' value from v$sesstat. That is the value in centiseconds used by each session. How to convert the value into a percentage of total CPU resource ?
Or there are some other ways to gain the value. i.e. Percentage of Total CPU usage for each Oracle session.