ORACLE SQL QUERIES : Storage

 SELECT TABLESPACE_NAME FROM DBA_TABLESPACES ;
COL NAME FORMAT A200
SELECT * FROM v$dbfile ORDER BY 1;
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
;

or super simple:

SELECT file_name FROM dba_data_files ORDER BY 1;

Without ASM

CREATE SMALLFILE 
    TABLESPACE "NOMBRE_DEL_TABLESPACE_DAT"
    DATAFILE '/ruta/completa/al/datafile.dbf'
    SIZE 500M AUTOEXTEND 
    ON MAXSIZE 5000M
    ;

With ASM

CREATE SMALLFILE 
    TABLESPACE "NOMBRE_DEL_TABLESPACE_DAT"
    DATAFILE '+ASMNAME'
    SIZE 500M AUTOEXTEND 
    ON MAXSIZE 5000M
   ;
SELECT dbms_metadata.get_ddl('TABLESPACE','<TABLESPACE_NAME>') FROM dual ;
ALTER tablespace NOMBRE_DEL_TABLESPACE 
      ADD datafile /ruta/completa/al/datafile.dbf
      ;

ASM Diskgroup information

SELECT name, total_mb, free_mb FROM v$asm_diskgroup_stat ;

o

SELECT  name, group_number, disk_number, total_mb, free_mb
FROM    v$asm_disk
ORDER   BY group_number
/

Tablespace information

Mi query, tiene en cuenta si el tablespace tiene autoextend o es de tamaño fijo para computar el espacio REAL:

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)
;

Query “normal”:

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
/

Space used by tables, indexes...

Column format

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 ;
  • Tables:
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
;
  • Indexes:
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
;

Sort by size:

  • Tables:
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
;
  • Indixes:
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
;

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:

ANALYZE TABLE <TABLE_NAME> COMPUTE STATISTICS ;

Necesitamos también el tamaño de bloque:

 SHOW PARAMETER DB_BLOCK_SIZE
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>' ;

Space used by a table with LOB columns

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'
            )
);

TEMP tablespace datafiles

col file_name format a150
 
SELECT
    file_id,
    file_name,
    tablespace_name,
    STATUS
FROM
    dba_temp_files;

New TEMP

CREATE TEMPORARY TABLESPACE TEMP tempfile '/u02/oradata/DBTEST/datafile/temp_01.dbf' SIZE 1G AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED ;

Add space to TEMP

ALTER TABLESPACE TEMP ADD TEMPFILE  '+DG_RECO_01' SIZE 2G autoextend ON NEXT 512m maxsize unlimited;

Switch TEMP tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP ;

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 :

SELECT TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS FROM v$sort_segment; 

And overall:

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;

Space used by TEMP

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
/

Resumido en MB:

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
/

Connection

Export vars:

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'

O usar el “*.env” por supuesto

Asm cli/wrapper:

asmcmd -p

Main views

V$ASM_ALIAS
Displays a row for each alias present in every disk group mounted by the ASM instance.
V$ASM_CLIENT
Displays a row for each database instance using a disk group managed by the ASM instance.
V$ASM_DISK
Displays a row for each disk discovered by the ASM instance, including disks which are not part of any disk group.
V$ASM_DISKGROUP
Displays a row for each disk group discovered by the ASM instance.
V$ASM_DISK_STAT

&

V$ASM_DISKGROUP_STAT
Same as before but 'DON'T MAKE A DISK DISCOVER CALL' (very recommended on 10.1)
V$ASM_FILE
Displays a row for each file for each disk group mounted by the ASM instance.
V$ASM_OPERATION
Displays a row for each file for each long running operation executing in the ASM instance.
V$ASM_TEMPLATE
Displays a row for each template present in each disk group mounted by the ASM instance.

Disk list

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
;

Diskgroup list

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 ;
CLEAR COLUMNS;

Diskgroups space

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
;

Diskgroups Info

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
;

Diskgroup Creation

Para crear el diskgroup, debemos hacer un :

/etc/init.d/oracleasm createdisk NOMBRE_DEL_DISCO DISPOSITIVO

El dispositivo tiene que ser una partición de disco (no directamente un sdX)

Después, listamos el path desde ASM:

SELECT NAME, PATH  FROM V$ASM_DISK ;

Y con el path hacemos la query de create:

CREATE diskgroup DATA external redundancy disk
	'ORCL:PATH_DISK1',
	'ORCL:PATH_DISK2';

Diskgroup deletion

DROP DISKGROUP 'DISKGROUP_NAME'

O

DROP DISKGROUP 'DISKGROUP_NAME' INCLUDING CONTENTS

Flash recovery area

FRA reserved size

SHOW parameter db_recovery;

FRA space usage

 SELECT * FROM v$flash_recovery_area_usage;

y

COL NAME FORMAT A100;
SELECT NAME, 
       SPACE_LIMIT/1024/1024, 
       SPACE_USED/1024/1024, 
       NUMBER_OF_FILES  
FROM V$RECOVERY_FILE_DEST ;

Undo Info

COL NAME FORMAT A20;
COL VALUE FORMAT A60;
SELECT name, VALUE
FROM gv$parameter
WHERE name LIKE '%undo%';

UNDO Space usage

Very simple:

SELECT tablespace_name, STATUS, SUM(blocks) * 8192/1024/1024/1024 GB 
FROM dba_undo_extents GROUP BY tablespace_name, STATUS;
SELECT  TOTALSPACE.TOTAL TOTAL_SPACE,
        NVL(USEDSPACE.USED,0.0) USED_SPACE,
        100 - 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
;

UNDO space usage by sessions

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
/

Optimal UNDO retention

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'
;

UNDO space estimation

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'
;

Add space to UNDO

ALTER TABLESPACE undotbs_01
ADD DATAFILE '+ASM' AUTOEXTEND ON NEXT 1G 
MAXSIZE UNLIMITED;

New UNDO

CREATE UNDO TABLESPACE undotbs_02
     DATAFILE '+ASM' SIZE 2G REUSE AUTOEXTEND ON;

UNDO switch

ALTER SYSTEM SET undo_tablespace = TESTUNDO SCOPE=BOTH;

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:

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;

(más simple):

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;

UNDO alert threshold

Cambiar UNDOTBS1 por el nombre del tablespace de UNDO:

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;
/

y:

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 ;

List dNFS servers

COL RDMAENABLE FORMAT A10
COL SVRNAME FORMAT A30
col DIRNAME FORMAT A120
SELECT * FROM V$DNFS_SERVERS ;

List dNFS channels

col path format a40
col LOCAL format a40
col SVRNAME format a40
SELECT pnum, svrname, path, LOCAL FROM v$dnfs_channels ;

List dNFS Files

COL FILENAME FORMAT A200
SELECT * FROM V$DNFS_FILES ;
  • oracle/oracle_sql_querys/storage_management.txt
  • Last modified: 2018/10/10 10:23
  • by dodger