dba:oracle:basic_oracle_sql_querys
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
dba:oracle:basic_oracle_sql_querys [2023/01/31 08:17] – created dodger | dba: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 " | ||
+ | <code sql> | ||
+ | and NOT owner in ( | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ) | ||
+ | </ | ||
+ | |||
- | ======SECCIÓN : STATUS====== | + | ====== |
===== Hora de la bbdd ===== | ===== Hora de la bbdd ===== | ||
<code sql> | <code sql> | ||
Line 141: | Line 162: | ||
</ | </ | ||
El event 10046 está sacado de Doc ID 1198753.1 (para rman), se puede usar cualquier otro | El event 10046 está sacado de Doc ID 1198753.1 (para rman), se puede usar cualquier otro | ||
- | ======SECCIÓN : Configuración====== | + | ====== |
=====Nombre de la bd===== | =====Nombre de la bd===== | ||
<code sql>show parameter db_name ;</ | <code sql>show parameter db_name ;</ | ||
Line 246: | Line 267: | ||
- | ======SECCIÓN : Storage====== | + | ====== |
=====Listar tablespaces===== | =====Listar tablespaces===== | ||
Line 1039: | Line 1060: | ||
- | ======SECCIÓN : Usuarios====== | + | ====== |
=====Listar===== | =====Listar===== | ||
* Todos: | * Todos: | ||
Line 1089: | Line 1110: | ||
WHERE GRANTEE=' | WHERE GRANTEE=' | ||
</ | </ | ||
- | ====Sobre | + | ====Sobre |
Mega query para obtener lo que sea: | Mega query para obtener lo que sea: | ||
Line 1161: | Line 1182: | ||
</ | </ | ||
- | Aparte, los grants sobre objetos | + | Aparte, los grants sobre objects |
* table_privileges | * table_privileges | ||
* dba_role_privs | * dba_role_privs | ||
Line 1378: | Line 1399: | ||
ALTER USER USERNAME PROFILE LIMIT ; | ALTER USER USERNAME PROFILE LIMIT ; | ||
</ | </ | ||
- | ======SECCIÓN | + | ====== |
+ | |||
+ | ===== 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 | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | ==== 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 ( | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ) | ||
+ | group by type_name | ||
+ | ) | ||
+ | / | ||
+ | </ | ||
===== Compilar ===== | ===== Compilar ===== | ||
- | ==== Compilar un objeto ==== | + | Compile an object: |
<code sql> | <code sql> | ||
</ | </ | ||
Line 1391: | Line 1463: | ||
</ | </ | ||
- | ==== Compilar todos los objetos invalidos ==== | + | Compile ALL objects (invalid): |
See: | See: | ||
- | [[dba: | + | [[dba: |
===== DBMS_METADATA ===== | ===== DBMS_METADATA ===== | ||
Line 1425: | Line 1496: | ||
</ | </ | ||
- | Lista de nombre de objetos | + | Lista de nombre de objects |
==== 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 ; | ||
</ | </ | ||
- | =====Objetos | + | =====Objects |
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 | + | | dba_tables | objects |
| 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 = '< | WHERE OWNER = '< | ||
ORDER BY OWNER, | ORDER BY OWNER, | ||
+ | </ | ||
+ | |||
+ | |||
+ | ===== 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=' | ||
+ | and OWNER in ( ' | ||
+ | order by owner, table_name, column_name | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | 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=' | ||
+ | and NOT owner in ( | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ) | ||
+ | order by owner, table_name, column_name | ||
+ | / | ||
</ | </ | ||
Line 1623: | Line 1744: | ||
/ | / | ||
</ | </ | ||
- | =====Dependencias entre objetos===== | + | =====Dependencias entre objects===== |
Line 1662: | Line 1783: | ||
</ | </ | ||
- | Después ejecutar para el objetos | + | Después ejecutar para el objects |
<code sql> | <code sql> | ||
EXEC deptree_fill(' | EXEC deptree_fill(' | ||
Line 1837: | Line 1958: | ||
- | ======SECCIÓN : Sesiones====== | + | ====== |
=====Listar numero de sesiones===== | =====Listar numero de sesiones===== | ||
<code sql> | <code sql> | ||
Line 2247: | Line 2368: | ||
</ | </ | ||
==== lock de DDLS ==== | ==== lock de DDLS ==== | ||
- | Mirar objetos | + | Mirar objects |
<code sql> | <code sql> | ||
col object format a50 ; | col object format a50 ; | ||
Line 2329: | Line 2450: | ||
/ | / | ||
</ | </ | ||
- | ======SECCIÓN : QUERYS====== | + | ====== |
====longest querys==== | ====longest querys==== | ||
Line 2408: | Line 2529: | ||
; | ; | ||
</ | </ | ||
- | ======SECCIÓN : Execution PLANS====== | + | ====== |
===== Hidden options/ | ===== Hidden options/ | ||
Line 2495: | Line 2616: | ||
* http:// | * http:// | ||
* http:// | * http:// | ||
- | ======SECCIÓN : Performance====== | + | ====== |
===== 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:// | + | * [[dba: |
===== 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 2643: | Line 2764: | ||
===== AWR ===== | ===== AWR ===== | ||
- | * [[awr|Automated Workload Repository (awr)]] | + | * [[.docs:awr|Automated Workload Repository (awr)]] |
- | ===== Paralelismo de objetos | + | ===== Paralelismo de objects |
Line 2680: | Line 2801: | ||
</ | </ | ||
- | ======SECCIÓN : Otros====== | + | ====== |
===== Añadir un redo group ===== | ===== Añadir un redo group ===== | ||
Line 3056: | Line 3177: | ||
- | ======SECCIÓN : Documentos adicionales====== | + | ====== |
=====Auditoria===== | =====Auditoria===== | ||
- | * [[audit_mini-howto|Auditing Mini-HOWTO]] | + | * [[.howtos:audit_mini-howto|Auditing Mini-HOWTO]] |
=====DataGuard===== | =====DataGuard===== | ||
- | * [[dataguard_mini-howto# | + | * [[.docs:dataguard_mini-howto# |
=====RMAN===== | =====RMAN===== | ||
- | * [[RMAN_basic_querys|RMAN basic querys]] | + | * [[.docs: |
=====RAC Status===== | =====RAC Status===== | ||
- | * [[CLI_tools|CommandLine tools para el control de Oracle]] | + | * [[.docs: |
dba/oracle/basic_oracle_sql_querys.1675153068.txt.gz · Last modified: 2023/01/31 08:17 by dodger