How to find out if the temporary tablespace is autoextensible or not

Situation

The user runs into multiple ORA-01555 errors stating that the TEMP tablespace is unable to extend the temp segment by a particular size.

If the database template was used in the creation of the database, this ought to be a very rare occurrence.  The DBT establish the temp tablespace with autoextend on and the ability to grow to a max of "unlimited", which is to say 32G for a single file.

Solution

To determine if the autoextend option has been set for a temp tablespace, log into SQL Plus as SYS / as SYSDBA, and run the following script:

column file_name format a30
SELECT file_name, tablespace_name, autoextensible
  FROM dba_temp_files;

To turn autoextend on, modify the following script to include your temp datafile and path, and run from SQL Plus as SYS / as SYSDBA:

SQL> alter database tempfile '<Your path>\<Your_TEMP01>.DBF' autoextend on;

Here's an example:

SQL> alter database tempfile 'C:\APP\ADMINISTRATOR\ORADATA\PROTECT\TEMP01.DBF' autoextend on;

Selecting DISTINCT values from an associative array

Problem: Is there any built-in function in Oracle for selecting distinct values from an associative array.

Answer: You can use SET operator but take into account that the set operator doesn't work on nested tables of PL/SQL records. You can use SET on nested tables of single values.

               

Look the following example that shows how to get DISTINCT value form an associative array.

SQL>

DECLARE

    TYPE num_list IS TABLE OF NUMBER;

    id_list       num_list := num_list();

    id_list_2     num_list := num_list();

BEGIN

    FOR i IN (SELECT 1 AS value

                FROM all_tables

               WHERE ROWNUM < 10

             )

    LOOP

        id_list.EXTEND;

        id_list(id_list.LAST) := i.value;

    END LOOP;

    FOR o IN id_list.FIRST .. id_list.LAST

    LOOP

        dbms_output.put_line('Org: '||id_list(o));

    END LOOP; 

    id_list_2 := set( id_list ) ;

    FOR d IN id_list_2.FIRST .. id_list_2.LAST

    LOOP

        dbms_output.put_line('New: '||id_list_2(d));

    END LOOP; 

END;

/

--Output

Org: 1

Org: 1

Org: 1

Org: 1

Org: 1

Org: 1

Org: 1

Org: 1

Org: 1

New: 1

How to set current value of a sequence without dropping/recreating

Oracle does not let you change directly the value of a sequence. If you need to change its value, you should re-create the sequence.
On the other hand, we can use a trick to change the value of a sequence without dropping/recreating it.

Let’s say we have a sequence and its value is 1000, and we want to set it to 500.

SQL> CREATE SEQUENCE test_seq

            INCREMENT BY 1

            START WITH 1000

            MINVALUE 1

            MAXVALUE 99999999;

SQL> SELECT last_number

       FROM user_sequences

      WHERE sequence_name = 'TEST_SEQ';

LAST_NUMBER

-----------

1000

SQL> ALTER SEQUENCE test_seq INCREMENT BY -500;

SQL> SELECT test_seq.NEXTVAL FROM dual;

SQL> ALTER SEQUENCE test_seq INCREMENT BY 1;

SQL> SELECT last_number

       FROM   user_sequences

      WHERE  sequence_name = 'TEST_SEQ';

LAST_NUMBER

-----------

500

By this way, we can set the new value of the given sequence.

Just for fun and for easy use of that logic in the future, let’s create a procedure for it.

CREATE OR REPLACE PROCEDURE Set_Sequence_value

              ( pi_owner         IN VARCHAR2,

                pi_seq_name      IN VARCHAR2,

                pi_new_seq_value IN NUMBER)

IS

    PRAGMA AUTONOMOUS_TRANSACTION;

    l_last_number            NUMBER;

    l_incr_by                NUMBER;

    CURSOR c_get_seq_param IS

    SELECT increment_by

      FROM all_sequences

     WHERE sequence_owner = UPPER (pi_owner)

       AND sequence_name = UPPER (pi_seq_name);

BEGIN

    OPEN c_get_seq_param;

    FETCH c_get_seq_param INTO l_incr_by;

    CLOSE c_get_seq_param;

    IF l_incr_by IS NOT NULL

    THEN

        EXECUTE IMMEDIATE 'SELECT '||pi_owner||'.'||pi_seq_name||'.CURRVAL FROM DUAL' INTO l_last_number;

        EXECUTE IMMEDIATE 'ALTER SEQUENCE '||pi_owner||'.'||pi_seq_name||' INCREMENT BY '||(pi_new_seq_value - l_last_number);

        EXECUTE IMMEDIATE 'SELECT '||pi_owner||'.'||pi_seq_name||'.NEXTVAL FROM DUAL' INTO l_last_number;

        EXECUTE IMMEDIATE 'ALTER SEQUENCE '||pi_owner||'.'||pi_seq_name||' INCREMENT BY ' || l_incr_by;

    END IF;

