passing variables in sqlplus scripts

The script snippet you provided is an SQL*Plus script used to interactively accept parameters and execute a stored procedure with those parameters. Here’s a detailed breakdown of each component and how it works:

Explanation of the Script:

1. SET VERIFY OFF

   • This command disables the verification of substitution variables in SQLPlus. When VERIFY is set to OFF, SQLPlus does not display the text of substituted variables, which can make the output cleaner.

2. ACCEPT par1 prompt "ENTER PARAMETER #1: "

   • The ACCEPT command is used to prompt the user for input. In this case, it asks the user to enter a value  par1 and displays the prompt message "ENTER PARAMETER #1: ". The value entered by the user is stored in the variable par1.

3. ACCEPT par2 prompt "ENTER PARAMETER #2: "

   • Similarly, this command prompts the user to enter a value for par2, with the prompt message "ENTER PARAMETER #2: ". The value entered by the user is stored in the variable par2.

4. execute pkg_TEST_VARIABLES.TEST_PASS_VARIABLES ( &&par1, &&par2);

   • This line executes a stored procedure TEST_PASS_VARIABLES from the package pkg_TEST_VARIABLES. The && notation is used to reference the variables par1 and par2 which were previously set by the ACCEPT commands. The double & notation ensures that the variables are resolved at runtime.

Putting It All Together:

When you run this SQL*Plus script, the following sequence of actions occurs:

1. Prompting for Input:

   • The script first prompts you to enter two parameters: par1 and par2.

2. Executing the Procedure:

   • After you enter the parameters, the script executes the procedure TEST_PASS_VARIABLES from the package pkg_TEST_VARIABLES, passing the entered parameters to the procedure.

Example Execution:

Assuming you have a stored procedure defined as follows:

sql

CREATE OR REPLACE PACKAGE pkg_TEST_VARIABLES AS
  PROCEDURE TEST_PASS_VARIABLES(p1 IN VARCHAR2, p2 IN VARCHAR2);
END pkg_TEST_VARIABLES;
/

CREATE OR REPLACE PACKAGE BODY pkg_TEST_VARIABLES AS
  PROCEDURE TEST_PASS_VARIABLES(p1 IN VARCHAR2, p2 IN VARCHAR2) IS
  BEGIN
    -- Procedure logic here
    DBMS_OUTPUT.PUT_LINE('Parameter 1: ' || p1);
    DBMS_OUTPUT.PUT_LINE('Parameter 2: ' || p2);
  END TEST_PASS_VARIABLES;
END pkg_TEST_VARIABLES;
/

Running the script will prompt for input:

ENTER PARAMETER #1: value1
ENTER PARAMETER #2: value2

After providing the values, the procedure will be executed, and you will see the output:

Parameter 1: value1
Parameter 2: value2

Summary:

• SET VERIFY OFF: Clean output by turning off variable verification.
• ACCEPT: Prompts for user input and stores it in variables.
• execute: Executes a stored procedure with the provided parameters.

This script is useful for testing stored procedures interactively or running scripts that require user inputs.

select grants on tables to users or roles

select grants on tables to users or roles

SELECT
  OWNER,
  GRANTEE,
  GRANTOR,
  TABLE_NAME,
  PRIVILEGE
FROM
  DBA_TAB_PRIVS
WHERE
  table_name   = 'TABLENAME'
AND PRIVILEGE IN ('DELETE','INSERT','SELECT','UPDATE')
AND GRANTEE   IN ('ROLE1','ROLE2')
ORDER BY
  1,2;

select grants on table to a user thru role or direct priv

select Grantee,'Granted Through Role' as Grant_Type, role, table_name
from role_tab_privs rtp, dba_role_privs drp
where rtp.role = drp.granted_role
and table_name = 'TABLENAME'
union
select Grantee,'Direct Grant' as Grant_type, null as role, table_name
from dba_tab_privs
where table_name = 'TABLENAME' ;

Online table redefination

Table and Dependent Objects Creation:

Drop Existing Objects:

Before creating new objects, ensure no conflicts with existing ones:

sql

DROP PROCEDURE get_description;
DROP VIEW redef_tab_v;
DROP SEQUENCE redef_tab_seq;
DROP TABLE redef_tab PURGE;

Create Table:

Define the table redef_tab with a primary key:

sql

CREATE TABLE redef_tab (
  id           NUMBER,
  description  VARCHAR2(50),
  CONSTRAINT redef_tab_pk PRIMARY KEY (id)
);

Create View:

Create a view that selects all columns from redef_tab:

