Query To Find Schema Name In Oracle

Query To Find Schema Name In Oracle

by

In Oracle databases, a schema is a logical collection of database objects such as tables, views, indexes, and stored procedures. Each schema is associated with a specific database user, meaning the terms "schema" and "user" are often used interchangeably in Oracle.

Knowing how to find the schema name is essential for database administrators and developers working on queries, access controls, and system maintenance. In this topic, we will explore different SQL queries to retrieve schema names in Oracle, covering both simple and advanced approaches.

Understanding Schemas in Oracle

A schema in Oracle is essentially a user that owns database objects. When a user is created, a corresponding schema is automatically assigned to store database objects owned by that user.

For example:

CREATE USER sales_user IDENTIFIED BY password;GRANT CONNECT, RESOURCE TO sales_user;

This creates a schema named SALES_USER where all tables and objects created by this user are stored.

Queries to Find Schema Name in Oracle

1. Using SYS_CONTEXT to Get the Current Schema

The easiest way to find the current schema is by using the SYS_CONTEXT function.

SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') AS schema_name FROM dual;

Explanation:

  • USERENV provides system information.

  • CURRENT_SCHEMA returns the schema currently in use for the session.

  • DUAL is a dummy table used in Oracle for single-value queries.

This query is useful when you need to confirm which schema the session is currently using.

2. Using USER to Get the Current User’s Schema

Each user in Oracle owns a schema with the same name. You can check the current user’s schema using:

SELECT USER AS schema_name FROM dual;

This query returns the user that owns the current session’s schema.

Example output:

SCHEMA_NAME
HR

3. Using ALL_USERS to List All Schemas

To view all available schemas in the database, use:

SELECT USERNAME AS schema_name FROM ALL_USERS ORDER BY USERNAME;

Explanation:

  • ALL_USERS contains a list of all database users, which represent schemas.

  • ORDER BY USERNAME sorts the results alphabetically.

Example output:

SCHEMA_NAME
HR
SALES_USER
SYSTEM

4. Using DBA_USERS for a Comprehensive Schema List

If you have DBA privileges, you can retrieve detailed schema information:

SELECT USERNAME AS schema_name, ACCOUNT_STATUS, CREATED FROM DBA_USERS ORDER BY CREATED DESC;

Explanation:

  • DBA_USERS provides schema details for all users.

  • ACCOUNT_STATUS shows whether the schema is active or locked.

  • CREATED displays the creation date of each schema.

Example output:

SCHEMA_NAME ACCOUNT_STATUS CREATED
HR OPEN 01-JAN-2023
SALES_USER LOCKED 15-FEB-2023

Checking Schema Name for Specific Objects

5. Finding the Schema of a Specific Table

If you need to determine which schema owns a specific table, use:

SELECT OWNER AS schema_name, TABLE_NAME FROM ALL_TABLES WHERE TABLE_NAME = 'EMPLOYEES';

Example output:

SCHEMA_NAME TABLE_NAME
HR EMPLOYEES

This confirms that the EMPLOYEES table belongs to the HR schema.

6. Finding Schema Names with Specific Privileges

To find which schemas have a specific privilege, such as CREATE SESSION, run:

SELECT GRANTEE AS schema_name FROM DBA_SYS_PRIVS WHERE PRIVILEGE = 'CREATE SESSION';

This lists all schemas with login privileges to the Oracle database.

Switching Between Schemas in Oracle

If you need to switch to a different schema without changing the session user, use:

ALTER SESSION SET CURRENT_SCHEMA = SALES_USER;

This allows you to work within the SALES_USER schema without changing your login credentials.

To verify the switch, run:

SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM dual;

Example output:

CURRENT_SCHEMA
SALES_USER

Best Practices for Managing Schemas

  1. Grant Only Necessary Privileges

    • Limit schema access using GRANT and REVOKE.

    • Example:

      GRANT SELECT ON hr.employees TO sales_user;

  2. Use Indexes for Faster Schema Queries

    • Creating indexes on schema-related tables improves performance:

      CREATE INDEX idx_users ON DBA_USERS(USERNAME);

  3. Backup Important Schemas

    • Use EXPDP for schema backups:

      expdp system/password schemas=HR directory=backup_dir dumpfile=hr_backup.dmp logfile=hr_backup.log

  4. Regularly Check Schema Usage

    • Run queries to find unused schemas:

      SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS WHERE ACCOUNT_STATUS = 'LOCKED';

  5. Monitor Schema Growth

    • Check schema size using:

      SELECT OWNER, SUM(BYTES)/1024/1024 AS size_mb FROM DBA_SEGMENTS GROUP BY OWNER ORDER BY size_mb DESC;

Common Errors When Querying Schema Names

Error Cause Solution
ORA-00942: Table or view does not exist User lacks privileges to query DBA_USERS Use ALL_USERS instead
ORA-01031: Insufficient privileges User does not have access to DBA_* views Ask DBA for SELECT_CATALOG_ROLE privileges
ORA-01435: User does not exist The specified schema does not exist Double-check schema name spelling

Finding schema names in Oracle is crucial for database administration, development, and security management. Whether you need to check the current schema, list all schemas, or find the schema of specific objects, Oracle provides powerful SQL queries to accomplish these tasks efficiently.

By using commands like SYS_CONTEXT, ALL_USERS, and DBA_USERS, you can easily retrieve schema information and ensure your database remains well-organized.