SQL & PL/SQL :: Query To Find User Who Did Not Login From Past 3 Years?

Nov 2, 2012

I have two table APPLICATION_1 and APPLICATION_2, where user's login date is captured.There will be multiple records for each user.Same user may or may not be present in both tables.

The requirement is to find those users who have not logged-in in the last 3 years.

-- Test Case

DROP TABLE application_1;
DROP TABLE application_2;
CREATE TABLE application_1
(
seq NUMBER ,
user_id VARCHAR2(30),

[code].....

Using the below query I'm able to get the desired output.

SELECT user_id, MAX(login_date) last_login_date FROM (
SELECT user_id, MAX(login_date) login_date FROM application_1 GROUP BY user_id
UNION ALL
SELECT user_id, MAX(login_date) login_date FROM application_2 GROUP BY user_id
) GROUP BY user_id

[code].....

View 10 Replies


ADVERTISEMENT

Security :: How To Get User Login / Logout Information For Past 30 Days

Feb 3, 2012

I just want to know the user login / log out information for the past 31days. how can i get that?

View 11 Replies View Related

SQL & PL/SQL :: Find Years Between Any Given Date Range

Apr 5, 2011

I require to find the years between any given date range. For example what are the years between the dates '01/12/2010' and '01/02/2012'? Answer must be '2010,2011,2012'. how to code the query for this result?

View 2 Replies View Related

Performance Tuning :: How To Find Out Session Consuming High Resource In Past

Mar 28, 2013

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

View 8 Replies View Related

PL/SQL :: Return Past Timestamp Values On Base Of Input Hour To User Function

Nov 22, 2012

resolve issue while modified the user function code for returns the values as timestamps

---function code

create or replace
function fun_test_timestamp(P_HOUR varchar2) return varchar2
is
sql_stmt varchar2(1000);
begin

[Code].....

Input:-
select fun_test_timestamp('5') from dual;

Output:-

SELECT CURRENT_TIMESTAMP - INTERVAL '5' HOUR FROM DUAL;

Modified Fun Code:

create or replace
function fun_test_timestamp(P_HOUR varchar2) return timestamp
is
sql_stmt varchar2(1000);

[Code]...

Input:-
select fun_test_timestamp('5') from dual;

Output:-

ORA-00911: invalid character
ORA-06512: at "NETVERTEXTRUNK.FUN_TEST_TIMESTAMP", line 8
00911. 00000 - "invalid character"

*Cause:    identifiers may not start with any ASCII character other than letters and numbers. $#_ are also allowed after the first character. Identifiers enclosed by doublequotes may contain
any character other than a doublequote. Alternative quotes (q'#...#') cannot use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL Language Reference Manual.

*Action: 

View 6 Replies View Related

PL/SQL :: Query To Get List Of Years?

Feb 15, 2013

I am trying to generate a list of years, starting with 2010 and counting up through 5 years from the current date. For example, if it is currently 2013, the list would include 2010 through 2018. 2 years from now, I want the list to go from 2010 through 2020.

I am currently using

SELECT to_char(add_months(to_date('2010','YYYY'),12 * (rownum-1)),'YYYY')
FROM dual
CONNECT BY level <= 10to get a list with 10 entries.

How can I make this dynamic enough to return a variable number of records depending on the current year?

View 1 Replies View Related

Server Administration :: Externally Authenticated User Login Syntax As Proxy User?

Nov 15, 2010

I created an externally authenticated user in database. And can login without password with below syntax.

SQL> connect / @TESTDB
Connected.
SQL> show user;
USER is "SCOTT"

This scott user has a proxy permission to another DBuser PROXY_USER. Previously I used to login using below syntax.

connect scott[proxy_user]/password_for_scott@TESTDB

So now, what syntax should be used for this "ExternallY Authenticated" user to login as a proxy user?

View 10 Replies View Related

Forms :: Login User From One CANVAS And Access Second As Per-user Rights?

Oct 20, 2012

- we have user id parameter.can we update the parameter(:parameter.p_userid) before firing "WHEN NEW FORM INSTANCE TRIGGER"(when new form instance trigger contain code for tree node), for login another canvas as per user rights?

- i have created a login form in one canvas.

- also tree node Hierarchy form created on another canvas. now we want to login through login screen, after login only those forms should show in tree Hierarchy which users have rights. for this purpose we want to pass the parameter of userid before connecting to tree node form through the following query

SELECT COUNT (*)
INTO v_count
FROM usersinfo
WHERE usersname = :USERBLOCK.usernames AND passwords = :USERBLOCK.passwords ;

[code]...

Every thing is working but when we click on button in login form at that time parameter.p_userid will return null. because above code is define on button click in login screen, i cannot understand where i need to define the above code .

View 1 Replies View Related

Pivot Query In Oracle To Get Years From Column And Make Separate Column For Each

Jul 22, 2009

I'm trying to do a pivot query in oracle to get the years from a column and make a separate column for each. I found an example of the code to use on the internet and i changed it for my own tables but i'm getting errors. Namely a "FROM keyword not where expected" error at the beginning of the 'avg(...' statements.

I have copied the code used in

select stud_id, 2006, 2007, 2008, 2009
from (
select stud_id,
avg(case when year=2006 then ((present/poss)*100) else null end) 2006,
avg(case when year=2007 then ((present/poss)*100) else null end) 2007,
avg(case when year=2008 then ((present/poss)*100) else null end) 2008,
avg(case when year=2009 then ((present/poss)*100) else null end) 2009
from attendance.vw_all_attendance_perc
group by stud_id
);

View 11 Replies View Related

Forms :: Login In Same Form With Different User?

Dec 1, 2010

I have a mainform through which one can access 3 different application(say a ,b,c)i have made three push buttons by clicking it the main menu of the respeective application is called.The three applcication have different users(user a,user b,userc)and have different database on the same version(db1,db2,db3 on 111g).I am using formservlet and i am calling the main form. configuration.I dont want the user to enter the username and password so i have hardcored it in formsweg.cfg file. my problem is that i login to the main form as 'user a' so i can access the application'a'.but now if i want to access applicatin 'b' how can i can i login as 'user b'?

View 6 Replies View Related

Client Tools :: Restricting User From Login To DB?

Oct 1, 2013

I wanted to restrict the Users to login to DB using Application like TOAD or PL/SQl Developer .

But the users can login to DB using sql*plus.

We can get this info once user looged in to DB.

SQL> select SID,USER#,USERNAME,SERVER,MODULE from V$SESSION where USERNAME='LIVE4LEARN';
SIDUSER#USERNAMESERVERMODULE
12418265LIVE4LEARNDEDICATEDSQL*Plus
23018265LIVE4LEARNDEDICATEDPL/SQL Developer
43718265LIVE4LEARNDEDICATEDPL/SQL Developer
54018265LIVE4LEARNDEDICATEDPL/SQL Developer

Can we make this check done before login process and allow users to login when module not in ('TOAD','PL/SQL Developer').

View 15 Replies View Related

Forms :: User Defined Login Form

May 3, 2011

I have designed a Login form which takes username, password and connect string is hardcoded.I have compiled it, made an fmx file. created a shortcut to desktop. given the BIN path in shortcut.

when i want to lauch it, oracle's default login popup is shown.It can be by passed while defining username/password@string in shortcut properties but it reveals the password. every one can access it. I want to launch my own form without logging in to oracle. how can i avoid default login popup?

View 8 Replies View Related

Server Administration :: User Login Fails?

Sep 27, 2011

I just create a user and tried to log in but it gives the following error, why?

SQL*Plus: Release 10.1.0.4.2 - Production on Tue Sep 27 10:57:55 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> create user user1 identified by user1;

User created.

SQL> conn user1
Enter password: *****
ERROR:
ORA-12560: TNS:protocol adapter error

Warning: You are no longer connected to ORACLE.

View 26 Replies View Related

Enterprise Manager :: Preventing A User To Login To OEM

May 25, 2011

Is there a way to prevent a user to login to OEM in oracle 10g?

View 6 Replies View Related

Forms :: Get The Current User Login On System?

Jun 19, 2011

How can i get the Current user log in on my system using global variable.

View 6 Replies View Related

User Unable To Login Even After Password Reset

Nov 27, 2012

10.2.0.3 on Linux

I reset a user password and the user is unable to login as you can see below

[oracle@testdb 10.2.0]$ sqlplus sys/manager as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Nov 27 16:20:21 2012

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> alter user sysman identified by oracle;

User altered.

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With the Partitioning, OLAP and Data Mining options

[oracle@testdb 10.2.0]$ sqlplus sysman/oracle

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Nov 27 16:20:32 2012

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

ERROR: ORA-01017: invalid username/password; logon denied

User unable to login even after password reset

View 4 Replies View Related

Security :: User Unable To Login After Password Reset

Dec 14, 2011

We have a production database on 11.2.0.2 version. The application user was prompted to change the password after his password expired.

USERID NTIMESTAMP# ACTION# RETURNCODE
------------------------------ --------------------------------------------------------------------------- ---------- ----------
M500796 13-DEC-11 06.11.06.065209 PM 100 28001

After changing the password he is not able to logon. The aud$ table does not show any occurrence of 1017, therefore it is not a question of Invalid password.

LVV> show parameter SEC_CASE_SENSITIVE_LOGON

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean FALSE

View 2 Replies View Related

Server Administration :: SLOW Login As User To DB Via Sqlplus?

Mar 2, 2012

I have a situation where when I login as a user to my DBvia sqlplus no service name it takes about 20 secs to connect.Yet when I login as a user with DBA privs it logs in immediately.

Is there something I can do to trace what is happneing behind scences to determine what the login delay may be..

View 9 Replies View Related

Oracle Database 11g Accepts DBA Login For Not Existed User?

Oct 24, 2012

I have Installed Oracle database 11g, By mistake I logged as :

mm/mm@orcl as sysdba

The Database acctepted the login with a full DBA privillages, although this user is not created within the database users.

View 9 Replies View Related

XE :: Unable To Login With Logon As OPS$-user To Get SAPSR3 Password?

Jun 18, 2013

my listener getting started and database also up

but gives error

Connecting as SAPSR3/<pwd>@ECC on connection 0 (nls_hdl 0)

Logon as OPS$-user to get SAPSR3's password

If can share screen shots are some link so i can go aehad

what is the default password for SYSTEM

View 1 Replies View Related

Restrict User To Login On Primary Database Using Discoverer

Dec 14, 2012

We are using Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 and Oracle Discoverer 9i desktop & Administrator,We have configured a physical standby database. We have ask users to connect to standby thru discoverer thru a different tns entry, but user can also connect on primary db.

We want to restrict the specified users to connect on primary database thru discoverer.

View 5 Replies View Related

Server Administration :: Can't Install SQLT - User Unable To Login

Jan 3, 2012

From metalink: Note 215187.1...Im trying to install SQLT. The installation process ask for:

Specify optional Connect Identifier (as per Oracle Net)
Include "@" symbol, ie. @PROD
If not applicable, enter nothing and hit the "Enter" key

I have tried with SID (@TESTDB) and SERVICE NAME (@TESTDB.mydomain.local) and leaving it blank, nothing works. I cant connect as SQLTXPLAIN.

View 5 Replies View Related

Server Administration :: Sys User Not Login And Giving Error ORA-27101

Sep 5, 2011

How we can resolve :

Enter user-name: sys as sysdba
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SVR4 Error: 2: No such file or directory

View 15 Replies View Related

Application Express :: Login User With Database Credential - Seen As APEX_PUBLIC_USER

Mar 29, 2013

I'm using Application Express 4.2.1.00.08

In the application, the authentication method used is the DB one, so the user (schema) must exist on the DB. This works fine.

For instance, I'm login as SCOTT

Creating the following query select upper(user) from dualshows that the real logged user is APEX_PUBLIC_USER and npot SCOTT.

How to create a connection with another "real" username ?

View 1 Replies View Related

Application Express :: Updating Record As Apex_public_user instead Of Login User?

Sep 9, 2013

I have created an application as database authentication.In the application, I  have developed a screen based on a tabular form,when I try to update a  record ,the record is updated with apex_public_user insted of the database login. 

View 3 Replies View Related

Query Without Login To Oracle database From Linux OS?

Aug 21, 2013

can we query this without login to the oracle  database from linux os?database is up and running . /apps/oracle/product/11.2.0/dbhome/bin/sqlplus system/passwd select name from v$database;

View 5 Replies View Related

How To Find CPU Usage By Each User Session

Sep 7, 2011

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

View 5 Replies View Related

Find Youngest User In Database?

Feb 6, 2007

How do you find the youngest user in a certain table

select a.illness_desc
from uma.pc_illness a, uma.pc_patient b, uma.pc_diagnosis c
where b.patient_no = c.patient_no and c.illness_code = a.illness_code
and b.age = '21';

i currently have this (it also needs to display their illness which i have done) but i can do only if i cheat and see what the youngest user is, so instead of b.age = 21 i need something that will search the database and find that youngest age

we are using SQL+

View 5 Replies View Related

Find High CPU User In Oracle 10g

Jan 25, 2013

clould you sand me sqary for find high cpu user in oarcle 10g

View 3 Replies View Related

Security :: To Find Out Which Row Locked By Particular User

May 14, 2011

If the user has locked some row in the database how to find out the row id or the row which is locked by that particular USER.

View 2 Replies View Related







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