Joining dba_users & v$database [closed]

Posted on

Question :

I need to generate a report that displays the account status of all schemas, which I’ve got.

SELECT   
''''|| USERNAME ||''''     USERNAME,
''''|| ACCOUNT_STATUS||'''' ACCOUNT_STATUS,
''''|| LOCK_DATE ||'''' LOCK_DATE,
''''|| PROFILE ||''''     PROFILE
FROM DBA_USERS
WHERE PROFILE='DEFAULT'
ORDER BY 1,2;

However the requirement also states that they want the database name on every row in the report, so…

SELECT 
''''|| NAME ||'''' 
FROM V$DATABASE

My SQL is pretty poor so I tried some sort of mismashed join, but realised that won’t work… any ideas?

Answer :

SELECT   
''''|| USERNAME ||''''     USERNAME,
''''|| ACCOUNT_STATUS||'''' ACCOUNT_STATUS,
''''|| LOCK_DATE ||'''' LOCK_DATE,
''''|| PROFILE ||''''     PROFILE,
(SELECT ''''|| NAME ||'''' FROM V$DATABASE) DB_NAME
FROM DBA_USERS
WHERE PROFILE='DEFAULT'
ORDER BY 1,2;

or

SELECT   
(SELECT ''''|| NAME ||'''' FROM V$DATABASE) DB_NAME,
''''|| USERNAME ||''''     USERNAME,
''''|| ACCOUNT_STATUS||'''' ACCOUNT_STATUS,
''''|| LOCK_DATE ||'''' LOCK_DATE,
''''|| PROFILE ||''''     PROFILE
FROM DBA_USERS
WHERE PROFILE='DEFAULT'
ORDER BY 2,3;

Leave a Reply

Your email address will not be published. Required fields are marked *