SQL & PL/SQL :: To Find Time At Which Query Fired By Any Of Users In Database

Oct 25, 2010

I 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


ADVERTISEMENT

SQL & PL/SQL :: How To Identify Client Fired Query Still Connected To Database

Dec 21, 2011

I need to get a way to find, The client fired the SQL Query is still connected to the database or not. For e.g.

I fire an SQL query using SQL Developer, Query approximately takes 5 mins to complete. As soon as I fire the query SQL devloper hangs and I close it using task manager.

Is there any way I can identify such session where SQL developer fired the query is terminated but the query fired is still running.

I have to identify such Orphan session and terminate it.

View 3 Replies View Related

Security :: Check Users / IPs Who Connect To Database In Specific Time

Jan 22, 2013

Is there a way where I can check users/IPs who connect to the database in a specific time 2 days ago?

View 5 Replies View Related

Networking And Gateways :: Find Out Users Hostname Who Are Connecting To Database Remotely

Apr 22, 2010

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

SQL & PL/SQL :: How To Find The Query Which Is Taking Most Time

Jul 4, 2011

I want to know how I can find which query is taking more time , for example some query's are run from unix, java and from toad,sqlplus. and one query is taking much more time to execute, so how i can get that query and all the details.

View 2 Replies View Related

SQL & PL/SQL :: Query To Find The Time Difference

Aug 21, 2010

I need to query a table to get the time difference based on different dates.

SAMPLE DATA:

JOB ID start_time end_time
1 201008190056 19/08/2010 01:23:12
2 201008190123 19/08/2010 01:50:12
2 201008200045 19/08/2010 01:50:12
3 201008070345 7/08/2010 04:50:12
3 201008070345 7/08/2010 04:50:12
1 201008070003 7/08/2010 00:30:12

in the above, for job 1 ,on 19/08/2010 the time difference should be calculated as

01:23:12-00:56:00 and the difference should be in minutes.

for end_time. i can't take the substring as the length of the date varies for 19 and 7. In case of start date, the time has to be in format, hh:mm:ss, to calculate the difference.

View 2 Replies View Related

Forms :: Execute Last Query Fired On Form When Someone Press CANCEL Button

Nov 8, 2005

i am writing a code on form "CANCEL" button which mean to cancel the current user action .now if the last action was execute_query so when user click the cancel button so my logic should capture the last query fired on form through ":SYSTEM.LAST_QUERY" and then execute the last query through EXECUTE_QUERY but my problem is that how i execute my last_query through execute_query.

View 21 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

Find Which User / Program Is Using USERS Tablespace?

Feb 27, 2012

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

View 1 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

Forms :: Lock Record When Two Users Updating Existing One At Same Time

Aug 22, 2013

I am using Forms 6i and developed a Master Detail Oracle Form for Oracle EBS 11i application.

I was asked to write a pl/sql program so that.. When a user updating an existing record at the same time another user query the same record and try to update the same time, the record should be locked and a message should popup saying the record is being updated by another user.

View 2 Replies View Related

Server Administration :: Get Details Of SYS / SYSTEM Users Logon And Logoff Time

Aug 13, 2012

I want to get the details of the SYS/SYSTEM users logon and Logoff's time.

Steps 1:
=======
SQL> SHOW PARAMETER AUDIT

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string C:ORACLEPRODUCT10.2.0ADMIN
DB10GADUMP
audit_sys_operations boolean FALSE
audit_trail string NONE
SQL>

Step 2:
=======
Set the Necessary parameters:
-----------------------------
alter system set audit_trail=db scope=spfile;

Step 3:
=======
Shutdown the DB

Step 4:
=======
Restart the DB

Step 5:
=======
For auditing the Session. Fire the below command.

AUDIT CREATE SESSION;

Step 6:
=======
select DISTINCT USERNAME,os_username,timestamp,logoff_time
from DBA_audit_session;

When I fire the above query, I couldn't get the SYS user's LOGOFF time. But for rest of the users, I am getting.

View 12 Replies View Related

SQL & PL/SQL :: How To Find Time Offset For Given Time

Sep 23, 2010

My time zone has the offset of 2 hrs during summer and 1 hr during winter.If I want Oracle to tell me what was offset for particular day for example I want to know the offset for February 01, 2010 and August 01, 2010, is it possible?

View 1 Replies View Related

SQL & PL/SQL :: Get End Time By Query Between Start Time And Duration

Jun 16, 2013

I Have three field and first field for START TIME ,Second END TIME & Third DURATION AND Putting START TIME AND END TIME i am getting duration in minutes by using code

""SELECT TO_CHAR
(TRUNC (SYSDATE)
+ (TO_DATE (:T_DONATION_END_TIME, 'HH24MI') - TO_DATE (:T_DONATION_START_TIME, 'HH24MI')),
'HH24MI')
INTO :T_DONATION_DURATION
[code].......

View 5 Replies View Related

Create As Many As 100 Database With Upto 100 Users (max) For Each Database As Client

Aug 25, 2010

I have CPU based Oracle server license. In this case as per license policies of Oracle I can have unlimited database with unlimited users connecting it.

I need to create as many as 100 database with upto 100 users (max) for each database as client. What is the recommended hardware for this server? CPU/ RAM/Harddisk, recommended chipset on the motherboard (in case of database storage capacity required is not high typically - 20 GB is OK per database - average), redundancy is critical, also Oracle database vault is going to be installed; as security is of prime importance between users of different database. Which specific server models from specific vendors like IBM/ Dell/ HP are good ones for this purpose?

