Friday, March 15, 2013

[RDBMS] List All Schemas

This post will provide information about Oracle schemas. First, its definition and then the correct SQL query to list the availables schemas.

Definition
An Oracle schema is a set of all objects owned by a user. Consequently, an schema is equivalent to a user account.

How-to
If you are interested in listing all schemas connect to the instance using a user with SYSDBA privileges. Then query the following SQL:

[RDBMS] How To Change Oracle Installation Owner

Applies to:
          Oracle Database 10g
          Oracle Database 11g
          Oracle Enterprise Manager 11gR1 and Cloud Control 12c

OS: Linux x86, Linux x86_64, Solaris (Tested). (It should work on all UNIX based systems)

Note: Before to proceed you need to stop all the services running on the Oracle Home (databases, listeners, oms, agent)

1. If you don't have the user that will be the new owner of the ORACLE_HOME created, create one. I'll use oraoem.

2. With all the services stopped, change the ORACLE_HOME's owner:

         chown -R oraoem $ORACLE_HOME/