How to see profile assigned to a user
In order to find out the users and the profile assigned you can use the commands below.
DESC DBA_USERS;
This will show you all the fields name for which you want to query
SELECT USERNAME, PROFILE, ACCOUNT_STATUS FROM DBA_USERS;
And this command will show you the user name, profile and account status i.e. which profile is assigned to which user
You can find this information in dba_users.profile view: http://docs.oracle.com/cd/E18283_01/server.112/e17110/statviews_5081.htm