Ok

En poursuivant votre navigation sur ce site, vous acceptez l'utilisation de cookies. Ces derniers assurent le bon fonctionnement de nos services. En savoir plus.

Expertise en SGBDR - Page 27

  • Paramètre obsolète

    https://mikedietrichde.com/2016/11/22/optimizer_adaptive_features-obsolete-in-oracle-12-2/

     

    The Oracle Database 12.1 parameter OPTIMIZER_ADAPTIVE_FEATURES has been made OBSOLETE (i.e. must be removed from the SPFILE when upgrading) in Oracle Database 12.2.

    It gets replaced with two parameters of whom one is enabled, the other one is disabled by default:

    • OPTIMIZER_ADAPTIVE_PLANS=TRUE by default
    • OPTIMIZER_ADAPTIVE_STATISTICS=FALSE by default

    https://blogs.oracle.com/optimizer/optimizer-adaptive-features-in-oracle-database-12c-release-2

  • Adjusting the optimizer_index_cost_adj Parameter

    https://xcoolwinds.wordpress.com/2013/08/23/adjusting-the-optimizer-index-cost-adj-parameter/

     

    Issue the following query to view the average wait times for both of the wait events:

    SQL> select event, average_wait from v$system_event
           where event like 'db file s%read';
    EVENT                                                            AVERAGE_WAIT
    ---------------------------------                            -------------------------------
    db file sequential read                                                   .91
    db file scattered read                                                   1.41

     

    SQL>

    Based on the output of this query, single block sequential reads take roughly 75% of the time it takes to perform a multiblock (scattered) read. This indicates that the optimizer_index_cost_adj parameter should be set to somewhere around 75. However, as mentioned earlier, setting the parameter at the database level isn't recommended—instead, use this parameter sparingly for specific statements where you want to force the use of an index. Note that setting the parameter at the session level requires the DBA to grant the user privileges to issue the alter session command (or you must do it through some other procedural mechanism). A good alternative to having to issue the alter session statement is to specify the OPT_PARAM hint to change the parameter for a specific query, such as /*+ opt_param('optimizer_index_cost_adj',50) */. 

  • Constraint NOT NULL missing

     

    Data Pump Import With EXCLUDE=CONSTRAINT Or INCLUDE=CONSTRAINT Is Excluding And Respectively Not Including NOT NULL Constraints (Doc ID 1930631.1)

    After doing a Data Pump import (impdp) in 12.1.0.2 database release with parameter EXCLUDE=CONSTRAINT the NOT NULL constraints from source tables are not in target tables. Sequence of steps leading to the problem:
    – Data Pump export of tables with NOT NULL and referential integrity constraints
    – Data Pump import with EXCLUDE=CONSTRAINT

     

    http://blog.umairmansoob.com/data-dump-import-missing-not-null-constraints/

  • Taille des fichiers unix

    du -ms expdp_$ORACLE_SID_20190* | awk '{ total += $1 }; END { print total }'

     

     

  • Lister la taille des blobs partitionnés

    La requête suivant permet de te lister la taille des partitions qui contiennent des LOBS  :

     

    SELECT DS.TABLESPACE_NAME, SEGMENT_NAME,PARTITION_NAME,ROUND(SUM(DS.BYTES)/(1024 * 1024)) AS MB
    FROM DBA_SEGMENTS DS
    WHERE
    DS.TABLESPACE_NAME not in ('SYSAUX','SYSTEM')
    AND
    SEGMENT_TYPE='LOB PARTITION'
    GROUP BY DS.TABLESPACE_NAME,SEGMENT_NAME,PARTITION_NAME
    ORDER BY 4;

    La requête suivante permet de te lister la taille des partitions pour le segment TOTO : 

    SELECT DS.TABLESPACE_NAME, SEGMENT_NAME, ROUND(SUM(DS.BYTES) / (1024 * 1024)) AS MB
    FROM DBA_SEGMENTS DS
    WHERE SEGMENT_NAME ='TOTO'
    GROUP BY DS.TABLESPACE_NAME,
    SEGMENT_NAME;

    Je ne suis pas un escroc, je suis un opportuniste...

  • Suppression des objets pour un schéma

    begin
    for f in (select table_name from user_tables) loop
    execute immediate 'drop table "'||f.table_name||'" cascade constraints';
    end loop;
    for f in (select object_type, object_name from user_objects
    where object_type in ('SEQUENCE','VIEW','FUNCTION','PROCEDURE','PACKAGE','SYNONYM','DATABASE LINK')) loop
    execute immediate 'drop '||f.object_type||' "'||f.object_name||'"';
    end loop;
    for f in (select type_name from user_types) loop
    execute immediate 'drop type "'||f.type_name||'" force';
    end loop;
    end;

    /