dba:oracle:oracle_sql_querys:objects
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
dba:oracle:oracle_sql_querys:objects [2022/02/11 11:12] – ↷ Links adapted because of a move operation dodger | dba:oracle:oracle_sql_querys:objects [2023/01/31 08:15] (current) – removed dodger | ||
---|---|---|---|
Line 1: | Line 1: | ||
- | ====== ORACLE SQL QUERIES : Objects ====== | ||
- | |||
- | |||
- | |||
- | ======SECCIÓN : Objetos====== | ||
- | |||
- | ===== Compilar ===== | ||
- | |||
- | ==== Compilar un objeto ==== | ||
- | <code sql> | ||
- | </ | ||
- | * Paquete: | ||
- | <code sql> | ||
- | * Trigger: | ||
- | <code sql> | ||
- | </ | ||
- | |||
- | ==== Compilar todos los objetos invalidos ==== | ||
- | |||
- | See: | ||
- | [[dba: | ||
- | |||
- | ===== DBMS_METADATA ===== | ||
- | |||
- | ==== Tuning de la salida de DBMS_METADATA ==== | ||
- | Excluir las claúsulas de storage: | ||
- | <code sql> | ||
- | execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,' | ||
- | </ | ||
- | Excluir | ||
- | <code sql> | ||
- | execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,' | ||
- | </ | ||
- | Que escriba el terminador de objeto (muy recomendado): | ||
- | <code sql> | ||
- | execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,' | ||
- | </ | ||
- | No Incluir fk's: | ||
- | <code sql> | ||
- | execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,' | ||
- | </ | ||
- | No incluir constraints: | ||
- | <code sql> | ||
- | execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,' | ||
- | </ | ||
- | |||
- | ==== Obtener metadatos ==== | ||
- | <code sql> | ||
- | SELECT DBMS_METADATA.GET_DDL(' | ||
- | </ | ||
- | |||
- | Lista de nombre de objetos soportados por [[http:// | ||
- | |||
- | ==== Obtener metadatos de dependencias ==== | ||
- | <code sql> | ||
- | SELECT DBMS_METADATA.GET_DEPENDENT_DDL(' | ||
- | </ | ||
- | |||
- | |||
- | ==== Usar source$ para obtener metadatos ==== | ||
- | |||
- | Buscar OBJ# (object identifier): | ||
- | <code sql> | ||
- | col name format a40 | ||
- | col subname format a40 | ||
- | |||
- | SELECT obj#, | ||
- | dataobj#, | ||
- | name, | ||
- | subname, | ||
- | CTIME , | ||
- | MTIME, | ||
- | STIME , | ||
- | STATUS | ||
- | FROM obj$ | ||
- | WHERE name LIKE ' | ||
- | </ | ||
- | |||
- | Buscar SOURCE: | ||
- | <code sql> | ||
- | SELECT * | ||
- | FROM source$ | ||
- | WHERE OBJ# IN | ||
- | ( | ||
- | SELECT obj# FROM obj$ WHERE name LIKE ' | ||
- | ) ; | ||
- | </ | ||
- | |||
- | |||
- | |||
- | =====Performance views de oracle===== | ||
- | <code sql> | ||
- | </ | ||
- | |||
- | <code sql> | ||
- | 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í): | ||
- | <code sql> | ||
- | SELECT OBJECT_TYPE FROM SYS.ALL_OBJECTS GROUP BY OBJECT_TYPE ; | ||
- | </ | ||
- | |||
- | =====Objetos por tipo===== | ||
- | Cualquier owner: | ||
- | <code sql> | ||
- | SELECT OBJECT_NAME, | ||
- | FROM SYS.ALL_OBJECTS | ||
- | WHERE OBJECT_TYPE LIKE ' | ||
- | ; | ||
- | </ | ||
- | Con un owner determinado: | ||
- | <code sql> | ||
- | SELECT OBJECT_NAME, | ||
- | FROM SYS.ALL_OBJECTS | ||
- | WHERE OBJECT_TYPE LIKE ' | ||
- | AND OWNER LIKE ' | ||
- | ; | ||
- | </ | ||
- | |||
- | Varios tipos | ||
- | <code sql> | ||
- | SELECT | ||
- | </ | ||
- | |||
- | =====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 | | ||
- | |||
- | <code sql> | ||
- | ====Desde sys==== | ||
- | <code sql> | ||
- | |||
- | ====Query al dict==== | ||
- | <code sql> | ||
- | SELECT TABLE_NAME FROM DICT WHERE TABLE_NAME LIKE ' | ||
- | </ | ||
- | |||
- | =====Ver índices de una tabla===== | ||
- | <code sql> | ||
- | select index_name from dba_indexes where table_name=' | ||
- | </ | ||
- | Y la descripción del índice: | ||
- | <code sql> | ||
- | select DBMS_METADATA.GET_DDL(' | ||
- | </ | ||
- | |||
- | O una descripción rápida: | ||
- | <code sql> | ||
- | COL INDEX_OWNER | ||
- | COL INDEX_NAME | ||
- | COL TABLE_OWNER | ||
- | COL TABLE_NAME | ||
- | COL COLUMN_NAME | ||
- | COL COLUMN_POSITION | ||
- | COL COLUMN_LENGTH | ||
- | COL CHAR_LENGTH | ||
- | COL DESCEND | ||
- | SELECT * FROM ALL_IND_COLUMNS WHERE OWNER='< | ||
- | </ | ||
- | |||
- | ===== Columnas de las tablas ===== | ||
- | <code sql> | ||
- | 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 = '< | ||
- | ORDER BY OWNER, | ||
- | </ | ||
- | |||
- | ===== Tablas particionadas ===== | ||
- | ==== Listar tablas particionadas de los usuarios ==== | ||
- | <code sql> | ||
- | SELECT TABLE_OWNER, | ||
- | FROM ALL_TAB_PARTITIONS | ||
- | WHERE TABLE_OWNER NOT IN (' | ||
- | GROUP BY TABLE_OWNER, | ||
- | </ | ||
- | |||
- | Más info: | ||
- | <code sql> | ||
- | 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 (' | ||
- | order by PARTITION_NAME | ||
- | / | ||
- | </ | ||
- | ==== Numero de rows de cada partición ==== | ||
- | <code sql> | ||
- | COL TABLE_OWNER FORMAT A20; | ||
- | COL TABLE_NAME FORMAT A20; | ||
- | SELECT TABLE_OWNER, | ||
- | FROM ALL_TAB_PARTITIONS | ||
- | WHERE TABLE_OWNER NOT IN (' | ||
- | AND NUM_ROWS IS NOT NULL | ||
- | ORDER BY PARTITION_POSITION DESC, TABLE_NAME ASC ; | ||
- | </ | ||
- | |||
- | |||
- | |||
- | =====Jerarquía de tablas===== | ||
- | dead :-/ | ||
- | * [[https:// | ||
- | |||
- | Encontrar Tablas Maestras/ | ||
- | <code sql> | ||
- | SELECT TABLE_NAME | ||
- | FROM dba_CONSTRAINTS | ||
- | WHERE owner='< | ||
- | AND CONSTRAINT_NAME IN ( | ||
- | SELECT R_CONSTRAINT_NAME | ||
- | FROM dba_CONSTRAINTS | ||
- | WHERE owner='< | ||
- | AND CONSTRAINT_TYPE=' | ||
- | ) | ||
- | AND NOT TABLE_NAME IN ( | ||
- | SELECT TABLE_NAME | ||
- | FROM dba_CONSTRAINTS | ||
- | WHERE owner='< | ||
- | AND CONSTRAINT_TYPE=' | ||
- | ) | ||
- | ORDER BY TABLE_NAME | ||
- | / | ||
- | </ | ||
- | =====Dependencias entre objetos===== | ||
- | |||
- | |||
- | ==== Query genérica para buscar deps ==== | ||
- | <code sql> | ||
- | 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 | ||
- | | ||
- | | ||
- | name, | ||
- | | ||
- | | ||
- | | ||
- | from | ||
- | | ||
- | where | ||
- | | ||
- | or | ||
- | | ||
- | | ||
- | | ||
- | | ||
- | | ||
- | order by | ||
- | | ||
- | </ | ||
- | |||
- | ==== Using utldtree.sql ==== | ||
- | Ejecutar: | ||
- | < | ||
- | @${ORACLE_HOME}/ | ||
- | </ | ||
- | |||
- | Después ejecutar para el objetos que queramos, el procedure: | ||
- | <code sql> | ||
- | EXEC deptree_fill(' | ||
- | </ | ||
- | y ver el resultado: | ||
- | <code sql> | ||
- | select * from ideptree; | ||
- | </ | ||
- | |||
- | ==== dba_dependencies.sql==== | ||
- | |||
- | [[http:// | ||
- | |||
- | |||
- | //The attached SQL file will show all dependencies for an object, | ||
- | both forwards and backwards through the heirarchy of objects | ||
- | in DBA_DEPENDENCIES, | ||
- | |||
- | //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. | ||
- | to queries on complex views and ' | ||
- | |||
- | '' | ||
- | as | ||
- | select * from sys.dba_dependencies ;'' | ||
- | |||
- | |||
- | |||
- | |||
- | <code sql> | ||
- | -- | ||
- | 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 | ||
- | -- | ||
- | -- added ' | ||
- | -- | ||
- | -- | ||
- | -- call on the command line: | ||
- | -- | ||
- | -- 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('& | ||
- | set term on feed on | ||
- | |||
- | prompt Dependencies for Object?: | ||
- | |||
- | col cobject noprint new_value uobject | ||
- | set term off feed off | ||
- | select upper('& | ||
- | 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_name || '"' | ||
- | type || ' "' | ||
- | level hlevel, | ||
- | referenced_owner, | ||
- | owner, name, type | ||
- | from dba_dependencies | ||
- | start with | ||
- | referenced_owner = '&& | ||
- | and referenced_name = '&& | ||
- | 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_name || '"' | ||
- | type || ' "' | ||
- | level hlevel, | ||
- | referenced_owner, | ||
- | owner, name, type | ||
- | from dba_dependencies | ||
- | start with | ||
- | owner = '&& | ||
- | and name = '&& | ||
- | connect by | ||
- | owner = prior referenced_owner | ||
- | and name = prior referenced_name | ||
- | and type = prior referenced_type | ||
- | order by 1, 2 | ||
- | ) | ||
- | select lpad(' ', | ||
- | display_child, | ||
- | 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 ==== | ||
- | |||
- | <code sql> | ||
- | 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 ' | ||
- | ; | ||
- | </ | ||
- | |||
- | |||
- | ==== List VIEW dependencies ==== | ||
- | |||
- | <code sql> | ||
- | COL SCHEMA_NAME FORMAT A40 | ||
- | COL REFERENCED_SCHEMA_NAME FORMAT A40 | ||
- | COL REFERENCED_NAME FORMAT A40 | ||
- | col REFERENCED_TYPE | ||
- | |||
- | select owner as schema_name, | ||
- | name as view_name, | ||
- | | ||
- | | ||
- | | ||
- | from sys.dba_dependencies | ||
- | where type = ' | ||
- | -- AND OWNER=' | ||
- | -- and name LIKE ' | ||
- | order by owner, name, referenced_name, | ||
- | / | ||
- | </ | ||
- | |||
- | |||
- | |||
- | ==== More info ==== | ||
- | * [[http:// | ||
- | * [[http:// | ||
- | |||
- | |||
dba/oracle/oracle_sql_querys/objects.1644577957.txt.gz · Last modified: 2022/02/11 11:12 by dodger