Users/Schemas/Objects/Segments

Schema Information

-- Requires Oracle 18c or later

SELECT u.username,

       u.created,

       u.profile,

       u.account_status,

       COUNT(o.object_name) AS objcnt

  FROM dba_users u,

       dba_objects o

 WHERE u.username = o.owner

 GROUP BY u.username,

          u.created,

          u.profile,

          u.account_status

 ORDER BY u.username;

Set Current Schema

ALTER SESSION SET CURRENT_SCHEMA=MYSCHEMA

Object Types (from OBJ$)

You can query OBJ$ to get the TYPE# of an object. A table of the different types is provided below...  

SELECT DISTINCT type#

  FROM obj$

 ORDER BY 1;

Bibliography