This is an old revision of the document!
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 |
dbatables | objetos accesibles siendo un dba (todo) | | usertables | 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 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 ;
<code sql>
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'
-- 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 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 (
- - 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>
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 /