CODEWITHSUNDEEP
oracle-databasedatabase-administration
user2639663
user2639663

Reputation: 9

List of usernames in oracle

I need to get a list of all users and their tables and details.

For Example : https://www.google.com/search?q=oracle+user&source=lnms&tbm=isch&sa=X&ved=0ahUKEwi-td2pjcPMAhXEFh4KHdMAAVcQ_AUIBygB&biw=1175&bih=621#tbm=isch&q=oracle+user++sql+developer&imgrc=Qvmfp57HchgwgM%3A

In the above screen towards left there is red color symbol (User) , SO i need the username, associated tables(under that user) and atrributes in that table. Is this possible. for all users.

Thanks Addy

Upvotes: 0

Views: 124

Answers (3)

kevinskio
kevinskio

Reputation: 4551

This shows all the users and all their tables except for SYS and SYSTEM

SELECT owner, table_name
  FROM All_All_Tables
 WHERE owner NOT IN ('SYS','SYSTEM')
 ORDER BY 1,2

Runs on Oracle 10, 11

Upvotes: 0

thatjeffsmith
thatjeffsmith

Reputation: 22467

ALL USERS, ALL TABLES, 'and details'

So do you really want ALL the users? Because many users are system users - users that own objects that the database itself uses, SYS being the biggest example. You could have dozens of these accounts. I'm guessing you don't want them.

All tables, tables in the recycle bin, tables there for materialized views, do you want those too?

And 'details'. Do you want their created date, their columns, their storage parameters? The more you want, the bigger and uglier your query is going to get.

All that being said, you pointed to a screenshot of Oracle SQL Developer. It contains a data modeling feature. Use it. Reverse engineer the users you really want into a data model. And then use the data dictionary reports it offers to give you the info you want.

You have to figure out what you REALLY want first though.

Pick the users, and then the objects you want

I talk about how to do the RE in the data modeler here.

Upvotes: 1

Aleksej
Aleksej

Reputation: 22969

You can start from this:

select *
from dba_tables t
  inner join dba_tab_columns
    using(owner, table_name)

This will give all the tables and columns, with some informations about tablespace, the type of the columns, and so on

Upvotes: 0

Related Questions