Les questions simples.

	Info TAFORA
	Méthodes Conseil
	Mon credo Oracle
	Nouveautés
	Expertise technique
	Fiches Techniques
	Séminaires

Technique
	FAQ
Domaines
	DBA
	Tuning
	PL/SQL
Divers
	Varia
	Liens
Contact
	TAFORA
	Radu
	Mail

ORACLE KB

En règle générale, les requêtes suivantes sont à lancer par sys ou bien créer des vues semblables et les granter à un utilisateur non dba (si la sécurité vous intéresse ;-))

Les connexions
En local

Définir l’instance :

Windows : set ORACLE_SID=Nom de l’instance
UNIX : export ORACLE_SID=Nom de l’instance

Se connecter

sqlplus "/ as sysdba" ou bien
sqlplus "username/password as sysdba"

En remote

sqlplus username/password@nomreseau

Nomreseau est défini dans le fichier tnsnames.ora ($ORACLE_HOME/network/admin/)

La base
Démarrer une instance

Lecture de init.ora, spfile.ora

startup nomount pfile=. . .

Monter la base

Les fichiers de contrôle (répertoriés dans init.ora, spfile.ora) sont lus (le premier intégralement, les entêtes des autres)

alter database mount

Ouvrir la base

Les fichiers de données (répertoriés dans le fichier de contrôle) sont lus.

A quelle instance je suis connecté ?

select instance_name from v$instance;

Sur quelle base pointe mon instance ?

select name from v$database;

Est ma base en mode archivelog ?

select log_mode from v$database;

Cette requête est dépendante de la version !

De quelles options disposons-nous ?

select * from v$option;

Quelle est ma version Oracle ?

select * from v$version;

Quelle est la valeur du paramètre . . . ?

select name, value from v$parameter where name like '%ce_que_je_veux%';

Rechercher des textes en minuscules !

L'architecture de la base
Liste de tablespaces

Select tablespace_name from dba_tablespaces;

Lien tablespace/fichier OS

select tablespace_name, file_name from dba_data_files;
select tablespace_name, file_name from dba_temp_files;

Liste de fichiers (données)

select name from v$datafile;
select name from v$tempfile;

Les deux dernières vues temp. . . sont disponibles en 8i+

Liste de fichiers (et membres) redo

Select member from v$logfile
Select * from v$log;

Liste de fichiers de contrôle

Select name from v$controlfile;
Select name, value from v$parameter where name like '%control%';

Varia
Quels sont les process qui entourent la SGA ?

TAFORA SQL>  select p.pid, bg.name, bg.description, p.program
  2          from  v$bgprocess bg, v$process p
  3          where bg.paddr = p.addr
  4          order by p.pid;
       PID NAME  DESCRIPTION               PROGRAM
---------- ----- ------------------------- ----------
         2 PMON  process cleanup           ORACLE.EXE
         3 DBW0  db writer process 0       ORACLE.EXE
         4 LGWR  Redo etc.                 ORACLE.EXE
         5 CKPT  checkpoint                ORACLE.EXE
         6 SMON  System Monitor Process    ORACLE.EXE
         7 RECO  distributed recovery      ORACLE.EXE
         8 CJQ0  Job Queue Coordinator     ORACLE.EXE
         9 QMN0  AQ Time Manager Process 0 ORACLE.EXE

Objets invalides

/* Liste des objets invalides dans la base 
   Ils seront la cible de vos reflection / compilation ;-) 
*/
col object_name FOR a20
col owner FOR a15
SELECT object_name, object_type, owner 
  FROM dba_objects 
  WHERE status='INVALID';

Colonne autoincrémentale

Rendre une colonne autoincrementale revient à créer une séquence, un trigger et à ne pas renseigner la colonne lors de la saisie.

Création d'une séquence

drop sequence   seq_bib_urls_base;
CREATE SEQUENCE "SEQ_PK_URL_BIB" 
	MINVALUE 1 MAXVALUE 999999999999999999999999999 
	INCREMENT BY 1 START WITH 5 CACHE 20 NOORDER NOCYCLE;

Création d'un trigger

