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 on time, the question were very long to read, and 181 questions in 210 minutes, I did not have the time to review all.

Now I must wait about 10 weeks to get the result, but I am quite optimistic 8-)

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';

declare
inFile utl_file.file_type;
outFile utl_file.file_type;
x varchar2(40);
begin
inFile := utl_file.fopen('TMP','in','R');
outFile := utl_file.fopen('TMP','out','W');
utl_file.put_line(outFile,'Enter a value for x : ');
utl_file.fflush(outFile);
utl_file.get_line(inFile,x);
utl_file.put_line(outFile,'you enterred '||x);
utl_file.fclose(inFile);
utl_file.fclose(outFile);
end;
/

quit

$ mknod /tmp/out p; mknod /tmp/in p
$ (cat /tmp/out &);(sqlplus -s scott/tiger @interactiveplsql &
);cat>/tmp/in
Enter a value for x :
ABC123
you enterred ABC123

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 have an user-defined column name.


SQL> create or replace type o is 
 2  object(BAR varchar2(12));
 3  /
Type created.

SQL> create or replace type t is 
 2  table of o;
 3  /
Type created.

SQL> create or replace function f return t is 
 2  begin return t(o('foo')); end;
 3  /
Function created.
SQL> select * from table(f);

BAR
------------
foo         

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 loop. To avoid incrementation, I divide by 1.014… and multiply by 0.986… in a way that the result keep the same all over the loop.


SQL> 
SQL> var z number
SQL> var y number
SQL> exec :z := power(2,102)*2e-31;
SQL> exec :y := 1e125;
SQL> set timi on
SQL> exec while (:y>1e-125) loop :y:=:y/:z; end loop
Elapsed: 00:00:00.42
SQL> set timi off
SQL> print y

                               Y
--------------------------------
9.9879215917842541374103299E-126

SQL> exec :z := power(2,-104)*2e31;
SQL> exec :y := 1e125;
SQL> set timi on
SQL> exec while (:y<1e-125) loop :y:=:y*:z; end loop
Elapsed: 00:00:00.28
SQL> set timi off
SQL> print y

                               Y
--------------------------------
9.9879215917842541374103299E-126

It has been difficult to find an example with clear difference and not too weird :twisted:

The operation divide is slower than multiply, probably to catch divide by zero errors...

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 :-D

I have one database which I created with SQL*PLUS called LSC01.

1) Create template lsc-template
dbca
–> Manage Templates
–> Create a database template
–> from an existing database (structure as well as data)
–> LSC01
–> lsc-template
–> convert the file locations to use OFA structure

This takes some place on disk and will speed up database creation. Technically speaking, it is doing a compressed backup with RMAN that will be restore, and restore is way faster than create database

2) Create database LSC99 in silent mode or progressOnly mode.
dbca -silent -createDatabase -templateName lsc-template.dbc -gdbName LSC99.lcsys.ch

It took me only two minutes to create my database on my notebook !

Try it ! Of course I expect comments on this post :mrgreen:

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 is added, renamed or removed, so long this column is not related to your query your code should keep working. A typical example is when the dba add a LAST_MODIFICATION_DATE and a trigger to automatically fill that column for auditing purpose. It should never make your application fail.

0) sample table


create table t1(x number, y number);
create table t2(a number, b number);
create table t3(x number, z number);

1) insert into table
bad:insert into t1 values(1,2);
good:insert into t1(x,y) values(1,2);

bad:

insert into t1
select * from t2;

good:
insert into t1(x,y)
select t2.a,t2.b from t2;

bad:

insert all
when a>0 then into t1
select * from t2;

good:
insert all
when a>0 then into t1(x,y)
select t2.a,t2.b from t2;

2) merge
bad:

merge into t1
using t2 on (x=a)
when matched then
  update set y=b
  where (b>y)
delete
  where (b>y)
when not matched then
  insert values(a,b)
  where (b>0);

good:
merge into t1
using t2 on (t1.x=t2.a)
when matched then
  update set y=t2.b
  where (t2.b>t1.y)
delete
  where (t2.b>t1.y)