END Set_Sequence_value;

Note: In order to not affect the transaction control, the procedure is created under AUTONOMOUS_TRANSACTION.

Have a fun and thank you for your time to read this article.

PLS-00313: "xxxxxx" not declared in this scope

PLS-00313: "string" not declared in this scope

Cause: There is no declaration for the given identifier within the scope of reference. The identifier might be misspelled, its declaration might be faulty, or the declaration might be placed incorrectly in the block structure.
Action: Check the spelling and declaration of the identifier. Also confirm that the declaration is placed correctly in the block structure.


If you do not want some procedures/functions to be publicly available you may not declare them in the package specification.
Declare them only in the package body. The cause of the error is declaration order of the procedures/functions in the package body or lack of forwarding declaration.

For example:

 create or replace package Test_pkg as
  2    procedure Proc1;
  3  end;
  4  /

Package created

create or replace package body Test_pkg as
  2  
  3    procedure proc1 is
  4    begin
  5      proc2;
  6    end;
  7  
  8    procedure Proc2 is
  9    begin
 10      dbms_output.put_line('proc2 is being executed');
 11    end;
 12  
 13  end;
 14  /

Warning: Package body created with compilation errors
Error: PLS-00313: 'PROC2' not declared in this scope

This is happening because we are calling Proc2 which declared later in the package. In this case our choices are:

Declare pro2 before the procedure which calls it

 create or replace package body Test_pkg as
  2  
  3  
  4    procedure Proc2 is
  5    begin
  6      dbms_output.put_line('proc2 is being executed');
  7    end;
  8  
  9    procedure proc1 is
 10    begin
 11      proc2;
 12    end;
 13  
 14  end;
 15  /

Package body created

Use forward declaration.

create or replace package body Test_pkg as
  2  
  3    procedure Proc2;
  4  
  5    procedure proc1 is
  6    begin
  7      proc2;
  8    end;
  9  
 10    procedure Proc2 is
 11    begin
 12      dbms_output.put_line('proc2 is being executed');
 13    end;
 14  
 15  
 16  end;
 17  /

Package body created

SQL> exec test_pkg.Proc1;

proc2 is being executed

PL/SQL procedure successfully completed

ORA-02030 error when try to grant permissions to a v$ tables

Face with an error ORA-02030 when you try to give permission on a v$ view

SQL> grant select on v$session to hr;
grant select on v$session to hr
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

Why it couldn’t give select privileges to a v$ view. If you tried to give permission to other v$views you will face same the error.

The problem is caused because of trying to give select privileges on a synonym. Oracle v$ views are named V_$VIEWNAME and they have synonyms in format V$VIEWNAME and you can’t give privilage on a synonym.
If you want to give permission to a V$ view you must grant permissions like below:

SQL> grant select on v_$session to hr;
Grant succeeded.

Switching to a Different Schema

The following statement sets the schema of the current session to the schema name specified in the statement.

ALTER SESSION SET CURRENT_SCHEMA = <schema name>;

In subsequent SQL statements, Oracle Database uses this schema name as the schema qualifier when the qualifier is omitted. In addition, the database uses the temporary tablespace of the specified schema for sorts, joins, and storage of temporary database objects. The session retains its original privileges and does not acquire any extra privileges by the preceding ALTER SESSION statement.

You can use this command above to avoid the use of public synonyms.  By setting the CURRENT_SCHEMA attribute to the schema owner name it is not necessary to create public synonyms for production table names. Without the current_schema syntax, you would have to assign a public synonym for every table in your production database.

If you want to check the current schema of the session you can use the following statement:

SELECT sys_context( 'userenv', 'current_schema' ) from dual; 

Let's look in the following example:

CONNECT user1

ALTER SESSION SET CURRENT_SCHEMA = schema_2;

SELECT * FROM empoyers;

Because user1 is not schema-qualified, the table name is resolved under schema schema_2. But if user1 does not have select privilege on table schema_2.empoyers, then user1 cannot execute the SELECT statement even switching the current session schema.

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

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.

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);

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.