sql

CREATE VIEW redef_tab_v AS
SELECT * FROM redef_tab;

Create Sequence:

Define a sequence to generate unique IDs:

sql

CREATE SEQUENCE redef_tab_seq;

Create Procedure:

Create a procedure to retrieve the description based on an ID:

sql

CREATE OR REPLACE PROCEDURE get_description (
  p_id          IN  redef_tab.id%TYPE,
  p_description OUT redef_tab.description%TYPE) AS
BEGIN
  SELECT description
  INTO   p_description
  FROM   redef_tab
  WHERE  id = p_id;
END;
/

Create Trigger:

Create a trigger to automatically assign a new ID from the sequence before inserting it into redef_tab:

sql

CREATE OR REPLACE TRIGGER redef_tab_bir
BEFORE INSERT ON redef_tab
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
  :new.id := redef_tab_seq.NEXTVAL;
END;
/

Verify Object Creation:

Check the status of the created objects:

sql

COLUMN object_name FORMAT A20
SELECT object_name, object_type, status FROM user_objects ORDER BY object_name;

Expected Output:

OBJECT_NAME          OBJECT_TYPE         STATUS
-------------------- ------------------- -------
GET_DESCRIPTION      PROCEDURE           VALID
REDEF_TAB            TABLE               VALID
REDEF_TAB_BIR        TRIGGER             VALID
REDEF_TAB_PK         INDEX               VALID
REDEF_TAB_SEQ        SEQUENCE            VALID
REDEF_TAB_V          VIEW                VALID

2. Online Table Redefinition:

Check Table Redefinition Feasibility:

Verify if the table redef_tab can be redefined:

sql

EXEC DBMS_REDEFINITION.can_redef_table('ATOORPU', 'REDEF_TAB');

Create Interim Table:

Create a new table redef_tab2 with the same structure as redef_tab, but initially empty:

sql

CREATE TABLE redef_tab2 AS
SELECT *
FROM   redef_tab WHERE 1=2;

Start Redefinition:

Begin the online redefinition process:

sql

EXEC DBMS_REDEFINITION.start_redef_table('ATOORPU', 'REDEF_TAB', 'REDEF_TAB2');

Synchronize Interim Table (Optional):

Synchronize redef_tab2 with any interim data:

sql

EXEC DBMS_REDEFINITION.sync_interim_table('ATOORPU', 'REDEF_TAB', 'REDEF_TAB2');

Add New Primary Key Constraint:

Add the primary key constraint to redef_tab2:

sql

ALTER TABLE redef_tab2 ADD (CONSTRAINT redef_tab2_pk PRIMARY KEY (id));

Complete Redefinition:

Finish the redefinition process:

sql

EXEC DBMS_REDEFINITION.finish_redef_table('ATOORPU', 'REDEF_TAB', 'REDEF_TAB2');

Drop Original Table:

Drop the original table, which has been renamed to REDEF_TAB2:

sql

DROP TABLE redef_tab2;

3. Verify the Updated Schema:

Check the status of the updated objects:

sql

COLUMN object_name FORMAT A20
SELECT object_name, object_type, status FROM user_objects ORDER BY object_name;

Expected Output:

OBJECT_NAME          OBJECT_TYPE         STATUS
-------------------- ------------------- -------
GET_DESCRIPTION      PROCEDURE           VALID
REDEF_TAB            TABLE               VALID
REDEF_TAB2_PK        INDEX               VALID
REDEF_TAB_SEQ        SEQUENCE            VALID
REDEF_TAB_V          VIEW                VALID

Notes:

• Procedure and View Validity: The GET_DESCRIPTION procedure and REDEF_TAB_V view remains valid after redefinition because they reference the table, which is still present under a new name (REDEF_TAB).
• Trigger Loss: The REDEF_TAB_BIR trigger is dropped because it was associated with the original table, which was renamed and subsequently dropped.

This comprehensive setup and redefinition process helps manage schema changes with minimal downtime, ensuring that your database schema evolves smoothly while maintaining data integrity.

ORA-00020: maximum number of processes exceeded

ORA-00020: maximum number of processes

This error occurs when your total numbers of sessions connecting to oracle database has exceeded the max limit set in parameter file. Simplest way to overcome this error is to reset the max sessions value to a greater value than existing.Here is how to do it
 



oracle@LINUX201:[~] $ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 13 10:20:26 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-00020: maximum number of processes (500) exceeded



oracle@LINUX201:[~] $ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 13 10:23:42 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-00020: maximum number of processes (500) exceeded

