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 revisionNext revisionBoth sides next revision | ||
dba:oracle:basic_oracle_sql_querys [2022/02/11 11:12] – ↷ Page moved from oracle:basic_oracle_sql_querys to dba:oracle:basic_oracle_sql_querys dodger | dba:oracle:basic_oracle_sql_querys [2023/07/03 08:23] – [[DOC] Oracle basic queries] dodger | ||
---|---|---|---|
Line 1: | Line 1: | ||
- | <WRAP center round important 60%> | + | ====== |
- | This document is being splited into pieces!!!!! \\ | + | |
- | see [[oracle: | + | |
- | </ | + | |
+ | ====== [SECTION] PRE-basics ====== | ||
+ | ===== NOT owned by oracle ===== | ||
+ | <code sql> | ||
+ | and NOT owner in ( | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ) | ||
+ | </ | ||
- | ====== [DOC] Oracle basic queries ====== | ||
- | ======SECCIÓN : STATUS====== | + | ====== |
===== Hora de la bbdd ===== | ===== Hora de la bbdd ===== | ||
<code sql> | <code sql> | ||
Line 147: | Line 161: | ||
</ | </ | ||
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 203: | Line 217: | ||
==== Localización de los REDO ==== | ==== Localización de los REDO ==== | ||
+ | |||
<code sql> | <code sql> | ||
+ | col member format a100 | ||
select * from v$logfile ; | select * from v$logfile ; | ||
</ | </ | ||
Line 221: | Line 237: | ||
=====Parametros===== | =====Parametros===== | ||
Por regla general: | Por regla general: | ||
- | * <code sql>show parameter < | + | * <code sql>show parameter < |
* <code sql> | * <code sql> | ||
* <code sql> | * <code sql> | ||
Line 231: | Line 247: | ||
</ | </ | ||
- | ======SECCIÓN : Storage====== | + | |
+ | ===== Standby redo logs ===== | ||
+ | This query will show '' | ||
+ | |||
+ | <code sql> | ||
+ | COL member format 150 | ||
+ | select lf.group#, | ||
+ | from v$logfile lf | ||
+ | join ( | ||
+ | | ||
+ | | ||
+ | | ||
+ | on lf.group# | ||
+ | order by lf.group# | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | ====== [SECTION] | ||
=====Listar tablespaces===== | =====Listar tablespaces===== | ||
Line 241: | Line 276: | ||
select * from v$dbfile order by 1; | select * from v$dbfile order by 1; | ||
</ | </ | ||
- | =====Ver datafiles (simple)===== | + | |
+ | ===== List datafiles (simple)===== | ||
<code sql> | <code sql> | ||
col FILE_NAME FORMAT A80; | col FILE_NAME FORMAT A80; | ||
Line 259: | Line 295: | ||
</ | </ | ||
- | =====Crear (datafile)===== | + | ===== Tablespace creation |
- | ====Sin ASM==== | + | ==== Without |
<code sql> | <code sql> | ||
TABLESPACE " | TABLESPACE " | ||
Line 268: | Line 304: | ||
; | ; | ||
</ | </ | ||
- | ====Con ASM==== | + | ==== With ASM==== |
<code sql> | <code sql> | ||
CREATE SMALLFILE | CREATE SMALLFILE | ||
Line 278: | Line 314: | ||
</ | </ | ||
- | =====Tablespace DDL===== | + | ===== View Tablespace DDL===== |
<code sql> | <code sql> | ||
select dbms_metadata.get_ddl(' | select dbms_metadata.get_ddl(' | ||
</ | </ | ||
- | =====Añadir espacio===== | + | ===== Add space to Tablespace |
- | " | + | |
<code sql> | <code sql> | ||
add datafile / | add datafile / | ||
Line 290: | Line 325: | ||
</ | </ | ||
- | =====Espacio===== | + | |
- | ====En ASM==== | + | ===== Space Information |
+ | |||
+ | === ASM Diskgroup information==== | ||
<code sql> | <code sql> | ||
o | o | ||
Line 302: | Line 339: | ||
- | ====Tablespace==== | + | ====Tablespace |
Mi query, tiene en cuenta si el tablespace tiene autoextend o es de tamaño fijo para computar el espacio REAL: | Mi query, tiene en cuenta si el tablespace tiene autoextend o es de tamaño fijo para computar el espacio REAL: | ||
<code sql> | <code sql> | ||
Line 375: | Line 412: | ||
- | ==== Espacio ocupado por tablas, indexes... ==== | + | ==== Space used by tables, indexes... ==== |
- | Formateo de columnas: | + | Column format |
<code SQL> | <code SQL> | ||
COL OWNER FORMAT A10; | COL OWNER FORMAT A10; | ||
Line 385: | Line 422: | ||
COL SUM_BYTES FORMAT 999999999999999999.9999 ; | COL SUM_BYTES FORMAT 999999999999999999.9999 ; | ||
</ | </ | ||
- | * Tablas: | + | * Tables: |
<code SQL> | <code SQL> | ||
SELECT | SELECT | ||
Line 410: | Line 447: | ||
</ | </ | ||
- | Ordenado por tamaño: | + | Sort by size: |
- | + | * Tables: | |
- | * Tablas: | + | |
<code sql> | <code sql> | ||
SELECT SEGMENT_NAME, | SELECT SEGMENT_NAME, | ||
Line 426: | Line 462: | ||
; | ; | ||
</ | </ | ||
- | * Indices: | + | * Indixes: |
<code sql> | <code sql> | ||
SELECT SEGMENT_NAME, | SELECT SEGMENT_NAME, | ||
Line 441: | Line 477: | ||
</ | </ | ||
- | ==== Espacio | + | ==== REAL space taken by a table ==== |
- | Basado en: | + | Based on: |
How To Find The Size Of A Number Of Rows Of A Table (Doc ID 1370050.1): | How To Find The Size Of A Number Of Rows Of A Table (Doc ID 1370050.1): | ||
Line 470: | Line 506: | ||
- | ==== Espacio/ | + | ==== Space used by a table with LOB columns |
<code sql> | <code sql> | ||
SELECT segment_name, | SELECT segment_name, | ||
Line 489: | Line 525: | ||
</ | </ | ||
- | ===== TEMP tablespace Management ===== | + | ==== Space DELTA of a table ==== |
- | ==== Espacio en el temporary tablespace ==== | + | Based on AWR tables: |
- | < | + | < |
- | COL TABLESPACE_SIZE FOR 999, | + | |
- | COL ALLOCATED_SPACE FOR 999, | + | SELECT |
- | COL FREE_SPACE FOR 999, | + | |
- | + | FROM | |
- | SELECT * | + | |
- | FROM dba_temp_free_space | + | |
- | / | + | ( |
- | </ | + | SELECT |
- | Resumido en MB: | + | |
- | <code SQL> | + | TS#, |
- | SELECT | + | OBJ#, |
- | A.tablespace_name tablespace, | + | |
- | D.mb_total, | + | FROM |
- | SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used, | + | |
- | D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free | + | where SPACE_USED_DELTA >0 |
- | FROM | + | ) s, |
- | v$sort_segment A, | + | v$tablespace |
- | ( | + | WHERE |
- | SELECT | + | |
- | | + | AND |
- | C.block_size, | + | |
- | SUM (C.bytes) / 1024 / 1024 mb_total | + | |
- | FROM | + | and s.SNAP_ID |
- | v$tablespace | + | ORDER BY |
- | | + | 1 desc |
- | WHERE | + | ; |
- | B.ts#= C.ts# | + | |
- | GROUP BY | + | |
- | | + | |
- | C.block_size | + | |
- | ) D | + | |
- | WHERE | + | |
- | A.tablespace_name | + | |
- | GROUP by | + | |
- | A.tablespace_name, | + | |
- | D.mb_total | + | |
- | / | + | |
</ | </ | ||
+ | |||
+ | ===== TEMP tablespace Management ===== | ||
==== TEMP tablespace datafiles ==== | ==== TEMP tablespace datafiles ==== | ||
Line 543: | Line 571: | ||
dba_temp_files; | dba_temp_files; | ||
</ | </ | ||
- | ==== Crear un nuevo TEMP ==== | + | ==== New TEMP ==== |
<code sql> | <code sql> | ||
CREATE TEMPORARY TABLESPACE TEMP tempfile '/ | CREATE TEMPORARY TABLESPACE TEMP tempfile '/ | ||
</ | </ | ||
- | ==== Añadir espacio al temp ==== | + | ==== Add space to TEMP ==== |
<code sql> | <code sql> | ||
ALTER TABLESPACE TEMP ADD TEMPFILE | ALTER TABLESPACE TEMP ADD TEMPFILE | ||
</ | </ | ||
- | ==== Cambiar de TEMP tablespace ==== | + | ==== Switch |
<code sql> | <code sql> | ||
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP ; | ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP ; | ||
Line 595: | Line 623: | ||
AND c.hash_value = a.sql_hash_value | AND c.hash_value = a.sql_hash_value | ||
ORDER BY b.tablespace, | ORDER BY b.tablespace, | ||
+ | </ | ||
+ | |||
+ | ==== Space used by TEMP==== | ||
+ | <code SQL> | ||
+ | COL TABLESPACE_SIZE FOR 999, | ||
+ | COL ALLOCATED_SPACE FOR 999, | ||
+ | COL FREE_SPACE FOR 999, | ||
+ | |||
+ | SELECT * | ||
+ | FROM | ||
+ | / | ||
+ | </ | ||
+ | Resumido en MB: | ||
+ | <code SQL> | ||
+ | SELECT | ||
+ | | ||
+ | | ||
+ | SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used, | ||
+ | | ||
+ | FROM | ||
+ | | ||
+ | ( | ||
+ | SELECT | ||
+ | | ||
+ | | ||
+ | SUM (C.bytes) / 1024 / 1024 mb_total | ||
+ | FROM | ||
+ | | ||
+ | | ||
+ | WHERE | ||
+ | | ||
+ | GROUP BY | ||
+ | | ||
+ | | ||
+ | ) D | ||
+ | WHERE | ||
+ | | ||
+ | GROUP by | ||
+ | | ||
+ | | ||
+ | / | ||
</ | </ | ||
Line 600: | Line 669: | ||
=====Managing ASM===== | =====Managing ASM===== | ||
- | ====Conexión==== | + | ==== Connection |
- | Exportar las variables habituales de oracle: | + | Export vars: |
<code sql> | <code sql> | ||
export ORACLE_BASE=/ | export ORACLE_BASE=/ | ||
Line 612: | Line 681: | ||
O usar el " | O usar el " | ||
- | También podemos conectar al cliente del asm: | + | Asm cli/wrapper: |
<code sql> | <code sql> | ||
- | ====Vistas interesantes==== | + | ====Main views==== |
| <code sql> | | <code sql> | ||
| <code sql> | | <code sql> | ||
Line 626: | Line 695: | ||
- | ====Listado de discos==== | + | ==== Disk list ==== |
<code sql> | <code sql> | ||
COL PATH FORMAT A100 | COL PATH FORMAT A100 | ||
Line 643: | Line 712: | ||
; | ; | ||
</ | </ | ||
- | ====Listado de Diskgroups==== | + | ==== Diskgroup list==== |
<code sql> | <code sql> | ||
COLUMN DISKGROUP FORMAT A20; | COLUMN DISKGROUP FORMAT A20; | ||
Line 659: | Line 728: | ||
FROM V$ASM_DISK D, V$ASM_DISKGROUP G | FROM V$ASM_DISK D, V$ASM_DISKGROUP G | ||
WHERE D.GROUP_NUMBER=G.GROUP_NUMBER ; | WHERE D.GROUP_NUMBER=G.GROUP_NUMBER ; | ||
- | CLEAR COLUMNS; | + | |
</ | </ | ||
- | ====Espacio en los Diskgroups==== | + | ==== Diskgroups |
<code sql> | <code sql> | ||
COLUMN DISKGROUP FORMAT A20; | COLUMN DISKGROUP FORMAT A20; | ||
Line 676: | Line 745: | ||
; | ; | ||
</ | </ | ||
- | ====Información de Diskgroups==== | + | ==== Diskgroups |
<code sql> | <code sql> | ||
COL NAME FORMAT A60 | COL NAME FORMAT A60 | ||
Line 690: | Line 759: | ||
</ | </ | ||
- | ====Creación de Diskgroup==== | + | ==== Diskgroup |
Para crear el diskgroup, debemos hacer un : | Para crear el diskgroup, debemos hacer un : | ||
<code sql> | <code sql> | ||
Line 706: | Line 775: | ||
' | ' | ||
</ | </ | ||
- | ====Borrar diskgroup==== | + | ==== Diskgroup deletion==== |
<code sql> | <code sql> | ||
DROP DISKGROUP ' | DROP DISKGROUP ' | ||
Line 724: | Line 793: | ||
Flash recovery area | Flash recovery area | ||
- | ====Tamaño destinado a la FRA==== | + | ==== FRA reserved size ==== |
<code sql> | <code sql> | ||
show parameter db_recovery; | show parameter db_recovery; | ||
</ | </ | ||
- | ==== Espacio Ocupado | + | ==== FRA space usage==== |
<code sql> select * from v$flash_recovery_area_usage; | <code sql> select * from v$flash_recovery_area_usage; | ||
</ | </ | ||
Line 742: | Line 811: | ||
</ | </ | ||
===== UNDO ===== | ===== UNDO ===== | ||
- | ==== Ver información | + | ==== Undo Info ==== |
<code sql> | <code sql> | ||
COL NAME FORMAT A20; | COL NAME FORMAT A20; | ||
Line 751: | Line 820: | ||
</ | </ | ||
- | ==== Espacio usado en el UNDO ==== | + | ==== UNDO Space usage ==== |
Very simple: | Very simple: | ||
<code sql> | <code sql> | ||
Line 782: | Line 851: | ||
- | ==== Sesiones consumiendo | + | ==== UNDO space usage by sessions |
<code sql> | <code sql> | ||
col ROLL_NAME format a30 | col ROLL_NAME format a30 | ||
Line 833: | Line 902: | ||
</ | </ | ||
- | ==== Espacio de UNDO necesario==== | + | ==== UNDO space estimation |
- | En el momento de ejecutar la query: | + | |
<code sql> | <code sql> | ||
Line 865: | Line 933: | ||
- | ==== Añadir espacio al UNDO ==== | + | ==== Add space to UNDO ==== |
<code sql> | <code sql> | ||
ALTER TABLESPACE undotbs_01 | ALTER TABLESPACE undotbs_01 | ||
Line 872: | Line 940: | ||
</ | </ | ||
- | ==== Crear un nuevo espacio de UNDO ==== | + | ==== New UNDO ==== |
<code sql> | <code sql> | ||
CREATE UNDO TABLESPACE undotbs_02 | CREATE UNDO TABLESPACE undotbs_02 | ||
Line 878: | Line 946: | ||
</ | </ | ||
- | ==== Cambiar de UNDO ==== | + | ==== UNDO switch |
<code sql> | <code sql> | ||
ALTER SYSTEM SET undo_tablespace = TESTUNDO SCOPE=BOTH; | ALTER SYSTEM SET undo_tablespace = TESTUNDO SCOPE=BOTH; | ||
Line 911: | Line 979: | ||
</ | </ | ||
- | ==== Umbral de alerta del UNDO ==== | + | ==== UNDO alert threshold |
Cambiar '' | Cambiar '' | ||
<code sql> | <code sql> | ||
Line 964: | Line 1032: | ||
- | =====dNFS===== | + | ===== dNFS ===== |
- | ==== Listar servidores | + | ==== List dNFS servers |
<code sql> | <code sql> | ||
COL RDMAENABLE FORMAT A10 | COL RDMAENABLE FORMAT A10 | ||
Line 974: | Line 1042: | ||
</ | </ | ||
- | ==== Listar canales | + | ==== List dNFS channels |
<code sql> | <code sql> | ||
col path format a40 | col path format a40 | ||
Line 983: | Line 1051: | ||
- | ==== Listar ficheros | + | ==== List dNFS Files ==== |
<code sql> | <code sql> | ||
COL FILENAME FORMAT A200 | COL FILENAME FORMAT A200 | ||
Line 991: | Line 1059: | ||
- | ======SECCIÓN : Usuarios====== | + | ====== |
=====Listar===== | =====Listar===== | ||
* Todos: | * Todos: | ||
- | <code sql> | + | <code sql> |
* Activos: | * Activos: | ||
<code sql> | <code sql> | ||
Line 1003: | Line 1071: | ||
col resource_name format a50 ; | col resource_name format a50 ; | ||
col limit format a30 | col limit format a30 | ||
- | SELECT * FROM DBA_PROFILES order by profile, resource_name ; | + | |
+ | SELECT * | ||
+ | FROM DBA_PROFILES | ||
+ | order by profile, resource_name ; | ||
</ | </ | ||
Line 1038: | Line 1109: | ||
WHERE GRANTEE=' | WHERE GRANTEE=' | ||
</ | </ | ||
- | ====Sobre | + | ====Sobre |
Mega query para obtener lo que sea: | Mega query para obtener lo que sea: | ||
Line 1110: | Line 1181: | ||
</ | </ | ||
- | Aparte, los grants sobre objetos | + | Aparte, los grants sobre objects |
* table_privileges | * table_privileges | ||
* dba_role_privs | * dba_role_privs | ||
Line 1238: | Line 1309: | ||
-- Andy Barry/A | -- Andy Barry/A | ||
-- 20/02/06 | -- 20/02/06 | ||
- | -- modified by jholgado | + | -- modified by dodger |
-- 17/02/2010 | -- 17/02/2010 | ||
Line 1327: | Line 1398: | ||
ALTER USER USERNAME PROFILE LIMIT ; | ALTER USER USERNAME PROFILE LIMIT ; | ||
</ | </ | ||
- | ======SECCIÓN : Objetos====== | + | ====== |
===== Compilar ===== | ===== Compilar ===== | ||
Line 1340: | Line 1411: | ||
</ | </ | ||
- | ==== Compilar todos los objetos | + | ==== Compilar todos los objects |
- | + | ||
- | See: [[oracle: | + | |
+ | See: | ||
+ | [[dba: | ||
===== DBMS_METADATA ===== | ===== DBMS_METADATA ===== | ||
Line 1374: | Line 1445: | ||
</ | </ | ||
- | Lista de nombre de objetos | + | Lista de nombre de objects |
==== Obtener metadatos de dependencias ==== | ==== Obtener metadatos de dependencias ==== | ||
Line 1422: | Line 1493: | ||
- | =====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 1454: | Line 1525: | ||
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 1504: | Line 1575: | ||
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 1572: | Line 1693: | ||
/ | / | ||
</ | </ | ||
- | =====Dependencias entre objetos===== | + | =====Dependencias entre objects===== |
Line 1611: | Line 1732: | ||
</ | </ | ||
- | Después ejecutar para el objetos | + | Después ejecutar para el objects |
<code sql> | <code sql> | ||
EXEC deptree_fill(' | EXEC deptree_fill(' | ||
Line 1759: | Line 1880: | ||
</ | </ | ||
+ | ==== 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 ==== | ==== More info ==== | ||
Line 1767: | Line 1907: | ||
- | ======SECCIÓN : Sesiones====== | + | ====== |
=====Listar numero de sesiones===== | =====Listar numero de sesiones===== | ||
<code sql> | <code sql> | ||
Line 2177: | Line 2317: | ||
</ | </ | ||
==== lock de DDLS ==== | ==== lock de DDLS ==== | ||
- | Mirar objetos | + | Mirar objects |
<code sql> | <code sql> | ||
col object format a50 ; | col object format a50 ; | ||
Line 2259: | Line 2399: | ||
/ | / | ||
</ | </ | ||
- | ======SECCIÓN : QUERYS====== | + | ====== |
====longest querys==== | ====longest querys==== | ||
Line 2292: | Line 2432: | ||
<code sql> | <code sql> | ||
- | col target format a30 | + | |
- | col units format a15 | + | COL UNITS FORMAT A12 |
- | col message | + | COL opname FORMAT A30 |
+ | COL target FORMAT A30 | ||
+ | COL message | ||
+ | COL SID_SERIAL FORMAT A15 | ||
select * from | select * from | ||
( | ( | ||
Line 2308: | Line 2452: | ||
from | from | ||
v$session_longops | v$session_longops | ||
- | | + | |
) | ) | ||
- | order by start_time asc | + | where rownum <=1; |
- | ; | + | |
</ | </ | ||
Line 2335: | Line 2478: | ||
; | ; | ||
</ | </ | ||
- | ======SECCIÓN : Execution PLANS====== | + | ====== |
===== Hidden options/ | ===== Hidden options/ | ||
Line 2363: | Line 2506: | ||
y | y | ||
<code sql> | <code sql> | ||
- | SET LINESIZE | + | SET LINESIZE |
- | SET PAGESIZE | + | SET PAGESIZE |
SELECT * FROM table(DBMS_XPLAN.DISPLAY); | SELECT * FROM table(DBMS_XPLAN.DISPLAY); | ||
</ | </ | ||
Line 2422: | Line 2565: | ||
* 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 2570: | Line 2713: | ||
===== AWR ===== | ===== AWR ===== | ||
- | * [[awr|Automated Workload Repository (awr)]] | + | * [[.docs:awr|Automated Workload Repository (awr)]] |
- | ===== Paralelismo de objetos | + | ===== Paralelismo de objects |
Line 2607: | Line 2750: | ||
</ | </ | ||
- | ======SECCIÓN : Otros====== | + | ====== |
===== Añadir un redo group ===== | ===== Añadir un redo group ===== | ||
Line 2790: | Line 2933: | ||
===== PL/SQL ===== | ===== PL/SQL ===== | ||
+ | |||
+ | ==== Executing queries with bind variables ==== | ||
+ | |||
+ | <code sql> | ||
+ | declare | ||
+ | c1 NUMBER; | ||
+ | c2 NUMBER; | ||
+ | c3 NUMBER; | ||
+ | -- ressult testing_binds%ROWTYPE; | ||
+ | -- inttype NUMBER := 4001; | ||
+ | | ||
+ | BEGIN | ||
+ | theSQL := ' | ||
+ | SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel OPT_ESTIMATE(@" | ||
+ | c1, | ||
+ | c2, | ||
+ | c3 | ||
+ | FROM | ||
+ | ( | ||
+ | SELECT /*+ qb_name(" | ||
+ | COUNT(*) AS c1, | ||
+ | 4294967295 AS c2, | ||
+ | SUM( | ||
+ | CASE | ||
+ | WHEN(" | ||
+ | ELSE 0 | ||
+ | END | ||
+ | ) AS c3 | ||
+ | FROM | ||
+ | ciberterminal." | ||
+ | WHERE | ||
+ | ( " | ||
+ | ) innerquery' | ||
+ | execute immediate theSQL into c1, c2, c3 using 4001, 4001; | ||
+ | end; | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
==== very simple loop ==== | ==== very simple loop ==== | ||
<code sql> | <code sql> | ||
Line 2805: | Line 2988: | ||
/ | / | ||
</ | </ | ||
- | |||
==== logging output in pl ==== | ==== logging output in pl ==== | ||
Line 2899: | Line 3081: | ||
| | ||
FROM dba_SCHEDULER_JOBS | FROM dba_SCHEDULER_JOBS | ||
- | WHERE ENABLED=TRUE | + | WHERE ENABLED='TRUE' |
; | ; | ||
</ | </ | ||
Line 2944: | Line 3126: | ||
- | ======SECCIÓN : Documentos adicionales====== | + | ====== |
=====Auditoria===== | =====Auditoria===== | ||
- | * [[audit_mini-howto|Auditing Mini-HOWTO]] | + | * [[.howtos:audit_mini-howto|Auditing Mini-HOWTO]] |
=====DataGuard===== | =====DataGuard===== | ||
- | * [[oracle: | + | * [[.docs: |
=====RMAN===== | =====RMAN===== | ||
- | * [[oracle: | + | * [[.docs: |
=====RAC Status===== | =====RAC Status===== | ||
- | * [[oracle: | + | * [[.docs: |
dba/oracle/basic_oracle_sql_querys.txt · Last modified: 2023/07/03 08:37 by dodger