Installation Oracle19c

Installation Oracle 19c

PostgreSQL bonnes pratiques

Restriction du schéma « public »
Définir des nouveaux tablespaces pour les données et indexes et temp et changer le nom du tablespace par défaut (data et temp)

 

[HVAULT] Roles postgreSQL

Script de création des roles HVAULT dans ibm cloud pour  postgreSQL (dMZR);

[PostgreSQL] Ora2pg Install

yum install oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm
yum install oracle-instantclient12.2-devel-12.2.0.1.0-1.x86_64.rpm
yum install oracle-instantclient12.2-jdbc-12.2.0.1.0-1.x86_64.rpm
yum install oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64.rpm
yum -y install perl-DBI
yum install gcc
yum install glibc-devel
yum install perl-ExtUtils-MakeMaker -y
yum install perl-open.noarch -y

export ORACLE_HOME=/usr/lib/oracle/12.2/client64
export LD_LIBRARY_PATH=/usr/lib/oracle/12.2/client64/lib

tar -xvzf ora2pg-23.0.tar.gz
cd ora2pg-23.0/
perl Makefile.PL
make && make install

tar -xvzf DBD-Oracle-1.83.tar.gz
cd DBD-Oracle-1.83
perl Makefile.PL
make && make install

[Oracle]Top shell script pour monitoring BDD

Top DBA Shell Scripts for Monitoring the Database – BMC Software | Blogs

[PostgreSQL] restore de blobs

SELECT lo_unlink(oid) FROM pg_largeobject_metadata;
WARNING:  out of shared memory
ERROR:  out of shared memory
HINT:  You might need to increase max_locks_per_transaction.

 

[Systeme] Create a logical volume for ora2pg

Create a logical volume of 450 Go

[PostgreSQL]tables sans oid en big serial

select sch.nspname as schemaname,
       tab.relname as tablename,
       col.attname as columnname,
       col.attnum as columnnumber,
       col.atttypid,
       typ.typname as columntype
from pg_attribute col
join pg_type typ on col.atttypid = typ.oid
join pg_class tab on col.attrelid = tab.oid
join pg_namespace sch on tab.relnamespace = sch.oid
left join pg_attrdef def on tab.oid = def.adrelid
and col.attnum = def.adnum
left join pg_depend deps on def.oid = deps.objid
and deps.deptype = 'N'
where sch.nspname != 'information_schema'
       and sch.nspname not like 'pg_%' -- won't work if you have user schemas matching pg_
       and col.attnum > 0
       and typ.typcategory = 'N'
       and col.attnotnull = true
       and typ.typname = 'int4'
order by sch.nspname,
       tab.relname,
       col.attnum;


with seqs as
       (select oid,
             relname as sequencename
             from pg_class
             where relkind = 'S' )
select sch.nspname as schemaname,
       tab.relname as tablename,
       col.attname as columnname,
       col.attnum as columnnumber,
       col.atttypid,
       typ.typname as columntype,
       seq.sequencename
from pg_attribute col
join pg_type typ on col.atttypid = typ.oid
join pg_class tab on col.attrelid = tab.oid
join pg_namespace sch on tab.relnamespace = sch.oid
left join pg_attrdef def on tab.oid = def.adrelid
and col.attnum = def.adnum
left join pg_depend deps on def.oid = deps.objid
and deps.deptype = 'N'
left join seqs seq on deps.refobjid = seq.oid
where sch.nspname != 'information_schema'
       and sch.nspname not like 'pg_%' -- won't work if you have user schemas matching pg_
       and col.attnum > 0
       and typ.typcategory = 'N'
       and col.attnotnull = true
order by sch.nspname,
       tab.relname,
       col.attnum;

select sch.nspname as schemaname,
       tab.relname as tablename,
       col.attname as columnname,
       col.attnum as columnnumber,
       col.atttypid,
       typ.typname as columntype
from pg_attribute col
join pg_type typ on col.atttypid = typ.oid
join pg_class tab on col.attrelid = tab.oid
join pg_namespace sch on tab.relnamespace = sch.oid
left join pg_attrdef def on tab.oid = def.adrelid
and col.attnum = def.adnum
left join pg_depend deps on def.oid = deps.objid
and deps.deptype = 'N'
where sch.nspname != 'information_schema'
       and sch.nspname not like 'pg_%' -- won't work if you have user schemas matching pg_
       and col.attnum > 0
       and typ.typcategory = 'N'
       and col.attnotnull = true
       and typ.typname = 'int4'
order by sch.nspname,
       tab.relname,
       col.attnum;


with seqs as
       (select oid,
             relname as sequencename
             from pg_class
             where relkind = 'S' )
select sch.nspname as schemaname,
       tab.relname as tablename,
       col.attname as columnname,
       col.attnum as columnnumber,
       col.atttypid,
       typ.typname as columntype,
       seq.sequencename
from pg_attribute col
join pg_type typ on col.atttypid = typ.oid
join pg_class tab on col.attrelid = tab.oid
join pg_namespace sch on tab.relnamespace = sch.oid
left join pg_attrdef def on tab.oid = def.adrelid
and col.attnum = def.adnum
left join pg_depend deps on def.oid = deps.objid
and deps.deptype = 'N'
left join seqs seq on deps.refobjid = seq.oid
where sch.nspname != 'information_schema'
       and sch.nspname not like 'pg_%' -- won't work if you have user schemas matching pg_
       and col.attnum > 0
       and typ.typcategory = 'N'
       and col.attnotnull = true
order by sch.nspname,
       tab.relname,
       col.attnum;

ADRCI

existe depuis la 11G  , 
adrci est un outil pratique pour à la fois 
visualiser facilement le contenu de l'alert.log
purger les fichiers de logs , trace, et incidents
pacakger les incidents ou les problemes pour les envoyer au support

s'utilise en mode interactif ou batch  (demo)

relation entre incident et problemes

un incident est une occurence d'un probleme 
en d'autre terme, le meme probleme peut avoir plusieurs incidents

avec adrci nous avons un utilitaire efficace pour traiter les erreurs et pour collecter des informations pour les envoyer au support Oracle.    

Document 454927.1.pdf

[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)