Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Location
    USA/PA
    Posts
    6

    Unanswered: viewing all schemas in a database instance

    Hi newbie question here:

    We have 2 oracle database instances and we have several schemas in each. My question is, if I wanted to view the names of all the schemas created in each database how would I do it. I assume by going into SQL plus and doing a query? if so what is the query?


    Thanks

  2. #2
    Join Date
    Sep 2003
    Location
    NE Florida w/ view of co-workers
    Posts
    32
    If you want the name any user that has any object you can use this:

    select distinct owner from dba_objects;

    Note that you have to have rights to the dba_objects view, this is sys, system, or anyone with 'select any dictionary' role.

  3. #3
    Join Date
    Sep 2003
    Location
    Assen, Nederland
    Posts
    55
    Forget the output and look at the script...

    Code:
    SQL*Plus: Release 9.2.0.3.0 - Production on Mon Sep 22 22:17:00 2003
    
    Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
    
    Enter user-name: appdev@nick920
    Enter password:
    
    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
    With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.3.0 - Production
    
    appdev@NICK920.US.ORACLE.COM> set pages 100
    appdev@NICK920.US.ORACLE.COM> set lines 9999
    
    appdev@NICK920.US.ORACLE.COM> SELECT   username, COUNT (DECODE (o.TYPE#, 2, o.obj#, '')) "Tables",
      2           COUNT (DECODE (o.TYPE#, 1, o.obj#, '')) "Indexes",
      3           COUNT (DECODE (o.TYPE#, 5, o.obj#, '')) "Synonyms",
      4           COUNT (DECODE (o.TYPE#, 4, o.obj#, '')) "Views",
      5           COUNT (DECODE (o.TYPE#, 6, o.obj#, '')) "Seq",
      6           COUNT (DECODE (o.TYPE#, 7, o.obj#, '')) "Procs",
      7           COUNT (DECODE (o.TYPE#, 8, o.obj#, '')) "Fxns",
      8           COUNT (DECODE (o.TYPE#, 9, o.obj#, '')) "Pkgs",
      9           COUNT (DECODE (o.TYPE#, 12, o.obj#, '')) "Triggers",
     10           COUNT (DECODE (o.TYPE#, 10, o.obj#, '')) "dep"
     11      FROM SYS.obj$ o, dba_users u
     12     WHERE u.user_id = o.owner#(+) AND o.TYPE# IS NOT NULL
     13  GROUP BY username
     14  ORDER BY username;
    
    
    USERNAME                           Tables    Indexes   Synonyms      Views        Seq      Procs       Fxns       P
    kgs      Triggers        dep
    ------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------
    --- ---------- ----------
    APPDEV                                  1          0          0          0          0          0       0
      0             0          0
    CTXSYS                                 36         48          0         47          3          1       3
     53             0         32
    HR                                      7         19          0          1          3          2       0
      0             2          0
    MDSYS                                  18         25          0         26          2          0      47
     24             7         23
    ODM                                    36         48          0          1         10          2       0
     15             1         14
    ODM_MTR                                12          0          0          0          0          0       0
      0             0          0
    OE                                     10         31          6         10          1          0       0
      0             3          1
    OEM_CC345772-A                        236        266          0         63         48          0       0
     13            18          3
    OLAPSYS                                58         92          0        189          2          0       0
     35            31         59
    ORDPLUGINS                              0          0          0          0          0          0       0
     14             0          5
    ORDSYS                                  5          2          0          2          0          0       2
     16             0          8
    OUTLN                                   3          3          0          0          0          1       0
      0             0          1
    PM                                      3         20          0          0          0          0       0
      0             0          0
    QS                                     15         16          0          6          2          0       0
      0             0          1
    QS_ADM                                  0          0          0          0          0          0       0
      1             0          4
    QS_CBADM                                8          9          0          4          1          0       0
      0             0          1
    QS_CS                                   8          6          0          3          1          0       0
      0             0          1
    QS_ES                                  14         12          0          6          2          0       0
      0             0          1
    QS_OS                                  14         12          0          6          2          0       0
      0             0          1
    QS_WS                                  14         12          0          6          2          0       0
      0             0          1
    RMAN                                   30         66          0         27          1          0       0
      2             0          3
    SCOTT                                   4          2          0          0          0          0       0
      0             0          0
    SH                                     15         25          0          0          0          0       0
      0             0          0
    SYS                                   347        343          6       2088         41         25      41
    357            10         15
    SYSTEM                                130        178          8         10         19          1       0
      1             2          2
    WKSYS                                  38         51          0         49         18          3       3
     18            23         33
    WMSYS                                  23         35          0         58          9          1       0
      0             0         11
    XDB                                    28        306          0          2          2          3       3
     18             9         14
    
    28 rows selected.
    
    appdev@NICK920.US.ORACLE.COM>

    adjust the pagesize, linesize to see the output better in sqplus, if you're using some other dev tool like toad or pl/sql developer then it's no probs...

    Cheers!

    Tarry

  4. #4
    Join Date
    Sep 2003
    Location
    USA/PA
    Posts
    6

    thanks

    Originally posted by skempins
    If you want the name any user that has any object you can use this:

    select distinct owner from dba_objects;

    Note that you have to have rights to the dba_objects view, this is sys, system, or anyone with 'select any dictionary' role.

    Thanks, I'll give it a try

  5. #5
    Join Date
    Jan 2003
    Location
    Vienna, Austria
    Posts
    102

    Re: viewing all schemas in a database instance

    if you want to know all schemas of your DB (not only schemas owning any objects) use
    select username from dba_users;
    ^/\x

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •