Problème export SQL avec SQL Developer

scripts et trucs pas de Commentaire »

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…

securite-oracle-Droits systeme

Divers, Sécurité Oracle pas de Commentaire »

Privilèges système
Les privilèges systèmes d’accès aux objets s’intéressent plutôt au contenant qu’au contenu. Ils concernent principalement des ordres de création, de modification de structure et de suppression d’objets (SQL DDL plutot que DML)

Ce sont donc des privilèges d’assez haut niveau, que l’on réservera par exemple aux développeurs mais qui seront utilisés avec beaucoup de parcimonie en phase de production…

Liste de quelques privilèges système d’Oracle

Nom
du privilege
Type
d’action autorisée
ANALYZE
ANALYZE ANY
Analyser
toutes les tables, clusters, ou indexs
dans
la base de données.
AUDIT
AUDIT ANY
Auditer tous
les objets dans la base de données.
AUDIT SYSTEM
Auditer les
actions de type DBA
CLUSTER
CREATE CLUSTER
créer un
cluster .
CREATE ANY
CLUSTER
créer un
cluster dans tous les schémas.
ALTER ANY
CLUSTER
Modifier
tous les cluster dans la base de données.
DROP ANY
CLUSTER
Supprimer
tous les cluster dans la base de données.
DATABASE
ALTER DATABASE
Modifier
la structure physique de la base
DATABASE
LINK
CREATE DATABASE
LINK
Créer
des database links privés.
INDEX
CREATE ANY
INDEX
créer un
index dans tous les schemas sur toutes les tables.
ALTER ANY
INDEX
Modidier
tous les index dans la base de données.
DROP ANY
INDEX
Supprimer
tous les index dans la base de données.
PRIVILEGE
GRANT ANY
PRIVILEGE
Donner tous
les privileges système
PROCEDURE
CREATE PROCEDURE
Créer
des procedures stockées, fonctions, et packages .
CREATE ANY
PROCEDURE
Créer
des procedures stockées, fonctions, et packages dans tous les schemas.
(suppose ALTER ANY TABLE, BACKUP ANY TABLE, DROP ANY TABLE, SELECT ANY TABLE,
INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE, ou GRANT ANY TABLE.)
ALTER ANY
PROCEDURE
Compiler
toutes les procedures stockées, fonction, ou packages dans tous les
schemas.
DROP ANY
PROCEDURE
Supprimer
toutes les procedures, function, ou package stockés dans tous les
schema.
EXECUTE ANY
PROCEDURE
Executer
toutes les procedures ou functions dans tous les schema.
PROFILE
CREATE PROFILE
Créer
des profils.
ALTER PROFILE
Modifier
tous les profils dans la base de données.
DROP PROFILE
Supprimer
tous les profils dans la base de données.
ALTER RESOURCE
COST
Modifier
la ressource ‘cost’ dans toutes les sessions.
PUBLIC
DATABASE LINK
CREATE PUBLIC
DATABASE LINK
Créer
des database links publics.
DROP PUBLIC
DATABASE LINK
Supprimer
database links publics.
PUBLIC
SYNONYM
CREATE PUBLIC
SYNONYM
Créer
des synonyms publics.
DROP PUBLIC
SYNONYM
Supprimer
des synonyms publics.
ROLE
CREATE ROLE
Créer
des roles.
ALTER ANY
ROLE
Modifier
tous les roles dans la base de données.
DROP ANY
ROLE
Supprimer
tous les roles dans la base de données.
GRANT ANY
ROLE
Grant tous
les roles dans la base de données.
ROLLBACK
SEGMENT
CREATE ROLLBACK
SEGMENT
Créer
des rollback segments.
ALTER ROLLBACK
SEGMENT
Modifier
des rollback segments.
DROP ROLLBACK
SEGMENT
Supprimer
des rollback segments.
SESSION
CREATE SESSION
Se connecter
!!!
ALTER SESSION
faire des
ALTER SESSION .
RESTRICTED
SESSION
Se connecter
malgré un démarrage ‘RESTRICT’. (OSOPER et OSDBA donnent ce
privilege.)
SEQUENCE
CREATE SEQUENCE
crée une
sequence dans son schema.
CREATE ANY
SEQUENCE
Créer
toutes les sequences dans tous les schemas.
ALTER ANY
SEQUENCE
Modifier toutes
les sequence dans tous les schémas.
DROP ANY
SEQUENCE
Supprimer toutes
les sequence dans tous les schémas.
SELECT ANY
SEQUENCE
Reference
toutes les sequence dans tous les schémas.
SNAPSHOT
CREATE SNAPSHOT
Créer
des snapshots (clichés) dans son schema
.
(l’utilisateur doit aussi avoir le privilege CREATE TABLE.)
CREATE SNAPSHOT
Créer
des snapshots dans tous les schémas.
(
CREATE ANY TABLE nécessaire.)
ALTER SNAPSHOT
Modifier
tous les snapshots dans tous les schémas.
DROP ANY
SNAPSHOT
Supprimer
tous les snapshots dans tous les schémas.
SYNONYM
CREATE SYNONYM
créer un
synonym dans son schema.
CREATE SYNONYM
Créer
tous les synonyms dans tous les schémas.
DROP ANY
SYNONYM
Supprimer
tous les synonyms dans tous les schémas.
SYSTEM
ALTER SYSTEM
faire des
ALTER SYSTEM .
TABLE
CREATE TABLE
Créer
des tables ou des indexs dans son propre schéma
CREATE
ANY TABLE
Créer
des tables dans tous les schémas.
ALTER ANY
TABLE
Modifier
toutes les table dans tous les schémas et compiler toutes les vues dans
tous les schémas.
BACKUP ANY
TABLE
Réaliser
des exports incrémentaux.
DROP ANY
TABLE
Supprimer
ou vider toutes les table dans tous les schémas.
LOCK ANY
TABLE
Verrouiller
toutes les tables ou vues dans tous les schémas.
COMMENT ANY
TABLE
Commenter
toutes les tables, vues, ou colonnes dans son schema.
SELECT ANY
TABLE
Interroger
toutes les tables, vues, ou clichés dans tous les schémas.
INSERT ANY
TABLE
Insert rows
into toutes les table ou view dans tous les schémas.
UPDATE ANY
TABLE
Update rows
in toutes les table ou view dans tous les schémas.
DELETE ANY
TABLE
Delete rows
from toutes les table ou view dans tous les schémas.
TABLESPACE
CREATE TABLE
SPACE
Créer tablespaces;
add files to the operating system via Oracle, regardless of the l’utilisateur’s
operating system privileges.
ALTER TABLESPACE
Modifier tablespaces;
add files to the operating system via Oracle, regardless of the l’utilisateur’s
operating system privileges.
MANAGE TABLESPACE
Take toutes
les tablespace offline, bring toutes les tablespace online, et begin et
end backups of toutes les tablespace.
DROP TABLESPACE
Supprimer tablespaces.
UNLIMITED
TABLESPACE
Use an unlimited
amount of toutes lestablespace. This privilege overrides toutes les
specific quotas assigned. If revoked, the grantee’s schema objects remain
but further tablespace allocation is denied unless allowed by specific tablespace
quotas. This system privilege can be granted only to l’utilisateurs et
not to roles. In general, specific tablespace quotas are assigned instead
of granting this system privilege.
TRANSACTION
FORCE TRANSACTION
Fouce the
commit ou rollback of own in-doubt distributed transaction in the local
database.
FORCE ANY
TRANSACTION
Fouce the
commit ou rollback of toutes les in-doubt distributed transaction in the
local database.
TRIGGER
CREATE TRIGGER
crée un trigger
in own schema.
CREATE ANY
TRIGGER
Créer toutes
les trigger dans tous les schémas associated with toutes les table dans
tous les schémas.
ALTER ANY
TRIGGER
Enable, disable,
ou compile toutes les trigger dans tous les schémas.
DROP ANY
TRIGGER
Supprimer toutes
les trigger dans tous les schémas.
USER
CREATE ANY
USER
Créer l’utilisateurs;
assign quotas on toutes les tablespace, set default et tempouary
tablespaces, et assign a profile as part of a CREATE USER statement.
BECOME ANY
USER
Become another
l’utilisateur. (Required by toutes les l’utilisateur perfouming a full database
impout.)
ALTER USER
Modifier other
l’utilisateurs: change toutes les l’utilisateur’s passwoud ou authentication
method, assign tablespace quotas, set default et tempouary tablespaces,
assign profiles et default roles, in an ALTER USER statement. (Not required
to alter own passwoud.)
DROP USER
Supprimer another
l’utilisateur.
VIEW
CREATE VIEW
crée un view
in own schema.
CREATE ANY
VIEW
crée un view
dans tous les schémas. (Requires that l’utilisateur also have ALTER ANY
TABLE, BACKUP ANY TABLE, DROP ANY TABLE, LOCK ANY TABLE, COMMENT ANY TABLE,
SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE,
ou GRANT ANY TABLE.)
DROP ANY
VIEW
Supprimer toutes
les view dans tous les schémas.

