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.

Setting this parameter to true will save disk space, if we expect lot of empty tables in the database.

Lets see the below example:->

When DEFERRED_SEGMENT_CREATION=TRUE( THIS IS THE DEFAULT ONE)

QL> show parameter DEFERRED_SEGMENT_CREATION

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------
deferred_segment_creation            boolean     TRUE
SQL>
SQL>

SQL> create table DEF_TABLE ( class_name VARCHAR2(100), SECTION NUMBER);

Table created.



SQL>  create index DEF_IDX on DEF_TABLE (SECTION);

Index created.


SQL>  select segment_name,segment_type from dba_segments where OWNER='DBACLASS';

no rows selected.

We can see, it haven’t created the segments. So if you expect lot of empty tables in your database, setting this parameter to TRUE, will save lot of disk space.

Now set to FALSE, do the same test:

SQL> alter system set deferred_segment_creation=FALSE scope=both;

System altered.


SQL> show parameter DEFERRED_SEGMENT_CREATION

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------
deferred_segment_creation            boolean     FALSE

SQL>  create table DEF_TABLE_2 ( class_name VARCHAR2(100), SECTION NUMBER);

Table created.



SQL>  create index DEF_IDX2 on DEF_TABLE_2(SECTION);

Index created.





SQL>  select segment_name,segment_type from dba_segments where OWNER='DBACLASS';

SEGMENT_NAME                                                            SEGMENT_TYPE
---------------------------------------------------------------------- ------------------
DEF_IDX2                                                               INDEX
DEF_TABLE_2                                                            TABLE

We can see, as soon as we create the table and and index, the segment got created ,despite it does not   contain any data.

The post DEFERRED_SEGMENT_CREATION parameter in oracle appeared first on DBACLASS.

LOG_CHECKPOINTS_TO_ALERT – > FALSE( défault value)

SQL> show parameter LOG_CHECKPOINTS_TO_ALERT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_checkpoints_to_alert             boolean     FALSE


SQL> alter system checkpoint;

System altered.

ALERT LOG:
Tue Feb 26 03:35:55 2019
Thread 1 advanced to log sequence 6066 (LGWR switch)
Current log# 3 seq# 6066 mem# 0: /B2CNSM/oradata/B2CNSM/onlinelog/redo03.log
Tue Feb 26 03:35:55 2019
Archived Log entry 6069 added for thread 1 sequence 6065 ID 0x54d5ee59 dest 1:

We can see, despite doing checkpoint, no informations is written in alert log.

LOG_CHECKPOINTS_TO_ALERT – > TRUE 

SQL> alter system set log_checkpoints_to_alert=TRUE scope=both;

System altered.

SQL> show parameter LOG_CHECKPOINTS_TO_ALERT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_checkpoints_to_alert             boolean     TRUE
SQL>  alter system checkpoint;

System altered.

ALERT LOG 

Tue Feb 26 13:33:33 2019
ALTER SYSTEM SET log_checkpoints_to_alert=TRUE SCOPE=BOTH;
Tue Feb 26 13:33:40 2019
Beginning global checkpoint up to RBA [0x17b2.126b2.10], SCN: 11904009889406
Completed checkpoint up to RBA [0x17b2.126b2.10], SCN: 11904009889406

Now checkpoint information is getting logged in the alert log .

NOTE – > This parameter has no impact on the database performance. It is just for additional checkpoint logging. 

The post LOG_CHECKPOINTS_TO_ALERT parameter in oracle appeared first on DBACLASS.

Basically, it does two steps.

1. Divides the table data into small chunks.
2. Apply DML change on each chunk parallely.

This method improves the performance the large DML operations, without consuming too much of rollback segment.

Lets check the below example.

TABLE_OWNER   -> DBACLASS
TABLE_NAME    ->  TEST100
DML STATEMENT -> Update dbaclass.test100 set OBJECT_ID=10 where NAMESPACE=1;

1. Create one task:

 SQL> execute DBMS_PARALLEL_EXECUTE.CREATE_TASK(TASK_NAME=>'query_test100_update');

PL/SQL procedure successfully completed.

2. CHUNK the table by row_id:

BEGIN
    DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID
  (
                TASK_NAME=> 'query_test100_update',
                TABLE_OWNER =>'DBACLASS',
                TABLE_NAME => 'TEST100',
                BY_ROW=> TRUE,
                CHUNK_SIZE => 10000
  );
END;
/

3. Run the DML update procedure(RUN_TASK)

SET SERVEROUTPUT ON
DECLARE
  l_sql_stmt VARCHAR2(32767);
