PL/SQL :: Killing And Dropping Users Connected From Application

Jul 11, 2012

I 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]........

View 4 Replies


ADVERTISEMENT

PL/SQL :: Dropping Connected Users In Oracle DB?

Mar 27, 2013

I 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

View 3 Replies View Related

Dropping Users And Recreating?

Jun 18, 2012

we are refreshing data base for our application from the base load,Every time when ever there is refresh required we need to drop the users and recreate every thing from the base load. For this we need to kill the sessions and drop the users ,Putting the instance in the restricted mode and refreshing the db.Some time when ever During the killing and dropping process there are some errors like you can not drop the users which is currently connected .

set termout on
set echo on
spool Kill_sessions_drop_users.log
DECLARE

[code]...

how to get rid of this

View 6 Replies View Related

SQL & PL/SQL :: Find Out IP Address For All Currently Connected Users In V$session

Apr 6, 2010

I need a query to find out IP Adress for all currently connected users in V$session.

View 7 Replies View Related

SQL & PL/SQL :: Dropping Users - Queue Tables

Dec 9, 2010

I am trying to drop a user but i get the following error

*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables

A couple of questions on this error:

- I did a search on the forum and the thread [URL] appears to have a solution to it by using

DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'DEF$_AQCALL', force =>TRUE);

What i am not sure of is which queue will the above statement drop if run as sys and there are multiple schemas with different schema names but with the same queue name?

- We recently rebuilt the database server and prior to the rebuild i have always managed to drop a user without the above error. Is it likely that some setting somewhere is changed?

View 2 Replies View Related

How To Store Space After Dropping Objects And Users To Maintain Storage Requirement

Jan 11, 2013

There is a scenario: I dropped all objects of 3 users and dropped other 2 users and then I checked the space from dba_segments it reflects decreased space which is perfectly fine. Problem is that when I checked the space of physical datafile on disk it remains same. How can I restore or shrink or regained the space after dropping objects and users to maintain my storage requirements.

View 1 Replies View Related

Forms :: Killing Inactive Session Through Oracle Application Server

Mar 5, 2013

I am trying use host command in Oracle forms. I get the process id as input from the user and on clicking ok the form should kill the session related to process id.

PS: Users will be entering only frmweb.exe process id which are inactive.

cmd := 'CMD /C taskkill /F /FI /pid 'process which is got as input' /IM frmweb.exe'; host(cmd)I also tried by writing the above command into a .bat file in application server.When trying to execute the bat file it din work. But when tryin to run the bat file by double clicking in Application server the session was killed.

On executing the above I am unable to kill the process. I would also like to know whether the host command was successful or not. Orakill and alter session are working but I don want to use it.

View 2 Replies View Related

Database Is Not Connected To Application

Jan 4, 2013

We 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

View 2 Replies View Related

Application Express :: Display List Of All Users On Page / Accessing Or Using Application

Mar 23, 2013

i want to display a list of all users on a page those currently accessing or using the application. How do i accomplish this requirement?

View 1 Replies View Related

Application Express :: Application Locking - Force Logout All Current Users And Prevent Login

Jun 12, 2012

I have an application that I'd like to prevent activity in after a certain time of day, say 3pm. Is there any way I can force logout of all current users and then prevent re-login until 9am the next day (short of deleting all of their login credentials and then recreating them the next morning)?

View 7 Replies View Related

Server Administration :: Monitoring User Session And Killing It?

Jun 8, 2010

I am using TOAD to monitor user sessions. I have a user who uses nearly 80% of Overall resources in the mornings. He calls me daily and say that his work is done. But even then the TOAD shows that he is using same resources for some time.

My question is can i kill the session once he confirms his work is done OR do i need to wait till the redologs are cleared.

View 9 Replies View Related

Find Sql Queries Run By Users Through Application?

Jul 10, 2012

We 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.

View 10 Replies View Related

Audit Dml On Few Table For Non Application Users

Oct 12, 2013

I want to audit  dml on few table for non application users.can I omit only application users from audit so that whenever new user is created we need not to add audit for user.do i have to create audit logon trigger for this which check first the application user names from table and if logged user is not application user auditing will start for it.

View 4 Replies View Related

Application Express :: Restricting Access To Users

Dec 22, 2012

restrict access to page or component for particular user. when that user login , restricted page should not be visible to the user..

APEX Verison :4.2

View 1 Replies View Related

Application Express :: Adding Users To A Database?

Nov 7, 2013

I have an application that I am adding users to as end users. How do I assign them to a particular application? I am using Apex 4.1, Oracle 11g. 

