This is an old revision of the document!
Table of Contents
ORACLE SQL QUERIES : Storage
Tablespaces list
SELECT TABLESPACE_NAME FROM DBA_TABLESPACES ;
Datafile list with the BD not OPEN
COL NAME FORMAT A200 SELECT * FROM v$dbfile ORDER BY 1;
List datafiles (simple)
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;
Tablespace creation
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 ;
View Tablespace DDL
SELECT dbms_metadata.get_ddl('TABLESPACE','<TABLESPACE_NAME>') FROM dual ;
Add space to Tablespace
ALTER tablespace NOMBRE_DEL_TABLESPACE ADD datafile /ruta/completa/al/datafile.dbf ;
Space Information
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 AVGROWLEN 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' ) );
Space DELTA of a table
Based on AWR tables:
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 1 DESC ;
TEMP tablespace Management
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 ;
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 :
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 /
Managing ASM
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 ;
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
ACFS
Documentación básica:
FRA
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
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 ;
dNFS
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 ;