BEGIN 
 -- DML to be execute in parallel
  l_sql_stmt := 'Update dbaclass.test100 set OBJECT_ID=10 where NAMESPACE=1 and rowid BETWEEN :start_id AND :end_id';

  -- Run the task
  DBMS_PARALLEL_EXECUTE.RUN_TASK
  (
    TASK_NAME      => 'query_test100_update',
    SQL_STMT       => l_sql_stmt,
    LANGUAGE_FLAG  => DBMS_SQL.NATIVE,
    PARALLEL_LEVEL => 10
  );  
END;
/

 
PL/SQL procedure successfully completed.

Elapsed: 00:00:21.07

DML completed successfully and it took 21 seconds only.

The post DBMS_PARALLEL_EXECUTE in oracle PL/SQL appeared first on DBACLASS.

What is SQL profile:

SQL profile is a collection of additional statistical information stored in the data dictionary that helps the optimizer to generate the best plan for the query. SQL profile is managed through SQL_TUNE package of SQL tuning advisor. i.e when we run SQL tuning advisor against a query, The tuning optimizer creates a profile for an SQL statement called SQL profile which consists additional statistical information about that statement, and it gives the recommendation to accept that profile.

NOTE – It is important to note that the SQL Profile does not freeze the execution plan of a SQL statement, as done by stored outlines. As tables grow or indexes are created or dropped, the execution plan can change with the same SQL Profile. The information stored in it continues to be relevant even as the data distribution or access path of the corresponding statement change.

SQL PROFILE can be applied to below statements.

SELECT statements
UPDATE statements
INSERT statements (only with a SELECT clause)
DELETE statements
CREATE TABLE statements (only with the AS SELECT clause)
MERGE statements (the update or insert operations)

EXAMPLE:

In this example, first we will run SQL tuning advisor against an sql_id , and then will do different operations on sql profile.

1. Run sql tuning advisor for sql_id=5dkrnbx1z8gcb

set long 1000000000
Col recommendations for a200
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => '5dkrnbx1z8gcb',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 500,
                          task_name   => '5dkrnbx1z8gcb_tuning_task_1',
                          description => 'Tuning task for statement 5dkrnbx1z8gcb');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/


EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '5dkrnbx1z8gcb_tuning_task_1');


SET LONG 10000000;
SET PAGESIZE 100000000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('5dkrnbx1z8gcb_tuning_task_1') AS recommendations FROM dual;
SET PAGESIZE 24

2. Go through the profile recommendation part of the report:

DBMS_SQLTUNE.report_tuning_task will generate the completed output of the advisory. If you go through the profile recommendation part, it will be as below.

  Recommendation (estimated benefit: 96.57%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
 
    execute dbms_sqltune.accept_sql_profile(task_name => '5dkrnbx1z8gcb_tuning_task_1', task_owner => 'SYS', replace =>TRUE);

2. Accept profile:

    execute dbms_sqltune.accept_sql_profile(task_name => '5dkrnbx1z8gcb_tuning_task_1', task_owner => 'SYS', replace =>TRUE);

3. Find the name of the sql_profile:

in the accept_sql_profile package we didn’t specify a sql_profile name, So we need to find the sql_profile from the task_name.
Use the below query.

select name,status,force_matching from dba_sql_profiles  where task_id in ( select task_id from DBA_ADVISOR_TASKS where task_name ='5dkrnbx1z8gcb_tuning_task_1')

set pagesize 299
set lines 299
col name for a45
NAME                               STATUS   FOR
---------------------------------- -------- ---
SYS_SQLPROF_01601b64332e0000       ENABLED  NO

4. Alter a profile,

You can disable a sql_profile using steps.

BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name => 'SYS_SQLPROF_01601b64332e0000', 
attribute_name => 'STATUS', 
value => 'DISABLED');
END;
/

To enable to changed the value from DISABLED to ENABLED.

5. Dropping an SQL profile:

begin
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'SYS_SQLPROF_01601b64332e0000');
end;
/

The post WHAT IS SQL PROFILE IN ORACLE appeared first on DBACLASS.

There are 2 methods by which LGWR and foreground processes can communicate in order to acknowledge that a commit has completed:
Post/wait: traditional method available in previous Oracle releases LGWR explicitly posts all processes waiting for the commit to complete.

Polling: Foreground processes sleep and poll to see if the commit is complete.

Below is the snippet from lgwr trace, which shows adaptive switching between these methods.

*** 2017-11-20 15:04:58.691
Warning: log write elapsed time 965ms, size 3KB
kcrfw_alfs_update_mode: post->poll long#=53 sync#=313 sync=4833 poll=4744 rw=1923 ack=2372 min_sleep=1069

