SQL & PL/SQL :: Find Out IP Address For All Currently Connected Users In V$session
Apr 6, 2010I need a query to find out IP Adress for all currently connected users in V$session.
View 7 Replies
ADVERTISEMENT
PL/SQL :: Dropping Connected Users In Oracle DB?
Mar 27, 2013I want to drop some users in Oracle DB using sqlplus but I am getting error:
SQL> DROP USER test CASCADE;
DROP USER test CASCADE
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected.But when I ran below command to know sessions connected I am not getting any results:
SQL> select sid,serial# from v$session where username = 'test';
no rows selected
PL/SQL :: Killing And Dropping Users Connected From Application
Jul 11, 2012I have a data base for the bank application development. So there might be more than one sessions of the application running on the data base as well as there might be more than one data base sessions being connected from the dbas.
There is refresh procedure to refresh the data base from the baseload scripts ,So for that first we need to disconnect the users (Kill the sessions) and then drop the users then recreate the users and populate the users with the base load data.
I have prepared the killing and dropping user script in the following manner, Most of the times it is working fine and killing the users but some times it is not able to kill the drop the users it is erroring out with the error that can not dorp the users as there are active sessions running ,
set termout on
set echo on
spool Kill_sessions_drop_users.log
DECLARE
v_alt_stmt VARCHAR2(1000) := 'ALTER SYSTEM DISCONNECT SESSION ''';
v_kill_stmt VARCHAR2(1000);
[code].......
--This will kill all the sessions except the current session
CURSOR cur_usr_dtls IS SELECT username FROM all_users WHERE username IN ('ACTIVITI','TP','TPCORR','TPTUX','TPVIEW');
v_dr_stmt VARCHAR2(1000);
BEGIN
EXECUTE IMMEDIATE 'ALTER SYSTEM ENABLE RESTRICTED SESSION';
DBMS_OUTPUT.PUT_LINE('The database has been put in the restricted mode');
FOR v_cur_ssn IN cur_session
[code]........
--Here we are making the processor to wait for 60 mins so that that the killing will happen at the OS label
DBMS_LOCK.SLEEP(60);
FOR v_cur_usr_dtls IN cur_usr_dtls LOOP
v_dr_stmt :='DROP USER '||v_cur_usr_dtls.username ||' CASCADE';
BEGIN
EXECUTE IMMEDIATE v_dr_stmt;
[code]........
Slow Database - V$session Has Lots Of Active Sessions With 00 As Sql-address?
Oct 1, 2012Server:
Windows 2008 R2 x64
Oracle Database 11g Release 11.2.0.1.0 Standard Edition
I recently applied the Oracle® Database Server Version 11.2.0.1 Patch 16 I have several databases running on this box with the same application.Since then I've had lots of customers complaining about slow systems. The server has plenty of RAM availableI also have several other servers all on the same versions, same patch, running the same application with no issues.
Looking at v$session there are often lots of active sessions that have a sql_address of "00". I've never seen this before and I regularly look at v$session, as you can see for below these have been running for a while. The sessions do drop off but I am at a loss as to what is happening.
select username, last_call_et, sid, serial#, user#, status, sql_address, sql_hash_value
from v$session
where username is not null and status = 'ACTIVE'
order by last_call_et desc, sid
[code]...
Security :: How To Find User And IP Address From Oracle Database
Jun 19, 2013Last week we have realized that a user who connects through SQL Developer(as nothing wrong found in application server logs) has made a serious change in the database which created a real mess. The user has done some mischief in some calculations by making some inserts and updates in some important tables in our production database.
How can I find which user or from which IP the change is made.
PL/SQL :: Find Database Server IP Address And Port Number
Aug 21, 2012Is it possible to find the Ip address and Port number of a database server in Oracle 10g version?
actually i wanta to connect it through Putty.
Find Sql Queries Run By Users Through Application?
Jul 10, 2012We have a database version 10.2.0.3 and it is connecting through an application with a schema called HR_OR.
I am using the below query to get sql statements.
SQL> SELECT
2 SST.VALUE CPU, S.USERNAME, S.SID, S.SERIAL#, SQL_TEXT
3 FROM
4 V$SESSION S,
5 V$SQLTEXT_WITH_NEWLINES T,
6 V$SESSTAT SST
7 WHERE S.SQL_ID IS NOT NULL
8 AND S.SQL_ID = T.SQL_ID
9 AND SST.SID = S.SID
10 AND SST.STATISTIC# = (SELECT STATISTIC# FROM V$STATNAME SN WHERE SN.NAME = 'CPU used by this session')
11 ORDER BY 1 DESC, S.SID,T.PIECE;
It is showing same query for all users and I tried to run the quey from sqlpus,getting error [SP2-0552: Bind variable "xxxx" not declared]. So I hv confusion here.
1. How I can see the queries running by users though application.
2. get the exact query run by users and the same query I want to run from sqlplus.
Find Which User / Program Is Using USERS Tablespace?
Feb 27, 2012I have a database in which there are lots of programs running by default user or programs started by some oracle users.
Now, I know that the USER tablespace is growing at very high speed - But I dont know which user/program is causing this growth of USER tablespace.
how to identify the program/user who is responsible for this growth of USERS tablespace.
SQL & PL/SQL :: To Find Time At Which Query Fired By Any Of Users In Database
Oct 25, 2010I have 10 users in my database, I need to find the time at which a query is fired by any of the users in the database. provide the query for this? I think i have to use V$sql,v$sqlarea or v$sql.
View 1 Replies View RelatedNot 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....
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.
Find Thread That Opened A Session In AIX
Sep 3, 2012As stated in "Determining which Oracle database session is being used by a specific Siebel user session [ID 863771.1]":
On the Oracle database side, you can query the V$SESSION table (make sure you are logged in as the tableowner) using the following WHERE clause format:
WHERE PROCESS='XXXX:YYYY'
XXXX = Server PID
YYYY = Thread ID
So querying v$session, sessions opened by windows clients have in the PROCESS column values like "12345:876", where 12345=process id and 876=thread id.
Our application servers are installed in AIX, which is also multithreaded, but in the PROCESS column only process id is stored. Is there any way to find thread id?
Networking And Gateways :: Find Out Users Hostname Who Are Connecting To Database Remotely
Apr 22, 2010i got to find out the users hostname who are connecting to database through remote,i tried with v$session bt it is showing null values in the terminal column.how to find out ?
View 8 Replies View RelatedObject Locked But Can't Find Session / Process Blocking?
Sep 5, 2012I am on database 10.2.0.5, windows x64.
A developer calls me and tells me there are locks on 3 tables, and the locks are not released. So I run this to see which objects are locked by which session
select vlo.object_id, vlo.session_id, vlo.oracle_username, vlo.process
, DECODE(vlo.LOCKED_MODE, 0,'NONE', 1,'NULL', 2,'ROW SHARE', 3,'ROW EXCLUSIVE', 4,'SHARE', 5,'SHARE ROW EXCLUSIVE', 6,'EXCLUSIVE', NULL) LOCK_MODE
, do.owner, do.object_name, do.object_type
[code]...
When I run the query later, I sometimes get another session, but the lock stays. It seems here, that I am catching the same SID on new sessions, but it is probably a different serial # than the original session locking the table rows.
I get nothing from
select * from dba_waiters;
select * from dba_blockers;
Now why are those 3 tables row locked on some rows and I can't find the session responsible? The developer tells me his application crashed and the rows are locked since. So far ( keep in mind I am not a locking expert) the only way I found to release the locks is a DB bounce, its a test DB so no biggy.
Security :: Find Out Locked Table And Session From Database
May 20, 2013I used to find out locked table and session from database....query with SYS user but i want to hand over the user session kill role to location level IT person so i have created one user in database named as rab and i have given "select any table,alter session and Grant dba to that rab user,but with that user they will drop and delete any table also
col owner format a12
col object_name format a25
col ORACLE_USERNAME format a15
col OS_USER_NAME format a15
col OBJECT_TYPE format a15
set lines 140
[code]....
SQL & PL/SQL :: Display Office Address / House Address And Emp ID In Single Query?
Feb 23, 2013I have two tables emp , emp address as below
emp table :
emp_id emp_name,
1 rajesh
2 suresh
emp address table :
emp id emp_addresstype emp address
1 O kukatpally
1 H Hitech
2 O Kolkata
2 H hydar nagar
I need the query to display the Office address & House address & emp id in single query
ex: 1,Kukatally,hitech
2,kolkata,hydarnagr
Server Administration :: Find SID From Current Session As It Make Connection With DB
Dec 27, 2011How to find the sid from v$session of my current session.
I want to ask:
Let say I have open 10 TOAD session and only in one toad session i have fired a sql query ( the rest 9 session are just blank), then the rest 9 status are in active status. Now, how i can figure out which sid ( from v$session ) belongs to which session as all the 9 session having nothing to run.
Quote: My main intention is to find out the current session sid as soon as it make connection with DB.
Server Administration :: Query To Find Hostname For Oracle Session
May 20, 2010How I can find the hostname for an oracle session id?
View 4 Replies View RelatedPerformance Tuning :: How To Find Out Session Consuming High Resource In Past
Mar 28, 2013How can I find out the particular oracle session which was consuming high memory in the past?
I can't get the data in v$sessstat
Unable to get the information in AWR
dba_hist_active_session_history do not have field which indicate memory related information
Shall I concetrate on EVENT in dba_hist_active_session_history which continuosly had sort, direct path read
Or
Locate sql_id from dba_hist_sqlstat with high SORTS_DELTA for snapshots belonging to problematic time period and then using the sql_id query dba_hist_active_session_history
which approach I shall take to find out the session which consumed most memory in the past?
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]....
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 View RelatedSP2-0640 Not Connected
Oct 4, 2007I am using Oracle 10g on Fedora Linux and trying to learn a bit of pl/sql. Here is the piece of code that I today tried to run in the SQL Plus window but it back with an error message saying SP2-0640 Not connected.
set serveroutput on
declare x number;
begin x :=7777;
dbms_output.put_line('x= ');
dbms_output.put_line(x);
end;
Database Is Not Connected To Application
Jan 4, 2013We have 11.2.0.3 RAC on Oracle Virtual Box,OEL 5.8. Storage is ASM on EMC SAN.Even when the database is not connected to application and thus open but idle and none is working on it, and all other virtual machines are powered off, we are still getting a continuous i/o in SAN.Since I am new to RAC setup I need to know the few things...
1.Is it normal behaviour?
2.If yes, what is the reason of this i/o? Is it due to continuous ping on voting disk?..write in OCR? automatic backup of OCR?
Attached File(s)
SAN_I0_Issue.png ( 65.33K )
Number of downloads: 2
RMAN Connected To 2 Database?
May 15, 2010Through RMAN i have to connect to two databases having same name but the databases are at two different nodes.Can RMAN recognise the 2 databases.If yes how? I want to know the command.
View 1 Replies View RelatedProcesses / Sessions Connected To DB After ORA 20 Error?
Dec 28, 2010Is there any way to tell what processes/sessions were connected to a DB after I get an ORA 20error. As you can see from the query results below, I did hit the max value of processes but I would like to know when it happen and who was connected.
I realize I can up the value, but before I do that I want to see what caused this to happen.
SQL> select * from v$resource_limit where resource_name in ('sessions', 'processes');
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL
LIMIT_VALU
----------
processes 221 1000 1000
1000
sessions 223 1020 1105
1105
Exadata :: Tell Version Of Machine Connected To?
Mar 8, 2013How can I tell version of exadata machine I'm connected to? Mean whether X2, X3-2. X3-8 ....
example: machinemodel=X2-2 Full rack A file named config.dat is mentioned in this doc [URL]
but I could not find that file on server I'm connected to (DB Host)
Any command, file I could use to get machinemodel?
Client Tools :: SP2-0640 - Not Connected?
Jun 30, 2006My Oracle DB is working properly till yesterday. When i tried connecting to it today using scott/tiger@orcl its not accepting.So i entered as system/system..it connected
when i tried to select instance_name from v$instance; it is giving me error
SP2-0640: Not connected
RAC & Failsafe :: Check How Many Sessions Connected To Particular Node
May 30, 2013I have few doubts in rac environment
1.In 2 node rac while adding datafile to tablespace if you forget to mention '+'then what will happen whether it is going to be create or it throws an error if it creates where exactly located and other node users how to work on that tablespace .what all steps to perform that datafile is useful for all node users.
2. In Rac environment how to check how many sessions connected to particular node.
3)In Rac any node is evicted due to network failure then after we rebuild the network .Is there any steps to do manually to access the failure node after rebuilding the network or it will automatically available in cluster group which service is perform this activity.
4.While configuring cluster ware you choose voting disk and ocr disk location and which redundancy you will choose suppose if you go for normal redundancy how many disks you can select for each file either one or two?
Express Edition (XE) :: Getting SP2-0640 / Not Connected Error?
Jul 23, 2012I first started database, then gone to option Run SQL command line. After that while creating table it is showing error like "SP2-0640: Not connected."
View 2 Replies View Related