Monday, October 17, 2016

Generate Sequence and Random Numbers Using SQL Query

Generate Sequence of Numbers from 1 to N using SQL Query


After certain numbers generation, you will run out of your memory saying that ORA-30009: Not enough memory for CONNECT BY operation. Use with the clause and create the cartesian product as shown below.

WITH more_data AS (
  SELECT /*+ MATERIALIZE */ level AS id
  FROM   dual
  CONNECT BY level <= 100
SELECT rownum AS id
FROM   more_data, more_data, more_data
WHERE  rownum <= 1000000;

Generate a random number using SQL Query

Connected to Oracle Database 11g Enterprise Edition Release 
Connected as HR@ORCLL

SQL> SELECT dbms_random.random FROM dual;


Sunday, October 16, 2016

When Compiler Catches Overloading Errors

When Compiler Catches Overloading Errors

I have a procedure INITIALIZE which accepts CHAR as IN parameter , another procedure INITIALIZE accepts VARCHAR2 as IN parameter, Now I'm calling INITIALIZE procedure with IN parameter value as 'AB'. Which procedure will execute?

In fact , the compiler throws PLS-00307: too many declarations of 'INITIALIZE' match this call. See the below demonstration .

Connected to Oracle Database 11g Enterprise Edition Release 
Connected as HR@ORCL
  2    PROCEDURE initialize ( in_val CHAR) IS
  3    BEGIN
  4    dbms_output.put_line('CHAR data type '||in_val);
  5    END initialize;
  7    PROCEDURE initialize ( in_val VARCHAR2) IS
  8    BEGIN
  9    dbms_output.put_line('VARCHAR2 data type '||in_val);
 10    END initialize;
 12  BEGIN
 13    initialize('AB');
 14  END;
 16  /
  PROCEDURE initialize ( in_val CHAR) IS
  dbms_output.put_line('CHAR data type '||in_val);
  END initialize;

  PROCEDURE initialize ( in_val VARCHAR2) IS
  dbms_output.put_line('VARCHAR2 data type '||in_val);
  END initialize;

ORA-06550: line 13, column 3:
PLS-00307: too many declarations of 'INITIALIZE' match this call
ORA-06550: line 13, column 3:
PL/SQL: Statement ignored

The above procedure program throws error PLS-00307, because :

"The PL/SQL compiler catches overloading errors as soon as it can determine that it will be unable to tell which subprogram was invoked. When subprograms have identical headings, the compiler catches the overloading error when you try to compile the subprograms themselves (if they are local) or when you try to compile the package specification that declares them (if they are packaged); otherwise, it catches the error when you try to compile an ambiguous invocation of a subprogram."

Let's correct the above program by changing the synonymous procedures to different procedures, i m changing the CHAR datatype to NUMBER in the first procedure. 

The anonymous block compiles successfully and executes.

  2    PROCEDURE initialize ( in_val NUMBER) IS
  3    BEGIN
  4    dbms_output.put_line('NUMBER data type '||in_val);
  5    END initialize;
  7    PROCEDURE initialize ( in_val VARCHAR2) IS
  8    BEGIN
  9    dbms_output.put_line('VARCHAR2 data type '||in_val);
 10    END initialize;
 12  BEGIN
 13    initialize('AB');
 14  END;
 15  /
VARCHAR2 data type AB
PL/SQL procedure successfully completed

Saturday, October 15, 2016

Oracle Data Dictionary

Data Dictionary tables/Views contain information about the database and its objects. These are automatically created and maintained by oracle server.

You use SQL statements to access the data dictionary. Because the data dictionary is read-only,you can issue only queries against its tables and views.You can query the dictionary views that are based on the dictionary tables to find information such as:
  • Definitions of all schema objects in the database (tables, views, indexes, synonyms, sequences, procedures, functions, packages, triggers, and so on)
  • Default values for columns
  • Integrity constraint information
  • Names of Oracle users
  • Privileges and roles that each user has been granted
  • Other general database information

  • Oracle Data Dictionary has two parts :
    1 Base Tables
    2 User Accessible Views

    Base tables can only read and written by Oracle Server. Views summarize and display the information stored in the base tables of data dictionary.
    The Oracle user SYS owns all base tables and user-accessible views of the data dictionary. No Oracle user should ever alter (UPDATE, DELETE, or INSERT) any rows or schema objects contained in the SYS schema, because such activity can compromise data integrity.

    View are categorized into 4 types.
    USER_: These views contain data from your schema or what you own
    ALL_: These views contain data from your schema and what all you can access from other schemas
    DBA_: These views contains data from the whole database
    V$ : Dynamic performance views

    Name       Type           Nullable Default Comments                  
    ---------- -------------- -------- ------- --------------------------
    TABLE_NAME VARCHAR2(30)   Y                Name of the object        
    COMMENTS   VARCHAR2(4000) Y                Text comment on the object
    FROM dictionary
    WHERE table_name = 'USER_OBJECTS';


    Query USER_OBJECTS to see all the objects that you own.
  • Using USER_OBJECTS, you can obtain a listing of all object names and types in your schema, plus the following information:
    -Date created
    -Date of last modification
    -Status (valid or invalid)


  • Query ALL_OBJECTS to see all the objects to which you have access.
    SQL> SELECT object_name, object_type, created, status
      2  FROM user_objects
      3  ORDER BY object_type;
    ------------------- ------------------- ----------- -------
    CHANNELS_DIM        DIMENSION           7/26/2016 1 VALID
    TIMES_DIM           DIMENSION           7/26/2016 1 VALID
    PRODUCTS_DIM        DIMENSION           7/26/2016 1 VALID
    CUSTOMERS_DIM       DIMENSION           7/26/2016 1 VALID
    PROMOTIONS_DIM      DIMENSION           7/26/2016 1 VALID
    CUSTOMERS_PK        INDEX               7/26/2016 1 VALID
    COUNTRIES_PK        INDEX               7/26/2016 1 VALID
    SALES_PROD_BIX      INDEX               7/26/2016 1 VALID
    SALES_CUST_BIX      INDEX               7/26/2016 1 VALID

    SQL> DESC user_tables
    Name                      Type         Nullable Default Comments                                                                                       
    ------------------------- ------------ -------- ------- ------------------------------------------------------------------------------------------------
    TABLE_NAME                VARCHAR2(30)                  Name of the table                                                                             
    TABLESPACE_NAME           VARCHAR2(30) Y                Name of the tablespace containing the table                                                   
    CLUSTER_NAME              VARCHAR2(30) Y                Name of the cluster, if any, to which the table belongs                                       
    IOT_NAME                  VARCHAR2(30) Y                Name of the index-only table, if any, to which the overflow or mapping table entry belongs     
    STATUS                    VARCHAR2(8)  Y                Status of the table will be UNUSABLE if a previous DROP TABLE operation failed,VALID otherwise
    SQL> SELECT table_name
      2  FROM user_tables;
    SQL> DESCRIBE user_tab_columns
    Name                 Type          Nullable Default Comments                                                          
    -------------------- ------------- -------- ------- --------------------------------------------------------------------
    TABLE_NAME           VARCHAR2(30)                   Table, view or cluster name                                       
    COLUMN_NAME          VARCHAR2(30)                   Column name                                                       
    DATA_TYPE            VARCHAR2(106) Y                Datatype of the column                                            
    DATA_TYPE_MOD        VARCHAR2(3)   Y                Datatype modifier of the column                                  
    DATA_TYPE_OWNER      VARCHAR2(30)  Y                Owner of the datatype of the column                              
    DATA_LENGTH          NUMBER                         Length of the column in bytes                                     
    DATA_PRECISION       NUMBER        Y                Length: decimal digits (NUMBER) or binary digits (FLOAT)          
    DATA_SCALE           NUMBER        Y                Digits to right of decimal point in a number                      
    NULLABLE             VARCHAR2(1)   Y                Does column allow NULL values?                                     
    COLUMN_ID            NUMBER        Y                Sequence number of the column as created                          
    DEFAULT_LENGTH       NUMBER        Y                Length of default value for the column                            
    DATA_DEFAULT         LONG          Y                Default value for the column                                      
    NUM_DISTINCT         NUMBER        Y                The number of distinct values in the column                      
    LOW_VALUE            RAW(32)       Y                The low value in the column                                       
    HIGH_VALUE           RAW(32)       Y                The high value in the column                                       
    SQL> SELECT column_name, data_type, data_length,
      2  data_precision, data_scale, nullable
      3  FROM user_tab_columns
      4  WHERE table_name = 'EMPLOYEES';
    ------------------------------ ----------------- ----------- -------------- ---------- --------
    EMPLOYEE_ID                    NUMBER                     22              6          0 N
    FIRST_NAME                     VARCHAR2                   20                           Y
    LAST_NAME                      VARCHAR2                   25                           N
    EMAIL                          VARCHAR2                   25                           N
    PHONE_NUMBER                   VARCHAR2                   20                           Y
    HIRE_DATE                      DATE                        7                           N
    JOB_ID                         VARCHAR2                   10                           N
    SALARY                         NUMBER                     22              8          2 Y
    COMMISSION_PCT                 NUMBER                     22              2          2 Y
    MANAGER_ID                     NUMBER                     22              6          0 Y
    DEPARTMENT_ID                  NUMBER                     22              4          0 Y
    11 rows selected

  • USER_CONSTRAINTS describes the constraint definitions on your tables.
  • USER_CONS_COLUMNS describes columns that are owned by you and that are specified in constraints.
    SQL> DESCRIBE user_constraints
    Name              Type         Nullable Default Comments                                                                 
    ----------------- ------------ -------- ------- ---------------------------------------------------------------------------
    OWNER             VARCHAR2(30) Y                Owner of the table                                                       
    CONSTRAINT_NAME   VARCHAR2(30)                  Name associated with constraint definition                               
    CONSTRAINT_TYPE   VARCHAR2(1)  Y                Type of constraint definition                                             
    TABLE_NAME        VARCHAR2(30)                  Name associated with table with constraint definition                     
    SEARCH_CONDITION  LONG         Y                Text of search condition for table check                                 
    R_OWNER           VARCHAR2(30) Y                Owner of table used in referential constraint                             
    R_CONSTRAINT_NAME VARCHAR2(30) Y                Name of unique constraint definition for referenced table                 
    DELETE_RULE       VARCHAR2(9)  Y                The delete rule for a referential constraint                             
    STATUS            VARCHAR2(8)  Y                Enforcement status of constraint -  ENABLED or DISABLED                   
    DEFERRABLE        VARCHAR2(14) Y                Is the constraint deferrable - DEFERRABLE or NOT DEFERRABLE               
    DEFERRED          VARCHAR2(9)  Y                Is the constraint deferred by default -  DEFERRED or IMMEDIATE           
    VALIDATED         VARCHAR2(13) Y                Was this constraint system validated? -  VALIDATED or NOT VALIDATED       
    GENERATED         VARCHAR2(14) Y                Was the constraint name system generated? -  GENERATED NAME or USER NAME 
    BAD               VARCHAR2(3)  Y                Creating this constraint should give ORA-02436.  Rewrite it before 2000 AD.
    RELY              VARCHAR2(4)  Y                If set, this flag will be used in optimizer                               
    LAST_CHANGE       DATE         Y                The date when this column was last enabled or disabled                   
    INDEX_OWNER       VARCHAR2(30) Y                The owner of the index used by the constraint                             
    INDEX_NAME        VARCHAR2(30) Y                The index used by the constraint                                         
    INVALID           VARCHAR2(7)  Y                                                                                         
    VIEW_RELATED      VARCHAR2(14) Y                                                                                         
    SQL> SELECT constraint_name, constraint_type,
      2  search_condition, r_constraint_name,
      3  delete_rule, status
      4  FROM user_constraints
      5  WHERE table_name = 'EMPLOYEES';
    CONSTRAINT_NAME                CONSTRAINT_TYPE SEARCH_CONDITION                                                                 R_CONSTRAINT_NAME              DELETE_RULE STATUS
    ------------------------------ --------------- -------------------------------------------------------------------------------- ------------------------------ ----------- --------
    EMP_MANAGER_FK                 R                                                                                                EMP_EMP_ID_PK                  NO ACTION   ENABLED
    EMP_JOB_FK                     R                                                                                                JOB_ID_PK                      NO ACTION   ENABLED
    EMP_DEPT_FK                    R                                                                                                DEPT_ID_PK                     NO ACTION   ENABLED
    EMP_LAST_NAME_NN               C               "LAST_NAME" IS NOT NULL                                                                                                     ENABLED
    EMP_EMAIL_NN                   C               "EMAIL" IS NOT NULL                                                                                                         ENABLED
    EMP_HIRE_DATE_NN               C               "HIRE_DATE" IS NOT NULL                                                                                                     ENABLED
    EMP_JOB_NN                     C               "JOB_ID" IS NOT NULL                                                                                                        ENABLED
    EMP_SALARY_MIN                 C               salary > 0                                                                                                                  ENABLED
    EMP_EMAIL_UK                   U                                                                                                                                           ENABLED
    EMP_EMP_ID_PK                  P                                                                                                                                           ENABLED
    10 rows selected
    SQL> DESCRIBE user_cons_columns
    Name            Type           Nullable Default Comments                                                                                       
    --------------- -------------- -------- ------- ------------------------------------------------------------------------------------------------
    OWNER           VARCHAR2(30)                    Owner of the constraint definition                                                             
    CONSTRAINT_NAME VARCHAR2(30)                    Name associated with the constraint definition                                                 
    TABLE_NAME      VARCHAR2(30)                    Name associated with table with constraint definition                                         
    COLUMN_NAME     VARCHAR2(4000) Y                Name associated with column or attribute of object column specified in the constraint definition
    POSITION        NUMBER         Y                Original position of column or attribute in definition                                         
    SQL> SELECT constraint_name, column_name
      2  FROM user_cons_columns
      3  WHERE table_name = 'EMPLOYEES';
    ------------------------------ --------------------------------------------------------------------------------
    EMP_MANAGER_FK                 MANAGER_ID
    EMP_JOB_FK                     JOB_ID
    EMP_DEPT_FK                    DEPARTMENT_ID
    EMP_LAST_NAME_NN               LAST_NAME
    EMP_EMAIL_NN                   EMAIL
    EMP_HIRE_DATE_NN               HIRE_DATE
    EMP_JOB_NN                     JOB_ID
    EMP_SALARY_MIN                 SALARY
    EMP_EMAIL_UK                   EMAIL
    EMP_EMP_ID_PK                  EMPLOYEE_ID
    10 rows selected

    Other Useful views:
    SELECT * FROM User_Users;
    SELECT * FROM all_users;
    SELECT * FROM User_Sys_Privs;
    SELECT * FROM User_Role_Privs;
    SELECT * FROM user_catalog;
    SELECT * FROM user_tablespaces;
    SELECT * FROM User_Free_Space;
    SELECT * FROM User_Ts_Quotas;
    SELECT * FROM User_Errors
    SELECT * FROM user_source
    SELECT * FROM user_indexes
    SELECT * FROM User_Procedures
    SELECT * FROM User_Triggers
    SELECT * FROM User_Indexes
    SELECT * FROM User_Views
    SELECT * FROM User_Synonyms
    SELECT * FROM user_tab_partitions
    SELECT * FROM user_part_indexes;
    SELECT * FROM User_Part_Tables;
    SELECT * FROM User_All_Tables;
    SELECT * FROM User_Tab_Privs;
    SELECT * FROM User_Tab_Privs_Made;
    SELECT * FROM user_tab_privs_recd;
    SELECT * FROM user_password_limits;
    SELECT * FROM user_scheduler_jobs;
    SELECT * FROM User_Jobs;
    SELECT * FROM user_datapump_jobs;
    SELECT * FROM User_Constraints;
    SELECT * FROM user_dependencies;
    SELECT * FROM User_Db_Links;
    SELECT * FROM User_Extents;
    SELECT * FROM User_Segments;
    SELECT * FROM User_External_Tables;
    SELECT * FROM User_Flashback_Archive;
    SELECT * FROM user_identifiers;
    SELECT * FROM User_Java_Classes;
    SELECT * FROM user_mviews;
    SELECT * FROM user_nested_tables;
    SELECT * FROM user_recyclebin;
    SELECT * FROM user_tab_statistics;
    SELECT * FROM user_unused_col_tabs;
    SELECT * FROM User_Updatable_Columns;
    SELECT * FROM user_varrays;
    SELECT * FROM user_xml_column_names;
    SELECT * FROM user_xml_indexes;
    SELECT * FROM all_Xml_Schemas;
    SELECT * FROM User_Xml_Views;
    SELECT * FROM v$instance;
    SELECT * FROM v$version;
    SELECT * FROM v$parameter;
    SELECT * FROM v$spparameter;
    SELECT * FROM v$session;
    SELECT * FROM v$session_blockers;
    SELECT * FROM v$session_longops;
    SELECT * FROM v$session_wait;
    SELECT * FROM v$session_event;
    SELECT * FROM v$sql
    SELECT * FROM v$sql_plan
    SELECT * FROM v$sql_shared_memory;
    SELECT * FROM v$sql_workarea;
    SELECT * FROM v$lock;
    SELECT * FROM v$library_cache_memory;
    SELECT * FROM v$license;
    SELECT * FROM v$listener_network;
    SELECT * FROM v$sess_io;
    SELECT * FROM v$memory_target_advice;
    SELECT * FROM v$sga;
    SELECT * FROM v$reserved_words;
    SELECT * FROM v$flash_recovery_area_usage;
    SELECT * FROM v$process
    SELECT * FROM v$db_cache_advice;
    SELECT * FROM v$datapump_session;
    SELECT * FROM v$nls_parameters;
    SELECT * FROM v$undostat;
    SELECT * FROM v$database;

    Invalid objects in Oracle Database

    Invalid Objects in Oracle Database :

    We always wonder why there are invalids in my schema/database. What has been changed/caused to increase invalids.The reason could be , there is a change in the dependent objects structure. There could be a change in the package which has many referencing/dependent objects.Or there are some errors in your   package/ procedure/ functions by which objects become invalid.

    Generally, these invalids are most common in the development database, development would be done my many developers and they would be doing changes to may of the objects which cause invalids. Also in the database where there is a major upgrade or patch deployed, in production databases where there is release happened.

    How do I deal with these? Here is what i generally do , when i find invalids.

    Find number of the invalid objects:
    Find what all objects are invalid :
    To check what object got changed recently:
    SELECT * FROM All_Objects ORDER BY last_ddl_time DESC;
    with above the query you will get the recently changed objects.
    To check the dependent objects of an object:
    SELECT * FROM All_Dependencies WHERE NAME ='MOVE_DATA';
    To check for the objects with errors:
    SELECT * FROM All_Errors;
    when you now the objects with errors, the most important thing is resolve them. The dependent object will get compiled only when the referencing object is in valid status.

    How to compile an individual/single object manually:
    I have a huge number of invalid objects in my schema, How do I compile ?
    EXEC DBMS_UTILITY.COMPILE_SCHEMA(schema => 'HR',compile_all => FALSE);
    compile_all parameter :when false, compiles only invalid objects in a schema; when true, compiles the entire schema.

    An alternative way of compiling objects in a schema level, database level.

    Schema Level
    EXEC UTL_RECOMP.recomp_serial('HR');
    EXEC UTL_RECOMP.recomp_parallel(8, 'HR');

    Database Level
    EXEC UTL_RECOMP.recomp_serial();
    EXEC UTL_RECOMP.recomp_parallel(8);

    RECOMP_SERIAL : Compile one by one object.
    RECOMP_PARALLEL :Recompile all objects using  parallel threads.

    Operational Notes as defined in documentation :

    This package uses the job queue for parallel recompilation.
    This package must be run using SQL*PLUS.
    You must be connected AS SYSDBA to run this script.
    This package expects the following packages to have been created with VALID status:
    STANDARD (standard.sql)
    DBMS_STANDARD (dbmsstdx.sql)
    DBMS_JOB (dbmsjob.sql)
    DBMS_RANDOM (dbmsrand.sql)
    There should be no other DDL on the database while running entries in this package. Not following this recommendation may lead to deadlocks.

    Tuesday, October 4, 2016

    Saturday, October 1, 2016

    Oracle - How Oracle SQL Statement is processed

    Stages of Processing SQL Statement:

    SQL statements pass through several stages during their processing:


    Oracle uses cursors, private SQL areas, to store parsed statements and other information relating to the statements it’s currently processing. Oracle automatically opens a cursor for all SQL statements.


    During the parsing stage, Oracle does several things to check your SQL statements:
    • Oracle checks that your statements are syntactically correct.
    The server consults the data dictionary to check whether the tables and column specifications are correct.
    • Oracle ensures that you have the privileges to perform the actions you are attempting through your SQL statements.
    • Oracle prepares  the execution plan for the statement, which involves selecting the best access methods for the objects in the statement.

    After it checks the privileges, Oracle assigns a number called the SQL hash value to the SQL statement for identification purposes. If the SQL hash value already exists in memory, Oracle will look for an existing execution plan for the statement, which details the ideal way it should access the various database objects, among other things. 

    Soft Parse:
    If the execution plan exists, Oracle will proceed straight to the actual execution of the statement using that execution plan.This is called a soft parse, and it is the preferred technique for statement processing. Because it uses previously formed execution plans, soft parsing is fast and efficient.

    Hard Parse:
    The opposite of a soft parse is a hard parse, and Oracle has to perform this type of parse when it doesn’t find the SQL hash value in memory for the statement it wants to execute. Hard parses are tough on system memory and other resources. Oracle has to create a fresh execution plan, which means that it has to evaluate the numerous possibilities and choose the best plan from among them. During this process, Oracle needs to access the library cache and dictionary cache numerous times to check the data dictionary, and each time it accesses these commonly used areas, Oracle needs to use latches, which are low-level serialization control mechanisms, to protect shared data structures in the SGA. Thus, hard parsing contributes to an increase in latch contention.

    Any time there’s a severe contention for resources during statement processing, the execution time will increase. Remember that too many hard parses will lead to a fragmentation of the shared pool, making the contention worse. 

    After the parsing operation is complete, Oracle allots a shared SQL area for the statement. Other users can access this parsed version as long as it is retained in memory.


    During the binding stage, Oracle retrieves the values for the variables used in the parsing stage. Note that the variables are expanded to literal values only after the parsing stage is over.


    Once Oracle completes the parsing and binding, it executes the statement. Note that Oracle will first check whether there is a parsed representation of the statement in memory already. If there is, the user can execute this parsed representation directly, without going through the parsing process all over again.

    It’s during the execution phase that the database reads the data from the disk into the memory buffers (if it doesn’t find the data there already). The database also takes out all the necessary locks and ensures that it logs any changes made during the SQL execution.

    After the execution of the SQL statement, Oracle automatically closes the cursors.