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.

How to Fix "ORA-12505, TNS: listener does not currently know of SID given in connect descriptor"

Cause: The listener received a request to establish a connection to a database or other service. The connect descriptor received by the listener specified a SID for an instance (usually a database instance) that either has not yet dynamically registered with the listener or has not been statically configured for the listener. This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.

To properly resolve this error and connect to the appropriate Oracle database, we’ll need to expound a bit on how Oracle behaves and, therefore, what is causing this issue in the first place.

SIDs vs SERVICE_NAMES
It is important to understand the (slight) difference between what Oracle defines as a SID compared to a SERVICE_NAME, as we’ll use this information to create a proper connection string later on.

In Oracle, the system identifier (or SID) is a local identifier of up to eight characters in length that is used to identify a particular database and differentiate it from other databases on the system.

Often the SID is the prefix word or DB_UNIQUE_NAME that precedes the DB_DOMAIN.

SERVICE_NAMES, on the other hand, represent the names by which database instances can be connected to. A SERVICE_NAME will typically follow the format of the SID followed by the database domain, like so: DB_UNIQUE_NAME.DB_DOMAIN

The TNS Listener
When a client is attempting to connect to an Oracle database, rather than connecting to the database directly, there is a broker service that intervenes and handles the connection request for the client.

This broker application is known as the listener and it performs the task of listening for incoming client requests. When a request is received, the listener processes and forwards that request onto the appropriate Oracle database server using a service handler, which just acts as the connection between the listener and the database server.

TNS Names and Configuration
When connecting to an Oracle database, typically your database server will have tnsnames.ora, which is a configuration file that informs the server about NET_SERVICE_NAMES which are valid database connections. By default, this file is located at ORACLE_HOME/network/admin.

For example, a NET_SERVICE_NAME descriptor in tnsnames.ora may be formatted like this:

myDatabaseNetService =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = tcp)(HOST = localhost)(PORT = 1521)(QUEUESIZE = 100))
    (CONNECT_DATA =
      (SERVICE_NAME = bookstore.company.com)
    )
  )

This would define a NET_SERVICE_NAME using the SERVICE_NAME we discussed earlier and connecting to localhost through port 1521.

Connection String
With a bit more knowledge about how Oracle actually connects to databases, we can now look at how connection strings are formatted.


Connect via TNS NAME
When connecting through a NET_SERVICE_NAME as specified in your tnsnames.ora config file, you must use the username, password, and then append the NET_SERVICE_NAME with the @ symbol, like so:

username/password@NET_SERVICE_NAME

Connect via NET_SERVICE_NAME
Thus, for our previous NET_SERVICE_NAME descriptor above, the actual NET_SERVICE_NAME we defined was myTestDatabaseNetService, so our connection string might look something like this:

john/Pass123@myTestDatabaseNetService

Connect via SERVICE_NAME
When connecting through a SERVICE_NAME, you’ll also need to add the host and port, along with the / symbol preceding the SERVICE_NAME itself:

username/password@host:port/SERVICE_NAME

Connect via SID
Finally, if connecting without a configured NET_SERVICE_NAME or even SERVICE_NAME, you can do so directly through the SID by using the : symbol instead of the / symbol as with the SERVICE_NAME connection string:

username/password@host:port:SID

Change a user's password in Oracle

Question: How do I change the password for a user in Oracle?

Answer: To change a user's password in Oracle, you need to execute the alter user command.

Syntax:
The syntax for changing a password in Oracle is:
ALTER USER user_name IDENTIFIED BY new_password;

Parameters or Arguments:
user_name - The user whose password you wish to change.
new_password - The new password to assign.

Example
Let's look at an example of how to change a password for a user in Oracle/PLSQL.

ALTER USER smith IDENTIFIED BY Pass05_NM;

This example would change the password for the user named smith and set the new password to Pass05_NM.

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.

Oracle File extensions

SQL*Plus files

.sql - SQL script

.lst - spool file

PL/SQL files

.pls - PL/SQL source

.plb - PL/SQL binary

.pks - Package source or package specification

.pkb - Package binary or package body

.pck - Combined package specification plus body

Oracle database files

.dbf - database file

