User and its average object id : user_objects « System Tables Data Dictionary « Oracle PL/SQL Tutorial






  1. Oracle PL/SQL Tutorial
  2. System Tables Data Dictionary
  3. user_objects
SQL>
SQL> select a.username, (select count(*) from all_objects b where b.owner = a.username) cnt,
  2                     (select avg(object_id ) from all_objects b where b.owner = a.username) avg
  3  from all_users a
  4  /

USERNAME                              CNT        AVG
------------------------------ ---------- ----------
SYS                                  6520 5009.74064
SYSTEM                                422 6095.87678
OUTLN                                   7 1172.57143
DIP                                     0
TSMSYS                                  2     8606.5
INV15                                   2    16237.5
DBSNMP                                 46 9592.65217
INV10                                   2    16227.5
CTXSYS                                338 9877.92012
XDB                                   334 10800.7485
ANONYMOUS                               0

USERNAME                              CNT        AVG
------------------------------ ---------- ----------
MDSYS                                 458 11667.2009
HR                                     34    12104.5
FLOWS_FILES                            11 12717.2727
FLOWS_020100                         1085  12813.424
JAVA2S                                530 16254.6849
INV11                                   2    16229.5
INV12                                   2    16231.5
INV13                                   2    16233.5
INV14                                   2    16235.5
PLSQL                                   0
INV16                                   2    16239.5

USERNAME                              CNT        AVG
------------------------------ ---------- ----------
INV17                                   2    16241.5
INV18                                   2    16243.5
INV19                                   2    16245.5
INV20                                   2    16247.5
DEFINER                                 4    16250.5

27 rows selected.

SQL>
SQL>








30.90.user_objects
30.90.1.Check user_objects for object name
30.90.2.Check the package status in user_objects
30.90.3.Check function/procedure status
30.90.4.Check the status for all stored procedures is by using the Oracle data dictionary view USER_OBJECTS
30.90.5.Get invalid table objects by joining user_segments and user_objects
30.90.6.Get all invalid user objects in user_objects table
30.90.7.Create drop command by query the user_objects table
30.90.8.Create grant permission command by querying user_objects table
30.90.9.Query user_objects for all packages, procedures and functions
30.90.10.Query user_objects for user-defined data type
30.90.11.Recompile all invalid package
30.90.12.Query user_objects for all java classes
30.90.13.Query user_objects for all PL/SQL code
30.90.14.Get Object id from user_objects
30.90.15.Join sys.col$ and user_objects
30.90.16.Query user_objects for object status
30.90.17.Query user_objects for your functions and procedures
30.90.18.User and its average object id
30.90.19.Show the procedure is marked invalid **
30.90.20.Behavior of dependent objects.
30.90.21.Finding, Validating, and Describing Packages
30.90.22.If procedure is valid