Comparing several schemas with dbms_space.object_space_usage

This is useful, for example, if we import or refresh a schema to another instance.
In this example we'll compare schemas DWH03 and PRUEBA03.

set serveroutput on

DECLARE
CURSOR C IS select OWNER, SEGMENT_NAME, SEGMENT_TYPE,100, PARTITION_NAME
from dba_segments
where (OWNER LIKE 'PRUEBA03' OR OWNER LIKE 'DWH03')
and segment_name not like 'BIN%'
AND PARTITION_NAME IS NULL
and SEGMENT_TYPE in ('TABLE', 'INDEX', 'CLUSTER')
group by owner, SEGMENT_NAME, SEGMENT_TYPE, PARTITION_NAME
order by owner, SEGMENT_TYPE, SEGMENT_NAME;

F C%rowtype;
SPACE_USED NUMBER;
SPACE_ALLOCATED NUMBER;
TOTAL1 NUMBER;TOTAL2 NUMBER;

BEGIN
DBMS_OUTPUT.ENABLE(999999999);
open c;
fetch c into f;
TOTAL1:=0; TOTAL2:=0;
LOOP
EXIT WHEN C%NOTFOUND;
dbms_space.object_space_usage(F.OWNER, F.SEGMENT_NAME,
F.SEGMENT_TYPE,100, F.PARTITION_NAME,SPACE_USED,
SPACE_ALLOCATED);
if F.OWNER='DWH03' THEN TOTAL1:=TOTAL1+SPACE_USED;
elsif F.OWNER='PRUEBA03' THEN TOTAL2:=TOTAL2+SPACE_USED;
end if;
dbms_output.put_line
(F.OWNER','F.SEGMENT_TYPE','F.SEGMENT_NAME','F.PARTITION_NAME','SPACE_USED','SPACE_ALLOCATED);
fetch c into f;
END LOOP;
close c;
dbms_output.put_line('SUMA DE DWH03 = 'TOTAL1);
dbms_output.put_line('SUMA DE PRUEBA03 = 'TOTAL2);
END;
/

PL/SQL to review the alert.log using it as an external table

First we need to create a logical directory and declare the alert as an external table. I use the oracle_loader drive in order to avoid the modification of the alert.log, but in 10G you can declare it using the ORACLE_DATAPUMP drive if you wish. I show the error line number, if any, in order to locate it easily with the editor.

create directory dir_alert as 'C:\ORANT\ORA102\...\DWHPRO\BDUMP\';

create table alert(
texto varchar2 (120 char) null)
organization external(
type oracle_loader
default directory dir_alert
access parameters (records delimited by newline)
location (dir_alert:'alert_dwhpro.log')
) reject limit 1000;

Here the PL/SQL I use to search for "ORA-" errors in the last lines (comments are in spanish):


set serveroutput on
set linesize 140
set pagesize 0

/******************************************************
* CONTAJE DE LOS ERRORES "ORA-" EN LAS ÚLTIMAS LÍNEAS *
******************************************************/
DECLARE
revisadas constant integer := 1000;
ultima number;
errores number;
fecha_inicio varchar2(120):='';
buscar boolean:=true;
cursor c1 (ultima in number) is
select * from (select row_number() over (order by ROWNUM) linea, texto
from alert)
where linea > ultima-revisadas;

fc1 c1%rowtype;
anterior c1%rowtype;

BEGIN
errores:=0; ultima:=1;
select max(linea) into ultima
from (select row_number() over (order by ROWNUM) linea, texto from alert);
open c1(ultima);
fetch c1 into fc1;anterior:=null;
loop
if fc1.texto like 'ORA-%' then
errores:= errores+1;
dbms_output.put_line('ERROR Numero '||to_char(errores,'99')||':');
dbms_output.put_line('LINEA MENSAJE');
dbms_output.put_line('-------------------------------------------');
dbms_output.put_line(to_char(anterior.linea,'99G999')
||' '||anterior.texto);
dbms_output.put_line(to_char(fc1.linea,'99G999')||' '||fc1.texto);
dbms_output.put_line('-------------------------------------------');
dbms_output.put_line(CHR(10));
end if;
anterior:=fc1;
if buscar and fc1.texto like '%'||extract(year from sysdate) then
buscar:=false;
fecha_inicio:=fc1.texto;
end if;
fetch c1 into fc1;
exit when c1%notfound;
end loop;
close c1;
dbms_output.put_line(CHR(10));
dbms_output.put_line('RESULTADO');
dbms_output.put_line('---------'||CHR(10));
dbms_output.put_line('Entre ['||fecha_inicio||' ---> ahora]
(ultimas '||revisadas||' lineas del alert)');
if errores=0 then dbms_output.put_line('No hay errores.');
else
dbms_output.put_line('Hay '||errores||' error(es).');
end if;
END;
/


Example of output:

SQL> @d:\dba\errores
ERROR Numero 1:
LINEA MENSAJE
---------------------------------------------------------------------------------
43.850 Tue Feb 05 04:57:27 2008
43.851 ORA-DUMMY This is an error
---------------------------------------------------------------------------------




RESULTADO
---------

Entre [Sat Feb 02 15:50:17 2008 ---> ahora] (ultimas 1000 lineas del alert)
Hay 1 error(es).

Procedimiento PL/SQL terminado correctamente.

In a similar way yo can list the last lines in the alert.log, I do it every morning.

PL/SQL to generate a DBMS_TTS.TRANSPORT_SET_CHECK to verify wether the tablespace set is self-contained

SET SERVEROUTPUT ON
/* PL/SQL to generate a DBMS_TTS.TRANSPORT_SET_CHECK to verify wether the tablespace set is self-contained before the transportation process */
DECLARE
/* You must customize this cursor: it represents the list of TS to transport */
CURSOR C IS select tablespace_name from dba_tablespaces where tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS','XDB','TOOLS','OWB_DATA','OWB_INDX','ODM','DRSYS','EXAMPLE','FD','FD_TEMP','CWMLITE','INDX','STATPACK') AND CONTENTS='PERMANENT' order by 1;
FC C%ROWTYPE;
i number;
BEGIN
dbms_output.put_line('-------------- C U T - H E R E --------------------------');
dbms_output.put_line('BEGIN /* DBMS_TTS.TRANSPORT_SET_CHECK EN PRO */');
dbms_output.put_line('SYS.DBMS_TTS.TRANSPORT_SET_CHECK (');
open c;
fetch c into fc;
i:=0;
dbms_output.put('''');
loop
dbms_output.put(fc.tablespace_name||',');
if i<3 then i:=i+1;
else dbms_output.put_line(''''); dbms_output.put('||''');
i:=0;
end if;
fetch c into fc;
exit when c%notfound;
end loop;
close c;
dbms_output.put_line(','');');
dbms_output.put_line('END; ');
dbms_output.put_line('/');
dbms_output.put_line(chr(10));
dbms_output.put_line('-------------- C U T - H E R E -------------------------');
dbms_output.put_line('SELECT * FROM TRANSPORT_SET_VIOLATIONS;');
END;
/

Example output:

-------------- C U T H E R E --------------------------
BEGIN /* DBMS_TTS.TRANSPORT_SET_CHECK EN PRO */
SYS.DBMS_TTS.TRANSPORT_SET_CHECK (
'USERS,,');
END;
/


-------------- C U T H E R E -------------------------
SELECT * FROM TRANSPORT_SET_VIOLATIONS;

Procedimiento PL/SQL terminado correctamente.