How to use Querying the DBA_USERS View In SQL
The DBA_USERS view provides a more comprehensive view of all user accounts in the database, along with their statuses. Let’s Develop a PL/SQL script to retrieve and display the names and account statuses of all user schemas in an Oracle Database. The script should query the DBA_USERS
view, iterate through the results and output each schema name along with its corresponding account status.
DECLARE
v_username VARCHAR2(100);
v_account_status VARCHAR2(100);
BEGIN
FOR user_rec IN (SELECT username, account_status FROM dba_users) LOOP
v_username := user_rec.username;
v_account_status := user_rec.account_status;
DBMS_OUTPUT.PUT_LINE('Schema: ' || v_username || ', Status: ' || v_account_status);
END LOOP;
END;
/
Output:
Schema: GSMADMIN_INTERNAL, Status: LOCKED
Schema: MDSYS, Status: LOCKED
Schema: OLAPSYS, Status: LOCKED
Schema: XDB, Status: LOCKED
Schema: WMSYS, Status: LOCKED
Schema: GSMCATUSER, Status: LOCKED
Schema: MDDATA, Status: LOCKED
Schema: SYSBACKUP, Status: LOCKED
Schema: REMOTE_SCHEDULER_AGENT, Status: LOCKED
Schema: QWERTY, Status: OPEN
Schema: GSMUSER, Status: LOCKED
Schema: SYSRAC, Status: LOCKED
Schema: GSMROOTUSER, Status: LOCKED
Schema: SI_INFORMTN_SCHEMA, Status: LOCKED
Schema: AUDSYS, Status: LOCKED
Schema: DIP, Status: LOCKED
Schema: ORDPLUGINS, Status: LOCKED
Schema: SYSKM, Status: LOCKED
Schema: ORDDATA, Status: LOCKED
Schema: ORACLE_OCM, Status: LOCKED
Schema: SYSDG, Status: LOCKED
Schema: ORDSYS, Status: LOCKED
Schema: JAGAN, Status: OPEN
PL/SQL procedure successfully completed.
Explanation:
- By the querying DBA_USERS view, you will get a more comprehensive view of all the user accounts along with the current status.
- The above output shows the schema name and account status to the console.
Note: To execute the PL/SQL code, make sure that we need to appropriate privileges typically granted by administrator of the database.
How to Show a List of Databases in PL/SQL?
Managing databases is a fundamental aspect of database administration and development. In Oracle Database, schemas represent logical containers for database objects like tables, views, procedures and functions. PL/SQL is the procedural extension of and used in Oracle Database and provides powerful capabilities for database programming and management.
In this article, We will learn about How to Show or List Databases by understanding the various methods with the help of examples and so on.
Contact Us