Exporting and importing statistics

Exporting and importing statistics from PROduction to PREproduction:
====================================================================

First we must create a table to save the statistics:

execute dbms_stats.create_stat_table('SYS','ESTADISTICAS','TOOLS');
^schema ^table ^TS

Second we export the schema statistics to this table:

execute dbms_stats.EXPORT_SCHEMA_STATS('DWH03','ESTADISTICAS','DWH03','SYS');

We can, alternativelly, export only one table:

PROCEDURE EXPORT_TABLE_STATS
Nombre de Argumento Tipo E/S +Por Defecto?
------------------------------ ----------------------- ------ --------
OWNNAME VARCHAR2 IN
TABNAME VARCHAR2 IN
PARTNAME VARCHAR2 IN DEFAULT
STATTAB VARCHAR2 IN
STATID VARCHAR2 IN DEFAULT
CASCADE BOOLEAN IN DEFAULT
STATOWN VARCHAR2 IN DEFAULT

execute dbms_stats.EXPORT_TABLE_STATS('DWH03','BSMZCONINFHIS03',NULL,'ESTADISTICAS','a','SYS');

Next we export the statistics table and import it in the destination instance:

EXP sys@PRO file=estad_pro.dmp log=estad_pro.log tables="ESTADISTICAS"

Conectado a: Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
Exportaci¾n realizada en el juego de caracteres WE8MSWIN1252 y el juego de carac
teres NCHAR AL16UTF16

Exportando las tablas especificadas a travÚs de la Ruta de Acceso Convencional .
..
. exportando la tabla ESTADISTICAS 100058 filas exportadas
La exportaci¾n ha terminado correctamente y sin advertencias.

IMP system@PRE file=estad_pro.dmp log=estad_pre.log full=Y

importaci¾n realizada en el juego de caracteres WE8MSWIN1252 y el juego de carac
teres NCHAR AL16UTF16
. importando objetos de SYS en SYSTEM
. importando objetos de SYS en SYSTEM
. importando la tabla "ESTADISTICAS" 100058 filas importadas

La importaci¾n ha terminado correctamente y sin advertencias.


PROCEDURE IMPORT_SCHEMA_STATS
Nombre de Argumento Tipo E/S +Por Defecto?
------------------------------ ----------------------- ------ --------
OWNNAME VARCHAR2 IN
STATTAB VARCHAR2 IN
STATID VARCHAR2 IN DEFAULT
STATOWN VARCHAR2 IN DEFAULT
NO_INVALIDATE BOOLEAN IN DEFAULT
FORCE BOOLEAN IN DEFAULT

execute dbms_stats.IMPORT_SCHEMA_STATS('DWH03','ESTADISTICAS','DWH03','SYSTEM');
Procedimiento PL/SQL terminado correctamente.

PROCEDURE IMPORT_TABLE_STATS
Nombre de Argumento Tipo E/S +Por Defecto?
------------------------------ ----------------------- ------ --------
OWNNAME VARCHAR2 IN
TABNAME VARCHAR2 IN
PARTNAME VARCHAR2 IN DEFAULT
STATTAB VARCHAR2 IN
STATID VARCHAR2 IN DEFAULT
CASCADE BOOLEAN IN DEFAULT
STATOWN VARCHAR2 IN DEFAULT
NO_INVALIDATE BOOLEAN IN DEFAULT

Or we can do the same procedure with the individual table:

SQL> execute dbms_stats.IMPORT_TABLE_STATS('DWH03','BSMZCONINFHIS03',NULL,'ESTAD
ISTICAS','DWH03',TRUE,'SYSTEM');

Procedimiento PL/SQL terminado correctamente.

And, finally, we can drop (or not) the statistics table in the source instance:

execute dbms_stats.drop_stat_table('SYS','ESTADISTICAS');

Example of resumable option

An usually forgotten feature. Here there is a basic example:

SQL> CREATE TABLESPACE chinorri
2 LOGGING
3 DATAFILE 'K:\ORANT\DWH\chinorri.DBF' SIZE 128k
4 autoextend off EXTENT MANAGEMENT LOCAL SEGMENT
5 SPACE MANAGEMENT AUTO ;

Tablespace creado.

SQL> create table kk(kk varchar2(2000)) tablespace chinorri;
Tabla creada.

-- WITHOUT RESUMABLE, THE USUAL ERROR FOLLOWS
-- SIN RESUMABLE, LA PETADA DE SIEMPRE

SQL> insert into kk select 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' from dba_objects;


insert into kk select 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' from dba_objects
*
ERROR en lÝnea 1:

ORA-01653: no se ha podido ampliar la tabla SYSTEM.KK con 8 en el tablespace
CHINORRI

-- WITH RESUMABLE AND WAITING ONE MINUTE:
-- CON RESUMABLE DE UN MINUTILLO:
SQL> alter session enable resumable timeout 60 name 'resumable test';
Sesi¾n modificada.

SQL> insert into kk select 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxx' from dba_objects;

-- OF COURSE, AT THE END OF THE PERIOD, THE TRANSACTIONS CRASHS
-- CLARO, AL CABO DEL MINUTILLO DA UN ERROR

insert into kk select 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxx' from dba_objects
*
ERROR en lÝnea 1:

ORA-30032: la sentencia suspendida (de reanudaci¾n) ha sufrido un timeout
ORA-01653: no se ha podido ampliar la tabla SYSTEM.KK con 8 en el tablespace
CHINORRI


-- BUT IF WE CONFIGURE ENOUGH TIME AND SOLVE THE ISSUE IN THE MEANWHILE...
-- PERO SI LE DAMOS EL TIEMPO SUFICIENTE Y LO SOLUCIONAMOS ENTRE TANTO:


SQL> alter session enable resumable timeout 7200 name 'resumable test';
Sesi¾n modificada.


SQL> insert into kk select 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' from dba_objects;

-- HERE THE SESSION IS SUSPENDED.....
…….. Y AQUÍ SE QUEDA ESPERANDO DURANTE UNA HORA (7200 SEGUNDOS).

-- BUT WHEN WE RESIZE THE TABLESPACE:
-- Se amplia el TS y ……

42842 filas creadas.

SQL> alter session disable resumable;
Sesi¾n modificada.


Similarly you can use this clause in the import utility (resumable=Y and RESUMABLE_TIMEOUT=time).

Grants, three ways to obtain it

1) If you are in Oracle <= 9i you can use the Unix commands strings and grep to filter the metadata existing in the export file header (interesting if you have a huge one):

