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
ADVERTISEMENT
Mar 29, 2012
hint of which DBA views we need to query to find current running queries which are not using indexex?
I suppose v$session is one , what else we need to look to?
View 1 Replies
View Related
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
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
Sep 9, 2011
here we have an scenario where we want to find out all the sql statements that are executed in a particular time. The sql statements are executed via our application. I tried in awr report but it shows only the sql query which has taken long time to execute. and i even tried in V$session and V$sqlarea. how to view the executed sql statements in a particular session/current session
View 3 Replies
View Related
Nov 19, 2012
how to find out queries bases on elapsed time from the window of 3 Months
DBA_HIST_SQLSTAT and DBA_HIST_SQLTEXT
View 6 Replies
View Related
Feb 6, 2013
Is there any oracle dictionary view which captures the queries being run by users on the database and time taken to execute those queries?We need to find out the OS user not the database user since we have to identify the users who are executing long running queries.We require this basically to monitor the long running queries on the database.
View 2 Replies
View Related
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
View Related
Sep 1, 2011
How to find non-indexed queries and columns that require indexes?
View 3 Replies
View Related
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
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
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
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
Jan 23, 2007
our system has always been running on mysql database and recently we have switched to oracle. As the current system is coded using mysql query syntax, when i run this program using oracle database, i got a error. The language that I'm using is JSP.
this is the error message:
The following query could not run on oracle. To convert these mysql queries to oracle compatible queries.
SELECT productID,productName FROM products order by productName;
select newsID,newsDate,newsHeadLine1 from news order by newsDate Desc limit 3
SELECT fuji_products.productID, productName_Display FROM products,products_availability where products_availability.productID=products.productID and (product_status='enabled' or product_status='all') AND category='12'
SELECT catID, catSub1 from category where catSub = '"+ prodCat +"' AND catSub1 is not null group by catSub1 order by catSub1
View 6 Replies
View Related
Oct 28, 2010
Name some database tool from which I can check the SQL Queries which my application is running.
NOTE: I do not want to check the queries which I am executing at the SQL command prompt but queries that are being run by my application at the backend.
View 4 Replies
View Related
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
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
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
View Related
Apr 5, 2013
After upgrading to Apex 4.2 I'm now unable to test Report Queries. Each time I click on the Test Report button via Shared Components|Report Queries I recieve the following error message:
ORA-20001: The printing engine could not be reached because either the URL specified is incorrect or a proxy URL needs to be specified.
I still have Apex version 4.1 installed on a different server and it appears to be working fine.
View 1 Replies
View Related
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
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
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
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
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
Jul 20, 2012
What is the code/javascript/url behind the test report button on report queries component?
We are having DSN problems using the print url given on the report query. The URL works for some of our developers and not for others.
The test report button on the report query appears to work for everyone but I do not know what is behind the "Test Report" button. If I could recreate the test report button on our page this would be GREAT!
View 0 Replies
View Related
Oct 1, 2013
find VIP in RAC from command line.
View 2 Replies
View Related
Feb 28, 2013
1. On the Home Page, click the "Settings" wheel in the header of the Team Development region on the right side.
2. On the Home Page Preferences popup set "Show" to something else as "All" and click [Apply Changes]
3. Back on the Home Page, in the Team Development region on the right side, click the number next to "Bugs"
4. ERR-1002 Unable to find item ID for item "P3500_RELEASE" in application "4800".
View 0 Replies
View Related
Nov 1, 2012
I have RAC environment which has 2 nodes. In which 1 node is down.. When i used the following command i found
ERROR: Could not find a suitable set of interfaces for VIPs.
oracle@YYYYYYYY:~> cluvfy comp nodecon -n all -verbose
Verifying node connectivity
Checking node connectivity...
Interface information for node "YYYYYYYY"
Interface Name IP Address Subnet
------------------------------ ------------------------------ ----------------
eth20 10.20.2.111 10.20.2.0
eth2 172.20.2.111 172.20.2.0
eth2 172.20.2.114 172.20.2.0
[code]....
View 6 Replies
View Related
Feb 5, 2010
I was looking for application column name corresponding to "Draft Invoice Number" ra_interface_lines_all table.
I tried the below.
/* Get descriptive_flexfield_name for the application table name*/
SELECT *
FROM FND_DESCRIPTIVE_FLEXS_VL
WHERE application_id = 222
AND APPLICATION_TABLE_NAME=upper('ra_interface_lines_all');
/* Get the application column name and end user column name*/
SELECT *
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE application_id = 222
AND descriptive_flexfield_name = 'RA_INTERFACE_LINES';
There are many DESCRIPTIVE_FLEX_CONTEXT_CODEs obtained. I could finally trace out that draft invoice number corresponds to INTERFACE_LINE_ATTRIBUTE2. How can I know what DESCRIPTIVE_FLEX_CONTEXT_CODE should I look for?
I want to build a single query to fetch the application column name and flex field name for a specific table .
View 6 Replies
View Related
Feb 13, 2013
I am trying to find the unix process for one of my application in the database but I am unable to view the same. To simulate, I did the following.
1. My database runs on different server.
2. I invoked "sqlplus" from another unix box to login to the database.
3. I found that the process id (ps -ef |grep sqlplus).
4. When I execute the below mentioned query it does not display the process id that I am looking for. But the osuser, username, program and machine details are correct. How can I know the process details from the database?
SELECT SYS.GV_$SESSION.OSUSER, SYS.GV_$SESSION.USERNAME, SYS.GV_$PROCESS.SPID,
SYS.GV_$SESSION.MACHINE, SYS.GV_$SESSION.PROGRAM,
SYS.GV_$PROCESS.PROGRAM ,SYS.GV_$SESSION.SQL_ID
FROM
SYS.GV_$PROCESS, SYS.GV_$SESSION
WHERE
SYS.GV_$PROCESS.ADDR=SYS.GV_$SESSION.PADDR and SYS.GV_$SESSION.USERNAME='TEST'
and SYS.GV_$SESSION.MACHINE like '%hostname%'
View 3 Replies
View Related
