Oracle : commandes basiques

De Wiki de Romain RUDIGER
Aller à : navigation, rechercher

AWR Report

Generate a full report

col BEGIN_INTERVAL_TIME format a30
col END_INTERVAL_TIME format a30
set long 90000 pages 1000 echo off line 200

select INSTANCE_NUMBER, INSTANCE_NAME, STARTUP_TIME from v$instance;

INSTANCE_NUMBER INSTANCE_NAME    STARTUP_TIME
--------------- ---------------- ---------------
              1 XXXX1            25-JUN-12

SQL> select DBID,SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME
from dba_hist_snapshot
where BEGIN_INTERVAL_TIME > '26-JUN-12 12.00.00.000 AM'  order by BEGIN_INTERVAL_TIME;

      DBID    SNAP_ID BEGIN_INTERVAL_TIME            END_INTERVAL_TIME
---------- ---------- ------------------------------ ------------------------------
2140482029         12 26-JUN-12 12.00.15.823 AM      26-JUN-12 01.00.22.491 AM
2140482029         13 26-JUN-12 01.00.22.491 AM      26-JUN-12 02.00.30.711 AM
2140482029         14 26-JUN-12 02.00.30.711 AM      26-JUN-12 03.00.42.839 AM
2140482029         15 26-JUN-12 03.00.42.839 AM      26-JUN-12 04.00.46.059 AM
2140482029         16 26-JUN-12 04.00.46.059 AM      26-JUN-12 05.00.51.104 AM
2140482029         17 26-JUN-12 05.00.51.104 AM      26-JUN-12 06.00.57.029 AM
2140482029         18 26-JUN-12 06.00.57.029 AM      26-JUN-12 07.00.02.079 AM
2140482029         19 26-JUN-12 07.00.02.079 AM      26-JUN-12 08.00.06.399 AM
2140482029         20 26-JUN-12 08.00.06.399 AM      26-JUN-12 09.00.10.241 AM

spool AWR_report_120626_morning.html
select output from table(dbms_workload_repository.awr_report_html (2140482029,1,12,20));
spool off
exit

Vérification du résultat :

$ head -5 AWR_report_120626_morning.html
SQL> select output from table(dbms_workload_repository.awr_report_html (2140482029,1,12,20));

OUTPUT                                                                                                                                                    
----------------------------------------------------------------------------------------
<html lang="en"><head><title>AWR Report for DB: XXXX, Inst: XXXX1, Snaps: 12-20</title>

Display execution plans from AWR

For a particular SQL ID :

select snap_id,sql_id,plan_hash_value from dba_hist_sqlstat where sql_id='a28rzhn05s24d' order by snap_id;

   SNAP_ID SQL_ID        PLAN_HASH_VALUE
---------- ------------- ---------------
     69498 a28rzhn05s24d      1136775913
     69583 a28rzhn05s24d       363623569
     69631 a28rzhn05s24d      3723328079
     69924 a28rzhn05s24d         5369503
     70111 a28rzhn05s24d      2804678812
     70304 a28rzhn05s24d       760455447
     70450 a28rzhn05s24d      1694068439
     70498 a28rzhn05s24d      2543727200
     70643 a28rzhn05s24d      1312663398
     70783 a28rzhn05s24d      1133196153
     70833 a28rzhn05s24d      3765087364
     70881 a28rzhn05s24d      1645357115
     70882 a28rzhn05s24d      1645357115
     70883 a28rzhn05s24d      1645357115
     70884 a28rzhn05s24d      1645357115
     70885 a28rzhn05s24d      1645357115
     70886 a28rzhn05s24d      1645357115
     70887 a28rzhn05s24d      1645357115
     70934 a28rzhn05s24d      3028609517
     70935 a28rzhn05s24d      3028609517
     70936 a28rzhn05s24d      3028609517
     70937 a28rzhn05s24d      3028609517
     70938 a28rzhn05s24d      3028609517
     70939 a28rzhn05s24d      3028609517
     70940 a28rzhn05s24d      3028609517
     70941 a28rzhn05s24d      3028609517
     70942 a28rzhn05s24d      3028609517
     70943 a28rzhn05s24d      3028609517
     70944 a28rzhn05s24d      3028609517
     70945 a28rzhn05s24d      3028609517
     70946 a28rzhn05s24d      3028609517
     70947 a28rzhn05s24d      3028609517
     70948 a28rzhn05s24d      3028609517
     70949 a28rzhn05s24d      3028609517

