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;
Professionnel - Page 11
-
[PostgreSQL]tables sans oid en big serial
-
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 supports'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 incidentsavec adrci nous avons un utilitaire efficace pour traiter les erreurs et pour collecter des informations pour les envoyer au support Oracle.
-
[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) -
[PostgreSQL] Streaming Replication Monitoring Script
Script de création d'une fonction "streaming_slave_check" pour la réplication en postgreSQL.
-
[PostgreSQL] repmgr tuto
-
[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] Anonymisation des données
-
Badge WAICF
-
PostgreSQL Un tour d'horizon !
PostgreSQL around the world
PostgreSQL 16 is out.
https://www.postgresql.org/docs/16/release-16.html
Digging into PostgreSQL iceberg
https://avestura.dev/blog/explaining-the-postgres-meme
PostgreSQL partitioning
https://www.timescale.com/blog/when-to-consider-postgres-partitioning/
Who does PostgreSQL
https://momjian.us/main/blogs/pgblog/2023.html#September_11_2023
-
[Postgres] NEWS
There is some new about PostgreSQL world.
Some useful tricks and tips about PostgreSQL, like how to get unused index list (but so many more like displaying fun smiley instead of blanks for NULL…)
https://www.crunchydata.com/postgres-tips
Some basics about transactions with examples
https://stormatics.tech/salmans-planet-postgresql/how-to-execute-transactions-in-postgresql
Huge progress with replication; implement bi-directional replication with upcoming PostgreSQL 16