Generate Sequence and Random Numbers Using SQL Query

Generate Sequence of Numbers from 1 to N using SQL Query

SELECT LEVEL AS ID 
FROM DUAL 
CONNECT BY LEVEL <= 20;

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 11.2.0.1.0 
Connected as HR@ORCLL

SQL> SELECT dbms_random.random FROM dual;
    RANDOM
----------
1624593079

SQL> 

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 11.2.0.1.0 
Connected as HR@ORCL
SQL> 
SQL> DECLARE
  2    PROCEDURE initialize ( in_val CHAR) IS
  3    BEGIN
  4    dbms_output.put_line('CHAR data type '||in_val);
  5    END initialize;
  6  
  7    PROCEDURE initialize ( in_val VARCHAR2) IS
  8    BEGIN
  9    dbms_output.put_line('VARCHAR2 data type '||in_val);
 10    END initialize;
 11  
 12  BEGIN
 13    initialize('AB');
 14  END;
 15  
 16  /
DECLARE
  PROCEDURE initialize ( in_val CHAR) IS
  BEGIN
  dbms_output.put_line('CHAR data type '||in_val);
  END initialize;

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

BEGIN
  initialize('AB');
END;
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.

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

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

DICTIONARY

SQL> DESC DICTIONARY;

Name       Type           Nullable Default Comments                  
---------- -------------- -------- ------- --------------------------
TABLE_NAME VARCHAR2(30)   Y                Name of the object        
COMMENTS   VARCHAR2(4000) Y                Text comment on the object

SQL> SELECT *
FROM dictionary
WHERE table_name = 'USER_OBJECTS';

USER_OBJECTS and ALL_OBJECTS

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)

ALL_OBJECTS:

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;

OBJECT_NAME         OBJECT_TYPE         CREATED     STATUS
------------------- ------------------- ----------- -------
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

USER_TABLES

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;

TABLE_NAME
------------------------------
EMPLOYEES_3
ERR$_BIGDATA
BIGDATA_MOVE
TAB_VIRTUAL_COL
SALES
EMPLOYEE_PHONE
USER_TAB_COLUMNS


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>

SQL> SELECT column_name, data_type, data_length,
  2  data_precision, data_scale, nullable
  3  FROM user_tab_columns
  4  WHERE table_name = 'EMPLOYEES';

COLUMN_NAME                    DATA_TYPE         DATA_LENGTH DATA_PRECISION DATA_SCALE NULLABLE
------------------------------ ----------------- ----------- -------------- ---------- --------
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
SQL>

USER_CONSTRAINTS and USER_CONS_COLUMNS

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>

SQL>

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>


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>

SQL>

SQL> SELECT constraint_name, column_name
  2  FROM user_cons_columns
  3  WHERE table_name = 'EMPLOYEES';

CONSTRAINT_NAME                COLUMN_NAME
------------------------------ --------------------------------------------------------------------------------
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

SQL>

Other Useful views:

SELECT * FROM DICTIONARY;
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:

SELECT COUNT(*) FROM All_Objects WHERE STATUS = 'INVALID';

Find what all objects are invalid :

SELECT * FROM All_Objects WHERE STATUS='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;

ALTER ... COMPILE
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:

ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;

DBMS_UTILITY
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.

UTL_RECOMP
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 : https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/u_recomp.htm

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.

Oracle PLSQL - Difference Between Parameter Modes

Parameter Modes  in PLSQL procedures and functions are IN , OUT, IN OUT. The differences are as follows.

Oracle PLSQL - Difference between procedure and function

Oracle - How Oracle SQL Statement is processed

Stages of Processing SQL Statement:

SQL statements pass through several stages during their processing:

Parsing
Binding
Executing

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.

Parsing

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.

Binding

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.

Execution

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.