select * from table(dbms_xplan.display_awr('a28rzhn05s24d','3765087364'));
PLAN_TABLE_OUTPUT                                                                                                                                                                                       
------------------------------------------------------------------------------------------------------------------------------
SQL_ID a28rzhn05s24d
--------------------                                                                                                                                                                                    
Select * From XXX                                                                                                                                                                    

Plan hash value: 3765087364                                                                                                                                                                           

-------------------------------------------------------------------------------------------------------------------------------       
| Id  | Operation   | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |       
-------------------------------------------------------------------------------------------------------------------------------       
|   0 | SELECT STATEMENT   |     |       |       |       |   324K(100)|          |       |       |        |      |            |

version oracle

select * from v$version;
strings $(which oracle)|grep -i version

liste des instances

select INSTANCE_NUMBER, INSTANCE_NAME, STARTUP_TIME from v$instance;

INSTANCE_NUMBER INSTANCE_NAME    STARTUP_TIME
--------------- ---------------- ---------------
              1 XXXX1            25-JUN-12

information sur la base

select * from V$DATABASE ;

Tablespaces

Informations

select * from  DBA_TABLESPACES ;

Modifications

ALTER TABLESPACE  ts_test READ ONLY ;
ALTER TABLESPACE  ts_test READ WRITE ;

Syntaxe du DROP :

DROP TABLESPACE tablespace    [ INCLUDING CONTENTS [ {AND | KEEP} DATAFILES ]     [ CASCADE CONSTRAINTS ]   ] ;

Pour faire un drop si le TS est vide :

DROP TABLESPACE ts_test;

Pour faire un drop même si le TS contient des données :

DROP TABLESPACE ts_test INCLUDING CONTENTS ;

Pour supprimer également les fichiers :

DROP TABLESPACE ts_test INCLUDING CONTENTS AND DATAFILES ;

Création :

CREATE TABLESPACE TS_TEST
DATAFILE '+DATA' SIZE 300M
AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED

Pour gérer la taille des extends (peut être bien si tous les objets sont de la même taille) :

EXTENT MANAGEMENT LOCAL [ AUTOALLOCATE | UNIFORM [ SIZE size_clause ] ]
  • AUTOALLOCATE specifies that the tablespace is system managed. Users cannot specify an extent size. You cannot specify AUTOALLOCATE for a temporary tablespace.
  • UNIFORM specifies that the tablespace is managed with uniform extents of SIZE bytes.The default SIZE is 1 megabyte. All extents of temporary tablespaces are of uniform size, so this keyword is optional for a temporary tablespace. However, you must specify UNIFORM in order to specify SIZE. You cannot specify UNIFORM for an undo tablespace.

If you do not specify AUTOALLOCATE or UNIFORM, then the default is UNIFORM for temporary tablespaces and AUTOALLOCATE for all other types of tablespaces.

Pour définir la politique d'allocation des segments (PCTFREE / PCTUSED) :

SEGMENT SPACE MANAGEMENT { AUTO | MANUAL }
  • AUTO Specify AUTO if you want the database to manage the free space of segments in the tablespace using a bitmap. If you specify AUTO, then the database ignores any specification for PCTUSED, FREELIST, and FREELIST GROUPS in subsequent storage specifications for objects in this tablespace. This setting is called automatic segment-space management and is the default.
  • MANUAL Specify MANUAL if you want the database to manage the free space of segments in the tablespace using free lists. Oracle strongly recommends that you do not use this setting and that you create tablespaces with automatic segment-space management.

Data Files

Informations

select * from  DBA_DATA_FILES ;

Ajout d'un datafile

select * from dba_temp_files;
FILE_NAME                 FILE_ID TABLESPACE_NAME        BYTES     BLOCKS STATUS    RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
---------------------- ---------- ----------------- ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- -----------
/XX/dbf/temp01.dbf         1 TEMP              1.0486E+10    1280000 AVAILABLE            1 YES 1.0486E+10    1280000        64000 1.0485E+10     1279872
/XX/dbf/temp02.dbf         2 TEMP              1.0486E+10    1280000 AVAILABLE            2 YES 1.0486E+10    1280000        64000 1.0485E+10     1279872

ALTER TABLESPACE "TEMP" ADD TEMPFILE '/XX/dbf/temp03.dbf' SIZE 10000M REUSE AUTOEXTEND ON NEXT 500M MAXSIZE 10000M;

