Dobject (dobject.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script provides a comprehensive view of database objects including their creation times, last modification times, and validity status. Essential for understanding schema structure and tracking object changes.

The Script

rem  dobject.sql
rem
set  linesize 150
rem
ttitle 'Database Objects'
rem
col owner format a12 heading 'OWNER'
col object_id format 9999999999
col object_name format a30 heading 'OBJECT NAME'
col subobject_name format a25 heading 'SUBOBJECT NAME'
rem col object_id format 99999 heading 'ID'
col data_object_id format 99999 heading 'DATA|OBJECT ID'
col object_type format a13 heading 'OBJECT TYPE'
col created format a14 heading 'CREATED'
col modified format a14 heading 'MODIFIED'
col status format a1 heading 'S'
rem
break on owner skip 1
rem
select owner, object_id, object_name, subobject_name, object_type,
       -- data_object_id,
       to_char(created, 'mm/dd/yy hh24:mi') created,
       to_char(decode( last_ddl_time, created, to_date(null), last_ddl_time ),'mm/dd/yy hh24:mi') modified,
       decode( status, 'VALID', null, 'INVALID', 'I', '?' ) status
  from sys.dba_objects
 where owner like nvl(upper('&owner'), '%')
   and object_type like nvl(upper('&type'), '%')
   and object_name like nvl(upper('&object'), '%')
   and (subobject_name like nvl(upper('&subobject'), '%')
        or subobject_name is null)
 order by owner, object_name;

Parameters

The script prompts for:

• &Schema - owner to filter (use % for all)
• &Object - type to filter (use % for all)
• &Object - name pattern to filter (use % for all)
• &Subobject - name pattern for partitions (use % for all)

Usage

-- Basic usage
@dobject.sql

-- When prompted, enter:
-- owner: Schema owner to filter (use % for all)
-- type: Object type to filter (use % for all)
-- object: Object name pattern to filter (use % for all)
-- subobject: Subobject name pattern for partitions (use % for all)

Sample Output

Database Objects

OWNER           OBJECT_ID OBJECT NAME                    SUBOBJECT NAME            OBJECT TYPE   CREATED        MODIFIED       S
------------ ----------- ------------------------------ ------------------------- ------------- -------------- -------------- -
HR                 73847 EMPLOYEES                                                TABLE         01/15/23 10:30 03/20/24 14:22
                   73848 EMPLOYEES_PK                                             INDEX         01/15/23 10:30
                   73849 EMP_EMAIL_UK                                             INDEX         01/15/23 10:30
                   73850 EMP_EMP_ID_PK                                            INDEX         01/15/23 10:30
                   73851 EMP_DEPARTMENT_IX                                        INDEX         01/15/23 10:31
                   73852 EMP_JOB_IX                                               INDEX         01/15/23 10:31
                   73853 EMP_MANAGER_IX                                           INDEX         01/15/23 10:31
                   73854 EMP_NAME_IX                                              INDEX         01/15/23 10:31
                   73855 EMPLOYEE_AUDIT_TRG                                       TRIGGER       02/10/23 09:15 02/10/23 09:15 I

SCOTT              84592 EMP                                                      TABLE         05/20/22 11:00 01/05/24 16:45
                   84593 PK_EMP                                                   INDEX         05/20/22 11:00