#!/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] pgBackRest sauvegarder la standby
Backup From a Standby | pgBackRest (pmatseykanets.github.io)
-
[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 serverpostgres=# select usename,application_name,client_addr,backend_start,state,sync_state from pg_stat_replication ;
On clientpostgres=# 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) -
[PostgreSQL] Streaming Replication Monitoring Script
Script de création d'une fonction "streaming_slave_check" pour la réplication en postgreSQL.
-
[PostgreSQL] repmgr tuto
-
[PostgreSQL] Anonymisation des données
-
[PostgreSQL] Déploiement d'un cluster PATRONI
-
[PostgreSQL]Kill une session
SELECT *, pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
AND datname = '<dbname>';
select pg_terminate_backend(pid)
from pg_stat_activity
where pid = '<PID>'; -
[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;