Ciberterminal Wiki

User Tools

Site Tools

Trace:
dba:oracle:basic_oracle_sql_querys

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:basic_oracle_sql_querys [2023/02/10 15:26] – [[SECTION] Usuarios] dodgerdba:oracle:basic_oracle_sql_querys [2023/07/03 08:37] (current) – dodger
Line 1: Line 1:
 ====== [DOC] Oracle basic queries ====== ====== [DOC] Oracle basic queries ======
 +
 +====== [SECTION] PRE-basics ======
 +===== NOT owned by oracle =====
 +This is really useful to remove "noise" on queries:
 +<code sql>
 +and NOT owner in (
 +       'SYSTEM','CTXSYS','DBSNMP','EXFSYS','LBACSYS','MDSYS','MGMT_VIEW',
 +       'OLAPSYS','ORDDATA','OWBSYS','ORDPLUGINS','ORDSYS','OUTLN',
 +       'SI_INFORMTN_SCHEMA','SYS','SYSMAN','WK_TEST','WKSYS','WKPROXY',
 +       'WMSYS','XDB','APEX_PUBLIC_USER','DIP','FLOWS_020100','FLOWS_030000',
 +       'FLOWS_040100','FLOWS_010600','FLOWS_FILES','MDDATA','ORACLE_OCM',
 +       'SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL','PERFSTAT',
 +       'SQLTXPLAIN','DMSYS','TSMSYS','WKSYS','APEX_040000','APEX_040200',
 +       'DVSYS','OJVMSYS','GSMADMIN_INTERNAL','APPQOSSYS','DVSYS','DVF',
 +       'AUDSYS','APEX_030200','MGMT_VIEW','ODM','ODM_MTR','TRACESRV','MTMSYS',
 +       'OWBSYS_AUDIT','WEBSYS','WK_PROXY','OSE$HTTP$ADMIN',
 +       'AURORA$JIS$UTILITY$','AURORA$ORB$UNAUTHENTICATED',
 +       'DBMS_PRIVILEGE_CAPTURE','CSMIG','MGDSYS','SDE','DBSFWUSER'
 +    )
 +</code>
 +
  
  
Line 1089: Line 1110:
 WHERE GRANTEE='USERNAME'; WHERE GRANTEE='USERNAME';
 </code> </code>
-====Sobre Objetos====+====Sobre Objects====
  
 Mega query para obtener lo que sea: Mega query para obtener lo que sea:
Line 1161: Line 1182:
 </code> </code>
  
-Aparte, los grants sobre objetos se detallan en las vistas:+Aparte, los grants sobre objects se detallan en las vistas:
   * table_privileges   * table_privileges
   * dba_role_privs   * dba_role_privs
Line 1378: Line 1399:
 ALTER USER USERNAME PROFILE LIMIT ; ALTER USER USERNAME PROFILE LIMIT ;
 </code> </code>