strings export.dmp | grep -i ' TO '
or
strings export.dmp|grep –i GRANT |sort > grants.sql


2) Classical dictionary views querys:

-- USUERS ASSIGNEDS TO EVERY ROLE --
SELECT GRANTED_ROLE as rol, GRANTEE as User
FROM DBA_ROLE_PRIVS
WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS)
order by granted_role;

-- ROLES OF EVERY USER --
SELECT GRANTEE as Usuario, GRANTED_ROLE as rol
FROM DBA_ROLE_PRIVS
WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS)
order by grantee;

-- PRIVILEGES OF EVERY USER --
select * from dba_sys_privs
where grantee in (select username from dba_users)
order by grantee;

-- OBJECT PRIVILEGES OF EVERY USER --
select substr(grantee, 1,15) usuario,
substr(table_name,1,35) tabla,
substr(privilege,1,20) privilegio
from dba_tab_privs
where GRANTEE IN (SELECT USERNAME FROM DBA_USERS)
ORDER BY table_name;

-- DBA USERS --
select grantee from dba_role_privs
where granted_role ='DBA';


3) The most powerful an flexible: query the dictionary.

select 'grant '||m$.name||' on '||uo$.name||'.'||o$.name||' to '||ue$.name
from
sys.objauth$ t$,
sys.obj$ o$, sys.user$ ur$,
sys.table_privilege_map m$,
sys.user$ ue$,
sys.user$ uo$
where
o$.obj# = t$.obj#
and t$.privilege# = m$.privilege
and t$.col# is null
and t$.grantor# = ur$.user#
and t$.grantee# = ue$.user#
and o$.owner# = uo$.user#
and t$.grantor# != 0
-- (to filter by role name:) and ue$.name='ROLE';

(10g) A query to find the most active users connected from another machine

This query is useful to find middle-tier (applications servers or so) users having a heavy activity in the instance, thanks to the column CLIENT_IDENTIFIER, that is maintained throught all the layers. You can, also, filter by active users.

set linesize 1000
col program format a30
col machine format a10
col PERSONA format a10
col program format a7
col username format a7
alter session set nls_date_format='dd/mm/yy hh24:mi';

select s.CLIENT_IDENTIFIER PERSONA, substr(s.program, 1,7) program, s.username, l.TIME_REMAINING REMAINING, l.ELAPSED_SECONDS ELAPSED,l.START_TIME, s.sid, s.serial#, s.process,
l.OPNAME,l.TARGET,l.TARGET_DESC,l.SOFAR,l.TOTALWORK,l.UNITS,
l.LAST_UPDATE_TIME,l.TIMESTAMP,l.CONTEXT,l.MESSAGE
from v$session s, v$session_longops l
where s.TYPE='USER'
and program like 'dis51ws@%'
and s.username='SCOTT'
and s.SID=l.sid
and s.SERIAL#=l.SERIAL#
--and status='ACTIVE'
order by elapsed_seconds desc;

(10G) How to tune a SQL with DMBS_SQLTUNE

We must tune a select. Possibly we've got this select from the Statspack/ADDM report or from a developer team. In the first example it have no bind variables, only literals.

1) Select with literals:

DECLARE tarea_de_tuning varchar2(30); sql_txt clob;
BEGIN
sql_txt := 'select username from dba_users order by 1';
tarea_de_tuning := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => sql_txt,
user_name => 'OWNER',
scope => 'COMPREHENSIVE',
time_limit => 300, -- 5 minutes
task_name => 'Tuning',
description => 'Tuning');
END;
/

SQL> execute dbms_sqltune.execute_tuning_task (task_name => 'Tuning’);

OK, now we obtain the recommendations:

set long 50000
set pagesize 0
select dbms_sqltune.report_tuning_task('Tuning') from dual;


2) With Bind variables:

DECLARE tarea_de_tuning varchar2(30); sql_txt clob;
BEGIN
sql_txt := 'select username from dba_users where username > :b1';
tarea_de_tuning := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => sql_txt,
bind_list => sql_binds(anydata.ConvertVarchar2('SYSTEM')),
user_name => 'OWNER',
scope => 'COMPREHENSIVE',
time_limit => 300,
task_name => 'Tuning with Binds',
description => 'Tuning with Binds');
END;
/

execute dbms_sqltune.execute_tuning_task (task_name => 'Tuning with Binds’);

OK, again, we obtain the recommendations:

set long 5000
set pagesize 0
select dbms_sqltune.report_tuning_task('Tuning with Binds') from dual;


And, finally, drop the task:

exec DBMS_SQLTUNE.DROP_TUNING_TASK (task_name => 'Tuning’);

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.