Database Helper

From RADION OpenLab

1 Oracle

set sqlplus line width :

i.e. when you want 150 char width :

SQL> set lin 150

Get the current log status :

SQL> select * from sys.v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TI
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------
         1          1        589  104857600          2 YES INACTIVE             265814630 16.01.06
         2          1        590  104857600          2 YES INACTIVE             265814633 16.01.06
         3          1        591  104857600          2 NO  CURRENT              265921102 16.01.06

get current archive log configuration :

SQL> ARCHIVE LOG LIST
Datenbank-Log-Modus              Archive-Modus
Automatische Archivierung             Deaktiviert
Archivierungsziel            e:\product\9.2.0.1.0\RDBMS
?lteste Online-Log-Sequenz     589
Nõchste zu archivierende Log-Sequenz   591
Aktuelle Log-Sequenz           591

create spfile from pfile :

SQL> create spfile='/PATH/TO/SPFILE.ORA' from pfile='/PATH/TO/PFILE.ORA'

2 PostgreSQL

In order to see all statements in the following queries you have to enable stats_command_string in your postgresql.conf.

stats_command_string = on

Get current locks :

SELECT dbu.usename AS locker, pg_stat_get_backend_pid(S.backendid) AS pid, 
db.datname||'.'||n.nspname||'.'||r.relname AS relation, l.mode, pg_stat_get_backend_activity(S.backendid ) AS query 
FROM  pg_user dbu, 
(SELECT pg_stat_get_backend_idset() AS backendid) AS S, pg_database db, pg_locks l, pg_class r, pg_namespace n 
WHERE db.oid = pg_stat_get_backend_dbid(S.backendid) 
AND   dbu.usesysid = pg_stat_get_backend_userid(S.backendid) 
AND   l.pid = pg_stat_get_backend_pid(S.backendid) 
AND   l.relation = r.oid 
AND   l.DATABASE = db.oid 
AND   r.relnamespace = n.oid 
AND   l.granted 
ORDER BY db.datname, n.nspname, r.relname, l.mode;

Get current activities :

SELECT datname, query_start, backend_start, client_addr, current_query FROM pg_stat_activity;