Query user_objects for object status : user_objects « System Tables Data Dictionary « Oracle PL/SQL Tutorial






SQL>
SQL>
SQL> create table t ( x int );

Table created.

SQL>
SQL> create or replace view v as select * from t;

View created.

SQL>
SQL> create or replace procedure p
  2  as
  3  begin
  4          for x in ( select * from t )
  5          loop
  6                  null;
  7          end loop;
  8  end;
  9  /



SQL>
SQL> create or replace function f return number
  2  as
  3          countValue number;
  4  begin
  5          select count(*) into countValue from t;
  6          return countValue;
  7  end;
  8  /

Function created.

SQL>
SQL>
SQL> alter table t add y number
  2  /

Table altered.

SQL>
SQL> select object_name, object_type, status from user_objects
  2  where rownum < 10
  3  /

OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE         STATUS
------------------- -------
MYSTATS
INDEX               VALID

MYSTATS
TABLE               VALID

S
SEQUENCE            VALID


OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE         STATUS
------------------- -------
P_ADD_ITEMS
PROCEDURE           INVALID

WORKING_CUSTOMERS
TABLE               VALID

ADDTUPLE3
PROCEDURE           INVALID


OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE         STATUS
------------------- -------
P_ADD_ORDERS
PROCEDURE           INVALID

P_ADD_PROD
PROCEDURE           INVALID

UPDATE_PRODUCT_PRICE
PROCEDURE           INVALID


9 rows selected.

SQL>
SQL> drop table t;

Table dropped.








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