*** 2017-11-21 15:37:44.019
Log file sync switching to polling --- >>>> It shows current method is polling 
Current scheduling delay is 1 usec
Current approximate redo synch write rate is 104 per sec




*** 2017-11-21 15:53:09.673
Warning: log write elapsed time 2165ms, size 2KB
kcrfw_alfs_update_mode: poll->post curr_sched_delay=0 switch_sched_delay=1 curr_sync_count_delta=21 switch_sync_count_delta=313

*** 2017-11-21 16:05:16.014
Log file sync switching to post/wait -- >>>> It shows current method is post/wait
Current approximate redo synch write rate is 7 per sec

However, there are few cases, where this adaptive switching caused the lgwr process to block other session or causes huge log file sync waits.
In that case, if you wish to disable this feature, then

alter system set "_use_adaptive_log_file_sync"=FALSE scope=both;

NOTE – Please check with oracle support before doing this change in the production database.

You can check the redo polling stats from the database using below query.

  1* select name,value from v$sysstat where name in ('redo synch poll writes','redo synch polls');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo synch poll writes                                               561500
redo synch polls                                                     753849

The post _use_adaptive_log_file_sync parameter in oracle appeared first on DBACLASS.

$ ps -ef | grep lreg
oracle     3536 105780  0 17:21 pts/5    00:00:00 grep lreg
oracle    95761      1  0 Oct26 ?        00:01:14 ora_lreg_ORCL      -- >>>>> lreg for oracle db 
oracle   116911      1  0 Aug23 ?        00:04:24 asm_lreg_+ASM      --->>>>>> lreg for ASM instance 

Basically, LREG process does below things.

1. Registers instance information with the listener.
2. It is a critical background process for each database instance (if it is killed, Oracle goes down).
3. It does everything PMON used to do and report: service_update, service_register, service_died in the listener.log.

The post LREG Background Process in oracle appeared first on DBACLASS.

Oracle provided an undocumented hidden parameter, _optimizer_ignore_hint. If this parameter is set to true, Then it will ignore the hints mentioned in the SQL queries.

DEFAULT VALUE OF _optimizer_ignore_hint is FALSE.

Let’s run a test, to see how it behaves.

EXAMPLE:
Parameter is set to FALSE:(DEFAULT)

SQL> show parameter _optimizer

NAME                          TYPE   			VALUE
------------------           -----------           -------------
_optimizer_ignore_hints       boolean                  FALSE

Execute a query with a  HINT.

SQL> select /*+ FULL(TEST5) */ count(*) from TEST5 where owner='SYS';


  COUNT(*)
----------
     42814


Execution Plan
----------------------------------------------------------
Plan hash value: 529722805

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |     7 |   480   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |       |     1 |     7 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST5 |  1184 |  8288 |   480   (1)| 00:00:01 | ------- >>>> .FULL SCAN DUE TO FULL HINT
----------------------------------------------------------------------------

We can see, as we have used a FULL hint, TABLE ACCESS FULL is used. That does not change the query behavior.

Now, lets set it to TRUE and re-run the same query.

The parameter is set to TRUE:

SQL> alter session set "_optimizer_ignore_hints"=TRUE ;

Session altered.

SQL> select /*+ FULL(TEST5) */ count(*) from TEST5 where owner='SYS';

  COUNT(*)
----------
     42814


Execution Plan
----------------------------------------------------------
Plan hash value: 1056693648

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     7 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |      |     1 |     7 |            |          |
|*  2 |   INDEX RANGE SCAN| TE   |  1184 |  8288 |     3   (0)| 00:00:01 | ------ >>>> IGNORED FULL HINT, WENT FOR INDEX . 
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER"='SYS')

Now despite using a FULL hint, it was ignored because of the parameter _optimizer_ignore_hints.

NOTE – Don’t use in production database without proper testing, Because it will disable all the hints used in sql queries, which might be recommended by your Application.

EXCEPTION:

One interesting point, we observed is that, this parameter is not having any impact on PARALLEL hint. I.e even if we set this to TRUE, PARALLEL hint will work as expected.

SQL> show parameter _optimizer

NAME                          TYPE   			VALUE
------------------           -----------           -------------
_optimizer_ignore_hints       boolean                  TRUE


SQL>  select /*+ parallel(4) */ count(*) from TEST5;

  COUNT(*)
----------
    107708

Execution Plan
----------------------------------------------------------
Plan hash value: 4160549356

--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |   133   (0)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          |   107K|   133   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| TEST5    |   107K|   133   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------

Note
-----
   - Degree of Parallelism is 4 because of hint

