10 septembre 2007
Tips Oracle
========== fonctionnement pour obtenir un Latch ============
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:409308200346402947
========== Tom Kyte ses debuts =======
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:13253348444918
Livre sur la théorie des bases de données :
Transaction Processing: Concepts and Techniques (The Morgan Kaufmann Series in Data Management Systems) (Hardcover)
by Jim Gray (Author), Andreas Reuter (Author) "Six thousand years ago, the Sumerians invented writing for transaction processing..." (more)
========== option noredo du recover de RMAN ==========
pour les bases en noarchivelog
voir le point Restore and Recovery of NOARCHIVELOG Databases
du livre
Oracle Database Backup and Recovery Advanced User's Guide
10g Release 2 (10.2)
Part Number B14191-01
========== commande RMAN ========
Doc Oracle de référence sur les commandes RMAN :
Oracle Database Backup and Recovery Reference
========== RMAn recovery Bloc =========
file:///home/aca/www/doc_oracle/doc10gR2/backup.102/b14191/rcmconc2003.htm#sthref409
partie
Block Media Recovery with RMAN
du livre Database Backup and Recovery Advanced User's Guide
========== Tuning SQL AskTom ===========
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8764517459743
Surtout analyser le fond du problème, c'est à dire quelle la question à laquelle on se doit de répondre ?
========== explication wait event total donne temps infini =======
voir thread : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:389726200346914112
You Asked
Tom - here are 2 small parts of a 1 hour statspack snapshot - question below.
This is a 9204 database.
Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- --------- -------------------
Begin Snap: 12835 14-Jun-07 15:00:01 378 3.6
End Snap: 12837 14-Jun-07 16:00:01 384 4.3
Elapsed: 60.00 (mins)
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 19,612 35.04
latch free 899,688 14,371 25.68
db file scattered read 16,333,411 13,105 23.42
SQL*Net message from dblink 439,535 4,440 7.93
direct path write 405,226 1,509 2.70
So this snapshot is 1 hour..
that is 3600 seconds. This is on a 8 cpu box. so that is 28,800 seconds?
How can 19,612 seconds be 35% of total elapased time? How can the total 5 events add up
to over 50,000 seconds?
and we said...
In one hour on an 8 cpu machine, you have 28,800 cpu seconds yes.
Now, in that report, you have a total of:
ops$tkyte%ORA10GR2> select 19612+14371+13105+4440+1509 from dual;
19612+14371+13105+4440+1509
---------------------------
53037
53,037 seconds - 35% of that is 19,612 - so that 35% just means "19,612 seconds was 35% of the sum of all of these times"
It is not total ELAPSED time - it is just "time" (if these were all WAITS, it would be more relevant - this just says you spent MOST of your time on the cpu doing something - probably spinning on latches given that your biggest wait was "latch free" which means you spun 2,000 times in a loop and then went to sleep to try again for the latch later).
the wait events could be infinite in duration (not really, but really really big is not out of the question).
If you have a 1 minute snapshot
And you have 1,000 users logged in
and one of them has issued "lock table t in exclusive mode"
and the rest try to insert into that table
then in one minute you will observe 999 minutes of elapsed wait time on enqueue waits as 999 sessions sit there concurrently and wait and wait and wait.
So, don't forget these things are cumulative as well! Over all sessions.
========== bloc corrompu RMAN ============
Detecting Physical and Logical Block Corruption
RMAN depends upon database server sessions to perform backups, and the database server can detect many types of physically corrupt blocks during the backup process. Each new corrupt block not previously encountered in a backup is recorded in the control file and in the alert.log. By default, error checking for physical corruption is enabled.
At the end of a backup, RMAN stores the corruption information in the recovery catalog and control file. Access this data using the V$DATABASE_BLOCK_CORRUPTION view.
========== sauvegarde automatique du fichier de control =============
Control File Autobackups After Database Structural Changes
The control file is also automatically backed up after database structural changes such as adding a new tablespace, altering the state of a tablespace or datafile (for example, bringing it online), adding a new online redo log, renaming a file, adding a new redo thread, and so on. Losing this information would compromise your ability to recover the database.
This backup is performed by the server process itself, rather than one of the RMAN channels. This type of autobackup, unlike autobackups that occur after a successful backup, is always created on disk. You can use CONFIGURE CONTROLFILE AUTOBACKUP FOR DEVICE TYPE DISK to set the location for this disk based control file autobackup. Note that a failure of the automatic control file autobackup after a structural change never causes the associated structural change to fail. For example, if you add a datafile, and if the resulting control file autobackup fails, then the datafile addition is still successful.
========== delete/drop database ================
8.5 Deleting a Database with RMAN
You may need to remove a database from the operating system. For example, you create a test database and then no longer have a use for it. In such a situation, use the DROP DATABASE command from within RMAN, or the DROP DATABASE statement in SQL*Plus.
DROP DATABASE requires that RMAN be connected to the target database, and that the target database be mounted. The command does not require connection to the recovery catalog. If RMAN is connected to the recovery catalog, and if you specify the option INCLUDE COPIES AND BACKUPS, then RMAN also unregisters the database.
To drop a database:
Connect RMAN to the target database and (optionally) recovery catalog. For example:
rman TARGET / CATALOG rman/rman@catdb
Catalog all backups that are associated with the database. For example, the following commands catalogs files in the flash recovery area, and then in a secondary archiving destination:
RMAN> CATALOG START WITH '+disk1'; # all files from flash recovery area
# (stored on ASM disk)
RMAN> CATALOG START WITH '/arch_dest2'; # all files from second arch dest
Delete all backups and copies associated with the database. For example:
RMAN> DELETE BACKUPSET; # deletes all backups
RMAN> DELETE COPY; # delete all image copies (including archived logs)
Remove the database from the operating system (and automatically unregister it from the recovery catalog if you are connected to the catalog). For example:
DROP DATABASE; # delete all database files and unregister the database
========== identifier les fichiers à restaurer ==================
Query the V$DATAFILE_HEADER view to determine the status of your datafiles. Run the following SQL statements to check the datafile headers:
COL FILE# FORMAT 999
COL STATUS FORMAT A7
COL ERROR FORMAT A10
COL TABLESPACE_NAME FORMAT A10
COL NAME FORMAT A30
SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME
FROM V$DATAFILE_HEADER
WHERE RECOVER = 'YES' OR (RECOVER IS NULL AND ERROR IS NOT NULL);
========== RMAN recover en limitant l'espace occuppé par les archives =========
RMAN> RECOVER DATABASE DELETE ARCHIVELOG MAXSIZE 25M;
====================== BACKUP database with RMAN ===================
Sauvergarde consistente et inconsistente :
4.3.1 Making Consistent and Inconsistent Backups with RMAN
A consistent backup of the database is one taken when the database is in a consistent state, that is, one taken after the database has been shut down normally (using SHUTDOWN NORMAL, SHUTDOWN IMMEDIATE or SHUTDOWN TRANSACTIONAL). At this point, all changes in the redo log have been applied to the datafiles. If you mount the database and take a backup at this point, then you can restore the database from this backup at a later date and open it without performing media recovery.
Any backup taken when the database has not been shut down normally is an inconsistent backup. When a database is restored from an inconsistent backup, Oracle must perform media recovery before the database can be opened, applying any pending changes from the redo logs.
As long as your database is running in ARCHIVELOG mode, and you back up your archived redo log files as well as your datafiles, inconsistent backups can be the foundation for a sound backup and recovery strategy. Inconsistent backups are an important part of the backup strategy for most databases, because they offer superior availability. For example, backups taken while the database is still open are inconsistent backups.
Doc Oracle 10gR2.
4.3.2 Making Whole Database Backups with RMAN
You can perform whole database backups with the database mounted or open. To perform a whole database backup, from the RMAN prompt, use the BACKUP DATABASE command. The simplest form of the command requires no parameters, as shown in this example:
RMAN> BACKUP DATABASE;
This example shows the procedure for taking a whole database backup to the default destination:
RMAN> BACKUP DATABASE; # uses automatic channels to make backup
RMAN> SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT'; # switches logs and archives all logs
By archiving the logs immediately after the backup, you ensure that you have a full set of archived logs through the time of the backup. This guarantees that you can perform media recovery after restoring this backup.
Sauvegarde des archive & switch de redo : doc 10gR2
4.3.7.1.1 Automatic Online Redo Log Switches During Backups of Archived Logs
When taking a backup of archived redo logs that includes the most recent log (that is, a BACKUP ... ARCHIVELOG command is run without the UNTIL or SEQUENCE option) if the database is open, then before beginning the backup, RMAN will switch out of the current online redo log group, and all online redo logs that have not yet been archived, up to and including the redo log group that was current when the command was issued. This ensures that the backup contains all redo that was generated prior to the start of the command.
4.3.7.2 Backing Up Logs with BACKUP ... PLUS ARCHIVELOG
You can add archived redo logs to a backup of other files by using the BACKUP ... PLUS ARCHIVELOG clause. Adding BACKUP ... PLUS ARCHIVELOG causes RMAN to do the following:
1 - Runs the ALTER SYSTEM ARCHIVE LOG CURRENT command.
2 - Runs BACKUP ARCHIVELOG ALL. Note that if backup optimization is enabled, then RMAN skips logs that it has already backed up to the specified device.
3 - Backs up the rest of the files specified in BACKUP command.
4 - Runs the ALTER SYSTEM ARCHIVE LOG CURRENT command.
5 - Backs up any remaining archived logs generated during the backup.
This guarantees that datafile backups taken during the command are recoverable to a consistent state.
To back up archived redo logs with BACKUP ... PLUS ARCHIVELOG:
After starting RMAN, run the BACKUP ... PLUS ARCHIVELOG command at the RMAN prompt . This example backs up the database and all archived logs:
BACKUP DEVICE TYPE sbt
DATABASE PLUS ARCHIVELOG;
====================== Flash Recovery Area FRA when delete file ============
3.5.6.1 When Files are Eligible for Deletion from the Flash Recovery Area
There are relatively simple rules governing when files become eligible for deleteion from the flash recovery area:
Permanent files are never eligible for deletion.
Files that are obsolete under the configured retention policy are eligible for deletion.
Transient files that have been copied to tape are eligible for deletion.
In a Data Guard environment, archived redo log deletion policy governs when archived redo log files can be deleted from the flash recovery area. See Oracle Data Guard Concepts and Administration for details on archived redo log deletion policy.
Note:
Exactly which of the eligible files will be deleted to satisfy a space request is unpredictable. The rules governing the selection of specific files for deletion are likely to change between releases and are dependent upon your configuration. The safe and reliable way to control deletion of files from the flash recovery area is to change your retention policy. If you wish to increase the likelihood that files moved to tape are also retained on disk to minimize expected restore and recovery times, increase the flash recovery area quota.
====================== RMAN NLS_DATE_FORMAT ================
Il faut les deux variables :
3.1.2 Setting Globalization Support Environment Variables for RMAN
Before invoking RMAN, it may be useful to set the NLS_DATE_FORMAT and NLS_LANG environment variables. These variables determine the format used for the time parameters in RMAN commands such as RESTORE, RECOVER, and REPORT.
The following example shows typical language and date format settings:
NLS_LANG=american
NLS_DATE_FORMAT='Mon DD YYYY HH24:MI:SS'
====================== RMAN RESTORE VALIDATE et VALIDATE ==================
Doc 10g
2.4.2 Validating RMAN Backups: VALIDATE and RESTORE VALIDATE
The RMAN VALIDATE and RESTORE VALIDATE commands should be part of ongoing testing of your recovery plan. VALIDATE causes RMAN to read specified backups on disk or tape and report whether they are intact and usable in a restore operation. RESTORE... VALIDATE causes RMAN to check whether the set of available backups is sufficient to restore the specified database objects. For example,RESTORE TABLESPACE TBS_1 VALIDATE selects backups sufficient to restore the named tablespace, just as RMAN does in a real restore operation, and reads the backups to ensure that they are present and not corrupted.
====================== RAC sequence ====================
http://www.pythian.com/blogs/383/sequences-in-oracle-10g-rac
====================== Realiser un switchover sur base physique ===========
Note Best practice Failover et switch over, DocID sur metalink : 387266.1
!!! Sur la BASE primaire A FAIRE EN PREMIER !!!
1) Arret/redemarrage
Vérifier qu'il n'y a plus de connexion client
2) Positionner la base principale en STANDBY
Verifier le status :
select OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS,DATAGUARD_BROKER
from v$database;
Alter database commit to switchover to physical standby without session shutdown wait;
Pour attendre la fin des session cliente :
Alter database commit to switchover to physical standby with session shutdown wait;
3) shutdown immediate
SUR LA BASE SECONDAIRE / STANDBY
1) la base de secour a du être notifie de la permutation.
Verifier cela dans la colonne SWITCHOVER_STATUS de v$database la valeur attendu est TO_PRIMARY
select OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS,DATAGUARD_BROKER
from v$database;
2) Lancer la commande pour la passer en primaire
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
3) Arret/redemarrage de la nouvelle primaire
shutdown
startup
select OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS,DATAGUARD_BROKER
from v$database;
=============== Procedure de FAILOVER ===================
Il ne reste de disponible qu'une base secondaire.
Vérifier sur la secondaire l'absence de trou dans les archivelog.
select OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS,DATAGUARD_BROKER
from v$database;
1)
select * from v$archive_gap;
S'il manque des archives et s'il est possible de les récupérer à la main
alors les deposer sur le serveur et utiliser l'instruction :
ALTER DATABASE REGISTER PHYSICAL LOGFILE 'file_archive1.arc';
ALTER DATABASE REGISTER PHYSICAL LOGFILE 'file_archive2.arc';
...
Verifier qu'il n'y a plus de trou
select * from v$archive_gap;
2) Si les standby redo log sont présent utiliser la commande :
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
!!!! Attention est partie pour un failover !!!!
select OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS,DATAGUARD_BROKER
from v$database;
si pas de standby redolog
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH SKIP STANDBY LOGFILE;
3) CONVERTIR LA STDBY BASE EN BASE PRINCIPALE
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
C'est irrevocable plus aucune information de journalisation de l'ancienne primaire ne peut être appliquée.
4) Arret/redémarrage de la nouvelle primaire
shutdown immediate
startup
Note Best practice Failover et switch over, DocID sur metalink : 387266.1
========= Support correctif et support etendu au niveau des version du SGBD ======================
https://metalink.oracle.com/metalink/plsql/f?p=130:14:240227830376363662::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,161818.1,1,1,1,helvetica
Doc ID:161818.1
========= transformer les colonnes VARCHAR2 bytes en VARCHAR2 CHAR utf8 ===========
2. Moulinette de passage des champs CHAR et VARCHAR2 de "byte" en "char".
Répondre : 'USER1','USER2' à la question
declare
cursor c1 is
select all_tab_columns.owner, all_tab_columns.table_name, column_name, data_type, data_length
from all_tab_columns, all_tables
where all_tab_columns.table_name = all_tables.table_name
and all_tab_columns.owner = all_tables.owner
and data_type in ('VARCHAR2', 'CHAR') and all_tab_columns.owner in (&1);
v_statement varchar2(255);
begin
dbms_output.put_line('Altering tables (semantic byte -> char).');
for r1 in c1 loop
v_statement := 'alter table ' || r1.owner || '.' || r1.table_name;
v_statement := v_statement || ' modify (' || r1.column_name || ' ';
v_statement := v_statement || r1.data_type || '(' || r1.data_length;
v_statement := v_statement || ' CHAR))';
dbms_output.put_line(v_statement);
execute immediate v_statement;
end loop;
dbms_output.put_line('Done.');
end;
/
============ RAC et pb de prise de connexion =================
Les problèmes relatif à la prise de connexion donnant l'erreur ORA-12545, sont documentés dans la note metalink :
364855.1
Il s'agit d'un problème de résolution d'adresse IP pour des serveurs ayant plusieurs interfaces réseaux dans ce cas de figure il faut correctement renseigner le paramètre d'instance local_listener.
En indiquant l'adresse locale du listener écoutant sur la VIP.
soit la commande suivante à éxécuter
alter system set local_listener= (ADDRESS = (PROTOCOL = TCP)(HOST = ip-vip1) (PORT = 1521)) scope=both sid='INSTANCE1';
A passer sur chaque instance du cluster.Les problèmes relatif à la prise de connexion donnant l'erreur ORA-12545, sont documentés dans la note metalink :
364855.1
Il s'agit d'un problème de résolution d'adresse IP pour des serveurs ayant plusieurs interfaces réseaux dans ce cas de figure il faut correctement renseigner le paramètre d'instance local_listener.
En indiquant l'adresse locale du listener écoutant sur la VIP.
soit la commande suivante à éxécuter
alter system set local_listener= (ADDRESS = (PROTOCOL = TCP)(HOST = ip-vip1) (PORT = 1521)) scope=both sid='INSTANCE1';
A passer sur chaque instance du cluster.
============ tracer une session dans un pool de sessions ==================
http://asktom.oracle.com/pls/asktom/f?p=100:11:2370714648119054::::P11_QUESTION_ID:35661775175899
http://www.oracle.com/technology/oramag/oracle/04-sep/o54talking.html
http://julian.dyke.users.btopenworld.com/com/Diagnostics/Packages/DBMS_MONITOR/CLIENT_ID_TRACE_ENABLE.html
utilitaires :
exec dbms_monitor.client_id_trace_enable(client_id=>'kimberly');
trcsess output="kfloss.trc" service="testenv" module="product update" action="batch insert"
trcsess output=aca.trc clientid=aca *.trc
le client_id se précise par
exec DBMS_SESSION.set_identifier('kimberly');
============ left outer join , jointure =======
FROM appart a LEFT OUTER JOIN personne p ON (a.id_appart = p.id_appart)
La table a gauche est la directrice !!!
C'est a dire la table à gauche de la table personne dirige en terme de données.
On ne doit rater aucune ligne de la table à gauche ici appart. se traduit donc
a.id_appart=p.id_appart(+)
Note : l'operateur JOIN ou LEFT OUTER JOIN renvoi sur l'une des tables precédentes.
soit les tables suivantes
create table a ( a number );
create table b ( a number,
c number,
d number );
create table c2 ( c number,
d number );
create table d ( a number,
d number );
cette syntaxe retourne logiquement une erreur
select
a,
d
from
a join b using(a)
join c2 on (c2.d=d.d)
join d on (d.a = a.a);
ERROR at line 6:
ORA-00904: "D"."D": invalid identifier
select
a,
d
from
a join b using(a)
join c2 on (c2.d=b.d);
requete correcte.
============ jdbc, java, RAC, FCF =========
resource metalink
reponse officiel du support par metalink :
The Thin JDBC driver does not support TAF.
Reference:
Note 297490.1 Is TAF (Transparent Application Failover) Supported On The Latest Jdbc Thin Driver (10g)?
______________________________________________________________________________________________________
How to implement (Fast Connection Failover) FCF Using JDBC driver ?
note ID : 414199.1
How To Use JDBC FCF Feature To Detect That a RAC Node Instance Has Been Shutdown ?
note ID : 364005.1
A priori le TAF n'est pas supporté en Thin Driver.
How to Implement Connect Failover Using JDBC Thin
Note ID : 213412.1
RAC Frequently Asked Questions
Note ID : 220970.1
Trouvé dans la note : RAC Survival Kit: High Availability and RAC
Note ID : 209914.1
Do you want to know what type of failovers are provided by Transparent Application Failover (TAF) ?
No
Yes - TAF protects or fails over the following:
* client/server connection
* user session state
* prepared statements
* active cursors (select statements) that have begun to return results
For example: OCI programs, Java JDBC thick drivers (OCI drivers), ODBC connections, SQL*Plus running Select statements
You'll find a detailed overview in Masking Failures with Transparent Application Failover (TAF)
============= mot de passe de sysman console EM ===========
Voir lien : http://forums.oracle.com/forums/thread.jspa?messageID=1812245
SYSMAN is default super user account used to set up and administer Enterprise Manager a.k.a EM repository user
Follow the step to change SYSMAN password
http://download-west.oracle.com/docs/cd/B19306_01/em.102/b40002/repository.htm#i1029558
For each Management Service associated with the Management Repository, locate the emoms.properties configuration file.
The emoms.properties file can be found in the following directory of the Oracle Application Server Home where the Oracle Management Service is installed and deployed:
IAS_HOME/sysman/config/
Locate the following entries in the emoms.properties file:
oracle.sysman.eml.mntr.emdRepPwd=ece067ffc15edc4f
oracle.sysman.eml.mntr.emdRepPwdEncrypted=TRUE
Enter your new password in the first entry and enter FALSE in the second entry.
For example:
oracle.sysman.eml.mntr.emdRepPwd=new_password
oracle.sysman.eml.mntr.emdRepPwdEncrypted=FALSE
Save and exit the emoms.properties file and restart each Management Service associated with the Management Repository.
============= constraint check ====================
create table t ( a number(1) constraint c1 check ( a in (0,1)) );
============= Ajouter un service au RAC =============
srvctl add service -d ADRIVE -s ACA -r ADRIVE1,ADRIVE2 -P BASIC
ensuite demarrer le service
srvctl start service -d ADRIVE -s ACA
==================== TAF en RAC 10.2.0.3 ===================
srvctl add service -d ADRIVE -s ACA -r ADRIVE1,ADRIVE2 -P BASIC
-P BASIC pour le transparent failover de type = BASIC (TAF)
Passer le service en failover :
SQL> begin
dbms_service.modify_service
(
SERVICE_NAME=>'ACA',
FAILOVER_METHOD=>dbms_service.failover_method_basic,
FAILOVER_TYPE=>dbms_service.failover_type_session,
FAILOVER_RETRIES=>180,
FAILOVER_DELAY=>5
);
end;
.
verifier au sein de la base la bonne configuration du service :
col name for a25
col name for a15
select name,failover_method from dba_services;
definir un tnsnames.ora avec
au minimum avec ces parametres :
AA_TAF =
(
DESCRIPTION = (FAILOVER=ON)(FAILOVER_MODE=(TYPE=SESSION))
(ADDRESS = (PROTOCOL = TCP)(HOST = 128.239.245.163)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 128.239.245.164)(PORT = 1521))
(CONNECT_DATA =(SERVICE_NAME = ACA.FR.AUCHAN.COM))
)
ou en precissant en plus la methode, le delai etc...
AA_TAF2 =
(
DESCRIPTION =(FAILOVER=ON) (FAILOVER_MODE=(TYPE=SESSION)(METHOD=BASIC)(RETRIES=180)(DELAY=5))
(ADDRESS = (PROTOCOL = TCP)(HOST = 128.239.245.163)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 128.239.245.164)(PORT = 1521))
(CONNECT_DATA =(SERVICE_NAME = ACA.FR.AUCHAN.COM))
)
sqlplus toto/toto@aa_taf2
alter session set nls_date_format='DD/MM/YYYY hh24:mi:ss';
select INSTANCE_NUMBER,INSTANCE_NAME,sysdate from v$instance;
Vérifier que la session est en mode TAF :
set linesize 120
col program for a30
col machine for a12
select
sid,
serial#,
program,
machine,
FAILOVER_TYPE,
FAILOVER_METHOD,
FAILED_OVER
from gv$session
where machine like 'aca%';
============= netca network listener en RAC ================
Ne nettoie pas tjs le fichier $ORACLE_HOME/network/listener.ora
============= Arret RAC ==============
srvctl stop listener -n aixfr88830021p29
srvctl stop database -d ADRIVE
srvctl stop asm -n aixfr88830021p29
srvctl stop nodeapps -n
============= convertir long vers varchar2 ================
Via le PL/SQL conversion implicite :
set serveroutput on
begin
for a in (select table_name t,search_condition s from user_constraints) loop
dbms_output.put_line('alter table ' || a.t || ' modify (' ||a.s || ');');
end loop;
end;
============= installer swingbench =====
avoir java 1.4
sous debian retoucher les scripts :
au niveau ORACLE_HOME et JAVA_HOME
cat bin/swingbench
#!/bin/bash
#export ORACLE_HOME=/home/oracle/orabase/product/10.2/db
export JAVAHOME=/usr/lib/j2sdk1.4-sun
pour lancer l'installation du schema soe
./oewizard.
============= Resource sur ASM ===============
Note:353761.1 Assigning a Physical Volume ID (PVID) To An Existing ASM Disk Corrupts the ASM Disk Header
Note:399482.1 How to recreate OCR/Voting disk accidentally deleted
============= installer ASM ==========
localconfig add
pour charger le processus ocssd
============= TRACER UNE SESSION EN 10GR2 ============
execute dbms_monitor.session_trace_enable(&sid,&serial, TRUE, FALSE);
activer la trace au niveau de la base :
exec dbms_monitor.DATABASE_TRACE_ENABLE(true,true,'TASM3');
exec dbms_monitor.DATABASE_TRACE_ENABLE(true,true,'TASM3');
============= HARD protection des données ============
http://www.oracle.com/technology/deploy/availability/htdocs/HARD.html
============= desactiver les process CRS au boot ================
crsctl disable crs
pour reactiver
crsctl enable crs
============= RAC & OCR =======
ocrcheck
fichier de log dans
$CRS_HOME/log/node/client/ocrcheck_pid.log
============= RAC identifie les causes de reboot ==============
Note:265769.1
============= Resources Utiles en RAC / Notes Metalink ===============
Note 220970.1 - RAC: Frequently Asked Questions
Note 259301.1 - CRS and 10g Real Application Clusters
Note 268937.1 - Repairing or Restoring an Inconsistent OCR in RAC
Note 279793.1 - How to Restore a Lost Voting Disk in 10g
Note:357262.1 - OCR Automatic Backups Are Not Generated On all the nodes
============= de-install RAC clusterware =============
STOP CRS sur les noeuds :
init.crs stop
voir note metalink : Note:239998.1
pour AIX
rm /etc/init.cssd
rm /etc/init.crs
rm /etc/init.crsd
rm /etc/init.evmd
rm /etc/rc.d/rc2.d/K96init.crs
rm /etc/rc.d/rc2.d/S96init.crs
rm -Rf /etc/oracle/scls_scr
rm -Rf /etc/oproc
rm /etc/inittab.crs
supprimer dans le fichier /etc/inittab les 3 lignes suivantes
h1:2:respawn:/etc/init.evmd run >/dev/null 2>&1 </dev/null
h2:2:respawn:/etc/init.cssd fatal >/dev/null 2>&1 </dev/null
h3:2:respawn:/etc/init.crsd run >/dev/null 2>&1 </dev/null
Nettoyer les disques :
[root@aixfr88830021p29] / #dd if=/dev/zero of=/dev/rac/voting_disk3 bs=1k count=200000
200000+0 records in
200000+0 records out
============= ssh et installation RAC et/ou patch =========
check que rien ne fonctionne :
ps -ef | grep smon
ps -ef | grep css
ps -ef | grep oracle
resultat attendu
ps -ef | grep smon
ps -ef | grep css
root 516256 1 0 10:33:53 - 0:00 /bin/sh /etc/init.cssd fatal
ps -ef | grep oracle
oracle 245932 651266 0 08:57:26 pts/2 0:00 /usr/bin/ksh
oracle 651266 442548 0 08:53:28 pts/2 0:00 -ksh
oracle 897236 245932 0 08:57:26 - 0:00 ssh-agent /usr/bin/ksh
!!! ATTENTION VERIFIER L'ESPACE DISPONNIBLE !!!
pour le ORACLE_HOME, CRS_HOME et /tmp !
sous root pour chaque noeud :
/usr/sbin/slibclean
avant de lancer ./runInstaller
toujours installer les patch avec l'utilisateur unix oracle que se soit le clusterware, ASM ou oracle db
ssh-agent $SHELL
ssh-add
============= arret agent oracle ==============
export AGENT_HOME=/product_p29/oracle/product/agent10g/bin
cd $AGENT_HOME
./emctl stop agent
============= RAC crsctls ===============
connaitre la version de la pile crs
crsctl query crs softwareversion hostname
CRS software version on node [aixfr88830021p30] is [10.2.0.2.0]
crsctl query crs activeversion
voir les disques votant (voting disk)
crsctl query css votedisk
RAC faq :
https://metalink.oracle.com/metalink/plsql/f?p=130:14:4363936385368209683::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,220970.1,1,1,1,helvetica#A6308
Note:220970.1
============= Bug introduits en 10.2.0.3 =============
https://metalink.oracle.com/metalink/plsql/f?p=130:14:4363936385368209683::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,401435.1,1,0,1,helvetica
Note:401435.1
============= RAC pile crs cause de reboot =============
https://metalink.oracle.com/metalink/plsql/f?p=130:14:4363936385368209683::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,265769.1,1,1,1,helvetica
Note:265769.1
============== RMAN backup backupset ===========
backup backupset all format '/orabck/%s';
============== RMAN passage ASM vers non ASM ==============
http://www.idevelopment.info/data/Oracle/DBA_tips/Automatic_Storage_Management/ASM_30.shtml
============== asktom optimisation ============
approche consommation.
http://asktom.oracle.com/~tkyte/runstats.html
...
o How many resources each approach takes. This can be more meaningful then even the wall clock
timings. For example, if one approach is faster then the other but it takes thousands of latches
(locks), I might avoid it simply because it will not scale as well.
....
single user tests -- elapsed times are virtually meaningless.
============== asktom Java optimisation ==========
This is exactly why I think the best java programs never have the words
o select
o insert
o update
o delete
o merge
in them -- only BEGIN ... END; calls to stored procedures (preferablly with an OUT parameter --
FORCING you to use a CallableStatement even! Making it so that statements don't even work!)
Touched a sort spot you did -- I cannot believe how many times and in how many ways I have to say
the same thing over and over and over again. Bind variables -- use them or LOSE.
============== local or global index ==============
http://www.oracle.com/technology/oramag/oracle/04-sep/o54asktom.html
statitics on temporary table (stats sur les tables temporaires)
pour determiner si un index est global ou local :
select index_name, locality from user_part_indexes;
============== RAC Pb vip =============
It's just something I have learned over time. Sometimes as I said the VIP
from one node gets stuck on another node. In this case the V
IP for node 30 was stuck on node29. The easiest way to get it back is to
shut down the node which has the VIP in the wrong place - in this case node 29
which will free up the VIP. Then restart the node whose VIP is missing - node30
which allows node30 to get it's VIP back then restart node29.
This technique can be used on any number of cluste rnodes bwcause it wil
only ever be 2 nodes with the problem. One will have 2 VIP's and one will have none.
So stopping the node
with 2 then restarting the node with none then the node which had 2 will put
it right without affecting the other cluster nodes.
============== cluster RAC ============
crsctl check crs
avoir de l'aide
srvctl start instance -h
srvctl start instance -d ADRIVE -i ADRIVE2
srvctl start asm -n aixfr88830021p30
note : errpt sur AIX
tnsnames.ora RAC
DRV =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = aixfr88830021p29-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = aixfr88830021p30-vip)(PORT = 1521))
(LOAD_BALANCE = yes)(CONNECT_DATA =(SERVICE_NAME = DRV.FR.AUCHAN.COM)
)
============== parametrage AIX utilisation de la mémoire ==========
en AIX 5.3
[root@aixfr88830021p30] / #vmo -a | grep perm
maxperm = 24324
maxperm% = 10
minperm = 12162
minperm% = 5
strict_maxperm = 0
[root@aixfr88830021p30] / #vmo -p -o maxperm%=90
Setting maxperm% to 90 in nextboot file
Setting maxperm% to 90
[root@aixfr88830021p30] / #vmo -p -o strict_maxclient=1
[root@aixfr88830021p30] / #vmo -a | grep stric
strict_maxclient = 1
strict_maxperm = 0
============== wait event ========
https://metalink.oracle.com/metalink/plsql/f?p=130:14:4502983508750003875::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,223117.1,1,1,1,helvetica
note : 223117.1
Subject: How to Identify Resource Intensive SQL for Tuning
Doc ID: Note:232443.1
Subject: Database Performance FAQ
Doc ID: Note:402983.1 Type: FAQ
Last Revision Date: 13-DEC-2006 Status: REVIEWED
============== disk group ASM =======
identifier la lun des disques sur AIX
[root@aixfr88830021p30] / #lscfg -vl hdisk4
hdisk4 U7311.D11.6583BDA-P1-C3-T1-W200800A0B813D9B2-L4000000000000 1722-600 (600) Disk Array Device
verifier le partage des disques :
[oracle@aixfr88830021p29] /product_p29/oracle/script >lsattr -El hdisk3
PR_key_value none Persistant Reserve Key Value True
cache_method fast_write Write Caching method False
ieee_volname 600A0B800017CFA2000002A545A4978F IEEE Unique volume name False
lun_id 0x0003000000000000 Logical Unit Number False
max_transfer 0x100000 Maximum TRANSFER Size True
prefetch_mult 1 Multiple of blocks to prefetch on read False
pvid none Physical volume identifier False
q_type simple Queuing Type False
queue_depth 10 Queue Depth True
raid_level 5 RAID Level False
reassign_to 120 Reassign Timeout value True
reserve_policy single_path Reserve Policy True
rw_timeout 30 Read/Write Timeout value True
scsi_id 0x1a0b00 SCSI ID False
size 10240 Size in Mbytes False
write_cache yes Write Caching enabled False
au niveau system AIX
mknod /dev/rac/asm_disk3 c 18 6
chdev -l hdisk3 -a pv=clear
chown oracle.oinstall asm_disk9
create diskgroup ADRIVE_DATA normal redundancy
disk '/dev/rac/asm_disk3',
'/dev/rac/asm_disk8';
create diskgroup ADRIVE_DATA normal redundancy
disk '/dev/asm/adrive_disk1' ,
'/dev/asm/adrive_disk2' ;
create diskgroup ADRIVE_FRA normal redundancy
disk '/dev/asm/adrive_disk3' force,
'/dev/asm/adrive_disk4' force;
create diskgroup dg_test high redundancy
disk '/dev/rac/asm_disk3',
'/dev/rac/asm_disk8',
'/dev/rac/asm_disk9';
create diskgroup dg_test high redundancy
failgroup grp_fail1 disk '/dev/rac/asm_disk3',
failgroup grp_fail2 disk '/dev/rac/asm_disk8',
failgroup grp_fail3 disk '/dev/rac/asm_disk9';
create diskgroup dg_data external redundancy
disk '/dev/rac/asm_disk3';
create diskgroup dg_data external redundancy
disk '/dev/rac/asm_disk3';
create diskgroup dg_data external redundancy
disk '/dev/hdisk5';
alter diskgroup ADRIVE_FRA dismount;
============== bind peeking ===========
"Histograms on skewed high cardinality data - can be tricky."
I'm curious as to how you handle indexed columns on large tables that have good cardinality on several thousand values and bad for only say about 500 (just example numbers) or so and bind variables are in use. If I understand correctly with bind variable peeking one of the "bad cardinality" queries gets executed and cached first one will get a bad plan for the subsequent good cardinality queries. If one does a histogram with 254 buckets and all of the bad are not covered what then?
can you elaborate on how to handle the tricky skewed high cardinality situations?
Followup:
if you are using binds, you are expecting one plan and only one plan. Histograms in general would not be advised in that case.
=============== AWR en 10g =========
par defaut les snapshot sont a frequence de une fois par heure et avec une historisation de 7 jours.
col BEGIN_INTERVAL_TIME for a30
col end_interval_time for a30
set linesize 120
SELECT snap_id, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
ORDER BY 1;
La liste des snapshots existant
Oracle Database 10g uses a scheduled job, GATHER_STATS_JOB, to collect AWR statistics.
Pour voir les jobs utiliser la requete suivante :
SELECT a.job_name, a.enabled, c.window_name, c.schedule_name,
c.start_date, c.repeat_interval
FROM dba_scheduler_jobs a,
dba_scheduler_wingroup_members b,
dba_scheduler_windows c
WHERE job_name='GATHER_STATS_JOB'
And a.schedule_name=b.window_group_name
And b.window_name=c.window_name;
disable job :
Exec dbms_scheduler.disable('GATHER_STATS_JOB');
creer une ligne de base de performance (fonctionnement normal) :
STATISTICS_LEVEL doit être positionné à TYPICAL ou ALL.
creer un snapshot à manuellement :
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/
drop snapshot
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 22,
high_snap_id => 32, dbid => 3310949047);
END;
/
modifier le paramétrage de awr :
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 43200,
interval => 30, topnsql => 100, dbid => 3310949047);
END;
/
retention en minute 43200 soit 30 jours
creation d'une ligne de reference :
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name => 'peak baseline',
cascade => FALSE, dbid => 3310949047);
END;
/
en SCRIPT l'equivalent de statpack sont :
awrrpt.sql pour le rapport global,
awrsqrpt.sql pour un ordre SQL
les scripts de rapports sont :
You can view AWR reports by running the following SQL scripts:
The awrrpt.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids.
The awrrpti.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids on a specified database and instance.
The awrsqrpt.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids. Run this report to inspect or debug the performance of a SQL statement.
The awrsqrpi.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids on a specified database and instance. Run this report to inspect or debug the performance of a SQL statement on a specific database and instance.
The awrddrpt.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods.
The awrddrpi.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods on a specific database and instance.
=============== check path filename ===========
select substr(name,1,instr(name,'/',-1)) from v$datafile
union
select substr(name,1,instr(name,'/',-1)) from v$controlfile
union
select substr(member,1,instr(member,'/',-1)) from v$logfile;
=============== resource groupe ================
select username,INITIAL_RSRC_CONSUMER_GROUP from dba_users;
begin
dbms_resource_manager_privs.grant_switch_consumer_group(
grantee_name => 'TK_FIX',
consumer_group => 'TK_STANDARD',
grant_option => FALSE);
end;
/
begin
dbms_resource_manager.set_initial_consumer_group(
user => 'TK_FIX',
consumer_group => 'TK_STANDARD');
end;
/
en 10gR2.
=============== type object / record / array =========
solution 1)
La première option est d'utiliser une table temporaire sur laquelle on réalise une insertion massive.
La seconde piste est (deux possibilités au niveau des types :)
piste 2A) passer un tableau de type simple (par exemple number)
Pour cela sur la base de données :
create or replace type num_table is table of number;
création d'une procédure stockée avec en paramètre un type tableau de number.
create or replace procedure aca_proc_1 ( p in num_table )
is
begin
null;
end;
.
/
A voir coté pro*c s'il est possible de déclarer des variables de type num_table ?
piste 2B) passer un tableau de type "record"
Je ne suis pas parvenu à créer un type record au niveau de la base, cela fonctionne dans la partie déclarative
PL/SQL mais dans l'instruction create type xxx is record (...), par conséquent je suis passé par un type object
create or replace type rec_aca as object
( id number,
l varchar2(80)
);
create or replace type t_arr_obj is table of rec_aca;
create or replace procedure aca_proc_2 ( p in t_arr_obj)
is
begin
null;
end;
.
/
A voir coté pro*c s'il est possible de déclarer des variables de type t_arr_obj ?
Note :
Au sein du PL/SQL ces types peuvent être transformés en ensemble de données manipulable via les ordres SQL.
En utilisant les instructions de typage table( cast(xxxx as myArrayType) )
par exemple :
insert into MaTable select x.id,x.l from table( cast(p as t_arr_obj) ) x;
ops$tkyte@ORA9IR2> create table emp as select empno, sal from scott.emp;
Table created.
ops$tkyte@ORA9IR2> create table emp2 as select empno, sal/2 sal from scott.emp
where mod(empno,2)=1;
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace type myScalarType as object ( empno number,
sal number )
2 /
Type created.
ops$tkyte@ORA9IR2> create or replace type myArrayType as table of myScalarType
2 /
Type created.
ops$tkyte@ORA9IR2> select count(*) from
2 (( select * from emp minus select * from emp2 ) union all (select * from
emp2 minus select * from emp ));
COUNT(*)
----------
18
ops$tkyte@ORA9IR2> declare
2 l_data myArrayType;
3 begin
4 select myScalarType(empno, sal) bulk collect into l_data
5 from emp;
6
7 merge into emp2
8 using ( select x.empno, x.sal from table( cast(l_data as myArrayType) )
X ) emp
9 on (emp2.empno = emp.empno)
10 when matched then update set sal = emp.sal
11 when not matched then insert (empno,sal) values(emp.empno, emp.sal);
12 end;
13 /
PL/SQL procedure successfully completed.
=============== bug max 10.2 ==========
Bug 4925103 - Wrong results using MAX() on column containing NULLs
Doc ID: Note:4925103.8
create table TEST(
col1 VARCHAR(2),
col2 NUMBER
);
CREATE INDEX TEST_IDX ON TEST (col1,col2);
insert into TEST values('01',1);
insert into TEST values('01',2);
insert into TEST values('01',3);
insert into TEST values('01',NULL);
commit;
analyze table TEST compute statistics;
SELECT /*+ INDEX(TEST TEST_IDX) */ MAX(col2)
FROM TEST
WHERE col1 = '01'
AND col2 > 0;
=============== exception =============
BEGIN
SELECT OBJECT_NAME INTO temp_var FROM USER_OBJECTS
WHERE OBJECT_NAME = UPPER(table_name) AND OBJECT_TYPE = 'TABLE';
-- processing here
EXCEPTION
WHEN NO_DATA_FOUND THEN -- catches all 'no data found' errors
DBMS_OUTPUT.PUT_LINE ('No Data found for SELECT on ' || temp_var);
END;
recuperer une erreur donnee en PL/SQL
declare
invalide_time_date EXCEPTION;
PRAGMA EXCEPTION_INIT(invalide_time_date, -1850);
begin
for r in (select ID_CHARGEMENT,lundi,mardi,mercredi,jeudi,vendredi from TMP_CHARGEMENT_BDC)
loop
begin
...
exception
when invalide_time_date then
dbms_output.put_line('Error on monday for id_chargement => ' || r.id_chargement);
end;
recuperation du code erreur et du message :
utl_file.put_line (m_FileTrace, TO_CHAR(SYSDATE,'DD/MM/YYYY HH24:MI:SS') || ' Exception Code erreur : ' || TO_CHAR (sqlcode) || ', ' || sqlerrm);
=============== wait & event ===========
select * from v$waitstat;
select EVENT,TOTAL_WAITS,TIME_WAITED,AVERAGE_WAIT from V$SYSTEM_EVENT
order by AVERAGE_WAIT;
select sid,event,WAIT_CLASS ,SECONDS_IN_WAIT , STATE from V$SESSION_WAIT
where sid = 349
select sid,event,WAIT_CLASS ,SECONDS_IN_WAIT , STATE from V$SESSION_WAIT
where sid = &sid;
col event for a60
select
sid,
event,
total_timeouts,
time_waited/100 t_waited_sec,
average_wait/100 avg_wt_sec
from V$SESSION_EVENT
where sid = &sid
order by time_waited;
=============== apres resto sans temp ============
en 10g
il faut au moins un fichier sur le tablespace temp :
ALTER TABLESPACE TMP_L ADD TEMPFILE '/oradata/TK_00/temp/TK_00_TMP_L_02.dbf' size 101M autoextend on next 100M maxsize 8000M;
drop du non sauvegarde :
alter tablespace TMP_L drop tempfile '/oradata/TK_00/temp/TK_00_TMP_L_1.dbf';
============== resize ou autoextend on tempfile ==============
alter database tempfile '/oradata/TK_00/temp/TK_00_TMP_L_02.dbf' autoextend on maxsize 8001M;
=============== comparer deux listes de fichiers =============
sous solaris
ls -l file1
ls -l file2
>> maliste_1.dat
file.awk
{
print $9" "$5
}
pour solaris.
position 9 c'est la taille
position 5 c'est le nom
cat maliste_1.dat | awk -f file.awk | sort >res_list_1.txt
cat maliste_2.dat | awk -f file.awk | sort >res_list_2.txt
diff maliste_1.dat maliste_2.dat
=============== sur une std by lancer l'application des redo log ===========
obliger une rotation moyenne toutes les n secondes
alter system set archive_lag_target=1800 scope=both;
role d'une base de donnees
select DATABASE_ROLE from v$database;
niveau de protection
select DB_UNIQUE_NAME,PROTECTION_MODE,PROTECTION_LEVEL,FORCE_LOGGING,DATAGUARD_BROKER from v$database;
SQL> STARTUP MOUNT;
Step 2 Start Redo Apply.
On the standby database, issue the following command to start Redo Apply:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
voir dans l'alert.log
Managed Standby Recovery not using Real Time Apply
Media Recovery Log /oradata/WEBPRO/data/archivelog/WEBPRO_482_1_594491265.arc
Tue Nov 21 13:24:00 2006
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
Tue Nov 21 13:24:14 2006
Media Recovery Log /oradata/WEBPRO/data/archivelog/WEBPRO_483_1_594491265.arc
Tue Nov 21 13:24:24 2006
Media Recovery Log /oradata/WEBPRO/data/archivelog/WEBPRO_484_1_594491265.arc
Media Recovery Log /oradata/WEBPRO/data/archivelog/WEBPRO_485_1_594491265.arc
Media Recovery Log /oradata/WEBPRO/data/archivelog/WEBPRO_486_1_594491265.arc
Tue Nov 21 13:24:39 2006
Media Recovery Waiting for thread 1 sequence 487 (in transit)
ou
startup mount;
alter database recover managed standby database nodelay disconnect
from session;
Application des redo en temps reel
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE
DISCONNECT FROM SESSION;
dans alert.log on voit la recuperation a partir des stand by redo log et non plus des archivelog.
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE
DISCONNECT FROM SESSION
Thu Nov 23 12:47:37 2006
Primary database is in MAXIMUM PERFORMANCE mode
RFS[10]: Successfully opened standby log 5: '/oradata/WEBPRO/redo1/WEBPRO_STB_redo5.log'
Thu Nov 23 12:47:38 2006
Media Recovery Waiting for thread 1 sequence 496 (in transit)
Thu Nov 23 12:47:38 2006
Recovery of Online Redo Log: Thread 1 Group 5 Seq 496 Reading mem 0
Mem# 0 errs 0: /oradata/WEBPRO/redo1/WEBPRO_STB_redo5.log
Mem# 1 errs 0: /oradata/WEBPRO/redo2/WEBPRO_STB_redo5.log
verifier l'historisation sur la stdby et non l'application (table pour l'application V$LOG_HISTORY):
alter session set nls_date_format='DD/MM/YYYY hh24:mi:ss';
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME,ARCHIVED,APPLIED
FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
voir les gap sur la stand by :
SELECT * FROM V$ARCHIVE_GAP;
note resolution des gap : 232649.1 Data Guard Gap Detection and Resolution
si echec de transfert via processus automatique fal :
alors les transferer a la main (ftp) et appliquer les commandes
ALTER DATABASE REGISTER LOGFILE '/oradata/WEBPRO/data/archivelog/WEBPRO_482_1_594491265.arc';
ALTER DATABASE REGISTER LOGFILE '/oradata/WEBPRO/data/archivelog/WEBPRO_483_1_594491265.arc';
(il y trace dans l'alert.log de l'enregistrement.)
sur la primaire :
select group#,sequence#,archived,status,first_time from v$log;
pour tester le transfer des redo :
sur la primaire
ALTER SYSTEM SWITCH LOGFILE;
stopper/arreter l'application des log sur la standby :
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
8.5.4.3 Accessing the V$ARCHIVE_DEST_STATUS Fixed View
To quickly determine the level of synchronization for the standby database, issue the following query on the physical standby database:
SQL> SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ#
FROM V$ARCHIVE_DEST_STATUS;
Pour determine le mode d'application :
To determine if real-time apply is enabled, query the RECOVERY_MODE column of the V$ARCHIVE_DEST_STATUS view
col dest_name for a20
set linesize 200
select
dest_id,
dest_name,
database_mode,
recovery_mode
from
V$ARCHIVE_DEST_STATUS;
SELECT dest_name,
status,
type,
database_mode,
recovery_mode,
protection_mode,
archived_seq#,
applied_seq#,
synchronized
FROM V$ARCHIVE_DEST_STATUS;
Voir sur la physique les redo logs reçu :
The V$ARCHIVED_LOG fixed view on the physical standby database shows all the archived redo log files received from the primary database.
SELECT
registrar,
creator,
thread#,
sequence#,
archived,
applied,
first_time,
first_change#,
next_change#,
round((blocks*block_size)/(1024*1024)) Mo
FROM V$ARCHIVED_LOG
order by sequence#;
sur la primaire autant de ligne que de destinations.
un redo log peut etre archive 2 ou n fois donc 2 ou n lignes dans cette table
pour un redo log donne.
Pour voir sur la standby tous les redo qui ont ete appliques:
Query the V$LOG_HISTORY fixed view on the physical standby database to show all the archived redo log files that were applied
SELECT
thread#,
sequence#,
first_time,
first_change#,
next_change#
FROM V$LOG_HISTORY
order by sequence#;
Les messages qui ne peuvent etre loguer au sein des trace file et/ou alert.log
peuvent etre stockees dans la table V$DATAGUARD_STATUS colonne message;
Valable pour la primaire et stdby
The V$DATAGUARD_STATUS fixed view displays events that would typically be triggered by any message to the alert log or server process trace files.
The following example shows output from the V$DATAGUARD_STATUS view on a primary database:
alter session set nls_date_format='DD/MM/YYYY hh24:mi:ss';
SQL> select timestamp,message from v$dataguard_status
order by timestamp;
verifier le parametrage desc log_archive_dest
table : v$archive_dest
select
dest_id,
dest_name,
destination,
status,
binding,
target,
archiver,
process,
log_sequence,
register,
failure_count,
fail_date,
fail_sequence,
max_failure,
transmit_mode
from v$archive_dest
where dest_id=&dest_id;
=============== support matrix OS ===========
https://metalink.oracle.com/metalink/plsql/f?p=130:14:9876118736948775382::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,69961.1,1,1,1,helvetica
https://metalink.oracle.com/metalink/plsql/f?p=130:14:9876118736948775382::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,223718.1,1,1,1,helvetica
desupported oracle 8i (8.1.7.4)
https://metalink.oracle.com/metalink/plsql/f?p=130:14:9876118736948775382::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,250629.1,1,1,1,helvetica
=============== migration 9.2.0.6 en 10.2.0.1 et stream ==============
pour rappel, suite à la migration 9.2.0.6 --> 10.2.0.1, impossible de déplacer les tables '%LOGMNR%' vers un autre tablespace.
La solution a été la suivante :
suppression des tables system.'%LOGMNR%' et sys.'%LOGMNR%'
et exécution des scripts suivants dans l'ordre :
dbmslm.sql
prvtlm.plb
dbmslmd.sql
prvtlmc.plb
prvtlmd.plb
prvtlmrd.plb
dbmslms.sql
prvtlms.plb
=============== prompt et accept ===========
ACCEPT Seuil_Alert PROMPT 'Donner le seuil d'alerte (nb d'extents dispo demandes) :'
=============== sh avec tkprof =======
#!/bin/bash
name=`basename $1 trc`
mydate=`date +'%Y-%m-%d'`
tkprof $1 ${name}${mydate}.prf
tkprof $1 ${name}${mydate}_exeela.prf sort=exeela
head -1000 ${name}${mydate}_exeela.prf > ${name}${mydate}_exeela_extrait.prf
=============== monitoring index usage ============
alter index idx1 monitoring usage;
info monitoring usage index
set linesize 120
SELECT index_name,
table_name,
monitoring,
used,
start_monitoring,
end_monitoring
FROM v$object_usage
WHERE
index_name = 'IDX_V'
ORDER BY table_name,index_name;
=============== disable job statistics =====
en etant connecte sys
call dbms_scheduler.disable('GATHER_STATS_JOB');
=============== clustering factor ========
pour un usage d'index le clustering factor doit être proche
du nombre de bloc de la table :
http://www.dba-oracle.com/t_gogala_cbo_oltp2.htm
=============== gestion tablespace allocation type ==========
EXTENT_MANAGEMENT => LOCAL ou DICTIONARY
ALLOCATION_TYPE => type de gestion pour les extents : SYSTEM, UNIFORM, USER (manuel ne doit plus exister)
SEGMENT_SPACE_MANAGEMENT => pcfused/pctused gerer par freelist (MANUAL) ou en
AUTO (BITMAP) mieux vaut choisir AUTO
en 9i:
create tablespace M01_data datafile '/product_e33/oracle/data/PCO00/tsM01_DATA_01.dbf' size 1M autoextend on next 100M maxsize 2001M
extent management local
autoallocate;
en 10g:
create [smallfile/bigfile] tablespace xxx datafile '/path/file_name.dbf'
size 1M autoextend on next 100M maxsize 8001M
extent management [local/dictinary] [local => autoallocate/uniforme size sss M]
segment space management [auto/manual];
CREATE TABLESPACE "USERS" LOGGING DATAFILE 'KEYWORD_ORACLE_DATA/data/KEYWORD_SID/tsKEYWORD_SID_USERS_01.dbf' SIZE 25M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace aca_test datafile '/oradata/TK_00/data/TK_00_aca_test_01.dbf' size 1M
extent management local autoallocate segment space management auto;
=============== remove ipc et semaphore ======
voir note oracle sur metalink : 123322.1
https://metalink.oracle.com/metalink/plsql/f?p=130:14:18704231906704804::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,123322.1,1,1,1,helvetica
sysresv -d on -l <SID1> <SID2>
Attempting to remove memory and semphores using sysresv when Oracle
detects an instance is running:
/u03/app/oracle/product/8.1.6> sysresv -f
IPC Resources for ORACLE_SID "X816" :
Shared Memory:
ID KEY
16437 0xe4efa8dc
Semaphores:
ID KEY
12320802 0x09d48346
Oracle Instance alive for sid "X816"
SYSRESV-005: Warning
Instance maybe alive - aborting remove for sid "X816"
Removing IPC resources using sysresv:
/u03/app/oracle/product/8.1.6> sysresv -i
IPC Resources for ORACLE_SID "X816" :
Shared Memory
ID KEY
No shared memory segments used
Semaphores:
ID KEY
No semaphore resources used
Oracle Instance not alive for sid "X816"
Remove ipc resources for sid "X816" (y/n)?y
Done removing ipc resources for sid "X816"
/u03/app/oracle/product/8.1.6
Verify the resources were removed:
/u03/app/oracle/product/8.1.6> sysresv
IPC Resources for ORACLE_SID "X816" :
Shared Memory
ID KEY
No shared memory segments used
Semaphores:
ID KEY
No semaphore resources used
Oracle Instance not alive for sid "X816"
o If you need to remove memory segments, and Oracle detects the
instance is alive through sysresv:
% ipcrm -m <memid>
Where <memid> is the memory id shown in the sysresv output.
Example:
% ipcrm -m 16437
If you need to remove semaphores, and Oracle detects the
instance is alive through sysresv:
% ipcrm -s <semid>
where <semid> is the semaphore id shown in the sysresv output.
Example:
% ipcrm -s 12320802
=============== changer le nom du fichier d'une table externe ==========
alter table ext_table location ('new_filename');
=============== trace file identifier =============
alter session set tracefile_identifier
There is an undocumented alter session command that allows you to control the name of the trace file written to user_dump_dest when tracing a session. It can also be used to create multiple tracefiles from the same session.
The default name for a trace files is INSTANCE_PID_ora_TRACEID.trc
Where INSTANCE is thename of the Oracle instance, PID is the operating system process ID (V$PROCESS.OSPID) and TRACEID is a character string of your choosing.
ALTER SESSION SET SQL_TRACE=TRUE;
ALTER SESSION SET TRACEFILE_IDENTIFIER=GARRY;
Use V$PROCESS.TRACEID to check the setting.
SQL> select username, traceid from v$process
USERNAME TRACEID
--------------- ----------
oracle GARRY
The file created in user_dump_dest would be named
orcl_ora_12586_GARRY.trc
========================= trace file ====================
--alter session set max_dump_file_size=unlimited;
--Alter session set tracefile_identifier='TRACK01';
--Alter session set events '10046 trace name context forever, level 8';
code SQL
--Alter session set events '10046 trace name context off';
=============== orapwd =============
orapwd file=c:\oracle\920\database\pwdTSH2.ora password=password entries=10
orapwd file=/u01/app/oracle/product/9.2.0.1.0/dbs/orapwDUP password=password entries=10
=============== collection vs table index ========
Hi
What is the difference between
type l_array is table of varchar2(256);
type l_array is table of varchar2(256) index by binary integer;
which is faster? When should index by binary integer used?
Followup:
one is a collection, the other a plsql index by table.
declare
type collection_type is table of varchar2(256);
type plsql_table_type is table of carchar2(256) index by binary_integer;
l_coll collection_type;
l_tab plsql_table_type;
begin
One (collection) needs to be "extended" to allocate space, the other does not.
l_coll.extend;
l_coll(1) := 'foo';
l_tab(1) := 'bar';
One (collection) can be initialized easily, the other -- not:
l_coll := collection_type( 'hello', 'world', 'foo', 'bar' );
l_tab(1) := 'hello';
l_tab(2) := 'world';
l_tab(3) := 'foo';
l_tab(4) := 'bar';
Those are the "major" differences -- I find plsql table types generally "easier"
to use since they need not be extended and "contigous" (eg: in order to have
l_coll(100) - I must have 1..99 allocated. In order to have l_tab(100) i only
need that entry)
http://asktom.oracle.com/pls/ask/f?p=4950:8:14267106355414222132::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:31703522290
=============== bulk et varray ============
il faut une version > 9i.
ops$tkyte@ORA920> declare
2 cursor cashCursor is
3 select empno, ename, hiredate from emp;
4
5 type cash_tab is table of cashCursor%rowtype index by
binary_integer;
6
7 l_cashTab cash_tab;
8 l_counter number := 10;
9 begin
10 open cashCursor;
11 fetch cashCursor bulk collect into l_cashTab limit l_counter;
12 close cashCursor;
13 end;
14 /
=============== supprimer espace d'un to_char =========
suppress blank
to_char(9,'FM000000000')
=============== installer agent ou autre cpio =========
cpio -idcmv < filename.cpio
================ analyser une serie de fichier .trc par tkprof =======
#/usr/bin/sh
rm -f *.prf
ls *.trc > tmp_lst_trc.$$
cp tmp_lst_trc.$$ tmp_lst_prf.$$
sed -e "s/\.trc//" tmp_lst_prf.$$ > tmp_lst_prf_without_ext.$$
paste tmp_lst_prf.$$ tmp_lst_prf_without_ext.$$ > tmp_script_tkprof.$$
sed -e "s/^/tkprof /" -e "s/$/ sys=no/" tmp_script_tkprof.$$ > tmp_script_tkprof_ok.$$
chmod +x tmp_script_tkprof_ok.$$
./tmp_script_tkprof_ok.$$
rm -f tmp_lst_trc.$$ tmp_lst_prf.$$ tmp_lst_prf_without_ext.$$ tmp_script_tkprof.$$ tmp_script_tkprof_ok.$$
rm -f result.dat
for i in `ls *.prf`
do
grep total $i | tail -2>>result.dat
done
ensuite python p.py
import sys,string
f=open('result.dat')
print "call;count;cpu;elapsed;disk;query;current;rows"
l=f.readline()
while l:
arr=l.split()
print string.joinfields(arr,';')
l=f.readline()
et importer sous format csv le resultat de stdout du script python.
================ version des composants ============
select COMP_NAME,version,status from dba_registry;
================ create pfile from spfile =========
create pfile ='aca_test_pfile.ora' from spfile;
================ describe ===========
a regarder dbms_describe
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_descrb.htm#998100
utilisé par la commande desc.
tout les informations sont dans la table user_arguments
http://asktom.oracle.com/pls/ask/f?p=4950:8:768379501604423341::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:934271910350
comment reconnaitre une fonction d'une procedure. La fonction a un argument zero sans nom (nom à NULL)
================ exemple de repartitionnement par split ================
set pagesize 20
col high_value for a15
drop table NEW_DWH_PRESSION_COMMERCIALE;
CREATE TABLE "NEW_DWH_PRESSION_COMMERCIALE"
( "CODSOCEX" CHAR(1) NOT NULL ENABLE,
"CODSOC" CHAR(1) NOT NULL ENABLE,
"CODSAI" VARCHAR2(2) NOT NULL ENABLE
)
nologging
partition by range (codsocex,codsoc,codsai)
(
partition P_SOCEX0_SOC0_SAI_64 values less than ('0','0','65') pctfree 0 pctused 99 ,
partition P_SOCEX0_SOC0_SAI_65 values less than ('0','0','66') pctfree 0 pctused 99 ,
partition P_SOCEX0_SOC0_SAI_66 values less than ('0','0','67') pctfree 0 pctused 99 ,
partition P_SOCEX0_SOC0_SAI_67 values less than ('0','0','68') pctfree 0 pctused 99 ,
partition P_SOCEX0_SOC0_SAI_68 values less than ('0','0','69') pctfree 0 pctused 99 ,
partition P_SOCEX0_SOC0_SAI_69 values less than ('0','0','70') ,
partition P_SOCEX0_SOC1_SAI_64 values less than ('0','1','65') pctfree 0 pctused 99 ,
partition P_SOCEX0_SOC1_SAI_65 values less than ('0','1','66') pctfree 0 pctused 99 ,
partition P_SOCEX0_SOC1_SAI_66 values less than ('0','1','67') pctfree 0 pctused 99 ,
partition P_SOCEX0_SOC1_SAI_67 values less than ('0','1','68') pctfree 0 pctused 99 ,
partition P_SOCEX0_SOC1_SAI_68 values less than ('0','1','69') pctfree 0 pctused 99 ,
partition P_SOCEX0_SOC1_SAI_69 values less than ('0','1','70')
);
select
table_name,
partition_name,
high_value,
decode(partition_name,'P_SOCEX0_SOC0_SAI_70','**NEW**',NULL) F
from user_tab_partitions
where table_name = 'NEW_DWH_PRESSION_COMMERCIALE'
order by partition_position;
-- la partition socex0_soc0_sai_70 n'existe pas
-- cette ligne se retrouve incorrectement stockée dans la partition P_SOCEX0_SOC1_SAI_64
PROMPT INSERT ROW (0,0,70) in incorrect partition => (0,1,64) P_SOCEX0_SOC1_SAI_64
insert into NEW_DWH_PRESSION_COMMERCIALE values (0,0,70);
PROMPT Nb rows dans la partition P_SOCEX0_SOC1_SAI_64
select count(*) from new_dwh_pression_commerciale partition (P_SOCEX0_SOC1_SAI_64);
-- remise des partitions au carre
PROMPT SPLIT DE PARTITION au niveau 0,0,71 => en deux partitions P_SOCEX0_SOC0_SAI_70 et P_SOCEX0_SOC1_SAI_64
alter table new_dwh_pression_commerciale split partition P_SOCEX0_SOC1_SAI_64 at ('0','0','71')
into (partition P_SOCEX0_SOC0_SAI_70,partition P_SOCEX0_SOC1_SAI_64);
-- PROMPT SPLIT FAUX A NE PAS FAIRE DANS NOTRE CAS !!!
-- alter table new_dwh_pression_commerciale split partition P_SOCEX0_SOC1_SAI_64 at ('0','0','71')
-- into ( partition P_SOCEX0_SOC1_SAI_64, partition P_SOCEX0_SOC0_SAI_70);
PROMPT Nb de rows dans la partition P_SOCEX0_SOC0_SAI_70
select count(*) from new_dwh_pression_commerciale partition (P_SOCEX0_SOC0_SAI_70);
PROMPT Nb de rows dans la partition P_SOCEX0_SOC1_SAI_64
select count(*) from new_dwh_pression_commerciale partition (P_SOCEX0_SOC1_SAI_64);
select
table_name,
partition_name,
high_value,
decode(partition_name,'P_SOCEX0_SOC0_SAI_70','**NEW**',NULL) F
from user_tab_partitions
where table_name = 'NEW_DWH_PRESSION_COMMERCIALE'
order by partition_position;
================ forme update d'ensemble 8i et + ============
http://asktom.oracle.com/pls/ask/f?p=4950:8:7413089603034987351::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:164612348068
update ( select a1, b1 from a, b where a.id = b.id ) set a1 = b1;
================ exemple de snapshot =============
CREATE SNAPSHOT SNP_TRANCHE_DE_PRIX
TABLESPACE DIM_DATA
BUILD DEFERRED
USING INDEX TABLESPACE DIM_INDEX
REFRESH WITH ROWID
AS
SELECT
CODE_SECTEUR,
CODE_FAMILLE,
CODE_SAISON,
IDENTIFIANT,
BORNE_INF,
BORNE_SUP,
DEVISE
FROM TRANCHE_DE_PRIX@dbecc;
================ tout recompiler pckage PL/SQL =============
$ORACLE_HOME/rdbms/admin/utlrp.sql
connecte en tant que sysdba
================ execution // parallel dans oracle ==========
create table trace
(
MSG VARCHAR2(100 CHAR),
DT DATE
);
create table done
(
PID NUMBER,
S NUMBER
);
create or replace procedure do_pp( p_pid number , p_s in number ) is
begin
insert into trace values ('START pid=' || to_char(p_pid),sysdate);
commit;
dbms_lock.sleep(p_s);
insert into done values (p_pid, p_s);
insert into trace values ('END pid=' || to_char(p_pid),sysdate);
commit;
end;
.
/
create or replace procedure para(p_nb in number,p_delais in number) is
v_cpt number :=0 ;
v_job_id binary_integer;
v_cmd varchar2(200);
begin
while v_cpt < p_nb
loop
v_cmd := 'begin do_pp(' || to_char(v_cpt) || ',' || to_char(v_cpt + 1) || '); end;';
dbms_job.submit(v_job_id,v_cmd,sysdate+(p_delais/(24*60*60)));
v_cpt := v_cpt + 1;
end loop;
commit;
end;
.
/
select
s.msg , (fin - deb)*24*3600 tps_sec
from
(select substr(msg,instr(msg,'=')+1) pid,msg, dt deb
from trace
where msg like 'START%') s,
(select substr(msg,instr(msg,'=')+1) pid,msg, dt fin
from trace
where msg like 'END%') e
where s.pid = e.pid;
================ sleep Oracle =============
dbms_lock.sleep(2)
================ reorg de partitions ============
alter table ACA_VTE_DER_ACHT2 move partition MOI200410 compress pctfree 0 pctused 99 storage (initial 8k) tablespace tools;
================ Profile Appli ===========
# Instances.
cat /etc/oratab | awk '
BEGIN {
FS=":";
printf("\n\nListe des ORACLE_SID declares dans le fichier \"/etc/oratab\".\n\n");
}
{
if (NF==3)
{
szTmp = "X";
if (index($2, "8.0")) szTmp = "8";
if (index($2, "8.1")) szTmp = "8i";
if (index($2, "9.2")) szTmp = "9i";
if (index($2, "10.0")) szTmp = "10g";
printf("%-10s (Version %s)\n", $1, szTmp);
}
}
END {
printf("\nAppelez la commande \". oraenv\" pour parametrer l'\''environnement Oracle.\n\n");
}
'
================ Pre requis AIX et Oracle 10gR2 =========
Pre-Install checks for 10gR2 RDBMS (10.2.x) - AIX Platforms
Doc ID: Note:334562.1
================ install oracle 10g ==============
Pb d'installation en 10gR2 sur Linux
export ORACLE_HOME=/u01/product/oracle/10.2
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
relink
relink client_sharedlib
oracle@aca-usb:/u01/product/oracle/10.2/network/lib$ make -f ins_net_client.mk nnfgt.o
ensuite
relink all
================ Chargement CLOB ===============
create directory MyDir as '/product_a29/commerce/BCA/tmp/work/pf';
declare
lobd CLOB;
fils BFILE ;
amt INTEGER := 8;
seq_id number;
file_readonly CONSTANT BINARY_INTEGER := 0;
BEGIN
SELECT b INTO lobd FROM BIDULE WHERE id = 1 FOR UPDATE;
fils := BFILENAME('MyDir','loic_carte.log');
dbms_lob.fileopen(fils, file_readonly);
dbms_lob.loadfromfile(lobd, fils, amt);
commit;
dbms_lob.fileclose(fils);
END;
/
============== Application AIX 32 Bit sur environnement 64 Bit ===============
Pb sur le programme pro*C ./generation_tx4 il faut positionner l'environnement 32 bit car programme 32 bit pass?sur environnement AIX 64 bit sans recompilation :
export LIBPATH=$ORACLE_HOME/lib32:$ORACLE_HOME/network/lib32
pour un bon fonctionnement en 32 bits.
============== pb de librairie ============
Could not load program /product_p08/ECC/bin/cat_int:
Dependent module libclntsh.a(shr.o) could not be loaded.
Could not load module libclntsh.a(shr.o).
solution ajouter dans le oraenv :
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib:$ORACLE_HOME/network/lib
export LIBPATH=$LD_LIBRARY_PATH
============== compilation pro*c ==============
exemple de compilation du prog cat_int
make -f demo_proc.mk OBJS=cat_int.o EXE=cat_int build
make -f demo_proc.mk OBJS=tpl_int.o EXE=tpl_int build
make -f demo_proc.mk OBJS="cat_int.o cat_mod.o" EXE=cat_int build
make -f demo_proc.mk EXE=sample1 OBJS=sample1.o build_static
make -f demo_proc.mk OBJS=rmo_ext.o EXE=rmo_ext build
make -f demo_proc.mk OBJS=generation_tx4.o EXE=generation_tx4 build
make -f demo_proc.mk OBJS=bsc_bfc.o EXE=bsc_bfc build
make -f demo_proc.mk OBJS=rea_bfc.o EXE=rea_bfc build
make -f demo_proc.mk OBJS=ext_bfc.o EXE=ext_bfc build
make -f demo_proc.mk OBJS=array_flat.o EXE=array_flat build
si les indicateurs ne sont pas utilises, alors il faut modifier le
demo_proc.mk en ajoutant en fin de fichier la ligne suivante :
============== Analyse au login de oratab unix ============
a mettre dans le .profile de l'utilisateur unix
# Instances.
cat /etc/oratab | awk '
BEGIN {
FS=":";
printf("\n\nListe des ORACLE_SID declares dans le fichier \"/etc/oratab\".\n\n");
}
{
if (NF==3)
{
szTmp = "X";
if (index($2, "8.0")) szTmp = "8";
if (index($2, "8.1")) szTmp = "8i";
if (index($2, "9.2")) szTmp = "9i";
if (index($2, "10.0")) szTmp = "10g";
printf("%-10s (Version %s)\n", $1, szTmp);
}
}
END {
printf("\nAppelez la commande \". oraenv\" pour parametrer l'\''environnement Oracle.\n\n");
}
'
============== Droits Oracle 10gR2 sous unix ===========
problemes de droits sur les repertoires oracle en 10gR2
l'installeur d'oracle semble plus restrictif.
cd /u01/app/oracle/product
chmod -R o+r *
find . -type d -exec chmod a+x {} \;
=============== create table ================
create table toto pctused 99 pctfree 0 tablespace ced_temp nologging as select /*+ parallel(pvi,5) */ * from dbacar.pvi where PVI_ANNTRT in ('2005','2006');
=============== reset parameter par alter system =======
alter system reset utl_file_dir scope=spfile sid='*';
=============== sqlplus gestion erreur ==========
WHENEVER SQLERROR CONTINUE
WHENEVER SQLERROR EXIT 1
WHENEVER OSERROR EXIT
WHENEVER OSERROR CONTINUE
=============== default temporary tablespace ============
alter database default temporary tablespace temp2;
============== temporary tablespace espace temporaire ==========
create temporary tablespace temp tempfile '/product_p08/oradata/temp/ECC/tsECC_TEMP_01.dbf' size 101M autoextend on next 100M maxsize 4001M;
=============== table dba_tables... ==========
ORA-00942: table or view does not exist
au sein d'une procedure stockee et/ou package
ce message n'apparait pas dans un bloc PL/SQL.
Il faut pour les procedures et packages avoir un grant direct et non
par un groupe d'utilisateur.
grant select on dba_tables to toto;
=============== gen_str ============
create or replace function gen_str2( p_n in number) return varchar2 as
v varchar2(32000);
begin
for i in 1..p_n
loop
v := v || 'B';
end loop;
return v;
end ;
.
/
limit 4000
=============== WHENEVER ============
WHENEVER OSERROR Exit if an OS error occurs
WHENEVER SQLERROR Exit if an SQL or PLSQL error occurs
===== code PL/SQL faux en temps de traitement retourn?par tkprof =====
exemple de code
declare
d date;
t float ;
begin
t := -500000;
select sysdate into d from dual;
for i in 1..5000000
loop
t := (t + i)/t;
end loop;
select sysdate into d from dual;
end;
.
/
tkprof donne 16s en elapse alors qu'en temps mesure on a 24s
AIX 5L (5.3)
===== fetch bulk =====
declare
n dbms_sql.NUMBER_table;
cursor c is select i from c;
begin
open c;
fetch c bulk collect into n;
for i in 1..n.count
loop
n(i) := n(i) * 1000;
end loop;
end;
.
/
===== insertion bulk et sans bulk =====
declare
v_nb_insert constant number := 500000;
TYPE temp_id IS VARRAY(500000) OF rr.id%type;
TYPE temp_lib IS VARRAY(500000) OF rr.l%type;
v_tab_id temp_id;
v_tab_lib temp_lib;
begin
v_tab_id := temp_id();
v_tab_lib := temp_lib();
for i in 1..v_nb_insert
loop
--v_tab_id.extend;
--v_tab_lib.extend;
insert into rr values (i,'Hello_' || to_char(i));
end loop;
end;
.
/
tps sur 8i : 53s
en bulk : 9s
declare
v_nb_insert constant number := 500000;
TYPE temp_id IS VARRAY(500000) OF rr.id%type;
TYPE temp_lib IS VARRAY(500000) OF rr.l%type;
v_tab_id temp_id;
v_tab_lib temp_lib;
begin
v_tab_id := temp_id();
v_tab_lib := temp_lib();
for i in 1..v_nb_insert
loop
v_tab_id.extend;
v_tab_lib.extend;
v_tab_id(i) := i;
v_tab_lib(i) := 'Hello_' || to_char(i);
end loop;
-- BULK INSERTION it's keyword forall and varray access
forall i in v_tab_id.first..v_tab_id.last
insert into rr values (v_tab_id(i),v_tab_lib(i));
end;
.
/
====== Nested Table & Varray =======
TYPE type_name IS TABLE OF element_type [NOT NULL]; => nested table
TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit) OF element_type [NOT NULL];
create table rr (id number, l varchar2(50));
declare
TYPE temp_table IS TABLE OF rr%rowtype;
v_temp temp_table;
v_r rr%rowtype;
v_n number; -- nombre d'elements
v_no number; -- index de parcours
begin
v_temp := temp_table();
v_temp.extend;
v_r.id := 1;
v_r.l := 'Hello';
v_no := v_temp.first;
dbms_output.put_line(v_no);
v_temp(v_no) := v_r;
v_n := v_temp.count;
dbms_output.put_line(v_n);
v_temp.extend;
v_temp(v_temp.next(v_no)) := v_r;
v_n := v_temp.count;
dbms_output.put_line(v_n);
end;
.
/
====== Oracle 10gR2 bug shared pool ======
# Suppression des warnings "Heap size xxxxK exceeds notification threshold (2048K)" dans l'alert.log
_kgl_large_heap_warning_threshold = 8388608
alter system set "_kgl_large_heap_warning_threshold"=8388708 scope=spfile;
====== connexion sans tnsnames.ora =====
sqlplus user/mdp@//ip_du_serveur:port_d'?oute_listener/service
Attention il FAUT un SERVICE NAME et NON SID
Par exemple essayez :
sqlplus system/manager@//128.239.245.206:1521/ga.fr.auchan.com
====== export/import script ======
export ORAENV_ASK=NO
export ORACLE_SID=WMSFELFR
. oraenv
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
exp parfile=exp_wmsfelfr_full.par
parfile=exp_wmsfelfr_full.par
userid=system
consistent=y
buffer=10485760
compress=n
rows=y
file=(exp_wmsfelfr_2005-12-05_01.dmp,exp_wmsfelfr_2005-12-05_02.dmp,exp_wmsfelfr_2005-12-05_03.dmp)
filesize=1800M
log=exp_wmsfelfr_2005-12-05.log
owner=(SA_FR01 ,
BCENT_FR01 ,
FAST_FR01 ,
PRESTA_FR01 ,
WEB_FR01 )
======= valeur par defaut du parametrage ======
alter system reset MonParam scope=spfile sid='*';
======= dbms_output =====
DBMS_OUTPUT.ENABLE (buffer_size IN INTEGER DEFAULT 20000);
======= Version Oracle & Version JVM =======
Oracle 10.2.0.1.
Version jvm :1.4.2_04 from Oracle Corporation
Oracle 9.2.0.6
======= MERGE INSERT/UPDATE =======
exemple de merge :
merge into t2 A using (select l,v from t1 order by l,v) B on (A.L = B.L)
when matched then update set a.v = decode(b.v,NULL,a.v,b.v)
when not matched then insert (a.l,a.v) values (b.l,b.v);
======= MODIFIER LE LOGIN =====
de sqlplus :
script glogin.sql dans $ORACLE_HOME/sqlplus/admin
======= STATPACK =======
Doc
UNIX:
Oracle8i 8.1.6
$ORACLE_HOME/rdbms/admin/statspack.doc
Oracle8i 8.1.7 and Oracle9i 9.x
$ORACLE_HOME/rdbms/admin/spdoc.txt
supprimer statpack
How do I remove StatsPack snapshots that I am no longer interested in?
This functionality is available starting with Oracle 8.1.7 using a script
called sppurge.sql located in $ORACLE_HOME/rdbms/admin.
SQL> connect / as sysdba
SQL> define default_tablespace='statspack'
SQL> define temporary_tablespace='temp'
SQL> @?/rdbms/admin/spcreate
Collecter l'etat de la base (statpack)
SQL> connect perfstat/perfstat
SQL> execute statspack.snap;
avoir dans le init.ora le parametre timed_statistics=true pour obtenir plus d'informations.
planifier le job de collecte des stats :
variable jobno number;
exec dbms_job.submit(:jobno, 'begin;statspack.snap;end;', sysdate+(5/(24*60)), 'SYSDATE+(5/(24*60))')
print jobno
show parameter job_queue_processes
alter system set job_queue_processes=2;
select job, next_date, next_sec
from user_jobs
where job = :jobno;
======= UTL_FILE DIR en 9.2.0.6 sur AIX =======
prvtfile.plb a zero. => existe patch pour corriger ce probl?e de la 9.2.0.6.
d'ou utl_file qui ne fonctionne plus.
======= flashback table ======
select * from dba_recyclebin;
select * from recyclebin;
flashback table aca.test to before drop;
======= drop temporary default database tablespace ==========
creation d'un tablespace temp2.
create temporary tablespace temp2 tempfile '/product_p01/oradata/DEALS/temp01.dbf' size 101M autoextend on next 100M maxsize 2001M;
alter database default temporary tablespace temp2;
le second devient le tablespace temporaire par defaut
drop tablespace temp;
on drop le temp;
alter tablespace temp2 rename to temp;
on renome temp2 en temp
======= create spfile et pfile =========
create spfile from pfile;
create pfile from spfile;
======= download database oracle =========
http://www.oracle.com/technology/software/products/database/oracle10g/index.html
======== PROCESS INSTANCE 10gR2 ============
http://localhost:8080/oracle10g_r2/server.102/b14220/process.htm#i24682
========= Cryptage ======
sqlnet.ora
WALLET_LOCATION=
(SOURCE=
(METHOD=file)
(METHOD_DATA=
(DIRECTORY=/product_p06/oracle/admin/DEALS/wallet)))
creation de la cle
alter system set encryption key authenticated by "toto";
ouverture de la cle
alter system set encryption wallet open authenticated by "toto";
fermeture de la cle
alter system set encryption wallet close;
creation d'une table avec colonne crypte
create table
(
id number,
name varchar2(30) encrypt
);
========= param compatible ======
alter system set compatible="10.2.0.1.0" scope=spfile;
====== UTF8 et AL32UTF8 =====
Note:237593.1
The default UTF8 characterset for 9i/10G is AL32UTF8, however this
characterset is NOT recognised by any pre-9i clients/server systems.
This causes several problems (see the bug list further in this note).
We recommend that you use UTF8 instead of AL32UTF8 as database characterset
if you have 8i (or older) servers and clients connecting to the 9i/10g system
until you can upgrade the older versions.
UTF8 is unicode revision 3.0 in 8.1.7 and up.
AL32UTF8 is Unicode 3.0 in 9.0.1, Unicode 3.1 in 9.2, Unicode 3.2 in 10.1
and Unicode 4.01 in 10.2
see also Note 260893.1 Unicode character sets in the Oracle database
======== load java ========
STEP 1 : ecriture du java
file : aca.java
public class aca
{
public static void main (String[] args)
{
System.out.println("Hello World !");
}
}
STEP 2 : Compilation
environnement
set PATH=c:\jdk1.3.1_16\bin
set JAVA_HOME=c:\jdk1.3.1_16
set CLASSPATH=.;c:\jdk1.3.1_16\lib
java -version
compilation
javac aca.java
production d'un exec class java => aca.class :
STEP 3 : Chargement de la class java en base
ftp + chargement dans la base :
[oracle@p520_3p02] /tmp/aca_java > loadjava -u aca/aca -v -r aca.class
arguments: '-u' 'aca/aca' '-v' '-r' 'aca.class'
created : JAVA$CLASS$MD5$TABLE
creating : class aca
created : CREATE$JAVA$LOB$TABLE
loading : class aca
resolving: class aca
STEP 4 : V?ifier la validit?de la classe charg?
check status of object java :
select object_name, object_type, status from user_objects
where object_name = 'aca';
Si la class est invalide => alter java class "aca" compile;
STEP 5 : Interface PL/SQL vers Java
wrap java class to PL/SQL
create or replace procedure Hello
as language java name 'aca.main(java.lang.String[])';
.
/
STEP 6 : Tester l'appel
executer la procedure :
set serveroutput on
exec dbms_java.set_output(5000);
exec Hello;
SQL> exec Hello
Hello World !
Proc?ure PL/SQL termin? avec succ?.
create or replace procedure get_url
as language java name 'gurl.main(java.lang.String[])';
.
/
create or replace procedure aca_get_url
as language java name 'gurl.main(java.lang.String[])';
.
/
create or replace and compile java source named "HelloWorld" as
public class HelloWorld
{
public static void main (String[] args)
{
System.out.println("Hello World !");
}
}
/
drop java class "gurl";
ou
si compile par le moteur oracle
drop java source "test_accord";
[oracle@p520_3p02] /tmp/aca_java > loadjava -u aca/aca -v -r gurl.class
arguments: '-u' 'aca/aca' '-v' '-r' 'gurl.class'
identical: gurl
The following operations failed
class gurl: creation
exiting : Failures occurred during processing
Pb car la session utilis?la classe java => deconnexion de la session et ensuite chargement r?ssi.
[oracle@p520_3p02] /tmp/aca_java > loadjava -u aca/aca -v -r gurl.class
arguments: '-u' 'aca/aca' '-v' '-r' 'gurl.class'
creating : class gurl
loading : class gurl
resolving: class gurl
Ouverture de session http en Java =>
dbms_java.grant_permission('ACA', 'SYS:java.net.SocketPermission', '128.239.18.24:80', 'connect,resolve' )
========= fetch sur une fenetre ========
Pour obtenir un fetch entre la ligne 10 et 20 pour un ensemble de donn?s E (bas?sur la table Test ) tri? sur la colonne id.
On part de la requ?e brute suivante (pas id?le avec son *) mais qui peut ?re tout autre requ?e de type SELECT
select * from Test
order by id desc;
on aurait tendance a ?rire :
select F.* from (select rownum, E.* from (select A.* from Test A order by name) E ) F
between F.rownum between 10 and 20;
On ?rira le SQL suivant, plus performant :
select F.* from
(select /*+ first_rows */ rownum nr,e.*
from
( select * from kk
order by id desc
) E
where rownum <= 20
) F
where F.nr >=10;
Merci ?Tom Kyte pour cette superbe astuce.
========================
Test jvm in Oracle
http://metalink.oracle.com/metalink/plsql/ml2_documents.showNOT?p_id=102717.1&p_showHeader=1&p_showHelp=0
configuration de la base :
http://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=103855.1&blackframe=1
http://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=156477.1&blackframe=1
1.4.1 Library Manager
To store Java classes in the Oracle Database, you use the loadjava command-line utility, which uses the SQL CREATE JAVA statements to do its work. When called by the CREATE JAVA {SOURCE | CLASS | RESOURCE} statement, the library manager loads Java source, class, or resource files into the database. These Java schema objects are not accessed directly, and only the Oracle JVM uses them.
1.4.2 Compiler
The Oracle JVM includes a standard Java compiler. When the CREATE JAVA SOURCE statement is run, it translates Java source files into architecture-neutral, one-byte instructions known as bytecodes. Each bytecode consists of an opcode followed by its operands. The resulting Java class files, which conform fully to the Java standard, are submitted to the interpreter at run time.
===== table sans PK ====
select table_name from dba_tables t
where owner = 'RCLI_QLF_DTA'
and not exists (
select 1
from
dba_constraints c
where c.owner=t.owner
and c.constraint_type = 'P'
and c.table_name=t.table_name
)
===== Trigger logon mise en trace d'une session lors de sa connexion ====
create or replace trigger ydl_logon_trigger after logon on database
declare v_osuser v$session.osuser%type;
begin
select osuser into v_osuser from v$session where audsid = sys_context('userenv', 'sessionid') and rownum < 2;
if (v_osuser = 'mco') then
-- ecriture dans l'alerte
-- sys.dbms_system.ksdwrt(2, to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS Dy') || ' YDL Tracage de ' || v_osuser);
execute immediate 'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 4''';
end if;
end; /
===== Job Oracle =====
Etre propri?aire du job pour le modifier ou appliquer la procedure
dbms_job.broken(NrJob,true);
commit;
remove job, supprimer job
exec dbms_job.remove(4)
commit;
===== PGA_AGGREGATE_TARGET ====
alter system set PGA_AGGREGATE_TARGET=200M scope=both;
alter system set WORKAREA_SIZE_POLICY=auto scope=both;
alter system set java_pool_size=100M scope=both;
select name,
to_char(decode( unit,
'bytes', value/1024/1024,
value ),'999,999,999.9') value,
decode( unit, 'bytes', 'mbytes', unit ) unit
from v$pgastat;
select
trunc(pga_target_for_estimate/1024/1024)
pga_target_for_estimate,
to_char(pga_target_factor * 100,'999.9') ||'%'
pga_target_factor,
trunc(bytes_processed/1024/1024) bytes_processed,
trunc(estd_extra_bytes_rw/1024/1024) estd_extra_bytes_rw,
to_char(estd_pga_cache_hit_percentage,'999') || '%'
estd_pga_cache_hit_percentage,
estd_overalloc_count
from v$pga_target_advice;
SELECT
case when low_optimal_size < 1024*1024
then to_char(low_optimal_size/1024,'999999') ||
'kb <= PGA < ' ||
(HIGH_OPTIMAL_SIZE+1)/1024|| 'kb'
else to_char(low_optimal_size/1024/1024,'999999') ||
'mb <= PGA < ' ||
(high_optimal_size+1)/1024/1024|| 'mb'
end pga_size,
optimal_executions,
onepass_executions,
multipasses_executions
from v$sql_workarea_histogram
where total_executions <> 0
order by low_optimal_size;
===== session mem AIX =========
Note:123754.1 - AIX: Determining Oracle memory usage on AIX
To reduce the size of an shadow process refer:
ps -elf
F S UID PID PPID C PRI NI ADDR SZ WCHAN STIME TTY TIME CMD
240001 A oracle 114942 1 0 60 20 4dfb5400 59792 f100090002b6b8c0 09:30:26 - 0:00 oracleFIOUL (LOCAL=NO)
240001 A oracle 176138 1 0 60 20 52d12400 54512 f100090004ed30c0 09:43:31 - 0:00 oracleFIOUL (LOCAL=NO)
240001 A oracle 180400 1 0 60 20 32bea400 17884 f1000900092d38c0 09:33:47 - 0:00 extprocPLSExtProc (LOCAL=NO)
240001 A oracle 184338 1 0 60 20 569d3400 59352 f1000900092c08c0 09:37:42 - 0:00 oracleFIOUL (LOCAL=NO)
240001 A oracle 192678 1 0 60 20 2604f400 17884 f1000900092f84c0 11:00:53 - 0:00 extprocPLSExtProc (LOCAL=NO)
[oracle@aixfr88830002p09] /product_p09/oracle >ps v 114942
PID TTY STAT TIME PGIN SIZE RSS LIM TSIZ TRS %CPU %MEM COMMAND
114942 - A 0:00 249 9996 23776 xx 49800 18092 0.0 1.0 oracleF
definition TRS = code segment
RSS = segment de memoire partage incluant le code segment (TRS)
Unite pour TRS et RSS sur aix est exprim?en Ko pour avoir le nombre de pages m?oire il faut diviser par 4.
Private memory : (RSS - TRS) x n + TRS pour n process => n fois la memoire priv?+ 1 fois la memoire partage.
====== TOAD DROITS POUR TRACE DE SESSION =======
grant select on v_$rollname to &user;
grant select on v_$transaction to &user;
grant select on v_$sqltext_with_newlines to &user;
grant select on v_$locked_object to &user;
grant select on v_$lock to &user;
grant select on v_$process to &user;
grant select on v_$session to &user;
grant select on v_$sess_io to &user;
grant select on v_$open_cursor to &user;
======= service NT lancement automatique ========
Dans le sqlnet.ora, sous Windows il FAUT la ligne suivante :
SQLNET.AUTHENTICATION_SERVICES = (NTS)
de plus l'utilisateur doit appartenir au groupe d'utilisateur ORA_DBA
======= EXPORT CONSISTANT =======
Toujours a realiser avec l'utilisateur SYSTEM surtout pas SYS ou / as SYSDBA
BUG non public (22/06/2005) : voir Note 277237.1
Bug 2996947 "EXP DID NOT RAISE ERROR WHEN SYSDBA EXPORTS WITH CONSISTENT=Y" (not a public bug; fixed in Oracle10g and higher)
Doc Oracle
CONSISTENT=y is unsupported for exports that are performed when you are
connected as user SYS or you are using AS SYSDBA, or both.
======== EVENT ========
event="10046 trace name context forever, level 4"
event="10046 trace name context forever, level 12"
pour r?liser un export avec des blocs corrompus en 8.0.4
event="10231 trace name context forever, level 10"
======== index invalides =========
tables partitionees
select distinct 'rebuild_part_idx.sh ' || table_name || ' 8'
from dba_indexes
where index_name in (select distinct index_name from dba_ind_partitions
where owner = 'DSSADMIN' and status = 'UNUSABLE')
and owner = 'DSSADMIN';
======== milliseconde 7,8,8i =========
create or replace function julian(d1 date) return varchar2
as
n1 number(20,6);
c1 varchar2(20) := '';
begin
c1 := to_char(d1,'J');
n1 := to_number(to_char(d1,'SSSSS'))/86399;
c1 := c1 || to_char(n1);
return c1;
end;
/
select julian(sysdate) from dual;
select ((sysdate-to_date('10/05/2005','DD/MM/YYYY'))-trunc(sysdate-to_date('10/05/2005','DD/MM/YYYY')))*86399 from dual;
======== Virtual private database ========
http://www.oracle.com/technology/obe/obe9ir2/obe-sec/vpd/vpd.htm
http://www.oracle-base.com/articles/8i/VirtualPrivateDatabases(VPD).php
======== UNUSABLE & REBUILD PARTITION INDEX ========
select 'alter index ' || index_name || ' rebuild partition ' ||
partition_name || ';'
from user_ind_partitions
where status = 'UNUSABLE';
alter index I_ESPS_N3 modify partition SEM200414 unusable;
alter index I_ESPS_N3 rebuild partition SEM200414;
alter index Nom_Index modify partition index_partition unusable;
alter index Nom_Index rebuild partition index_partition;
unusable sur toutes les partitions d'index associ?s ?une partition de table
alter table test modify partition PT_TEST_00 unusable local indexes;
inactivation des partitions d'index :
de l'index TEST_IDX2 la partition PT_TEST_00
de l'index TEST_IDX la partition IDX_PT_TOTO_00
rebuild de toutes les partitions d'index associees a une partion de table:
alter table test modify partition PT_TEST_00 rebuild unusable local indexes;
Ajout et drop de partition
alter table W23_FRU_PRD add partition PT_W21_EXT_TRU_01 VALUES LESS THAN (1) NOLOGGING;
alter table W23_FRU_PRD drop partition PT_W21_EXT_TRU_01;
alter index W23_ART_FCO_IDX modify partition PT_W23_FRU_TRU_00 unusable;
alter index W23_ART_FCO_IDX rebuild partition PT_W23_FRU_TRU_00;
============ CHAINAGE SUR PARTITION ===============
Probl?e de cha?age en 9.2.0.4, r?lisation d'un jeu de test, le script insert_aca_part.sql.
En cr?nt une table partitionn? ayant 10 partitions, nous ins?ons 5000 lignes avec un libell?court et ensuite nous r?lisons un update sur ce libell?court pour le passer ?68 caract?es. Il y a cr?tion de cha?age, suite au alter table aca_part move partition nnn, la r?rganisation est bien r?lis? mais il faut passer un analyze table nom_table compute statistics; pour mettre ?jour les donn?s du dictionnaire au niveau de la colonne chain_cnt de la table dba_tab_partitions.
Ce probl?e est reproductible en 8.1.7.4, 9.2.0.4, 9.2.0.6 et 10.1.0.3.
================== SUPPRIME LES ESPACES A DROIT ==============
set linesize 200
set trimspool on
================== TABLES EXTERNES =================
sous system creer le repertoire :
donner le droit de creer tout repertoire : CREATE ANY DIRECTORY => grant CREATE ANY DIRECTORY to scott;
create directory aca_ext as '/product_p02/oracle/aca_ext';
grant read,write on directory aca_ext to aca;
sous l'utilisateur aca
create table ext_test
(
id number(4),
firstname varchar2(30),
lastname varchar2(30)
)
organization external (type oracle_loader default directory aca_ext
access parameters (fields terminated by ';')
location ('test.dat'));
Fichier test.dat
1;Jean;Dubois
2;Pierre;Dupont
3;Chris;Toto
4;Yves;Aca
============== Fonction ANALYTIQUE =================
select
unite,
etape || '-->' || lead(etape,1,etape+1) over (order by unite,etape,dt) pass_step,
dt,
lead(dt,1) over (order by unite,etape,dt),
(lead(dt,1) over (order by unite,etape,dt) - dt)*24*60*60 duree_mn,
to_date(to_char(round((lead(dt,1) over (order by unite,etape,dt) - dt)*24*60*60)),'SSSSS') d
from tps_tt;
UNITE PASS_STEP DT LEAD(DT,1)OVER(ORDE DUREE_MN
---------- ---------- ------------------- ------------------- ----------
1 1-->2 25/02/2005 14:00:00 25/02/2005 14:02:00 2
1 2-->3 25/02/2005 14:02:00 25/02/2005 14:05:00 3
1 3-->1 25/02/2005 14:05:00 25/02/2005 15:02:00 57
2 1-->2 25/02/2005 15:02:00 25/02/2005 15:03:02 1,03
2 2-->3 25/02/2005 15:03:02 25/02/2005 15:06:00 2,97
2 3-->4 25/02/2005 15:06:00
================== TABLE TEMPORAIRE ================
create global temporary table aca_res_mensuel
(
PRD_IDT NUMBER(10),
MAG_IDT NUMBER(3),
FCD_IDT NUMBER(10),
MOI_VTE NUMBER(6),
DER_ACHT NUMBER(6),
VEN_MNTBRT NUMBER,
VEN_MNTPXR NUMBER,
VEN_MNTPVV NUMBER,
VEN_MNTRMM NUMBER,
VEN_MNTTAX NUMBER,
VEN_MNTVGN NUMBER,
VEN_MNTTPL NUMBER,
VEN_MNTTVA NUMBER,
VEN_TVAPVC NUMBER,
VEN_TVAPVV NUMBER,
VEN_MNTRFA NUMBER,
VEN_MNTPRE NUMBER,
VEN_MNTCFD NUMBER,
VEN_MNTTX4 NUMBER,
VEN_MNTTX5 NUMBER
) on commit preserve rows;
create global temporary table tcf as select * from flux where 1=0;
par defaut conserve les lignes le temps de la transaction.
create global temporary table tcf on commit preserve rows as select * from flux where 1=0;
================== drop colonne ====================
alter table aca_vte_der_acht_hebdo2 drop column MaColonne;
=====================================================
CREATION D'UNE TABLE PARTITIONNE
CREATE TABLE ACA_VTE_DER_ACHT2
(
PRD_IDT NUMBER(10),
MAG_IDT NUMBER(3) ,
SCT_IDT NUMBER(3) ,
MOIS_VTE NUMBER(6) ,
DER_ACHT NUMBER(6)
)
PARTITION BY RANGE (MOIS_VTE)
(
PARTITION MOI200311 VALUES LESS THAN (200312)
NOLOGGING,
PARTITION MOI200312 VALUES LESS THAN (200401)
NOLOGGING,
PARTITION MOI200401 VALUES LESS THAN (200402)
NOLOGGING,
PARTITION MOI200402 VALUES LESS THAN (200403)
NOLOGGING,
PARTITION MOI200403 VALUES LESS THAN (200404)
NOLOGGING,
PARTITION MOI200404 VALUES LESS THAN (200405)
NOLOGGING,
PARTITION MOI200405 VALUES LESS THAN (200406)
NOLOGGING,
PARTITION MOI200406 VALUES LESS THAN (200407)
NOLOGGING,
PARTITION MOI200407 VALUES LESS THAN (200408)
NOLOGGING,
PARTITION MOI200408 VALUES LESS THAN (200409)
NOLOGGING,
PARTITION MOI200409 VALUES LESS THAN (200410)
NOLOGGING,
PARTITION MOI200410 VALUES LESS THAN (200411)
NOLOGGING,
PARTITION MOI200411 VALUES LESS THAN (200412)
NOLOGGING,
PARTITION MOI200412 VALUES LESS THAN (200501)
NOLOGGING
)
NOCACHE
NOPARALLEL;
ALTER TABLE p ADD CONSTRAINT PK_P PRIMARY KEY (codsai,codsocex,codsoc,numsoc)
using index local
( partition P_SAI64_SOCEX0_SOC0_PK tablespace NIVEAU2_MIXTE_2003_064_IDX,
partition P_SAI64_SOCEX0_SOC1_PK tablespace NIVEAU2_MIXTE_2003_064_IDX
...
);
CREATE index IDX_N1
ON ACA_VTE_DER_ACHT2(sct_idt)
LOCAL
(
PARTITION MOI200311 tablespace ITEMDATA,
PARTITION MOI200312 tablespace itemdata,
PARTITION MOI200401 tablespace itemdata,
PARTITION MOI200402 tablespace itemdata,
PARTITION MOI200403 tablespace itemdata,
PARTITION MOI200404 tablespace itemdata,
PARTITION MOI200405 tablespace itemdata,
PARTITION MOI200406 tablespace itemdata,
PARTITION MOI200407 tablespace itemdata,
PARTITION MOI200408 tablespace itemdata,
PARTITION MOI200409 tablespace itemdata,
PARTITION MOI200410 tablespace itemdata,
PARTITION MOI200411 tablespace itemdata,
PARTITION MOI200412 tablespace itemdata
);
CREATE TABLE MYEMP
(EMPNO NUMBER,JOB VARCHAR2(20), DEPTNO NUMBER)
PARTITION BY RANGE (DEPTNO)
SUBPARTITION BY HASH (JOB) SUBPARTITIONS 4
( PARTITION P1 VALUES LESS THAN (11),
PARTITION P2 VALUES LESS THAN (21),
PARTITION P3 VALUES LESS THAN (31),
PARTITION P4 VALUES LESS THAN (MAXVALUE));
Oracle 10g en RAC sour Linux avec VMWare
http://www.oracle.com/technology/tech/linux/vmware/cookbook/index.html
OFA
(for 9i - Administering Oracle, for 10g - Installing Oracle)
http://www.oracle.com/technology/documentation/oracle9i.html
Ajout de redolog / logfile
ALTER DATABASE ADD LOGFILE ('/product_p01/oracle/log1/CAR8I/log14CAR8I.dbf','/product_p01/oracle/log1/CAR8I/log24CAR8I.dbf') SIZE 50M;
Lib et variable Unix attention ?l'AIX => LIBPATH
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib:$ORACLE_HOME/network/lib
export LIBPATH=$LD_LIBRARY_PATH
ecrire dans fichier de trace
EXEC DBMS_System.ksdwrt(n, message);
EXEC DBMS_System.ksdwrt(2, 'My Test Alertlog Message');
Mettre un event par dbms_system
EXEC DBMS_System.Set_Ev(sid, serial#, event, level, name);
EXEC DBMS_System.Set_Ev(31, 97, 10046, 4, '');
drop tempfile
alter database tempfile '/u01/app/oracle/oradata/temp/CAR/tsCAR_TEMP_01.dbf' drop;
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/temp/CAR/tsCAR_TEMP_01.dbf'
SIZE 105906176 REUSE AUTOEXTEND ON NEXT 104857600 MAXSIZE 2001M;
Constraint Contraintes
ALTER TABLE RMI ADD
CONSTRAINT FK_RMI_TO_DFA FOREIGN KEY (DFA_IDT)
REFERENCES DFA (DFA_IDT)
ON DELETE CASCADE ENABLE NOVALIDATE;
TBS extent management local
create tablespace M01_data datafile '/product_e33/oracle/data/PCO00/tsM01_DATA_01.dbf' size 1M autoextend on next 100M maxsize 2001M extent management local autoallocate;
create tablespace data datafile '/product_e01/oracle/data/O817/tsO817_DATA_01.dbf' size 1M autoextend on next 100M maxsize 2001M extent management local autoallocate;
AUTO EXTEND
alter database datafile '/product_a77/oracle/rbs/CAR/tsCAR_RBS_1.dbf' size 1M autoextend on next 100M maxsize 1001M;
Ecriture blob dans un fichier :
DECLARE
aFile UTL_FILE.FILE_TYPE ;
aBlob BLOB ;
offset INTEGER := 1 ;
amount INTEGER := 32767 ;
aRaw RAW(32767) ;
BEGIN
SELECT blob_col
INTO aBlob
FROM lob_table
WHERE ROWNUM = 1 ;
aFile := UTL_FILE.FOPEN('/path','file.name','w', 32767) ;
LOOP
DBMS_LOB.READ(aBlob, amount, offset, aRaw) ;
offset := offset + amount ;
UTL_FILE.PUT_RAW(aFile, aRaw, TRUE) ;
UTL_FILE.FFLUSH(aFile) ;
EXIT WHEN amount
END LOOP ;
UTL_FILE.FCLOSE(aFile) ;
END ;
SNAPSHOT
CREATE SNAPSHOT SNP_TRANCHE_DE_PRIX
TABLESPACE DIM_DATA
BUILD DEFERRED
USING INDEX TABLESPACE DIM_INDEX
REFRESH WITH ROWID
AS
SELECT
CODE_SECTEUR,
CODE_FAMILLE,
CODE_SAISON,
IDENTIFIANT,
BORNE_INF,
BORNE_SUP,
DEVISE
FROM TRANCHE_DE_PRIX@dbecc;
exec dbms_refresh.make('catalogue_ecc','SNP_ARTICLE,SNP_COLLECTION,SNP_CTG',sysdate,'sysdate+30');
exec dbms_refresh.refresh('catalogue_ecc'); dbms_refresh.destroy('catalogue_ecc');
refresh un snapshot :
exec dbms_snapshot.refresh('rem');
exec dbms_snapshot.refresh('rayon');
sqlldr sql loader sql*loader
example de ctl, pour chargement de donn?s fichier longeur fixe
LOAD DATA
INFILE 'MAP_EAF.dat'
INTO TABLE aca_sea
(
sea_ann POSITION(01:04) CHAR,
sea_moi POSITION(05:06) CHAR,
sea_sem POSITION(07:08) CHAR,
sea_codfam POSITION(09:12) CHAR,
sea_chrcug POSITION(13:22) CHAR,
sea_chrfou POSITION(23:32) CHAR,
sea_chrgrp POSITION(33:42) CHAR "nvl(:sea_chrgrp,'0000000000')",
sea_uniach POSITION(43:43) CHAR,
sea_chrmag POSITION(44:47) CHAR,
sea_mntbrt POSITION(48:63) CHAR ":sea_mntbrt/100",
sea_nbrart POSITION(64:79) CHAR ":sea_nbrart/100"
)
chargement de donn?s d'un fichier de longeur variable type csv
LOAD DATA INFILE prd.txt
INSERT
INTO TABLE prd2
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED by '`'
(
id_prd,
lib
)
Fonction analytique
select acc, sum(value) over (partition by to_char(dt,'MM')) , count(value) over (partition by acc) from tr ;
=========================================================================================
You can do this by:
Tracing the query coordinator using <Event:10046> Level 12
or
-- get the query coordinators
select distinct KXFPDPCPR creator, qc.SID
from X$KXFPDP qs, V$SESSION qc
where KXFPDPCPR!=hextoraw('0')
and KXFPDPPRO!=hextoraw('0')
and bitand(KXFPDPFLG,16)=0 /* Slave not Idle */
and qc.paddr=qs.KXFPDPCPR
and (inst_id=0 or qc.sid=qs.inst_id);
-- get the slaves associated with the above creator
select KXFPDPNAM name, KXFPDPPRO producer
from X$KXFPDP
where qs.KXFPDPCPR=creator
and KXFPDPPRO!=hextoraw('0');
From: Claudia O'Callaghan 28-Feb-02 00:06
Subject: Re : Mapping a parallel query process to its master process
You can look at v$px_session, it will tell you the QC sid and slave sids for active parallel queries.
===========================================================================================
Calcul des stat en 8i
/* 10Mo */
ALTER SESSION SET sort_area_size=10485760;
/* 100Mo */
alter session set sort_area_size=104857600;
CETTE PARTIE PORTANT SUR LA COMMANDE ANALYZE EST TOTALEMENT OBSOLETE A PARTIR DE LA 8I
QUE FAIT LA COMMANDE ANALYZE ?
analyze table t compute statistics;
ou estimate statistics sample 10 percent;
- calcul des statistics de la table
- calcul des statistics sur les index
- calcul des statistics sur TOUTES LES COLONNES !!!
- calcul des histogrammes sur TOUTES LES COLONNES DE LA TABLE (nb de buckets 2 ?uivalent ?size 1),
pas vrai on 2 buckets avec des valeurs ?null ce qui corresponds aux dbms_stats.gather_table_stats()!!!!
select COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE,endpoint_actual_value from user_tab_histograms where table_name = 'T'
minus
select COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE,endpoint_actual_value from user_tab_histograms where table_name = 'TT';
equivalent ?
analyze table t compute statistics for table for all indexes for all columns size 1;
INFO SUR LES STATISTIQUES
INFO STAT TABLE
~~~~~~~~~~~~~~~
select table_name,global_stats,num_rows,blocks from user_tables where table_name = '&tab_name';
select table_name,num_rows,blocks,last_analyzed
from user_tables
where table_name = '&tab_name';
INFO STAT COLONNES
~~~~~~~~~~~~~~~~~~
select
table_name,
column_name,
num_distinct,
density,
num_nulls,
num_buckets,
global_stats
from user_tab_col_statistics
where table_name='&tab_name';
select index_name,num_rows,blevel from user_indexes where table_name = '&tab_name';
Histogramme :
~~~~~~~~~~~~~
select table_name,column_name,endpoint_number nb_buckets from user_tab_histograms where table_name = '&tab_name';
select table_name,column_name,endpoint_number nb_buckets,endpoint_actual_value from user_tab_histograms where table_name = '&tab_name';
select table_name,column_name,count(*)
from user_tab_histograms
where table_name = '&tab_name'
group by table_name,column_name;
si pas d'histo alors
select * from user_tab_histograms
retourne : no rows selected
select count(distinct column_name) cnt from user_tab_histograms where table_name = '&tab_name';
effacer les statistiques :
analyze table T delete statistics;
Pour revenir sur la question du calcul des statistiques sur toutes bases de donn?s > 8.0 c'est-?dire 8i,9i,10g nous utilisons la m?hode suivante :
exec dbms_stats.gather_table_stats(user, '&table_name', method_opt=>'for all indexed columns size 1', degree=>X,cascade=> true);
Pour les quelques bases en 8.0.X nous devons encore et toujours utiliser la commande analyze.
Il est pr??able de passer la commande en compute lorsque la volum?rie le permet.
Nous conseillons la commande suivante :
analyze table XXXX compute statistics for table for all indexes for all indexed columns size 1; Ou alors plus judicieux lors que l'on connait les requ?es attaquant la table en question, identifier les colonnes utilis? dans les clauses WHERE et les reprendre au niveau de la commande analyse via cette forme, c'est de loin la meilleure m?hode :
analyze table XXXX compute statistics for table for all indexes for columns COL1 size 1, COL2 size 1; Cette commande calculant les statistiques de la table, des index et sur les colonnes COL1 et COL2 suivant la m?hode compute.
Si la m?hode estimate doit ?re utilis?, alors employer cette serie de commandes analyze, en ayant identifi?au pr?lable les colonnes utilis?s dans les clauses WHERE :
analyze table XXXX estimate statistics sample YY percent for table for columns COL1 size 1, COL2 size 1; analyze index AAA compute statistics; analyze index BBB compute statistics; ...
outil de diagnostic de structure, ne calcul pas les stats :
analyze table t validate structure;
exec dbms_stats.gather_table_stats(user, '&table_name', method_opt=>'for all columns', degree=>4,cascade=> true);
par defaut size = 74
Calcul des statistiques en 10g
dbms_stats.gather_table_stats(user,'&table_name')
dbms_stats.gather_table_stats(user,'&table_name',degree=>&n)
Calcul des statistiques en 8i/9i
exec dbms_stats.gather_table_stats(user, '&table_name', method_opt=>'for all indexed columns size 1', degree=>4,cascade=> true);
exec dbms_stats.gather_table_stats('dbacarmap', 'SEA', method_opt=>'for all indexed columns size 1', degree=>4,cascade=> true);
exec dbms_stats.gather_table_stats('dbacarmap', 'EMA', method_opt=>'for all indexed columns size 1', degree=>4,cascade=> true);
exec dbms_stats.gather_table_stats('dbacarmap', 'EMV', method_opt=>'for all indexed columns size 1', degree=>4,cascade=> true);
exec dbms_stats.gather_table_stats('dssadmin', 'VMPM', method_opt=>'for all indexed columns size 1', degree=>8,cascade=> true);
exec dbms_stats.gather_table_stats('dssadmin', 'EMPS', method_opt=>'for all indexed columns size 1', degree=>8,cascade=> true);
exec dbms_stats.gather_table_stats('dssadmin', '', method_opt=>'for all indexed columns size 1', degree=>8,cascade=> true);
exec dbms_stats.gather_table_stats('dssadmin', 'VSPM', method_opt=>'for all indexed columns size 1', degree=>8,cascade=> true);
exec dbms_stats.gather_table_stats('dssadmin', 'EAN', method_opt=>'for all indexed columns size 1', degree=>8,cascade=> true);
exec dbms_stats.gather_table_stats('dssadmin', 'OPE_PRD', method_opt=>'for all indexed columns size 1', degree=>8,cascade=> true);
exec dbms_stats.gather_table_stats('dssadmin', 'MAG', method_opt=>'for all indexed columns size 1', degree=>8,cascade=> true);
exec dbms_stats.gather_table_stats('dssadmin', 'PRD', method_opt=>'for all indexed columns size 1', degree=>8,cascade=> true);
calcul au niveau de la partition
exec dbms_stats.gather_table_stats(user, 'aca_part', partname=>'val_5',method_opt=>'for all indexed columns size 1',cascade=> true);
!!!!!!!! ALL COLUMNS !!!!!!!
begin
dbms_stats.gather_schema_stats(ownname=> 'DBAOPTIC', -- le schema dont il aut calculer les stats
estimate_percent => NULL, -- compute pas d'estimate
block_sample => FALSE, -- pas de random sur les blocs n'a de sens qu'en estimate
method_opt=>'FOR ALL COLUMNS SIZE 1', -- calcul les stats en //
degree=>4, -- le degree de parall?isme
granularity=> 'DEFAULT', -- global table et partition
cascade=>TRUE); -- calcul des stats sur les index
end;
.
/
begin
dbms_stats.gather_schema_stats(ownname=> 'DBAOPTIC', -- le schema dont il aut calculer les stats
estimate_percent => NULL, -- compute pas d'estimate
block_sample => FALSE, -- pas de random sur les blocs n'a de sens qu'en estimate
method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1', -- calcul les stats en //
degree=>4, -- le degree de parall?isme
granularity=> 'DEFAULT', -- global table et partition
cascade=>TRUE); -- calcul des stats sur les index
end;
.
/
begin
DBMS_STATS.GATHER_DATABASE_STATS (
estimate_percent NUMBER DEFAULT NULL,
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT 'FOR ALL COLUMNS SIZE 1',
degree NUMBER DEFAULT NULL,
granularity VARCHAR2 DEFAULT 'DEFAULT',
cascade BOOLEAN DEFAULT FALSE);
end;
.
/
exec DBMS_STATS.DELETE_TABLE_STATS(ownname=>user,tabname=>'ACA_TKAU200',cascade_columns=>true,cascade_indexes=>true);
exec dbms_stats.gather_table_stats(user, 'ACA_TKAU200',cascade=> true);
===========================================================================================
IMPORTER UNE TABLE
userid=system/manager
file=pipe_imp_CARMAP
log=20041011_imp_DFA.log
ignore=y
buffer=10485760
fromuser=carmap
touser=carmap
tables=dfa
indexes=n
constraints=no
commit=y
note : constraints = no uniquement en 8i
creation rollback rbs
create public rollback segment r01 storage ( initial 10M next 10M minextents 40 maxextents unlimited optimal 400M ) tablespace rbs;
Oracle
Memoire partag? :
ipcs -a | grep oracle
pour obtenir les segments de m?oire.
exemple d'affichage :
Shared Memory:
T ID KEY MODE OWNER GROUP CREATOR CGROUP NATTCH SEGSZ CPID LPID ATIME DTIME CTIME
m 0 0x3253bc5c --rw-rw-rw- root system root system 2 648 2058 2072 17:51:40 17:51:39 17:51:39
m 13825 0x6e53879 --rw-r----- oracle dba oracle dba 14 784 10668 551 11:42:00 11:42:00 1:56:38
m 15362 0xae53879 --rw-r----- oracle dba oracle dba 14 436207616 10668 551 11:42:00 11:38:52 1:56:38
m 7171 0x6d7388e --rw-r----- oracle dba oracle dba 17 784 18545 32641 14:26:28 14:37:05 12:34:44
m 7172 0xad7388e --rw-r----- oracle dba oracle dba 17 125829120 18545 32641 14:26:28 14:37:05 12:34:44
m 517 0x6fd33d6 --rw-r----- oracle dba oracle dba 8 784 26277 9042 18:34:21 18:34:21 14:59:47
m 518 0xafd33d6 --rw-r----- oracle dba oracle dba 8 117440512 26277 9042 18:34:21 18:34:21 14:59:47
m 7 0x6fd32bc --rw-r----- oracle dba oracle dba 13 784 3668 550 11:41:57 11:41:57 18:25:02
pour purger un segment de m?oire :
utiliser la commande
ipcrm -m ID
pour supprimer ces segments.
Export pour evaluer la taille de l'export compresse :
compress < aca_pipe | dd bs=1024 of=/dev/null&
exp system owner=dbasasco log=exp_aca_dbasasco_20050929.log file=aca_pipe
EXPORT
Parametrage d'export
SELECT 'NLS_LANG='||t3.VALUE ||'_'|| t2.VALUE ||'.'|| t1.VALUE
FROM nls_database_parameters t1,
nls_database_parameters t2,
nls_database_parameters t3
WHERE t3.PARAMETER='NLS_LANGUAGE'
AND t2.PARAMETER='NLS_TERRITORY'
AND t1.PARAMETER='NLS_CHARACTERSET' ;
export fichier de param?rage
tables=(dbasipp.produit,dbasipp.TMP_PRIX2_DET, dbasipp.CUMULPERFOURN)
userid=system/password
file=exp_tab_prd_20040407.dmp
log=exp_tab_prd_20040407.log
compress=n
export par pipe
/usr/sbin/mknod my_pipe_exp p
compress <my_pipe_exp >exp_com_20040323.dmp.Z &
ou
gzip -dc exp_aca_medleyqt.dmp.gz > aca_imp_pipe &
exp system/manager full=yes log=exp_file.log file=my_pipe_exp buffer=3072000 compress=y
exp owner=dbasipp log=20040415_exp_sipp.log file=my_pipe_exp buffer=3072000 compress=n
exp system/FVD01 tables=ADM_FVD.CRESEL_W rows=y compress=N direct=y buffer=30720000 file=my_pipe_exp log=exp_comm_20040323.log
Import fromuser touser
export NLS_LANG=AMERICAN_AMERICA.WE8DEC
export ORACLE_SID=ECC
imp system file=exp_ecc_20040604.dmp fromuser=dbaecc touser=aca_imp tables=EAFMAP commit=y buffer=5475400 log=imp_eafmap_20040508.log
Partition
d?ombre les lignes d'une partition :
select count(*) from Table partition ( nom_partition);
La d?inition des partitions d'une table se trouve au sein de la table :
DBA_TAB_PARTITIONS
Oracle password utilisateur
Alter user toto identified by values qqqqqqqqqqqqqqqqqqqqqqq
Oracle
r?up?ation def sch?a
exp userid=/ owner=some_schema
imp userid=/ indexfile=foo.sql
================================================================================
Tuer une requ?e d'un utilisateur sans tuer la session
"Est-il possible de stopper une requ?e SQL active sans tuer la session Oracle d'un utilisateur?"
Il existe deux m?anismes non document? ( donc non support?) :
* kill -2 sur le UNIX PID du process ORACLE ( tu trouve ce PID avec TOAD ou en joignant "v$process.addr = v$session.paddr")
* d?erminer le UNIX PID du process Oracle, puis
sqlplus internal
oradebug setospid PID
oradebug event 10237 trace name context forever
oradebug event 10237 trace name context off
exit
La session de l'utilisateur va recevoir
ORA-01013: user requested cancel of current operation
================================================================================
Oracle
exemple curseur
declare
cursor c is select DT_DEB "date deb 1",DT_DEB "date deb 2" from log_batch;
v_d1 date;
v_d2 date;
begin
open c;
loop
fetch c into v_d1, v_d2;
exit when c%notfound;
dbms_output.put_line( 'val d1 : ' || v_d1 || ' val d2 : ' || v_d2);
end loop;
end;
.
/
===============================================================================
Oracle
diff
Ceil
round
floor
==============================================================================
Cette base est h?erg? par une machine SUN-SOLARIS 2.8 . La version Oracle utilis? est 8171.
De temps ?autre, cette base est fig?, le seul contournement pour le moment consiste en un arr?/relance de la base .
Sans une observation plus fine, il est impossible pour le moment d'analyser le probl?e .
Mes recommandations sont :
* installer STATSPACK dans cette base et faire un relev?toutes les heures ( level 0)
* si le blocage revient , il faut effectuer le dump suivant ( avant l'arr?/relance de la base) :
sqlplus internal
alter session set events 'immediate trace name systemstate level 2';
!sleep 30
alter session set events 'immediate trace name systemstate level 2';
!sleep 30
alter session set events 'immediate trace name systemstate level 2';
exit
Trouver le fichier trace g???dans UDUMP et nous l'envoyer . Ce dump nous permettra d'identifier le type de blocage et de trouver la cause .
Installer Oracle 8.1.7 sur XP
Copier le CD sur le disque dur et renomer la dll symcjit.dll en symcjit.old
Lancer l'installation.
INSTALLER SUR LINUX SANS VERIF SUR LE SYSTEME
./runInstaller -ignoreSysPrereqs
Opatch
derni?e version de optach
http://updates.oracle.com/download/2617419.html
Oracle optimisation par hint
INDEX (table, index)
USE_NL(table)
PARALLEL(scott_emp, 5)
/*+ NOPARALLEL(hr_emp) */
/*+ ORDERED */
Favoriser les index :
/* valeur par defaut 100, cout standard d'utilisation des index */
alter session set optimizer_index_cost_adj=50;
Alter session date
alter session set nls_date_format = 'DD/MM/YYYY hh24:mi:ss';
Fonction Oracle
instr trouver la position d'un chaine
instr(chaine,sous_chaine_a_trouver,pos,nb occurrence)
instr('/abc/toto','b') => trouve la 1ere occurrence de b = 2
instr('/abc/toto','/',-1) => trouve la 1ere occurence de / en partant de la fin
/product_a25/oracle/index2/SIPP806/tsSIPP806_SIPP_INDEX_MDD1.dbf
-- donne le nom du fichier
select substr(name,instr(name,'/',-1,1)+1) from v$datafile;
-- donne le chemin du fichier (basename)
select substr(name,1,instr(name,'/',-1,1)) from v$datafile;
select 'alter database rename file ''' || member || ''' to ''/product_e01/oracle/SIPP806/' || substr(member,instr(member,'/',-1,1)+1) || ''';'
from v$logfile;
Export
SELECT 'NLS_LANG='||t3.VALUE ||'_'|| t2.VALUE ||'.'|| t1.VALUE
FROM nls_database_parameters t1,
nls_database_parameters t2,
nls_database_parameters t3
WHERE t3.PARAMETER='NLS_LANGUAGE'
AND t2.PARAMETER='NLS_TERRITORY'
AND t1.PARAMETER='NLS_CHARACTERSET' ;
Profiling oracle optimisation
connexion en tant que sys
@$ORACLE_HOME/rdbms/profload.sql
executer le script suivant au sein du schema utilisateur poss?ant les procedures ?analyser.
@$ORACLE_HOME/rdbms/proftab.sql
Note : les compteurs sont donn? en nano-seconde
exemple d'utilisation
var b number
exec :b := DBMS_PROFILER.START_PROFILER('premier test' ,'test')
exec sp_test
exec :b := DBMS_PROFILER.STOP_PROFILER
l'exploitation des r?ultats se r?lise au niveau des tables
PLSQL_PROFILER_RUNS
PLSQL_PROFILER_UNITS
PLSQL_PROFILER_DATA
Synth?e des temps d'execution au niveau des unit? d'ex?ution :
select u.runid,u.unit_number,u.unit_name,round(sum(d.total_time)/1000000000) tps_sec
from PLSQL_PROFILER_DATA d,PLSQL_PROFILER_UNITS u
where u.runid = d.runid
and
u.unit_number = d.unit_number
group by u.runid,u.unit_number,unit_name;
pour analyse au nivau d'une procedure utiliser le script prof_sp.sql
NAME LINE# TEXTE TOTAL_OCCUR TPS_SEC
---------- ---------- ---------------------------------------- ----------- ----------
SP_TEST 4 insert into trace values (seq_trc.next 1 0
SP_TEST 5 select count(*) into c from test; 1 0
SP_TEST 6 insert into trace values (seq_trc.next 1 0
SP_TEST 7 update test set lib='bonjour'; 1 21
SP_TEST 8 insert into trace values (seq_trc.next 1 0
SP_TEST 9 update test set lib='hello'; 1 21
SP_TEST 10 insert into trace values (seq_trc.next 1 0
SP_TEST 11 end sp_test; 1 0
Event bind variable
Tuning avec l'??ement 10046
Le noyau Oracle fournit des informations de tr? bas niveau si certaines interruptions lui sont impos?s, interruptions appel?s events. Un des events le plus profitable pour celui qui analyse les performances Oracle est l'event 10046. Cette interruption peut offrir de plus en plus d'informations en fonction du niveau de d?oguage auquel elle est initialis?. Les r?ultats de cette action se retrouvent dans un fichier trace de la session qui est trac?. L'event 10046 pr?ente ?son niveau 1 les m?es informations que pr?ente l'option SQL_TRACE. A ses niveaux sup?ieurs, l'event 10046 fournit des informations concernant les attentes syst?e rencontr?s pendant l'ex?ution des divers ordres SQL, offrant des d?ails extr?ement int?essants sur le comportement syst?e et permettant une fine analyse qui g??era pratiquement imm?iatement les bonnes r?olutions concernant le tuning.
Tafora utilise tr? souvent les r?ultats de l'event 10046 pour d?ister et optimiser les instructions SQL rebelles.
Niveaux de tra?ge de l'event 10046
10046 trace name context forever, level 1
Il a le m?e comportement que set SQL_TRACE ON, il trace les ordres SQL.
10046 trace name context forever, level 4
M?e comportement que level 1 et montre les bind variables.
10046 trace name context forever, level 8
M?e comportement que level 1 et montre les attentes
10046 trace name context forever, level 12
C'est une combinaison de level 4 et level 8
Activer et d?activer le tra?ge de l'event 10046
Pour tirer le meilleur profit de l'event 10046, plusieurs param?res doivent convenir ?ce tra?ge:
Le param?re TIMED_STATISTICS doit ?re initialis??TRUE pour la session analys?, sinon les compteurs de temps ne seront pas renseign?. Ce param?re est renseign?soit dans init.ora (TIMED_STATISTICS=TRUE), modifi?pour la session en cours (alter session set TIMED_STATISTICS=TRUE), ou modifi??distance (exec dbms_system).
Le fichier trace doit pouvoir contenir une grande quantit?d'informations. La taille du fichier trace devrait ?re g?? avec le param?re MAX_DUMP_FILE_SIZE (modifiable soit en init.ora soit avec alter session).
La d?activation de l'event 100046 se fait de la m?e mani?e que son activation ou en fermant tout simplement la session. Il est souhaitable que la session soit ferm? pour clore le fichier de trace et vider tous les curseurs trac?. Arr?er une trace avec des curseurs ouverts (session en cours) peut l??ement fausser la trace.
Tracer la session courante
L'activation pour la session courante se fait avec :
alter session set timed_statistics=true;
alter session set max_dump_file_size=unlimited;
alter session set events '10046 trace name context forever, level 8';
alter session set events '10046 trace name context forever, level 4';
exec sys.dbms_system.set_ev(&sid, &serial,10046, 4, '');
SVRMGR> oradebug event 10046 trace name context forever, level 8
:oradebug event 10046 trace name context forever, level 4
:
SVRMGR> oradebug event 10046 trace name context off
Nom du fichier de trace
Get the name of the trace file that will be generated when you enable tracing:
select
c.value||'\ora_'||b.spid||'.trc'
from v$session a, v$process b, v$parameter c
where name = 'user_dump_dest'
and a.paddr = b.addr
and a.sid = 8
For detailed information about how to interpret raw trace data, visit Oracle Metalink bulletin 39817.1.
http://www.hotsos.com/dnloads/1/10046a/
event='10046 trace name context forever, level 4'
PL/SQL attribue curseur cursor
declare
nb_row number;
begin
insert into aca select * from aca;
nb_row := SQL%ROWCOUNT;
insert into aca_log values (sysdate,nb_row);
end;
.
/
Autres attribues : %ISOPEN %FOUND %NOTFOUND %BULK_ROWCOUNT
DELETE FROM emp WHERE empno = my_empno;
IF SQL%FOUND THEN -- delete succeeded
SQLPLUS
set linesize 200
set wrap off -- evite d'aller a la ligne
set trimspool on -- supprime les blancs en fin de ligne
LOG avec date iso pour sqlplus unix/shell
#!/usr/bin/ksh
DATE_LOG=`date +'%Y-%m-%d_%Hh%Mmn%Ss'`
echo $DATE_LOG
sqlplus dbacarmap/dbacarmap @toto.sql ${DATE_LOG}_toto.log
Pour pr?enir tout probl?e Oracle de type ORA-04030 relatif aux limitations dallocation m?oire sur les processus Oracle. Il faut supprimer tout seuil de limitation de consommation m?oire au niveau AIX pour lutilisateur Oracle et pour les utilisateurs applicatif unix ayant vocation ?ex?uter des processus se connectant aux bases de donn?s Oracle. Ces seuils dallocation m?oire sont d?inis au sein du fichier /etc/security/limits, la configuration au niveau de lutilisateur Oracle doit ?re
oracle:
stack = -1
data = -1
rss = -1
idem pour les autres utilisateurs applicatif .
Attention redemarrer la base et listener.
CREATION DATABASE LINK
create public database link dbaecc connect to dbaecc identified by dbaecc using 'TNSNAME_VERS_ECC';
create database link atis connect to current_user using 'atis';
install Ora10g on AIX 5.3
Note:282036.1
Subject: Minimum software versions and patches required to Support Oracle Products on IBM pSeries.
export / import compatibilit?
Note:132904.1
Subject: Compatibility Matrix for Export & Import Between Different Oracle Versions
GESTION SPFILE UNDO RBS
alter system set undo_management=manual scope=spfile ;
alter system set undo_management=auto scope=spfile ;
=================================== TRACE LEVEL ===========================
trace_level_client = 16
trace_file_client = cli
trace_directory_client=c:\oracle
/tempo_oracle_install/CD_patch_9206/Disk1/stage/products.xml
======== CREATION DU SCHEMA SCOTT/TIGER ======
$ORACLE_HOME/sqlplus/demo
email
CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_from varchar2 := 'EMAILADDRESS@DOMAIN.COM', ----- MAIL BOX SENDING THE EMAIL
msg_to varchar2 := 'EMAILADDRESS@DOMAIN.COM', ----- MAIL BOX RECIEVING THE EMAIL
msg_subject varchar2 := 'Output file TEST1', ----- EMAIL SUBJECT
msg_text varchar2 := 'THIS IS THE TEXT OF THE EMAIL MESSAGE.',
v_output1 varchar2 := 'THIS IS THE TEXT OF THE ATTACHMENT FILE. THIS TEXT SHOULD BE IN A TEXT FILE ATTACHED TO THE EMAIL.')
IS
c utl_tcp.connection;
rc integer;
crlf VARCHAR2(2):= CHR(13)||CHR(10);
mesg VARCHAR2( 32767 );
BEGIN
c := utl_tcp.open_connection('196.35.140.18', 25); ----- OPEN SMTP PORT CONNECTION
rc := utl_tcp.write_line(c, 'HELO 196.35.140.18'); ----- PERFORMS HANDSHAKING WITH SMTP SERVER
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'EHLO 196.35.140.18'); ----- PERFORMS HANDSHAKING WITH SMTP SERVER, INCLUDING EXTRA INFORMATION
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'MAIL FROM: '||msg_from); ----- MAIL BOX SENDING THE EMAIL
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'RCPT TO: '||msg_to); ----- MAIL BOX RECIEVING THE EMAIL
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'DATA'); ----- EMAIL MESSAGE BODY START
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'Date: '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ));
rc := utl_tcp.write_line(c, 'From: '||msg_from||' <'||msg_from||'>');
rc := utl_tcp.write_line(c, 'MIME-Version: 1.0');
rc := utl_tcp.write_line(c, 'To: '||msg_to||' <'||msg_to||'>');
rc := utl_tcp.write_line(c, 'Subject: '||msg_subject);
rc := utl_tcp.write_line(c, 'Content-Type: multipart/mixed;'); ----- INDICATES THAT THE BODY CONSISTS OF MORE THAN ONE PART
rc := utl_tcp.write_line(c, ' boundary="-----SECBOUND"'); ----- SEPERATOR USED TO SEPERATE THE BODY PARTS
rc := utl_tcp.write_line(c, ''); ----- INSERTS A BLANK LINE. PART OF THE MIME FORMAT AND NONE OF THEM SHOULD BE REMOVED.
rc := utl_tcp.write_line(c, '-------SECBOUND');
rc := utl_tcp.write_line(c, 'Content-Type: text/plain'); ----- 1ST BODY PART. EMAIL TEXT MESSAGE
rc := utl_tcp.write_line(c, 'Content-Transfer-Encoding: 7bit');
rc := utl_tcp.write_line(c, '');
rc := utl_tcp.write_line(c, msg_text); ----- TEXT OF EMAIL MESSAGE
rc := utl_tcp.write_line(c, '');
rc := utl_tcp.write_line(c, '-------SECBOUND');
rc := utl_tcp.write_line(c, 'Content-Type: text/plain;'); ----- 2ND BODY PART.
rc := utl_tcp.write_line(c, ' name="Test.txt"');
rc := utl_tcp.write_line(c, 'Content-Transfer_Encoding: 8bit');
rc := utl_tcp.write_line(c, 'Content-Disposition: attachment;'); ----- INDICATES THAT THIS IS AN ATTACHMENT
rc := utl_tcp.write_line(c, ' filename="Test.txt"'); ----- SUGGESTED FILE NAME FOR ATTACHMENT
rc := utl_tcp.write_line(c, '');
rc := utl_tcp.write_line(c, v_output1);
rc := utl_tcp.write_line(c, '-------SECBOUND--');
rc := utl_tcp.write_line(c, '');
rc := utl_tcp.write_line(c, '.'); ----- EMAIL MESSAGE BODY END
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'QUIT'); ----- ENDS EMAIL TRANSACTION
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
utl_tcp.close_connection(c); ----- CLOSE SMTP PORT CONNECTION
EXCEPTION
when others then
raise_application_error(-20000, SQLERRM);
END;
/
========= RULE vs COST optimiseur ==========
ATTENTION LA RU_PRD_011a est vide !!!
DONC ZERO LIGNE A UPDATER, mais la table UNITE_DE_GESTION
a 1,5 millions de lignes
update UNITE_DE_GESTION
set
CODE_ETAT_UG = 'E',
TYPE_TRAITEMENT_UG = 'S',
DATE_TRAITEMENT_UG = SYSDATE,
DATE_DB_EPURATION_UG = SYSDATE
where (CODE_SITE_REFERENCEUR,NUMERO_CHRONO_UG) in
(
select ugg.CODE_SITE_REFERENCEUR,ugg.NUMERO_CHRONO_UG
from RU_PRD_011a RU,
UNITE_DE_GESTION UGG
where
MVT_COD = 'S'
AND RU.NUMERO_CHRONO_UG = UGG.NUMERO_CHRONO_UG
AND UGG.CODE_SITE_REFERENCEUR = 'FR1'
);
plan suivant en choose => 0.39s
QUERY_PLAN
--------------------------------------------------------------------------------
364 card=1 UPDATE STATEMENT
card= UPDATE UNITE_DE_GESTION
364 card=1 NESTED LOOPS
362 card=1 VIEW
362 card=1 SORT UNIQUE
358 card=1 NESTED LOOPS
357 card=1 TABLE ACCESS FULL RU_PRD_011A
1 card=1476388 INDEX UNIQUE SCAN PK_UNITE_DE_GESTION
1 card=1476388 INDEX UNIQUE SCAN PK_UNITE_DE_GESTION
plan suivant en RULE => plus de 1h30
QUERY_PLAN
--------------------------------------------------------------------------------
card= UPDATE STATEMENT
card= UPDATE UNITE_DE_GESTION
card= NESTED LOOPS
card= VIEW
card= SORT UNIQUE
card= NESTED LOOPS
card= TABLE ACCESS BY INDEX ROWID UNITE_DE_GESTION --
card= INDEX RANGE SCAN UNITE_DE_GESTION_K1 | loop de 1,5 millions de fois
card= TABLE ACCESS BY INDEX ROWID RU_PRD_011A <-
card= INDEX RANGE SCAN PRD011A_CHRONO_UG_IDX
card= INDEX UNIQUE SCAN PK_UNITE_DE_GESTION
note : index UNITE_DE_GESTION_K1 sur les colonnes (CODE_SITE_REFERENCEUR,CODE_UG) => avec la condition CODE_SITE_REFERENCEUR = 'FR1' donne 1,5 millions de lignes.
autre demonstration :
select /*+ ORDERED */ ugg.CODE_SITE_REFERENCEUR,ugg.NUMERO_CHRONO_UG
from
UNITE_DE_GESTION UGG,
RU_PRD_011a RU
where
MVT_COD = 'S'
AND RU.NUMERO_CHRONO_UG = UGG.NUMERO_CHRONO_UG
AND UGG.CODE_SITE_REFERENCEUR = 'FR1';
QUERY_PLAN
--------------------------------------------------------------------------------
14697 card=1 SELECT STATEMENT
14697 card=1 NESTED LOOPS
14697 card=1476388 TABLE ACCESS FULL UNITE_DE_GESTION
card=1 TABLE ACCESS BY INDEX ROWID RU_PRD_011A
card=1 INDEX RANGE SCAN PRD011A_CHRONO_UG_IDX
select /*+ ORDERED */ ugg.CODE_SITE_REFERENCEUR,ugg.NUMERO_CHRONO_UG
from RU_PRD_011a RU,
UNITE_DE_GESTION UGG
where
MVT_COD = 'S'
AND RU.NUMERO_CHRONO_UG = UGG.NUMERO_CHRONO_UG
AND UGG.CODE_SITE_REFERENCEUR = 'FR1';
QUERY_PLAN
--------------------------------------------------------------------------------
358 card=1 SELECT STATEMENT
358 card=1 NESTED LOOPS
357 card=1 TABLE ACCESS FULL RU_PRD_011A
1 card=1476388 INDEX UNIQUE SCAN PK_UNITE_DE_GESTION
============================= TRUCS TORDUS A EVITER =======================
select sysdate, 0from dual;
select sysdate, 0 "toto"from dual;
Commentaires
Poster un commentaire
Rétroliens
URL pour faire un rétrolien vers ce message :
http://www.canalblog.com/cf/fe/tb/?bid=171298&pid=6166926
Liens vers des weblogs qui référencent ce message :