erreur ORA-28112 failed to execute policy function

Divers, Musée des erreurs pas de Commentaire »

Ca veut bien dire ce que ca veut dire !
Une fois les policies créées,  les packages et fonctions testées, l’utilisation implicite des polices (par un SELECT sur la table sécurisée par exemple) peut renvoyer cette erreur.

Une xeception est arrivée lors de l’execution qui n’est pas ‘attrapée’ par le programme et remonte donc au noyau. Reste à savoir quelle exception et pourquoi…

Pour avoir plus d’infos, il faut voir le fichier trace *ora*.trc généré dans USER_DUMP_DEST!!!!!!
Voici un exemple de contenu de trace intéressant (chercher éventuellement la chaine ORA-28112 si le fichier est trop gros)

srv-test$ > more pprun_ora_237820.trc
Dump file /work/oracle/log/prod_ora_237820.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /oracle/10GDB
System name: AIX
Node name: sr-pprod-1
Release: 3
Version: 5
Machine: 005F0ADA4C00
Instance name: prod
Redo thread mounted by this instance: 1
Oracle process number: 23
———————————————————-
Policy function execution error:
Logon user : SYSTEM
Table/View : SC1.CONTROLE
Policy name : SC1_APP_RESTRICT
Policy function: SYSTEM.PK_SET_VPD.FN_RESTRICT
ORA-04063: package body « SYSTEM.PK_SET_VPD » has errors
ORA-06508: PL/SQL: could not find program unit being called: « SYSTEM.PK_SET_VPD »
ORA-06512: at line 1
*** 2008-09-11 11:39:48.898
———————————————————-
Policy function execution error:
Logon user : SYSTEM
Table/View : SC1.CONTROLE_RMI
Policy name : SC1_APP_RESTRICT
Policy function: SYSTEM.PK_SET_VPD.FN_RESTRICT
ORA-06508: PL/SQL: could not find program unit being called: « SYSTEM.PK_SET_VPD »
ORA-06512: at line 1

