ALTER <OBJECT_TYPE> [schema.]package_name COMPILE ;

• Paquete:

ALTER PACKAGE [schema.]package_name COMPILE [DEBUG PACKAGE|SPECIFICATION|BODY];

• Trigger:

ALTER TRIGGER [schema.]package_name COMPILE ;

See: [SCRIPT] Recompile all database objects

Excluir las claúsulas de storage:

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);

Excluir

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',TRUE);

Que escriba el terminador de objeto (muy recomendado):

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);

No Incluir fk's:

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'REF_CONSTRAINTS',FALSE);

No incluir constraints:

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',FALSE);

SELECT DBMS_METADATA.GET_DDL('OBJECT_TYPE','OBJECT_NAME','SCHEMANAME') FROM DUAL;

Lista de nombre de objetos soportados por DBMS_METADATA.

SELECT DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_TYPE','OBJECT_NAME','SCHEMANAME') FROM DUAL;

Buscar OBJ# (object identifier):

col name format a40
col subname format a40
 
SELECT obj#,
  dataobj#,
  name,
  subname,
  CTIME ,
  MTIME,
  STIME ,
  STATUS
FROM obj$
WHERE name LIKE '%OBJECT_NAME%';

Buscar SOURCE:

SELECT *
FROM SOURCE$
WHERE OBJ# IN
  ( 
  SELECT obj# FROM obj$ WHERE name LIKE '%OBJECT_NAME%'
  ) ;

SELECT TABLE_NAME FROM dict WHERE TABLE_NAME LIKE 'V$%' ;

SELECT name FROM V$FIXED_TABLE ;

No existe ninguna tabla que determine el nombre de los objetos, se pueden obtener (más o menos así):

SELECT OBJECT_TYPE FROM SYS.ALL_OBJECTS GROUP BY OBJECT_TYPE ;

Cualquier owner:

SELECT OBJECT_NAME, OWNER, OBJECT_TYPE
FROM SYS.ALL_OBJECTS
WHERE OBJECT_TYPE LIKE 'OBJECT_TYPE'
;

Con un owner determinado:

SELECT OBJECT_NAME, OWNER, OBJECT_TYPE
FROM SYS.ALL_OBJECTS
WHERE OBJECT_TYPE LIKE 'OBJECT_TYPE'
AND OWNER LIKE 'OWNER'
;

Varios tipos

SELECT   * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('FUNCTION','PROCEDURE','PACKAGE') ;

Se pueden listar:

SELECT TABLE_NAME FROM all_tables ;

SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE='TABLE' AND OWNER LIKE '%username%';

SELECT TABLE_NAME FROM DICT WHERE TABLE_NAME LIKE '%EXAMPLE%' ;

SELECT index_name FROM dba_indexes WHERE TABLE_NAME='tablename'; 

Y la descripción del índice:

SELECT DBMS_METADATA.GET_DDL('INDEX','INDEXNAME','SCHEMANAME') FROM DUAL;

O una descripción rápida:

COL INDEX_OWNER  FORMAT A30 ;      
COL INDEX_NAME  FORMAT A30 ;       
COL TABLE_OWNER  FORMAT A30 ;      
COL TABLE_NAME  FORMAT A30 ;       
COL COLUMN_NAME  FORMAT A30 ;      
COL COLUMN_POSITION  FORMAT 9999999
COL COLUMN_LENGTH  FORMAT 9999999
COL CHAR_LENGTH  FORMAT 9999999
COL DESCEND  FORMAT A30 ;          
SELECT * FROM ALL_IND_COLUMNS WHERE OWNER='<USERNAME>' AND TABLE_NAME='<TNAME>' ;

COL OWNER FORMAT A30;
COL TABLE_NAME FORMAT A30;
COL COLUMN_NAME FORMAT A30;
COL DATA_TYPE FORMAT A50;
SELECT OWNER, 
        TABLE_NAME, 
        COLUMN_NAME, 
        DATA_TYPE, 
        DATA_LENGTH 
