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

ORACLE ARCHITECTURE

Normally, one database is serviced by one instance, but in the case of clustering there can be many instances running for a single database.
The System Identifier, or SID, is the name given to the database instance.

There are two major components to the software: the TNS Listener (Transparent Network Substrate ) and the relational database management system (RDBMS) itself.
The TNS Listener is the hub of all Oracle communications.
When a database instance is brought up or started, it registers with the TNS Listener.
When a client wishes to access the database, it first connects to the Listener and the Listener then redirects the client to the database server.When the RDBMS wants to launch an external procedure, it connects first to the Listener, which in turns launches a program called extproc. One exception to this is the external jobs launched by the databases job scheduler. Here the RDBMS connects directly to the external job server.

 The Listener is comprised of two binaries: (1) tnslsnr which is the Listener itself and (2) the Listener Control Utility
(lsnrctl) which is used to administer the Listener on the server or remotely.

 Transparent Network Substrate (TNS) is the network protocol used by Oracle for connectivity to Oracle Databases.

The lsnrctl program is the mechanism for starting and stopping the listener process (tnslsnr). 
tnslnsr starts and reads the listener.ora and sqlnet.ora files for configuration information, such as
port numbers and database service names.

The tnslnsr processes starts with the process owner of the lsnrctl program,


A database instance describes all the processes and memory structures that provide access to the database.
There are two kinds of processes - background and shadow, or server. Shadow or server processes serve client requests. In other words, when a client connects
to the TNS Listener and requests access to database services, the Listener hands them off to a server process. This server process takes SQL queries and
executes them on behalf of the client. Background processes exist to support this. There are a number of different background processes each with a different role,
including the Database Writer, the Log Writer, the Archiver, the System Monitor, and the Process Monitor, among others.

When a local user attempts to connect to Oracle on Windows, it does so over named pipes. Four threads are created in the main server process to handle the
communication between the client and the server. These four threads have a Discretionary Access Control List (DACL) that gives the user permission to open the thread.

In Unix platforms each of these background processes is a separate running process, as in an operating system process. On Windows, theyre all
wrapped up into one larger process - namely, oracle.exe. There is a special area of memory that is mapped among all processes on Unix called the System Global Area
(SGA). The SGA is implemented as a memory-mapped file (section) and contains information pertaining to the instance and the database. It also contains an area known
as the shared pool, which contains structures shared among all users, such as table definitions and the like.

Materialized view

A materialized view is a stored summary containing precomputed results. Materialized views allow for significantly faster data warehouse query processing. The Oracle database server automatically rewrites queries to use the summary data, rather than retrieving data from detail tables by doing expensive joins and aggregate operations. This query rewrite facility is totally transparent to the application, which is not aware of the existence of the materialized view.

The DBA's first step in creating materialized views is to define dimensions. These represent the hierarchies that are present in the real world; for instance, multiple months make up a quarter, multiple districts make up a region, etc. The CREATE MATERIALIZED VIEW statement is used to create a materialized view. This statement includes a sub query, typically a join or a data aggregation (GROUP BY), the results of which comprise the materialized view.

A materialized view is maintained by a refresh process. The refresh process can be done automatically when a commit is done on a detail table, or it can be controlled manually by the DBA. A refresh is specified as complete or incremental. A complete refresh truncates existing data, then repopulates the summary with new data from the detail tables. An incremental refresh updates only changed data.


To create a materialized view in a users own schema, the user must have the  CREATE MATERIALIZED VIEW, CREATE TABLE, CREATE INDEX, and CREATE VIEW system privileges. To create a materialized view in another user's schema, a user must have the CREATE ANY MATERIALIZED VIEW system privilege.