Mémo

De Le wiki de Fred sur gantzer.eu

Sommaire

Le controlfile

Affichage des paramètres

  select * from v$controlfile;
  select name,value from v$parameter where name='control_files';
  SHOW PARAMETER control_files;

Ajout et copie d'un controlfile

  ALTER SYSTEM SET control_files= '/DBTEST/log1/control01.ctl','/DBTEST/log2/control02.ctl','/DBTEST/log3/control03.ctl' SCOPE=SPFILE;
  shutdown immediate;
  cp /DBTEST/log2/control02.ctl /DBTEST/log3/control03.ctl
  startup

Les redologs

Affichage

  select member from  v$logfile;
  select GROUP#, SEQUENCE#, bytes, members, status from v$log;

Etat des redologs

  archive log list

Changement fichier de journalisation

  alter system switch logfile;

Application d'un point de reprise

  ALTER SYSTEM CHECKPOINT

Ajout / Suppression d'un groupe de redolog

  alter database add logfile group 3 '/DBTEST/log1/LDBTEST/onlinelog/log3a.log','/DBTEST/log2/LDBTEST/onlinelog/log3b.log')  size 100M
  alter database drop logfile group 3;

Ajout / suppression d'un membre à un groupe

  alter database add logfile member '/DBTEST/log2/LDBTEST/onlinelog/log3c.log' to group 3;
  alter database drop logfile member  '/DBTEST/log2/LDBTEST/onlinelog/log3c.log';

Les archivelogs

Visualisation de repertoire cible des archive

  show parameter LOG_ARCHIVE_DEST
  show parameter LOG_ARCHIVE_FORMAT

Démarrer la base en mode Archive

  SHUTDOWN ;
  STARTUP MOUNT EXCLUSIVE;
  ALTER DATABASE ARCHIVELOG;
  ALTER DATABASE OPEN; 

Démarrer la base en NoArchivelog Mode

  SHUTDOWN;
  STARTUP MOUNT EXCLUSIVE;
  ALTER DATABASE NOARCHIVELOG;
  ALTER DATABASE OPEN;

Paramètre de switch de log

Au bout d’un certain temps Oracle switchera de log même s’il n’y a pas d’activité

  show parameter ARCHIVE_LAG_TARGET

Forcer le switch du log

  alter system switch logfile ;

Les tablespaces

Espace dans les TBS (global)

  SELECT c.tablespace_name, a.bytes/1048576 Megs_Alloc, b.bytes/1048576 Megs_Free, (a.bytes-b.bytes)/1048576 Megs_Used, b.bytes/a.bytes * 100 Pct_Free, (a.bytes-b.bytes)/a.bytes * 100 Pct_Used, c.initial_extent/1048576 Init_Ext, c.next_extent/1048576 Next_Ext, a.minbytes/1048576 Min_Ext, a.maxbytes/1048576 Max_Ext 
  FROM (select tablespace_name, sum(a.bytes) bytes, min(a.bytes) minbytes, max(a.bytes) maxbytes FROM sys.dba_data_files a group by tablespace_name) a, (SELECT a.tablespace_name, nvl(sum(b.bytes),0) bytes FROM sys.dba_data_files a, sys.dba_free_space b 
  WHERE a.tablespace_name = b.tablespace_name (+) AND a.file_id = b.file_id (+) GROUP BY a.tablespace_name) b, sys.dba_tablespaces c WHERE a.tablespace_name = b.tablespace_name(+) and a.tablespace_name = c.tablespace_name ORDER BY c.tablespace_name;

Espace libre dans les TBS

  select TABLESPACE_NAME,sum(BYTES/1024/1024) from  dba_free_space group by TABLESPACE_NAME;

Information sur les tablespace

  select TABLESPACE_NAME,FILE_NAME from DBA_DATA_FILES;
  select TABLESPACE_NAME, BLOCK_SIZE, MIN_EXTENTS ,NEXT_EXTENT from user_tablespaces;
  desc dba_extents;
  desc dba_segments;

Création d'un tablespace

  CREATE TABLESPACE userdata DATAFILE '/DBTEST/data/userdata01.dbf' SIZE 20M; 