FROM ALL_TAB_COLUMNS 
WHERE OWNER = '<USERNAME>'
ORDER BY OWNER,TABLE_NAME ;

SELECT TABLE_OWNER, TABLE_NAME 
FROM  ALL_TAB_PARTITIONS 
WHERE TABLE_OWNER NOT IN ('SYS', 'SYSTEM') 
GROUP BY TABLE_OWNER, TABLE_NAME ;

Más info:

col table_owner format a20
col COMPRESS_FOR format a30
 
SELECT
TABLE_OWNER,
TABLE_NAME,
PARTITION_NAME,
PARTITION_POSITION,
TABLESPACE_NAME,
LOGGING,
COMPRESSION,
COMPRESS_FOR,
AVG_ROW_LEN
FROM DBA_TAB_PARTITIONS 
WHERE TABLE_OWNER NOT IN ('SYS', 'SYSTEM')
ORDER BY PARTITION_NAME
/

COL TABLE_OWNER FORMAT A20;
COL TABLE_NAME FORMAT A20;
SELECT TABLE_OWNER, TABLE_NAME, PARTITION_POSITION, NUM_ROWS
FROM ALL_TAB_PARTITIONS
WHERE TABLE_OWNER NOT IN ('SYS', 'SYSTEM')
AND NUM_ROWS IS NOT NULL
ORDER BY PARTITION_POSITION DESC, TABLE_NAME ASC ;

dead

• https://netfiles.uiuc.edu/jstrode/www/oradd/dict_catagories.html

