How to use Querying the ALL_USERS view In SQL
Let’s Create a PL/SQL script to list all the schemas in an Oracle Database. The script should query the `ALL_USERS` view to fetch the schema names and iterate through the result set and output each schema name.
DECLARE
v_schema_name VARCHAR2(100);
BEGIN
FOR user_rec IN (SELECT username FROM all_users) LOOP
v_schema_name := user_rec.username;
DBMS_OUTPUT.PUT_LINE('Schema: ' || v_schema_name);
END LOOP;
END;
/
Output:
Schema: SYS
Schema: AUDSYS
Schema: SYSTEM
Schema: SYSBACKUP
Schema: SYSDG
Schema: SYSKM
Schema: SYSRAC
Schema: OUTLN
Schema: XS$NULL
Schema: GSMADMIN_INTERNAL
Schema: GSMUSER
Schema: GSMROOTUSER
Schema: DIP
Schema: REMOTE_SCHEDULER_AGENT
Schema: DBSFWUSER
Schema: ORACLE_OCM
Schema: SYS$UMF
Schema: DBSNMP
Schema: APPQOSSYS
Schema: GSMCATUSER
Schema: GGSYS
Schema: XDB
Schema: ANONYMOUS
Schema: WMSYS
Schema: MDDATA
Schema: OJVMSYS
Schema: CTXSYS
Schema: ORDSYS
Schema: ORDDATA
Schema: ORDPLUGINS
Schema: SI_INFORMTN_SCHEMA
Schema: MDSYS
Schema: OLAPSYS
Schema: DVSYS
Schema: LBACSYS
Schema: DVF
Schema: HR
Schema: JAGAN
Schema: QWERTY
PL/SQL procedure successfully completed.
The above output shows the names of the schemas available in Oracle Database instance.
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