flashback archive table

One of the problem with flashback queries in 10g and before is that you never know if it will works, especially you cannot expect to have flashback queries working for very old tables.

Let’s imagine you want to export your CUSTOMER as of 30/6/2007. No chance in 10g…

Well, with 11g, you can create a flashback archive, and it will save all change until end of retention (many years if you want).

Here it is :

SQL> connect / as sysdba
Connected.
SQL> create tablespace s;

Tablespace created.

SQL> create flashback archive default fba01 tablespace s 
  retention 1 month;

Flashback archive created.

SQL> connect scott/tiger
Connected.
SQL> create table t(x number) flashback archive;

Table created.

SQL> host sleep 10

SQL> insert into t(x) values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT dbms_flashback.get_system_change_number FROM dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
                  337754

SQL> update t set x=2;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from t as of scn 337754;
         X
----------
         1

SQL> alter table t no flashback archive;

Table altered.

SQL> drop table t;

Table dropped.

SQL> select FLASHBACK_ARCHIVE_NAME, RETENTION_IN_DAYS,
  STATUS from DBA_FLASHBACK_ARCHIVE;
FLASHBACK_ARCHIVE_NAME RETENTION_IN_DAYS STATUS
---------------------- ----------------- -------
FBA01                                 30 DEFAULT

SQL> connect / as sysdba
Connected.
SQL> drop flashback archive fba01;

Flashback archive dropped.

SQL> drop tablespace s;

Tablespace dropped.

note that a month is 30 days. If you try to create a flashback archive in a non-empty tablespace you may get
ORA-55603: Invalid Flashback Archive command
which is not a very helpful message

select*from”EMP”where’SCOTT’=”ENAME”…

What is wrong with this query?


select*from"EMP"where'SCOTT'="ENAME"and"DEPTNO"=20;
     EMPNO ENAME      JOB              MGR HIREDATE     
---------- ---------- --------- ---------- ---------
      7788 SCOTT      ANALYST         7566 13-JUL-87

It is a zero-space query 8-)

You could write it as


select
    *
from
    "EMP"
where
    'SCOTT'="ENAME"
    and
    "DEPTNO"=20;

personnaly, I would write it as


select *
from emp
where ename='SCOTT'
  and deptno=20;

Formatting is very important, it makes your code nice to read and indentation make the blocks visualable.

Auto-formatting is also fine, but I like to decide myself if the line is too long, or if I want to have FROM and EMP on the same line.

Have a look at the free online SQL Formatter SQLinForm

positive infinity

I have read a long long time ago the following note on positive infinity http://www.ixora.com.au/notes/infinity.htm

Today I finally succeeded in inserting positive infinity in a number field


create table t as select 
  STATS_F_TEST(cust_gender, 1, 'STATISTIC','F') f
from (
  select 'M' cust_gender from dual union all 
  select 'M' from dual union all 
  select 'F' from dual union all 
  select 'F' from dual)
;

I am so happy :-D

Let’s try a few queries


SQL> desc t
 Name              Null?    Type
 ----------------- -------- ------
 F                          NUMBER

SQL> select f from t;

         F
----------
         ~

SQL> select f/2 from t;
select f/2 from t
        *
ERROR at line 1:
ORA-01426: numeric overflow

SQL> select -f from t;

        -F
----------
        -~

SQL> select cast(f as binary_double) from t;

CAST(FASBINARY_DOUBLE)
----------------------
                   Inf

SQL> select * from t
  2  where cast(f as binary_double) = binary_double_infinity;

         F
----------
         ~

Now expect a lot of bugs with your oracle clients 8-)

Toad 9 for example returns


SQL>  select f from t
select f from t
              *
Error at line 1
OCI-22065: number to text translation for the given 
format causes overflow

on delete cascade

The use of a referential integrity constraint is to enforce that each child record has a parent.


SQL> CREATE TABLE DEPT
  2    (DEPTNO NUMBER PRIMARY KEY,
  3    DNAME VARCHAR2(10)) ;

Table created.

SQL> CREATE TABLE EMP
  2    (EMPNO NUMBER PRIMARY KEY,
  3    ENAME VARCHAR2(10),
  4    DEPTNO NUMBER
  5      CONSTRAINT EMP_DEPT_FK
  6      REFERENCES DEPT(deptno));

Table created.

SQL> INSERT INTO DEPT(deptno,dname) VALUES
  2    (50,'CREDIT');

1 row created.

SQL> INSERT INTO EMP(EMPNO,ENAME,DEPTNO) VALUES
  2    (9999,'JOEL',50);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> DELETE DEPT WHERE DEPTNO=50;
