Q:

How to see profile assigned to a user

Let's say i have created a user as 'TEST_USER', and then i assigned a profile to it.
Now how can i see for TEST_USER which profile has been assigned?
I want some query that will check what profile a user is assigned with.

A:

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 

A:

You can find this information in dba_users.profile view:
http://docs.oracle.com/cd/E18283_01/server.112/e17110/statviews_5081.htm

