Migration 11.2.0.4 vers 12.2.0.1 (en ligne de commande)
http://www.br8dba.com/tag/upgrade-oracle-database-manually-from-11-2-0-4-to-12-2-0-1/
Oracle12C,19C - Page 6
-
-
Tracer sql query dans base oracle
• Pour tracer les requêtes SQL dans une base Oracle il faut passer la commande :
execute dbms_monitor.database_trace_enable(waits=>TRUE,binds=>TRUE,instance_name=>'QUALIF');
• Pour stopper la trace :
execute dbms_monitor.database_trace_disable(instance_name=>'QUALIF');
• La trace sera stockée sous :
select
value
from
v$diag_info
where
name ='Default Trace File';
Exemple :
/export/BD/ORACLE/oratrc/QUALIF/bdump/diag/rdbms/qualif/QUALIF/trace/QUALIF_ora_58689.trc
Elle pourra être lisible avec tkprof :
Syntaxe : tkprof <nom_de_la_trace> <nom_du_fichier_texte_lisible> sys=no
Exemple:
$tkprof QUALIF_ora_7767.trc QUALIF_ora_7767.txt sys=no
L’option sys = no ne garde pas les requêtes lancées par l’utilisateur sys. -
Trouver les clés étrangères qui font référence à un champ dans une table x
--donne la liste des clés étrangères
select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name
from all_constraints
where constraint_type='R' and R_OWNER='nom_du_schéma'
and r_constraint_name in (select constraint_name
from all_constraints
where constraint_type in ('P','U')
and table_name='nom_de_la_table'); -
Comparer deux schémas Oracle
compare.sh
#!/bin/sh
# Exemple : sh compare.sh 'SCHEMA1' 'SCHEMA2' 'DEV'
# Modifier la valeur de ORACLE_SID si besoin
export ORACLE_SID=DEV
SCHEMA1=$1
SCHEMA2=$2
DBLINK=$3
echo "Schema1="$SCHEMA1" ; Schema2="$SCHEMA2"@"$DBLINK
sqlplus / as sysdba <<-_EOF
@COMP1.sql $SCHEMA1 $SCHEMA2 $DBLINK;
exit;
_EOFCOMP1.sql
-- Au prealable on a cree un repertoire pour les scripts generes :
-- CREATE DIRECTORY SCRIPTS_GENERES as '/export/home/oracle/SCRIPTS_GENERES/'
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
CURSOR c1 IS
select table1, table2 from
(select table_name table1 from dba_tables where owner='&1') t1
full outer join
(select table_name table2 from dba_tables@&3 where owner='&2') t2
on t1.table1=t2.table2
where (table1 is null and table2 not like 'BIN$%') or (table2 is null and table1 not like 'BIN$%')
order by table1, table2;
CURSOR c2 IS
select table1, column1, table2, column2 from
(select table_name table1, column_name column1
from dba_tab_columns
where owner='&1'
and table_name not in (select view_name from dba_views where owner='&1')) t1
full outer join
(select table_name table2, column_name column2 from dba_tab_columns@&3
where owner='&2'
and table_name not in (select view_name from dba_views@&3 where owner='&2')) t2
on t1.table1=t2.table2 and t1.column1=t2.column2
where (column1 is null and table2 not like 'BIN$%') or (column2 is null and table1 not like 'BIN$%')
order by table1, table2, column1, column2;
CURSOR c3 is
select table1, column1, type1, length1, precision1, scale1, table2, column2, type2, length2, precision2, scale2
from
(select table_name table1, column_name column1, data_type type1, data_length length1, data_precision precision1, data_scale scale1
from dba_tab_columns
where owner='&1'
and table_name not in (select view_name from dba_views where owner='&1')) t1
full outer join
(select table_name table2, column_name column2, data_type type2, data_length length2, data_precision precision2, data_scale scale2
from dba_tab_columns@&3
where owner='&2'
and table_name not in (select view_name from dba_views@&3 where owner='&2')) t2
on t1.table1=t2.table2 and t1.column1=t2.column2
where type1<>type2
or length1<>length2
or precision1<>precision2
or scale1<>scale2
or (type1 is not null and precision1 is null and precision2 is not null)
or (type2 is not null and precision2 is null and precision1 is not null)
order by table1, table2, column1, column2;
CURSOR c4 IS
select table1, table2 from
(select view_name table1 from dba_views where owner='&1') t1
full outer join
(select view_name table2 from dba_views@&3 where owner='&2') t2
on t1.table1=t2.table2
where (table1 is null and table2 not like 'BIN$%') or (table2 is null and table1 not like 'BIN$%')
order by table1, table2;
CURSOR c5 IS
select v1.view_name table1, v1.text_length length1, v2.view_name table2, v2.text_length length2
from dba_views v1
full outer join dba_views@&3 v2
on v1.owner=v2.owner and v1.view_name=v2.view_name
where v1.text_length<>v2.text_length
and v1.owner='&1'
and v2.owner='&2';
filesortie UTL_FILE.FILE_TYPE;
table1 varchar2(50);
table2 varchar2(50);
column1 varchar2(50);
column2 varchar2(50);
type1 varchar2(50);
type2 varchar2(50);
length1 varchar2(10);
length2 varchar2(10);
precision1 varchar2(10);
precision2 varchar2(10);
scale1 varchar2(10);
scale2 varchar2(10);
BEGIN
filesortie:= UTL_FILE.FOPEN('SCRIPTS_GENERES', 'resultat_comparaison.csv', 'W', 10000);
UTL_FILE.PUT_LINE(filesortie,'Schema 1;Schema 2');
UTL_FILE.NEW_LINE(filesortie);
UTL_FILE.PUT_LINE(filesortie,'Tables schema 1;Tables schema 2');
OPEN c1;
LOOP
FETCH c1 INTO table1, table2;
EXIT WHEN c1%NOTFOUND;
IF table1 is not null THEN
UTL_FILE.PUT_LINE(filesortie,table1||';_');
ELSIF table2 is not null THEN
UTL_FILE.PUT_LINE(filesortie,'_;'||table2);
END IF;
END LOOP;
CLOSE c1;
UTL_FILE.NEW_LINE(filesortie);
UTL_FILE.PUT_LINE(filesortie,'Colonnes schema 1;Colonnes schema 2');
OPEN c2;
LOOP
FETCH c2 INTO table1, column1, table2, column2;
EXIT WHEN c2%NOTFOUND;
IF column1 is not null THEN
UTL_FILE.PUT_LINE(filesortie,table1||'('||column1||');_');
ELSIF column2 is not null THEN
UTL_FILE.PUT_LINE(filesortie,'_;'||table2||'('||column2||')');
END IF;
END LOOP;
CLOSE c2;
UTL_FILE.NEW_LINE(filesortie);
UTL_FILE.PUT_LINE(filesortie,'Types colonnes schema 1;Types colonnes schema 2');
OPEN c3;
LOOP
FETCH c3 INTO table1, column1, type1, length1, precision1, scale1, table2, column2, type2, length2, precision2, scale2;
EXIT WHEN c3%NOTFOUND;
UTL_FILE.PUT(filesortie,table1||'('||column1||') : '||type1);
UTL_FILE.PUT(filesortie,'('||length1||'/'||precision1||'/'||scale1||')');
UTL_FILE.PUT(filesortie,';');
UTL_FILE.PUT(filesortie,table2||'('||column2||') : '||type2);
UTL_FILE.PUT(filesortie,'('||length2||'/'||precision2||'/'||scale2||')');
UTL_FILE.NEW_LINE(filesortie);
END LOOP;
CLOSE c3;
UTL_FILE.NEW_LINE(filesortie);
UTL_FILE.PUT_LINE(filesortie,'Vues schema 1;Vues schema 2');
OPEN c4;
LOOP
FETCH c4 INTO table1, table2;
EXIT WHEN c4%NOTFOUND;
IF table1 is not null THEN
UTL_FILE.PUT_LINE(filesortie,table1||';_');
ELSIF table2 is not null THEN
UTL_FILE.PUT_LINE(filesortie,'_;'||table2);
END IF;
END LOOP;
CLOSE c4;
UTL_FILE.NEW_LINE(filesortie);
UTL_FILE.PUT_LINE(filesortie,'Longueur texte vues schema 1;Longueur texte vues schema 2');
OPEN c5;
LOOP
FETCH c5 INTO table1, length1, table2, length2;
EXIT WHEN c5%NOTFOUND;
UTL_FILE.PUT_LINE(filesortie,table1||' : '||length1||';'||table2||' : '||length2);
END LOOP;
CLOSE c5;
UTL_FILE.FCLOSE(filesortie);
DBMS_OUTPUT.PUT_LINE('Fin de l''execution du script');
DBMS_OUTPUT.PUT_LINE('Le resultat de la comparaison a ete genere dans le dossier /export/home/oracle/SCRIPTS_GENERES/');
END;
/ -
Mise en trace oracle , Tuning avec TKPROF
Pour tracer les requêtes SQL dans une base Oracle il faut passer la commande :
execute dbms_monitor.database_trace_enable(waits=>TRUE,binds=>TRUE,instance_name=>'QUALIF');
Pour stopper la trace :
execute dbms_monitor.database_trace_disable(instance_name=>'QUALIF');
La trace sera stockée sous :
/export/BD/ORACLE/oratrc/QUALIF/bdump/diag/rdbms/qualif/QUALIF/trace/
Elle pourra être lisible avec tkprof :
Syntaxe : tkprof <nom_de_la_trace> <nom_du_fichier_texte_lisible> sys=no
Exemple:
$tkprof QUALIF_ora_7767.trc QUALIF_ora_7767.txt sys=no
L’option sys = no ne garde pas les requêtes lancées par l’utilisateur sys.source : http://www.dbrev.com/dbBlog/2014/12/31/how-to-enable-various-level-of-trace-in-oracle-11g-database/
tuning avec tkprof : https://oracle.developpez.com/guide/tuning/tkprof/
-
oradebug
http://what-when-how.com/Tutorial/topic-16mglm7e/Secrets-of-the-Oracle-Database-520.html
-
last access table oracle
select a.obj#,a.table_scans_delta,b.object_name,b.owner,b.object_type from dba_hist_seg_stat a, dba_objects b where a.obj# = b.object_id and b.owner like 'USERNAME%' order by table_scans_total desc
-
Erreur EXP-00008, ORA-04063, ORA-06508, EXP-00083 PL/SQL: could not find program unit being called: "WMSYS.LTUTIL"
Alors lorsque je faisais un export classique avec exp, j'avais cette erreur là :
EXP-00008, ORA-04063, ORA-06508, EXP-00083 PL/SQL: could not find program unit being called: "WMSYS.LTUTIL"
Je suis tombé sur cette page :
http://olashowunmi.blogspot.fr/2015/06/exp-00008-ora-04063-ora-06508-exp-00083.html
Qui ne m'a pas aidé.
Par contre en cherchant bien je suis tombé sur ça :
http://surachartopun.com/2009/11/invalid-objects-on-sys-and-ora-04063.html
et cela a résolu, mon pb.
-
oratab -> dbhome
Tout est dans le titre...
Si vous ne paramétrez pas correctement votre oratab , la commande dbhome $ORACLE_SID retourne de la merde !
-
Suppression d'object sys
Il se peut que vous ayez créé sans le vouloir des objets dans le schéma sys.
Pour les supprimer faites comme cela :
select 'drop procedure '||object_name||' ;' from dba_objects
where
to_date(created) ='19/10/17' and
object_type='PROCEDURE'
and owner='SYS';