How to retrieve view definition?

set long 500000000
set arraysize 1
set maxdata 50000

>select text from user_viewswhere view_name = 'Whatever';

If your dont set above parameters, text comes truncated.

How to make Oracle Table Read only?
(For complete details refer: http://www.dizwell.com/prod/node/60)

• You could lock the table in exclusive mode. But the lock disappears, and DML resumes, when the locking session terminates.

connect / as sysdbalock table scott.emp in exclusive mode;
connect scott/tiger
select sum(sal) from emp;
SUM(SAL)
----------29025
update emp set sal=sal*2;
[session hangs]

• You could simply revoke DML privileges on a table from all users, but risk forgetting one or two, or risk the possibility of a user hacking into the database with higher privileges.

create or replace function lockme
(object_schema in varchar2, object_name in varchar2)
return varchar2 is predicate varchar2(20);
begin predicate := '1=2';
return predicate;
end;
/
exec dbms_rls.add_policy('SCOTT','EMP','LCKPOL',-'SCOTT','LOCKME',’INSERT, UPDATE, DELETE’)

This creates a function which returns a never-true predicate (that is, 1 never equals 2), and uses Oracle’s Virtual Private Database functionality to attach a policy to the table which causes the function to fire and to append that predicate to whatever DML-style SQL is issued against the EMP table. Selects should be fine, however:
SYS is exempt from Virtual Private Database policies. Your table really isn’t made read-only by this technique -merely protected from casual DML issued by ordinary users. Which might genuinely be all that you neded. But if the reason you wanted to make the table read-only was to prevent any modification of this table’s data, then this mechanism (clever and convenient as it is) fails that test. The DBA can always by-pass the mechanism.

• You could create a 'no DML' trigger, but risk the trigger being dropped or invalidated, and hence by-passed, by an ordinary user.

create or replace trigger lock_emp
before insert or update or delete on scott.emp
begin
raise_application_error(-20001, 'EMP is now Read-Only');
end;
/

• You could apply a VPD policy to the table, but that never applies to SYS

• You could make the table's tablespace read-only, and rely on auditing to let you know if an administrator tries to change that status (capturing the culprit after the event)

alter tablespace ro_tables read only;
alter tablespace ro_tables read write; (to restore)

• You could truly lock down the table by moving it to read-only tablespace and burning the data files involved onto physically read-only media. That is un-circumventable by anyone.

What is Mutating Table Errors?

Sometimes you may find that Oracle reports a "mutating table error" when your trigger executes. This happens when the trigger is querying or modifying a "mutating table", which is either the table whose modification activated the trigger, or a table that might need to be updated because of a foreign key constraint with a CASCADE policy. To avoid mutating table errors:

• A row-level trigger must not query or modify a mutating table. (Of course, NEW and OLD still can be accessed by the trigger.)
• A statement-level trigger must not query or modify a mutating table if the trigger is fired as the result of a CASCADE delete.

How to delete duplicate rows using analytic functions?

delete from emp
where rowid in
(select rid
from (select rowid rid, row_number() over
( partition by ENAME order by rowid ) rn from emp
)
where rn <> 1
);

This is much more fast and efficient way of removing duplicates compared to
standard approach.

Oracle SQL

How to find the nth largest salary?

There are many ways to do this out of many few possible are listed below:

1.
Select ename , sal , rank
from
( select x.ename , x.sal , rownum rank
from
( select e.ename, e.sal
from
emp e order by e.sal desc
) x
)
where rank = N

2.
select ename, salfrom (select ename, sal, rownum() (partition by ename order by sal desc) rank from emp)where rank = N

3.
Select * from emp where sal =(select min(sal) from emp A where &n >= (select count(*) from empwhere sal >= a.sal))

4.
SELECT * FROM (SELECT sal,ROWNUM RN FROM (Select distinct salfrom emp order by sal desc) WHERE ROWNUM < &n )WHERE RN = &n-1;

5.
select t.salfrom(select sal, rank() over (order by sal desc) rnkfrom emp) twhere t.rnk =&n;

-----------------------------------------------------------------------------------------------