(auto-extend par défaut)

Pour les tables gérées localement (pas par le dictionnaire)

  CREATE TABLESPACE userdata2 DATAFILE '/DBTEST/data/userdata02.dbf' SIZE 10M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

Passage en AUTO extension d'un fichier de tablespace existant

  ALTER DATABASE DATAFILE 'E:\orant\database\TEST\Usr1TEST.ora' AUTOEXTEND ON;

Tablespace d'annulation:

  CREATE UNDO TABLESPACE undo1 DATAFILE '/DBTEST/data/undo01.dbf' SIZE 1M;

Tablespaces TEMPORARY

  CREATE TEMPORARY TABLESPACE temp01 TEMPFILE '/DBTEST/data//temp01.dbf' SIZE 1M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

Pour voir le temporary par defaut:

  SELECT * from DATABASE_PROPERTIES;

Pour voir tous les temporary:

  select * from v$tempfile;

Passer en read-only

  alter tablespace USERDATA2 read only;
  alter tablespace USERDATA2 read write;

Passer offline/online

  ALTER TABLESPACE USERDATA2 OFFLINE;
  ALTER TABLESPACE USERDATA2 ONLINE;

Autoextend sur datafile

  select TABLESPACE_NAME,AUTOEXTENSIBLE from dba_data_files;
  alter database datafile '/u01/app/oracle/ts_32k.dbf'autoextend on;

Gestion UNDO et TEMP

UNDO

  CREATE UNDO   TABLESPACE "UND_NEW"  datafile SIZE 1000M AUTOEXTEND   ON NEXT  1024K MAXSIZE  32767M;
  alter system set undo_tablespace='UND_NEW';
  drop tablespace UND_BT including contents and datafiles;
  CREATE UNDO   TABLESPACE "UND_BT"  datafile SIZE   1700M AUTOEXTEND   ON NEXT  1024K MAXSIZE  32767M;
  alter system set undo_tablespace='UND_BT';
  drop tablespace UND_NEW including contents and datafiles;

TEMP

  CREATE  TEMPORARY TABLESPACE "TEMP2" TEMPFILE SIZE 405M  AUTOEXTEND ON NEXT  100M MAXSIZE  32767M;
  ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP2";
  drop tablespace temp including contents and datafiles;
  CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE  SIZE 405M AUTOEXTEND ON NEXT 100M MAXSIZE  32767M EXTENT MANAGEMENT LOCAL UNIFORM  SIZE 2048K;
  ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP"
  drop tablespace temp2 including contents and datafiles;

Les contraintes

Afficher les contraintes existantes

   select TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION, STATUS from ALL_CONSTRAINTS

Utilisateurs et des roles

user en cours

  show user;

Créer un user identifié par l'OS

  create user ops$patrol identified externally default tablespace PATROLTBS temporary tablespace PATROLTEMP;
  grant connect,resource to ops$patrol;
  grant dba to ops$patrol;

Changer mdp

en gardant le cryptogramme

  ALTER USER scott IDENTIFIED BY VALUES 'F894844C34402B67';

avec un mdp en clair

  ALTER USER scott IDENTIFIED BY toto;

Unlock d'un user

  alter user scott account unlock;

Autorisation connexion user

  connect SYSTEM/SYS
  grant connect to scott;

Forcer changement mdp d'un user

alter user scott password expire;

information sur les users

  select USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE from dba_users;

information sur les profils

  select * from dba_profiles order by profile,resource_name, resource_type;

Statistiques & Tuning

  exec dbms_stats.CREATE_STAT_TABLE  ('STAGIAIRE', 'STATS');
  exec dbms_stats.GATHER_SCHEMA_STATS('STAGIAIRE');

Vidage du sharepool

  alter system flush shared_pool

Parametres systeme

  show parameter db_
  
  SQL> show parameter sga_max_size
  NAME                                 TYPE        VALUE
  ------------------------------------ ----------- --------
  sga_max_size                         big integer 92M
  alter system set sga_max_size=256 scope=spfile;
Outils personnels
Navigation