How to lock table statstics in Oracle

Use dbms_stats.lock_table_stats

conn scott
exec dbms_stats.lock_table_stats('SCOTT','DEPT');

Verify the setting:

select table_name,object_type,stattype_locked 
from user_tab_statistics;

TABLE_NAME                     OBJECT_TYPE                          STATTYPE_LOCKED
------------------------------ ------------------------------------ ---------------
DEPT                           TABLE                                ALL
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
RECEIVED_DOCUMENTS             TABLE

Lock stats on a specific partition:

exec dbms_stats.lock_partition_stats('SCOTT','RECEIVED_DOCUMENTS','SETTELM');

Verify the setting:

select table_name,partition_name,subpartition_name,object_type,stattype_locked 
from user_tab_statistics 
where table_name='RECEIVED_DOCUMENTS';

TABLE_NAME                     PARTITION_NAME  SUBPARTITION_NAME OBJECT_TYPE          STATTYPE_LOCKED
------------------------------ --------------- ----------------- -------------------- ---------------
RECEIVED_DOCUMENTS                                               TABLE
RECEIVED_DOCUMENTS             APPLICANT                         PARTITION
RECEIVED_DOCUMENTS             APPLICATIONS                      PARTITION
RECEIVED_DOCUMENTS             SETTELM                           PARTITION            ALL

To reverse the process:

