Useful Oracle Websites

1.    http://www.orafaq.org/               - The Underground Oracle Frequently-Asked Questions List (especially the Oracle Database Administration pages)

2.     http://www.oracle.com/               - Oracle Corporation's Web Site.

3.     http://www.orafans.com/             - ORACLE User Forum and Fans Club (especially the ORACLE Technical Papers pages).

4.     http://exforsys.com           - Very good site for Various Tutorials on All technologies and Career guidance

5.    http://www.oraclefans.com/         - Info from various sites on their pages, such as machine-based FAQ's from faqs.org in their Links page.

6.    http://www.fors.com/orasupp      - More articles by Oracle Worldwide Customer Support. Includes some of the older Oracle Technical Bulletins.

7.    http://www.dbresources.com/       - Scanning the web for Oracle articles or news?  Check out this compilation.

8.    http://my.ittoolbox.com/       - Oracle Forums and Articles

9.    http://www.oracletuning.com/      - Scripts and articles for DBA's.

10. http://think-forward.com/     - Adelante, Ltd, Computer Consultants, with scripts and tips, including a UNIX to VMS Translation Table.

11. http://searchoracle.com/     - Tips, scripts, news, white papers, ask the experts, discussion forums - lots of neat stuff!

12. http://asktom.oracle.com/    - For any oracle question very useful site

13. http://www.psoug.org/links.html   - Various Oracle Links

14. http://the-big-o.port5.com/   - Oracle Tips, Tricks, Hints, and How-To's, including Oracle Forms articles and general database articles.

15. http://education.oracle.com/ - Oracle Corporation's Education Site.

16. http://www.psoug.org/library.html          - Oracle SQL and PLSQL Syntax

17. http://www.oracle-books.com/oracle      - Rhubarb's Oracle Site (from which came the Oracle Technical Bulletins).

18. http://bijoos.com/oracle/index.htm           - Biju's Oracle Page - Scripts, Utilities, Source code generators, etc.

19. http://www.szofi.hu/index_link.html#Oracle       - Link Exchange - tons of links, Oracle and other programming languages and operating systems as well.

20. http://www.akadia.com/html/ora_scripts.html    - Various Oracle Scripts

21. http://www.vb-bookmark.com/vbOracle.html     - Oracle Bookmark with Oracle and Perl sites containing articles, tutorials, tips, tricks, guides, and samples.

22. http://techonthenet.com/oracle/index.php  - Oracle, Unix, Linux etc.

23. http://www.dbspecialists.com/presentations.html - Various useful presentations

24. http://oracle.com/pls/db92/db92.homepage         - Oracle 9i Documentation

25. http://www.tusc.com/oracle/download/categories.html  - Years of PowerPoint presentations, including lots of Oracle 9i stuff.

26. http://www.geekinterview.com/question_details/12376  -

27. http://www.orafaq.com/scripts/index.htm#GENPLSQL   - For various useful SQL and PLSQL Scripts

28. http://www.osborne.com/downloads/downloads.shtml#J-L       - For All the Codes of Java as well as other Languages

29. http://www.akhilamatrix.com/website_designing_basics.htm    - Website Designing

30. http://home.clara.net/dwotton/dba/oracle_extn_rtn.htm - Calling External routines from PL/SQL.

31. http://www.fortunecity.com/skyscraper/oracle/699/orahtml/index.html         - A bunch of articles (tuning, etc.) for Oracle DBA's (another Rhubarb collection).

32. http://otn.oracle.com/pub/articles/calish_file_commands.html    - Oracle Articles

33. http://www.akadia.com/services/ora_interpreting_explain_plan.html   - Nice articles about Performance Tuning

34. http://forums.oracle.com/forums/category.jspa?categoryID=18 - Oracle Forum

35. http://oracle-base.com/articles/Misc/EfficientSQLStatements.php                   -

36. http://4shared.com/dir/1493877/e168fc62/Oracle_E-Books.html                    -       

37. http://www.oracle.com/technology/oramag/oracle/06-nov/o66plsql.html                 - On Object Types in Collections)

38. http://www.intelligentedu.com/blogs/post/Best_New_Training_Sites/456/Fast-          - Very good link for information about PLSQL and PLSQL Tuning

39. http://searchoracle.techtarget.com/general/0,295582,sid41_gci1087688,00.html         - RDBMS

40. http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96533/toc.htm         - Oracle Performance Tuning Guide

41. http://www.w3schools.com/xml/default.asp       - XML Tutorials        

42. http://sql-plsql.blogspot.com/2007/03/sql-introduction.html        - Basics of SQL and PLSQL

43. http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_103a.htm#2065826 - Various Clause of a SELECT statement.

44. http://www.enterprisedb.com/documentation/index.html           - SQL and PLSQL Enterprise Book

45. http://www.oracle-developer.net/display.php?id=320                - Flashback version query in oracle 10g

46. http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/dynamic.htm#sthref1551          (Native Dynamic SQL)

A.  SQL Plus Commands

1.    http://igor.gold.ac.uk/oracle/9i/server.920/a90842/ch13.htm#1011230        - SQL Commands

Do You Know-Small Queries

1.    Execute a Procedure Dynamically ( Procedure Name is passed as a Parameter to another procedure)

EXECUTE IMMEDIATE 'BEGIN '||procedure_namel||'; END;';