.log - Online Redo Log

.rdo - Online Redo Log

.arc - Archive log

SQL*Loader files

.ctl - Control file

.dat - Data file

.bad - Bad file

.dsc - Discard file

SQL*Net files

.ora - tnsnames.ora, sqlnet.ora, etc.

Forms files

.fmb - Forms binary

.fmt - Forms text

.fmx - Forms executable

Reports files

.rdf - contains a single report definition in binary format. 

        .rdf files are used to both run and edit reports.

.rep - contains a single report definition in binary format. 

          .rep files are used solely to run reports; you cannot edit a .rep file

.rex - contains a single report definition in text format. .REX files are portable.

PGA usage by sessions

Summary 

Each user session contains its own PGA taken from the operating system free memory pool. As sessions have the need to use PGA memory, the Oracle database will allocate the session memory from the PGA until the total allocation of memory is equal to the pga_aggregate_target value set within the database. As sessions require additional memory from the PGA, Oracle will pull the memory from other sessions that no longer need the memory.

SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') 
       || TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE - LOGON_TIME, 1) * 86400), 'SSSSS'),'HH24:MI:SS') LOGON,
       SID,
       v$session.SERIAL#,
       v$process.SPID,
       ROUND(v$process.pga_used_mem / (1024 * 1024), 2) PGA_MB_USED,
       v$session.USERNAME,
       STATUS,
       OSUSER,
       MACHINE,
       v$session.PROGRAM,
       MODULE
  FROM v$session, v$process
 WHERE v$session.paddr = v$process.addr
--AND status = 'ACTIVE' 
--AND module = 'ECR'
--AND v$session.sid = 452
--AND v$session.username = 'MR54' 
--AND v$process.spid = 5698
 ORDER BY pga_used_mem DESC;

To find the total PGA memory used by processes
SELECT ROUND(SUM(pga_used_mem) / (1024 * 1024), 2) PGA_USED_MB
  FROM v$process;

To find PGA usage for a specific session
SELECT SID, b.NAME, ROUND(a.VALUE / (1024 * 1024), 2) MB
  FROM v$sesstat a, v$statname b
 WHERE (NAME LIKE '%session uga memory%' OR
       NAME LIKE '%session pga memory%')
   AND a.statistic# = b.statistic#
   AND SID = 596;


To calculate the amount of memory that you gone need for PGA, estimate the number of maximum connected sessions and run:
SELECT &MAX_CONNECTED_SESSIONS * (2048576 + P1.VALUE + P2.VALUE) /
       (1024 * 1024) YOU_NEED_PGA_MB
  FROM V$PARAMETER P1, V$PARAMETER P2
 WHERE P1.NAME = 'sort_area_size'
   AND P2.NAME = 'hash_area_size';

V$PGASTAT provides PGA memory usage statistics as well as statistics about the automatic PGA memory manager when it is enabled (that is, when PGA_AGGREGATE_TARGET is set). Cumulative values in V$PGASTAT are accumulated since instance startup.

To query the contents of the current PGA settings within Oracle 11g, issue a request against the v$pgastat dynamic performance view as shown:
SELECT *  FROM V$PGASTAT; 

NAME                                   VALUE               UNIT
---------------------------------------------------------------- 
aggregate PGA target parameter         163577856           bytes
aggregate PGA auto target              80363520            bytes
global memory bound                    32714752            bytes
total PGA inusen                       74283008            bytes
total PGA allocated                    156244992           bytes
maximum PGA allocated                  240105472           bytes
total freeable PGA memory              9240576             bytes
process count                          30
max processes count                    41
PGA memory freed back to OS            273874944           bytes
total PGA used for auto workareas      0                   bytes
maximum PGA used for auto workareas    7929856             bytes
total PGA used for manual workareas    0                   bytes
maximum PGA used for manual workareas  270336              bytes
over allocation count                  0       
bytes processed                        581040128           bytes
extra bytes read/written               0                   bytes
cache hit percentage                   100 percent
recompute count (total)                717


19 rows selected.


To change PGA memory parameter
ALTER SYSTEM SET pga_aggregate_target = 3500M SCOPE=BOTH;

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.