keywords : control file, Oracle control file, Change Control File location,  CONTROL_FILES, Change default Control File location

Its not possible to change CONTROL FILES location with Oracle ENterprise Manager Console ( OEM ) . But you can do it with some SQL commands and…a SPFILE. Here is the script :

$> sqlplus /nolog
SQL> connect sys as sysdba

SQL> show parameter CONTROL_FILES
NAME                                 TYPE        VALUE
———————————— ———– ——————————
control_files                        string      E:\ORADATA\ORCL\CONTROL01.CTL,
E:\ORADATA\ORCL\CONTROL02.CTL
,  E:\ORADATA\ORCL\CONTROL03.CTL

SQL> CREATE PFILE=’c:\temp\pfile.ora’ FROM SPFILE;

edit  c:\temp\pfile.ora and change one of the PATH value for CONTROL_FILES
here :
E:\ORADATA\ORCL\CONTROL03.CTL becomes  C:\TEMP\CONTROL03.CTL

copy the  control file
ici CONTROL03.CTL de E:\ORADATA\ORCL\ vers c:\TEMP

– shutdown and restart with the new parameter
SQL> shutdown abort
SQL> startup

SQL> show parameter control_files
NAME                                 TYPE        VALUE
———————————— ———– ——————————
control_files                        string      E:\ORADATA\ORCL\CONTROL01.CTL,
E:\ORADATA\ORCL\CONTROL02.CTL
, C:\TEMP\CONTROL03.CTL

You’re done !

Mots ckés : control file, Oracle control file, Changer l’emplacement d’un Control File , changer l’emplacement par défaut d’un control file, CONTROL_FILES

Il n’est pas possible de changer dynamiquement l’emplacement d’un  CONTROL FILE  pour le multiplexer par exemple, avec  Oracle ENterprise Manager Console ( OEM ) . Quelques commandes SQL et l’appui du SPFILE suffisent… :

$> sqlplus /nolog
SQL> connect sys as sysdba

SQL> show parameter CONTROL_FILES
NAME                                 TYPE        VALUE
———————————— ———– ——————————
control_files                        string      E:\ORADATA\ORCL\CONTROL01.CTL,
E:\ORADATA\ORCL\CONTROL02.CTL
,  E:\ORADATA\ORCL\CONTROL03.CTL

SQL> CREATE PFILE=’c:\temp\pfile.ora’ FROM SPFILE;

editer  c:\temp\pfile.ora and change one of the PATH value for CONTROL_FILES
ici :
E:\ORADATA\ORCL\CONTROL03.CTL becomes  C:\TEMP\CONTROL03.CTL

copier le control file
ici CONTROL03.CTL de E:\ORADATA\ORCL\ vers c:\TEMP

SQL> shutdown abort
SQL> startup

SQL> show parameter control_files
NAME                                 TYPE        VALUE
———————————— ———– ——————————
control_files                        string      E:\ORADATA\ORCL\CONTROL01.CTL,
E:\ORADATA\ORCL\CONTROL02.CTL
, C:\TEMP\CONTROL03.CTL

C’est fait!

Par défaut SQL Developer ne reconnait pas la configuration standard Oracle Net et notamment la résolution de nom via OID, même  si le client est correctement configuré par ailleurs.

ie : une connexion sqlplus fonctionne avec la résolution via OID mais pas sqldeveloper.

Si vous tentez une connexion LDAP vous risquez une erreur du genre : ‘ property ‘hostname’ not set on the database provider ‘ …

Les hypotheses ici :

ORACLE_HOME c:\oracle\instant_client

et dans le sous répertoire standard \network\admin les fichiers de configuration pour la résolution de nom via OID / LDAP :

sqlnet.ora
NAMES.DIRECTORY_PATH= (LDAP, EZCONNECT)
TNSPING.TRACE_DIRECTORY = c:\oracle\instant_client_10g
TNSPING.TRACE_LEVEL = c:\oracle\instant_client_10g

ldap.ora
DEFAULT_ADMIN_CONTEXT = “dc=mondomaine,dc=fr”
DIRECTORY_SERVERS = (sr-oid.mondomaine.fr:389)
DIRECTORY_SERVER_TYPE = OID

Pour que SQLdevelopper fonctionne il faut rajouter la variable TNS_ADMIN dans l’environnement WINDOWS qui pointe sur votre rep NETWORK\ADMIN

C:\>set tns_admin=C:\oracle\instant_client_10g\Network \Admin

