Infrastructure at your Service

Category

Database Administration & Monitoring

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: [[email protected] ~]# 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
Nathan Courtine

Get the main information from Windows Server with PowerShell

By | Database Administration & Monitoring | No Comments

This blog will present you a way to retrieve Windows Server main information via PowerShell. The goal is to be able to automate the extraction of information for different purposes: audit, report generation, dashboards…   Disclaimer: I am not a developer but a SQL Server dba. If you find errors or some ways of improvement, I will be glad to read your comments!     Computer System information First, I initialize the WMI object containing…

Read More
Oracle Team

Flashback logging overhead: ‘db file sequential read’ on UNDO tablespace

By | Database Administration & Monitoring, Oracle | No Comments

By Franck Pachot . in my previous post I’ve reproduced an issue where some bulk insert into a GTT was generating too much undo. But the real case I encountered was worse: the insert was reading lot of UNDO. And lot of this undo was read from disk. Jonathan Lewis has pointed me to a possible explanation he has given (here) I’ve reproduced the testcase from the previous post after setting the database to do…

Read More
Oracle Team

Oracle: an unexpected lock behavior with rollback

By | Database Administration & Monitoring, Oracle | No Comments

By Franck Pachot . Here is an odd Oracle behavior I observed when a transaction that acquired a lock is rolled back. Note that this is related to a specific combination of locks that should not be encountered in production. So it’s not a bug. Just something unexpected.   First session In my first session I lock the DEPT table in share mode (RS) 20:56:56 SQL1> lock table dept in row share mode; Table(s) Locked….

Read More