How to include the timestamp in filenames (Windows)

It's little more difficult than in Unix.

We begin forming the name of the dumpfile:

Set fichero=MSMTD_%date:~-4,4%%date:~-7,2%%date:~4,2%%time:~0,2%%time:~3,2%

Example output: MSMTD_200803171559

Let's see the batch file:

export.bat

@echo off
set fecha=MSMTD_%date:~-4,4%%date:~-7,2%%date:~4,2%%time:~0,2%%time:~3,2%echo Se generaran los ficheros %fecha% {.dmp y .log}
expdp system/password directory=exports dumpfile=%fecha%.dmp logfile=%fecha%.log schemas=MSMTD content=all keep_master=N
@echo ****** FIN DE LA EXPORTACION *********

Test the connections to all the instances

I find very useful to connect to all my instances at the beginning of the day; in that way I can anticipate a lot of e-mails. This one is the most simplest version, using a windows batch file.

We need to enter a shell on our desktop PC (surely with all the network permissions to access the instances we administer), an Oracle Client installed (to use sqlplus) and a tnsnames.ora configured (at least an Instance Client).

Example of use:

U:\>connects
BDEXTPPR

BDINTPPR

DWHDEV

DWHREP10

DWHPRO
...
U:\>

Here we can see the "connects.bat" file and the "exit.sql" file.

U:\>type d:\dba\connects.bat
@echo off
sqlplus -S "sys/password@bdintppr as sysdba" @c:\exit.sql
sqlplus -S "system/password@dwhpro" @c:\exit.sql
....


U:\>type c:\exit.sql
set pagesize 0
col instancia format a10
select UPPER(instance) INSTANCIA from v$thread;
exit


It's not the solution I most like, but it works well. The other solution I'm working in on consists in a PL/SQL the establish connections to the locations stored in a VARRAY.

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’);