Oracle Questions

 1) Please find this  link of some Oracle questions to practice yourself  .


2) Questions

ORA-01555 : snapshot too old

ORA-01555 : snapshot too old : rollback segment number 60 with name

 This is because rollback records needed by a reader for consistent read are overwritten by other writers
This error normally occurs if you are running a very big transaction (like activating millions of records in an odds) and the redo log is to small. Try to enlarge the redo log and run your transaction again.

sql> show parameter undo_retention

 Increase Undo Table space to Auto Extended on.

1- increase the setting of UNDO_RETENTION:

 to change: SQL> alter system set undo_retention=(new-value)

2- increase the tablespace UNDO that contain the ROLLBACK segments

alter tablespace UNDO add datafile '(name-of-the-new-datafile)' size (size-value)M;

Crazy Query

 Do you have any idea how this queries are working  ?


 select * from user_details d where exists (select 1/0 from dual x where d.first_name =x.dummy) ;

 select * from user_details d where exists (select 1/0 from dual ) ;

 Query  to get HR, MI,SS
 
select trunc((sysdate-created)*24) "Hr",trunc(mod( (sysdate-created)*24*60,60)) "Mi",
 trunc( mod( (sysdate-created)*24*60*60,60 )) "Sec"
from all_users where rownum < 10 ;

ORA-01450: maximum key length

create table ir_temp_feeload (s_category varchar2(800),s_cov varchar2(800),v_rate varchar2(10) ) ;

 alter table ir_temp_feeload  add constraint uk_ unique(s_category,s_cov) ;

ORA-01450: maximum key length (6398) exceeded

This error is due to the bad database design
This error occurs as key length of index exceeds 6398

key length = sum of all column lengths in index + number of columns + 2 (key length) + 6 (restricted ROWID) + 1 (ROWID field length)


Here  = (800+800) + (3)+2+6+1 = 1612

select * from nls_database_parameters where parameter like '%SET%';

AL32UTF8 is a multi-byte character set with up to 4 bytes per character

So the key value =  1612*4 = 6448

 Solution :
---------

As a general rule, indexing on very large columns (raw, long, clob) is rarely useful because the optimizer will almost always find a full-table scan cheaper than invoking an index on a long column value.

It is better to create a look up table that holds all the distinct values for the large text with a number id. Then you have a foreign key in the main table or use it as primary key

Orale Forms to Oracle ADF

Its high time for Oracle Form Developers to learn Oracle ADF or Oracle Apex if you want to stick in front end development else you can polish your PL/SQL & DBA Stuffs .

Those who know Java and have ample time and patience to study its better to go for Oracle ADF So u can be star in Oracle ADF & Java .

Those who doesn't have patience and time to study the you should at least try to learn Oracle Apex .These are my suggestions you can take it if its good else please excuse me.

Here I will try to give some idea about Oracle ADF

First we can check the task of a framework (or What is a Framework)

Oracle ADF Framework

Migrating Oracle Forms to ADF

Features both ADF & Forms has 

Is it Simple to create Web Page in ADF