Verifica Undo TBS

#################################################
#     Para ver o tamanho disponivel da UNDO     #
#################################################

SELECT (UR * (UPS * DBS)) + (DBS * 24) Bytes
FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM v$undostat),
(select block_size as DBS from dba_tablespaces where tablespace_name=
(select value from v$parameter where name = 'undo_tablespace'));



##########################################################
#   para saber se o gerenciamento do undo é automático   #
##########################################################

SHOW PARAMETER UNDO_MANAGEMENT


##########################################################
#       para saber quanto tempo de retenção o undo       #
#      possuí/parametro para ajudar na definição do      #
#               tamanho do tablespace.                   #
##########################################################

SHOW PARAMETER UNDO_TABLESPACE


##########################################################
#             CRIANDO UM TABLESPACE DE UNDO              #
##########################################################

CREATE UNDO TABLESPACE UNDO2
DATAFILE 'C:\oracle\product\10.2.0\oradata\dbter\UNDO02.DBF'
SIZE 20M;

select file_name, tablespace_name from dba_data_files where tablespace_name='UNDO2';


############################################################
# PARA ADICIONAR UM ARQUIVO DE DADOS AO TABLESPACE DE UNDO #
############################################################

ALTER TABLESPACE UNDO2
ADD DATAFILE 'C:\oracle\product\10.2.0\oradata\dbter\UNDO02A.DBF' SIZE 30M AUTOEXTEND ON;

select file_name, tablespace_name from dba_data_files where tablespace_name='UNDO2';


############################################################
#         PARA TORNAR O TABLESPACE UNDO PADRÃO             #
############################################################
SHOW PARAMETER UNDO_TABLESPACE;

ALTER SYSTEM SET UNDO_TABLESPACE=UNDO2;

SHOW PARAMETER UNDO_TABLESPACE;

DROP TABLESPACE UNDOTBS1 including contents and datafiles;

select * from v$tablespace;

###################################################
#               OUTROS PARAMETROS                 #
###################################################

SHOW PARAMETER UNDO


############################################################
#           PARA SABER AS ESTATÍSTICAS DO UNDO             #
############################################################

alter session set nls_date_format = 'DD/MM/YYYY HH24:MI:SS';


############################################################
#     para ver o que aconteceu no tablespace de undo       #
############################################################

create table scott.xpto2 (id number);
insert into scott.xpto2 values (1);

insert into scott.xpto2
select * from scott.xpto2;insert into xpto2 values (1);

insert into scott.xpto2
select * from scott.xpto2;insert into xpto2 values (1);

insert into scott.xpto2
select * from scott.xpto2;insert into xpto2 values (1);

insert into scott.xpto2
select * from scott.xpto2;insert into xpto2 values (1);

delete scott.xpto2;
commit;

SELECT begin_time Hora_Inicio_Transacao, end_time Hora_Fim_Transacao, undoblks Blocos_Undo_Consumidos FROM v$undostat;












--para dimensionar o tablespace de undo

select sum(end_time-begin_time) consumo_undo_dias from v$undostat;

select sum(end_time-begin_time)*86400 consumo_undo_segundos from v$undostat;

select ((sum(undoblks)/(sum(end_time-begin_time)*86400)))blks_undo_consumidos_Por_segs from v$undostat;

select (((sum(undoblks)/(sum(end_time-begin_time)*86400)))*4096) bytes_undo_consumidos_Por_segs from v$undostat;


show parameter undo_retention

select (((((sum(undoblks)/(sum(end_time-begin_time)*86400)))*4096)*10800)/1024/1024) tamanho_tablespace_undo_MB from v$undostat;

--para ver a % de esperas no segmento de undo/ tem que ser menor que 1%

SELECT (sum(waits)/sum(gets)*100) "Taxa de Porcentagem", sum(waits) "Total de Esperas", sum(gets) "Total de Acessos"
 FROM v$rollstat;


-- INFORMAÇÕES SOBRE UNDO

SELECT segment_name, owner, tablespace_name,status FROM DBA_ROLLBACK_SEGS;