User Tools

Site Tools


dba:oracle:oracle_sql_querys:objects

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision
Previous revision
dba:oracle:oracle_sql_querys:objects [2022/02/11 11:12] – ↷ Links adapted because of a move operation dodgerdba: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>ALTER <OBJECT_TYPE> [schema.]package_name COMPILE ; 
-</code> 
-  * Paquete: 
-<code sql>ALTER PACKAGE [schema.]package_name COMPILE [DEBUG PACKAGE|SPECIFICATION|BODY];</code> 
-  * Trigger: 
-<code sql>ALTER TRIGGER [schema.]package_name COMPILE ; 
-</code> 
- 
-==== Compilar todos los objetos invalidos ==== 
- 
-See:  
-[[dba:oracle:recompile_it_all|[SCRIPT] Recompile all database objects]] 
- 
-===== 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,'STORAGE',false); 
-</code> 
-Excluir  
-<code sql> 
-execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',TRUE); 
-</code> 
-Que escriba el terminador de objeto (muy recomendado): 
-<code sql> 
-execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true); 
-</code> 
-No Incluir fk's: 
-<code sql> 
-execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'REF_CONSTRAINTS',false); 
-</code> 
-No incluir constraints: 
-<code sql> 
-execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',false); 
-</code> 
- 
-==== Obtener metadatos ==== 
-<code sql> 
-SELECT DBMS_METADATA.GET_DDL('OBJECT_TYPE','OBJECT_NAME','SCHEMANAME') FROM DUAL; 
-</code> 
- 
-Lista de nombre de objetos soportados por [[http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_metada.htm#BGBIEDIA|DBMS_METADATA]]. 
- 
-==== Obtener metadatos de dependencias ==== 
-<code sql> 
-SELECT DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_TYPE','OBJECT_NAME','SCHEMANAME') FROM DUAL; 
-</code> 
- 
- 
-==== 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 '%OBJECT_NAME%'; 
-</code> 
- 
-Buscar SOURCE: 
-<code sql> 
-SELECT * 
-FROM source$ 
-WHERE OBJ# IN 
-  (  
-  SELECT obj# FROM obj$ WHERE name LIKE '%OBJECT_NAME%' 
-  ) ; 
-</code> 
- 
- 
- 
-=====Performance views de oracle===== 
-<code sql>select table_name from dict where table_name like 'V$%' ; 
-</code> 
- 
-<code sql> 
-select name from V$FIXED_TABLE ; 
-</code> 
- 
- 
-=====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 ; 
-</code> 
- 
-=====Objetos por tipo===== 
-Cualquier owner: 
-<code sql> 
-SELECT OBJECT_NAME, OWNER, OBJECT_TYPE 
-FROM SYS.ALL_OBJECTS 
-WHERE OBJECT_TYPE LIKE 'OBJECT_TYPE' 
-; 
-</code> 
-Con un owner determinado: 
-<code sql> 
-SELECT OBJECT_NAME, OWNER, OBJECT_TYPE 
-FROM SYS.ALL_OBJECTS 
-WHERE OBJECT_TYPE LIKE 'OBJECT_TYPE' 
-AND OWNER LIKE 'OWNER' 
-; 
-</code> 
- 
-Varios tipos 
-<code sql> 
-SELECT   * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('FUNCTION','PROCEDURE','PACKAGE') ; 
-</code> 
- 
-=====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>select TABLE_NAME FROM all_tables ;</code> 
-====Desde sys==== 
-<code sql>select OBJECT_NAME from DBA_OBJECTS where OBJECT_TYPE='TABLE' and OWNER like '%username%';</code> 
- 
-====Query al dict==== 
-<code sql> 
-SELECT TABLE_NAME FROM DICT WHERE TABLE_NAME LIKE '%EXAMPLE%' ; 
-</code> 
- 
-=====Ver índices de una tabla===== 
-<code sql> 
-select index_name from dba_indexes where table_name='tablename';  
-</code> 
-Y la descripción del índice: 
-<code sql> 
-select DBMS_METADATA.GET_DDL('INDEX','INDEXNAME','SCHEMANAME') from DUAL; 
-</code> 
- 
-O una descripción rápida: 
-<code sql> 
-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>' ; 
-</code> 
- 
-===== 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 = '<USERNAME>' 
-ORDER BY OWNER,TABLE_NAME ; 
-</code> 
- 
-===== Tablas particionadas ===== 
-==== Listar tablas particionadas de los usuarios ==== 
-<code sql> 
-SELECT TABLE_OWNER, TABLE_NAME  
-FROM  ALL_TAB_PARTITIONS  
-WHERE TABLE_OWNER NOT IN ('SYS', 'SYSTEM' 
-GROUP BY TABLE_OWNER, TABLE_NAME ; 
-</code> 
- 
-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 ('SYS', 'SYSTEM') 
-order by PARTITION_NAME 
-/ 
-</code> 
-==== Numero de rows de cada partición ==== 
-<code sql> 
-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 ; 
-</code> 
- 
- 
- 
-=====Jerarquía de tablas===== 
-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): 
-<code sql> 
-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 
-/ 
-</code> 
-=====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  
-   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; 
-</code> 
- 
-==== Using utldtree.sql ==== 
-Ejecutar: 
-<code> 
-@${ORACLE_HOME}/rdbms/admin/utldtree.sql 
-</code> 
- 
-Después ejecutar para el objetos que queramos, el procedure: 
-<code sql> 
-EXEC deptree_fill('object_type', 'object_owner', 'object_name'); 
-</code> 
-y ver el resultado: 
-<code sql> 
-select * from ideptree; 
-</code> 
- 
-==== dba_dependencies.sql==== 
- 
-[[http://www.freelists.org/post/oracle-l/Shared-Pool-causing-packages-to-automatically-go-invalid,4|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 ;'' 
- 
- 
- 
- 
-<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 
---   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 
-/ 
-</code> 
- 
-==== 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 '%TABLE_NAME%' 
-; 
-</code> 
- 
- 
-==== List VIEW dependencies ==== 
- 
-<code sql> 
-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 
-/ 
-</code> 
- 
- 
- 
-==== More info ==== 
-  * [[http://docs.oracle.com/cd/B28359_01/server.111/b28318/dependencies.htm]] 
-  * [[http://www.dba-oracle.com/d_dba_dependencies.htm]] 
- 
- 
  
dba/oracle/oracle_sql_querys/objects.1644577957.txt.gz · Last modified: 2022/02/11 11:12 by dodger