-====== [SECTION]  Objetos======+====== [SECTION]  Objects====== 
 + 
 +===== UDT: User defined types ===== 
 + 
 +==== List UDTs ==== 
 +<code sql> 
 +col type_name format a40 
 +col type_oid format a40 
 +col SUPERTYPE_NAME format a40 
 +col SUPERTYPE_OWNER format a40 
 +select owner,  
 +  type_name,  
 +  type_oid,  
 +  SUPERTYPE_OWNER,  
 +  SUPERTYPE_NAME  
 +from dba_types 
 +/ 
 +</code> 
 + 
 +==== List UDTs usage in tables ==== 
 +<code sql> 
 +col type_name format a40 
 +col type_oid format a40 
 + 
 +select owner, 
 +  table_name, 
 +  column_name, 
 +  data_type, 
 +  virtual_column 
 +from dba_tab_cols 
 +where data_type in ( 
 +  select 
 +    type_name 
 +  from dba_types 
 +  where NOT owner in ( 
 +         'SYSTEM','CTXSYS','DBSNMP','EXFSYS','LBACSYS','MDSYS','MGMT_VIEW', 
 +         'OLAPSYS','ORDDATA','OWBSYS','ORDPLUGINS','ORDSYS','OUTLN', 
 +         'SI_INFORMTN_SCHEMA','SYS','SYSMAN','WK_TEST','WKSYS','WKPROXY', 
 +         'WMSYS','XDB','APEX_PUBLIC_USER','DIP','FLOWS_020100','FLOWS_030000', 
 +         'FLOWS_040100','FLOWS_010600','FLOWS_FILES','MDDATA','ORACLE_OCM', 
 +         'SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL','PERFSTAT', 
 +         'SQLTXPLAIN','DMSYS','TSMSYS','WKSYS','APEX_040000','APEX_040200', 
 +         'DVSYS','OJVMSYS','GSMADMIN_INTERNAL','APPQOSSYS','DVSYS','DVF', 
 +         'AUDSYS','APEX_030200','MGMT_VIEW','ODM','ODM_MTR','TRACESRV','MTMSYS', 
 +         'OWBSYS_AUDIT','WEBSYS','WK_PROXY','OSE$HTTP$ADMIN', 
 +         'AURORA$JIS$UTILITY$','AURORA$ORB$UNAUTHENTICATED', 
 +         'DBMS_PRIVILEGE_CAPTURE','CSMIG','MGDSYS','SDE','DBSFWUSER' 
 +      ) 
 +  group by type_name 
 +  ) 
 +/ 
 +</code>
  
 ===== Compilar ===== ===== Compilar =====
  
-==== Compilar un objeto ====+Compile an object:
 <code sql>ALTER <OBJECT_TYPE> [schema.]package_name COMPILE ; <code sql>ALTER <OBJECT_TYPE> [schema.]package_name COMPILE ;
 </code> </code>
Line 1391: Line 1463:
 </code> </code>
  
-==== Compilar todos los objetos invalidos ==== +Compile ALL objects (invalid):
 See:  See: 
 [[dba:oracle:scripts:recompile_it_all|[SCRIPT] Recompile all database objects]] [[dba:oracle:scripts:recompile_it_all|[SCRIPT] Recompile all database objects]]
Line 1425: Line 1496:
 </code> </code>
  
