User Tools

Site Tools


dba:oracle: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:oracle_sql_querys [2022/02/11 11:12] – ↷ Page moved from oracle:oracle_sql_querys to dba:oracle:oracle_sql_querys dodgerdba:oracle:oracle_sql_querys [2023/01/31 08:16] (current) – removed dodger
Line 1: Line 1:
-<WRAP center round important 60%> 
-THIS document is [[oracle:basic_oracle_sql_querys]] but with the parts splitted removed</WRAP> 
- 
- 
-====== [DOC] Oracle basic queries ====== 
- 
- 
-======SECCIÓN : QUERYS====== 
- 
-====longest querys==== 
-Las querys con duración de más de 10 segundos: 
-<code sql> 
-COL SQL_TEXT FORMAT A130 WORD WRAPPED 
-SELECT * FROM 
-    ( 
-    SELECT  SQL_ID,  
-            CPU_TIME/NVL(EXECUTIONS,1) TIME_PER_EXECUTION, 
-            CPU_TIME,  
-            EXECUTIONS, 
-            SQL_TEXT 
-    FROM V$SQLAREA 
-    WHERE EXECUTIONS>10 
-    ORDER BY EXECUTIONS DESC, TIME_PER_EXECUTION DESC, CPU_TIME DESC 
-    ) 
-WHERE ROWNUM <31; 
-</code> 
- 
- 
-Ver también: 
-  * V$SQL 
-  * V$SESSION 
- 
- 
-====Long operations==== 
-La vista LONGOPS nos da información de las operaciones largas/costosas. 
-  * [[http://www.dba-oracle.com/longops.htm|Burleson]] info. 
-  * [[https://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2092.htm|Official view information]]. 
- 
- 
-<code sql> 
- 
-COL UNITS FORMAT A12 
-COL opname FORMAT A30 
-COL target FORMAT A30 
-COL message FORMAT A70 
-COL SID_SERIAL FORMAT A15 
- 
-select * from 
-( 
-  select 
-     opname, 
-     start_time, 
-     target, 
-     sofar, 
-     totalwork, 
-     units, 
-     elapsed_seconds, 
-     message 
-   from 
-        v$session_longops 
-  order by start_time desc 
-) 
-where rownum <=1; 
-</code> 
- 
-====Bind variables de querys==== 
- 
-<code sql> 
-COL SQL_ID FORMAT A15 
-COL NAME FORMAT A30 
-COL POSITION FORMAT 999 
-COL DATATYPE_STRING FORMAT A20 
-COL VALUE_STRING FORMAT A100 
-SELECT SQL_ID, 
-        LAST_CAPTURED, 
-        HASH_VALUE, 
-        NAME, 
-        POSITION, 
-        DUP_POSITION, 
-        DATATYPE_STRING, 
-        VALUE_STRING 
-FROM  V$SQL_BIND_CAPTURE  
-WHERE  sql_id='SQL_ID'  
-ORDER BY 1,2,4 
-; 
-</code> 
-======SECCIÓN : Execution PLANS====== 
-===== Hidden options/formatting ===== 
- 
-The display_cursor funcion has the following undocumented format options: 
-  * ALL 
-  * ADVANCED 
-  * OUTLINE 
-  * PROJECTION 
- 
- 
-<code sql> 
-SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp', NULL, 'ADVANCED'); 
-</code> 
- 
-[[https://jonathanlewis.wordpress.com/2008/03/06/dbms_xplan3/|Information about the formatting ops]] 
- 
- 
-===== Ver un plan de ejecución ===== 
-==== De una query ==== 
-Capturar la query: 
-<code sql> 
-EXPLAIN PLAN FOR 
-SELECT * FROM emp e, dept d 
-   WHERE e.deptno = d.deptno 
-   AND e.ename='benoit'; 
-</code> 
-y 
-<code sql> 
-SET LINESIZE 280 
-SET PAGESIZE 999 
-SELECT * FROM table(DBMS_XPLAN.DISPLAY); 
-</code> 
- 
-==== De un SQL_ID ==== 
-<code sql> 
-SELECT * FROM table ( 
-   DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp')); 
-</code> 
- 
-==== Eliminar un Plan de ejecución ==== 
-Primero, hay que saber el SQL_ID y el PLAN_HASH_VALUE, podemos verlos con la siguiente query al ''SQLAREA'': 
-<code sql> 
-select sql_id, sql_text, hash_value, plan_hash_value 
-from v$sql 
-where sql_text like '%SOMETHING HERE%' 
-/ 
-</code> 
- 
-Después, ejecutar el siguiente proceseo: 
-<code sql> 
-vari rc number 
-exec :rc:=dbms_spm.load_plans_from_cursor_cache(sql_id=> '<SQL_ID>',plan_hash_value=> <PLAN_HASH_VALUE>) ; 
-</code> 
-Substituir: 
-  * ''<SQL_ID>'' 
-  * ''<PLAN_HASH_VALUE>'' 
- 
-Habitualmente con eso vale, auqnue a lo mejor hay que hacer "[[http://oracleprof.blogspot.com.es/2011/07/how-to-find-sqlid-and-planhashvalue-in.html|algo más]]". 
- 
-Obtener los valores necesarios para borrarlo (los hashes): 
-<code sql> 
-COL SQL_HANDLE FORMAT A50 
-COL PLAN_NAME FORMAT A50 
-select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines ; 
-</code> 
- 
-Ejecutar el siguiente procedimiento para borarrlo: 
-<code sql> 
-SET SERVEROUTPUT ON 
-DECLARE 
-  l_plans_dropped  PLS_INTEGER; 
-BEGIN 
-  l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline ( 
-    sql_handle => '<SQL_HANDLE>', 
-    plan_name  => '<SQL_PLAN_NAME>'); 
-     
-  DBMS_OUTPUT.put_line(l_plans_dropped); 
-END; 
-/ 
-</code> 
- 
- 
-==== Links ==== 
- 
-  * http://www.oracle-base.com/articles/11g/sql-plan-management-11gr1.php 
-  * http://fordba.wordpress.com/tag/dbms_spm-alter_sql_plan_baseline/ 
-  * http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_spm.htm 
-======SECCIÓN : Performance====== 
-===== Hard parse ===== 
-  * How to Identify Hard Parse Failures (Doc ID 1353015.1) 
-  * http://wiki.ciberterminal.net/doku.php?id=killing_hardparse 
-===== Top 100 querys ===== 
-Esta query se basa en las estadísticas guardadas en el repositorio awr. 
- 
-<code sql> 
-COL CPU_RANK FORMAT 999 ; 
-COL TOTAL_CPU_TIME FORMAT 999999999 ; 
-COL SQLID FORMAT A23 ; 
-select CPU_RANK, SQLID, TOTAL_CPU_TIME, MODULE 
-from ( 
-    select  s.sql_id SQLID,  
-            RANK() OVER (ORDER BY (max(s.CPU_TIME_TOTAL)) DESC) cpu_rank, 
-            ROUND(MAX(S. CPU_TIME_TOTAL)/1000000) TOTAL_CPU_TIME, 
-            MODULE 
-    from 
-    dba_hist_sqlstat s, 
-    dba_hist_snapshot sn 
-    where 
-       sn.snap_id=s.snap_id 
-    group by 
-       s.sql_id, s.MODULE 
-   ) 
-where cpu_rank <=100 
-; 
-</code> 
-===== SGA stats ===== 
-Full: 
-<code sql> 
-select POOL, NAME, BYTES/1024/1024 From v$sgastat ; 
-</code> 
-Únicamente lo libre: 
-<code sql> 
-select POOL, NAME, BYTES/1024/1024 From v$sgastat where name like '%free memory%' ; 
-</code> 
- 
- 
-===== Shared pool histogram ===== 
-<code sql> 
-COL BEGIN_INTERVAL_TIME FORMAT A50 
-COL END_INTERVAL_TIME FORMAT A50 
-COL NAME FORMAT A50 
-  SELECT sn.BEGIN_INTERVAL_TIME, 
-         SN.END_INTERVAL_TIME, 
-         sg.NAME, 
-         sg.POOL, 
-         sg.BYTES 
-    FROM dba_hist_snapshot sn, DBA_HIST_SGASTAT sg 
-   WHERE     sn.SNAP_ID = sg.SNAP_ID 
-         AND POOL = 'shared pool' 
-         AND NAME LIKE '%free memory%' 
-ORDER BY BEGIN_INTERVAL_TIME 
-; 
-</code> 
-===== Library cache Hit ratio ===== 
-<code sql> 
-SELECT 'Buffer Cache' NAME, 
-       ROUND ( 
-            (congets.VALUE + dbgets.VALUE - physreads.VALUE) 
-          * 100 
-          / (congets.VALUE + dbgets.VALUE), 
-          2) 
-          VALUE 
-  FROM v$sysstat congets, v$sysstat dbgets, v$sysstat physreads 
- WHERE     congets.NAME = 'consistent gets' 
-       AND dbgets.NAME = 'db block gets' 
-       AND physreads.NAME = 'physical reads' 
-UNION ALL 
-SELECT 'Execute/NoParse', 
-       DECODE ( 
-          SIGN ( 
-             ROUND ( 
-                  (ec.VALUE - pc.VALUE) 
-                * 100 
-                / DECODE (ec.VALUE, 0, 1, ec.VALUE), 
-                2)), 
-          -1, 0, 
-          ROUND ( 
-             (ec.VALUE - pc.VALUE) * 100 / DECODE (ec.VALUE, 0, 1, ec.VALUE), 
-             2)) 
-  FROM v$sysstat ec, v$sysstat pc 
- WHERE     ec.NAME = 'execute count' 
-       AND pc.NAME IN ('parse count', 'parse count (total)') 
-UNION ALL 
-SELECT 'Memory Sort', 
-       ROUND ( 
-            ms.VALUE 
-          / DECODE ( (ds.VALUE + ms.VALUE), 0, 1, (ds.VALUE + ms.VALUE)) 
-          * 100, 
-          2) 
-  FROM v$sysstat ds, v$sysstat ms 
- WHERE ms.NAME = 'sorts (memory)' AND ds.NAME = 'sorts (disk)' 
-UNION ALL 
-SELECT 'SQL Area get hitrate', ROUND (gethitratio * 100, 2) 
-  FROM v$librarycache 
- WHERE namespace = 'SQL AREA' 
-UNION ALL 
-SELECT 'Avg Latch Hit (No Miss)', 
-       ROUND ( (SUM (gets) - SUM (misses)) * 100 / SUM (gets), 2) 
-  FROM v$latch 
-UNION ALL 
-SELECT 'Avg Latch Hit (No Sleep)', 
-       ROUND ( (SUM (gets) - SUM (sleeps)) * 100 / SUM (gets), 2) 
-  FROM v$latch; 
-</code> 
- 
-===== Espacio usado dentro de la SGA ===== 
-<code sql> 
-set pagesize 132 
- 
-column owner format a16 
-column name  format a36 
-column sharable_mem format 999,999,999 
-column executions   format 999,999,999 
-</code> 
- 
-==== Memory Usage of Shared Pool Order - Biggest First ==== 
- 
-<code sql> 
-column name format 45 
-select  owner, name||' - '||type name, sharable_mem from v$db_object_cache 
-where sharable_mem > 10000 
-  and type in ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE') 
-order by sharable_mem desc 
-/ 
-</code> 
- 
-==== Loads into Shared Pool  - Most Loads First ==== 
-<code sql> 
-select  owner, name||' - '||type name, loads , sharable_mem from v$db_object_cache 
-where loads > 3 
-  and type in ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE') 
-order by loads desc 
-/ 
-</code> 
-==== Executions of Objects in the  Shared Pool  - Most Executions First ==== 
-<code sql> 
-select  owner, name||' - '||type name, executions from v$db_object_cache 
-where executions  > 100 
-  and type in ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE') 
-order by executions  desc 
-/ 
-</code> 
- 
-===== AWR ===== 
- 
-* [[awr|Automated Workload Repository (awr)]] 
- 
- 
-===== Paralelismo de objetos ===== 
- 
- 
-**DEGREE** in dba_* views is VARCHAR! 
-==== indexes ==== 
- 
-Object parallelism ignoring degree 0 or 1 
-<code sql> 
-SELECT 
-  owner, 
-  INDEX_NAME, 
-  degree 
-FROM 
-  DBA_INDEXES 
-WHERE 
-  NOT RTRIM(LTRIM(DEGREE)) IN ( 'DEFAULT', '1', '0' ) 
-; 
-</code> 
- 
- 
-==== TABLES ==== 
- 
-Object parallelism ignoring degree 0 or 1 
-<code sql> 
-SELECT OWNER, 
-    TABLE_NAME, 
-    DEGREE 
-FROM 
-    DBA_TABLES 
-WHERE 
-    NOT RTRIM(LTRIM(DEGREE)) IN ( 'DEFAULT', '1', '0' ) 
-; 
-</code> 
- 
-======SECCIÓN : Otros====== 
- 
-===== Añadir un redo group ===== 
-<code sql> 
-ALTER DATABASE ADD LOGFILE GROUP 5 ('+DG_RECO_01', '+DG_DATA_01') SIZE 512M ; 
-</code> 
-Hay que tener en cuenta los thread groups (rac), si tenemos varios thread groups, habrá que añadirlo al thread correspondiente: 
-<code sql> 
-ALTER DATABASE ADD LOGFILE thread 1 GROUP 5 ('+DG_RECO_01', '+DG_DATA_01') SIZE 512M ; 
-</code> 
- 
-===== Errores de usuario ===== 
-Hay veces que show err no muestra todos los errores: 
-<code sql> 
-select * from user_errors where name like '%OBJECT_NAME%' ; 
-</code> 
-Desde SYS pueden no aparecer los errores, hay que conectarse con el usuario. 
-===== Crear un directorio ===== 
-<code sql> 
-CREATE DIRECTORY <IDENTIFIER> AS '/real/path/of/disk'; 
-</code> 
- 
-===== Estadísticas de generacion de REDO ===== 
- 
-<code SQL>SELECT A.*, Round(A.COUNT#*B.AVG#/1024/1024) Daily_Avg_Mb 
-FROM 
-    ( 
-    SELECT To_Char(First_Time,'YYYY-MM-DD') DAY, COUNT(1) COUNT#, MIN(RECID) MIN#, MAX(RECID) MAX# 
-    FROM v$log_history 
-    GROUP BY To_Char(First_Time,'YYYY-MM-DD') 
-    ORDER BY 1 DESC 
-    ) A, 
-    ( 
-    SELECT Avg(BYTES) AVG# 
-    FROM v$log 
-    ) B 
-    ORDER BY DAY 
-; 
-</code> 
-===== Log Buffer Hit Rate Histogram ===== 
-In order to track the amount of redo read from the in-memory log buffer vs. the amount of redo read from disk, a log buffer hit rate histogram has also been implemented. A new ''x$'' table (''x$logbuf_readhist'') was added to display the histogram information. There are 5 columns of interest that can be obtained using the following query: 
- 
-<code SQL> 
-COL BUFSIZE FORMAT A30 
-COL BUFINFO FORMAT A30 
-select BUFSIZE, RDMEMBLKS, RDDISKBLKS, HITRATE, BUFINFO from X$LOGBUF_READHIST; 
-</code> 
-It returns the following columns: 
- 
-  * ''BUFSIZE'': the log buffer size in Kbytes. If "A" stands for the current log buffer size (as specified by the LOG_BUFFER parameter), then the values of ''BUFSIZE'' in the view are in the range of [0.5A, 2A] increasing by 10% for each row. There are a total of 16 rows. 
-  * ''BUFINFO'': identifies the ‘current’ log buffer and ‘target’ log buffer. The log buffer with ''BUFINFO'' equal to 'CURRENT' is the currently configured buffer size. 
-  * ''RDMEMBLKS'': number of blocks read from memory. 
-  * ''RDDISKBLKS'': number of blocks read from disk. 
-  * ''HITRATE'': calculated by 100 * ''RDMEMBLKS'' / (''RDMEMBLKS + RDDISKBLKS'') 
-In the histogram, the data in the row pointed to by ''CURRENT'' is captured while system is running. ''RDMEMBLKS'' in that row stands for the number of blocks actually read from log buffer in memory, and ''RDDISKBLKS'' stands for the number of blocks actually read from ORL on disk. The ''HITRATE'' is calculated correspondingly. The data in all other rows are calculated based on heuristics. ''RDMEMBLKS'' in those rows stands for the number of blocks that would be read from the log buffer should the buffer size be increased or reduced. If the hit ratio is less than 100%, then if possible, increase the buffer size to the value needed to achieve a 100% hit rate. 
- 
-(Doc ID 951152.1) 
-===== Switch de Archived por hora ===== 
-<code SQL> 
-SELECT to_date(first_time) DAY, 
-    to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00", 
-    to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01", 
-    to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02", 
-    to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03", 
-    to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04", 
-    to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05", 
-    to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06", 
-    to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07", 
-    to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "08", 
-    to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09", 
-    to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10", 
-    to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11", 
-    to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12", 
-    to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13", 
-    to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14", 
-    to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15", 
-    to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16", 
-    to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17", 
-    to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18", 
-    to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19", 
-    to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20", 
-    to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21", 
-    to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22", 
-    to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23" 
-from v$log_history 
-where to_date(first_time) > sysdate -8 
-GROUP by to_char(first_time,'YYYY-MON-DD'), to_date(first_time) 
-order by to_date(first_time) 
-/ 
-</code> 
- 
-===== Flush de redos ===== 
-<code sql> 
-alter system checkpoint ; 
-</code> 
- 
-===== Stopping hardway ===== 
-Stop/shutdown de emergencia cuando nada más funciona: 
-<code sql> 
-sqlplus -prelim "/ as sysdba" 
-shutdown abort 
-</code> 
- 
-=====Localizar una query desde un sql_id del alert===== 
-Para localizar la query, tenemos que tener el SQL_ID que aparece en el alert y esperar que la query no se haya ido de memoria. 
-<code sql>SELECT SQL_FULLTEXT FROM v$sql WHERE sql_id like 'IDENTIFICADOR_DE_QUERY' ;</code> 
- 
-===== Dropping the database ===== 
-<code sql>shutdown abort; 
-startup mount exclusive restrict;  
-drop database; 
-exit ; 
-</code> 
- 
-===== Listar Parámetros ocultos e indocumentados ===== 
-Información completa: 
-<code sql> 
-COL NAME FORMAT A60 
-COL VALUE FORMAT A30 
-COL DEFLT FORMAT A30 
-COL TYPE FORMAT A10 
-COL description FORMAT A120 
-select  a.ksppinm name, 
-        b.ksppstvl value, 
-        b.ksppstdf deflt, 
-        decode(a.ksppity, 1, 'boolean', 2, 'string', 3, 'number', 4, 'file', a.ksppity) type, 
-        a.ksppdesc description 
-from sys.x$ksppi a, sys.x$ksppcv b 
-where a.indx = b.indx 
-and a.ksppinm like '\_%' escape '\' 
-order by name ; 
-</code> 
-únicamente nombre y descripción: 
-<code sql> 
-COL KSPPINM FORMAT A60 
-COL ksppdesc FORMAT A120 
-select ksppinm, ksppdesc 
-from  x$ksppi  
-where substr(ksppinm,1,1) = '_' 
-order by  1,2; 
-</code> 
-===== db_link / dblink ===== 
-DATABASE LINKS! 
-==== crear ==== 
-<code sql> 
-sqlplus SYSTEM 
-</code> 
-<code sql> 
-CREATE DATABASE LINK VOXEL CONNECT TO SYSTEM identified by ******* using 'ORIGEN'; 
-</code> 
- 
-==== listar ==== 
- 
-<code sql> 
-SET PAUSE 'Press Return to Continue' 
-SET PAGESIZE 60 
-SET LINESIZE 300 
-  
-COLUMN owner FORMAT A30 
-COLUMN db_link FORMAT A50 
-COLUMN username FORMAT A30 
-COLUMN host FORMAT A30 
-  
-SELECT owner, 
-       db_link, 
-       username, 
-       host 
-FROM   dba_db_links 
-ORDER BY owner, db_link 
-/ 
-</code> 
- 
-==== Borrar ==== 
-<code sql> 
-DROP DATABASE LINK <link_name>; 
-</code> 
-o 
-<code sql> 
-DROP PUBLIC DATABASE LINK <link_name>; 
-</code> 
- 
- 
- 
-===== Multi-line comments ===== 
-Para insertar un comentario multi-linea: 
-  * Habilitar las blanklines: 
-<code sql> 
-SET SQLBLANKLINES ON 
-</code> 
-  * Insertar el comentario: 
-<code sql> 
-COMMENT ON TABLE TESTTABLE IS 'TEST 
- 
-MULTILINE 
- 
-COMMENT! 
-' 
-; 
-</code> 
- 
-===== REGEXP_LIKE ===== 
- 
-  * [[http://docs.oracle.com/cd/E11882_01/server.112/e41084/conditions007.htm#SQLRF00501|Official reference]] 
-  * [[http://docs.oracle.com/cd/E11882_01/server.112/e41084/ap_posix003.htm#SQLRF55544|Regexp in oracle#1]] 
-  * [[http://docs.oracle.com/cd/E11882_01/server.112/e41084/ap_posix002.htm#SQLRF55542|Regexp in oracle#2]] 
-  * [[http://docs.oracle.com/cd/E11882_01/server.112/e41084/ap_posix001.htm#SQLRF55540|Regexp in oracle#3]] 
- 
-===== DBMS_SCHEDULER ===== 
- 
-==== Listar jobs ==== 
- 
-<code sql> 
-COL owner FORMAT A20 
-COL job_name FORMAT A40 
-COL job_subname FORMAT A30 
-COL job_style FORMAT A30 
-COL job_creator FORMAT A30 
-COL client_id FORMAT A30 
-COL program_owner FORMAT A20 
-COL program_name FORMAT A35 
-COL job_type FORMAT A30 
-COL job_action FORMAT A30 
-COL schedule_owner FORMAT A20 
-COL schedule_name FORMAT A30 
-COL REPEAT_INTERVAL FORMAT A70 
-COL START_DATE FORMAT A50 
- 
-SELECT owner, 
-       job_name, 
---        job_subname, 
---        job_style, 
---        job_creator, 
---        client_id, 
---        program_owner, 
---        program_name, 
-       job_type, 
---        job_action, 
---        schedule_owner, 
---        schedule_name, 
-       START_DATE, 
-       REPEAT_INTERVAL, 
-       ENABLED 
-FROM dba_SCHEDULER_JOBS 
-WHERE ENABLED=TRUE 
-; 
-</code> 
- 
- 
-==== Historial de ejecutiones ==== 
-<code sql> 
-COL owner FORMAT A20 
-COL job_name FORMAT A40 
-COL job_subname FORMAT A30 
-COL job_style FORMAT A30 
-COL job_creator FORMAT A30 
-COL client_id FORMAT A30 
-COL program_owner FORMAT A20 
-COL program_name FORMAT A35 
-COL job_type FORMAT A30 
-COL job_action FORMAT A30 
-COL schedule_owner FORMAT A20 
-COL schedule_name FORMAT A30 
-COL REPEAT_INTERVAL FORMAT A70 
-COL START_DATE FORMAT A50 
- 
-SELECT owner, 
-       job_name, 
---        job_subname, 
---        job_style, 
---        job_creator, 
---        client_id, 
---        program_owner, 
---        program_name, 
-       job_type, 
---        job_action, 
---        schedule_owner, 
---        schedule_name, 
-       START_DATE, 
-       REPEAT_INTERVAL, 
-       ENABLED 
-FROM dba_SCHEDULER_JOBS 
-WHERE ENABLED=TRUE 
-; 
-</code> 
- 
- 
- 
- 
-======SECCIÓN : Documentos adicionales====== 
- 
-=====Auditoria===== 
-  * [[audit_mini-howto|Auditing Mini-HOWTO]] 
- 
-=====DataGuard===== 
- 
-  * [[dataguard_mini-howto#status_querys|Documento]] 
- 
-=====RMAN===== 
-  * [[RMAN_basic_querys|RMAN basic querys]] 
- 
-=====RAC Status===== 
-  * [[CLI_tools|CommandLine tools para el control de Oracle]] 
- 
- 
-=====Links===== 
-See [[:start#esternal_doc|here]] 
- 
- 
- 
-===== Lista de DocId interesantes ===== 
-  * How To Find The Size Of A Number Of Rows Of A Table (Doc ID 1370050.1) 
- 
  
dba/oracle/oracle_sql_querys.1644577952.txt.gz · Last modified: 2022/02/11 11:12 by dodger