Differences

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

--- dba:oracle:basic_oracle_sql_querys [2023/01/31 08:17] – removed dodger
+++ dba:oracle:basic_oracle_sql_querys [2023/07/03 08:37] (current) – dodger
@@ Line 1: / Line 1: @@
+====== [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>
+====== [SECTION] STATUS======
+===== Hora de la bbdd =====
+<code sql>SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW" FROM DUAL;</code>
+===== SCN =====
+==== get scn ====
+<code sql>
+select current_scn from v$database;
+</code>
+==== convert de/a timestamp ====
+<code sql>
+select timestamp_to_scn(to_timestamp('24/09/2012 14:24:54','DD/MM/YYYY HH24:MI:SS')) as scn from dual;
+</code>
+<code sql>
+select scn_to_timestamp(7705798324) as timestamp from dual;
+</code>
+=====Status genérico=====
+<code sql>
+set lines 110
+col strtd hea 'STARTED'
+col instance_name for a8 hea 'INSTANCE'
+col host_name for a15 hea 'HOSTNAME'
+col version for a10
+select instance_name, version, host_name, status, database_status, to_char(startup_time,'DD-MON-YYYY HH:MI:SS') strtd
+from v$instance;
+</code>
+<code sql>
+col instnace format a35
+select instance, status, enabled, open_time, checkpoint_time from v$thread;
+</code>
+=====Memoria=====
+====SGA====
+Configuración actual:
+<code sql>SHOW SGA;</code>
+<code sql>select * from v$sgainfo;</code>
+<code sql>
+COL COMPONENT FORMAT A30
+select * from v$sga_dynamic_components;</code>
+====PGA====
+<code sql>
+show parameter PGA;
+</code>
+or
+<code sql>
+set pages 999;
+column pga_size format 999,999,999
+select
+    1048576+a.value+b.value   pga_size
+from
+   v$parameter a,
+   v$parameter b
+where
+   a.name = 'sort_area_size'
+and
+   b.name = 'hash_area_size'
+;
+</code>
+=====Archivelog Mode=====
+<code sql>
+SELECT LOG_MODE FROM SYS.V$DATABASE;
+</code>
+<code sql>
+SELECT GROUP#, ARCHIVED FROM SYS.V$LOG;
+</code>
+<code sql>
+ARCHIVE LOG LIST
+</code>
+=====Ficheros/Directorios=====
+====Localizar spfile====
+<code sql>show parameter spfile ;</code>
+<code sql>
+col value format a200
+select name, value from v$parameter where name = 'spfile' ;
+</code>
+====Listar controlfiles====
+<code sql>
+COL NAME FORMAT A200;
+select NAME from V$CONTROLFILE ;
+</code>
+o en RAC:
+<code sql>
+COL NAME FORMAT A200;
+select NAME from GV$CONTROLFILE ;
+</code>
+Y también:
+<code sql>
+COL VALUE FORMAT A200;
+SELECT value
+FROM gv$parameter
+WHERE name = 'control_files';
+</code>
+====Directorios====
+=== Listar ===
+<code sql>
+COL DIRECTORY_NAME FORMAT A40;
+COL DIRECTORY_PATH FORMAT A180;
+select DIRECTORY_NAME, DIRECTORY_PATH FROM all_directories ;
+</code>
+=====Hubicación de trazas y alert=====
+<code sql>SHOW PARAMETER BACKGROUND_DUMP_DEST ; </code>
+=====Debugging=====
+====ASM alert y trazas====
+No he conseguido query para sacarlo, solo mediante variables de sistema:
+<code sql>$ORACLE_BASE/diag/$ASMDB/$DB1/$ORACLE_SID/trace/</code>
+Hay que cargar las variables de ''+ASM'', por supuesto:
+  * ''$ASMDB'': Normalmente se corresponde con: ''tolower("+ASM") | sed 's,+,,g'''
+  * ''$DB1'': Normalmente se corresponde con: ''tolower("+ASM")''
+  * ''$ORACLE_SID'': Normalmente se corresponde con "''+ASM''"
+====Debug de otra sesión====
+Se puede debugar otra sesión a la nuestra mediante:
+<code sql>
+oradebug setospid 3885
+oradebug unlimit
+oradebug event 10046 trace name context forever,level 12
+</code>
+El event 10046 está sacado de Doc ID 1198753.1 (para rman), se puede usar cualquier otro
+====== [SECTION] Config ======
+=====Nombre de la bd=====
+<code sql>show parameter db_name ;</code>
+=====DBID de la bd=====
+<code sql>SELECT DBID FROM V$DATABASE;</code>
+En caso de que la bbdd esté down, podemos verlo en los logs de RMAN
+<code sql>
+$rman TARGET /
+Recovery Manager: Release 10.2.0.1.0 - Production on Tue May 6 01:25:48 2008
+Copyright (c) 1982, 2005, Oracle. All rights reserved.
+connected to target database: ARJU (DBID=2869417476)
+</code>
+Mas info: [[http://arjudba.blogspot.com/2008/05/how-to-discover-find-dbid.html]]
+=====Versión=====
+<code sql> select   * from v$version ;</code>
+==== charset ====
+<code sql>
+COL VALUE FORMAT A50
+COL PARAMETER FORMAT A50
+select * from nls_database_parameters ;
+</code>
+===== REDO's =====
+==== Status de los REDO (all in one query) ====
+<code sql>
+col members format a150
+col status format a20
+select vl.group#,
+    vl.thread#,
+    vl.sequence#,
+    vl.bytes/1024/1024 SIZE_MB,
+    vl.archived,
+    vl.status,
+    gm.type status,
+    gm.members
+from v$log vl,
+    (select group#, type, listagg(vlf.member, ' ; ') within group (order by member) as members
+        from v$logfile vlf
+    group by vlf.group#, type) gm
+where
+    vl.group#=gm.group#
+;
+</code>
+==== Status de los REDO ====
+<code sql>
+select * from v$log;
+</code>
+==== Localización de los REDO ====
+<code sql>
+col member format a100
+select * from v$logfile ;
+</code>
+==== Destino de los redos ====
+Dónde se envían los redos:
+<code sql>
+col DEST_NAME FORMAT A20;
+COL STATUS FORMAT A15;
+COL NAME_SPACE FORMAT A15;
+COL SCHEDULE FORMAT A15;
+COL DESTINATION FORMAT A30;
+SELECT DEST_NAME, STATUS, NAME_SPACE, SCHEDULE, DESTINATION FROM V$ARCHIVE_DEST WHERE ROWNUM < 10 ;
+</code>
+=====Parametros=====
+Por regla general:
+  * <code sql>show parameter <param_name></code> in sql*plus
+  * <code sql>select value from v$parameter where name = lower('param_name') </code>
+  * <code sql>select value from gv$parameter where name = lower('param_name') </code>
+=====Resetear un parámetro=====
+<code sql>
+ALTER SYSTEM RESET <PARAMETER_NAME> ;
+</code>
+===== Standby redo logs =====
+This query  will show ''ORL'' for //ONLINE redo logs// and ''SRL'' for //STANDBY redo logs//
+<code sql>
+COL member format 150
+select lf.group#,l_type.log_type as type, lf.member
+from v$logfile lf
+join (
+   select group#,'ORL' as log_type from v$log
+   union
+   select group#,'SRL' as log_type from v$standby_log) l_type
+on lf.group#=l_type.group#
+order by lf.group#
+/
+</code>
+====== [SECTION]  Storage======
+=====Listar tablespaces=====
+<code sql> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES ;</code>
+=====Listar datafiles con la BBDD not open=====
+<code sql>
+COL NAME FORMAT A200
+select * from v$dbfile order by 1;
+</code>
+===== List datafiles (simple)=====
+<code sql>
+col FILE_NAME FORMAT A80;
+COL tablespace_name FORMAT A30;
+select FILE_ID,
+        file_name,
+        Tablespace_name,
+	bytes/(1024*1024) "Size MB",
+	MAXBYTES/(1024*1024) "MAX MB",
+	AUTOEXTENSIBLE "extensible"
+from dba_data_files
+order by FILE_ID
+;</code>
+or super simple:
+<code sql>
+select file_name from dba_data_files order by 1;
+</code>
+===== Tablespace creation =====
+==== Without ASM====
+<code sql>CREATE SMALLFILE
+    TABLESPACE "NOMBRE_DEL_TABLESPACE_DAT"
+    DATAFILE '/ruta/completa/al/datafile.dbf'
+    SIZE 500M AUTOEXTEND
+    ON MAXSIZE 5000M
+    ;
+</code>
+==== With ASM====
+<code sql>
+CREATE SMALLFILE
+    TABLESPACE "NOMBRE_DEL_TABLESPACE_DAT"
+    DATAFILE '+ASMNAME'
+    SIZE 500M AUTOEXTEND
+    ON MAXSIZE 5000M
+   ;
+</code>
+===== View Tablespace DDL=====
+<code sql>
+select dbms_metadata.get_ddl('TABLESPACE','<TABLESPACE_NAME>') from dual ;
+</code>
+===== Add space to Tablespace =====
+<code sql>alter tablespace NOMBRE_DEL_TABLESPACE
+      add datafile /ruta/completa/al/datafile.dbf
+      ;
+</code>
+===== Space Information =====
+=== ASM Diskgroup information====
+<code sql>select name, total_mb, free_mb from v$asm_diskgroup_stat ;</code>
+o
+<code sql>
+select  name, group_number, disk_number, total_mb, free_mb
+from    v$asm_disk
+order   by group_number
+/
+</code>
+====Tablespace information====
+Mi query, tiene en cuenta si el tablespace tiene autoextend o es de tamaño fijo para computar el espacio REAL:
+<code sql>
+COL TABLESPACE_NAME FORMAT A40
+COL MAX_MB FORMAT 99999999999
+COL REAL_FREE_MB FORMAT 99999999999
+COL PERCENT FORMAT 999.99
+SELECT TABLESPACE_NAME,
+        MAX_MB,
+        REAL_FREE_MB FREE_MB,
+        MAX_MB-REAL_FREE_MB USED_MB,
+        (MAX_MB-REAL_FREE_MB)/MAX_MB*100 "PERCENT"
+FROM (
+    SELECT MAXUSAGE.TABLESPACE_NAME,
+            MAXUSAGE.MAX_MB,
+            CASE WHEN MAXUSAGE.ACTUAL_DATAFILE_MB < MAXUSAGE.MAX_MB THEN
+                MAX_MB-(ACTUAL_DATAFILE_MB-FREE_MB)
+            ELSE
+                FREE_MB
+            END  REAL_FREE_MB
+    FROM
+        (
+        select TABLESPACE_NAME,
+                SUM(case when MAXBYTES > 0 then MAXBYTES else BYTES END)/1024/1024 MAX_MB,
+                SUM(BYTES)/1024/1024 ACTUAL_DATAFILE_MB
+        FROM DBA_DATA_FILES
+        GROUP BY TABLESPACE_NAME
+        ) MAXUSAGE,
+        (
+        select TABLESPACE_NAME, SUM(BYTES)/1024/1024 FREE_MB
+            FROM dba_free_space
+            GROUP BY TABLESPACE_NAME
+        ) FREEUSAGE
+    WHERE MAXUSAGE.TABLESPACE_NAME=FREEUSAGE.TABLESPACE_NAME)
+;
+</code>
+Query "normal":
+<code sql>SET PAGES 999
+COL TABLESPACE_NAME FORMAT A40
+COL "SIZE_MB" FORMAT 99999999999
+COL "FREE_MB" FORMAT 99999999999
+COL "OCCUPIED_MB" FORMAT 99999999999
+COL "PERCENT" FORMAT 999
+SELECT  USED.TABLESPACE_NAME,
+        CEIL(USED.USED_MB) "SIZE_MB",
+        USED.USED_MB-FREE.FREE_MB "OCCUPIED_MB",
+        DECODE(CEIL(FREE.FREE_MB),NULL,0,CEIL(FREE.FREE_MB)) "FREE_MB",
+        DECODE(100 - CEIL(FREE.FREE_MB/USED.USED_MB*100),NULL,100,100 - CEIL(FREE.FREE_MB/USED.USED_MB*100)) "PERCENT"
+FROM (
+        SELECT  TABLESPACE_NAME,
+                SUM(BYTES)/1024/1024 USED_MB
+        FROM DBA_DATA_FILES
+        GROUP BY TABLESPACE_NAME
+        UNION ALL
+        SELECT TABLESPACE_NAME || '**TEMP**', SUM(BYTES)/1024/1024 USED_MB
+        FROM DBA_TEMP_FILES
+        GROUP BY TABLESPACE_NAME
+    ) USED,
+    (
+        SELECT  TABLESPACE_NAME,
+                SUM(BYTES)/1024/1024 FREE_MB
+        FROM DBA_FREE_SPACE
+        GROUP BY TABLESPACE_NAME
+    ) FREE
+WHERE USED.TABLESPACE_NAME=FREE.TABLESPACE_NAME
+ORDER BY 1
+/
+</code>
+==== Space used by tables, indexes... ====
+Column format
+<code SQL>
+COL OWNER FORMAT A10;
+COL SEGMENT_NAME FORMAT A35;
+COL TABLESPACE_NAME FORMAT A35;
+COL TOTAL_MB FORMAT 99999990.9999 ;
+COL SUM_BYTES FORMAT 999999999999999999.9999 ;
+</code>
+  * Tables:
+<code SQL>
+SELECT  SEGMENT_NAME,
+        SUM(BYTES) SUM_BYTES,
+        NVL(SUM(BYTES)/1024/1024,0.0) TOTAL_MB
+FROM DBA_EXTENTS
+WHERE OWNER='DATABASE_USER'
+AND SEGMENT_TYPE='TABLE'
+GROUP BY SEGMENT_NAME, SEGMENT_TYPE, OWNER, TABLESPACE_NAME
+ORDER BY SEGMENT_NAME,TABLESPACE_NAME
+;
+</code>
+  * Indexes:
+<code SQL>
+SELECT  SEGMENT_NAME,
+        SUM(BYTES) SUM_BYTES,
+        NVL(SUM(BYTES)/1024/1024,0.0) TOTAL_MB
+FROM DBA_EXTENTS
+WHERE OWNER='DATABASE_USER'
+AND SEGMENT_TYPE='INDEX'
+GROUP BY SEGMENT_NAME, SEGMENT_TYPE, OWNER, TABLESPACE_NAME
+ORDER BY SEGMENT_NAME,TABLESPACE_NAME
+;
+</code>
+Sort by size:
+  * Tables:
+<code sql>
+SELECT SEGMENT_NAME, SUM_BYTES, NVL(SUM_BYTES/1024/1024,0.0) TOTAL_MB FROM
+    (
+    SELECT  SEGMENT_NAME,
+            SUM(BYTES) SUM_BYTES
+    FROM DBA_EXTENTS
+    WHERE OWNER='DATABASE_USER'
+    AND SEGMENT_TYPE='TABLE'
+    GROUP BY SEGMENT_NAME, SEGMENT_TYPE, OWNER, TABLESPACE_NAME
+    )
+    ORDER BY SUM_BYTES
+;
+</code>
+  * Indixes:
+<code sql>
+SELECT SEGMENT_NAME, SUM_BYTES, NVL(SUM_BYTES/1024/1024,0.0) TOTAL_MB FROM
+    (
+    SELECT  SEGMENT_NAME,
+            SUM(BYTES) SUM_BYTES
+    FROM DBA_EXTENTS
+    WHERE OWNER='DATABASE_USER'
+    AND SEGMENT_TYPE='INDEX'
+    GROUP BY SEGMENT_NAME, SEGMENT_TYPE, OWNER, TABLESPACE_NAME
+    )
+    ORDER BY SUM_BYTES
+;
+</code>
+==== REAL space taken by a table ====
+Based on:
+How To Find The Size Of A Number Of Rows Of A Table (Doc ID 1370050.1):
+Se puede usar el AVG_ROW_LEN de DBA_TABLES, que es espacio medio en BYTES de cada row en la tabla.
+Primero hay que lanzar el cálculo de stats:
+<code sql>
+ANALYZE TABLE <TABLE_NAME> COMPUTE STATISTICS ;
+</code>
+Necesitamos también el tamaño de bloque:
+<code sql>
+ SHOW PARAMETER DB_BLOCK_SIZE
+</code>
+<code sql>
+SELECT (
+    (SELECT COUNT(*) FROM <TABLE_NAME>)
+    /
+    ((<DB_BLOCK_SIZE>-(<DB_BLOCK_SIZE>*(PCT_FREE/100)))/AVG_ROW_LEN)*<DB_BLOCK_SIZE>/1024/1024
+    ) as ESTIMATED_MB
+FROM DBA_TABLES
+WHERE TABLE_NAME LIKE '<TABLE_NAME>' ;
+</code>
+==== Space used by a table with LOB columns ====
+<code sql>
+SELECT segment_name, segment_type, bytes / 1024 / 1024 size_in_MB
+FROM dba_segments
+WHERE (
+    owner = 'USERNAME'
+        AND segment_name = 'TABLE_NAME'
+        OR segment_name IN (
+            SELECT segment_name
+            FROM all_lobs
+            WHERE table_name = 'TABLE_NAME'
+            UNION
+            SELECT index_name
+            FROM all_lobs
+            WHERE table_name = 'TABLE_NAME'
+            )
+);
+</code>
+==== Space DELTA of a table ====
+Based on AWR tables:
+<code sql>
+SELECT
+    h.BEGIN_INTERVAL_TIME  || ';' || o.OBJECT_NAME || ';' || s.SPACE_USED_DELTA
+FROM
+    DBA_OBJECTS o,
+    DBA_HIST_SNAPSHOT h,
+    (
+        SELECT
+            SNAP_ID,
+            TS#,
+            OBJ#,
+            SPACE_USED_DELTA
+        FROM
+            DBA_HIST_SEG_STAT
+        where SPACE_USED_DELTA >0
+    ) s,
+    v$tablespace t
+WHERE
+    s.OBJ# = o.OBJECT_ID
+    AND   s.TS# = t.TS#
+    and   o.owner = 'TABLE_OWNER'
+    and   o.object_type = 'TABLE'
+    and s.SNAP_ID = h.SNAP_ID
+ORDER BY
+desc
+;
+</code>
+===== TEMP tablespace Management =====
+==== TEMP tablespace datafiles ====
+<code SQL>
+col file_name format a150
+SELECT
+    file_id,
+    file_name,
+    tablespace_name,
+    status
+FROM
+    dba_temp_files;
+</code>
+==== New TEMP ====
+<code sql>
+CREATE TEMPORARY TABLESPACE TEMP tempfile '/u02/oradata/DBTEST/datafile/temp_01.dbf' SIZE 1G AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED ;
+</code>
+==== Add space to TEMP ====
+<code sql>
+ALTER TABLESPACE TEMP ADD TEMPFILE  '+DG_RECO_01' SIZE 2G autoextend on next 512m maxsize unlimited;
+</code>
+==== Switch TEMP tablespace ====
+<code sql>
+ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP ;
+</code>
+===== TEMP space usage =====
+Based on the following docs:
+  * Doc ID 793380.1
+  * Doc ID 317441.1
+Both based on trying to solve ORA-1652
+Check the status of the sort segment utilization :
+<code sql>
+select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;
+</code>
+And overall:
+<code sql>
+COL USERNAME FORMAT A33
+COL SID_SERIAL FORMAT A15
+COL OSUSER FORMAT A33
+COL TABLESPACE FORMAT A40
+COL sql_text format A100 word wrapped
+SELECT a.username,
+    a.sid || ',' || a.serial# AS SID_SERIAL,
+    a.osuser,
+    b.tablespace,
+    b.blocks,
+    c.sql_text
+FROM v$session a,
+    v$tempseg_usage b,
+    v$sqlarea c
+WHERE a.saddr = b.session_addr
+AND c.address= a.sql_address
+AND c.hash_value = a.sql_hash_value
+ORDER BY b.tablespace, b.blocks;
+</code>
+==== Space used by TEMP====
+<code SQL>
+COL TABLESPACE_SIZE FOR 999,999,999,999
+COL ALLOCATED_SPACE FOR 999,999,999,999
+COL FREE_SPACE FOR 999,999,999,999
+SELECT *
+FROM   dba_temp_free_space
+/
+</code>
+Resumido en MB:
+<code SQL>
+SELECT
+   A.tablespace_name tablespace,
+   D.mb_total,
+   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
+FROM
+   v$sort_segment A,
+(
+SELECT
+   B.name,
+   C.block_size,
+   SUM (C.bytes) / 1024 / 1024 mb_total
+FROM
+   v$tablespace B,
+   v$tempfile C
+WHERE
+   B.ts#= C.ts#
+GROUP BY
+   B.name,
+   C.block_size
+) D
+WHERE
+   A.tablespace_name = D.name
+GROUP by
+   A.tablespace_name,
+   D.mb_total
+/
+</code>
+=====Managing ASM=====
+==== Connection ====
+Export vars:
+<code sql>
+export ORACLE_BASE=/opt/ora11g
+export ORACLE_SID=+ASM
+export ORACLE_HOME=/opt/ora11g/product/11.1.0/asm
+export TNS_ADMIN=/opt/ora11g/product/11.1.0/asm/network/admin
+export PATH=/opt/ora11g/product/11.1.0/asm/bin:$PATH
+sqlplus '/ as sysasm'
+</code>
+O usar el "*.env" por supuesto
+Asm cli/wrapper:
+<code sql>asmcmd -p</code>
+====Main views====
+| <code sql>V$ASM_ALIAS</code> |Displays a row for each alias present in every disk group mounted by the ASM instance.|
+| <code sql>V$ASM_CLIENT</code> |Displays a row for each database instance using a disk group managed by the ASM instance. |
+| <code sql>V$ASM_DISK</code> |Displays a row for each disk discovered by the ASM instance, including disks which are not part of any disk group. |
+| <code sql>V$ASM_DISKGROUP</code> |Displays a row for each disk group discovered by the ASM instance. |
+| <code sql>V$ASM_DISK_STAT</code> & <code sql>V$ASM_DISKGROUP_STAT</code> | Same as before but '''DON'T MAKE A DISK DISCOVER CALL''' (very recommended on 10.1) |
+| <code sql>V$ASM_FILE</code> |Displays a row for each file for each disk group mounted by the ASM instance. |
+| <code sql>V$ASM_OPERATION</code> |Displays a row for each file for each long running operation executing in the ASM instance.|
+| <code sql>V$ASM_TEMPLATE</code> |Displays a row for each template present in each disk group mounted by the ASM instance. |
+==== Disk list ====
+<code sql>
+COL PATH FORMAT A100
+COL NAME FORMAT A40
+COL FAILGROUP FORMAT A30
+SELECT  NAME,
+        PATH,
+        OS_MB,
+        TOTAL_MB,
+        FREE_MB,
+        STATE,
+        REDUNDANCY,
+        FAILGROUP
+FROM V$ASM_DISK
+ORDER BY NAME
+;
+</code>
+==== Diskgroup list====
+<code sql>COLUMN DISK_NAME FORMAT A25;
+COLUMN DISKGROUP FORMAT A20;
+COLUMN CAPACITY FORMAT 999999999;
+COLUMN TOTAL_MB FORMAT 999999999;
+COLUMN FREE_MB FORMAT 999999999;
+COLUMN SYS_PATH FORMAT A80;
+SELECT D.NAME AS DISK_NAME,
+	G.NAME AS DISKGROUP,
+	D.OS_MB AS CAPACITY,
+	D.TOTAL_MB AS TOTAL_MB,
+	D.FREE_MB AS FREE_MB,
+	D.STATE AS STATE,
+	D.PATH AS SYS_PATH
+FROM V$ASM_DISK D, V$ASM_DISKGROUP G
+WHERE D.GROUP_NUMBER=G.GROUP_NUMBER ;
+</code>
+==== Diskgroups space====
+<code sql>
+COLUMN DISKGROUP FORMAT A20;
+COLUMN CAPACITY_GB FORMAT 999999999.99;
+COLUMN FREE_GB FORMAT 999999999.99;
+SELECT G.NAME AS DISKGROUP,
+    SUM(D.TOTAL_MB)/1024 AS CAPACITY_GB,
+    SUM(D.FREE_MB)/1024 AS FREE_GB
+FROM V$ASM_DISK D, V$ASM_DISKGROUP G
+WHERE D.GROUP_NUMBER=G.GROUP_NUMBER
+GROUP BY G.NAME
+;
+</code>
+==== Diskgroups Info====
+<code sql>
+COL NAME FORMAT A60
+COL VALUE FORMAT A30
+SELECT dg.name AS diskgroup,
+        a.name,
+        A.VALUE,
+        READ_ONLY
+FROM V$ASM_DISKGROUP dg, V$ASM_ATTRIBUTE a
+WHERE dg.group_number = a.group_number
+ORDER BY DG.NAME
+;
+</code>
+==== Diskgroup Creation ====
+Para crear el diskgroup, debemos hacer un :
+<code sql>
+/etc/init.d/oracleasm createdisk NOMBRE_DEL_DISCO DISPOSITIVO
+</code>
+El dispositivo tiene que ser una partición de disco (no directamente un sdX)
+Después, listamos el path desde ASM:
+<code sql>
+SELECT NAME, PATH  FROM V$ASM_DISK ;
+</code>
+Y con el path hacemos la query de create:
+<code sql>create diskgroup DATA external redundancy disk
+	'ORCL:PATH_DISK1',
+	'ORCL:PATH_DISK2';
+</code>
+==== Diskgroup deletion====
+<code sql>
+DROP DISKGROUP 'DISKGROUP_NAME'
+</code>
+O
+<code sql>
+DROP DISKGROUP 'DISKGROUP_NAME' INCLUDING CONTENTS
+</code>
+=====ACFS=====
+Documentación básica:
+  * [[http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/storage/acfs/acfs.htm|Managing the ASM Cluster File System (ACFS)]]
+===== FRA =====
+Flash recovery area
+==== FRA reserved size ====
+<code sql>
+show parameter db_recovery;
+</code>
+==== FRA space usage====
+<code sql> select * from v$flash_recovery_area_usage;
+</code>
+y
+<code sql>
+COL NAME FORMAT A100;
+SELECT NAME,
+       SPACE_LIMIT/1024/1024,
+       SPACE_USED/1024/1024,
+       NUMBER_OF_FILES
+FROM V$RECOVERY_FILE_DEST ;
+</code>
+===== UNDO =====
+==== Undo Info ====
+<code sql>
+COL NAME FORMAT A20;
+COL VALUE FORMAT A60;
+SELECT name, value
+FROM gv$parameter
+WHERE name LIKE '%undo%';
+</code>
+==== UNDO Space usage ====
+Very simple:
+<code sql>
+select tablespace_name, status, sum(blocks) * 8192/1024/1024/1024 GB
+from dba_undo_extents group by tablespace_name, status;
+</code>
+<code sql>
+select  TOTALSPACE.TOTAL TOTAL_SPACE,
+        NVL(USEDSPACE.USED,0.0) USED_SPACE,
+- trunc(NVL(USEDSPACE.USED,0.0)/TOTALSPACE.TOTAL * 1000) / 10 PERCENT_FREE
+FROM (
+    SELECT TABLESPACE_NAME, SUM(MAXBYTES)/1024/1024 TOTAL
+    FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = (SELECT value FROM v$parameter WHERE name LIKE 'undo_tablespace')
+    GROUP BY TABLESPACE_NAME
+    ) TOTALSPACE
+LEFT OUTER JOIN
+    (
+    SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 USED
+    FROM DBA_UNDO_EXTENTS
+    WHERE (STATUS='UNEXPIRED' OR STATUS='ACTIVE')
+        AND TABLESPACE_NAME = (SELECT value FROM v$parameter WHERE name LIKE 'undo_tablespace')
+    GROUP BY TABLESPACE_NAME
+    ) USEDSPACE
+ON TOTALSPACE.TABLESPACE_NAME=USEDSPACE.TABLESPACE_NAME
+;
+</code>
+==== UNDO space usage by sessions  ====
+<code sql>
+col ROLL_NAME format a30
+col userID format a40
+col sid_serial format a15
+COL PROGRAM FORMAT A50
+COL MACHINE FORMAT A40
+SELECT r.name roll_name,
+         s.osuser || '/' || s.username userID,
+         s.sid || '.' || s.serial# SID_SERIAL,
+         s.program program,
+         s.status status,
+         s.machine machine
+   FROM v$lock l, v$rollname r, v$session s
+   WHERE     s.sid = l.sid
+         AND TRUNC (l.id1(+) / 65536) = r.usn
+         AND l.TYPE(+) = 'TX'
+         AND l.lmode(+) = 6
+ORDER BY r.name
+/
+</code>
+==== Optimal UNDO retention ====
+<code sql>
+col "UNDO RETENTION [Sec]" format a30
+SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
+       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
+       ROUND((d.undo_size / (to_number(f.value) *
+       g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
+  FROM (
+       SELECT SUM(a.bytes) undo_size
+          FROM v$datafile a,
+               v$tablespace b,
+               dba_tablespaces c
+         WHERE c.contents = 'UNDO'
+           AND c.status = 'ONLINE'
+           AND b.name = c.tablespace_name
+           AND a.ts# = b.ts#
+       ) d,
+       v$parameter e,
+       v$parameter f,
+       (
+       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
+              undo_block_per_sec
+         FROM v$undostat
+       ) g
+WHERE e.name = 'undo_retention'
+  AND f.name = 'db_block_size'
+;
+</code>
+==== UNDO space estimation ====
+<code sql>
+SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
+       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
+       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
+       g.undo_block_per_sec) / (1024*1024)
+      "NEEDED UNDO SIZE [MByte]"
+  FROM (
+       SELECT SUM(a.bytes) undo_size
+         FROM v$datafile a,
+              v$tablespace b,
+              dba_tablespaces c
+        WHERE c.contents = 'UNDO'
+          AND c.status = 'ONLINE'
+          AND b.name = c.tablespace_name
+          AND a.ts# = b.ts#
+       ) d,
+      v$parameter e,
+       v$parameter f,
+       (
+       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
+         undo_block_per_sec
+         FROM v$undostat
+       ) g
+ WHERE e.name = 'undo_retention'
+  AND f.name = 'db_block_size'
+;
+</code>
+==== Add space to UNDO ====
+<code sql>
+ALTER TABLESPACE undotbs_01
+ADD DATAFILE '+ASM' AUTOEXTEND ON NEXT 1G
+MAXSIZE UNLIMITED;
+</code>
+==== New UNDO ====
+<code sql>
+CREATE UNDO TABLESPACE undotbs_02
+     DATAFILE '+ASM' SIZE 2G REUSE AUTOEXTEND ON;
+</code>
+==== UNDO switch ====
+<code sql>
+ALTER SYSTEM SET undo_tablespace = TESTUNDO SCOPE=BOTH;
+</code>
+==== Auto tuned "UNDORETENTION" ====
+Si estamos en Automatic UNDO management (lo normal), el parámetro UNDO_RETENTION es IGNORADO, y se gestiona dinámicamente basado en si el tablespace es fijo o dinámico e intentando mantener siempre la máxima retención en el UNDO y el umbral de alerta.
+Para ver el auto tune:
+<code sql>
+COL begin_time format a30
+col end_time format a30
+col MAXQUERYID FORMAT A20
+SELECT to_char(begin_time, 'DD-MON-RR HH24:MI') begin_time,
+       to_char(end_time, 'DD-MON-RR HH24:MI') end_time,
+       MAXQUERYLEN,
+       MAXQUERYID,
+       MAXCONCURRENCY,
+       UNXPSTEALCNT STEALED_SEGMENTS,
+       tuned_undoretention
+FROM v$undostat
+ORDER BY end_time;
+</code>
+(más simple):
+<code sql>
+select to_char(begin_time, 'DD-MON-RR HH24:MI') begin_time,
+       to_char(end_time, 'DD-MON-RR HH24:MI') end_time,
+       tuned_undoretention
+from v$undostat
+order by end_time;
+</code>
+==== UNDO alert threshold ====
+Cambiar ''UNDOTBS1'' por el nombre del tablespace de UNDO:
+<code sql>
+declare
+    warning_operator binary_integer;
+    warning_value varchar2(60);
+    critical_operator binary_integer;
+    critical_value varchar2(60);
+    observation_period binary_integer;
+    consecutive_occurrences binary_integer;
+begin
+    dbms_server_alert.get_threshold(
+        dbms_server_alert.tablespace_pct_full,
+        warning_operator,
+        warning_value,
+        critical_operator,
+        critical_value,
+        observation_period,
+        consecutive_occurrences,
+        null,
+        dbms_server_alert.object_type_tablespace,
+        object_name => 'UNDOTBS1');
+    dbms_output.put_line('Warning operator: ' || warning_operator);
+    dbms_output.put_line('Warning value: ' || warning_value);
+    dbms_output.put_line('Critical operator: ' || critical_operator);
+    dbms_output.put_line('Critical value: ' || critical_value);
+    dbms_output.put_line('Observation_period: ' || observation_period);
+    dbms_output.put_line('Consecutive occurrences:' || consecutive_occurrences);
+end;
+/
+</code>
+y:
+<code sql>
+COL METRICS_NAME FORMAT A30;
+COL WARNING_OPERATOR FORMAT A16;
+COL WARNING_VALUE FORMAT A30;
+COL CRITICAL_OPERATOR FORMAT A16;
+COL CRITICAL_VALUE FORMAT A8;
+COL OPERATION_PERIOD FORMAT A10;
+COL CONSECUTIVE_OCCURRENCES FORMAT 9999999999;
+COL INSTANCE_NAME FORMAT A15;
+COL OBJECT_NAME FORMAT A12;
+COL OBJECT_TYPE FORMAT A10;
+COL STATUS FORMAT A10;
+SELECT * FROM DBA_THRESHOLDS ;
+</code>
+===== dNFS =====
+==== List dNFS servers ====
+<code sql>
+COL RDMAENABLE FORMAT A10
+COL SVRNAME FORMAT A30
+col DIRNAME FORMAT A120
+select * from V$DNFS_SERVERS ;
+</code>
+==== List dNFS channels ====
+<code sql>
+col path format a40
+col local format a40
+col SVRNAME format a40
+select pnum, svrname, path, local from v$dnfs_channels ;
+</code>
+==== List dNFS Files ====
+<code sql>
+COL FILENAME FORMAT A200
+select * from V$DNFS_FILES ;
+</code>
+====== [SECTION]  Users ======
+=====Listar=====
+  * Todos:
+<code sql>SELECT USERNAME, PASSWORD, PROFILE, ACCOUNT_STATUS from dba_users order by 1 ;</code>
+  * Activos:
+<code sql>SELECT USERNAME, PASSWORD, PROFILE, ACCOUNT_STATUS from dba_users WHERE ACCOUNT_STATUS LIKE 'OPEN';</code>
+=====Profiles=====
+<code sql>
+col profile format a30
+col resource_name format a50 ;
+col limit format a30
+SELECT *
+FROM DBA_PROFILES
+order by profile, resource_name ;
+</code>
+=====Ver como esta creado=====
+Esto se refiere a un "usuario" pero viene siendo lo mismo:
+<code sql>
+select dbms_metadata.get_ddl(''USER'',''usuari'') from dual;
+</code>
+=====Grants=====
+==== De sistema ====
+<code sql>
+SELECT LPAD(' ', 2*level) || granted_role "USER PRIVS"
+FROM (
+    SELECT NULL grantee, username granted_role FROM dba_users WHERE username LIKE UPPER('%&uname%')
+  UNION
+    SELECT grantee, granted_role FROM dba_role_privs
+  UNION
+    SELECT grantee, privilege FROM dba_sys_privs
+  )
+START WITH grantee IS NULL
+CONNECT BY grantee = prior granted_role
+/
+</code>
+Plain mode:
+<code sql>
+SELECT GRANTED_ROLE FROM (
+SELECT grantee, granted_role FROM dba_role_privs
+  UNION
+SELECT grantee, privilege FROM dba_sys_privs
+)
+WHERE GRANTEE='USERNAME';
+</code>
+====Sobre Objects====
+Mega query para obtener lo que sea:
+<code sql>
+COL OBJECT_NAME FORMAT A35
+COL OBJECT_TYPE FORMAT A25
+COL OWNER FORMAT A33
+COL GRANTOR FORMAT A33
+COL GRANTEE FORMAT A33
+COL PERMISSION_NAME FORMAT A50
+SELECT  OBJECT_NAME,
+        OBJECT_TYPE,
+--         OWNER,
+        GRANTOR,
+        GRANTEE,
+--         PERMISSION,
+        PERMISSION_NAME
+FROM (
+    SELECT  OBJECT.NAME OBJECT_NAME,
+            decode (OBJECT.TYPE#,
+, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE',
+, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
+, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
+, 'PACKAGE BODY', 12, 'TRIGGER', 13, 'TYPE',
+, 'TYPE BODY', 19, 'TABLE PARTITION',
+, 'INDEX PARTITION', 21, 'LOB', 22, 'LIBRARY',
+, 'DIRECTORY', 24, 'QUEUE', 28, 'JAVA SOURCE',
+, 'JAVA CLASS', 30, 'JAVA RESOURCE',
+, 'INDEXTYPE', 33, 'OPERATOR',
+, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
+, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
+, 'MATERIALIZED VIEW', 43, 'DIMENSION',
+, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
+, 'CONSUMER GROUP', 55, 'XML SCHEMA',
+, 'JAVA DATA', 57, 'EDITION', 59, 'RULE',
+, 'CAPTURE', 61, 'APPLY', 62, 'EVALUATION CONTEXT',
+, 'JOB', 67, 'PROGRAM', 68, 'JOB CLASS',
+, 'WINDOW', 72, 'SCHEDULER GROUP', 74, 'SCHEDULE',
+, 'CHAIN', 81, 'FILE GROUP', 82, 'MINING MODEL',
+, 'ASSEMBLY', 90, 'CREDENTIAL',
+, 'CUBE DIMENSION', 93, 'CUBE',
+, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS',
+, 'FILE WATCHER', 101, 'DESTINATION','UNDEFINED'
+                    ) OBJECT_TYPE,
+            U.NAME      OWNER,
+            UR.NAME     GRANTOR,
+            UE.NAME     GRANTEE,
+            OA.PRIVILEGE#  PERMISSION,
+            MIN(TPM.NAME) PERMISSION_NAME
+    FROM    sys.objauth$ oa,
+            sys."_CURRENT_EDITION_OBJ" OBJECT,
+            sys.USER$ U,
+            sys.USER$ UR,
+            sys.USER$ UE,
+            sys.table_privilege_map TPM
+    WHERE   OA.OBJ#=OBJECT.OBJ#
+    AND     OA.COL# IS NULL
+    AND     U.USER#=OBJECT.OWNER#
+    AND     oa.grantor# = ur.USER#
+    AND     oa.grantee# = ue.USER#
+    AND     TPM.PRIVILEGE = OA.PRIVILEGE#
+    GROUP BY OBJECT.NAME,U.NAME,OBJECT.TYPE#,UR.NAME,UE.NAME, OA.PRIVILEGE#
+    )
+WHERE GRANTOR='USERNAME'
+AND GRANTEE='USERNAME'
+AND OWNER='USERNAME'
+AND OBJECT_TYPE='TABLE'
+AND OBJECT_NAME='NAME'
+ORDER BY OBJECT_NAME, PERMISSION_NAME
+;
+</code>
+Aparte, los grants sobre objects se detallan en las vistas:
+  * table_privileges
+  * dba_role_privs
+De CADA ESQUEMA.
+Es decir, los que concede SYS están en su esquema, los que concede ADMINUSER están en su propio esquema.
+Hay que hacer switch a cada esquema para conocer sus grants
+  * Grants directos:
+<code SQL>
+SELECT owner, table_name, select_priv, insert_priv, delete_priv, update_priv, references_priv, alter_priv, index_priv
+  FROM table_privileges
+ WHERE grantee = '&theUser'
+ ORDER BY owner, table_name;
+</code>
+  * Grants INdirectos:
+<code sql>
+SELECT DISTINCT owner, table_name, PRIVILEGE
+  FROM dba_role_privs rp JOIN role_tab_privs rtp ON (rp.granted_role = rtp.role)
+ WHERE rp.grantee = '&theUser'
+ ORDER BY owner, table_name;
+</code>
+=====quota sobre tablespaces=====
+<code sql>
+select * from DBA_TS_QUOTAS order by TABLESPACE_NAME, USERNAME;
+</code>
+=====Alta=====
+<code sql>CREATE USER "USERNAME"
+    PROFILE "DEFAULT"
+    IDENTIFIED BY "********" DEFAULT TABLESPACE "TABLESPACE_DAT"
+    TEMPORARY TABLESPACE "TEMP"
+    ACCOUNT UNLOCK ;
+grant CONNECT, RESOURCE to "USERNAME" ;
+</code>
+=====Re-Crear usuario=====
+Si el usuario ya existe/existía y lo que queremos es reutilizar su password, primero hemos de obtener el hash del password con el siguiente procedure:
+<code sql>set serveroutput on size 200000
+declare
+    stmt varchar2(200);
+    v_old_hash user$.password%type;
+    v_new_hash user$.spare4%type;
+    v_hash varchar2(200);
+begin
+   for user_rec in (select name, password, spare4 from user$ where type#=1 and astatus in(0,1,2)) loop
+      v_old_hash := user_rec.password;
+      v_new_hash := user_rec.spare4;
+      if not ((v_old_hash is null) and (v_new_hash is null)) then
+         if (v_new_hash is null) then
+             if v_old_hash <> 'EXTERNAL' then
+                 v_hash := ''''||v_old_hash||'''';
+             else
+             goto end_loop;
+         end if;
+      end if;
+      if (v_old_hash is null) then
+          v_hash := ''''||v_new_hash||'''';
+      end if;
+      if ((v_old_hash is not null) and (v_new_hash is not null)) then
+          v_hash := ''''||v_old_hash||';'||v_new_hash||'''';
+      end if;
+          stmt := 'alter user '||user_rec.name||' identified by values'||v_hash;
+      end if;
+     dbms_output.put_line(stmt||';');
+  <<end_loop>>
+  null;
+  end loop;
+end;
+/
+</code>
+La columna ASTATUS de USER$ la he limitado a 3 valores:
+  * 0 = ACCOUNT_STATUS OPEN
+  * 1 = ACCOUNT_STATUS EXPIRED
+  * 2 = ACCOUNT_STATUS EXPIRED(GRACE)
+Y luego usarlo en la query:
+<code sql>CREATE USER "USERNAME"
+    PROFILE "DEFAULT"
+    IDENTIFIED BY VALUES '<PASSWORD>;<SPARE4>'
+    DEFAULT TABLESPACE "TABLESPACE_DAT"
+    TEMPORARY TABLESPACE "TEMP"
+    ACCOUNT UNLOCK ;
+</code>
+Podemos obtener previamente los datos necesarios (tablespace, profile...) con la query:
+<code sql>SELECT 'CREATE USER ' || USERNAME ||
+    ' IDENTIFIED BY VALUES ''' || S.PASSWORD || ';' || S.SPARE4 ||
+    ''' DEFAULT TABLESPACE ' || D.DEFAULT_TABLESPACE ||
+    ' TEMPORARY TABLESPACE  ' || D.TEMPORARY_TABLESPACE ||
+    ' PROFILE ' || D.PROFILE ||
+    ' ACCOUNT UNLOCK ; '
+    FROM DBA_USERS D, SYS.USER$ S
+    WHERE D.USER_ID=S.USER#
+    AND
+    USERNAME = 'VOXELADMIN' ;
+</code>
+Se usa el combo '<11g password hash>;<10g password hash>' por que si especificamos HASH normal o SPARE4, el otro deja de existir (en caso de que exista).
+[[http://marcel.vandewaters.nl/oracle/security/password-hashes|Más info]]
+=====Borrar=====
+<code sql>drop user USERNAME cascade ;</code>
+Cuidado con el cascade por el tema de privilegios concedidos (puede seguir el cascade a otros usuarios).
+=====Cambiar de usuario=====
+<code sql>
+ALTER SESSION SET CURRENT_SCHEMA="NOMBRE_USUARIO";
+</code>
+Entre comillas por que lo que se cambia así es el //[[http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_2012.htm#i2143260|schema]]//.
+Verificar:
+<code sql>
+select sys_context ('userenv', 'current_schema') from dual ;
+</code>
+=====Cambiar password de usuario=====
+<code sql>ALTER USER username IDENTIFIED BY "PASSWORD" ;</code>
+=====Desbloquear un usuario=====
+<code sql>alter user USERNAME account unlock;</code>
+=====Clonar un usuario=====
+Para clonar un usuario sin clonar los datos (remap desde export) se puede usar el siguiente script:
+<code sql>-- user_clone.sql
+-- Andy Barry/A
+-- 20/02/06
+-- modified by dodger
+-- 17/02/2010
+set lines 999 pages 999
+set verify off
+set feedback off
+set heading off
+select username from dba_users order by username
+/
+undefine user
+accept userid prompt 'Enter user to clone: '
+accept newuser prompt 'Enter new username: '
+accept passwd prompt 'Enter new password: '
+select username, created
+from   dba_users
+where  lower(username) = lower('&newuser')
+/
+accept poo prompt 'Continue? (ctrl-c to exit)'
+spool /tmp/user_clone_tmp.sql
+select 'create user ' || '&newuser' ||
+       ' identified by ' || '&passwd' ||
+       ' default tablespace ' || default_tablespace ||
+       ' temporary tablespace ' || temporary_tablespace || ';' "user"
+from   dba_users
+where  username = '&&userid'
+/
+select 'alter user &newuser quota '||
+       decode(max_bytes, -1, 'unlimited'
+       ,                     ceil(max_bytes / 1024 / 1024) || 'M') ||
+       ' on ' || tablespace_name || ';'
+from   dba_ts_quotas
+where  username = '&&userid'
+/
+select 'grant ' ||granted_role || ' to &newuser' ||
+       decode(admin_option, 'NO', ';', 'YES', ' with admin option;') "ROLE"
+from   dba_role_privs
+where  grantee = '&&userid'
+/
+select 'grant ' || privilege || ' to &newuser' ||
+       decode(admin_option, 'NO', ';', 'YES', ' with admin option;') "PRIV"
+from   dba_sys_privs
+where  grantee = '&&userid'
+/
+select 'grant ' || privilege || ' to &newuser' ||
+       decode(GRANTABLE, 'NO', ';', 'YES', ' with grant option;') "PRIV"
+from   dba_tab_privs
+where  grantee = '&&userid'
+/
+spool off
+undefine user
+set verify on
+set feedback on
+set heading on
+</code>
+Esto genera un script SQL "<code sql>/tmp/user_clone_tmp.sql</code>" que contiene todos los privilegios del usuario objetivo.
+Hay que tener en cuenta si queremos que el usuario tenga los datos en un tablespace diferente, para lo cual hay que cambiar el create user por supuesto.
+=====Limites=====
+====Estado====
+<code sql>
+show parameter resource_limit ;
+</code>
+====Activar====
+<code sql>alter system set RESOURCE_LIMIT=true scope=both;
+</code>
+====Crear profile con limite====
+<code sql>
+CREATE PROFILE "LIMIT" LIMIT SESSIONS_PER_USER 2;
+</code>
+y modificar el usuario:
+<code sql>
+ALTER USER USERNAME PROFILE LIMIT ;
+</code>
+====== [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 =====
+Compile an object:
+<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>
+Compile ALL objects (invalid):
+See:
+[[dba:oracle:scripts: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 objects 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 objects=====
+No existe ninguna tabla que determine el nombre de los objects, se pueden obtener (más o menos así):
+<code sql>
+SELECT OBJECT_TYPE FROM SYS.ALL_OBJECTS GROUP BY OBJECT_TYPE ;
+</code>
+=====Objects 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 | objects 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>
+===== 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>
+===== 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 objects=====
+==== 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 objects 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]]
+====== [SECTION]  Sesiones======
+=====Listar numero de sesiones=====
+<code sql>select	rpad(c.name||':',11)||
+	rpad(' current logons='||(to_number(b.sessions_current)),20)||
+	'cumulative logons='||rpad(substr(a.value,1,10),10)||
+	'highwater mark='||b.sessions_highwater Information
+from
+   v$sysstat a,
+   v$license b,
+   v$database c
+where
+   a.name = 'logons cumulative' ;
+</code>
+===== Usuarios conectados =====
+<code sql>
+col ID format a15;
+col USERNAME FORMAT A30 ;
+col OSUSER FORMAT A40 ;
+COL MACHINE FORMAT A20 WORD WRAPPED;
+COL "SID,SERIAL" FORMAT A15;
+SELECT USERNAME,
+        SID || ',' || SERIAL# "SID,SERIAL",
+        STATUS,
+        OSUSER,
+        MACHINE,
+        SQL_ID,
+        LAST_CALL_ET "Last Activity"
+from v$session
+where username is not null
+order by status desc, last_call_et desc
+/
+</code>
+===== Numero de sesiones por usuario =====
+<code sql>
+COLUMN USERNAME FORMAT A35 WORD_WRAPPED ;
+COLUMN num_of_sessions FORMAT 99999999999 ;
+SELECT ses.username username,
+        count(*) num_of_sessions
+FROM V$SESSION SES, V$PROCESS P
+WHERE SES.paddr = p.addr
+AND ses.username IS NOT NULL
+group by ses.username
+ORDER BY num_of_sessions
+/
+</code>
+=====Listar sesiones=====
+<code sql>
+COLUMN SID_SERIAL FORMAT A20 ;
+COLUMN OSPID FORMAT A7;
+COLUMN SQLID FORMAT A15 WORD_WRAPPED ;
+COLUMN USERNAME FORMAT A25 WORD_WRAPPED ;
+COLUMN OSUSER FORMAT A45 WORD_WRAPPED ;
+COLUMN HOSTNAME FORMAT A33 WORD_WRAPPED ;
+col client_info format a40 ;
+COLUMN PROGRAM FORMAT A70 WORD_WRAPPED;
+SELECT ses.sid || ',' || ses.serial# "SID_SERIAL",
+      p.spid as OSPID,
+      SES.SQL_ID SQLID,
+      SES.USERNAME USERNAME,
+      SES.OSUSER OSUSER,
+      SES.MACHINE HOSTNAME,
+      SES.PROGRAM PROGRAM,
+      SES.CLIENT_INFO
+FROM V$SESSION SES, V$PROCESS P
+WHERE SES.paddr = p.addr
+ORDER BY SES.MACHINE
+/
+</code>
+Con usuario inicializado:
+<code sql>
+COLUMN SID_SERIAL FORMAT A20 ;
+COLUMN OSPID FORMAT A7;
+COLUMN SQLID FORMAT A15 WORD_WRAPPED ;
+COLUMN USERNAME FORMAT A25 WORD_WRAPPED ;
+COLUMN OSUSER FORMAT A45 WORD_WRAPPED ;
+COLUMN HOSTNAME FORMAT A33 WORD_WRAPPED ;
+COLUMN PROGRAM FORMAT A70 WORD_WRAPPED;
+SELECT ses.sid || ',' || ses.serial# "SID_SERIAL",
+      p.spid as OSPID,
+      SES.SQL_ID SQLID,
+      SES.USERNAME USERNAME,
+      SES.OSUSER OSUSER,
+      SES.MACHINE HOSTNAME,
+      SES.PROGRAM PROGRAM,
+      SES.CLIENT_INFO
+FROM V$SESSION SES, V$PROCESS P
+WHERE SES.paddr = p.addr
+and ses.username is not null
+ORDER BY SES.MACHINE
+/
+</code>
+Más:
+<code sql>col "SID/SERIAL" format a10
+col username format a15
+col osuser format a15
+col program format a80
+select s.sid || ',' || s.serial# "SID/SERIAL", s.username, s.osuser, p.spid "OS PID", s.program
+from v$session s, v$process p
+Where s.paddr = p.addr
+order by to_number(p.spid)
+/
+</code>
+=====Listar sesiones ACTIVAS=====
+Con status=ACTIVE y SIN sqltext
+<code sql>
+set linesize 280;
+COLUMN SID_SERIAL FORMAT A20 ;
+COLUMN OSPID FORMAT A7;
+COLUMN SQLID FORMAT A15 WORD_WRAPPED ;
+COLUMN USERNAME FORMAT A35 WORD_WRAPPED ;
+COLUMN HOSTNAME FORMAT A40 WORD_WRAPPED ;
+COLUMN PROGRAM FORMAT A20 WORD_WRAPPED ;
+COLUMN EVENT FORMAT A60 WORD_WRAPPED ;
+col OSUSER format a30 ;
+COLUMN WAITCLASS HEADING 'WAITING|CLASS' FORMAT A20 ;
+COLUMN WAITINGSECS HEADING 'SECONDS|WAITING' FORMAT 99999 ;
+COLUMN QUERY HEADING 'SQL|QUERY' FORMAT A100 WORD_WRAPPED ;
+select ses.sid || ',' || ses.serial# "SID_SERIAL",
+      p.spid OSPID,
+      SES.SQL_ID SQLID,
+      SES.USERNAME USERNAME,
+      SES.OSUSER OSUSER,
+      SES.MACHINE HOSTNAME,
+      lpad(SES.PROGRAM,15) PROGRAM,
+      LPAD(WA.EVENT,30) EVENT,
+      WA.WAIT_CLASS WAITCLASS,
+      WA.SECONDS_IN_WAIT WAITINGSECS
+from V$SESSION SES, V$SQLAREA SQL, V$SESSION_WAIT WA, V$PROCESS P
+where SES.STATUS='ACTIVE'
+AND SES.SQL_ID=SQL.SQL_ID
+AND SES.SID=WA.SID
+and SES.paddr = p.addr
+/
+CLEAR COLUMNS ;
+</code>
+Con status=ACTIVE
+<code sql>
+set linesize 280;
+COLUMN SID FORMAT 99999 ;
+COLUMN SERIAL FORMAT 999999 ;
+COLUMN OSPID FORMAT A5;
+COLUMN SQLID FORMAT A15 WORD_WRAPPED ;
+COLUMN USERNAME FORMAT A20 WORD_WRAPPED ;
+COLUMN HOSTNAME FORMAT A40 WORD_WRAPPED ;
+COLUMN PROGRAM FORMAT A20 WORD_WRAPPED ;
+COLUMN EVENT FORMAT A60 WORD_WRAPPED ;
+COLUMN P1TEXT FORMAT A20 WORD_WRAPPED;
+COLUMN WAITCLASS HEADING 'WAITING|CLASS' FORMAT A20 ;
+COLUMN WAITINGSECS HEADING 'SECONDS|WAITING' FORMAT 99999 ;
+COLUMN QUERY HEADING 'SQL|QUERY' FORMAT A100 WORD_WRAPPED ;
+select SES.SID SID,
+      SES.SERIAL# SERIAL,
+      p.spid OSPID,
+      SES.SQL_ID SQLID,
+      SES.USERNAME USERNAME,
+      SES.MACHINE HOSTNAME,
+      lpad(SES.PROGRAM,15) PROGRAM,
+      LPAD(WA.EVENT,30) EVENT,
+      WA.P1TEXT P1TEXT,
+      WA.WAIT_CLASS WAITCLASS,
+      WA.SECONDS_IN_WAIT WAITINGSECS,
+      SQL.SQL_TEXT QUERY
+from V$SESSION SES, V$SQLAREA SQL, V$SESSION_WAIT WA, V$PROCESS P
+where SES.STATUS='ACTIVE'
+AND SES.SQL_ID=SQL.SQL_ID
+AND SES.SID=WA.SID
+and SES.paddr = p.addr
+/
+CLEAR COLUMNS ;
+</code>
+Mediante SQL_ID:
+<code sql>
+SET linesize 280;
+COLUMN SID_SERIAL FORMAT A20 ;
+COLUMN OSPID FORMAT A7;
+COLUMN SQLID FORMAT A15 WORD_WRAPPED ;
+COLUMN USERNAME FORMAT A20 WORD_WRAPPED ;
+COLUMN HOSTNAME FORMAT A40 WORD_WRAPPED ;
+COLUMN PROGRAM FORMAT A20 WORD_WRAPPED ;
+COLUMN EVENT FORMAT A60 WORD_WRAPPED ;
+COLUMN P1TEXT FORMAT A20 WORD_WRAPPED;
+COLUMN WAITCLASS HEADING 'WAITING|CLASS' FORMAT A20 ;
+COLUMN WAITINGSECS HEADING 'SECONDS|WAITING' FORMAT 99999 ;
+COLUMN QUERY HEADING 'SQL|QUERY' FORMAT A100 WORD_WRAPPED ;
+SELECT ses.sid || ',' || ses.serial# "SID_SERIAL",
+      p.spid OSPID,
+      SES.SQL_ID SQLID,
+      SES.USERNAME USERNAME,
+      SES.MACHINE HOSTNAME,
+      lpad(SES.PROGRAM,15) PROGRAM,
+      LPAD(WA.EVENT,30) EVENT,
+      WA.P1TEXT P1TEXT,
+      WA.WAIT_CLASS WAITCLASS,
+      WA.SECONDS_IN_WAIT WAITINGSECS,
+      SQL.SQL_TEXT QUERY
+FROM V$SESSION SES, V$SQLAREA SQL, V$SESSION_WAIT WA, V$PROCESS P
+WHERE SES.SQL_ID IS NOT NULL
+AND SES.SQL_ID=SQL.SQL_ID
+AND SES.SID=WA.SID
+AND SES.paddr = p.addr
+/
+</code>
+===== Tiempo desde la última actividad =====
+<code sql>
+SELECT username, MACHINE, last_call_et seconds, STATUS
+FROM v$session
+WHERE username IS NOT NULL
+ORDER BY last_call_et
+/
+</code>
+=====Listar sesiones de usuario=====
+<code sql>select SID,SERIAL# from V$SESSION where USERNAME='USUARIO';</code>
+=====Matar sesion=====
+<code sql>alter system kill session 'SID,SERIAL' ;
+</code>
+=====Matar todas las sesiones de un usuario=====
+<code sql>
+select 'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ''' IMMEDIATE ; '
+FROM V$SESSION
+WHERE USERNAME IN ('USERNAME1', 'USERNAMEn')
+</code>
+=====Bloqueos / locks=====
+ ==== HARD LOCKS ====
+  * **v$lock** based:
+^ Human readable ^ plain mode ^
+| <code sql>
+select 'SID ' || l1.sid || ' is blocking ->' || l2.sid blocking
+from v$lock l1, v$lock l2
+where l1.block =1 and l2.request > 0
+and l1.id1=l2.id1
+and l1.id2=l2.id2
+/
+</code> | <code sql>SELECT L1.SID, L2.SID
+FROM V$LOCK L1, V$LOCK L2
+WHERE
+   L1.BLOCK=1
+   AND L2.REQUEST > 0
+   AND L1.ID1=L2.ID1
+   AND L1.ID2=L2.ID2 ;
+</code> |
+  * **v$session** based:
+<code sql>
+col wait_class format a40
+col event format a60
+SELECT
+    sid || '.' || serial# sid_serial,
+    username,
+    osuser,
+    wait_class_id,
+    wait_class#,
+    wait_class,
+    event
+FROM
+    v$session
+WHERE
+        state = 'WAITING'
+    AND
+        wait_class = 'Concurrency';
+</code>
+==== Bloqueos de usuario (Soft locks) ====
+Formateo:
+<code sql>
+COL USERNAME FORMAT A23
+COL OSUser FORMAT A23
+COL Machine FORMAT A33
+COL mode_held FORMAT A23
+COL mode_requested FORMAT A23
+COL lock_type format A25
+COL lock_id1 format A10
+COL lock_id2 format a10
+COL object_name format a33
+col object_type format a20
+</code>
+  * Tx enqueue ready2kill:
+<code sql>
+COLUMN SID_SERIAL FORMAT A15 ;
+SELECT
+    lk.sid || ',' || se.serial# as SID_SERIAL,
+  se.username,
+  se.OSUser,
+  se.Machine,
+  DECODE(lk.TYPE, 'TX', 'Transaction', 'TM', 'DML', 'UL', 'PL/SQL User Lock', lk.TYPE) lock_type,
+  DECODE(lk.lmode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(lk.lmode)) mode_held,
+  DECODE(lk.request, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(lk.request)) mode_requested,
+  TO_CHAR(lk.id1) lock_id1,
+  TO_CHAR(lk.id2) lock_id2,
+  DECODE(block, 0, 'No', 1, 'Yes', 2, 'Global') block,
+  se.lockwait
+FROM
+  v$lock lk,
+  v$session se
+WHERE
+  lk.type  = 'TX'
+AND lk.SID = se.SID
+;
+</code>
+  * Transaction enqueue (original):
+<code sql>
+SELECT
+  lk.SID,
+  se.username,
+  se.OSUser,
+  se.Machine,
+  DECODE(lk.TYPE, 'TX', 'Transaction', 'TM', 'DML', 'UL', 'PL/SQL User Lock', lk.TYPE) lock_type,
+  DECODE(lk.lmode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(lk.lmode)) mode_held,
+  DECODE(lk.request, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(lk.request)) mode_requested,
+  TO_CHAR(lk.id1) lock_id1,
+  TO_CHAR(lk.id2) lock_id2,
+  DECODE(block, 0, 'No', 1, 'Yes', 2, 'Global') block,
+  se.lockwait
+FROM
+  v$lock lk,
+  v$session se
+WHERE
+  lk.type  = 'TX'
+AND lk.SID = se.SID
+;
+</code>
+  * DML enqueue & User supplied:
+<code sql>
+SELECT
+  lk.SID,
+  se.username,
+  se.OSUser,
+  se.Machine,
+  DECODE (lk.TYPE, 'TX', 'Transaction', 'TM', 'DML', 'UL', 'PL/SQL User Lock', lk.TYPE) lock_type,
+  DECODE (lk.lmode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR (lk.lmode)) mode_held,
+  DECODE (lk.request, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR (lk.request)) mode_requested,
+  TO_CHAR (lk.id1) lock_id1,
+  TO_CHAR (lk.id2) lock_id2,
+  ob.owner,
+  ob.object_type,
+  ob.object_name,
+  DECODE(lk.Block, 0, 'No', 1, 'Yes', 2, 'Global') block,
+  se.lockwait
+FROM
+  v$lock lk,
+  dba_objects ob,
+  v$session se
+WHERE
+  lk.TYPE IN ('TM','UL')
+AND lk.SID = se.SID
+AND lk.id1 = ob.object_id
+;
+</code>
+==== Sid,SERIAL# de los bloqueantes primarios ====
+Sesiones que inician la cadena de bloqueos:
+<code sql>
+SELECT SID || ',' || SERIAL#
+FROM V$SESSION
+WHERE SID IN (
+    SELECT l1.sid
+    FROM v$lock l1, v$lock l2
+    WHERE l1.block =1 AND l2.request > 0
+    AND l1.id1=l2.id1
+    AND l1.id2=l2.id2
+    AND l1.id1 not IN (
+        SELECT l2.sid
+        FROM v$lock l1, v$lock l2
+        WHERE l1.block =1 AND l2.request > 0
+        AND l1.id1=l2.id1
+        AND l1.id2=l2.id2
+        )
+    )
+/
+</code>
+==== Bloqueadores ====
+Extrictamente bloqueadores (sesiones que bloquean otras):
+<code sql>SELECT * from DBA_BLOCKERS ;
+</code>
+==== lock de DDLS ====
+Mirar objects que se están accediendo:
+<code sql>
+col object format a50 ;
+col type format a20 ;
+col owner format a20 ;
+select * from V$ACCESS where type LIKE 'TYPE'  ;
+</code>
+Y el propio bloqueo en sí:
+<code sql>
+COL SESSION_ID FORMAT 999999;
+COL OWNER FORMAT A20;
+COL NAME FORMAT A30;
+COL TYPE FORMAT A20;
+SELECT * FROM DBA_DDL_LOCKS ;
+</code>
+==== lock de DML ====
+Casi lo mismo que el anterior:
+<code sql>
+COL SESSION_ID FORMAT 999999;
+COL OWNER FORMAT A20;
+COL NAME FORMAT A30;
+COL TYPE FORMAT A20;
+SELECT * FROM DBA_DML_LOCKS ;
+</code>
+==== Links bloqueos====
+  * http://dbamohsin.wordpress.com/2011/07/19/t-sql-session-locks-blocks-waits/
+=====Consumo por sesiones=====
+<code sql>
+SELECT TO_CHAR(m.END_TIME,(''DD-MM-YYYY HH24:MI:SS'')) e_dtm, m.intsize_csec/100 ints, s.username usr, m.session_id sid, m.session_serial_num ssn,
+	ROUND(m.cpu) cpu100, m.physical_reads prds, m.logical_reads lrds, m.pga_memory pga, m.hard_parses hp, m.soft_parses sp, m.physical_read_pct prp,
+	m.logical_read_pct lrp
+FROM v$sessmetric m, v$session s
+WHERE (m.physical_reads > 100 OR m.cpu > 100 OR m.logical_reads > 100) AND m.session_id = s.sid  AND m.session_serial_num = s.serial#
+ORDER BY m.physical_reads DESC, m.cpu DESC, m.logical_reads DESC;
+</code>
+A partir del *SID*, podemos saber qué _query_ están lanzando mediante:
+<code sql>
+SELECT c.sid, d.piece, c.serial#, c.username, d.sql_text
+FROM v$session c, v$sqltext d
+WHERE c.sql_hash_value = d.hash_value
+and c.sid = &SID
+ORDER BY  c.sid, d.piece;
+</code>
+===== QUERYS activas =====
+<code sql>
+set feedback off
+set serveroutput on size 9999
+column username format a20
+column sql_text format a55 word_wrapped
+begin
+  for x in
+   (select username||'('||sid||','||serial#||') ospid = '|| process || ' program = ' || program username,
+    to_char(LOGON_TIME,' Day HH24:MI') logon_time,
+    to_char(sysdate,' Day HH24:MI') current_time,
+    sql_address,
+    sql_hash_value
+   from v$session
+   where status = 'ACTIVE'
+   and rawtohex(sql_address) <> '00'
+   and username is not null ) loop
+   for y in (select sql_text
+   from v$sqlarea
+   where address = x.sql_address ) loop
+   if ( y.sql_text not like '%listener.get_cmd%' and
+    y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%' ) then
+    dbms_output.put_line( '--------------------' );
+    dbms_output.put_line( x.username );
+    dbms_output.put_line( x.logon_time || ' ' || x.current_time || ' SQL#=' || x.sql_hash_value);
+    dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
+   end if;
+  end loop;
+ end loop;
+end;
+/
+</code>
+====== [SECTION]  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>
+====== [SECTION]  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
+====== [SECTION]  Performance======
+===== Hard parse =====
+  * How to Identify Hard Parse Failures (Doc ID 1353015.1)
+  * [[dba:oracle:docs:killing_hardparse|[DOC] Kill 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),
+)
+          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),
+)),
+          -1, 0,
+          ROUND (
+             (ec.VALUE - pc.VALUE) * 100 / DECODE (ec.VALUE, 0, 1, ec.VALUE),
+))
+  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,
+)
+  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 =====
+* [[.docs:awr|Automated Workload Repository (awr)]]
+===== Paralelismo de objects =====
+**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>
+====== [SECTION]  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>
+===== PL/SQL =====
+==== Executing queries with bind variables ====
+<code sql>
+declare
+   c1 NUMBER;
+   c2 NUMBER;
+   c3 NUMBER;
+--    ressult testing_binds%ROWTYPE;
+--    inttype NUMBER := 4001;
+   theSQL    VARCHAR2(2000);
+BEGIN
+    theSQL := '
+    SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel OPT_ESTIMATE(@"innerQuery", TABLE, "T_ITS", SCALE_ROWS=21196.12092) */
+    c1,
+    c2,
+    c3
+FROM
+    (
+        SELECT /*+ qb_name("innerQuery") INDEX( "T_ITS" "IT_TBUFFER_SEARCH_UX3_R") */
+            COUNT(*) AS c1,
+            4294967295 AS c2,
+            SUM(
+                CASE
+                    WHEN("T_ITS"."INTTYPE" =:b1) THEN 1
+                    ELSE 0
+                END
+            ) AS c3
+        FROM
+            ciberterminal."T_IT_TBUFFER_SEARCH" "T_ITS"
+        WHERE
+            ( "T_ITS"."INTTYPE" =:b1 )
+    ) innerquery' ;
+    execute immediate theSQL into c1, c2, c3 using 4001, 4001;
+end;
+/
+</code>
+==== very simple loop ====
+<code sql>
+DECLARE
+    RESSULT VARCHAR(100);
+BEGIN
+  FOR tname IN (
+    SELECT OBJECT_NAME FROM SYS.ALL_OBJECTS WHERE OWNER='SYS' AND OBJECT_TYPE='TABLE'
+  )
+  LOOP
+    SELECT REGEXP_SUBSTR(DDL, '.*TABLESPACE.*') INTO RESSULT FROM (SELECT DBMS_METADATA.GET_DDL('TABLE', tname.object_name, 'SYS' ) DDL FROM DUAL) ;
+    DBMS_OUTPUT.PUT_LINE(TO_CHAR(RESSULT));
+  END LOOP;
+END;
+/
+</code>
+==== logging output in pl ====
+Sometimes you want to have feedback of the PL/SQL output.
+DMBS_OUTPUT does not shows the ressult until the script finish, the wait time will make you run crazy.
+The "solution" is to use a log file and use UTL_FILE which has FLUSH function for instant feedback!
+A sample code for this will be:
+<code sql>
+DECLARE
+    Rows_count PLS_INTEGER := 0;
+    LOGFILE UTL_FILE.FILE_TYPE;
+BEGIN
+    LOGFILE := UTL_FILE.FOPEN('LOGDIR','testoutput.log','w',1024);
+    WHILE true LOOP
+        Rows_count := Rows_count + 1;
+        IF MOD(Rows_count, 10000) = 0 THEN
+            dbms_output.put_line('Commited ' || Rows_count || ' rows');
+            UTL_FILE.PUT_LINE(LOGFILE,'Commited ' || Rows_count || ' rows');
+            UTL_FILE.FFLUSH (LOGFILE);
+        END IF;
+    END LOOP;
+    COMMIT;
+    UTL_FILE.FCLOSE(LOGFILE);
+END;
+/
+</code>
+Where LOGDIR is a directory defined inside oracle.
+You will have the DBMS_OUTPUT feedback when the script finish and log feedback instantly.
+===== 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>
+====== [SECTION]  Documentos adicionales======
+=====Auditoria=====
+  * [[.howtos:audit_mini-howto|Auditing Mini-HOWTO]]
+=====DataGuard=====
+  * [[.docs:dataguard_mini-howto#status_querys|Documento]]
+=====RMAN=====
+  * [[.docs:rman_basic_querys|RMAN basic querys]]
+=====RAC Status=====
+  * [[.docs: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)