-Lista de nombre de objetos soportados por [[http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_metada.htm#BGBIEDIA|DBMS_METADATA]].+Lista de nombre de objects soportados por [[http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_metada.htm#BGBIEDIA|DBMS_METADATA]].
  
 ==== Obtener metadatos de dependencias ==== ==== Obtener metadatos de dependencias ====
Line 1473: Line 1544:
  
  
-=====Tipos de objetos===== +=====Tipos de objects===== 
-No existe ninguna tabla que determine el nombre de los objetos, se pueden obtener (más o menos así):+No existe ninguna tabla que determine el nombre de los objects, se pueden obtener (más o menos así):
 <code sql> <code sql>
 SELECT OBJECT_TYPE FROM SYS.ALL_OBJECTS GROUP BY OBJECT_TYPE ; SELECT OBJECT_TYPE FROM SYS.ALL_OBJECTS GROUP BY OBJECT_TYPE ;
 </code> </code>
  
-=====Objetos por tipo=====+=====Objects por tipo=====
 Cualquier owner: Cualquier owner:
 <code sql> <code sql>
Line 1505: Line 1576:
 Se pueden listar: Se pueden listar:
 | NOMBRE DE LA TABLA | Descripción | | NOMBRE DE LA TABLA | Descripción |
-| dba_tables | objetos accesibles siendo un dba (todo) |+| dba_tables | objects accesibles siendo un dba (todo) |
 | user_tables | tablas accesibles por el usuario que estamos usando | | user_tables | tablas accesibles por el usuario que estamos usando |
 | all_tables | todo | | all_tables | todo |
Line 1555: Line 1626:
 WHERE OWNER = '<USERNAME>' WHERE OWNER = '<USERNAME>'
 ORDER BY OWNER,TABLE_NAME ; ORDER BY OWNER,TABLE_NAME ;
 +</code>
 +
 +
 +===== Virtual columns =====
 +<code sql>
 +COL OWNER FORMAT A30;
 +COL TABLE_NAME FORMAT A30;
 +COL COLUMN_NAME FORMAT A30;
 +COL virtual_column FORMAT A5;
 +
 +select owner,
 +  table_name,
 +  column_name,
 +  virtual_column
 +from dba_tab_cols
 +where VIRTUAL_COLUMN='YES'
 +and OWNER in ( 'USERNAME1', 'USER2' )
 +order by owner, table_name, column_name
 +/
 +</code>
 +
 +Not owned by system users:
 +<code sql>
 +COL OWNER FORMAT A30;
 +COL TABLE_NAME FORMAT A30;
 +COL COLUMN_NAME FORMAT A30;
 +COL virtual_column FORMAT A5;
 +
 +select owner,
 +  table_name,
 +  column_name,
 +  virtual_column
 +from dba_tab_cols
 +where VIRTUAL_COLUMN='YES'
 +and NOT owner in (
 +       'SYSTEM','CTXSYS','DBSNMP','EXFSYS','LBACSYS','MDSYS','MGMT_VIEW',
 +       'OLAPSYS','ORDDATA','OWBSYS','ORDPLUGINS','ORDSYS','OUTLN',
 +       'SI_INFORMTN_SCHEMA','SYS','SYSMAN','WK_TEST','WKSYS','WKPROXY',
 +       'WMSYS','XDB','APEX_PUBLIC_USER','DIP','FLOWS_020100','FLOWS_030000',
 +       'FLOWS_040100','FLOWS_010600','FLOWS_FILES','MDDATA','ORACLE_OCM',
 +       'SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL','PERFSTAT',
 +       'SQLTXPLAIN','DMSYS','TSMSYS','WKSYS','APEX_040000','APEX_040200',
 +       'DVSYS','OJVMSYS','GSMADMIN_INTERNAL','APPQOSSYS','DVSYS','DVF',
 +       'AUDSYS','APEX_030200','MGMT_VIEW','ODM','ODM_MTR','TRACESRV','MTMSYS',
 +       'OWBSYS_AUDIT','WEBSYS','WK_PROXY','OSE$HTTP$ADMIN',
 +       'AURORA$JIS$UTILITY$','AURORA$ORB$UNAUTHENTICATED',
 +       'DBMS_PRIVILEGE_CAPTURE','CSMIG','MGDSYS','SDE','DBSFWUSER'
 +    )
 +order by owner, table_name, column_name
 +/
 </code> </code>
  
Line 1623: Line 1744:
 / /
 </code> </code>
-=====Dependencias entre objetos=====+=====Dependencias entre objects=====
  
  
Line 1662: Line 1783:
 </code> </code>
  
-Después ejecutar para el objetos que queramos, el procedure:+Después ejecutar para el objects que queramos, el procedure:
 <code sql> <code sql>
 EXEC deptree_fill('object_type', 'object_owner', 'object_name'); EXEC deptree_fill('object_type', 'object_owner', 'object_name');
Line 2247: Line 2368:
 </code> </code>
 ==== lock de DDLS ==== ==== lock de DDLS ====
-Mirar objetos que se están accediendo:+Mirar objects que se están accediendo:
 <code sql> <code sql>
 col object format a50 ; col object format a50 ;
Line 2498: Line 2619:
 ===== Hard parse ===== ===== Hard parse =====
   * How to Identify Hard Parse Failures (Doc ID 1353015.1)   * How to Identify Hard Parse Failures (Doc ID 1353015.1)
-  * http://wiki.ciberterminal.net/doku.php?id=killing_hardparse+  * [[dba:oracle:docs:killing_hardparse|[DOC] Kill hardparse]]
 ===== Top 100 querys ===== ===== Top 100 querys =====
 Esta query se basa en las estadísticas guardadas en el repositorio awr. Esta query se basa en las estadísticas guardadas en el repositorio awr.
Line 2646: Line 2767:
  
  
-===== Paralelismo de objetos =====+===== Paralelismo de objects =====
  
  
dba/oracle/basic_oracle_sql_querys.1676042793.txt.gz · Last modified: 2023/02/10 15:26 by dodger