Using UNIX environment variables in SQL Plus

Passing variables to a SQL script under UNIX enviroment.

Method 1

$ Variable1="Someting value"
sqlplus db_user/db_password@db_sid <<EOF
set serveroutput on
BEGIN
  dbms_output.put_line('${Variable1}');
END;
/
exit;
EOF



Method 2
Create a test file called test_script.sql

$ cat test_script.sql
set serveroutput on
DECLARE
  var1 varchar2(100):='&&1';
  var2 varchar2(100):='&&2';
BEGIN
  dbms_output.put_line(var1);
  dbms_output.put_line(var2);
END;
/
exit;

For the test purpose let’s create a UNIX script file which executes out sql script.
$ cat test_unix_script.sh
var1="Some text"
var2="Some other text”
sqlplus -s db_user/db_password@db_sid @test_script.sql $var1 $var2

Output from execution of  the script
$ test_str.sh
old   3:   l_var1 varchar2(100):='&&1';
new   3:   l_var1 varchar2(100):='Some text';
old   4:   l_var2 varchar2(100):='&&2';
new   4:   l_var2 varchar2(100):='Some other text';

Some text 
Some other text

Fix for Oracle12c - ORA-01792: maximum number of columns in a table or view is 1000

SYMPTOMS

It is possible to get an ORA-01792 "MAXIMUM NUMBER OF COLUMNS IN A TABLE OR VIEW IS 1000"  error even when the original query does not select that many columns  in Oracle 12.1.0.2.0 version.

SOLUTION

According to the My Oracle Support, ORA-01792 is caused due to an unpublished bug.
Oracle suggest to alter the query and/or view definitions to avoid the error. However in cases where the SQL cannot be adjusted then the checking can be disabled by hidden parameter "_fix_control"

The workaround is to set "_fix_control"='17376322:OFF'

On Session level
SQL> alter session set "_fix_control"='17376322:OFF';

OR at system level :

SQL> alter system set "_fix_control"='17376322:OFF';

OR

Apply Patch 19509982 if available for your DBVersion and Platform

To download the patch, please follow the steps below:
1) Go To MOS
2) Click on Patches & Updates
3) In the Patch Search Section, enter your patch number <19509982> in the Patch Name or Number field.
4) Click Search
5) Click the patch that meets your DbVersion and platform
6) Read any applicable notes before downloading, then click the download button.

Setting, Resetting/Unsetting Oracle DB Parameters

There are more than 250 documented and more than 900 undocumented parameters for Oracle.
You can query the documented parameters with the query below

select name,value,isdefault,isses_modifiable,issys_modifiable,
isinstance_modifiable,isdeprecated,
from v$parameter

You can query the list of undocumented parameters with the script  list of hidden parameters in oracle db

Also you can query v$parameter2 as the same way you query the v$parameter view to view the session specific parameter values.

The values from the columns isdefault,isses_modifiable,issys_modifiable,
isinstance_modifiable,isdeprecated are important.

isdefault = TRUE/FALSE –indicates if the value is the default value

isses_modifiable=TRUE/FALSE –indicates if the value can be modified by sessions

issys_modifiable = IMMEDIATE/DEFERRED/FALSE –indicates if the value can be modified by alter system command. Values represents when the changes takes effect. For immediate, no matter what you use (spfile or init file ) parameter change takes effect immediately.For deferred, parameter can be changed but change takes affect in subsequent sessions not for currently established sessions. For false it means parameter cannot be changed if you dont have a spfile.

isinstance_modifiable= true/false –this option is spesicif for RAC which has multiple instances for one database

If you want to view value of the parameter briefly you can also use show parameter command which searches the entered string with wildcard on both ends

SQL> show parameter pool

NAME TYPE VALUE
————————————
buffer_pool_keep string
buffer_pool_recycle string
global_context_pool_size string
java_pool_size big integer 0
large_pool_size big integer 0
olap_page_pool_size big integer 0
shared_pool_reserved_size big integer 7759462
shared_pool_size big integer 0
streams_pool_size big integer 0