ou utiliser la fenetre ‘propriétés’ de vopre poste de travail et le bouton ‘variables d’environnement’ pour ajouter TNS_ADMIN

Dès lors SQLdevelopper reconnait votre serveur LDAP qui apparait dans la liste déroulante (si vous avez choisit ‘type de connexion’ LDAP)

Ensuite il ne vous reste plus qu’a choisir le contexte racine dans la liste et cliquer sur le bouton ‘charger’ pour avoir la liste de vos alias définis dans OID…

A partir de Oracle 8.1 on peut utiliser une commande ‘ALTER’ pour changer le jeu de caractères ( CHARACTER SET) de la base Oracle(qui est codé en dur lors du CREATE DATABASE…) Pour par exemple la convertir de US7ASCII à WE8MSWIN1252 ou WE8ISO8859P1. Note : la conversion peut fonctionner car tous les caratères encodés de la sources sont représentables dans le jeu de caractère cible. En d’autres termes par que les jeux de caractères 8bits sont en général des sur ensembles (supersets) de U7ASCII. Faire une sauvegarde de la base puis passer les commandes suivantes :

SQL> STARTUP MOUNT; — la base doit être en mode RESTRICT
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET WE8MSWIN1252;
– ou par exemple CHARACTER SET WE8ISO8859P1;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

Cela permettra de résoudre des problèmes de compatibilité d’export / import par exemple.

Remarque : il est peu évident d’admettre qu’une base en jeu de caractère 7 bits US7ASCII, puisse afficher des caractères accentués, c’est pourtant le cas.

Il suffit de laisser la base telle quelle et de paramétrer les clients avec un jeu de caractères 8 bits, via le paramètre d’environnement NLS_LANG par exemple. Voir la doc NLS pour + d’infos.
ATTENTION ! il faut bien sûr que les données aient été saisies (INSERT SQL) dans CET environnement pour que cela marche !

Un exemple :

SQL> update ma_table
set ma_date=’10/04/2009′
/
ERROR at line 1:
ORA-01843: not a valid month

Le format de date proposé est incompatible avec la base de données cible.

Ceci se produit lorsque le client Oracle n’est pas dans la même configuration que le serveur au niveau de son environnement de ‘localisation’ : environnement NLS (Oracle National Language Support

solution temporaire (la durée de la session)
SQL> ALTER SESSION SET NLS_DATE_FORMAT=’MM/DD/YYYY’;

solution un peu plus pérenne sous Unix/Linux :
Changer le paramétrage du client Oracle, pour positionner le nouveau format de date par défaut.
Ceci se fait avec une variable d’environnement Unix :

$> export NLS_DATE_FORMAT=DD/MM/YYYY

Sous Windows (personne n’est parfait) l’environnement client Oracle est spécifié dans la base de registres, donc on modifiera / ajoutera une clé NLS_DATE_FORMAT dans la section HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE avce regedit par exemple.

Note : lorsqu’on utilise un client léger (application Web ou i*SQLPlus par exemple), il ne faut pas regarder la cofiguration du poste client bien sûr, mais celle su serveur Web qui, dans une architecture 3-tier, est le ‘vrai’ client de la base de données…

SQLDeveloper présente une fonctionalité fort sympatique , il permet d’exporter au format SQL tout ou partie de la base, d’un schema , d’une table, ou d’une table filtrée.
Et on peut mettre ca dans le presse papier ou dans un fichier.
Ca s’appelle faire du reverse Engineering et c’est beau.
On peut s’en servir pour migrer des données d’Oracle à Oracle, ou d’Oracle vers d’autres SGBDRs…

Pour l’export de tables , il crée des ‘INSERT’ …mais malheureusement les valeurs sont séparées par des ‘espaces’.
Plus précisément la clause ‘VALUES’ de l’INSERT au lieu d’utiliser des valeurs séparés par des ‘,’ utilise le séparateur de groupe pour afficher des numériques.

Ce bug apparait notamment avec la version SQL Developer 1.51

Pour y remédier et mettre des ‘,’ comme en SQL (!).
Il faut changer le parametre NLS séparateur de groupe par défaut :

Sous sql developer
clic -> tools -> preferences -> Database -> NLS Parameters
puis Mettre le séparateur decimal à “.” et le separateur de groupe à “,”

PS : prendre en compte que cela affectera vos affichages de (gros) numériques…

Un exemple de script pour gérer et utiliser des tables externes Oracle (external tables)

On va charger emp a partir d’un fichier externe CSV, en utilisant SQL et les tables externes Oracle ( Oracle 9i, Oracle 10g et + si affinités )

D’abord, creer un repertoire oracle de travail (Oracle directory) associé au repertoire ou se trouve le fichier d’entree.
Pour ce faire, SCOTT doit avoir le privilège Systeme ‘CREATE ANY DIRECTORY’
…et le CREATE TABLE bien sur !

sous SQL>

CONNECT SYSTEM/xx@db
GRANT CREATE ANY DIRECTORY TO scott;
CONNECT scott/tiger@db
create or replace directory dossier_temp as ‘/tmp’;
drop table table_externe_emp;

rem : si le user qui lit/ecrit dans la directory n’est pas celui qui l’a créé
(ici c’est SCOTT qui fait tout), ce uyser aura aussi besoin des droitsd R/W sur la directory :

SQL> grant read,write on directory dossier_temp to scott;

Créer une ‘table’ externe attachee au fichier d’entree

create table table_externe_emp
(EMPNO NUMBER(4) ,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2)
)
ORGANIZATION EXTERNAL
( type oracle_loader
default directory dossier_temp
access parameters
( records delimited by newline
badfile ‘charge_emp.bad’
logfile ‘charge_emp.log’
fields terminated by ‘,’
(empno, ename, job, mgr, hiredate date, sal, comm, deptno)
)
location (’emp.csv’)
)
– attention c’est important sinon ca marche moins bien !
reject limit unlimited
/