Enter user-name:

Disconnected from ORACLE

I wasn't able get into the oracle database to kill some database session. So I tried to kill few sessions on OS to make my way into DB.

oracle@LINUX201:[~] $ ps -ef|grep oracle
.
.
.
.
.
.
.

oracle   64373     1  0 Jun08 ?        00:00:03 oracleQPDEV (LOCAL=NO)
oracle   64540     1  0 Jun08 ?        00:00:00 oracleQPDEV (LOCAL=NO)
oracle   64554     1  0 Jun08 ?        00:00:01 oracleQPDEV (LOCAL=NO)
oracle   64633     1  0 Jun08 ?        00:00:03 oracleQPDEV (LOCAL=NO)
oracle   64637     1  0 Jun08 ?        00:00:00 oracleQPDEV (LOCAL=NO)
.
.
.
oracle   65186     1  0 Jun08 ?        00:00:04 oracleQPDEV (LOCAL=NO)
oracle   65190     1  0 Jun08 ?        00:00:00 oracleQPDEV (LOCAL=NO)
oracle   65192     1  0 Jun08 ?        00:00:01 oracleQPDEV (LOCAL=NO)
oracle   65202     1  0 Jun08 ?        00:00:00 oracleQPDEV (LOCAL=NO)
oracle   65206     1  0 Jun08 ?        00:00:02 oracleQPDEV (LOCAL=NO)
root     65407 65381  0 May16 pts/2    00:00:00 sudo -u oracle -i
oracle   65408 65407  0 May16 pts/2    00:00:00 -bash
oracle   65458 65408  0 May16 pts/2    00:00:00 sqlplus
oracle   65459 65458  0 May16 ?        00:00:00 oracleQPDEV (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   65518     1  0 Jun08 ?        00:00:00 oracleQPDEV (LOCAL=NO)
oracle   65520     1  0 Jun08 ?        00:00:02 oracleQPDEV (LOCAL=NO)
oracle   65534     1  0 Jun08 ?        00:00:00 oracleQPDEV (LOCAL=NO)

oracle@LINUX201:[~] $ kill -9 64785
oracle@LINUX201:[~] $ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 13 10:26:25 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-00020: maximum number of processes (500) exceeded


Enter user-name: ^C

Killing few processes on Linux :

oracle@LINUX201:[~] $ kill -9 65192 65085 64785 64777 64655 64653 64637


oracle@LINUX201:[~] $ ps -ef|grep 65192 65085 64785 64777 64655 64653 64637

.
.
.
.
oracle   50258     1  0 Jun07 ?        00:00:04 oracleQPDEV (LOCAL=NO)
oracle   50264     1  0 Jun07 ?        00:00:03 oracleQPDEV (LOCAL=NO)
oracle   50268     1  0 Jun07 ?        00:00:02 oracleQPDEV (LOCAL=NO)
.
.
.
oracle   64554     1  0 Jun08 ?        00:00:01 oracleQPDEV (LOCAL=NO)
oracle   64633     1  0 Jun08 ?        00:00:03 oracleQPDEV (LOCAL=NO)
oracle   65186     1  0 Jun08 ?        00:00:04 oracleQPDEV (LOCAL=NO)
oracle   65190     1  0 Jun08 ?        00:00:00 oracleQPDEV (LOCAL=NO)
oracle   65202     1  0 Jun08 ?        00:00:00 oracleQPDEV (LOCAL=NO)
oracle   65206     1  0 Jun08 ?        00:00:02 oracleQPDEV (LOCAL=NO)
.
.
.

oracle@LINUX201:[~] $ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 13 10:30:07 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter process;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     1
cell_offload_processing              boolean     TRUE
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     1000
log_archive_max_processes            integer     4
processes                            integer     500
processor_group_name                 string


Now reset the max processes to a greater value:

SQL> alter system set processes=1200 scope=spfile;

System altered.

SQL>  show parameter process;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     1
cell_offload_processing              boolean     TRUE
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     1000
log_archive_max_processes            integer     4
processes                            integer     500
processor_group_name                 string
SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
QPDEV     READ WRITE

This will need a restart to take affect


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup;
ORACLE instance started.

Total System Global Area 3206836224 bytes
Fixed Size                  2257520 bytes
Variable Size            1275071888 bytes
Database Buffers         1912602624 bytes
Redo Buffers               16904192 bytes
Database mounted.
Database opened.

SQL> show parameter process

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     1
cell_offload_processing              boolean     TRUE
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     1000
log_archive_max_processes            integer     4
processes                            integer     1200
processor_group_name                 string

SQL>

Create Temporary Tables in Oracle

Global Temporary Tables in Oracle

Temporary tables are useful in applications where a result set is to be buffered, perhaps because it is constructed by running multiple DML operations. For example, consider the following:

A Web-based airlines reservations application allows a customer to create several optional itineraries. Each itinerary is represented by a row in a temporary table. The application updates the rows to reflect changes in the itineraries. When the customer decides which itinerary she wants to use, the application moves the row for that itinerary to a persistent table.

During the session, the itinerary data is private. At the end of the session, the optional itineraries are dropped.

This statement creates a temporary table that is transaction specific:

NOTE : Indexes can be created on temporary tables. They are also temporary and the data in the index has the same session or transaction scope as the data in the underlying table.

*********************************************************************************

HERE is an example to create a global temporary table with on commit DELETE ROWS :

*********************************************************************************

sql>  CREATE GLOBAL TEMPORARY TABLE admin_work_area

        (startdate DATE,

         enddate DATE,

         class CHAR(20))

      ON COMMIT DELETE ROWS;

      

      

    

sql>  insert into ADMIN_WORK_AREA values (sysdate,sysdate+ 1,'A');

1 row inserted.

sql> select * from ADMIN_WORK_AREA;

commit;

Commit complete.

sql> select * from ADMIN_WORK_AREA;

NOTE: records in this temp table will be deleted upon commit. This is equivalent to truncating table on commit.

*********************************************************************************

HERE is an example to create a global temporary table with on commit PRESERVE ROWS :

*********************************************************************************

sql>  CREATE GLOBAL TEMPORARY TABLE admin_work_area

        (startdate DATE,

         enddate DATE,

         class CHAR(20))

      ON COMMIT PRESERVE ROWS;

      

            

sql>  insert into ADMIN_WORK_AREA values (sysdate,sysdate+ 1,'A');

1 row inserted.

1 row inserted.

sql>  select * from ADMIN_WORK_AREA;

commit;

Commit complete.

sql>  select * from ADMIN_WORK_AREA;

NOW exit the session and login back and select the table.

sql>  select * from ADMIN_WORK_AREA;

table is empty

NOTE: records (rows) in this temp table will be deleted upon session exit only, as long as you are using same session you can see these rows. 

This is equivalent to truncating table on session exit.

Inserting Data into table with DML Error Logging (catching errors whiles inserting data into table)

Inserting Data with DML Error Logging:

When you load a table using an INSERT statement with subquery, if an error occurs, the statement is terminated and rolled back in its entirety. This can be wasteful of time and system resources. For such INSERT statements, you can avoid this situation by using the DML error logging feature.

To use DML error logging, you add a statement clause that specifies the name of an error logging table into which the database records errors encountered during DML operations. When you add this error logging clause to the INSERT statement, certain types of errors no longer terminate and roll back the statement. Instead, each error is logged and the statement continues. You then take corrective action on the erroneous rows at a later time.

DML error logging works with INSERT, UPDATE, MERGE, and DELETE statements. This section focuses on INSERT statements.

--------------------------------------------------------
--  DDL for Table ATEST1
--------------------------------------------------------

  CREATE TABLE "ATOORPU"."ATEST1"
   (    "ID" NUMBER constraint ATEST1_PK PRIMARY KEY,
    "TDATE" DATE,
    "AMOUNT" VARCHAR2(20 BYTE),
    "ORD_NO" NUMBER
   ) ;

--------------------------------------------------------
INSERT SOME VALUES
--------------------------------------------------------

Insert into ATEST1 (ID,TDATE,AMOUNT,ORD_NO) values (1,to_date('04-APR-16','DD-MON-RR'),null,300);
Insert into ATEST1 (ID,TDATE,AMOUNT,ORD_NO) values (2,to_date('04-APR-16','DD-MON-RR'),null,300);
Insert into ATEST1 (ID,TDATE,AMOUNT,ORD_NO) values (3,to_date('01-MAR-16','DD-MON-RR'),null,100);
Insert into ATEST1 (ID,TDATE,AMOUNT,ORD_NO) values (4,to_date('01-MAR-16','DD-MON-RR'),'100',200);

--------------------------------------------------------
CREATE ERROR LOG TABLE USING THE DBMS PACKAGE :
--------------------------------------------------------

EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('ATEST1', 'ERR_ATEST1');   -- ATEST1 source table and ERR_ATEST1 error log table


Error Logging Restrictions and Caveats

• Oracle Database logs the following errors during DML operations:
• Column values that are too large
• Constraint violations (NOT NULL, unique, referential, and check constraints)
• Errors raised during trigger execution
• Errors resulting from type conversion between a column in a subquery and the corresponding column of the table
• Partition mapping errors

Certain MERGE operation errors (ORA-30926: Unable to get a stable set of rows for MERGE operation.)

--------------------------------------------------------
-- This will generate some insert errors
--------------------------------------------------------

INSERT INTO ATEST1
  SELECT ID+3,TDATE,AMOUNT,ORD_NO
  FROM ATEST1
  WHERE id > 1
  LOG ERRORS INTO ERR_ATEST1 ('daily_load') REJECT LIMIT 9;

--- daily_load is TAG, REJECT LIMT will set the max errs before terminating insert statement.


Note:

If the statement exceeds the reject limit and rolls back, the error logging table retains the log entries recorded so far.

--------------------------------------------------------
-- This will generate some update errors
--------------------------------------------------------

update ATEST1 set ID=3 where ID>5 LOG ERRORS INTO ERR_ATEST1 ('daily_load') REJECT LIMIT 9;

--- daily_load is TAG, REJECT LIMT will set the max errs before terminating insert statement.


--------------------------------------------------------
LETS CHECK THE ERROR MESSAGES RECORDED:
--------------------------------------------------------

select * from ERR_ATEST1;

Using Index Hints in oracle

Hints :

Hints are used to give specific information that we know about our data and application, to Oracle. This further improves the performance of our system. There can be instances where the default optimizer may not be efficient for certain SQL statements. We can specify HINTS with the SQL statements, to improve the efficiency of those SQL statements. Hints should only be used as a last-resort if statistics were gathered and the query is still following a sub-optimal execution plan.

Example of the correct syntax for an index hint:

select /*+ index(TEST_IDX IDX_OS_USR) */ * from TEST_IDX;

If we alias the table (A in below case), you must use the alias in the index hint:

select /*+ index(A IDX_OS_USR) */ * from TEST_IDX A;

Note :

Oracle decides to use weather to use this hint or not, of oracle finds that it has faster execution plan without using hint it ignores it. You might think that an index may be helpfull and provide it as hint but oracle may still ignore it. In below case you can see hint being ignored.

CREATE INVISIBLE INDEX ON A TABLE

INVISIBLE INDEX:

Oracle 11g gives us ability to create indexes that can be marked as invisible. Invisible indexes are maintained like any other index, but they are ignored by the optimizer unless the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to TRUE at the instance or session level

CREATE AN INVISIBLE INDEX:

CREATE INDEX INV_IDX_OS_USR ON TEST_IDX (ID) INVISIBLE;

lets check the newly created index :

SQL> select OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,VISIBILITY from all_indexes where index_name='INV_IDX_OS_USR';

OWNER         INDEX_NAME            TABLE_OWNER         TABLE_NAME      VISIBILITY       
--------      -----------                                    ----------               -----------                  --------
ATOORPU       INV_IDX_OS_USR       ATOORPU            TEST_IDX               INVISIBLE 


USER CAN'T MAKE USE OF INVISIBLE INDEX UNTIL HE MAKES IT VISIBLE IN THAT SESSION LETS SEE IF WE CAN USE INVISIBLE INDEX WITH OUT ENABLING IT IN OPTIMIZER:

select /*index (TEST_IDX INV_IDX_OS_USR)*/ * from TEST_IDX where ID=284;

MAKING AN INDEX VISIBLE IN CURRENT SESSION:

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;
select /*index (TEST_IDX INV_IDX_OS_USR)*/ * from TEST_IDX where ID=284;

MAKING AN INDEX INVISIBLE IN CURRENT SESSION:

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE;

select /*index (TEST_IDX INV_IDX_OS_USR)*/ * from TEST_IDX where ID=284;

-->> you will not have to provide any hints to use index. I have provided hint just to make sure  oracle uses it.

select  * from TEST_IDX where ID=284;        -->> Same as above



MAKING AN INDEX INVISIBLE OR VISIBLE:


Indexes can be created as invisible by using the INVISIBLE keyword at the end, and their visibility can be managed using the ALTER INDEX command

TO MAKE AN EXISTING INDEX INVISIBLE USE BELOW SYNTAX: 

ALTER INDEX index_name INVISIBLE;

TO MAKE AN EXISTING INDEX VISIBLE USE BELOW SYNTAX: 

ALTER INDEX index_name VISIBLE;