Finding objects:
In this post I am trying to give simple example to find a database object like table or any other type.Finding the objects and it’s properties and it’s current state are extremely useful in some cases. For example we can find out all the invalid procedures in one schema nd recompile it again.
The query given below will give you all the objects which are of type ‘table’

select * from user_objects u where u.object_type like '%TABLE%';

The above query will give you a result with all the tables ,irrespective of ‘VALID’ or ‘INVALID’

SQL> select object_name,Object_type, status from user_objects u where u.object_type like '%TABLE%';

OBJECT_NAME                            OBJECT_TYPE         STATUS
BULK_PROF_KTM_RFS_QT                    TABLE               VALID
BK_ABCACHE_UPDATE_QT                    TABLE               VALID
AQ$_UPDATE_QT_S       TABLE               VALID
AQ$_UPDATE_QT_T       TABLE               VALID
SYS_IOT_OVER_63751                      TABLE               VALID
AQ$_CACHE_UPDATE_QT_G                   TABLE               VALID
AQ$_CACHE_UPDATE_QT_I                   TABLE               VALID
AQ$_CACHE_UPDATE_QT_P                   TABLE               VALID
SYS_IOT_OVER_63792                      TABLE               VALID
AQ$_CACHE_UPDATE_QT_D                   TABLE               VALID

Finding invalid objects:-

For example if you want find ‘INVALID’ views in your schema,you can find them as follows.

SQL> select object_name,Object_type, status from user_objects u where u.object_type like 'VIEW%' and u.status like '%INVALID%';

OBJECT_NAME                           OBJECT_TYPE         STATUS
PARK_VCHR_DET_V    VIEW               INVALID
LOUNGE_DET_V    VIEW               INVALID
HIRE_DET_V     VIEW               INVALID
TRNFER_DET_V    VIEW               INVALID
VCHR_DET_V     VIEW               INVALID
FLIGHT_V     VIEW               INVALID
PAX_DET_V     VIEW               INVALID

The following will give you invalid procedures.

SQL> select object_name,Object_type, status from user_objects u where u.object_type like '%PROCEDURE%' and u.status like '%INVALID%';

OBJECT_NAME                          OBJECT_TYPE         STATUS

PPTEMP_POPULATE_COUNT                PROCEDURE           INVALID
PPTEMP_PRICE_REDUCTION               PROCEDURE           INVALID
GEOGR_REMOVE_MAPPING                 PROCEDURE           INVALID
EXTERNAL_MAPPING                     PROCEDURE           INVALID
LOAD_DATA_FROM_SCRATCH               PROCEDURE           INVALID
GENERATE_VOUCHER                     PROCEDURE           INVALID
GEOGRAPHY_AREA       PROCEDURE           INVALID
PPTEMP_POPULATE_EXTRA                PROCEDURE           INVALID
POPULATE_INCOMPLETE_DATA             PROCEDURE           INVALID

The following will give you the number of invalid procedures in your schema.

SQL> select count(*) INVALID_PROCEDURE_COUNT from user_objects u where u.object_type like '%PROCEDURE%' and u.status like '%INVALID%';</span>

INVALID_PROCEDURE_COUNT
                    14

Now how to find the total number of Indexes existing in schema

SQL> select count(*) TOTAL_INDEX_COUNT from user_objects u where u.object_type like 'INDEX';
TOTAL_INDEX_COUNT
 1328

database objects, valid and INVALID db objects, finding schema object, SQL, user_objects