when sec_case_sensitive_logon is TRUE – -> t he database use login passwords are case sensitive.

when sec_case_sensitive_logon is FALSE – -> means database use login passwords are case insensitive.

DEFAULT VALUE IS TRUE .

Lets see below DEMO:

DEMO:

1. When sec_case_sensitive_logon is TRUE.

SQL> show parameter sec_case

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE



SQL> alter user DBACLASS identified by DBAclass;

User altered.



SQL> connect DBACLASS/dbaclass
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.



SQL> connect  DBACLASS/DBAclass
Connected.

We can see the the password is case sensitive.

2. When sec_case_sensitive_logon is FALSE.

Lets make the parameter to false:

SQL> ALTER SYSTEM SET sec_case_sensitive_logon=FALSE scope=both;

database altered.


SQL> show parameter sec_case

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     FALSE

SQL> alter user DBACLASS identified by DBAclass;

User altered.


Now try to connect with any case(small or caps)  

SQL>
SQL> connect DBACLASS/dbaclass
Connected.
SQL>  connect DBACLASS/DBACLASS
Connected.

We can see password is case insensitive.

The post sec_case_sensitive_logon parameter in oracle appeared first on DBACLASS.

Let’s see the below example:

SQL> show parameter resumable_timeout                    

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resumable_timeout                    integer     0

The parameter is set to  ZERO. Let’s create a table.

SQL> create table DBACLASS.TEST_TAB as select * from dba_objects;
create table DBACLASS.TEST_TAB as select * from dba_objects
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEST2

As soon as the transaction couldn’t find any space, it terminated the process instantly( Because resumable_timeout is set to 0).

The alert log also:

Mon May 29 11:59:29 2017
ORA-1652: unable to extend temp segment by 128 in tablespace TEST2

Lets set resumable_timeout to a higher value:

SQL> alter system set resumable_timeout=900 scope=both;

System altered.

Create the table again:

SQL> create table DBACLASS.TEST_TAB as select * from dba_objects;
..
..

Now the query is stuck, let’s check the alert log:

Mon May 29 11:59:59 2017
ORA-1652: unable to extend temp segment by 128 in tablespace TEST2
statement in resumable session 'User SYS(0), Session 1103, Instance 1' was suspended due to
    ORA-01652: unable to extend temp segment by 128 in tablespace TEST2
Mon May 29 12:00:35 2017

It clearly states that, the statement in suspended.

We can check dba_resumables also:

SQL> select name, status, timeout, sql_text, error_msg from dba_resumable where status='SUSPENDED';

NAME                           STATUS       TIMEOUT SQL_TEXT
------------------------------ --------- ---------- ------------------------------
ERROR_MSG
-------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
User SYS(0), Session 1160, Ins SUSPENDED        900 create table DBACLASS.TEST_TAB
tance 1                                              as select * from dba_objects
ORA-01652: unable to extend temp segment by 128 in tablespace TEST2

Now we will add some space to that tablespace,

SQL> alter database datafile '/SIBIP16/SBIP18DB/SBIP18DB/test02.dbf' resize 1G;

Database altered.

Alert log now:

Completed: alter database datafile '/SIBIP16/SBIP18DB/SBIP18DB/test02.dbf' resize 1G
Mon May 29 12:00:40 2017
statement in resumable session 'User SYS(0), Session 1103, Instance 1' was resumed

DBA_RESUMABLES:

SQL> select name, status, timeout, sql_text, error_msg from dba_resumable where status='SUSPENDED';

no rows selected

We can see, as soon as space is available, the statement resumed its transaction

SQL> create table DBACLASS.TEST_TAB as select * from dba_objects;

Table created.

The post resumable_timeout parameter in oracle database appeared first on DBACLASS.