Differences

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

--- dba:oracle:oracle_sql_querys:storage_management [2022/02/11 11:36] – external edit 127.0.0.1
+++ dba:oracle:oracle_sql_querys:storage_management [2023/01/31 08:15] (current) – removed dodger
@@ Line 1: / Line 1: @@
-====== ORACLE SQL QUERIES : Storage ======
-===== Tablespaces list=====
-<code sql> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES ;</code>
-===== Datafile list with the BD 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>