[Oracle]Top shell script pour monitoring BDD
Top DBA Shell Scripts for Monitoring the Database – BMC Software | Blogs
- Page 7
-
-
[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 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