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.

  • [Oracle] Calculate the lobs size

    #!/bin/sh
    #script to calculate the lobs size

    sqlplus /nolog<<!EOF 
    conn / as sysdba
    spool req1.sql
    set head off
    set feed off
    select 'SELECT max(dbms_lob.getlength('''||col.column_name||''')) from '||col.owner||'.'||col.table_name||';'
    from sys.dba_tab_columns col inner join sys.dba_tables t on col.owner = t.owner and col.table_name = t.table_name where col.data_type in ('BLOB', 'CLOB', 'NCLOB', 'BFILE') 
    and col.owner not in ('GSMADMIN_INTERNAL','OCOR_OWN','AUDSYS','ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN', 'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST', 'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'WKSYS', 'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')
    ;
    spool off
    exit
    !EOF

    sqlplus -s / as sysdba  @req1.sql

  • [PostgreSQL] Vérifier le statut de la réplication

    On master:

    select * from pg_stat_replication;

    On replica (streaming replication in my case):

    select * from pg_stat_wal_receiver;


    On your master, pg_stat_replication provides data about ongoing replication:

    select client_addr, state, sent_location, write_location,
        flush_location, replay_location from pg_stat_replication;

    On postgresql v10:

    select client_addr, state, sent_lsn, write_lsn,
    flush_lsn, replay_lsn from pg_stat_replication;

    On server
    postgres=# select usename,application_name,client_addr,backend_start,state,sync_state from pg_stat_replication ;


    On client

    postgres=# select pg_is_in_recovery();

    postgres=# select pg_last_xlog_receive_location();
    postgres=#    SELECT CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location()
                  THEN 0
                ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())
              END AS log_delay;
    postgres=# select pg_last_xlog_replay_location();

  • [PostgreSQL] COMPRESS

    Detecting inline, inline-compressed and TOAST storage

    +--+--------------+------------------+----------+-----------+-------------+
|id|full_name     |uncompressed_bytes|compressed|out_of_line|bytes_on_disk|
+--+--------------+------------------+----------+-----------+-------------+
|1 |joe toast     |4004              |false     |true       |4000         |
|2 |joe compressed|3000              |true      |false      |44           |
|3 |joe inline    |10                |false     |false      |11           |

  • [PostgreSQL] Find all the table and index size

    SELECT
    TableName
    ,pg_size_pretty(pg_table_size(TableName)) AS TableSize
    ,pg_size_pretty(pg_indexes_size(TableName)) AS IndexSize
    ,pg_size_pretty(pg_total_relation_size(TableName)) AS TotalSize
FROM 
(
     SELECT ('"' || table_schema || '"."' || table_name || '"') AS TableName
     FROM information_schema.tables
) AS Tables
ORDER BY 4 DESC

    source :How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database (tutorialdba.com)

  • [ORACLE]Evaluer une base de données

    Il peut être utile d'évaluer une base de données avant de penser à la migrer dans un autre SGBD.

    On va alors s'intéresser aux types d'objets qu'elle contient pour valider ou invalider la migration dans tel ou tel SGBDR.

    Exemple le plus probant : une migration d'une base oracle dans une base postgreSQL.

    La requête suivante générique et passe partout pourra aider à faire l'évaluation.

    select (select name from v$database) as database_name, owner,sum(bytes)/1024/1024 as "Number", 'Size in MB' as object_type
    from dba_segments 
    where owner not in ('WMSYS','APPQOSSYS','DBSFWUSER','DBSNMP','AUDSYS','GSMADMIN_INTERNAL','ORACLE_OCM','OUTLN','PUBLIC','REMOTE_SCHEDULER_AGENT','XDB','SYS','SYSTEM')              
    group by owner              
    union
    select (select name from v$database) as database_name,
    col.owner as schema_name, count(distinct col.table_name),
    'Lobs' as object_type
    from dba_tab_columns col 
    inner join dba_tables t on col.owner = t.owner and col.table_name = t.table_name 
    where col.data_type in ('BLOB', 'CLOB', 'NCLOB', 'BFILE')
    and col.owner not in ('OAUTHDBSCHEMA','AUDSYS','GSMADMIN_INTERNAL','ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN', 'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST', 'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'WKSYS', 'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC') 
    group by col.owner 
    union    
    SELECT 
    (select name from v$database) as database_name,
    TABLE_OWNER,count(distinct table_name) as partitionned_tables,'Partitions'              
    FROM   dba_tab_partitions                                    
    WHERE TABLE_OWNER not in('WMSYS','SYS','SYSTEM','DBSFWUSER','DBSNMP','AUDSYS','GSMADMIN_INTERNAL','XDB','ORACLE_OCM')                    
    group BY TABLE_OWNER
    union    
    select (select name from v$database) as database_name,
    owner,
    count(distinct object_name),
    'Packages' as object_type 
    from dba_objects where object_type='PACKAGE' and owner not in ('WMSYS','SYS','SYSTEM','DBSFWUSER','DBSNMP','AUDSYS','GSMADMIN_INTERNAL','XDB','ORACLE_OCM')               
    group by owner
    union
    select (select name from v$database) as database_name,
    owner,
    count(distinct object_name),
    'Procedures' as object_type 
    from dba_objects where object_type='PROCEDURE' 
    and owner not in ('WMSYS','SYS','OUTLN','REMOTE_SCHEDULER_AGENT','SYSTEM','DBSFWUSER','DBSNMP','AUDSYS','GSMADMIN_INTERNAL','XDB','ORACLE_OCM')
    group by owner
    union
    select (select name from v$database) as database_name,
    owner,
    count(distinct object_name) , 'Functions'
    from dba_objects 
    where object_type='FUNCTION' 
    and owner not in ('WMSYS','SYS','OUTLN','REMOTE_SCHEDULER_AGENT','SYSTEM','DBSFWUSER','DBSNMP','AUDSYS','GSMADMIN_INTERNAL','XDB','ORACLE_OCM') 
    group by owner
    union
    select (select name from v$database) as database_name,
    owner ,
    count(distinct trigger_name),'Triggers' as object_type  
    from dba_triggers
    where owner not in ('GSMADMIN_INTERNAL','ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
       'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN', 
       'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
       'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'WKSYS',
       'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
       'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')  
    group by owner
    union
    select (select name from v$database) as database_name,
    owner,count(distinct table_name),'Global Temporary Tables' 
    from dba_tables 
    where owner not in ('GSMADMIN_INTERNAL','ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS',
       'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN',
       'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
       'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'WKSYS',
       'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
      'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')
       and TEMPORARY = 'Y'
       group by owner
    union 
    select (select name from v$database) as database_name, OWNER,count(distinct TABLE_NAME) ,'External Tables' 
     from dba_external_tables 
    where owner not in ('GSMADMIN_INTERNAL','ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS',
       'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN',
       'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
       'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'WKSYS',
       'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
       'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')
       group by owner
    union
    SELECT (select name from v$database) as database_name,schema,count(distinct namespace),'Context' as object_type
      FROM dba_context
      where schema not in ('GSMADMIN_INTERNAL','ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS',
       'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN',
       'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
       'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'WKSYS',
       'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
       'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC','OCOR_OWN')
       group by schema;

     

  • Résoudre l'ORA-600 doc id 2523249.1

    Optimizer expression statistics monitoring is introduced in 12.2 database version.

     For specific applications, this might end up in growing repository segments. Monitoring expression statistics is introduced for a new optimizer feature in 12.2 to collect expression usage statistics in SQL queries.

    Monitoring feature is controlled by parameter "_column_tracking_level". High growth have been noticed when monitoring expression statistics is active, enabled by default.

     

     

    Solution:

    Disable future monitoring:

    alter system set "_column_tracking_level"=17 scope=both;

    --wait 10 minutes

     

    To purge data from tables:

    exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();

    truncate table sys.exp_head$ drop storage;

    truncate table sys.exp_obj$ drop storage;

    truncate table sys.exp_stat$ drop storage;

    alter index SYS.I_EXP_HEAD$ rebuild tablespace sysaux online;

    alter index SYS.I_EXP_STAT$ rebuild tablespace sysaux online;

    alter index SYS.I_EXP_OBJ$ rebuild tablespace sysaux online;

     

    To re-enable expression statistics monitoring later :

    alter system set "_column_tracking_level"=21 scope=both;