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
/
Output:
OWNER OBJECT_NAME OBJECT_TYPE --------------- ---------------------------------------- ----------------- ORDSYS flashType172_T TYPE PUBLIC DBMS_FLASHBACK SYNONYM PUBLIC FLASHBACK_TRANSACTION_QUERY SYNONYM PUBLIC GV$FLASHBACK_DATABASE_LOG SYNONYM PUBLIC GV$FLASHBACK_DATABASE_LOGFILE SYNONYM PUBLIC GV$FLASHBACK_DATABASE_STAT SYNONYM PUBLIC V$FLASHBACK_DATABASE_LOG SYNONYM PUBLIC V$FLASHBACK_DATABASE_LOGFILE SYNONYM PUBLIC V$FLASHBACK_DATABASE_STAT SYNONYM PUBLIC V$FLASH_RECOVERY_AREA_USAGE SYNONYM SYS DBMS_FLASHBACK PACKAGE SYS DBMS_FLASHBACK PACKAGE BODY SYS FLASHBACKTBLIST TYPE SYS FLASHBACK_TRANSACTION_QUERY VIEW SYS GV_$FLASHBACK_DATABASE_LOG VIEW SYS GV_$FLASHBACK_DATABASE_LOGFILE VIEW SYS GV_$FLASHBACK_DATABASE_STAT VIEW SYS V_$FLASHBACK_DATABASE_LOG VIEW SYS V_$FLASHBACK_DATABASE_LOGFILE VIEW SYS V_$FLASHBACK_DATABASE_STAT VIEW SYS V_$FLASH_RECOVERY_AREA_USAGE VIEW 21 rows selected.
Invalid objects
set lines 200 pages 999 col "obj" format a40 select owner || '.' || object_name "obj", object_type from dba_objects where status = 'INVALID' / Output:
OBJ OBJECT_TYPE ---------------------------------------- -------------------- WEBUSER.CHECK PACKAGE BODY WEBUSER.MAILER PACKAGE WEBUSER.APP_FUNCS PACKAGE BODY PUBLIC.DBA_HIST_FILESTATXS SYNONYM PUBLIC.DBA_HIST_SQLSTAT SYNONYM PUBLIC.DBA_HIST_SQLBIND SYNONYM PUBLIC.DBA_HIST_TABLESPACE_STAT SYNONYM PUBLIC.DBA_HIST_SERVICE_STAT SYNONYM PUBLIC.DBA_HIST_SERVICE_WAIT_CLASS SYNONYM ANDY.TEST_PROC PROCEDURE
Recompile all invalid objects...
@?/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' /
Output:
Enter value for obj_name: LOTS_OF_ROWS old 5: where segment_name like '&obj_name' new 5: where segment_name like 'LOTS_OF_ROWS' SEGMENT_NAME SIZE_BYTES SIZE_MB -------------------- ---------- ---------- LOTS_OF_ROWS 4194304 4 1 row selected.
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 / Output:
Enter value for user: ANDY old 4: where owner = '&user' new 4: where owner = 'ANDY' OBJECT_NAME OBJECT_TYPE ---------------------------------------- -------------------- BIN$Q+k4qsgBLNngRAAQg5ZRWg==$0 INDEX BIN$Q+k4qsgELNngRAAQg5ZRWg==$0 INDEX BOX_PK INDEX CD_PK INDEX LOCATION_PK INDEX SYS_C0023466 INDEX SYS_C0023467 INDEX SYS_C0027234 INDEX TRACK_PK INDEX TEST_PROC PROCEDURE BOX_ID_SEQ SEQUENCE CD_ID_SEQ SEQUENCE LOCATION_ID_SEQ SEQUENCE BIN$Q+k4qsgCLNngRAAQg5ZRWg==$0 TABLE BIN$Q+k4qsgFLNngRAAQg5ZRWg==$0 TABLE BOX TABLE CD TABLE LOCATION TABLE LOTS_OF_ROWS TABLE POO TABLE SERVERS TABLE TEST_TABLE TABLE TRACK TABLE 23 rows selected.
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')
/
Output:
Enter value for col: HOST%
old 4: where column_name like upper('&col')
new 4: where column_name like upper('HOST%')
TAB COLUMN_NAME
------------------------------------------------------------ --------------------
SYS.LINK$ HOST
SYS.V_$INSTANCE HOST_NAME
SYS.GV_$INSTANCE HOST_NAME
SYS.USER_DB_LINKS HOST
SYS.ALL_DB_LINKS HOST
SYS.DBA_DB_LINKS HOST
SYS.DBA_2PC_PENDING HOST
SYS.EXU9LNK HOST
SYS.EXU8LNKU HOST
SYS.EXU9LNKU HOST
SYS.EXU8LNK HOST
SYS.EXU7LNK HOST
.
.
.
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 /
No comments:
Post a Comment