CREATE OR REPLACE TRIGGER t_bib_urls_pk 
 before INSERT or UPDATE ON bib_urls 
 for each row 
 declare 
 begin 
   if :new.url_id is null then 
     SELECT seq_pk_url_bib.NEXTVAL into :new.url_id FROM dual; 
     bau.loglog('TRIGGER t_bib_urls_pk ' || :new.url_id);
   end if; 
 end; 
/

Gestion des fichiers redo log

L'emplacement des fichiers redo est dicté par les fichiers de contrôle.

Les fichiers redo sont créés lors de la commande CREATE DATABASE.

Des nouveaux fichiers redo log peuvent être ajoutés à la base.

Création des fichiers

CREATE DATABASE prod
  DATAFILE '/u02/dbs/system_1_PROD.dbf' SIZE 30M
  AUTOEXTEND ON NEXT 10M MAXSIZE 150M
    LOGFILE  
      GROUP 1 ('/u10/dbs/redo1_PROD.dbf',
               '/u11/dbs/redo1_PROD.dbf',) SIZE 5M, 
      GROUP 2 ('/u10/dbs/redo2_PROD.dbf',
               '/u11/dbs/redo2_PROD.dbf',) SIZE 5M,
      GROUP 3 ('/u10/dbs/redo3_PROD.dbf',
               '/u11/dbs/redo3_PROD.dbf',) SIZE 5M
  MAXDATAFILES	1000
  MAXLOGFILES 	15
  MAXLOGMEMBERS 	5
  CHARACTER SET	WE8ISO8859P1;

Création d'un nouveau groupe

alter database add logfile group 4 
  ('/db01/ORACLE/prod/log_41.dbf',  
   '/db02/ORACLE/prod/log_42.dbf') 
size 5M;

Ajout d'un membre à un groupe

alter database 
   add logfile member '/db03/ORACLE/prod/log_42.dbf' to group 4;

Modification/Suppression

La seule modification que les fichiers redo supportent est la destruction.

alter database drop logfile member '/db03/ORACLE/prod/log_42.dbf';
alter database drop logfile group 4;

Gestion des fichiers
Créer un tablespace permanent (8i+)

Create tablespace TBS_KBX_1
  datafile '/home/san1/lib9/ftbs_kbx_1.dat' SIZE 6000 M REUSE;

Rendre un tablespace autoextensible

(en rendant son/ses fichiers autoextensibles)

alter database datafile '/home/san1/lib9/ftbs_kbx_1.dat' autoextend on next 50M;

Créer un tablespace temporaire (8i+)

Create temporary tablespace TEMP
  tempfile '/home/san1/lib9/temp/temp01.dbf' SIZE 544 M REUSE
  EXTENT management local uniform SIZE 1M;

Gestion des utilisateurs
Créer un utilisateur

-- preparation de son environnement (incomplet) !
alter database datafile 'p:\dbneuf\ulib\CCindex.DBF'
  autoextend on next 200M; 
--
drop user ccsys cascade;
create user ccsys identified by SON_PASS 
  default tablespace ccenter
  temporary tablespace cctemp;
grant connect, resource to ccsys;
-- privileges additionneles
grant create public database link to ccsys;
grant drop public database link to ccsys;
grant select_catalog_role         to ccsys;
grant dba to ccsys;
grant select on v_$timer to ccsys;

Gestion de la SGA
Invalidations dans le shared pool

select sql_text, optimizer_mode, invalidations from v$sql 
where sql_text like '%select . . . % '
          and sql_text not like '%v$sql%';

Backup
Comment je fais un backup à chaud ?

Vérifiez que la base est en mode archivelog

select log_mode from v$database;

Vérifiez que le process d’archivage tourne

select name, description from v$bgprocess 
where paddr <> '00' and name like '%RC%';

Démarrer le process d’archivage s’il est inactif

LOG_ARCHIVE_START=TRUE         # dans init.ora

Ajouter des process d’archivage (si plusieurs destinations des archives, par exemple)

LOG_ARCHIVE_MAX_PROCESSES =2    # dans init.ora 
alter system set LOG_ARCHIVE_MAX_PROCESSES=4;

Archiver manuellement pour un tour de redo logs

alter system archive log all ;

Nom des fichiers archives