The post _optimizer_ignore_hint appeared first on DBACLASS.

Below are the steps for configuring ASMlib and marking disks as ASM.

1. Download Asmlib:

You can download these rpm packages directly from OTN:

http://www.oracle.com/technetwork/server-storage/linux/downloads/index-088143.html

Else, if your system is registered with ULN, then you can download directly using yum as below.

- Run as root on the database server

yum install oracleasm-support oracleasmlib oracleasm-`uname -r` 

       2. Configure oracleasm:[ Do on all nodes if RAC]

[root$mapper]#  /etc/init.d/oracleasm init
[root$mapper]#  /etc/init.d/oracleasm configure -i
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets ('[]').  Hitting  without typing an
answer will keep that current value.  Ctrl-C will abort.

Default user to own the driver interface []: oracle
Default group to own the driver interface []: oinstall
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver:                     [  OK  ]
Scanning the system for Oracle ASMLib disks:               [  OK  ]

       3. Start oracleasm:[ Do on all nodes ]

# /etc/init.d/oracleasm start
Initializing the Oracle ASMLib driver:                     [  OK  ]
Scanning the system for Oracle ASMLib disks:               [  OK  ]

Now as we have configured and started the oracleasm, we can proceed with making the disks as ASM disk.

NOTE – > IF oracleasm start command is not working, then use oracleasm init command.

    4.Create ASM disk [ Do only from one node ]

Createdisk need to be run only from one node

# /etc/init.d/oracleasm createdisk MGMTP1 /dev/mapper/mpathup1
Marking disk "MGMTP1" as an ASM disk:                      [  OK  ]

   5.Now list and scan the disks [ On all the nodes ]

Once disk is created, you need to scan the disks using scandisks on all the nodes,

#  /etc/init.d/oracleasm scandisks
#  /etc/init.d/oracleasm listdisks

Now, these ASM disks are ready to be added to ASM disk group.

automatic start can be enabled or disabled with the ‘enable’ and ‘disable’ options to /etc/init.d/oracleasm:

      
-- Enable 
                                     
# /etc/init.d/oracleasm disable
  Writing Oracle ASM library driver configuration            [  OK  ]
  Unmounting ASMlib driver filesystem                        [  OK  ]
  Unloading module "oracleasm"                               [  OK  ]

-- Disable 

# /etc/init.d/oracleasm enable
  Writing Oracle ASM library driver configuration            [  OK  ]
  Loading module "oracleasm"                                 [  OK  ]
  Mounting ASMlib driver filesystem                          [  OK  ]
  Scanning system for ASM disks                              [  OK  ]

Delete asmdisk

Disks that are no longer used by ASM can be unmarked using deletedisk command

oracleasm deletedisk

 oracleasm deletedisk OCRVD1

Check whether a disk is already marked for asm or not:

[root~]# oracleasm querydisk /dev/emcpowerac1
Device "/dev/emcpowerac1" is marked an ASM disk with the label "REDO1"

Get physical disk details with ASM DISK NAMES:

blkid command will give information about the physical disks and respective asmdisk label name

[rootmapper]# /sbin/blkid 
/dev/sdm1: LABEL="REDO06" TYPE="oracleasm"
/dev/sdp1: LABEL="REDO04" TYPE="oracleasm"
/dev/sds1: LABEL="REDO03" TYPE="oracleasm"
/dev/sdv1: LABEL="REDO01" TYPE="oracleasm"
/dev/sdy1: LABEL="REDO05" TYPE="oracleasm"
/dev/sdab1: LABEL="REDO02" TYPE="oracleasm"
/dev/sdjn1: LABEL="REDO04" TYPE="oracleasm"
/dev/sdjk1: LABEL="REDO06" TYPE="oracleasm"
/dev/sdjq1: LABEL="REDO03" TYPE="oracleasm"
/dev/sdjt1: LABEL="REDO01" TYPE="oracleasm"
/dev/sdjw1: LABEL="REDO05" TYPE="oracleasm"
/dev/sdjz1: LABEL="REDO02" TYPE="oracleasm"
/dev/mapper/mpathop1: LABEL="REDO04" TYPE="oracleasm"
/dev/mapper/mpathup1: LABEL="REDO01" TYPE="oracleasm"
/dev/mapper/mpathyp1: LABEL="REDO05" TYPE="oracleasm"
/dev/mapper/mpathsp1: LABEL="REDO03" TYPE="oracleasm"
/dev/mapper/mpathmp1: LABEL="REDO06" TYPE="oracleasm"
/dev/mapper/mpathalp1: LABEL="REDO02" TYPE="oracleasm"

The post oracleasm utility for ASMLIB 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.