Oracle How to
De Wikifago.
Déconnecter les utilisateurs d'un schéma
select sid,serial# from v$session where username = '<your_schema>' alter system kill session '<sid>,<serial#>'
Exemple : alter system kill session '39,1232';
Tablespace
Lister les tablespace TEMP avec le compte system :
select * from dba_temp_files;
Lister tous les tablespace avec le compte system :
select * from dba_data_files;
Ajouter un fichier au tablespace TEMP :
ALTER TABLESPACE TEMP ADD TEMPFILE SIZE 32767M AUTOEXTEND ON NEXT 1G MAXSIZE 32767M;
Créer une copie de table
create table schema.TABLE_copie as select * from schema.TABLE_A_COPIER;
Data pump
Voir Oracle data pump
Importer un schéma oracle d'un dump fait avec datapump Le DIRECTORY est le nom d'un répertoire Oracle à créer avant l'import. C'est un nom qui pointe vers un répertoire physique, dans lequel doit etre placé le fichier fichierDump.dmp
impdp system/pwdSystem@instanceOracle dumpfile=fichierDump.dmp full=n SCHEMAS=shemaAImporter DIRECTORY=repertoireOracle logfile=imp.log
Importer un shéma oracle d'un dump fait avec datapump sur un autre Schéma
impdp system/pwdSystem@instanceOracle dumpfile=fichierDump.dmp full=n SCHEMAS=shemaAImporter REMAP_SCHEMA=shemaAImporter:shemaDestination DIRECTORY=repertoireOracle logfile=imp.log
Exemple de fichier bash, pouvant etre appelé par crontab
#!/bin/sh export PATH=/logiciels/oracle/product/10gR2/bin:/home/weblogic/bea/jdk150_12/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin export HOME=/home/oracle export ORACLE_HOME=/logiciels/oracle/product/10gR2 export OLDPWD=/home/oracle env > /home/oracle/admgen/env.log cd /home/oracle/admgen /logiciels/oracle/product/10gR2/bin/expdp system/pwdSystem@instanceOracle dumpfile=instance.dmp full=y logfile=instance.log parfile=datapump.par # le direcctory des dump est /data/oracle/dumps/ cd /data/oracle/dumps/ tar cfz DUMP_$(date +%Y_%m_%d__%H_%M_%S)_instance.tgz instance.dmp tar cfz LOG_$(date +%Y_%m_%d__%H_%M_%S)_instance.tgz instance.log rm instance.log instance.dmp
Contenu du fichier de paramètre du datapump : datapump.par
directory=repertoireOracle exclude=SCHEMA:"in('SYSTEM','SYSMAN','OUTLN')"
Exemple de script sql permettant de creer un utilisateur ayant les droits en lecture seule sur toute les tables d'un utilisateur
/*============================================================================*/ /* Cree un utilisateur avec les doits en lecture seul sur les tables du */ /* du proprietaire */ /*============================================================================*/ ACCEPT proprio CHAR PROMPT '- Nom de l utilisateur existant et proprietaire des tables COLT : ' ACCEPT user_read CHAR PROMPT '- Nom du nouvel utilisateur qui aura acces en lecture seule des tables de &proprio : ' ACCEPT user_read_pass CHAR PROMPT '- Mot de passe du nouvel utilisateur &user_read : ' ACCEPT role_read CHAR PROMPT '- Nom du nouveau role pour la lecture seule sur les tables de &proprio : ' DROP ROLE &role_read; CREATE ROLE &role_read; DROP USER &user_read CASCADE; CREATE USER &user_read identified BY &user_read_pass; GRANT CONNECT TO &user_read; GRANT &role_read TO &user_read; DECLARE STR VARCHAR2(1024); CURSOR GET_TABLES IS -- liste des tables du proprio Select TABLE_NAME from all_tables WHERE OWNER = '&proprio'; BEGIN FOR NOMTABLES IN GET_TABLES LOOP STR:='GRANT SELECT ON "&proprio".' || NOMTABLES.TABLE_NAME || ' TO &role_read '; EXECUTE IMMEDIATE STR; STR:='CREATE SYNONYM "&user_read".' || NOMTABLES.TABLE_NAME || ' FOR &role_read.' || NOMTABLES.TABLE_NAME; EXECUTE IMMEDIATE STR; END LOOP; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('-- ERREUR lors de la creation de l utilisateur en lecture seul '); DBMS_OUTPUT.PUT_LINE('-- ' || SQLERRM); END; /
Exemple de script pour exporter un shéma dans un dump (oracle 10 et +)
#!/bin/bash if [ $(whoami) != "oracle" ] then echo Ce script doit etre lance avec la compte oracle exit 1 fi read -p "Shema oracle a exporter : " USER_SRC expdp system/<password>@SID schemas=$USER_SRC directory=dumps dumpfile=${USER_SRC}_$(date +%Y_%m_%d__%H_%M_%S).dmp logfile=${USER_SRC}_$(date +%Y_%m_%d__%H_%M_%S).log
Exemple de script pour dupliquer un shéma à partir d'un dump
#!/bin/bash if [ $(whoami) != "oracle" ] then echo Ce script doit etre lance avec la compte oracle exit 1 fi export REP_DUMP=/home/oracle/dumps cd $REP_DUMP echo echo Liste des dumps dispos dans $REP_DUMP : ls -lrt *.dmp echo read -p "Fichier de dump :" DUMP if [[ ! -a $REP_DUMP/${DUMP} ]] then echo Le fichier de dump : $REP_DUMP/${DUMP} n existe pas essayer a nouveau ! read -p "Fichier de dump :" DUMP if [[ ! -a $REP_DUMP/${DUMP} ]] then echo echo Le fichier de dump : $REP_DUMP/${DUMP} n existe pas ! exit 1 fi fi read -p "Shema oracle source :" USER_SRC read -p "Shema oracle destination :" USER_DEST echo echo copie du shema $USER_SRC vers $USER_DEST impdp system/<password>@<SID> dumpfile=$DUMP exclude=statistics full=n SCHEMAS=$USER_SRC REMAP_SCHEMA=$USER_SRC:$USER_DEST DIRECTORY=dumps logfile=$USER_SRC_to_$USER_DEST.log echo echo Application du mot de passe $USER_DEST l utilisateur $USER_DEST echo "alter user $USER_DEST identified by $USER_DEST;" > alterUser.sql echo "exit" >> alterUser.sql sqlplus system/<password>@<SID> @alterUser.sql echo echo FIN DE LA COPIE. exit 0
Exemple de script pour supprimer des lignes orphelines par paquet pour soulager le UNDO Puis creer un contrainte externe pour empecher de nouvelles lignes orphelines. Puis récupérer l'espace libéré dans la table pour le remettre à disposition du tablespace.
-- ATTENTION A LANCER AVEC L UTILISATEUR PROPRIETAIRE DE LA TABLE ENFANT -- exemple : sqlplus user/password@INSTANCE @nettoyageENFANT.sql; select 'Debut nettoyage de la table ENFANT : ' || to_char(sysdate, 'DD/MM/YYYY HH24:Mi:ss') from dual; select 'Nombre de lignes initiales : ' || count(1) from ENFANT; select 'Ajout de la colonne UTILE' from dual; ALTER TABLE ENFANT ADD UTILE NUMBER(1); select 'Ajout de la fonction NETTOIE' from dual; create or replace FUNCTION NETTOIE RETURN NUMBER IS nb NUMBER :=0; BEGIN UPDATE ENFANT SET UTILE = 1 WHERE EXISTS (SELECT 1 FROM PARENT p WHERE ENFANT.nom = p.nom AND ENFANT.parent_id = p.id ); commit; LOOP delete from ENFANT where UTILE is null AND ROWNUM <= 500; nb := SQL%ROWCOUNT; COMMIT; EXIT WHEN nb = 0; END LOOP; RETURN nb; END; / select 'Appel de la fonction NETTOIE' from dual; / DECLARE v_Return NUMBER; BEGIN v_Return := NETTOIE(); END; / select 'Suppression de la fonction NETTOIE' from dual; drop FUNCTION NETTOIE; select 'Suppression de la colonne UTILE' from dual; alter table ENFANT drop column UTILE; select 'Fin de nettoyage ENFANT : ' || to_char(sysdate, 'DD/MM/YYYY HH24:Mi:ss') from dual; select 'Nombre de lignes restantes : ' || count(1) from ENFANT; select 'Ajout de la contrainte FK_ENFANT_REFERENCE_PARENT' from dual; alter table ENFANT add constraint FK_ENFANT_REFERENCE_PARENT foreign key (NOM, PARENT_ID) references PARENT (NOM, ID) on delete cascade; select 'Recuperation de l espace libere par le tablespace ' || to_char(sysdate, 'DD/MM/YYYY HH24:Mi:ss') from dual; alter table ENFANT enable row movement; ALTER TABLE ENFANT SHRINK SPACE CASCADE; select 'Fin du traitement ' || to_char(sysdate, 'DD/MM/YYYY HH24:Mi:ss') from dual;
Exemple de requête permettant de splitter une chaine de charactère selon le séparateur ",". Ainsi 'aa,bb,cc' -> donne trois lignes : aa,bb et cc.
select to_number(regexp_substr('aa,bb,cc','[^,]+', 1, level)) from dual connect by regexp_substr('aa,bb,cc', '[^,]+', 1, level) is not null;
Procédure d'écriture de trace hors transaction pour debug PL/SQL
create table trace ( message VARCHAR2(50) not null, DATE_MAJ DATE default sysdate not null); CREATE OR REPLACE PROCEDURE message (p_mess in varchar2) IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN insert into TRACE (message) values (p_mess); commit; END message; -- Utilisation DECLARE BEGIN MESSAGE('coucou'); END;
Exemple de Insert de select
insert into TABL1 t_cible ( ID, VALEUR1, DATE1) select HIBERNATE_SEQUENCE.NEXTVAL, t_origin.VALEUR1, add_months(t_origin.DATE1,12) from TABL1 t_origin JOIN variable v ON t_origin.PROD = v.PROD and t_origin.ID = v.ID where t_origin.ID = 54 and (( DATE1 >= TO_DATE('01-01-2035','DD-MM-YYYY') and DATE1 < TO_DATE('01-01-2036','DD-MM-YYYY') and v.QUALIT != 'S' ) or ( DATE1 >= TO_DATE('31-12-2034','DD-MM-YYYY') and DATE1 < TO_DATE('31-12-2035','DD-MM-YYYY') and v.QUALIT = 'S' ))
Estimer la taille occupée par les données d'un Schéma par tablespace
SELECT OWNER AS "SCHEMA", TABLESPACE_NAME AS "TABLESPACE", Sum(BYTES) / 1024 / 1024 AS "TAILLE (Mb)" FROM DBA_EXTENTS where OWNER = 'COLT_RECETTE' GROUP BY OWNER, TABLESPACE_NAME ORDER BY OWNER, TABLESPACE_NAME
Créer une vue qui génère tout les jours entre 2 dates
CREATE OR REPLACE VIEW V_JOUR (jour) AS WITH calendrier as ( SELECT (to_date('01-01-2000','DD-MM-YYYY') + level -1) AS jour FROM dual CONNECT BY LEVEL <= (to_date('01-01-2050','DD-MM-YYYY') - to_date('01-01-2000','DD-MM-YYYY') +1 ) ) select jour from calendrier;
Générer tout les mois entre 2 dates
SELECT ADD_MONTHS(TO_DATE('201301', 'YYYYMM'), LEVEL - 1) MONTHS FROM DUAL CONNECT BY LEVEL <= MONTHS_BETWEEN(TO_DATE('201708', 'YYYYMM'), TO_DATE('201301', 'YYYYMM'));
Détecter les chevauchements de périodes
https://blog.developpez.com/philben/p10875/sql-access/chevauchement_de_periodes