How to list all schemas from oracle database
In Oracle the entity "user" and entity "schema" are same if provided user has created at least one object in it.
Login in sqlplus as sys
To list all schemas:
SQL> SELECT username FROM all_users ORDER BY username;
SQL> Select username from dba_users;
Will give you list of all users but all users may not have created objects in it. So we can not call them as schema.
SQL> Select distinct owner from dba_objects;
Will give you the list of schemas available.
Using some conditions
SQL> SELECT username FROM all_users where username like '%_OWNER' OR username like '%_USER' ;
Login in sqlplus as sys
To list all schemas:
SQL> SELECT username FROM all_users ORDER BY username;
SQL> Select username from dba_users;
Will give you list of all users but all users may not have created objects in it. So we can not call them as schema.
SQL> Select distinct owner from dba_objects;
Will give you the list of schemas available.
Using some conditions
SQL> SELECT username FROM all_users where username like '%_OWNER' OR username like '%_USER' ;
or
select distinct(owner) from dba_objects where owner in ( SELECT username FROM all_users where username like '%_OWNER' OR username like '%_USER');
Subscribe to:
Post Comments
(
Atom
)
No comments :
Post a Comment