Table of Contents
ORACLE SQL QUERIES : Objects
SECCIÓN : Objetos
Compilar
Compilar un objeto
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 ;
Compilar todos los objetos invalidos
DBMS_METADATA
Tuning de la salida de DBMS_METADATA
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);
Obtener metadatos
SELECT DBMS_METADATA.GET_DDL('OBJECT_TYPE','OBJECT_NAME','SCHEMANAME') FROM DUAL;
Lista de nombre de objetos soportados por DBMS_METADATA.
Obtener metadatos de dependencias
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_TYPE','OBJECT_NAME','SCHEMANAME') FROM DUAL;
Usar source$ para obtener metadatos
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%' ) ;
Performance views de oracle
SELECT TABLE_NAME FROM dict WHERE TABLE_NAME LIKE 'V$%' ;
SELECT name FROM V$FIXED_TABLE ;
Tipos de objetos
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 ;
Objetos por tipo
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') ;
Tablas
Logado como un usuario
Se pueden listar:
NOMBRE DE LA TABLA | Descripción |
dba_tables | objetos accesibles siendo un dba (todo) |
user_tables | tablas accesibles por el usuario que estamos usando |
all_tables | todo |
SELECT TABLE_NAME FROM all_tables ;
Desde sys
SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE='TABLE' AND OWNER LIKE '%username%';
Query al dict
SELECT TABLE_NAME FROM DICT WHERE TABLE_NAME LIKE '%EXAMPLE%' ;
Ver índices de una tabla
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>' ;
Columnas de las tablas
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 ;
Tablas particionadas
Listar tablas particionadas de los usuarios
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 /
Numero de rows de cada partición
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 ;
Jerarquía de tablas
dead
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 /
Dependencias entre objetos
Query genérica para buscar deps
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;
Using utldtree.sql
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;
dba_dependencies.sql
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 /
Listar foreign keys de una tabla
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%' ;
List VIEW dependencies
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 /