Infrastructure at your Service

Category Archives: Database Administration & Monitoring

Daniel Westermann

Swiss Postgres Conference 2015

By | Database Administration & Monitoring | No Comments

At the 26th of June I had the chance to attend the second Swiss Postgres Conference at the HSR Rapperswil. It was packed with interesting sessions. Magnus Hagander, a well known community member, started by giving an overview of PostgreSQL Europe. Then I joined Hans Schönig for learning how to write aggregate functions in PostgreSQL. Honestly I did not understand all of it but at least I am now aware of what you can do…

 
Read More
Daniel Westermann

another way to list invalid objects

By | Database Administration & Monitoring | One Comment

How often did I type a query like this to list the invalid objects in a database? select count(*) from dba_objects where status ‘VALID'; — and user in/not in Today I learned another way to do the same. There is a view called dba_invalid_objects. Surprisingly the view is not listed in the official 12c database reference. It is however mentioned in the upgrade guide. Let’s do some tests. Currently I do not have any invalid…

 
Read More
Franck Pachot

2 ways to move archivelogs – both need RMAN

By | Database Administration & Monitoring, Oracle | One Comment

By Franck Pachot . The database must know where are the files. If you move them from the OS, the database will not be aware of it. But there is a database tool to copy or move files and update the database repository (controlfile). It’s RMAN. With RMAN you can either: update the repository after you’ve moved the file from the OS or do the both: move and update the repository The syntax is a…

 
Read More
Daniel Westermann

When a function returns a collection: can you directly reference the index of the returned collection?

By | Database Administration & Monitoring | One Comment

As I am still interested in PLSQL I browsed the documentation some days ago and learned something new which is about functions returning collections. Here is a simple test case: Let’s create a simple package, specifying on collection and one function: create or replace package pkg1 as type t1 is table of number index by pls_integer; function f1 ( a number ) return t1; end pkg1; / create or replace package body pkg1 as function…

 
Read More
Daniel Westermann

ODA workshop at Arrow ECS

By | Database Administration & Monitoring | One Comment

On the 16th and 17th of June David Hueber, Georges Grey and myself had the chance to attend the ODA hands on workshop at Arrow ECS. Lead Trainer Ruggero Citton (Oracle ODA Product Development) did the first day with plenty of theory and examples. On the second day we had the opportunity to play on a brand new ODA X5-2: [root@odax50 ~]# oakcli show server Power State : On Open Problems : 0 Model :…

 
Read More
Daniel Westermann

What is more efficient: arrays or single columns values? – oracle

By | Database Administration & Monitoring | One Comment

In the last post on this topic it turned out that using an array as a column type needs more space than using a column per value in PostgreSQL. Now I’ll do the same test case in Oracle. As with the PostgreSQL test case I’ll create two test tables in Oracle: One using 10 columns and one just using one column of type varray: SQL> create table single_values ( a number, b number, c number,…

 
Read More
Daniel Westermann

What is more efficient: arrays or single column values?

By | Database Administration & Monitoring | One Comment

In PostgreSQL ( as well as in other rdbms ) you can define columns as arrays. What I wondered is: What is more efficient when it comes to space: Creating several columns or just creating once column as array? The result, at least for me, is rather surprising. First, let’s create a table with columns of type int: postgres=# create table single_values ( a int, b int, c int, d int, e int, f int,…

 
Read More
Daniel Westermann

SQL Interpolation with psql

By | Database Administration & Monitoring | One Comment

The PostgreSQL psql utility provides some really nice features. One of these features is SQL interpolation which allows us to do interesting things, e.g. reading files and analyze the results directly in the database. This post will show how to use this by reading and analyzing sar files of a linux server. Usually linux hosts store sar statistics in the /var/log/sa directory: ls /var/log/sa sa02 sa03 sa04 sa05 sa06 sa07 sa08 sa09 sa10 sa11 sa12…

 
Read More
Daniel Westermann

A free PostgreSQL cloud database?

By | Database Administration & Monitoring | One Comment

Recently I was looking for a free PostgreSQL cloud database service for testing. Why? Because I’d like to use such a cloud instance for testing no matter on which workstation or OS I am currently on. Another reason is, that I could could prepare some demos at home and use the same demos at work without needing to worry about taking the database with me each time. There are probable many more services than the…

 
Read More
Daniel Westermann

PostgreSQL portable?

By | Database Administration & Monitoring | One Comment

What a surprise: Headed over to the sourceforge page, downloaded, installed (which is just a matter of next/next/next) : Not really the latest patchset but far more than I expected So I can do tests on a Windows machine where I do not have any permissions to install software. Great. If you prefer a graphical client then go ahead and use the portable version of PgAdmin.  

 
Read More