Wednesday Jan 21, 2015

Oracle Supplied Objects Listing in upobjxt.lst ... ... Oracle Supplied Users Listing in upuserxt.lst

Maybe not important to many people but as I didn't find much about it via a search I'd thought I post something about the new upobjxt.lst and upuserxt.lst files. Since Oracle Database 12.1.0.1 we deliver these files in ?/rdbms/admin .

All Oracle supplied objects are kept in an external table format.
The file's name is: upobjxt.lst

If you'd like to access it you may do the following:

  1. Create a directory object:
    CREATE OR REPLACE DIRECTORY ext_tab_data AS '/u01/app/oracle/product/12.1.0.2/rdbms/admin/';
    --
  2. Define the external table to query it:
    CREATE TABLE oracle_supplied_ext (
      owner       VARCHAR2(31),
      name        VARCHAR2(127),
      placeholder VARCHAR2(20),
      object_type number
    )
    ORGANIZATION EXTERNAL (
      TYPE ORACLE_LOADER
      DEFAULT DIRECTORY ext_tab_data
      ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        FIELDS TERMINATED BY ','
        MISSING FIELD VALUES ARE NULL
        (
          owner             CHAR(31),
          name              CHAR(127),
          placeholder       CHAR(20),
          object_type       CHAR(2)
        )
      )
      LOCATION ('upobjxt.lst')
    )
    PARALLEL 5
    REJECT LIMIT UNLIMITED;

    --
  3. Now you can query from the table oracle_supplied_ext
    For example:
    select count(*), object_type
    from oracle_supplied_ext
    group by object_type
    order by 2;
    --
  4. Would you like to match object types to object codes? Try this one  ...:
    select count(o.type#), o.type#,d.object_type
     from dba_objects d, obj$ o
     where d.object_id=o.obj#
      group by o.type#,d.object_type
      order by 2;
    Object mapping can be found also in the definition of OBJ$ in the dcore.bsq file

All Oracle supplied users are kept in an external table format in another file named: upuserxt.lst

-Mike

Thursday Nov 17, 2011

DOAG Conference 2011: Seven Flavors of Database Upgrades

Thanks to everybody who did attend at my DOAG Conference session in Nürnberg this year "Seven Flavor of Database Upgrades" (or in German: "7 Wege zum Datenbank-Upgrade - Geschichten, die das Leben schrieb"). And thanks for your patience staying with me in overtime as well ;-)

In case you'd like to download the slides I've presented at the session please download them via this link or from the download section to your right.

About

Mike Dietrich - Oracle Mike Dietrich
Senior Principal Technologist - Database Upgrade Development Group - Oracle Corporation

Based near Munich/Germany and spending plenty of time in airplanes to run either upgrade workshops or work onsite with reference customers. Acting as interlink between customers and the Upgrade Development.

Contact me either via XING or LinkedIn

Search

Archives
« March 2015
SunMonTueWedThuFriSat
1
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
    
       
Today
Oracle related Tech Blogs
Slides Download Center
Visitors since 17-OCT-2011
White Paper and Docs
Workshops
Viewlets and Videos
This week on my Rega/iPod/CD
Workshop Map
Upgrade Reference Papers