when not matched then
  insert (x,y) values(t2.a,t2.b)
  where (t2.b>0);

Not prefixing the column here will bug as soon as a column a or b is added to table t1

2) joins

just never use natural join in production

bad:

select *
from t1 natural join t3;

good:
select x, t1.y, t3.z
from t1 join t3 using (x);

bad:

select *
from t1,t2
where x=a;

good:
select t1.x,t1.y,t2.a,t2.b
from t1,t2
where t1.x=t2.a;

It is not about good looking code, it is about data quality and stability 8-)

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 a table.


SQL> sho pages
pagesize 14
SQL> select empno,ename from emp;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS

     EMPNO ENAME
---------- ----------
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.

This is rather an annoying effect of the default setting than a feature and there is no set pagesize unlimited. The only way to have the header only once, is to set the pagesize to the maximum or use this trick : set pages 0 emb on newp none. Unfortunately, the later does not work for HTML reporting.

One of the less known and advanced sql*plus capabilities is the title of the page.

Today on the developpez.net forums I had a question about generating a describe for each table in user_tables.

The short answer is select * from user_tab_columns

In my output, I want to have each table on a separate page, with the table_name in the title, and the column names, not null options and datatypes in the page.

First I want to have a page per table, this I can define with break


bre on table_name ski page

I want to have the table name in the title of the page, but not as a column. I add some blank lines in the top title and in the bottom title.


col table_name new_v table_name nopri
tti le table_name s 2
bti s 1

Finally I set the pagesize to something bigger than the maximum number of columns of tables plus 6 for header and title

set pages 0
col pages new_v pages nopri
select max(count(*))+6 pages
from user_tab_columns
where table_name in (‘EMP’,’DEPT’,’T’)
group by table_name;
set pages &pages

Now the select

select
table_name,
column_name “Name”,
decode(nullable,’N’,’NOT NULL’) “Null?”,
DATA_TYPE||
case when DATA_TYPE in (‘NUMBER’,’FLOAT’)
and (data_precision is not null
or data_scale is not null) then
‘(‘||nvl(DATA_precision,38)||
case when data_scale!=0 then
‘,’||DATA_SCALE
end
||’)’
when data_type like ‘%CHAR%’ then
‘(‘||DATA_LENGTH||’)’ end
“Type”
from user_tab_columns
where table_name in (‘EMP’,’DEPT’,’T’);

DEPT

Name                           Null?    Type
------------------------------ -------- --------------------
DEPTNO                         NOT NULL NUMBER(2)
DNAME                                   VARCHAR2(14)
LOC                                     VARCHAR2(13)

EMP

Name                           Null?    Type
------------------------------ -------- --------------------
EMPNO                          NOT NULL NUMBER(4)
ENAME                                   VARCHAR2(10)
JOB                                     VARCHAR2(9)
MGR                                     NUMBER(4)
HIREDATE                                DATE
SAL                                     NUMBER(7,2)
COMM                                    NUMBER(7,2)
DEPTNO                                  NUMBER(2)

For a HTML report, set mark html on


set mark html on
/

DEPT
Name Null? Type
DEPTNO NOT NULL NUMBER(2)
DNAME   VARCHAR2(14)
LOC   VARCHAR2(13)

 
 

EMP
Name Null? Type
EMPNO NOT NULL NUMBER(4)
ENAME   VARCHAR2(10)
JOB   VARCHAR2(9)
MGR   NUMBER(4)
HIREDATE   DATE
SAL   NUMBER(7,2)
COMM   NUMBER(7,2)
DEPTNO   NUMBER(2)

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 current sequence number with the following query


select sequence#,thread# 
from v$log 
where status='CURRENT';

The system change number (SCN) is a much higher number, which is continously increasing, even when you do nothing. The dbms_flashback package has a function to return the current system change number :


select dbms_flashback.get_system_change_number
from dual;

In 10g, there is a standard function to get the current system change number


select timestamp_to_scn(current_timestamp) from dual;

Before a major application/database upgrade, it is good practice to make a backup and write down the SCN for an easier recovery procedure.

read my comment about non-accuracy of timestamp_to_scn