As you see the output above all parameters which include “pool” string in it are listed briefly.

Setting Parameter for Instance

You can set the parameter values with the command below

Alter system set parameter=value <comment=’text’> <deferred>
<scope=memory|spfile|both> <sid=’sid|*’>

elements of the command;

scope =BOTH/SPFILE/MEMORY indicates the scope of the parameter setting. both is the default behaviour.

sid = SID/* if you are on RAC environment you can give SID or * for this element which indicate that the setting will effect only the given SID or all nodes in RAC configuration.

comment= You can comment the change for future lookups. This comment can be viewed from UPDATE_COMMENT column of v$parameter view.

deferred = Indicates that the parameter change takes place for subsequent sessions only (not currently established sessions, including the one making the change). This element must be used for the parameters whose ISSYS_MODIFIABLE column in v$parameter view is deferred.

When you want to change the parameter only for the running instance and not for the subsequent instance you can change it only in memory. ;

SQL> alter system set cpu_count=4 scope=MEMORY;
System altered.

When you want to change the parameter for subsequent instances only
SQL> alter system set cpu_count=4 scope=SPFILE;
System altered.

When you want to change the parameter for running and subsequent instances (if issys_modifiable is not false);

SQL> alter system set cpu_count=4 scope=BOTH;
System altered.

!!!!!NB: When using an init.ora parameter file, the default and only valid value is SCOPE=MEMORY. So you must manually edit your init.ora file to change the parameter for subsequent instances.

Setting Parameters for sessions

You can change the values of some parameters whose ISSES_MODIFIABLE column of v$parameter view is TRUE. Scope of the parameter will be the end of the session.

SQL> alter session set timed_statistics=false;
Session altered.

SQL> show parameter timed_statistics

NAME TYPE VALUE
———————————— ———– ——-
timed_statistics boolean FALSE

Resetting/Unsetting parameters

If you want to use the default value of the parameter you have changed. You must reset it.

alter system reset parameter <scope=memory|spfile|both> sid=’sid|*’

Elements are same as setting parameter. The only difference is “you must” give si value (for single instance use ‘*’).

SQL> alter system reset control_file_record_keep_time scope=spfile sid=’*’;
System altered.

!!!!!NB: Do not try to change the value of a parameter with ” or null to reset it to default

List of hidden parameters in Oracle DB

How to view list of hidden parameters?

Hidden parameters sometimes ease the life of  DBA but be carefull while using them. Oracle usually does not support the usage of hidden parameters without their knowledge and you can't even know the side effects of the parameter.
Here is the query you can use to view the values of hidden parameter.

Managing Initialization Parameters Using a Server Parameter File (SPFILE)

Initialization parameters for the Oracle Database have traditionally been stored in a text initialization parameter file(PFILE). For better manageability, you can choose to maintain initialization parameters in a binary server parameter file (SPFILE) that is persistent across database startup and shutdown.

Grants to execute AWR (Automatic Workload Repository) Report

Problem:  How to give access rights to certain user to run AWR reports.

How to give access to run awr reports from normal users.
Generally, we run AWR from some administrator accounts (usually from sys or system users).
In order to generate the AWR reports from normal user we need to grant the follow privileges to him.

Let's follow the following plan:
  1. Create user.
  2. Grant the advisor privilege.
  3. Grant execute permission on dbms_workload_repository package.
  4. Grant select any dictionary

Here here is the complete summary of the activity
===================================================================
SQL> create user awradmin identified by pass123;

User created.

SQL> grant connect,resource to awradmin;

Grant succeeded.

SQL> grant advisor to awradmin;

Grant succeeded.

SQL> grant execute on dbms_workload_repository to awradmin;

Grant succeeded.

SQL> grant select any dictionary to awradmin;

Grant succeeded.

SQL> conn awradmin/awradmin123
Connected.
SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL>


===================================================================
In some cases it may be not possible to grant all permissions so in that case you can use follow the plan described below.

GRANT SELECT ON SYS.V_$DATABASE TO awradmin;
GRANT SELECT ON SYS.V_$INSTANCE TO awradmin;
GRANT EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY TO awradmin;
GRANT SELECT ON SYS.DBA_HIST_DATABASE_INSTANCE TO awradmin;
GRANT SELECT ON SYS.DBA_HIST_SNAPSHOT TO awradmin;

SQL> GRANT SELECT ON SYS.V_$DATABASE TO awradmin;

Grant succeeded.

SQL> GRANT SELECT ON SYS.V_$INSTANCE TO awradmin;

Grant succeeded.

SQL> GRANT EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY TO awradmin;

Grant succeeded.

SQL> GRANT SELECT ON SYS.DBA_HIST_DATABASE_INSTANCE TO awradmin;

Grant succeeded.

SQL> GRANT SELECT ON SYS.DBA_HIST_SNAPSHOT TO awradmin;

Grant succeeded.

SQL>

=====================================================================
There is a fast way to do that :) You need to grant role CONNECT, SELECT_CATALOG_ROLE and OEM_MONITOR to user and that is all.

GRANT CONNECT TO awradmin;
GRANT SELECT_CATALOG_ROLE TO awradmin;
GRANT OEM_MONITOR TO awradmin;

The role "SELECT_CATALOG_ROLE" contains the privileges:
   SELECT ON DBA_HIST_SNAPSHOT
   SELECT ON DBA_HIST_DATABASE_INSTANCE
   SELCT ON V_$INSTANCE
   SELECT ON V_$DATABASE

The role "OEM_MONITOR" contains the privilege EXECUTE ON DBMS_WORKLOAD_REPOSITORY.


SQL> GRANT CONNECT TO awradmin;

Grant succeeded.

SQL> GRANT SELECT_CATALOG_ROLE TO awradmin;

Grant succeeded.

SQL> GRANT OEM_MONITOR TO awradmin;

Grant succeeded.

SQL>


Thank you, Enjoy ... :)

Invisible Indexes in Oracle Database

DESCRIPTION
Oracle 11g introduces a new feature for indexes, invisible indexes. That can be useful in several different situations. An invisible index is an index that is maintained by the database but ignored by the optimizer unless explicitly specified. The invisible index is an alternative to dropping or making an index unusable. This feature is also functional when certain modules of an application require a specific index without affecting the rest of the application.

SYNTAX
CREATE INDEX index_name ON table_name(column_name) INVISIBLE;
ALTER INDEX index_name INVISIBLE;
ALTER INDEX index_name VISIBLE;


EXAMPLES
The following script creates and populates a table, then creates an invisible index on it.

CREATE TABLE test_tab (id  NUMBER);
Table created.

BEGIN
  FOR i IN 1 .. 10000 LOOP
    INSERT INTO test_tab VALUES (i);
  END LOOP;
  COMMIT;
END;
/
PL/SQL procedure successfully completed.

CREATE INDEX test_idx ON test_tab(id) INVISIBLE;
Index created.

EXEC DBMS_STATS.gather_table_stats(USER, 'test_tab', cascade=> TRUE);
PL/SQL procedure successfully completed.

A query using the indexed column in the WHERE clause ignores the index and does a full table scan.

SET AUTOTRACE ON
SELECT * FROM test_tab WHERE id = 9999;

----------------------------------------------------------------------------------------
| Id  | Operation         | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |      1 |        |      1 |00:00:00.01 |      24 |
|*  1 |  TABLE ACCESS FULL| TEST_TAB |      1 |      1 |      1 |00:00:00.01 |      24 |
----------------------------------------------------------------------------------------

Setting the OPTIMIZER_USE_INVISIBLE_INDEXES parameter makes the index available to the optimizer.

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;
Session altered.

SELECT id FROM test_tab WHERE id = 9999;

---------------------------------------------------------------------------------------
| Id  | Operation        | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |      1 |        |      1 |00:00:00.01 |       3 |
|*  1 |  INDEX RANGE SCAN| TEST_IDX |      1 |      1 |      1 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------

Making the index visible means it is still available to the optimizer when the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is reset.

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE;
Session altered.

ALTER INDEX test_idx VISIBLE;
Index altered.

---------------------------------------------------------------------------------------
| Id  | Operation        | Name     | Starts | E-Rows | A-Rsows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |      1 |        |      1 |00:00:00.01 |       3 |
|*  1 |  INDEX RANGE SCAN| TEST_IDX |      1 |      1 |      1 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------

The current visibility status of an index is indicated by the VISIBILITY column of the [DBA|ALL|USER]_INDEXES views.

SELECT index_name, visibility FROM user_indexes WHERE index_name='TEST_IDX';

INDEX_NAME VISIBILITY
---------- --------------
TEST_IDX   VISIBLE

Resizing initial extent of the table

DESCRIPTION

There is no way to change the initial extent size of the table directly. There is a way to do this without dropping and recreating the table. It just needs to move the table with storage clause. It’s possible to move the table in other tablespace and back it again to the current one but this is not necessary. It’s just an option.

SYNTAX

ALTER TABLE [schema_name].[table_name] MOVE TABLESPACE [tablespace_name] STORAGE (INITIAL [number1] NEXT [number2] PCTINCREASE 0);

PARAMETERS or ARGUMENTS

schema_name - Name of the schema.

table_name - Name of the table which you want to change initial extent

number1 - Initial extent size in bytes. 

number2 - Size of the next extent which will be allocated to the object

NOTE

[number1] and [number2] can be represented with size_clause.

The size_clause lets you specify the amount of disk memory space. It can be a number of bytes, kilobytes (K), megabytes (M), gigabytes (G), terabytes (T). If you don’t specify any abbreviation the integer is considered as bytes.

Example: 1024; 64K; 10M ; 2G; 1T;

EXAMPLES

Tablespace name: def_tbs

Schema name      : def_schema

Table name         : test_table_1

Example 1:

ALTER TABLE def_schema.test_table_1

  MOVE TABLESPACE def_tbs

  STORAGE (INITIAL 64K NEXT 1M PCTINCREASE 0);

Example 2 :    Make the same like Example 1

ALTER TABLE def_schema.test_table_1

  MOVE TABLESPACE def_tbs

  STORAGE (INITIAL 65536 NEXT 1048576 PCTINCREASE 0);

MS Excel: ABS function

DESCRIPTION

The Microsoft Excel ABS function returns the absolute value of a number

SYNTAX

The syntax for the ABS function in Microsoft Excel is:

ABS( number )

PARAMETERS or ARGUMENTS

number - A numeric value used to calculate the absolute value.

EXAMPLES

Let's look at some Excel ABS function examples and explore how to use the ABS function as a worksheet function in Microsoft Excel:

Based on the Excel spreadsheet above, the following ABS examples would return:

=ABS(A1)

 Result: 120

=ABS(A2)

 Result: 3.5

=ABS(A3)

 Result: 45

=ABS(-6.9)

 Result: 6.9

=ABS(5-15)

 Result: 10

MS Excel: IF function

DESCRIPTION

The Microsoft Excel IF function returns one value if a condition is true and another value if is not.

SYNTAX

The syntax for the IF function in Microsoft Excel is:

IF(logical_test, value_if_true, [value_if_false])

PARAMETERS or ARGUMENTS

logical_test     - The condition you want to test. You can use other logical functions within this argument, including AND, OR and XOR functions.

value_if_true - The value that you want to be returned if the result of logical_test is TRUE.

value_if_false - The value that you want to be returned if the result of logical_test is FALSE. This parameter is optional.

NOTE

You can put another IF function in IF function in the place of some of the arguments.

=IF(E2>=85,"A",IF(E2>=75,"B","C"))

EXAMPLES

Let's look at some Excel IF function examples and explore how to use it

The following IF examples would return:

=IF(A2>B2,"Over Budget","OK")

  Result: Over Budget

=IF(A4=500,B4-A4,"")

  Result: 425

=IF(A3>200, "Larger", "Smaller")

  Result: Larger

=IF(A2=1500, "Equal", "Not Equal")

  Result: Equal

Oracle DB: REPLACE Function

DESCRIPTION

The Oracle REPLACE function replaces a sequence of characters in a string with another set of characters.

SYNTAX

REPLACE( string1, string_to_replace, replacement_string )

PARAMETERS or ARGUMENTS

string1 - The string to replace a sequence of characters with another set of characters.

string_to_replace - The string that will be searched for in string1.

replacement_string – This parameter is optional. All occurrences of string_to_replace will be replaced with replacement_string in string1. If the replacement_string parameter is omitted, the REPLACE function simply removes all occurrences of string_to_replace, and returns the resulting string.

EXAMPLES

REPLACE('123123test', '12');    
     Result: '33test'

REPLACE('123work123', '123');   
     Result: 'work'

REPLACE('222oracle', '2', '3'); 
     Result: '333oracle'

REPLACE('0000102300', '0');     
     Result: '123'

REPLACE('0000888', '0', ' ');   
     Result: '    888'

Oracle DB: NVL Function

DESCRIPTION

NVL replaces NULL value with other value in the results of a query.

SYNTAX

NVL(expr1, expr2)

PARAMETERS or ARGUMENTS

If expr1 is NULL, then NVL returns expr2. If expr1 is not NULL, then NVL returns expr1.

The arguments expr1 and expr2 can have any datatype. If their datatypes are different, then Oracle Database implicitly converts one to the other. If they are cannot be converted implicitly, the database returns an error.

The implicit conversion is implemented as follows:

-          If expr1 is character data, then Oracle Database converts expr2 to the datatype of expr1 before comparing them and returns VARCHAR2 in the character set of expr1.

-          If expr1 is numeric, then Oracle determines which argument has the highest numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype.

EXAMPLES

SQL> SELECT NVL(supplier_city, 'n/a')

       FROM suppliers;

The SQL statement above would return 'n/a' if the supplier_city field contained a NULLvalue. Otherwise, it would return the supplier_city value.

MS Excel: SUM function

DESCRIPTION

The Microsoft Excel SUM function adds all numbers in a range of cells and returns the result.

SYNTAX
The syntax for the SUM function in Microsoft Excel is:
SUM( number1, [number2, ... number_n] )

OR

SUM ( cell1:cell2, [cell3:cell4], ... )

PARAMETERS or ARGUMENTS

number    -  A numeric value that you wish to sum.

cell           -  The range of cells that you wish to sum.

NOTE

You can sum combinations of both numbers and ranges of cells using the SUM function.

EXAMPLES

Let's look at some Excel SUM function examples and explore how to use the SUM function as a worksheet function in Microsoft Excel:

Based on the Excel spreadsheet above, the following SUM examples would return:

=SUM(A2, A3)            
  Result: 17.7
=SUM(A3, A5, 45)        
  Result: 57.6
=SUM(A2:A6)             
  Result: 231.2
=SUM(A2:A3, A5:A6)      
  Result: 31.2
=SUM(A2:A3, A5:A6, 500) 
  Result: 531.2

Execute a SQL script files in sqlplus

Question: How do I execute a SQL script file in SQLPlus?

Answer: To execute a script file in SQLPlus, type @ and then the file name.

SQL> @{yourfile}

For example, if your file was called yourscript.sql, you'd type the following command at the SQL prompt:

SQL> @yourscript.sql

The above command assumes that the file is in the current directory, i.e. the current directory is usually the directory that you were located in before you launched SQLPlus.

If you need to execute a script file that is not in the current directory, you would type:

SQL> @{path}{yourfile}

For example:

SQL> @/oracle/scripts/yourscript.sql

This command would run a script file called yourscript.sql that was located in the /oracle/scripts directory.