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 DBADEPENDENCIES, along with the LASTDDL_TIME of all children.

The LASTDDLTIME 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.dbadependencies as select * from sys.dbadependencies ;

Jared Still Certifiable Oracle DBA and Part Time Perl Evangelist – dbadependencies.sql – jkstill@xxxxxxxxx – base query from Jacques Kilchoer – 11/07/2006 - jkstill - added nomerge hints – encapsulated into inline view – added 'level'

– 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 displayparent format a58 column displaychild format a58 column referencedowner noprint column referencedobject noprint column referencedtype noprint column owner noprint column object noprint column type noprint column lastddl_time format a22 head 'CHILD DDL TIME'

undef 1 2

with dependencies as (

1. - 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 displayparent, d.child displaychild, o.lastddltime from dependencies d, dbaobjects o where o.owner = d.owner and o.objecttype = d.type and d.name = o.object_name order by parent, child / </code>

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