Encontrar Tablas Maestras/padres/primarias (tablas SIN fk's y referenciadas por otras tablas):

SELECT TABLE_NAME
FROM dba_CONSTRAINTS
WHERE owner='<USERNAME>'
AND CONSTRAINT_NAME IN (
    SELECT R_CONSTRAINT_NAME
    FROM dba_CONSTRAINTS
    WHERE owner='<USERNAME>'
    AND CONSTRAINT_TYPE='R'
    )
AND NOT TABLE_NAME IN (
    SELECT TABLE_NAME
    FROM dba_CONSTRAINTS
    WHERE owner='<USERNAME>'
    AND CONSTRAINT_TYPE='R'
    )
ORDER BY TABLE_NAME
/

COL NAME FORMAT A35
COL OWNER FORMAT A30
COL TYPE FORMAT A30
COL REFERENCED_OWNER FORMAT A30
COL REFERENCED_TYPE FORMAT A30
COL DEPENDENCY_TYPE FORMAT A20
COL REFERENCED_NAME FORMAT A33
SELECT 
   owner, 
   TYPE, 
   name,
   referenced_owner, 
   referenced_type, 
   referenced_name
FROM 
   dba_dependencies
WHERE 
   ((owner LIKE UPPER('&1') AND name LIKE UPPER('&2')) 
   OR
   (referenced_owner LIKE UPPER('&1') AND referenced_name LIKE UPPER('&2') ))
   AND 
   referenced_owner != 'SYS'
   AND 
   referenced_type != 'NON-EXISTENT'
ORDER BY 
   owner, TYPE, name;

Ejecutar:

@${ORACLE_HOME}/rdbms/admin/utldtree.sql

Después ejecutar para el objetos que queramos, el procedure:

EXEC deptree_fill('object_type', 'object_owner', 'object_name');

y ver el resultado:

SELECT * FROM ideptree;

Link

The attached SQL file will show all dependencies for an object, both forwards and backwards through the heirarchy of objects in DBA_DEPENDENCIES, along with the LAST_DDL_TIME of all children.

The LAST_DDL_TIME should help track down why an object became invalid.

I'm fairly sure this query will not work in 8i. :(

You can circumvent that by logging into some account other than SYS and creating a table DBA_DEPENDENCIES. (it doesn't work in 8i due to queries on complex views and 'connect by' not working)

create table scott.dba_dependencies as select * from sys.dba_dependencies ;

--
Jared Still
Certifiable Oracle DBA AND Part TIME Perl Evangelist
-- dba_dependencies.sql
-- jkstill@xxxxxxxxx
-- base query from Jacques Kilchoer
-- 11/07/2006 - jkstill - added no_merge hints
--   encapsulated into inline view
--   added 'level'
--   display child DDL time
--
-- call on the command line:
--   @dba_dependencies <OWNER> <OBJECT>
-- if not on the command line, user will be
-- prompted for values
 
 
@clears
@COLUMNS
 
prompt Dependencies FOR Owner?:
 
col cowner noprint new_value uowner
SET term off feed off
SELECT UPPER('&1') cowner FROM dual;
SET term ON feed ON
 
prompt Dependencies FOR Object?:
 
col cobject noprint new_value uobject
SET term off feed off
SELECT UPPER('&2') cobject FROM dual;
SET term ON feed ON
 
SET line 142 pages 60
 
COLUMN display_parent format a58
COLUMN display_child format a58
COLUMN referenced_owner noprint
COLUMN referenced_object noprint
COLUMN referenced_type noprint
COLUMN owner noprint
COLUMN object noprint
COLUMN TYPE noprint
COLUMN last_ddl_time format a22 head 'CHILD DDL TIME'
 
undef 1 2
 
WITH dependencies AS (
        -- top down through the heirarchy
        SELECT /*+ no_merge */
                referenced_type || ' "' || referenced_owner || '"."' ||
                referenced_name || '"' AS parent,
                TYPE || ' "' || owner || '"."' || name || '"' AS child,
                level hlevel,
                referenced_owner, referenced_name, referenced_type,
                owner, name, TYPE
        FROM dba_dependencies
        START WITH
                referenced_owner = '&&uowner'
                AND referenced_name = '&&uobject'
        CONNECT BY
                referenced_owner = prior owner
                AND referenced_name = prior name
                AND referenced_type = prior TYPE
        UNION
        -- bottom up through the heirarchy
        SELECT /*+ no_merge */
                referenced_type || ' "' || referenced_owner || '"."' ||
                referenced_name || '"' AS parent,
                TYPE || ' "' || owner || '"."' || name || '"' AS child,
                level hlevel,
                referenced_owner, referenced_name, referenced_type,
                owner, name, TYPE
        FROM dba_dependencies
        START WITH
                owner = '&&uowner'
                AND name = '&&uobject'
        CONNECT BY
                owner = prior referenced_owner
                AND name = prior referenced_name
                AND TYPE = prior referenced_type
        ORDER BY 1, 2
)
SELECT lpad(' ',2*d.hlevel,' ') || d.parent display_parent, d.child 
display_child, o.last_ddl_time 
FROM dependencies d, dba_objects o
WHERE o.owner = d.owner
AND o.object_type = d.type
AND d.name = o.object_name
ORDER BY parent, child
/

COL OWNER FORMAT A30
COL R_OWNER FORMAT A30
COL CONSTRAINT_NAME FORMAT A50
COL R_CONSTRAINT_NAME FORMAT A50
 
SELECT OWNER,
        CONSTRAINT_NAME,
        CONSTRAINT_TYPE,
        TABLE_NAME,
        R_OWNER,
        R_CONSTRAINT_NAME,
        STATUS
FROM USER_CONSTRAINTS 
WHERE TABLE_NAME LIKE '%TABLE_NAME%'
;

COL SCHEMA_NAME FORMAT A40 
COL REFERENCED_SCHEMA_NAME FORMAT A40 
COL REFERENCED_NAME FORMAT A40 
col REFERENCED_TYPE  format a30 
 
SELECT owner AS schema_name,
       name AS view_name,
       referenced_owner AS referenced_schema_name,
       referenced_name,
       referenced_type
FROM sys.dba_dependencies
WHERE TYPE = 'VIEW'
-- AND OWNER='OWNER'
-- and name LIKE '%VIEW_NAME%'
ORDER BY owner, name, referenced_name, referenced_owner, referenced_type
/

• http://docs.oracle.com/cd/B28359_01/server.111/b28318/dependencies.htm
• http://www.dba-oracle.com/d_dba_dependencies.htm