DELETE DEPT WHERE DEPTNO=50
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.EMP_DEPT_FK) violated
 - child record found

I cannot delete this department, because the department is not empty. Fortunately :!:

Let’s redefine the constraint with a DELETE CASCADE clause


SQL> alter table emp drop constraint emp_dept_fk;

Table altered.

SQL> alter table emp add constraint emp_dept_fk
  2  foreign key (deptno) references dept(deptno)
  3  on delete cascade;

Table altered.

SQL> DELETE DEPT WHERE DEPTNO=50;

1 row deleted.

SQL> select * from emp where ename='JOEL';

no rows selected

Note the line 1 row deleted. This is evil :evil: I have deleted a department, and there were employees in it, but I got no error, no warning and no feedback about the DELETE EMP.

Instead of improving the data quality, the ON DELETE CASCADE foreign key constraint here silently deleted rows. Joel will once phone you and ask why he has been deleted…

There is one more clause of the foreign key which sets the refering column to null


SQL> INSERT INTO DEPT(deptno,dname) VALUES
  2    (60,'RESTAURANT');

1 row created.

SQL> INSERT INTO EMP(EMPNO,ENAME,DEPTNO) VALUES
  2    (9998,'MARC',60);

1 row created.

SQL> alter table emp drop constraint emp_dept_fk;

Table altered.

SQL> alter table emp add constraint emp_dept_fk
  2  foreign key (deptno) references dept(deptno)
  3  on delete set null;

Table altered.

SQL> DELETE DEPT WHERE DEPTNO=60;

1 row deleted.

SQL> select * from emp where ename='MARC';

     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      9998 MARC

Marc has no department, because his department has been deleted. Again, no feedback, no warning, no error.

Instead of improving the data quality, the ON DELETE SET NULL foreign key constraint here silently updated rows columns to NULL. Marc will wonder why he get no invitation to the department meetings.

What could be worse???

Triggers of course! Triggers not only removes rows in child tables, but triggers can also do very weird things, like updating another table, changing the values you are trying to insert, outputing a message, etc.

Also triggers are programmed by your colleagues, so they must be full of bugs :twisted:

You cannot imagine the number of problems that are caused by triggers and revealed only when tracing.

I once had something like

SQL> CREATE INDEX I ON T(X);

P07431B processed

Well, after enabling the trace, I discover one trigger fired on any ddl and the trigger was doing nothing else than this distracting dbms_output for “debugging” purpose. Guess google and metalink for the message did not help much…

errorlogging in 11g

This is a very neat feature in 11g.

I have a script called foo.sql


create table t(x number primary key);
insert into t(x) values (1);
insert into t(x) values (2);
insert into t(x) values (2);
insert into t(x) values (3);
commit;

It is eyes-popping that this script will return an error, but which one?

Let’s errorlog !


SQL>set errorl on
SQL> @foo

Table created.

1 row created.

1 row created.

insert into t(x) values (2)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C004200) violated

1 row created.

Commit complete.

SQL> set errorl off
SQL> select timestamp,script,statement,message from sperrorlog;
TIMESTAMP  SCRIPT  STATEMENT
---------- ------- ---------------------------
MESSAGE
---------------------------------------------------------
11:18:56   foo.sql insert into t(x) values (2)
ORA-00001: unique constraint (SCOTT.SYS_C004200) violated

There is also a huge bonus :-D

You can use it with 9i and 10g databases too! Only the client must be 11g. To download the 11g client only, go to Oracle E-Delivery Website

Even small, this is one of my favorite new features!

the password is not longer displayed in dba_users.password in 11g

By reading Pete Finnigan’s Oracle security weblog today, I discovered that the password is no longer displayed in DBA_USERS in 11g.


select username,password 
from dba_users 
where username='SCOTT';
USERNAME PASSWORD
-------- ------------------------------
SCOTT

select name,password 
from sys.user$ 
where name='SCOTT';
NAME  PASSWORD
----- ------------------------------
SCOTT F894844C34402B67

on the one hand, it is good for the security.

On the other hand, it is a huge change which is not documented (I immediately sent comments to the Security and Reference book authors) and it will make a lot of script failing (scripts that use to change the password to log in and change it back to the original value afterwards).

Protecting the hash is extremely important, check your scripts for 11g compatibility!

keep dense_rank with multiple column

create table t(
  deptno number, 
  firstname varchar2(10), 
  lastname varchar2(10), 
  hiredate date);

insert into t values (
  10,'Jo','Smith',date '2001-01-01');

insert into t values (
  10,'Jack','River',date '2002-02-02');

to get the latest hiredate per department

select deptno, 
  max(hiredate) hiredate
from t 
group by deptno;

    DEPTNO HIREDATE 
