ORACLE GATHER STATS QUERY

We run gather stats query to imporve query performance on a table.

Below is the Gather stats query for multiple tables:

DECLARE
  sql_stmt VARCHAR2(500);
BEGIN
  FOR rec IN
  (SELECT DISTINCT INDEX_NAME FROM ALL_IND_STATISTICS WHERE OWNER='schema_name' AND STALE_STATS = 'YES'
  AND TABLE_NAME LIKE 'table_name'
  )
  LOOP
    sql_stmt := 'begin dbms_stats.gather_index_stats (ownname=>'||'''schema_name'''||',indname =>'||''''||rec.INDEX_NAME||'''' || ',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, degree=>64); end;';
    dbms_output.put_line (sql_stmt);
    execute immediate sql_stmt;
  END LOOP;
END;

Gather stats query for partitioned Table:

exec dbms_stats.gather_table_stats ('schema_name','Table_name',CASCADE=>TRUE,granularity=>'ALL',estimate_percent=>dbms_stats.auto_sample_size, degree=>15) ;

Gather stats query for normal Table:

execute dbms_stats.Gather_table_stats(ownname=>'schema_name',tabname=>'Table_name', estimate_percent=>dbms_stats.auto_sample_size, CASCADE=>true, degree=>15);  normal table

replace the table owner(schema_name) and table name in the above queries.
----------------------------------------------------------------------------------------------

PLSQL PRACTICE QUESTIONS

1.    Write a PL/SQL code to retrieve the employee name, join_date, and designation of an employee whose number is input by the user (employee table).

2.    Write a row trigger to insert the existing values of the employee table into a new table when the salary table is updated.

3.    Create a stored function that accepts 3 numbers and 2 operators (+/-*) and performs the calculations based on the input. Also write the code to call your function.

4.    Write a PL/SQL Program which raises a user defined exception if the input date is Friday?

Hint: char(DATE,’DY’) returns the DAY NAME

5.    Write a PL/SQL Program which can handle 3 different predefined oracle exceptions?

6.    Write a PL/SQL cursor program to display all employee names and their salary from employee table by using % not found attributes?

7.    Create a package which consists of procedure and functions and having one procedure called in another procedure? also write the code to call your function and procedure from package?

8.    Write After insert PL/ SQL Trigger which uses all the conditional Predicates (ex: Updating etc..,)

9.    Create a view on Employee and works table and update the view using instead of trigger?

10.    Plsql program to reverse a string with out using string function?

11.    How Can we execute DDL statements in PLSQL? Give an example procedure which creates a table?

12.    What are the different types of  loops in PLSQL? Write a program to print odd numbers from 1000 to 500?

13.    Write a PLSQL Program to increase the salary by 10% for top 5 employees and 5% for the remaining employees?

ORACLE SQL SCRIPT TO GENERATE ALTER SCRIPT TO MODIFY THE DATATYPES OF EXISTING COLUMNS

In the below script CHAR_USED stores the value 'B','C' which means BYTE OR CHAR.
we can modify the script as per our need.

SELECT 'ALTER TABLE '||TABLE_NAME||' MODIFY ('||COLUMN_NAME||' '||DATA_TYPE||'('||CHAR_LENGTH||' CHAR));' AL, TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHAR_LENGTH,CHAR_USED FROM ALL_TAB_COLS WHERE TABLE_NAME LIKE '%'-- ENTER TABLE NAME
AND DATA_TYPE IN('VARCHAR2','CHAR') AND CHAR_USED='B';

The output would be like below

ALTER TABLE WC__SETUP_DS MODIFY (TYPE VARCHAR2(50 CHAR));
ALTER TABLE WC__SETUP_DS MODIFY (NAME VARCHAR2(40 CHAR));

ORACLE PL/SQL QUERY TO SEARCH FOR A PARTICULAR VALUE IN ALL THE DATABASE TABLES AND COLUMNS

SET SERVEROUTPUT ON;

DECLARE
    match_count   INTEGER;

BEGIN
    FOR i IN (SELECT table_name, column_name
                FROM all_tab_columns
               WHERE table_name LIKE 'XX%')
    LOOP
        BEGIN

            EXECUTE IMMEDIATE
                   'SELECT COUNT(*) FROM '
                || i.table_name
                || ' WHERE TO_CHAR('
                || i.column_name
                || ') LIKE :1'
                INTO match_count
                USING 'ENTER VALUE TO BE SEARCHED HERE';
        EXCEPTION
            WHEN OTHERS

            THEN
                DBMS_OUTPUT.put_line (i.table_name || '~' || i.column_name);
        END;


        IF match_count > 0
        THEN
            DBMS_OUTPUT.put_line (
                i.table_name || ' ' || i.column_name || ' ' || match_count);
        END IF;
    END LOOP;
END;
/

TYPES OF PL/SQL BLOCKS

There are 2  different types of plsql blocks, Anonymous and Named Block.

•      Anonymous block are Subprogramss. 
•      Syntax of Anonymous block

                Declare
                 -- define variables here
                 -- declare is optional
                Begin
                -- define the actual logic here
                 exception;
                End;
 
       Example

              Declare
              v_inum number:=10;
              v_outnum number;
              Begin
              v_outnum=v_inum*10;
              dbms_output.put_line(v_outnum);
              end; 

•     Subprograms are named PL/SQL blocks that can have parameters.Functions and procedure are subprograms.
•     Syntax : 

                Procedure name is/as
                 -- define variables here
                Begin
                -- define the actual logic here 
                 exception;
                End;

              
                Function  name is/as
                 Return Datatype
                 -- define variables here
                Begin
                -- define the actual logic here 
                 Return Value;
                 exception;
                End;