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.

How to force a trace file to be created when an ORA error occurs?

There was an issue we ran into where one of the users ran out temporary tablespace (TEMP) we had a message in the database alert log that there oracle couldn’t extend temporary tablespace and we couldn’t find the cause and user who ran into the issue. So turning on an event for the error oracle will create a trace file which will contain user, machine, os, SQL which will allow DBA to find additional information.

Example:

Oracle docs note this about ORA-01652:
Error: RA-01652: unable to extend temp segment by string in tablespace string
Cause: Failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.


To turn trace on you would run the following SQL:
ALTER system SET EVENTS ‘1652 TRACE NAME ERRORSTACK LEVEL 3’;

It will write to the alert.log

Tue Jul 21 11:04:45 2009
Errors in file /u01/admin/TESTDB/udump/testdb_ora_17682588.trc:
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

The trace file would contain the following information including the SQL statement:
….
oct: 2, prv: 0, sql: 7000000593f3dc8, psql: 700000057c3ec30, user: 420/TEST
O/S info: user: ajaffer, term: MYCOMPUTER, ospid: 3684:2444, machine: MYCOMPUTERNAME

To turn trace off you would run the following SQL:
ALTER system SET EVENTS ‘1652 TRACE NAME ERRORSTACK OFF’;

If one would like to set an event in the spfile, you can set by executing the DDL below. If there are multiple events then it can be set by they have to be “:” separated. In the example below event is set for ORA-01653 and ORA-01652
ALTER system SET event=’1653 TRACE NAME ERRORSTACK LEVEL 3:1652 TRACE NAME ERRORSTACK LEVEL 3′ scope=SPFILE;

To clear the event in the spfile, you can execute the following SQL
ALTER SYSTEM SET EVENT=” SCOPE=spfile;

One can similar turn trace on for various ora errors but be aware that some of them by caused internally from within Oracle example ORA-604 which you may not want to turn on an event for.

Thank you for reading and support.

Recompile the whole DB using Oracle's scripts utlrp.sql and utlprp.sql

The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database. 

They are typically run after major database changes such as upgrade or downgrade or patches installation. 

They are located in the $ORACLE_HOME/rdbms/admin directory and provide a wrapper for the UTL_RECOMP package. UTL_RECOMP provides a more general recompilation interface, including options to recompile objects in a single schema. Please see the documentation for package UTL_RECOMP for more details. 

The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of "0". 

The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows.

     0 - The level of parallelism is derived based on the CPU_COUNT parameter.

     1 - The recompilation is run serially, one object at a time.

     N - The recompilation is run in parallel with "N" number of threads.

Both scripts must be run as the SYS user, or another user with SYSDBA, to work correctly.

Thank you for the reading and support.