On voit qu’il ne trouve pas le package de la policy dans SYSTEM…parce qu’elle a été créé dans un autre schéma (SC1 sur cet exemple)

Jobs , queues et ordonnancement

Administration Oracle, Divers 1 Commentaire »

La 10 et la 11g offrent un nouveau package pour gérer les jobs : DBMS_SCHEDULER en remplacement de DBMS_JOB (voir en fin d’article une migration, ou un résumé des changement de l’un à l’autre extrait de la doc officielle.
Plusieurs avantages :
- une gestion des droits plus fine,
- des jobs nommés (finis les job_id peu explicites),
- une gestion des intervalles de temps simplifiée (finis les calculs savants avec SYSDATE mais une frequence horaire, hebdomadaire, mensuelle,…)

Prerequis

Qu’il existe au moins une JOB QUEUE et le process associé

SQL> SHOW PARAMETER JOB

ou

SQL> select name
from v$bgprocess
where name like ‘CJQ%’

Le user créateur du JOB doit aussi avoir le privilège system ‘CREATE JOB’ …ou etre DBA

Infos minimales pour créer / planifier un JOB

Job Name: le nom du job (unique / aux noms d’objets)
Job Type: 3 types possibles : PLSQL_BLOCK, STORED_PROCEDURE, EXECUTABLE. .
Job Action: un bloc, un nom de proc, ou un chemin complet d’executable
Start Date: date de debut (la 1ere fois ou le job s’execute , format : TIMESTAMP WITH TIME ZONE
Enabled: TRUE ou FALSE. Attention !!! par default le job est créé DISABLED
Repeat Interval: intervalle de répétition. On peut utiliser l’aritmetique de date ‘old style’ comme avec DBMS_JOB, ou préférablement la notion de fréquence :
exemple :FREQ=WEEKLY;BYDAY=MON,THU;BYHOUR=18;BYMINUTE=0
les valeurs possibles sont BYMONTH, BYMONTHDAY, BYYEARDAY, BYDAY, BYHOUR, BYMINUTE, BYWEEKNO

On peut facilement tester l’execution en lancant le Job sans attendre…

exec DBMS_SCHEDULER.run_job (job_name => ‘job_dd’);

Création d’un Job

On utilise le package DBMS_SCHEDULER et la procédure CREATE_JOB

BEGIN
DBMS_SCHEDULER.create_job (
job_name => ‘job_dd’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘BEGIN NULL; /* a fe ren */ END;’,
start_date => SYSTIMESTAMP,
repeat_interval => ‘FREQ=DAILY’,
enabled => TRUE,
comments => ‘c est pas obligatoire mais un bon commentaire…’);
END;
/

– SUPPRESSION

exec DBMS_SCHEDULER.drop_job (job_name => ‘job_dd’ );

remarque : si l’on ne veut pas supprimer un JOB définitivement mais simplement empechere son activation pendant un certain temps, on peut mettre son statut à DISABLED :

dbms_scheduler.disable

Des infos dans le dictionnaire ?

A minima

SQL> select job_name, enabled
from user_scheduler_jobs;

ou avec un peu plus de détails sur la planification

SQL> select JOB_NAME , JOB_CREATOR , JOB_TYPE , JOB_ACTION , STATE ,
START_DATE , REPEAT_INTERVAL , LAST_START_DATE , NEXT_RUN_DATE
FROM USER_SCHEDULER_JOBS

JOB_NAME JOB_CREATOR JOB_TYPE JOB_ACTION STATE START_DATE REPEAT_INTERVAL NEXT_RUN_DATE
- – - – - – - – - – - – - – - – - – - – - – - – - – - – - -
JOB_DD SYSTEM PLSQL_BLOCK BEGIN NULL;END; SCHEDULED 08-SEP-08 11.00.03 FREQ=DAILY 09-SEP-08 11.00.03

mais aussi les vues
DBA_SCHEDULER_SCHEDULES – informations sur les jobs planifiés courants
DBA_SCHEDULER_PROGRAMS , DBA_SCHEDULER_PROGRAM_ARGS – informations sur les programmes

De DBMS_JOB à DBMS_SCHEDULER

créer un Job

VARIABLE jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT(:jobno, ‘INSERT INTO employees VALUES (7935,  »SALLY »,
 »DOGAN »,  »sally.dogan@xyzcorp.com », NULL, SYSDATE,  »AD_PRES », NULL,
NULL, NULL, NULL);’, SYSDATE, ‘SYSDATE+1′);
COMMIT;
END;
/

equivalent statement using DBMS_SCHEDULER is the following:

BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => ‘job1′,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘INSERT INTO employees VALUES (7935,  »SALLY »,
 »DOGAN »,  »sally.dogan@xyzcorp.com », NULL, SYSDATE, »AD_PRES », NULL,
NULL, NULL, NULL);’);
start_date => SYSDATE,
repeat_interval => ‘FREQ = DAILY; INTERVAL = 1′);
END;
Modifier un Job

BEGIN
DBMS_JOB.WHAT(31, ‘INSERT INTO employees VALUES (7935,  »TOM »,  »DOGAN »,
 »tom.dogan@xyzcorp.com », NULL, SYSDATE, »AD_PRES », NULL,
NULL, NULL, NULL);’);
COMMIT;
END;
/

equivalent à

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => ‘JOB1′,
attribute => ‘job_action’,
value => ‘INSERT INTO employees VALUES (7935,  »TOM »,  »DOGAN »,
 »tom.dogan@xyzcorp.com », NULL, SYSDATE,  »AD_PRES », NULL,
NULL, NULL, NULL);’);
END;
/

supprimer un Job de la Job Queue

BEGIN
DBMS_JOB.REMOVE(14144);
COMMIT;
END;
/

équivallent à

BEGIN
DBMS_SCHEDULER.DROP_JOB(‘myjob1′);
END;
/