---------- ---------
        10 02-FEB-02

if you want to get the name of the employee at that date, you could by mistake believe the following works


select deptno, 
  max(firstname) keep (dense_rank last 
    order by hiredate) firstname,
  max(lastname) keep (dense_rank last 
    order by hiredate) lastname,
  max(hiredate) hiredate
from t
group by deptno;

    DEPTNO FIRSTNAME  LASTNAME   HIREDATE 
---------- ---------- ---------- ---------
        10 Jack       River      02-FEB-02

This will produce wrong result if hiredate is not unique


insert into t values (10,'Bob','Zhong', date '2002-02-02');
select deptno, 
  max(firstname) keep (dense_rank last 
    order by hiredate) firstname,
  max(lastname) keep (dense_rank last 
    order by hiredate) lastname,
  max(hiredate) hiredate
from t
group by deptno;

    DEPTNO FIRSTNAME  LASTNAME   HIREDATE 
---------- ---------- ---------- ---------
        10 Jack       Zhong      02-FEB-02

of course there is Jack Zhong.

To get a consistent record, it is possible to add all the columns in the order by


select deptno, 
  max(firstname) keep (dense_rank last 
    order by hiredate,firstname,lastname) firstname,
  max(lastname) keep (dense_rank last 
    order by hiredate,firstname,lastname) lastname,
  max(hiredate) hiredate
from t
group by deptno;

    DEPTNO FIRSTNAME  LASTNAME   HIREDATE 
---------- ---------- ---------- ---------
        10 Jack       River      02-FEB-02

get Nth column of a table

I answered this question twice, once on otn forums and once on developpez.net

Here is the latest to get the third column of emp

select
column_name as name,
extractvalue(column_value,’/ROW/’||column_name) as value
from table(xmlsequence(cursor(select * from emp))),
user_tab_columns
where COLUMN_ID=3 and table_name=’EMP’
;


NAME VALUE     
---- ----------
JOB  CLERK     
JOB  SALESMAN  
JOB  SALESMAN  
JOB  MANAGER   
JOB  SALESMAN  
JOB  MANAGER   
JOB  MANAGER   
JOB  ANALYST   
JOB  PRESIDENT 
JOB  SALESMAN  
JOB  CLERK     
JOB  CLERK     
JOB  ANALYST   
JOB  CLERK     

probably useless, but fun ;)

How to compare schema

If you have receive ddl statements from your developer and you want to check if it matches the current state of the development database, because the developer have done a lot of change in a quick and undocumented manner, what are your options?

I found this handy feature in Toad :
1) I create my objects on a separate database with the ddl I received from development
2) I compare the schema they use with the schema I created in Toad
–> Database –> Compare –> Schema
I select the options I want:
–> functions, indexes, packages, procedures, triggers, tables, view
I select the Reference and Comparison connections/schemas. Then I click compare
3) I receive the result
(only) 29 differences
4) the real bonus, I receive a script to update the live data according to the script I received. Undocumented change should never happen, so I do some communication with the developers


drop index foo;
drop table bar;
alter table gaz drop column bop;
alter table gaz modify (quux null);

this is not going to be blind-executable, some change are simply impossible to implement, but for my little test, I was happy to discover that function

I have been using ERwin for this purpose before, but the version I have (4.1) is very buggy and does not support a lot of syntaxes (ex: deferred constraints, create view v as select cast(1 as number(1)) x from dual, etc…). Also ERwin can compare only with the current model, so no direct comparison between 2 database schema.

how to spell 999999999 ?


begin
  dbms_output.put_line(
    to_char(
      timestamp '9999-12-31 23:59:59.999999999',
      'FF9SP'));
end;
/ 
NINE HUNDRED NINETY-NINE MILLION NINE HUNDRED NINETY-NINE 
THOUSAND NINE HUNDRED NINETY-NINE

Unfortunately, I could not get this in sql/10.2.0.2


select 
  to_char(
    timestamp '9999-12-31 23:59:59.999999999',
    'FF9SP') X
from
  dual;
ORA-01877: string is too long for internal buffer

Well, since the string is too long, let’s try with a LONGER string :twisted:


select 
  regexp_substr(
    to_char(
      timestamp '9999-12-31 23:59:59.999999999',
'FF9SP/FMDAY MONTH DDTHSP YYYYSP A.D. HH24SP MISP SSSP')
    ,'[^/]+')X
from
  dual;

X                                                                                          
---------------------------------------------------------
----------------------------------
NINE HUNDRED NINETY-NINE MILLION NINE HUNDRED NINETY-NINE 
THOUSAND NINE HUNDRED NINETY-NINE