select * from dba_temp_files;
FILE_NAME                 FILE_ID TABLESPACE_NAME        BYTES     BLOCKS STATUS    RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCK
---------------------- ---------- ----------------- ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- -----------
/XX/dbf/temp01.dbf         1 TEMP              1.0486E+10    1280000 AVAILABLE            1 YES 1.0486E+10    1280000        64000 1.0485E+10     1279872
/XX/dbf/temp02.dbf         2 TEMP              1.0486E+10    1280000 AVAILABLE            2 YES 1.0486E+10    1280000        64000 1.0485E+10     1279872
/XX/dbf/temp03.dbf         3 TEMP              1.0486E+10    1280000 AVAILABLE            3 YES 1.0486E+10    1280000        64000 1.0485E+10     1279872

Mise en autoextend des datafiles d'un tablespace

Avant :

select * from dba_data_files where TABLESPACE_NAME = 'DBX';

FILE_NAME           FILE_ID TABLESPACE_NAME        BYTES     BLOCKS STATUS    RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------------- ------- ----------------- ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- -----------
/DBX/dbf/dbx5.dbf        10 DBX                734003200      89600 AVAILABLE           10 NO           0          0            0  733995008       89599
/DBX/dbf/dbx4.dbf         9 DBX                629145600      76800 AVAILABLE            9 NO           0          0            0  629137408       76799
/DBX/dbf/dbx3.dbf         8 DBX                734003200      89600 AVAILABLE            8 NO           0          0            0  733995008       89599
/DBX/dbf/dbx2.dbf         7 DBX                734003200      89600 AVAILABLE            7 NO           0          0            0  733995008       89599
/DBX/dbf/dbx.dbf          6 DBX                734003200      89600 AVAILABLE            6 NO           0          0            0  733995008       89599
/DBX/dbf/dbx6.dbf        11 DBX                734003200      89600 AVAILABLE           11 NO           0          0            0  733995008       89599

Modification :

alter database datafile '/DBX/dbf/dbx.dbf' autoextend on next 100M maxsize 2000M;
alter database datafile '/DBX/dbf/dbx2.dbf' autoextend on next 100M maxsize 2000M;
alter database datafile '/DBX/dbf/dbx3.dbf' autoextend on next 100M maxsize 2000M;
alter database datafile '/DBX/dbf/dbx4.dbf' autoextend on next 100M maxsize 2000M;
alter database datafile '/DBX/dbf/dbx5.dbf' autoextend on next 100M maxsize 2000M;
alter database datafile '/DBX/dbf/dbx6.dbf' autoextend on next 100M maxsize 2000M;

Résultat :

SQL> select * from dba_data_files where TABLESPACE_NAME = 'DBX';

FILE_NAME           FILE_ID TABLESPACE_NAME        BYTES     BLOCKS STATUS    RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------------- ------- ----------------- ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- -----------
/DBX/dbf/dbx5.dbf        10 DBX                734003200      89600 AVAILABLE           10 YES 2097152000     256000        12800  733995008       89599
/DBX/dbf/dbx4.dbf         9 DBX                629145600      76800 AVAILABLE            9 YES 2097152000     256000        12800  629137408       76799
/DBX/dbf/dbx3.dbf         8 DBX                734003200      89600 AVAILABLE            8 YES 2097152000     256000        12800  733995008       89599
/DBX/dbf/dbx2.dbf         7 DBX                734003200      89600 AVAILABLE            7 YES 2097152000     256000        12800  733995008       89599
/DBX/dbf/dbx.dbf          6 DBX                734003200      89600 AVAILABLE            6 YES 2097152000     256000        12800  733995008       8959
/DBX/dbf/dbx6.dbf        11 DBX                734003200      89600 AVAILABLE           11 YES 2097152000     256000        12800  733995008       89599

Log Files

Display redo log groups and files:

select GROUP#, THREAD#, SEQUENCE#, BYTES, ARCHIVED, STATUS, FIRST_CHANGE# from V$LOG;

    GROUP#    THREAD#  SEQUENCE#      BYTES ARC STATUS		 FIRST_CHANGE#
---------- ---------- ---------- ---------- --- ---------------- -------------
	 1	    1	      55   52428800 NO	CURRENT 	       1918986
	 2	    1	      53   52428800 YES INACTIVE	       1918280
	 3	    1	      54   52428800 YES INACTIVE	       1918546

