WCH_Applications
asked on
Best way to run a query/report to check for accounts expiring within 21 days.
I have the syntax I want to run. Just can't figure out what would be the best way to run this against all of our production databases at once. Tried going through Enterprise manager but none of the reporting tools or jobs seemed to do what I wanted them to do. Just want to know if anyone has done anything like this. Your words and wisdom would be much appreciated.
here is the report I want to run:
SELECT A.NAME, B.USERNAME, B.ACCOUNT_STATUS, B.EXPIRY_DATE,
TRUNC(B.EXPIRY_DATE)-TRUNC
FROM v$database A, DBA_USERS B
WHERE TRUNC(B.EXPIRY_DATE) - TRUNC(SYSDATE) < 30
ORDER BY num_days desc;
Thanks,
-Kyle
here is the report I want to run:
SELECT A.NAME, B.USERNAME, B.ACCOUNT_STATUS, B.EXPIRY_DATE,
TRUNC(B.EXPIRY_DATE)-TRUNC
FROM v$database A, DBA_USERS B
WHERE TRUNC(B.EXPIRY_DATE) - TRUNC(SYSDATE) < 30
ORDER BY num_days desc;
Thanks,
-Kyle
OP_Zaharin
- you can create a pl/sql procedure and schedule it sa a job to run daily. output it to a table or a file as report and email the report to your email daily.
You can setup remote database links and run it all in one single script.
select a.*, 'ORA1' from dba_users@ora1 a
union
select b.*, 'ORA2' from dba_users@ora2 b
union
...
Otherwise, you'll have to write a script (such as Perl) that connects to each database in a loop.
select a.*, 'ORA1' from dba_users@ora1 a
union
select b.*, 'ORA2' from dba_users@ora2 b
union
...
Otherwise, you'll have to write a script (such as Perl) that connects to each database in a loop.
set pagesize 500
set linesize 200
set trimspool on
column “EXPIRE DATE” format a20
select username as “USER NAME”, expiry_date as “EXPIRE DATE”, account_status
from dba_users
where expiry_date < sysdate+21and account_status IN ( ‘OPEN’, ‘EXPIRED(GRACE)’ )
order by account_status, expiry_date, username
/
set linesize 200
set trimspool on
column “EXPIRE DATE” format a20
select username as “USER NAME”, expiry_date as “EXPIRE DATE”, account_status
from dba_users
where expiry_date < sysdate+21and account_status IN ( ‘OPEN’, ‘EXPIRED(GRACE)’ )
order by account_status, expiry_date, username
/
ASKER
I've been takling to some of our guys, we want one report that shows all expiring (or expired) user accounts across all (production) databases. If we can link all of the account names with their e-mail addresses, we might be able to ALSO solve an issue for the user-password change program. This way we could identify users with expiring passwords AND notify them of a method were they can change their own passwords with minimal work on our part.
To do it from a single source, you need something that can see everything. This will likely, as others have suggested, need some script/program.
Have you considered changing the databases to some central authentication method?
Have you considered changing the databases to some central authentication method?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.