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.

Professionnel - Page 3

  • 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) */. 

  • PLug in sqlserver pour OEM 13c

    https://docs.oracle.com/cd/E63000_01/SQLPG/overview.htm#SQLPG108

     

    https://docs.oracle.com/cd/E24628_01/doc.121/e24473/plugin_mngr.htm#EMADM13102

  • 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;

    /

  • Tuner une requête Oracle

    http://www.br8dba.com/tag/manually-run-sql-advisor/#2

  • Agrandir datafile avant import


    define TBS='SIM_SIM_LOB01'
    define DFSZ='10000M'
    select 'Alter database datafile '''||FILE_NAME||''' resize '||'&DFSZ'||';' from dba_data_files where TABLESPACE_NAME like '&TBS' order by FILE_NAME;

  • Déplacer des objets de type LOB dans le bon tabespace (LOB)

    SET serveroutput ON;
    DECLARE
    ordre_sql VARCHAR2(32000 CHAR);
    nomTable VARCHAR2(200 CHAR);
    nomColonne VARCHAR2(200 CHAR);
    nomSchema VARCHAR2(200 CHAR) := 'JOJO';
    BEGIN
    DBMS_OUTPUT.ENABLE(1000000);
    FOR lob_cols IN (
    SELECT table_name nomTable, column_name nomColonne FROM ALL_TAB_COLS
    WHERE data_type LIKE '%LOB%'
    AND owner = nomSchema
    AND table_name IN (SELECT table_name FROM ALL_TABLES WHERE partitioned = 'NO' AND TEMPORARY = 'N')
    )
    LOOP
    ordre_sql := 'ALTER TABLE '||nomSchema||'."'
    || lob_cols.nomTable
    || '" MOVE LOB('
    || lob_cols.nomColonne
    || ') STORE AS SECUREFILE (TABLESPACE JOJO_LOB01 NOcompress);';
    --EXECUTE IMMEDIATE ordre_sql;
    DBMS_OUTPUT.PUT_LINE(ordre_sql);
    END LOOP;
    COMMIT;
    EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
    DBMS_OUTPUT.PUT_LINE('Il faut créer le tablespace JOJO_LOB01 avant d effectuer cette action');
    END;
    /

  • Change permission using UT_FILE

    https://jenniferlinca.wordpress.com/2008/03/28/using-utl_file-how-file-permissions-are-determined-working-sample/

  • Supprimer les segments vides

    exec dbms_space_admin.drop_empty_segments(schema_name=>'HR');