col MEMBER format a50
select * FROM V$LOGFILE;

    GROUP# STATUS  TYPE    MEMBER					       IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------- -----------------------
	 3	   ONLINE  /BDD/oradata/db/redo03.log			      NO
	 2	   ONLINE  /BDD/oradata/db/redo02.log			      NO
	 1	   ONLINE  /BDD/oradata/db/redo01.log			      NO

Add a redo log file:

ALTER DATABASE ADD LOGFILE ('/BDD/oradata/db/redo04.log') SIZE 50M;
ALTER DATABASE ADD LOGFILE GROUP 4 ('/BDD/oradata/db/redo04.log') SIZE 50M;
ALTER DATABASE ADD LOGFILE MEMBER '/BDD/oradata/db/redo02.log' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER '/BDD/oradata/db/redo02c.log' TO ('/BDD/oradata/db/redo02a.log', '/BDD/oradata/db/redo02b.log');

Rename:

ALTER DATABASE RENAME FILE '/BDD/fra/DB_SEC/onlinelog/o1_mf_1_8l4crdx9_.log' to '/BDD/oradata/db/redo01.log'

Segments

Informations

Affichage de la taille par type :

select SEGMENT_TYPE, count(*) NB, sum(BYTES) / 1024 taille_ko
from DBA_SEGMENTS 
group by SEGMENT_TYPE
order by taille_ko desc ;

SEGMENT_TYPE               NB  TAILLE_KO
------------------ ---------- ----------
TABLE                    1619     633792
INDEX                    2377     300800
LOBSEGMENT                724     203008
CLUSTER                    10      48704
LOBINDEX                  724      46400
TABLE PARTITION           144      28224
INDEX PARTITION           300      25792
TYPE2 UNDO                 10      17856
NESTED TABLE               33       2432
ROLLBACK                    1        384
LOB PARTITION               1         64

ASM

Show ASM disks (V$ASM_DISK):

col PATH format a30
SET long 90000 pages 1000 echo off line 200
SELECT GROUP_NUMBER, NAME, PATH, HEADER_STATUS, PATH FROM V$ASM_DISK;

GROUP_NUMBER NAME                           PATH                           HEADER_STATU PATH
------------ ------------------------------ ------------------------------ ------------ ------------------------------
           0                                /dev/rhdiskpower1              FORMER       /dev/rhdiskpower1
           0                                /dev/rhdiskpower3              FORMER       /dev/rhdiskpower3
           0                                /dev/rhdiskpower4              FORMER       /dev/rhdiskpower4
           0                                /dev/rhdiskpower5              CANDIDATE    /dev/rhdiskpower5
           2 ORADATA_0008                   /dev/rac_dg2_06                MEMBER       /dev/rac_dg2_06
           2 ORADATA_0007                   /dev/rac_dg2_05                MEMBER       /dev/rac_dg2_05
           2 ORADATA_0006                   /dev/rac_dg2_04                MEMBER       /dev/rac_dg2_04
           2 ORADATA_0005                   /dev/rac_dg2_03                MEMBER       /dev/rac_dg2_03
           2 ORADATA_0004                   /dev/rac_dg2_02                MEMBER       /dev/rac_dg2_02
           2 ORADATA_0003                   /dev/rac_dg2_01                MEMBER       /dev/rac_dg2_01
           1 FRA_0001                       /dev/rac_dg1_02                MEMBER       /dev/rac_dg1_02
           1 FRA_0000                       /dev/rac_dg1_01                MEMBER       /dev/rac_dg1_01

Paramètres

Affichage

select * from V$PARAMETER
order by NAME ;

select NAME, VALUE, ISDEFAULT, ISSYS_MODIFIABLE
from V$PARAMETER
order by NAME ;

Modification :

ALTER SYSTEM SET db_file_multiblock_read_count = 16 SCOPE=BOTH

Scope : both/memory/spfile

Créer pfile depuis un spfile:

SQL> show parameter spfile

NAME	TYPE	 VALUE
------- -------- ------------------------------
spfile	string	 /BDD/oracle/product/11.2.0/dbs/spfiledb.ora

SQL> create pfile='/BDD/oracle/product/11.2.0/dbs/initdb.ora' from spfile='/BDD/oracle/product/11.2.0/dbs/spfiledb.ora';

File created.

Démarrer depuis le pfile et (re)créer le spfile:

