Search all objects based on partial input values for owner, type, name and/or id
SQL> @soa
[= allowed ] Object Name : dba_b
[=,! allowed] Object Type :
[= allowed ] Owner : s
Object ID:
Obj Type Obj Name Sub Obj Owner Timestamp Created Last DDL Status
------------------- ------------------------------ ---------- ---------------------------- -------------------- ------------------- ------------------- -------
VIEW DBA_BASE_TABLE_MVIEWS SYS 2011-09-17:09:47:51 17/09/2011 09:47:51 04/05/2012 15:27:13 VALID
VIEW DBA_BLOCKERS SYS 2011-09-17:09:47:33 17/09/2011 09:47:33 04/05/2012 15:27:10 VALID
2 rows selected.
prompt
column "Obj Name" for a30
column "Owner" for a28
COLUMN "Timestamp" for A20
col "Sub Obj" for a10
col user_t new_value user_s noprint
select user user_t from dual;
accept x_obj_name prompt "[= allowed ] Object Name : "
accept x_obj_type prompt "[=,! allowed] Object Type : "
accept x_owner prompt "[= allowed ] Owner : "
accept x_object_id prompt "Object ID: "
prompt
select object_type "Obj Type",
object_name "Obj Name",
subobject_name "Sub Obj",
a.owner "Owner",
substr( timestamp, 1, 20 ) "Timestamp",
created "Created",
last_ddl_time "Last DDL",
a.status "Status"
from dba_objects a
where ( ( substr( upper( nvl( '&&x_obj_name', 'x' ) ), 1, 1 ) <> '='
and instr( nvl( '&&x_obj_name', 'x' ), '_' ) = 0
and upper( object_name ) like upper( '%&&x_obj_name%' ) )
or
( substr( upper( '&&x_obj_name' ), 1, 1 ) = '=' and object_name = upper( substr( '&&x_obj_name', 2 ) ) )
or
-- Oracle substitutes '_' as a generalised expression
( substr( upper( nvl( '&&x_obj_name', 'x' ) ), 1, 1 ) <> '='
and instr( nvl( '&&x_obj_name', 'x' ), '_' ) > 0
and instr( upper( object_name ), upper( '&&x_obj_name' ) ) > 0
)
)
and ( ( substr( upper( '&&x_obj_type' ), 1, 1 ) = '!' and object_type <> upper( substr( '&&x_obj_type', 2 ) ) )
or
( substr( upper( '&&x_obj_type' ), 1, 1 ) = '=' and object_type = upper( substr( '&&x_obj_type', 2 ) ) )
or
( substr( upper( nvl( '&&x_obj_type', 'x' ) ), 1, 1 ) <> '='
and substr( upper( nvl( '&&x_obj_type', 'x' ) ), 1, 1 ) <> '!'
and object_type like upper( '%&&x_obj_type%' ) )
)
and ( ( substr( upper( nvl( '&&x_owner', 'x' ) ), 1, 1 ) <> '=' and a.owner like upper( '%&&x_owner%' ) )
or
( substr( upper( '&&x_owner' ), 1, 1 ) = '=' and a.owner = upper( substr( '&&x_owner', 2 ) ) )
)
and ( '&&x_object_id' is null or a.object_id = '&&x_object_id' )
and not exists
( select 1
from dba_recyclebin
where owner = a.owner
and object_name = a.object_name
)
order by a.object_name, a.object_type, a.owner, a.created
/