Differences

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

--- oracle:oracle_sql_querys:storage_management [2018/10/10 08:16] – [En ASM] dodger
+++ oracle:oracle_sql_querys:storage_management [2018/10/10 08:23] – dodger
@@ Line 73: / Line 73: @@
-====Tablespace====
+====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>
@@ Line 146: / Line 146: @@
-==== Espacio ocupado por tablas, indexes... ====
+==== Space used by tables, indexes... ====
-Formateo de columnas:
+Column format
 <code SQL>
 COL OWNER FORMAT A10;
@@ Line 156: / Line 156: @@
 COL SUM_BYTES FORMAT 999999999999999999.9999 ;
 </code>
-  * Tablas:
+  * Tables:
 <code SQL>
 SELECT  SEGMENT_NAME,
@@ Line 181: / Line 181: @@
 </code>
-Ordenado por tamaño:
+Sort by size:
+  * Tables:
-  * Tablas:
 <code sql>
 SELECT SEGMENT_NAME, SUM_BYTES, NVL(SUM_BYTES/1024/1024,0.0) TOTAL_MB FROM
@@ Line 197: / Line 196: @@
 ;
 </code>
-  * Indices:
+  * Indixes:
 <code sql>
 SELECT SEGMENT_NAME, SUM_BYTES, NVL(SUM_BYTES/1024/1024,0.0) TOTAL_MB FROM
@@ Line 212: / Line 211: @@
 </code>
-==== Espacio REAL ocupado por una tabla ====
+==== REAL space taken by a table ====
-Basado en:
+Based on:
 How To Find The Size Of A Number Of Rows Of A Table (Doc ID 1370050.1):
@@ Line 241: / Line 240: @@
-==== Espacio/Tamaño de una tabla con LOB ====
+==== Space used by a table with LOB columns ====
 <code sql>
 SELECT segment_name, segment_type, bytes / 1024 / 1024 size_in_MB
@@ Line 261: / Line 260: @@
 ===== TEMP tablespace Management =====
-==== Espacio en el temporary tablespace ====
-<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>
 ==== TEMP tablespace datafiles ====
@@ Line 314: / Line 273: @@
     dba_temp_files;
 </code>
-==== Crear un nuevo TEMP ====
+==== 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>
-==== Añadir espacio al temp ====
+==== Add space to TEMP ====
 <code sql>
 ALTER TABLESPACE TEMP ADD TEMPFILE  '+DG_RECO_01' SIZE 2G autoextend on next 512m maxsize unlimited;
 </code>
-==== Cambiar de TEMP tablespace ====
+==== Switch TEMP tablespace ====
 <code sql>
 ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP ;
@@ Line 366: / Line 325: @@
 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>
@@ Line 371: / Line 371: @@
 =====Managing ASM=====
-====Conexión====
+==== Connection ====
-Exportar las variables habituales de oracle:
+Export vars:
 <code sql>
 export ORACLE_BASE=/opt/ora11g
@@ Line 383: / Line 383: @@
 O usar el "*.env" por supuesto
-También podemos conectar al cliente del asm:
+Asm cli/wrapper:
 <code sql>asmcmd -p</code>
-====Vistas interesantes====
+====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. |
@@ Line 397: / Line 397: @@
-====Listado de discos====
+==== Disk list ====
 <code sql>
 COL PATH FORMAT A100
@@ Line 414: / Line 414: @@
 ;
 </code>
-====Listado de Diskgroups====
+==== Diskgroup list====
 <code sql>COLUMN DISK_NAME FORMAT A25;
 COLUMN DISKGROUP FORMAT A20;
@@ Line 434: / Line 434: @@
-====Espacio en los Diskgroups====
+==== Diskgroups space====
 <code sql>
 COLUMN DISKGROUP FORMAT A20;
@@ Line 447: / Line 447: @@
 ;
 </code>
-====Información de Diskgroups====
+==== Diskgroups Info====
 <code sql>
 COL NAME FORMAT A60
@@ Line 461: / Line 461: @@
 </code>
-====Creación de Diskgroup====
+==== Diskgroup Creation ====
 Para crear el diskgroup, debemos hacer un :
 <code sql>
@@ Line 477: / Line 477: @@
 	'ORCL:PATH_DISK2';
 </code>
-====Borrar diskgroup====
+==== Diskgroup deletion====
 <code sql>
 DROP DISKGROUP 'DISKGROUP_NAME'
@@ Line 495: / Line 495: @@
 Flash recovery area
-====Tamaño destinado a la FRA====
+==== FRA reserved size ====
 <code sql>
 show parameter db_recovery;
 </code>
-==== Espacio Ocupado ====
+==== FRA space usage====
 <code sql> select * from v$flash_recovery_area_usage;
 </code>
@@ Line 513: / Line 513: @@
 </code>
 ===== UNDO =====
-==== Ver información ====
+==== Undo Info ====
 <code sql>
 COL NAME FORMAT A20;
@@ Line 522: / Line 522: @@
 </code>
-==== Espacio usado en el UNDO ====
+==== UNDO Space usage ====
 Very simple:
 <code sql>
@@ Line 553: / Line 553: @@
-==== Sesiones consumiendo UNDO ====
+==== UNDO space usage by sessions  ====
 <code sql>
 col ROLL_NAME format a30
@@ Line 604: / Line 604: @@
 </code>
-==== Espacio de UNDO necesario====
+==== UNDO space estimation ====
-En el momento de ejecutar la query:
 <code sql>
@@ Line 636: / Line 635: @@
-==== Añadir espacio al UNDO ====
+==== Add space to UNDO ====
 <code sql>
 ALTER TABLESPACE undotbs_01
@@ Line 643: / Line 642: @@
 </code>
-==== Crear un nuevo espacio de UNDO ====
+==== New UNDO ====
 <code sql>
 CREATE UNDO TABLESPACE undotbs_02
@@ Line 649: / Line 648: @@
 </code>
-==== Cambiar de UNDO ====
+==== UNDO switch ====
 <code sql>
 ALTER SYSTEM SET undo_tablespace = TESTUNDO SCOPE=BOTH;
@@ Line 682: / Line 681: @@
 </code>
-==== Umbral de alerta del UNDO ====
+==== UNDO alert threshold ====
 Cambiar ''UNDOTBS1'' por el nombre del tablespace de UNDO:
 <code sql>
@@ Line 735: / Line 734: @@
-=====dNFS=====
+===== dNFS =====
-==== Listar servidores dNFS ====
+==== List dNFS servers ====
 <code sql>
 COL RDMAENABLE FORMAT A10
@@ Line 745: / Line 744: @@
 </code>
-==== Listar canales dNFS ====
+==== List dNFS channels ====
 <code sql>
 col path format a40
@@ Line 754: / Line 753: @@
-==== Listar ficheros dNFS ====
+==== List dNFS Files ====
 <code sql>
 COL FILENAME FORMAT A200