Discussion :

[mimidatabase]

Bonjour,


Je suis en train d'écrire un script de génération d'un autre script, dont le but est de transférer d'un dossier source vers un dossier cible, les datafiles composant les tablespaces,. Je ne vais pas rentrer dans le détail parce que je ne pense pas que ce soit le forum adéquat, mais voilà ce que je voudrais faire.

Pour un même tablespace, plusieurs datafiles peuvent exister. Je souhaite entourer la partie qui transfère les différents datafile de la manière suivante :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
spool script.sql
 
break on tbsName
 
select
'alter tablespace ''' || tablespace_name || ''' begin backup;' tbsName,
'dbms_file_transfer.get_file(
  source_directory_object => ''SOURCE_DIR'',
  source_file_name => ''' || substr(file_name,instr(file_name,'/',-1)+1) || ''',
  destination_directory_object => ''DEST_DIR'',
  destination_file_name => ' || substr(file_name,instr(file_name,'/',-1)+1) || ',
  source_database => ''SOURCE_DB'');',
'alter tablespace ''' || tablespace_name || ''' end backup;'
from dba_data_files where tablespace_name in (select tablespace_name from dba_tablespaces)
order by tbsName
/
spool off

J'ai résolu mon premier soucis à l'aide de la commande BREAK, pour n'afficher qu'une seule fois par tablespace, la requête suivante dans le bloc :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

'alter tablespace ''' || tablespace_name || ''' begin backup;'

Mon problème ici, c'est que je ne sais pas comment faire, pour n'afficher la requête suivante, qu'une seule fois par tablespace, à la fin du bloc :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

'alter tablespace ''' || tablespace_name || ''' end backup;'

Je ne sais pas si une commande SQL pur peut résoudre mon soucis, ou bien une commande SQL Plus ?

J'espère avoir été assez clair. Merci d'avance pour vos réponses

[pacmann]

Salut !

Utilise LEAD / LAG pour savoir si la ligne précédente correspond au même table tbsname. Si oui, tu sors NULL, sinon, tu sors le begin ou end (selon le cas)

Tu vois l'idée ?

[mimidatabase]

Effectivement pacmann,

Je ne connaissais pas ces deux fonctions. Elles semblent en effet répondre parfaitement à mon besoin

Je bouquine ça, et j'essaie d'adapter mon script. Je te tiens au courant.

Merci pour ta réponse

[mimidatabase]

Hmm en fait il semble que je me sois un peu emballé.

En effet, juste avant de poster ma réponse, je pensais avoir obtenu le comportement que je cherche, sur une simple requête, mais en fait non.

Je m'explique : LAG et LEAD prennent en paramètre (entre autres) un offset, que l'on doit fixer. Or dans mon cas, un tablespace peut avoir 1, 2, 3, 5... 10 datafiles, donc dès que le nombre de datafile change d'un tablespace à l'autre, un décalage apparaît et les lignes insérées dans le script créé ne sont alors plus en phase avec le reste.

Je n'ai peut-être pas été assez clair dans ce que je souhaitais faire, et sans doute embrouillé les lecteurs avec le passage "à la fin du bloc".

C'est vrai que si je voulais ajouter des instructions à la fin et uniquement à la fin du bloc de code complet généré, ces fonctions auraient pu faire l'affaire.

Pour essayer d'être un peu plus clair, voilà un extrait de la sortie que j'obtiens, avec le bloc cité dans le premier post du topic :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
alter tablespace 'SYSTEM' begin backup;
dbms_file_transfer.get_file(
  source_directory_object => 'SOURCE_DIR',
  source_file_name => 'System.dbf',
  destination_directory_object => 'DEST_DIR',
  destination_file_name => System.dbf,
  source_database => 'SOURCE_DB');
alter tablespace 'SYSTEM' end backup;
 
alter tablespace 'TEST' begin backup;
dbms_file_transfer.get_file(
  source_directory_object => 'SOURCE_DIR',
  source_file_name => 'TEST_01.dbf',
  destination_directory_object => 'DEST_DIR',
  destination_file_name => TEST_01.dbf,
  source_database => 'SOURCE_DB');
alter tablespace 'TEST' end backup; -- ligne que j'aimerai ne pas avoir
 
 
dbms_file_transfer.get_file(
  source_directory_object => 'SOURCE_DIR',
  source_file_name => 'TEST_02.dbf',
  destination_directory_object => 'DEST_DIR',
  destination_file_name => TEST_02.dbf,
  source_database => 'SOURCE_DB');
alter tablespace 'TEST' end backup; -- ligne OK car c'est le dernier datafile du tablespace

Le tablespace 'TEST' est composé de deux datafiles.
Le problème ici, c'est la présence en double de l'instruction :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

ALTER tablespace 'TEST' end backup;

[pacmann]

Hmmm, tu les as utilisés comment tes LEAD / LAG ?
=> Tu as juste besoin de la précédente à priori...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
WITH t as (
select 'tbs0' as tbsn, 'dtf01' as dtf from dual
UNION ALL select 'tbs1' as tbsn, 'dtf11' as dtf from dual
UNION ALL select 'tbs1' as tbsn, 'dtf12' as dtf from dual
UNION ALL select 'tbs2' as tbsn, 'dtf21' as dtf from dual)
select tbsn, dtf, lead(tbsn, 1) OVER (ORDER BY tbsn, dtf), lag(tbsn, 1) OVER (ORDER BY tbsn, dtf) 
from t

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
TBSN DTF   LEAD LAG(
---- ----- ---- ----
tbs0 dtf01 tbs1     
tbs1 dtf11 tbs1 tbs0
tbs1 dtf12 tbs2 tbs1
tbs2 dtf21      tbs1
 
 
4 rows selected.

Si tu considère l'ordre tbsname, dtfname, tu peux comparer :
- le tbsn précédent au courant
- le tbsn suivant au courant

Chacun de ces deux tests détermine si oui ou non tu mets tes instructions...

Exemple sur le tbs1 :
la première ligne 'tbs1' = 'tbs1' mais 'tbs0' <> 'tbs1'
la deuxième ligne 'tbs2' <> 'tbs1' mais 'tbs1' = 'tbs1'

Non ?

[mimidatabase]

Hmm alors effectivement, j'ai réussi à m'en sortir, en combinant LEAD et LAG à la fonction DECODE.
Voici ce que ça donne :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
set echo off time off timing off ver off feed off term off
set pages 0 lines 255
set trims on
 
spool script.sql
 
SELECT
decode(lag(tablespace_name, 1) OVER (ORDER BY tablespace_name), tablespace_name, NULL, 'alter tablespace ''' || tablespace_name || ''' begin backup;') LAG,
'dbms_file_transfer.get_file(
  source_directory_object => ''SOURCE_DIR'',
  source_file_name => ''' || substr(file_name,instr(file_name,'/',-1)+1) || ''',
  destination_directory_object => ''DEST_DIR'',
  destination_file_name => ''' || substr(file_name,instr(file_name,'/',-1)+1) || ''',
  source_database => ''SOURCE_DB'');' dbms_file_transfer,
decode(tablespace_name, lead(tablespace_name, 1) OVER (ORDER BY tablespace_name, file_name), NULL, 'alter tablespace ''' || tablespace_name || ''' end backup;') LEAD
FROM dba_data_files
/
 
spool off

Et voici un extrait du fichier de sortie :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
alter tablespace 'I_USER' begin backup;
dbms_file_transfer.get_file(
  source_directory_object => 'SOURCE_DIR',
  source_file_name => 'Dbf26',
  destination_directory_object => 'DEST_DIR',
  destination_file_name => 'Dbf26',
  source_database => 'SOURCE_DB');
alter tablespace 'I_USER' end backup;
 
alter tablespace 'TEST' begin backup;
dbms_file_transfer.get_file(
  source_directory_object => 'SOURCE_DIR',
  source_file_name => 'TEST_01.dbf',
  destination_directory_object => 'DEST_DIR',
  destination_file_name => 'TEST_01.dbf',
  source_database => 'SOURCE_DB');
 
 
 
dbms_file_transfer.get_file(
  source_directory_object => 'SOURCE_DIR',
  source_file_name => 'TEST_02.dbf',
  destination_directory_object => 'DEST_DIR',
  destination_file_name => 'TEST_02.dbf',
  source_database => 'SOURCE_DB');
 
 
 
dbms_file_transfer.get_file(
  source_directory_object => 'SOURCE_DIR',
  source_file_name => 'TEST_03.dbf',
  destination_directory_object => 'DEST_DIR',
  destination_file_name => 'TEST_03.dbf',
  source_database => 'SOURCE_DB');
alter tablespace 'TEST' end backup;
 
alter tablespace 'SYSAUX' begin backup;
dbms_file_transfer.get_file(
  source_directory_object => 'SOURCE_DIR',
  source_file_name => 'SysAux.dbf',
  destination_directory_object => 'DEST_DIR',
  destination_file_name => 'SysAux.dbf',
  source_database => 'SOURCE_DB');
alter tablespace 'SYSAUX' end backup;

J'ai fait le test sur une base avec des tablespaces composés de deux voire trois datafiles et ça fonctionne comme attendu

Merci pour ton aide pacmann