I have an assignment, i am suppose to disconnect all the users that are blocking sessions for over three hours. Below is my code which is not working
Create or replace procedure b_user IS
Declare
ssid number;
serial number;
Begin
[code]......
I am using oracle 10g as server in my lab. I faced some problems initially, but later after increasing the USERS tablespace it is working fine.
But there is still one problem. During the query execution some queries will be blocked and it doesn't leave any consequent queries to execute from the same user.
The blocked sessions will be displayed in the admin page under blocking sessions link. There is a option to kill the session. But when i do that, it affects all the users and the connection will be lost to all the users. again I have startup the database from beginning.
My statspack reports are sometimes showing figures such as this from the Foreground Wait Events section:
Avg %Total
%Tim Total Wait wait Waits Call
Event Waits out Time (s) (ms) /txn Time
---------------------------- ------------ ---- ---------- ------ -------- ------
PL/SQL lock timer 1,430 100 6,982 4883 0.0 18.0
enq: TM - contention 3 0 6,438 ###### 0.0 16.6
db file sequential read 310,385 0 1,765 6 3.5 4.6
TCP Socket (KGAS) 12,593 13 180 14 0.1 .5
db file scattered read 23,673 0 87 4 0.3 .2
log file sync 19,409 0 82 4 0.2 .2
ksfd: async disk IO 1,680 0 29 17 0.0 .1
[code].....
The TM lock that occurred 3 times appears to be disastrous.Historically, this could be caused by missing indexes on foreign key columns.
How can I be alerted when this event occurs, so that I can do some real-time investigation into the sessions and the SQL that hit it? I suppose I could schedule a job to query v$lock/dba_waiters/dba_blockers every few minutes, but is there a better way? Any standard edition scripts for this?
The following link states
Quote:
SESSIONS_PER_USER
Each instance maintains its own SESSIONS_PER_USER count. If SESSIONS_PER_USER is set to 1 for a user, the user can log on to the database more than once as long as each connection is from a different instance.
[URL].....
Of course the following is not working even when resource_limit is TRUE
ALTER PROFILE DEFAULT LIMIT SESSIONS_PER_USER 2;
How can I restrict a user to have limited sessions say 2 sessions across 4 node cluster? Presently I am checking the sessions logged in using sql+ and no connection pooling of front end etc. is involved.
I need to trace all sessions created from specific user and specific machine . Got some code from internet and customised .
CREATE OR REPLACE TRIGGER SYS.enable_trace_trigger
AFTER LOGON ON DATABASE
DECLARE
[Code]....
I have yet not created it on db .
I'm sure you are all familiar with proxy users, they've been around since 9i: orcl> create user low identified by low;
User created.
orcl> create user high identified by high;
User created.
orcl> grant dba to high;
Grant succeeded.
orcl> alter user high grant connect through low;
User altered.
orcl> connect low[high]/low
Connected.
orcl> sho user
USER is "HIGH"
orcl>
Is there any way that I can find out which of the current sessions was proxied, and through what user? I know that from within the session I can query my userenv context and find out, but I can't see how to do it otherwise. It must be possible: the audit trail records both the real user and the proxy user.
Is there any setting on oracle that allows to disconnect users automatically and display a message to users such: "Try a new connection to the db in 30 minutes"?
View 7 Replies View RelatedI am running a pro*c its working fine but my connection gets disconnect to my db server so i want to continue my proc if connection between my machine & server reestablish again weather it starts from start or from where it was stooped because of connection break
View 9 Replies View RelatedRecently I came across a problem that 'ORACLE Client Side Disconnect every 5mins in app server' these days.
Situation: We have 3 APP servers, which was configured the same OS and ORACLE Client version and 1 DB server. 2 app servers ORACLE Client Side Disconnect every 5mins.but it's ok in another App server.
1. both Oracle SQL PLUS and PLSQL have this problem.
2. SQLNET.EXPIRE_TIME is the same for 3 APP
3. 2 APP server are virtual machine and balance server
3. APP servers : OS: 2003 Server ORACLE 9i
DB server : ORACLE 11g
4. I don't think it's DB server problem because one App server hasn't this problem
I want to disconnect and connect sql prompt in loop using script file.But whenever I try to do this it is giving error,check the below code.I save below code into some file and call that file using @"filename.sql", it won't work.
Basically I want to run one procedure in loop, and in each loop counter I want to disconnect the session and connect again. Becaue CONN and DISC not working while calling through @"..."
DECLARE
BEGIN
FOR N IN 1..5 LOOP
DISC
CONN SCOTT/TIGER@TEST;
EXEC MY_PROC;
END LOOP;
END;
I 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....
I want to create notification/ or triggered mail to my client and my dba group which will occur while session and user has been blocked. I want to create this procedure in 12c OEM.
View 3 Replies View RelatedI am using Oracle Application Server 10gAs and Linux 4.
Our Test Application is access by users using 7778 port,i want just few users to access my test application(just admin user) and all other user should not get access to the application,even if they specify proper URL and port number.
Some access control policy using oracle application server 10gas.
how to avoid blocking locks.
View 27 Replies View RelatedI am new to performance tuning. One procedure is blocking and another is locking it.
SQL> @glockers
Oracle OS
INSTANCE SID TY Usernam SERIAL# SPID Process LMODE REQUEST
---------- ------ -- ------- ---------- ------------ ------- ------------------------------------ ------------------------------------
[code]...
writing a script where there is a blocking lock for more than 30 minutes ?
View 2 Replies View RelatedOne issue happens frequently in My database,Logwr process blocking other session,when i checked blocked session in v$session wait_class was commit and event was logfile sync,
EVENT# WAIT_CLASS
------ ----------------------------------------------------------------
116 Commit
116 Commit
116 Commit
116 Commit
116 Commit
116 Commit
115 Configuration
116 Commit
116 Commit
116 Commit
116 Commit
EVENT# WAIT_CLASS
------ ----------------------------------------------------------------
116 Commit
116 Commit
116 Commit
116 Commit
116 Commit
116 Commit
116 Commit
116 Commit
116 Commit
116 Commit
116 Commit
EVENT# WAIT_CLASS
------ ----------------------------------------------------------------
As per this we predicate Logbuffer Might be full so i just to reboot server,Note server was 32 Bit i can allocate only 1GB of SGA SYStem not allows me to increase SGA further. Server reboot is proper solution?
I 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.
I am using JDBC to run a few queries from my Java program (multi-threaded one).I am facing an issue where a select statement is blocking a delete statement. From the java code point of view, there are 2 different threads accessing the same tables (whith different DB connection objects).
When the block occurs (which i was able to find out from the java thread dump that there is a lock on oracle), the below is the output:
SQL> SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS')
2 || ' User '||s1.username || '@' || s1.machine
3 || ' ( SID= ' || s1.sid || ' ) with the statement: ' || sqlt2.sql_text
||' is blocking the SQL statement on '|| s2.username || '@'
4 5 || s2.machine || ' ( SID=' || s2.sid || ' ) blocked SQL -> '
6 ||sqlt1.sql_text AS blocking_status FROM v$lock l1, v$session s1, v$lock l2 ,
7 v$session s2,v$sql sqlt1, v$sql sqlt2
8 WHERE s1.sid =l1.sid
9 AND s2.sid =l2.sid AND sqlt1.sql_id= s2.sql_id
AND sqlt2.sql_id= s1.prev_sql_id AND l1.BLOCK =1
10 AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2;
[code]...
From the above it can be seen that a select statement is blocking a delete. Unless the select is select for Update, it should not block other statements is not it ?
I have set my processes and session value in my database as 1000 and 1248 respectively. I am using 11.2.0.1 but when I restart my DB it showing me sessions=1524...its too high value then what I set.I don't know why it showing higher value than the value I set...
ALTER SYSTEM SET processes=1000 SCOPE=SPFILE;
ALTER SYSTEM SET sessions=1248 SCOPE=SPFILE;
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
Using parameter settings in server-side spfile /u02/app/oracle/product/11.2.0/db_1/dbs/spfileeng11g02.ora
System parameters with non-default values:
processes = 1000
sessions = 1524
And one more thing is there any recommended formula for calculate sessions in 11g? [ I found one formual from google:
SESSIONS derived: (1.1 * PROCESSES) + 5
One of our DB server is high on CPU utilization, as per the AWR we found couple of queries are running many times and taking huge CPU, hence we need to know from which user these queries are runnings.find the session information as per the sql id?
View 9 Replies View RelatedIn our databace there are around 300 sessions were in inactive state.
Database is not releas the sessions.
our db shows more than 200 INACTIVE Sessions ; and the DBA plans to reboot the db to get rid of these sessions . Can we not KILL these sessions and avoid the reboot ?
View 4 Replies View RelatedI'm using Toad 11.6, I can see about 156 sessions for the database in the session browser but when I do a select from v$session, only 40 sessions are showing up. I was able to look at all the sessions until yesterday. I tried several views like v$session, v$open_cursor etc, but only 60 sessions show up. I'm connected to the database using same user login yesterday and this morning.. May be this user had some privileges revoked last night??If so is it possible to limit sessions in these views (v$session etc) but it's strange that I can see them in the session browser in Toad. 'm very confident that there are 156 sessions in the database but it's just that I'm not able to see them in the v$session etc views. I need to troubleshoot a session but it is not showing up in any views.
View 9 Replies View RelatedOracle database 11g Expression Edition 11.2.0.2
I want to change the the number of processes to 150 (default is 100) I did this by issuing the below command.
alter system set processes=150 scope=spfile;But why that modified value is not being reflected?
select name, value from v$parameter where name in ('sessions','processes','transactions');
NAME VALUE
-------------------- -----
processes 100
sessions 172
transactions 189
Is 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
how to avoid oracle deadlock with two sessions. Here is my example, We use Select query with For Update in order to get the unique number from a table. When one user has accessed this query, while the other user tries, System gives a Performace slow and when checked it is due to locking of this select query with For update.
We don't want to use For update WAIT or FOR UPDATE NOWAIT since these will result in missing of number.
why are there in the Administration --> --> Session 2 entries for 1 database connection (here with benthic golden).But if I login in a browser, there is only 1 entry for this database connection
View 3 Replies View RelatedI have a RAC on 9i and I want to know the number of active sessions that it's executing the same sql sentence.I'm using this sql but I'm not sure that this query give me te correct result.
SELECT se.inst_id,se.status,se.SQL_HASH_VALUE,se.USERNAME,substr(sq.SQL_TEXT,1,50) SQL,count(*)
FROM GV$SESSION se, gv$sql sq
WHERE se.STATUS='ACTIVE'
and se.username is not null
[code]...
I create table in session1:
create table marco_tmp_270511
as
select 5 as t from dual
Then I log in session2 and perform query to
select * from marco_tmp_270511 and receive 5 in column t.
Then I return to session1 and perform:
delete marco_tmp_270511;
insert into marco_tmp_270511
select 99 as t from dual ;
select * from marco_tmp_270511;
Then I go again to session2 and perform query to
select * from marco_tmp_270511
and still receive 5 in column t. How do I synchronize data between sessions?