Lire la table externe

select * from table_externe_emp;

!! Attention en cas de problème regardez les fichiers LOG et BAD sur le serveur

– lecture table externe et INSERTION dans une autre cible
– on insère dans la table destination si elle existe

insert into mon_emp
select * from table_externe_emp;
commit;

– lecture table externe et CREATION/INSERTION dans une autre cible
– on peut aussi pour aller + vite recopier la table externe ET son contenu !

create table mon_emp
as select * from table_externe_emp;

Si l’optimiseur est un peu fatigué et que les requetes sql ne sont pas meix on peut toujours essayer d’aider le dit optimiseur en recalculant les statistiques pours tous les objets du schema.

Il existe un package Oracle pour ca en 9i et 10g, plutot que de faire de fastidieux ANALYZE TABLE…COMPUTE .

C’est DBMS_STATS et la procedure GATHER_SCHEMA_STATS

begin
dbms_stats.gather_schema_stats(’&OWNER’,DBMS_STATS.AUTO_SAMPLE_SIZE,false,
‘FOR ALL COLUMNS SIZE AUTO’,1,’GLOBAL’,true,null,null,’GATHER AUTO’);
end;
/

ou plus simplement (en utilisant les valeurs par defaut des parametres :

execute dbms_stats.gather_schema_stats(ownname=>’nom_du_proprietaire’);

Pour plus d’infos sur le paramétrage voir la doc officielle PL/SQL supplied packages

et la procedure GATHER_SCHEMA_STATS du package DBMS_STAT

Bonnes stats !

Il est possible de faire l’équivallent d’un ’su’ Unix, sous Oracle, pour changer l’utilisateur courant.

C’est très pratique et s’appuie sur une fonctionnalité peu documentée : le ALTER USER … IDENTIFIFIED BY VALUES.

Voici le script (je n’en suis pas l’auteur) :

set head off termout off
set verify off
col nl newline
spool /tmp/sud.tmp
column EXTERNE new_value NEXTERNE noprint
select decode (password,’EXTERNAL’,'oui’,'non’) EXTERNE
from dba_users where username=upper(’&&1′)
/
select ‘alter user &&1 identified by &&1;’ nl,
‘grant alter user to &&1;’ nl
from dual
/
select
‘connect &&1/&&1.@’||rpad(global_name,5) nl,
‘alter user &&1 identified’||
decode(’&NEXTERNE’,'oui’,’ EXTERNALLY;’,
‘ by values ”’||u.password||”’;') nl
from sys.dba_users u, global_name
where u.username = upper(’&&1′)
and u.username <> user
/
spool off
@/tmp/sud.tmp
set termout on
select ‘Connected as ‘||USER||’ on ‘||global_name||’.’
from global_name;
–!rm /tmp/su.tmp
set termout on head on

Seule petite particularité : quand le compte est en authentification externe ( IDENTIFIED EXTERNALLY) …ca marche aussi.