exec dbms_stats.unlock_table_stats('SCOTT','DEPT);
exec dbms_stats.unlock_partition_stats('SCOTT','RECEIVED_DOCUMENTS','SETTELM');

How to check if the "gather_database_stats" automatic maintenance jobs has succseeded

select id,operation,to_char(start_time,'dd.mm.yyyy hh24:mi:ss') "starttime",
                        to_char(end_Time,'dd.mm.yyyy hh24:mi:ss') "endtime",
                        to_dsinterval(to_char(end_time-start_Time,'dd.mm.yyyy hh24:mi:ss')) "elapsed",
                        status,job_name
from DBA_OPTSTAT_OPERATIONS
where operation = 'gather_database_stats (auto)'
order by start_time desc
fetch first 5 rows only;

Gave the following result:

ID	OPERATION	starttime	endtime	elapsed	STATUS	JOB_NAME
58778	gather_database_stats (auto)	07.02.2017 22:00:02	08.02.2017 02:00:15	+00 04:00:13.178197	TIMED OUT	ORA$AT_OS_OPT_SY_1965
58585	gather_database_stats (auto)	06.02.2017 22:00:06	07.02.2017 02:00:24	+00 04:00:18.089479	TIMED OUT	ORA$AT_OS_OPT_SY_1963
58273	gather_database_stats (auto)	05.02.2017 06:00:04	06.02.2017 02:00:26	+00 20:00:21.968028	TIMED OUT	ORA$AT_OS_OPT_SY_1950
58088	gather_database_stats (auto)	04.02.2017 06:00:02	05.02.2017 02:00:32	+00 20:00:29.696085	TIMED OUT	ORA$AT_OS_OPT_SY_1945
58038	gather_database_stats (auto)	03.02.2017 22:00:02	04.02.2017 02:00:17	+00 04:00:15.314408	TIMED OUT	ORA$AT_OS_OPT_SY_1943

The output indicates that the Automatic maintenance job never get to finish before the maintenance Windows Closes.

You can examine another view, dba_opstat_operation_tasks, to see the details on what the job didn't have time to do before the window closed:

select  opid, target,target_type, 
        to_char(start_Time,'dd.mm.yyyy hh24:mi:ss') "starttime",
        to_char(end_time,'dd.mm.yyyy hh24:mi:ss') "endtime"
        ,status
from dba_optstat_operation_tasks 
where opid=58778
order by start_Time desc
;

Sample output:

OPID	TARGET	TARGET_TYPE	starttime	endtime	STATUS
58778	SCOTT.TABLE1.SYS_SUBP25275	TABLE	08.02.2017 02:00:11	08.02.2017 02:00:11	TIMED OUT
58778	SCOTT.TABLE1.SYS_SUBP25267	TABLE	08.02.2017 02:00:11	08.02.2017 02:00:11	TIMED OUT

Notice how the starttime and endtime is identical; this is because the window Closes, and the job aborts. Oracle then records this timestamp as the start time and end time for the Objects it never had time to analyze.

The Predefined Maintenance Windows in Oracle 12c

Oracle documentation states:

"By default there are seven predefined maintenance windows, each one representing a day of the week. The weekend maintenance windows, SATURDAY_WINDOW and SUNDAY_WINDOW, are longer in duration than the weekday maintenance windows. The window group MAINTENANCE_WINDOW_GROUP consists of these seven windows."

Verify by executing:

SELECT WINDOW_NAME,REPEAT_INTERVAL,DURATION,to_char(LAST_START_DATE,'dd.mm.yyyy hh24:mi') "laststart",
    to_char(NEXT_START_DATE,'dd.mm.yyyy hh24:mi') "nextstart",ENABLED "window enabled?",ACTIVE "window open?"
FROM DBA_SCHEDULER_WINDOWS 
WHERE RESOURCE_PLAN='DEFAULT_MAINTENANCE_PLAN';

WINDOW_NAME	REPEAT_INTERVAL	DURATION	laststart	nextstart	window enabled?	window open?
SUNDAY_WINDOW	freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0	+00 20:00:00.000000	05.02.2017 06:00	12.02.2017 06:00	TRUE	FALSE
SATURDAY_WINDOW	freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0	+00 20:00:00.000000	04.02.2017 06:00	11.02.2017 06:00	TRUE	FALSE
FRIDAY_WINDOW	freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0	+00 04:00:00.000000	03.02.2017 22:00	10.02.2017 22:00	TRUE	FALSE
THURSDAY_WINDOW	freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0	+00 04:00:00.000000	02.02.2017 22:00	09.02.2017 22:00	TRUE	FALSE
WEDNESDAY_WINDOW	freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0	+00 04:00:00.000000	01.02.2017 22:00	08.02.2017 22:00	TRUE	FALSE
TUESDAY_WINDOW	freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0	+00 04:00:00.000000	07.02.2017 22:00	14.02.2017 22:00	TRUE	FALSE
MONDAY_WINDOW	freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0	+00 04:00:00.000000	06.02.2017 22:00	13.02.2017 22:00	TRUE	FALSE

The weekend Windows starts at 6 in the morning and run for 20 hours.
The weekday Windows starts at 22:00 and runs until 02:00 in the morning.

An overview of some important data Dictionary views can be found here

How to display a SYSTIMESTAMP data type as a TIMESTAMP WITH TZ

To display a value of datatype SYSTIMESTAMP as TIMESTAMP WITH TIME ZONE, use TO_CHAR first, then convert to TIMESTAMP WITH TIMEZONE.

The last expression adds one hour, which can be done after the conversion between the datatypes is complete:

select  TO_CHAR(SYSTIMESTAMP,'yyyy/mm/dd hh24:mi:ss.ff tzh:tzm'),
        TO_TIMESTAMP_TZ(to_char(SYSTIMESTAMP,'yyyy/mm/dd hh24:mi:ss.ff tzh:tzm'),'yyyy/mm/dd hh24:mi:ss.ff tzh:tzm'),
        TO_TIMESTAMP_TZ(to_char(SYSTIMESTAMP,'yyyy/mm/dd hh24:mi:ss.ff tzh:tzm'),'yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')+1/24
from dual;

systimestamp	TIMESTAMP_TZ	TIMESTAMP_TZ + 1hr
2017/02/01 10:36:28.158921 +01:00	01/02/2017 10:36:28,158921000 +01:00	01.02.2017 11:36:28

How to use gzip and tar together

To compress and tar a directory in one command, use the following syntax:

# tar -czvf mytarfile.tar.gz mydir

which will tar and compress the directory "mydir" with its contents.

To extract and untar a .tar.gz file in one command, use the following syntax:

# tar -zxvf mytarfile.tar.gz

How to confirm that the parameter INBOUND_CONNECT_TIMEOUT_listener_name is working

From the documentation:

Purpuose: To specify the time, in seconds, for the client to complete its connect request to the listener after the network connection had been established.

and

If the listener does not receive the client request in the time specified, then it terminates the connection. In addition, the listener logs the IP address of the client and an ORA-12525:TNS: listener has not received client's request in time allowed error message to the listener.log file.

My listener.ora file:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myserver.mydomain.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

inbound_connect_timeout_listener=3

Verify that the parameter is set:

LSNRCTL> show inbound_connect_timeout
LISTENER parameter "inbound_connect_timeout" set to 3
LSNRCTL>

Verify that logging is set and the location of the logfile:

LSNRCTL> show log_file
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserver.mydomain.com)(PORT=1521)))
LISTENER parameter "log_file" set to /u01/oracle/diag/tnslsnr/myserver/listener/alert/log.xml
The command completed successfully
LSNRCTL> show log_status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserver.mydomain.com)(PORT=1521)))
LISTENER parameter "log_status" set to ON
The command completed successfully

