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 2

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

  • [OEM] EMCLI - Ajout d'une propriété pour une target donnée

    Il faut se connecter au serveur où le GRID ou l'OMS est installé.
    Positionner l'environnement OMS (exemple /oracle/Middleware/emcc13/bin).