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