View 2 Replies View Related

SQL & PL/SQL :: How Many Users Can Connect With Database

Apr 23, 2010

I want to know what will be the performance of my DB before loading data and how many users can connect with my database.

View 7 Replies View Related

SQL & PL/SQL :: How To Find Out Time Variance Dynamically

Mar 28, 2012

I wanted to calculate the time variance dynamically as below.I have one table called process_status where we can see the process name, start time, end time and the status .

1. Now i wants to calculate the duration(end_time - start_time) in hh:mi:ss format and i got the output using below query.

select name, start_time, end_time, status, to_char(trunc(sysdate) + (end_time - start_time), 'HH24:MI:SS') duration from process_status ;

I got currect oupput but i am stuggling with the below step.

2). Actually the process expectaiotn time is 2 hours only(fixed time). Now i wants to calculate variance b/w expected time and the duration time(end_time - start_time)

i.e variance = expected_time - (end_time - start_time ) ;
here expected time is 02:00:00(fixed).

I tried using with to_char and to_date function but no luck.

View 7 Replies View Related

SQL & PL/SQL :: Find Lock On Particular Date And Time?

Dec 18, 2012

we can easily find out the locks which are currently active session.

Is any other way to find out locks on particular date and time using data dictionary?

For example, we have to find whether TT_objects occured the lock or not on ON yesterday.

View 3 Replies View Related

How To Limit Users From Connecting To Database

Aug 14, 2012

i have a database nd only 10 users must allowed to access db at any point of time and sessions per user must be 1.that is only 10 users must be able to access the database.

View 6 Replies View Related

SQL & PL/SQL :: How To Find Date And Time Of A Column In Table

Jun 20, 2013

How to find date and time of a column in table ?

say for example there is a column called 'date_txn' in a table .When i select that particular column it display output as 'June 2013'.But i want output "with date and time".

View 10 Replies View Related

SQL & PL/SQL :: Find The Time Of A Record Inserted Into Table

Oct 20, 2010

I have a table which is getting populated from a source. I dont have a time-stamp column in the table. Is there anyway to find the time when a record was inserted into the table?

View 3 Replies View Related

PL/SQL :: Find Duration By Subtracting Start And End Time?

Jun 25, 2013

Version: 11.2 I am not an SQL expert. I am trying to subtract the start time and end time and thereby find the difference between these two times. In the below example , the difference between start_time and end_time is around 52 minutes (6:15 PM to 7:07 PM)I tried some stuff to find the difference below. But it didn't work out .  

SQL> create table t (start_time date, end_time date); Table created. SQL> desc t Name Null?    Type ----------------------------------------------------------------- -------- --------------------- START_TIME  DATE END_TIME DATE SQL> insert into t values (to_date('24/JUN/2013 18:15:42', 'DD/MON/YYYY HH24:MI:SS'), to_date('24/JUN/2013 19:07:54','DD/MON/YYYY HH24:MI:SS')); 1 row created. SQL> commit; Commit complete. SQL>SQL>SQL> select * from t; START_TIME               END_TIME--------------------     --------------------24 Jun 2013 18:15:42    

[Code] .......

View 5 Replies View Related

Which Trigger Will Be Fired First

Sep 4, 2013

There is a Table, on that table there are 2 triggers are written 1) row level trigger 2) statement level trigger. Now i am inserting one row into that table, so which trigger will be fired first and why?

View 3 Replies View Related

Server Administration :: How To Get Password For Database Users

Nov 24, 2010

get the actual password for the database users. If we talk about the dba_users account we have password in the encrypted form how to decrypt them and get actual password.

View 6 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

Server Administration :: How To Get Size Of All 80 Users From Database

Mar 19, 2012

select username,account_status,default_tablespace from dba_users;

The ran the above query and it return 80 users but when i ran below query it shows just 14 rows.

select owner,sum(bytes)/(1024*1024) "GB" from dba_segments group by owner;how to get the size of all 80 users from database ?

View 2 Replies View Related

Assign Different Profile For Oracle Database Users According To Their Job Functions

Apr 10, 2011

it is a good practise to assign different profile for oracle database users according to their job functions.what could be the resons for that?

View 3 Replies View Related

Forms :: Create Different Database Users Like STORE / PAYROLL

Apr 12, 2011

I create different database users like STORE, PAYROLL, GL , SYSADMIN, COMMON to contain objects accordingly then I create another user ABC and grant DBA privileges to all users.

After that I grant SELECT/INSERT/UPDATE/DELETE privileges on each and every objects of STORE/PAYROLL/GL/SYSADMIN/COMMON users to ABC then create synonyms of STORE/PAYROLL/GL/SYSADMIN/COMMON users objects into ABC.

Actually, I want to use single user of ABC to build/execute my application but I think I am doing something wrong because whenever I tried to compile my form, its showing this error.

View 8 Replies View Related

SQL & PL/SQL :: Created 2 Users Named User_a And User_b In Database

Mar 2, 2012

I have created 2 users named "user_a" and "user_b" in my database and i gave "connect","dba" and "resource" roles to both users,then i created a table,synonym in user_a and i observe that user_b can also access this table and synonym of user_a without "granting" to "user_b", how it is possible. is it because of any of above role?

View 12 Replies View Related

Server Utilities :: How To Find Estimated Time To Complete Import (imp)

Apr 17, 2012

I am using imp / Schema level. Dump file contains 3 schemas with 120 GB size.Imp is running now. How to find completion time. So that I can inform to application developer about activity completion time.

View 4 Replies View Related







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