View 10 Replies View Related

Application Express :: Implementing DMZ For Intranet And Internet Users

Aug 28, 2012

We are planning to use an Application Express application for intranet and internet users. We want to implement a DMZ configuration for this application.

Is there any good guide to implement it?, we want to use Oracle Http Server to access Oracle Application Express.

View 2 Replies View Related

Application Express :: How To Access Data Of All Users In A Page

Jan 23, 2013

I have installed APEX on my local machine, created a workspace, and added a few users with roles.

I am creating an application, similar to a Library app, where I have a table that stores Books information (BOOK_ID, BOOK_NAME, etc). I'd like to create another table BOOK_USER with two columns, BOOK_ID and USER_ID. BOOK_ID would refer to the BOOKS table, and I want USER_ID to refer to the table that stores user details of the workspace that I am using. The data entry would happen from a page that has two LOVs, one for book name (using BOOK_ID as the value) and another for user name (USER_ID, or whatever field APEX uses to store user's ID). Can I do that in APEX? I've found ways to access and authenticate a user for a page, but how to get data of all users in the workspace? In other words, how to get all usernames in a LOV?

View 5 Replies View Related

Application Express :: Creating Functionality For Changing Users Password

Mar 12, 2013

I'm trying to create a functionality on my application that lets the user change their password whenever they want or when the password gets expired.

But to do that, first I need a Branch to the page "Password Change" whenever the login procedure returns "Password Expire", but when the user has an expired password he can't access none of the pages of the application.

My other problem is:

Even if I can get the user to be redirect to the "Password Change" page, I would need a function that validate his current expired password, because in order to alter his own, he would need to inform his current password and the new one he desires. If I was using Apex Authentication that would be very easy I think, but I'm obligate to use Database Account Authentication.

View 4 Replies View Related

Application Express :: Raise Warning When Password Due To Expire - Oracle Database Users

Aug 23, 2012

Using Apex 4.1 and custom authentication based on Oracle Database users.

I want to be able to show a warning immediately after a user logs in if their password is due to expire in xx days. Oracle raises a warning (ORA-28002) but I don't know how to handle that from the standard Apex login page.

View 1 Replies View Related

Application Express :: Store Encrypted Numeric Value And Make Unencrypted Value Visible To Just Some Users In Apex

Jul 3, 2013

I'm looking for a way to store an encypted numeric value in one field in a table (so that it appears encrypted even to a DBA) and to display the unencypted value in Apex forms and interactive reports for some users but not others.

View 4 Replies View Related

SP2-0640 Not Connected

Oct 4, 2007

I 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;

View 1 Replies View Related

RMAN Connected To 2 Database?

May 15, 2010

Through 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 Related

Processes / Sessions Connected To DB After ORA 20 Error?

Dec 28, 2010

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

View 6 Replies View Related

Exadata :: Tell Version Of Machine Connected To?

Mar 8, 2013

How 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?

View 9 Replies View Related

Client Tools :: SP2-0640 - Not Connected?

Jun 30, 2006

My 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

View 6 Replies View Related

SQL & PL/SQL :: Dropping A Snapshot

Apr 13, 2008

I am unable to drop a snapshot , i tried even from sys, it is giving the following error,

drop snapshot adm.dup_resource_status
9:46:29 ORA-08103: object no longer exists

but, when i try to create a new snapshot with the same name

CREATE MATERIALIZED VIEW ADM.DUP_RESOURCE_STATUS
..............
I get
ORA-12006: a materialized view with the same user.name already exists.

View 31 Replies View Related

PL/SQL :: Dropping A Partition?

Jul 7, 2012

Do i have to create indexes again if i drop a partition on a table?

View 6 Replies View Related

PL/SQL :: Dropping Partitions At Once

Nov 2, 2012

ALTER TABLE table_name DROP PARTITION (partition_1000);
ALTER TABLE table_name DROP PARTITION (partition_1001);
...
.........
......
ALTER TABLE table_name DROP PARTITION(partition_1320);(b

it is a delta partition,so trying to remove 320 partitions at once in pl/sql developer for a single table.

Like this i have to remove for more then 15 tables one by one, will this effect the database like filling up the archinve log destination by writing more logs.

kind of problems that i am going to face , as i am doing it on the production box directly.

View 5 Replies View Related

RAC & Failsafe :: Check How Many Sessions Connected To Particular Node

May 30, 2013

I 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?

View 9 Replies View Related

Express Edition (XE) :: Getting SP2-0640 / Not Connected Error?

Jul 23, 2012

I 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







Copyrights 2005-15 www.BigResource.com, All rights reserved