LOG_ARCHIVE_FORMAT=NOM_INSTANCE.%S.%T.arc # init.ora

%S détermine l’écriture du log archivé sous la forme NOM_INSTANCE.000028.00001.arc si le numéro de séquence est 28 et le numéro d’instance est 1. (Sans parallel server, le numéro d’instance est forcement 1).

Quelques paramètres

SQL> col name for a40
SQL> col value for a39
SQL> select name, value from v$parameter where name like 'log%' and name is not
null;
NAME                                     VALUE
---------------------------------------- ---------------------------------------
log_file_name_convert
log_archive_start                        TRUE
log_archive_dest                         g:\gateARCHIVE
log_archive_duplex_dest
log_archive_dest_1
log_archive_dest_2
log_archive_dest_3
log_archive_dest_4
log_archive_dest_5
log_archive_dest_6
log_archive_dest_7
log_archive_dest_8
log_archive_dest_9
log_archive_dest_10
log_archive_dest_state_1                 enable
log_archive_dest_state_2                 enable
log_archive_dest_state_3                 enable
log_archive_dest_state_4                 enable
log_archive_dest_state_5                 enable
log_archive_dest_state_6                 enable
log_archive_dest_state_7                 enable
log_archive_dest_state_8                 enable
log_archive_dest_state_9                 enable
log_archive_dest_state_10                enable
log_archive_max_processes                2
log_archive_min_succeed_dest             1
log_archive_trace                        0
log_archive_format                       GATE%S%T.ARC
log_buffer                               524288
log_checkpoint_interval                  0
log_checkpoint_timeout                   1800
log_parallelism                          1
log_checkpoints_to_alert                 FALSE
logmnr_max_persistent_sessions           1

Le backup

Pour chaque tablespace de la base, (Sauf les tablespaces temporaires, read only), (Trouvé avec select tablespace_name from dba_tablespaces)

alter tablespace tbsx begin backup ;

pour chaque fichier du tablespace concerné :

(trouvé avec select file_name from dba_data_files where tablespace_name=tbsx)

         host ocopy fichier.generique fichier.generique.bck

Fin pour chaque fichier

alter tablespace tbsx end backup ;

Fin pour chaque tablespace

Objets
Quels index a ma table ?

select index_name from dba_indexes where upper(table_name) = upper(&matable);

Dans quel tbs se trouve matable ?

Select tablespace_name from dba_tables where upper(owner)=upper(&proprietaire) and upper(table_name) = upper(&matable);

Dans quel tbs se trouve monidex ?

Select tablespace_name from dba_indexes where upper(owner)=upper(&proprietaire) and upper(index_name) = upper(&monindex);

Quel est la définition de la vue MAVUE ?

SELECT TEXT 
  FROM DBA_VIEWS 
  WHERE VIEW_NAME 	= 'MAVUE'
  -- and OWNER 		= 'UTILISATEUR – Si l’on veut
;

Reverse engineering
Les tablespaces

/*
|| Reverse engineering des tablespaces, a partir de 9i
|| Radu Caulea, TAFORA, 2004
*/
SET serveroutput ON;
CREATE TABLE TAFORA_ordres (id NUMBER, ordre VARCHAR2(1000));
TRUNCATE TABLE TAFORA_ordres;
DECLARE 
ordre VARCHAR2(2000);
i NUMBER := 0;
BEGIN
DBMS_OUTPUT.enable(1000000);
FOR x IN (SELECT tablespace_name FROM dba_tablespaces) LOOP
   SELECT dbms_metadata.get_ddl('TABLESPACE',x.tablespace_name) INTO ordre
   FROM dual;
   i:=i+1;
   INSERT INTO TAFORA_ordres VALUES (i, ordre);
END LOOP;
COMMIT;
END;
/
SET heading OFF feedback OFF;
spool TAFORA_ordres.lis
SELECT ordre || ';' FROM TAFORA_ordres ORDER BY id;
DROP TABLE TAFORA_ordres;
spool OFF

radupointcauleaattaforapointfr, www.tafora.fr

Version 0.320 du 6 November 2006

Stats


Copyright © 1998-2002 Radu Caulea, TAFORA	MAJ 06/11/2006
Commentaires et suggestions radu[CHEZ]tafora.fr