RAC exam

I have attend the RAC beta exam this afternoon. 181 questions in 3.5 hours, it is a lot of questions! I prepared by reading the 2-day dba RAC document. There are also some questions about Maximum Availability Architecture (MAA) with physical and logical standby. Also some question which are Linux speci I have been short […]

read user-input in plsql

How can I read user input in plsql? kind of begin write(‘Enter a value for x : ‘); read(x); write(‘you enterred ‘||x); end; / the short answer is : you cannot do that. Ok, let’s try to do it in Linux ! $ cat interactiveplsql.sql set feedb off create or replace directory tmp as ‘/tmp’; […]

rename column_value

SQL> create or replace type t is 2 table of varchar2(12); 3 / Type created. SQL> create or replace function f return t is 2 begin return t(‘foo’); end; 3 / Function created. SQL> select * from table(f); COLUMN_VALUE ———— foo What is this column_value field? It is a pseudo-column. But you may want to […]

to divide or to multiply

warning, this test is cpu intensive, do not try on your productive server One user on the developpez.net French forums asked today about rewritting a division in a multiplication for tuning. Like select avg(sal)/2 from emp; in select avg(sal)*.5 from emp;. Well, I had to test this ! I execute 41055 divisions in a plsql […]

create your database with dbca

With dbca you can fasten the procedure of creating databases in your company. You can also run this in silent mode and create exactly the database you want, with your redo/undo size, with your parameters settings. You create the template once, and use it many times. Fast and easy 😀 I have one database which […]

Column qualification best practice

Lazyness at the development can have dramatic costs in production and maintenance. I want to summarize why and where you should always qualify your columns. Why? when you select or modify data from a table, you must qualify the columns you are using so if the order of the column change, or if one column […]

sql*plus pagesize explained

SQL*Plus is a not only the command-line interface to the database server, it is also a featured reporting tool with paging capabilities. The pagesize is the number of rows of one page. The default is 14 and the maximum is 50000. One of the common property of the page is the headers when selecting from […]

sequence / thread / system change number

I have seen a confusion between sequence and system change number quite often. The sequence is a fairly little number which is the number of log switch since database creation (it can be resetted by open resetlogs). The thread is only relevant in RAC. In single-instance database, it is always 1. You can find the […]