From a remote client, execute telnet against the listener port, in my case the default port 1521.

Use the "time" command in Linux to see how long the command executes:

[vk@myclient]$ time telnet myserver.mydomain.com 1521
Trying 192.168.0.122...
Connected to myserver.mydomain.com
Escape character is '^]'.
Connection closed by foreign host.

real    0m3.010s
user    0m0.001s
sys     0m0.001s

From the listener's log.xml file, we can see the message clearly:

<msg time='2017-01-23T11:09:12.118+01:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='myserver.mydomain.com'
 host_addr='192.168.0.122'>
 <txt>23-JAN-2017 11:09:12 * <unknown connect data> * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.25)(PORT=49264)) * establish * <unknown sid> * 12525
 </txt>
</msg>
<msg time='2017-01-23T11:09:12.119+01:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='myserver.mydomain.com'
 host_addr='192.168.0.122'>
 <txt>TNS-12525: TNS:listener has not received client's request in time allowed
 TNS-12535: TNS:operation timed out
  TNS-12606: TNS: Application timeout occurred
 </txt>
</msg>

From Oracle 10g and onwards, thet the default setting for INBOUND_CONNECT_TIMEOUT in listener.ora is 60 seconds
If set to zero, you disable the connect timeout functionality altogether. In such a situation, the telnet session above would simply "hang" and wait for its connection request to be completed.

Oracle recommends setting the INBOUND_CONNECTION_TIMEOUT in listener.ora in conjunction with INBOUND_CONNECT_TIMEOUT in sqlnet.ora.
Set the sqlnet.ora value slightly higher than the listener.ora value.

Sources:

http://docs.oracle.com/database/121/NETRF/sqlnet.htm#NETRF210
http://docs.oracle.com/database/121/NETRF/listener.htm#NETRF312

How to solve ORA-02391 exceeded simultaneous SESSIONS_PER_USER limit

The error definition:

//  *Cause: An attempt was made to exceed the maximum number of
//          concurrent sessions allowed by the SESSION_PER_USER clause
//          of the user profile.
//  *Action: End one or more concurrent sessions or ask the database
//           administrator to increase the SESSION_PER_USER limit of
//           the user profile.

You need to adjust the SESSIONS_PER_USER of the profile the user is assigned to.

alter profile general_users LIMIT SESSIONS_PER_USER <num> | default | unlimited;

num = a fixed number of sessions that is the upper bound for the number of concurrent session the user can create
Default = the user is subject to the limits on those resources defined by the DEFAULT profile
unlimited = the user assigned this profile can use an unlimited amount of this resource