2.    Get count of a particular alphabet in a String

SELECT LENGTH(UPPER('Arun Kumar Gupta')) - LENGTH(REPLACE(UPPER( 'Arun  Kumar Gupta'),'A')) "Count of A"

  FROM DUAL;

3.    Get prime numbers between 2 numbers using only SQL

  SELECT l prime_number

    FROM (SELECT LEVEL l

            FROM DUAL CONNECT BY LEVEL <= 100

         )

        ,(SELECT LEVEL m

            FROM DUAL CONNECT BY LEVEL <= 100

         )

   WHERE m        <= l

GROUP BY l

  HAVING COUNT(CASE l/m WHEN TRUNC(L/M)

                        THEN 'Y'

                END

              )   = 2

ORDER BY l;

--Some Efficient methods.
--Method-1

WITH t AS
(SELECT LEVEL val, sqrt(level) sqrt_val
   FROM DUAL CONNECT BY LEVEL <= 10000
)            
SELECT prime_number
  FROM (
          SELECT t1.val prime_number
            FROM t          t1
                ,t          t2
           WHERE t2.val     <= t1.sqrt_val
             AND MOD(t1.val,t2.val) = 0
       )
GROUP BY prime_number
HAVING COUNT(1) = 1

ORDER BY prime_number; 

--Method-2

WITH t AS
(SELECT LEVEL val, sqrt(level) sqrt_val
   FROM DUAL CONNECT BY LEVEL <= 10000
)            
  SELECT t1.val     prime_number
    FROM t          t1
        ,t          t2
   WHERE t2.val     <= t1.sqrt_val
GROUP BY t1.val
  HAVING COUNT(CASE WHEN t1.val/t2.val = TRUNC(t1.val/t2.val)
                    THEN 1
                END
              )   = 1
ORDER BY t1.val

; 

4.    Get the product (multiplication) of the values in a column

SELECT EXP(SUM(LN(column_name)))

  FROM table_name;

5.    Get list Of days in a Month

SET PAGESIZE 100

SET FEEDBACK OFF

SET TIMING OFF

SELECT TO_CHAR((SYSDATE + 16 - ROWNUM),'DAY       DD-MON-YYYY')  AS "Day             Date"

  FROM all_objects

 WHERE ROWNUM <= 31;

6.    Get the sequential numbers between 2 numbers

 SELECT n + &initial_value

   FROM ( SELECT ROWNUM -1 n

            FROM DUAL      

CONNECT BY LEVEL <= (&final_value  - &initial_value) + 1

        )

7.    Select nth Highest Salary without Using ROWNUM or ROWID

SELECT EMPNO,ENAME,SAL

  FROM EMP     A

 WHERE &N       = (SELECT COUNT(DISTINCT SAL)

                    FROM EMP        B

                   WHERE B.SAL      >= A.SAL

                 );

Here N=1,2,3,4,5.......

Locked Object Queries

CREATE OR REPLACE VIEW locked_tables_v

/******************************************************************************

*     Query to identify the sessions which have locked a particular table     *

******************************************************************************/

AS

SELECT /*+ ORDERED */

       s.sid

      ,s.serial#

      ,do.object_name

      ,do.object_type

      ,do.owner

      ,s.status

      ,s.osuser

      ,s.machine

  FROM gv$session          s

      ,gv$locked_object    lo

      ,dba_objects         do

 WHERE s.sid               = lo.session_id

   AND lo.object_id        = do.object_id ;

CREATE OR REPLACE VIEW locked_objects_v

/******************************************************************************

*  Query to identify the sessions which have locked a particular Procedure,   *

*  Package or Concurrent Program.                                             *

******************************************************************************/

AS

SELECT s.sid

      ,s.serial#

      ,ddl.name

      ,ddl.type

      ,ddl.owner

      ,s.status

      ,s.osuser

      ,s.machine

  FROM dba_ddl_locks      ddl

      ,gv$session         s

 WHERE ddl.session_id     = s.sid ;

CREATE OR REPLACE VIEW locked_programs_v

/******************************************************************************

*  Query to identify the sessions which have locked a particular Concurrent   *

*  Program for known Request ID.                                              *

******************************************************************************/

AS

SELECT /*+ ORDERED */

       s.sid

      ,s.serial#

      ,s.status

      ,s.osuser

      ,s.machine

      ,fcr.request_id

      ,fcr.oracle_process_id

      ,fcr.status_code

      ,fcr.phase_code

      ,p.spid

  FROM fnd_concurrent_requests    fcr

      ,fnd_concurrent_processes   fcp

      ,gv$process                    p

      ,gv$session                    s

 WHERE fcr.controlling_manager       = fcp.concurrent_process_id

   AND fcp.oracle_process_id         = p.pid

   AND p.addr                        = s.paddr ;

  CREATE OR REPLACE VIEW sql_text_v

/******************************************************************************

*  Query to find out the DML a independent procedure or Concurrent Program is *

*  performing for known sid.                                                  *

******************************************************************************/

  AS

  SELECT s.sid

        ,s.serial#

        ,st.piece

        ,st.sql_text

        ,s.status

        ,s.osuser

        ,s.machine

    FROM gv$session        s

        ,gv$sqltext        st

   WHERE s.sql_hash_value  = st.hash_value(+)

ORDER BY st.piece ;