Oracle How to

De Wikifago.

Sommaire

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

Outils personnels