SQL> startup pfile='/BDD/oracle/product/11.2.0/dbs/initdb.ora';
SQL> create spfile='/BDD/oracle/product/11.2.0/dbs/spfiledb.ora' from pfile='/BDD/oracle/product/11.2.0/dbs/initdb.ora';
SQL> shutdown immediate
SQL> startup

Utilisateurs

Syntaxe :

CREATE USER user
   IDENTIFIED { BY password
              | EXTERNALLY [ AS 'certificate_DN'  |  AS 'kerberos_principal_name' ]
              | GLOBALLY [ AS '[ directory_DN ]' ]
              }
   [ DEFAULT TABLESPACE tablespace
   | TEMPORARY TABLESPACE
        { tablespace | tablespace_group_name }
   | { QUOTA { size_clause | UNLIMITED } ON tablespace }...
   | PROFILE profile
   | PASSWORD EXPIRE
   | ACCOUNT { LOCK | UNLOCK }
     [ DEFAULT TABLESPACE tablespace
     | TEMPORARY TABLESPACE
          { tablespace | tablespace_group_name }
     | { QUOTA { size_clause | UNLIMITED } ON tablespace }...
     | PROFILE profile
     | PASSWORD EXPIRE
     | ACCOUNT { LOCK | UNLOCK }
     | ENABLE EDITIONS
     ]...
  ] ;

DROP USER user [ CASCADE ] ;
  • DEFAULT TABLESPACE : Specify the default tablespace for objects that are created in the user's schema. If you omit this clause, then the user's objects are stored in the database default tablespace. If no default tablespace has been specified for the database, then the user's objects are stored in the SYSTEM tablespace.
  • CASCADE : Specify CASCADE to drop all objects in the user's schema before dropping the user.

Exemples :

CREATE USER test IDENTIFIED BY passwd;

ALTER USER test IDENTIFIED BY new_passwd;

SELECT * FROM all_users;

SELECT USERNAME,DEFAULT_TABLESPACE from dba_users order by USERNAME;

RMAN

Start rman for the current BDD and connect to the catalog:

[oracle@lab-oem ~]$ rman catalog 'rman/rman@rmandb' target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Feb 8 12:32:34 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: RMANDB (DBID=3595053987)
connected to recovery catalog database

RMAN>

Show rman parameters:

RMAN> show all;

RMAN configuration parameters for database with db_unique_name RMANDB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/OEMDB/oracle/product/11.2.0/dbs/snapcf_rmandb.f'; # default

Register a database:

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

Import/Export

Export FULL :

exp userid=system/system file=DATE-BDD.dmp log=DATE-BDD.log full=y consistent=y

Export d'un schéma :

exp userid=system/system file=DATE-BDD-SCH.dmp log=DATE-BDD-SCH.log owner=user

Certaines lignes d'une table :

exp userid=SYSTEM/SYSTEM file=user-table.dmp log=user-table.log tables=user.table query="'where xxx_id = 103 and xxx_id < 12234374'" statistics=none

Import :

imp userid=SYSTEM/SYSTEM file=user-table.dmp log=user-table-imp.log fromuser=user touser=user ignore=yes

Datapump d'un schéma complet (export) :

expdp system/system DIRECTORY=DIR DUMPFILE=DATE-BDD-SCH.dpmp LOGFILE=DATE-BDD-SCH.log SCHEMAS=SCH CONTENT=ALL

Content :

  • ALL unloads both data and metadata. This is the default
  • DATA_ONLY unloads only table row data; no database object definitions are unloaded
  • METADATA_ONLY unloads only database object definitions; no table row data is unloaded


Datapump d'une table :

expdp system/system DIRECTORY=DIR DUMPFILE=DATE-BDD-TABLE.dpmp LOGFILE=DATE-BDD-TABLE.log TABLES=user.table

Import d'un dump (import) :

impdp system/system DIRECTORY=DIR DUMPFILE=DATE-BDD-SCH.dpmp logfile=DATE-BDD-SCH-imp.log REMAP_SCHEMA=SCHSRC:SCHDST REMAP_TABLESPACE=TBSRC:TBDEST CONTENT=ALL

Gestion des dossiers (voir Default Locations for Dump, Log, and SQL Files) :

CREATE DIRECTORY dpump_dir1 AS '/usr/apps/datafiles';
GRANT READ, WRITE ON DIRECTORY dpump_dir1 TO hr;
DROP DIRECTORY dpump_dir1;