3.2 SOBRE
LOS PARÁMETROS DE LA BASE DE DATOS
Los siguientes
scripts suministran información en referencia a los parámetros de la base de
datos.
- La siguiente sentencia suministra la información de los parámetros de la
base de datos.
SQL>select * from v$parameter;
- Desde sqlplus se puede usar el comando show para mostrar un valor de un
parámetro
SQL>show parameter <nombre del parámetro>
- El cambio de un parámetro se realiza con el siguiente comando
(Existen 3 opciones para aplicar el parámetro MEMORY, SPFILE, o BOTH)
Solamente los parámetros dinámicos se pueden aplicar en memoria.
. Parámetros “fijos”=> Son parámetros que una vez instalada la base
de datos no se pueden volver a modificar / configurar. El juego de caracteres
es un claro ejemplo.
. Parámetros Estáticos => Son parámetros que se pueden
modificar, pero su modificación implica cerrar la base de datos y volverla a
abrir para que los lea del fichero y pueda realizar el cambio.
. Parámetros Dinámicos=> Son parámetros cuyo valor se
puede cambiar sin necesidad de cerrar la base de datos a diferencia de los
estáticos.
SQL> alter system set NOMBRE_PARAMETRO=VALOR scope=OPCION;
3.3 TABLESPACES
Los siguientes scripts suministran información en el caso que sea
necesario modificar la estructura de un tablespace.
PARA TABLESPACE DE DATOS.
- El siguiente script suministra el nombre del tablespace, la ruta de
cada datafile que lo confirma y el tamaño de cada datafile en MB.
SQL>select TABLESPACE_NAME, FILE_NAME,
BYTES/1024/1024 TAMANOMB
FROM
DBA_data_FILES D
where
TABLESPACE_NAME='SYSTEM';
- El siguiente script permite modificar el tamaño del datafile. Se debe
ingresar la ruta del datafile y el nuevo tamaño en Mb.
ALTER DATABASE DATAFILE
'/rman/data/rman/system01.dbf' RESIZE 800M;
- El siguiente script permite adicionar un datafile.
Se debe ingresar el nombre del tablespace, la ruta destino del datafile
y el tamaño de creación en MB.
ALTER TABLESPACE LOGOS ADD dataFILE
'/Datamart/oradata/datamart/DATAMART_TEMP02.DBF' SIZE 2000M;
- El script permite eliminar un Datafile. Se debe ingresar el nombre del tablespace y la ruta del datafile que se
va a eliminar
SQL>alter tablespace PROVEEDORES_INDX_32K drop datafile
'/backup/CENPAGOS/proveedo_indx_32k_80.dbf';
PARA TABLESPACE TEMPORALES.
- El siguiente script suministra el nombre del tablespace, la ruta de
cada tempfile que lo confirma y el tamaño de cada tempfile en MB.
SQL>select TABLESPACE_NAME, FILE_NAME,
BYTES/1024/1024 TAMANOMB
FROM
DBA_TEMP_FILES D
where
TABLESPACE_NAME='TEMP';
- El siguiente script permite modificar el tamaño del tempfile. Se debe
ingresar la ruta del tempfile y el nuevo tamaño en Mb.
SQL>ALTER DATABASE TEMPFILE
'/FIDELIUS/temp/temp03.dbf' RESIZE 2000M;
- El siguiente script permite adicionar un tempfile.
Se debe ingresar el nombre del tablespace, la ruta destino del tempfile
y el tamaño de creación en MB.
SQL> ALTER TABLESPACE TEMP ADD tempFILE
'/FIDELIUS/temp/temp03.dbf' SIZE 500M;
- El script permite eliminar un Tempfile.
Se debe ingresar el nombre del tablespace y la ruta del datafile que se
va a eliminar
SQL>alter tablespace TEMP drop tempfile
'/backup//FIDELIUS/temp/temp03.dbf';
- Las siguientes sentencias dinámicas genera el script para poner
AUTOEXTEND en ‘YES’ o ‘NO’ según la necesidad.
SQL>select 'alter database datafile '|| ''''||
file_name|| '''' || ' autoextend off;'
from dba_data_files
where autoextensible='YES';
SQL>select 'alter database datafile '|| ''''||
file_name|| '''' || ' autoextend off;'
from DBA_TEMP_FILES
where autoextensible='NO';
- El siguiente script genera un informe grafico de todos los TABLESPACES
asociados a la base de datos. (Ejecutándolo desde sql-developer)
SELECT nvl(name,'=== TOTAL for DB ===')
"Tablespace"
,
TO_CHAR(SUM(mbytes),'999,999,999.99') "Total MB" ,
TO_CHAR(SUM(used),'999,999,999.99')
"Used MB"
,
TO_CHAR(SUM(free),'999,999,999.99')
"Free MB" ,
ROUND(AVG(pct_used),2)
"%Used"
,
ROUND(AVG(pct_free),2)
"%Free"
,
'SQLDEV:GAUGE:0:100:0:0:'
|| AVG(pct_used) AS "Tablespace
Utilization" ,
TO_CHAR(SUM(new_sz),'999,999,999.99')
"Expected Size" ,
TO_CHAR(SUM(add_sz),'999,999,999.99')
"Add MB for desired %"
FROM
(SELECT
name
,
ROUND(mbytes,2)
mbytes ,
ROUND(used,2)
used ,
ROUND(free,2)
free ,
ROUND(pct_used,2) pct_used,
ROUND(pct_free,2) pct_free,
ROUND(
CASE
WHEN ( ((100 *used)/
trim(:desired_pct))-mbytes < 0)
THEN mbytes
ELSE ((100*used)/
trim(:desired_pct))
END,2) new_sz,
ROUND(
CASE
WHEN ( (((100
*used)/ trim(:desired_pct))-mbytes) < 0 )
THEN 0
ELSE (((100 *used)/
trim(:desired_pct))-mbytes)
END,2) add_sz
FROM
(SELECT NVL(b.tablespace_name,
NVL(a.tablespace_name,'UNKOWN')) name,
mbytes_alloc
Mbytes
,
mbytes_alloc-NVL(mbytes_free,0)
used
,
NVL(mbytes_free,0)
free
,
((mbytes_alloc-NVL(mbytes_free,0))/ mbytes_alloc)*100
pct_used ,
100
-(((mbytes_alloc-NVL(mbytes_free,0))/ mbytes_alloc)*100) pct_free
FROM
(SELECT
SUM(bytes)/1024/1024 Mbytes_free,
MAX(bytes) /1024/1024 largest ,
tablespace_name
FROM dba_free_space
GROUP BY tablespace_name
)
a
,
(SELECT
SUM(bytes)/1024/1024 Mbytes_alloc,
SUM(maxbytes) /1024/1024 Mbytes_max ,
tablespace_name
FROM sys.dba_data_files
GROUP BY tablespace_name
UNION ALL
SELECT
SUM(bytes)/1024/1024 Mbytes_alloc,
SUM(maxbytes) /1024/1024 Mbytes_max ,
tablespace_name
FROM dba_temp_files
GROUP BY tablespace_name
) b
WHERE
a.tablespace_name (+) = b.tablespace_name
)
)
GROUP BY rollup (name)
ORDER BY
CASE
WHEN name IS NULL
THEN 200
ELSE ROUND(AVG(PCT_FREE),2)
END ASC;
· El siguiente script
nos permite ver el estado actual de los tablespace y genera una proyección de
crecimiento en una hora, en un dia, una semana y un mes, determinando un status
en el cual se encuentra el tablespace
SQL>select tsname,
round(tablespace_size*t2.block_size/
1024/1024,2) TSize,
round(tablespace_usedsize*t2.block_size/1024/1024,2)
TUsed,
round((tablespace_size-tablespace_usedsize)*t2.block_size/1024/1024,2)
TFree,
round(val1*t2.block_size/1024/1024,2)
"Dif_1hora",
round(val2*t2.block_size/1024/1024,2)
"Dif_1dia",
round(val3*t2.block_size/1024/1024,2)
"Dif_1semana",
round(val4*t2.block_size/1024/1024,2)
"Dif_1mes",
round((tablespace_usedsize/tablespace_size)*100)||'%'
"%Used",
round(((tablespace_usedsize+val3)/tablespace_size)*100)||'%'
"%Proy_1s",
round(((tablespace_usedsize+val4)/tablespace_size)*100)||'%'
"%Proy_1m",
case when ((((tablespace_usedsize+val3)/tablespace_size)*100
< 80) and
(((tablespace_usedsize+val4)/tablespace_size)*100 < 80)) then 'NORMAL'
when
((((tablespace_usedsize+val3)/tablespace_size)*100 between 80 and 90)
or
(((tablespace_usedsize+val4)/tablespace_size)*100 between 80 and 90))
then 'WARNING'
else 'CRITICAL' end STATUS
from
(select distinct tsname,
rtime,
tablespace_size,
tablespace_usedsize,
tablespace_usedsize-first_value(tablespace_usedsize)
over (partition by tablespace_id order by rtime
rows 1 preceding) val1,
tablespace_usedsize-first_value(tablespace_usedsize)
over (partition by tablespace_id order by rtime
rows 24 preceding) val2,
tablespace_usedsize-first_value(tablespace_usedsize)
over (partition by tablespace_id order by rtime
rows 168 preceding) val3,
tablespace_usedsize-first_value(tablespace_usedsize)
over (partition by tablespace_id order by rtime
rows 720 preceding) val4
from (select t1.tablespace_size, t1.snap_id,
t1.rtime,t1.tablespace_id,
t1.tablespace_usedsize-nvl(t3.space,0) tablespace_usedsize
from
dba_hist_tbspc_space_usage t1,
dba_hist_tablespace_stat t2,
(select ts_name,sum(space) space
from recyclebin group by ts_name) t3
where t1.tablespace_id =
t2.ts#
and t1.snap_id
= t2.snap_id
and t2.tsname
= t3.ts_name (+)) t1,
dba_hist_tablespace_stat t2
where t1.tablespace_id = t2.ts#
and t1.snap_id = t2.snap_id) t1,
dba_tablespaces t2
where t1.tsname = t2.tablespace_name
and rtime = (select max(rtime) from
dba_hist_tbspc_space_usage)
and t2.contents = 'PERMANENT'
order by "Dif_1h" desc,"Dif_1d"
desc,"Dif_1s" desc, "Dif_1m" desc;
3.4 UBICACIÓN DE FICHEROS
· LOCALIZAR UBICACIÓN
Y NOMBRE DEL FICHERO SPFILE
Como el fichero de parámetros puede haberse cambiado de lugar, se puede
localizar de la siguiente manera.
SQL>SELECT value FROM v$system_parameter WHERE
name = 'spfile';
· LOCALIZAR UBICACIÓN
Y NOMBRE DE LOS FICHEROS DE CONTROL
Como el fichero de parámetros puede haberse cambiado de lugar, se puede
localizar de la siguiente manera
SQL>SELECT value FROM v$system_parameter WHERE
name = 'control_files';
· TODOS LOS FICHEROS
DE DATOS Y SU UBICACIÓN
SQL>SELECT * FROM V$DATAFILE;
· FICHEROS TEMPORALES
SQL>SELECT * FROM V$TEMPFILE;
3 3.5 MODO RESTRINGIDO
· Poner en modo
restringido una instancia.
- Verificar el estado
actual de la base de datos:
SQL> select logins from v$instance;
LOGINS
----------
RESTRICTED
- Usar el comando
STARTUP para restringir el acceso a la base de datos:
SQL>STARTUP RESTRICT
- Usar el comando
ALTER SYSTEM para colocar una instancia en modo restringido:
SQL>ALTER SYSTEM ENABLE RESTRICTED SESSION;
- Usar el comando
para quitar el modo restringido de una instancia:
SQL>alter system disable restricted session;
- Habilitar un
usuario para que ingrese a una base de datos o instancia que se encuentra en
modo restringido (cualquiera de las dos sentencias).
SQL> grant restricted session to USUARIO;
SQL> grant create session to USUARIO;
- Para REVOKAR el
privilegio de acceso de un usuario en el modo restringido se ejecuta el
siguiente comando:
SQL>REVOKE RESTRICTED SESSION FROM
"usuario";
3.6 PARÁMETRO
UTL_FILE_DIR
· CAMBIAR O MODIFICAR
EL PARAMETRO UTL_FILE_DIR
- Compruebe el valor
del parámetro actualmente.
A) SQL> select name,value from
v$parameter where name='utl_file_dir';
B) SQL> sho parameter utl_file_dir
- Compruebo que tipo de parámetro es utl_file_dir para ver si lo puedo
modificar en caliente o deberé bajar la base de datos
SQL> col
NAME format a50
col
ISSYS_MODIFIABLE format a20;
SELECT NAME,
ISSYS_MODIFIABLE FROM V$PARAMETER
WHERE
name='utl_file_dir' ORDER BY 1;
- FALSE: Indica que
obligatoriamente la base de datos debe bajarse para poder efectuar el cambio.
Por tanto, se trata de un parámetro estático y se puede modificar, pero su
modificación implica cerrar la base de datos y volverla a abrir para que los
lea del fichero de inicialización y pueda realizar el cambio. Por eso en scope
ponemos spfile y no both.
- Cambiar el Nuevo
valor al parámetro.
SQL> alter system set
utl_file_dir='<directorio_o_ruta>' scope=spfile;
Reiniciar la base de datos.
Validar el cambio del valor del parámetro.
- De ser necesario
agregar varias rutas al parámetro se separa cada ruta por una COMA(,)
SQL>alter system set utl_file_dir='<directorio_o_ruta>',
'<directorio_o_ruta>', '<directorio_o_ruta>'
scope=spfile
- Para usar paquete
UTL_FILE, que es propiedad de SYS, tienes que conceder el privilegio EXECUTE
para el usuario. Dale privilegio EXECUTE para el usuario requerido o público
(todos los usuarios) de la siguiente manera:
SQL>grant execute on UTL_FILE to public;
3.7
CODIGO DDL PARA OBJETOS
· A veces es
necesarios generar el código DDL para crear un objeto de un esquema especifico,
para eso Oracle tiene la función dbms_metadata.get_ddl la cual permite extraer
la estructura DDL para realizar la creación de un objeto.
A la función se le debe incluir el tipo de objeto, nombre del objeto y
Esquema al cual pertenece.
SQL>select
dbms_metadata.get_ddl('TABLE','AFILIACIONES','UXXIAC') from dual;
" CREATE TABLE "UXXIAC"."AFILIACIONES"
( "AFI_IDENTIFICADOR"
VARCHAR2(8) NOT NULL ENABLE,
"AFI_LETRA" VARCHAR2(1) NOT NULL ENABLE, "AFI_TIDE_CODIGO"
VARCHAR2(1),
"AFI_APELLIDOS" VARCHAR2(50), "AFI_NOMBRE"
VARCHAR2(30),
"AFI_SIGLA_VIA" VARCHAR2(2), "AFI_DOMICILIO"
VARCHAR2(50), "AFI_NUMERO"
NUMBER(5,0), "AFI_AMPLIACION"
VARCHAR2(25),
"AFI_PUEBLO" NUMBER(5,0), "AFI_LOCAL_EXTRANJ"
VARCHAR2(40),
"AFI_PAIS_EXTRANJ" VARCHAR2(3), "AFI_CODIGO_POSTAL"
VARCHAR2(5),
"AFI_TELEFONO" VARCHAR2(14), "AFI_FECHA_NAC" DATE, "AFI_PUEBLO_NAC"
NUMBER(5,0),
"AFI_LUGAR_NAC" VARCHAR2(25), "AFI_PAIS_NAC"
VARCHAR2(3),
"AFI_SEXO" VARCHAR2(1), "AFI_NACIONALIDAD"
VARCHAR2(3), "AFI_RELACIONADO"
VARCHAR2(1) ) SEGMENT CREATION
DEFERRED PCTFREE 10 PCTUSED 40 INITRANS
1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE( INITIAL 131072 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT) TABLESPACE
"UXXIAC_DAT_128K" "
· El siguiente script
es dinámico y me permite generar las sentencias para la function
dbms_metadata.get_ddl para cada table que pertenezca a un esquema específico.
select 'select dbms_metadata.get_ddl(' || '''' || 'TABLE' || '''' || ','
|| '''' || table_name || '''' || ',' || '''' || owner || '''' || ') from dual;'
--owner,table_name,tablespace_name, num_rows
from dba_tables
where owner='<nombre esquema>'
and num_rows>0;
· El siguiente script
dinámico permite generar las sentencias para la función dbms_metadata.get_ddl
para cada índice que pertenezca
a un Esquema especifico.
SQL>select 'select dbms_metadata.get_ddl(' || '''' || 'INDEX' || ''''
|| ',' || '''' || i.index_name || '''' || ',' || '''' || i.owner || '''' || ')
from dual;'
--owner,table_name
from dba_tables t, dba_indexes i
where t.owner='RSNWEB'
and t.num_rows>0
and i.owner=t.owner
and i.TABLE_NAME=t.table_name;
· El siguiente script
dinámico permite generar las sentencias para conceder permisos de GRANT sobre
todas las tablas de un esquema específico.
SQL>SELECT 'GRANT ALL ON
'||OWNER||'.'||OBJECT_NAME||' TO <USUARIO>;' FROM ALL_OBJECTS WHERE OWNER
='<ESQUEMA>' AND OBJECT_TYPE='TABLE';
· El siguiente script
dinamico permite generar las sentencias para conceder permisos de GRANT sobre
todos los objetos diferentes a tablas de un esquema específico (1)
SQL>SELECT 'GRANT ALL ON '||OWNER||'.'||TABLE_NAME||'
TO CONSULTA_PRU;' from dba_TABLES where owner='UXXIAC'
union
SELECT 'GRANT select, insert, update, delete ON
'||OWNER||'.'||OBJECT_NAME||' TO CONSULTA_PRU;' from dba_objects where
owner='UXXIAC' and OBJECT_TYPE ='VIEW'
union
SELECT 'GRANT EXECUTE ON
'||OWNER||'.'||OBJECT_NAME||' TO CONSULTA_PRU;' from dba_objects where
owner='UXXIAC' and OBJECT_TYPE ='PROCEDURE';
· El siguiente script
dinámico permite generar las sentencias para conceder permisos de GRANT sobre
todos los objetos diferentes a tablas de un esquema específico(2).
SQL>SELECT 'GRANT EXECUTE ON
'||OWNER||'.'||OBJECT_NAME||' TO <USUARIO>;' FROM ALL_OBJECTS WHERE OWNER
='<ESQUEMA>' AND OBJECT_TYPE='<TIPO DE OBJETO>';
3.8 ALGO DE TAMAÑO Y VOLUMEN
· El siguiente scritp
determina el tamaño ocupado por la bases de datos.
SQL>SELECT SUM(BYTES)/1024/1024 MB FROM
DBA_EXTENTS;
· El siguiente script
determina el tamaño de los ficheros de datos de la base de datos
SQL>SELECT SUM(bytes)/1024/1024 MB FROM
dba_data_files;
· El siguiente script
determina el tamaño por una tabla concreta sin incluir los índices de la misma.
SQL>SELECT SUM(bytes)/1024/1024 MB FROM
user_segments WHERE segment_type='TABLE' AND segment_name='NOMBRETABLA';
· El siguiente script
determina el tamaño por una tabla concreta incluyendo los índices de la misma
SQL> SELECT SUM(bytes)/1024/1024
Table_Allocation_MB
FROM user_segments
WHERE segment_type in ('TABLE','INDEX')
AND(segment_name='NOMBRETABLA'
OR
segment_name IN (SELECT index_name
FROM user_indexes
WHERE
table_name='NOMBRETABLA'));
· El siguiente script
determina el tamaño ocupado por una columna de una tabla.
SQL>SELECT
SUM(vsize('Nombre_Columna'))/1024/1024 MB FROM Nombre_Tabla;
· El siguiente script
determina el tamaño o espacio ocupado por usuario
SQL>SELECT owner, SUM(BYTES)/1024/1024 FROM
DBA_EXTENTS MB GROUP BY owner;
· El siguiente script
determina el tamaño o espacio ocupado por los diferentes segmentos(Tablas,
índices, undo, rollback, cluster,…)
SQL>SELECT SEGMENT_TYPE, SUM(BYTES)/1024/1024
FROM DBA_EXTENTS MB GROUP BY SEGMENT_TYPE;
· El siguiente script
determina el tamaño o ocupación de todos los objetos de la base de datos, los
ordena de mayor a menor espacio.
SQL> SELECT SEGMENT_NAME, SUM(BYTES)/1024/1024
FROM DBA_EXTENTS MB GROUP BY SEGMENT_NAME ORDER BY 2 DESC;
· Este script te
permite saber la cantidad de objetos que tiene un esquema determinado en una
instancia.
SQL>select owner, object_type, count(*) TABLAS
from dba_objects
where owner='NOMBRE_ESQUEMA'
group by owner, object_type
order by 3 desc;
· Este script te
permite saber el tamaño de las tablas e índices que tiene un esquema
determinado
SQL>select owner, segment_type, count(*) CANT,
sum(bytes/1024/1024) MB from dba_segments
where owner='NOMBRE_ESQUEMA'
group by owner, segment_type
order by 4 desc;
· Este script te
permite la cantidad de objetos por esquema que están en diferentes tablespaces,
esto es útil para hacer mantención de espacios en una instancia.
SQL>select owner, tablespace_name,segment_type,
count(segment_type)
from dba_segments
group by tablespace_name,owner,segment_type
order by 1 asc
3.9 FECHA DEL ULTIMO REINICIO DE LA BASE DE DATOS
· La siguiente
sentencia nos permite ver la fecha y hora de cuándo fue la última vez que se
reinició la base de datos.
SQL>select status Estado, to_char(startup_time, 'HH24:MI DD-MON-YY')
"Hora y Fecha de Arranque", instance_name Instancia from v$instance;
3.10
EL CATÁLOGO DE ORACLE
· El catálogo de Oracle es un conjunto
de tablas y vistas que contienen la definición de la estructura lógica y física
de la base de datos.
Esto incluye los objetos existentes, la situación de los datafiles, la
configuración de los usuarios, etc.
Los nombres de las tablas de catálogo sigue la siguiente nomenclatura de
prefijos:
DBA_ Objetos con información de administrador. Sólo accesibles por
usuarios DBA.
USER_ Objetos con información del usuario con el que estamos conectados
(proporcionan menos información que los objetos DBA_ALL_ Objetos con
información de todos los objetos en base de datos.
V_$ ó V$ Tablas virtuales
· Existe una tabla de
catálogo para cada tipo de objeto posible (en plural).
TABLES, VIEWS, SEQUENCES, TABLESPACES...
Sabiendo esto podemos ver algunos ejemplos:
DBA_TABLES Información para administradores de las tablas en base de
datos.
USER_VIEWS Información de las vistas creadas por el usuario desde el que
accedemos.
ALL_SEQUENCES Información de todas las secuencias existentes en base de
datos.
DBA_TABLESPACES Información de administración sobre los tablespaces.
USER_TAB_COLUMNS Todas las columnas de tabla en el usuario activo.
Los objetos de catálogo pueden estar relacionados entre ellos. Por
ejemplo, el objeto ALL_TABLES guarda una relación 1-N con el objeto
ALL_TAB_COLUMNS: Una tabla tiene N columnas.
Si se quiere que todos los usuarios tengan algún tipo de acceso a un
objeto, podemos dar ese privilegio al rol PUBLIC.
El catálogo público son aquellas tablas (USER_ y ALL_) que son
accesibles por todos los usuarios. Normalmente dan información sobre los
objetos creados en la base de datos.
El catálogo de sistema (DBA_ y V_$) es accesible sólo desde usuarios DBA
y contiene tanto información de objetos en base de datos, como información
específica de la base de datos en sí (versión, parámetros, procesos
ejecutándose...)
Ciertos datos del catálogo de Oracle debe actualizarse para el buen
funcionamiento de la base de datos (ANALYZE).
Disponemos de un par de tablas en el catálogo público que sirven al
analista y al programador para crear comentarios sobre tablas y columnas.
ALL_TAB_COMMENTS Contiene los comentarios para tablas y vistas.
ALL_COL_COMMENTS Contiene los comentarios para las columnas de tablas y
vistas.
Estas tablas se mantienen por medio de la sentencia COMMENT.
Con toda esta información en la base de datos, podemos escribir
procedimientos o scripts SQL para generar documentación o generar a su vez
scripts.
