We help IT Professionals succeed at work.

Display the last two users created.

119 Views
Last Modified: 2019-02-12
Hi I have created an sql script that creates two users.  At the end of the script how do i display the two Username, Status, and Creation Date that I have created.
Comment
Watch Question

Sam JacobsCitrix Technology Professional / Director of TechDev Services, IPM
CERTIFIED EXPERT

Commented:
SQL has an OUTPUT clause that you should be able to use... along the lines of:
INSERT INTO dbo.YourTable(UserName, Status, CreationDate)
OUTPUT Inserted.UserName, Inserted.Status, Inserted.CreationDate
VALUES(val1, val2, val3)

Open in new window

You can also insert the values into another table, if desired.
See full documentation here.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
You can execute the query below to get the last 2 users created..
SELECT TOP 2 Username, Status, CreationDate
FROM ur_table
ORDER BY CreationDate desc

Open in new window

Author

Commented:
Still having trouble getting it to display to last 2 users created.  Here is my code to create the two users.

CREATE USER RMAC IDENTIFIED BY "password"
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA 30M on USERS
PROFILE DEFAULT
PASSWORD EXPIRE
ACCOUNT UNLOCK;
CREATE USER LMac IDENTIFIED BY "password"
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA 30M on USERS
PROFILE DEFAULT
PASSWORD EXPIRE
ACCOUNT UNLOCK;
Sam JacobsCitrix Technology Professional / Director of TechDev Services, IPM
CERTIFIED EXPERT

Commented:
Sorry ... I didn't realize you were creating system users. Try this:
SELECT top 2 name, create_date FROM sys.database_principals
order by create_date desc 

Open in new window

I don't know what you mean by status though.

Author

Commented:
status is the account status, which is set to expired.  would something like this work

SELECT USERNAME, ACCOUNT_STATUS, CREATED
FROM USERS
where USERNAME = 'DBSEC'
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Thanks for sharing the script.. seems like you are using Oracle..
So, use the below script..
SELECT NAME, ASTATUS, CTIME
FROM SYS.USER$
ORDER BY CTIME desc
fetch first 2 rows only;

Open in new window

Author

Commented:
So when I run the script this is the output

User created.


User created.

  4  

I am trying to have it display

USERNAME                             ACCOUNT_STATUS                CREATED
RMAC                                      Expired                                    4-Feb-19
LMAC                                      Expired                                     4-FEb-19
Sam JacobsCitrix Technology Professional / Director of TechDev Services, IPM
CERTIFIED EXPERT

Commented:
Sorry, I missed the Oracle part ... how about the following:
select username, account_status, created
from dba_users
order by created desc
fetch first 2 rows only;

Open in new window

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Typo in my earlier comment, it is supposed to use SYS.USER$ table..

Author

Commented:
I get an error of

fetch first 2 rows only
ERROR at line 4:
ORA-00933: SQL command not properly ended

Author

Commented:
So I created this line SELECT USERNAME, ACCOUNT_STATUS, CREATED FROM DBA_USERS; which lists all the users, I just want to display RMAC and LMAC only.  How would I add a filter to drill down to just these two usernames.  Sorry I am new to the Oracle framework and learning the coding.
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
"fetch first 2 rows only" is version dependent, only recent versions support fetch starting with 12c, I suggest you try row_number() over() instead:

select
    username, account_status, created
from (
    select
        username, account_status, created
       , row_number() over(order by  created desc) as rn
    from dba_users
    )
where rn <= 2
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
By the way, access to user information tables is in itself controlled by permissions

try the table ALL_USERS if you have trouble finding any user information:

select
    *
from (
    select
        all_USERS.*
       , row_number() over(order by  created desc) as rn
    from all_USERS
    )
where rn <= 2

But the columns available from all_users is very limited.

Author

Commented:
So I got this so far as below, I was able to display RMAC, now just need to add LMAC to display.  

SELECT USERNAME, ACCOUNT_STATUS, CREATED FROM DBA_USERS
where USERNAME = 'RMAC';
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
Have you tired using the row_number() method yet?

ps: As you can access the DBA_USERS table, you don't need to use ALL_USERS
SQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
Just a note.  One of the posts mentions using SYS.USER$.  You really should never directly access those tables.  You should be using the DBA_ or ALL_ views to get to the information.
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
One other note about SYS.USER$.  It also stores roles (roles are just specialized users).  If you did this:

create user a ...
create role b ...
create user c ...

Querying SYS.USER$ with the query posted above, you would get B and C, not A and C.

In addition, the ASTATUS column is of the NUMBER datatype, which is really a bit field.  Not sure what you think you can accomplish by looking at that field.
jtriftsMI and Automation
CERTIFIED EXPERT

Commented:
Holy moly folks... Talk about making this difficult!

Select * From
(Select * From DBA_USERS
ORDER BY  CREATED DESC)
WHERE ROWNUM <3;

No need to look at User$ unless there are additional fields you require.

The reason for the inline select Is to control the timing of ROWNUM. If it was in the inner select, two random rows could be selected and then they would be sorted by date. We want them sorted by date (descending) and then we restrict the dataset to the last two rows.
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
What's wrong with PortletPaul's windowed aggregate method?
jtriftsMI and Automation
CERTIFIED EXPERT

Commented:
Complexity.
Analytic functions are not necessary.
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
Using row_number() is as valid as using an ordered subquery followed by filtering via rownum.  What makes that approach any less complex?
jtriftsMI and Automation
CERTIFIED EXPERT

Commented:
I agree. It is just as valid.
I love analytics.
But for me, I think, *time and place*.
And when someone is seeking support, I tend to opt for something simple.
I haven't run the numbers to see if they end up with similar or different execution plans.
But again, for someone seeking this level of support, I wouldn't assume they'd be familiar with tkprof, etc.

There is usually more than one way to skin a cat. Options may be chosen for different reasons.

Whenever I've implemented row_number () over (partition by...), I've always had to add more (sometimes excessive) comments.
Whereas everyone always seemed to just get inline views.

Maybe time have changed.

In any case, I apologise if I ruffled any feathers or confused matters.
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
Window functions are now very commonly implemented and are part of ANSI standards. For me they are preferable to vendor specific solutions if there is a choice. I just don't see how the traditional Oracle specific "rownum" approach is any more readable, in fact I would argue the opposite :)

nb: The most expensive operation in the solution is ordering, and both approaches require this.
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
Then why is the ordered subquery preferable to Sam's:

fetch first 2 rows only

Vendor and version specific, but the most readable option.
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
"fetch first 2 rows only" is VERSION dependent (starts with 12c)

When that version (or later) is reached, then yes it would make sense to use that approach.

In the meantime: I would use row_number(), but using the older rownum approach is also a valid option. Window functions are not obscure and exotic these days, they are part of the standard and widely/frequently used.
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
I did say it was version dependent

Vendor and version specific

I am not saying any one way is better or worse than the other.  Someone else seems to be.  I say they are all equal from a performance standpoint, as all of them need to sort the same dataset, which would be the most expensive part of the operation.

I don't think that someones personal preference for what they deem as the more readable way to do it makes it the best solution.

I would love to show them some 200 line data warehouse queries and let them make those more readable.
Geert GOracle dba
CERTIFIED EXPERT
Top Expert 2009

Commented:
200 lines ?
I've been working a month on this 25 page (a page is only an A4 size) query ...

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.