Find an object

set pages 999
col owner format a15
col object_name format a40
col object_type format a20
select owner
,      object_name
,      object_type
from   dba_objects
where  lower(object_name) like lower('%&object%')
order by owner, object_type, object_name
/

Invalid objects

set lines 200 pages 999
col "obj" format a40
select owner || '.' || object_name "obj",  
object_type
from dba_objects
where status = 'INVALID'
/

@?/rdbms/admin/utlrp.sql

Show the size of an object

col segment_name format a20
select segment_name
,      bytes "SIZE_BYTES"
,      ceil(bytes / 1024 / 1024) "SIZE_MB"
from   dba_segments
where  segment_name like '&obj_name'
/

All objects owned by a user

col object_name format a40
select object_name
,      object_type
from dba_objects
where owner = '&user'
order by object_type, object_name
/

Source code of a procedure

select text
from dba_source
where owner = 'ANDY'
and name = 'FILE_TEST'
and type = 'PACKAGE BODY'
order by line
/

Get an objects ddl (9i onwards)

set pagesize 0
set long 90000
select dbms_metadata.get_ddl('TABLE','TABLE_A','ANDY') from dual;

Display compilation errors and warnings

show errors 
show errors view <veiw_name>
show errors procedure <proc_name>

select * from dba_errors;

Find all tables containing the specified column

set pages 999 lines 100
col tab	format a60
col column_name format a20
select	owner || '.' || table_name as tab
,	column_name
from	dba_tab_columns
where	column_name like upper('&col')
/

List all tables owned by a user sorted by size

set lines 100 pages 999
col	segment_name	format a40
col 	mb 		format 999,999,999
select	segment_name
,	ceil(sum(bytes) / 1024 / 1024) "MB"
from	dba_segments
where	owner like '&user'
and	segment_type = 'TABLE'
group	by segment_name
order 	by ceil(sum(bytes) / 1024 / 1024) desc
/