Crear tablespaces
SQL>
CREATE TABLESPACE NOM_TBS DATAFILE
'/u01/BASEDATOS/DAT_NOM_TBS.dbf' SIZE 20M AUTOEXTEND ON NEXT 16K MAXSIZE 1241M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
Crear Tablespaces de Blockes mas pequeños 4k
1 - Seleccionamos el buffer de la SGA
SQL> show sga;
Total System Global Area 1.0122E+10 bytes
Fixed Size 2261968 bytes
Variable Size 1711279152 bytes
Database Buffers 8388608000 bytes
Redo Buffers 19443712 bytes
SQL>
SQL> select name, block_size, current_size from v$buffer_pool;
NAME BLOCK_SIZE CURRENT_SIZE
-------------------- ---------- ------------
DEFAULT 8192 8000
2 - Creamos un Tablespaces de bloke de 4 K
SQL> CREATE TABLESPACE TS_AUXILIAR DATAFILE
'/u01/BASEDEDATOS/datafile/AUXILIAR.dbf' SIZE 100M AUTOEXTEND ON NEXT 104K MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 4K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
ORA-29339: tablespace block size 4096 does not match configured block
3 - Alteramos el parametro del sistema para 4k
SQL> alter system set db_4k_cache_size = 60M;
System altered.
SQL>
4 - Creamos el tablespace de 4k
CREATE TABLESPACE TS_AUXILIAR DATAFILE
'/u01/BASEDEDATOS/datafile/AUXILIAR.dbf' SIZE 1M AUTOEXTEND ON NEXT 104K MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 4K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
tablespace creado
5 - Consultamos nuevamente los bloques
SQL> select name, block_size, current_size from v$buffer_pool;
NAME BLOCK_SIZE CURRENT_SIZE
-------------------- ---------- ------------
DEFAULT 8192 7936
DEFAULT 4096 64
6- Ahora movemos la tabla al TABLESPACE de 4k
Aumentar el tamaño de un TABLESPACES con un nuevo datafile Primero consultar el espacio físico en el servidor.
SQL>
ALTER TABLESPACE test ADD DATAFILE '/oradata/datafiles/TABLESPACE_1.dbf' SIZE 10264M AUTOEXTEND ON NEXT 500M MAXSIZE 10264M;
Tablespace altered.
Mover TABLESPACES
Primero dejar el tablespace fuera de linea
SQL> ALTER TABLESPACE test OFFLINE;
Segundo, copiamos los datafiles que conformasn el tablespace
cp /directorio origen/test.dbf /directorio destino/test.dbf’
Ponemos a apuntar el tablespace a los nuevos datafiles
SQL> ALTER TABLESPACE test RENAME datafile ‘/directorio origen/test.dbf’ TO ‘/directorio destino/test.dbf’
iniciamos nuevamente el tablespaces
SQL> ALTER TABLESPACE test ONLINE;
Para una lista de los nombres y los parámetros de almacenamiento por defecto de todos los espacios de tablas en una base de datos, utilice la siguiente
consulta en la DBA_TABLESPACES vista:
SELECT TABLESPACE_NAME "TABLESPACE",
INITIAL_EXTENT "INITIAL_EXT",
NEXT_EXTENT "NEXT_EXT",
MIN_EXTENTS "MIN_EXT",
MAX_EXTENTS "MAX_EXT",
PCT_INCREASE
DE DBA_TABLESPACES;
TABLESPACE INITIAL_EXT NEXT_EXT MIN_EXT MAX_EXT PCT_INCREASE
-------------------------------------------------- -----
RBS 1048576 1048576 2 40 0
SISTEMA 106496 106496 1 99 1
TEMP 106496 106496 1 99 0
TESTTBS 57344 16384 2 10 1
USUARIOS 57344 57344 1 99 1
Consultar el tamaño de un los TABLESPACES
SELECT / + RULE / df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT / + RULE / df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;
Tablespace UNDO
Libera el espacio de UNDO. Si es vuestro caso, podéis comprobarlo con la siguiente sentencia:
SELECT DISTINCT status, SUM (BYTES) / (1024 * 1024) AS size_mb, COUNT (*) FROM dba_undo_extents GROUP BY status
STATUS | SIZE_MB | COUNT(*)
UNEXPIRED | 54435,1875 | 56526
ACTIVE | 8,0983 | 1
EXPIRED | 3,4375 | 25
Los STATUS que pueden tener los segmentos son:
ACTIVE: son transacciones con commit todavía pendientes
EXPIRED: son transacciones commiteadas y que ya se pueden sobreescribir
UNEXPIRED: son transacciones commiteadas pero que se guardan por un tiempo para dar consistencia de lectura a los datos.
Los dos errores que nos dan son:
ORA-30036: Este se produce cuando nos quedamos sin espacio de UNDO_TBS (tenemos 96GB que son suficientes)
ORA-01555: Este se produce cuando se sobreescribe el espacio UNEXPIRED y una consulta intenta recuperarlo
Con la versión 10g de Oracle, el espacio de undo se maneja automáticamente por Oracle a partir de un parámetro inicial (UNDO_RETENTION = 900 segundos) que dice cuanto tiempo se guardan las transacciones (tiempo que permanecen en estado UNEXPIRED) y cálculos estadísticos para modificarlo en función de la carga del sistema.