Friday, October 28, 2016

Solved - java.sql.SQLException: ORA-02396: exceeded maximum idle time, please connect again

This kind of error is usually caused by the IDLE_TIME limit set to database users.

SQL> select * from USER_RESOURCE_LIMITS;

RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME 60
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED

9 rows selected.

Set the IDLE_TIME to UNLIMITED shall fix this problem if the user does not have a profile. Note: this will set the default idle_time for all users to UNLIMITED.

Very often, the IDLE_TIME limit for a user is set in a profile assigned to the user. To change the IDLE_TIME in a user's profile, do the following.

1. Find the user's profile

SQL> SELECT USERNAME, PROFILE, ACCOUNT_STATUS FROM DBA_USERS;

USERNAME PROFILE ACCOUNT_STATUS
------------------------------------- ---------------------------------- ---------------------------------
ORAUSER1 DEFAULT OPEN

ADMIN1 ADMIN_USER OPEN

2. View the profile

SQL> SELECT * FROM DBA_PROFILES where profile = 'ADMIN_USER' and RESOURCE_NAME='IDLE_TIME';

PROFILE RESOURCE_NAME RESOURCE LIMIT COM
-------------------------------------------------------------------------------------------------------------
ADMIN_USER IDLE_TIME KERNEL 60 NO

3. Change the LIMIT of IDLE_TIME

SQL> Alter profile ADMIN_USER limit IDLE_TIME UNLIMITED;

or

SQL> Update DBA_PROFILES set LIMIT='UNLIMITED' where profile = 'ADMIN_USER' and RESOURCE_NAME='IDLE_TIME';

4. Restart Database

-----------------------------------------------------------------------------------------------------------------

Watch the blessing and loving online channel: SupremeMasterTV live

If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book for free here.

Tuesday, October 25, 2016

make: pg_config: Command not found

To solve this problem, add the bin directory containing the pg_config to your PATH. For example, add /usr/pgsql-9.5/bin to your environmental variable PATH.

-----------------------------------------------------------------------------------------------------------------

Watch the blessing and loving online channel: SupremeMasterTV live

Thursday, October 6, 2016

postgresql alter table hangs

When the SQL, "Alter table Fruit add Column Location VarChar(30))", is executed at the psql terminal, it hangs.

It turns out that there is a lock on a table. The SQL below helps find all the locks in your database.

SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_statement, blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;

Then use the SQL below to kill the blocking process.

select pg_terminate_backend(<blocking_pid>);

Reference:

Lock Monitoring

---------------------------------------------------------------------------------------------------

Wednesday, October 5, 2016

crystal reports group sum and total sum

It is very often that a report displays data grouped by some category, displays a group summary at the end of each group and a total summary at the end of the report.

Following is about how to create the formula fields to accumulate the counts and display them.

If it is a simple account such as the sum or count of a single field, you can insert a summary by

1. Click the Insert on the top menu and select Summary.
2. In the Insert Summary window, choose the field you would like to use, the type of calculation you would like to perform on this field, and the location you want this summary to be on your report.
3. Click OK to close the window.
4. Find the summary on your report, right click on it and select Format Field.
5. Click each tab on the popup Format Editor window to specify the appearance of it.

However, if you want to create a complicated counter or summary that involve more than one field or complex logic, you may following the guide below to create it.

1. Open the Formula Workshop. From top menu, click Report, then select Formula Workshop
2. Create a reset formula, which will set the count to zero when a new group starts to display. Right click on Formula Fields and select new, enter the formula name, and click OK. Enter the following into the lower-right pane.

Shared Numbervar recordCount;
recordCount := 0;

For example you want to count on the records which have been created or updated, you can set it like this.

  Shared Numbervar countInserted;
Shared Numbervar countUpdated;
countInserted:=0;
countUpdated :=0;

Place the formula in group header.

3. Create the increment formula.

In general, it is like this.

Shared Numbervar recordCount;
recordCount := recordCount + 1;

As in the above example, it will look like this.

  Shared Numbervar countInserted;
  Shared Numbervar countUpdated;

if(not isModified({<createDate>}, {<modifyDate>})) then
countInserted:=countInserted+1;
else
  countUpdated:=countUpdated+1;

countInserted;
countUpdated;

Add the formula to the detail section.

4. Create the display formula(s).

In general

Shared Numbervar recordCount;
Shared Numbervar totalRecordCount;
totalRecordCount := totalRecordCount + recordCount;
recordCount;

As in the example, you need to created two display formulas, one for the newly created records and one for the updated records.

Add the formula(s) to the group footer.

5. Create the display total formula(s).

In general

Shared Numbervar totalRecordCount;
  totalRecordCount;

As in the example, create two display total formulas, one for the created and one for the updated records.

Add the formula(s) to the report footer.

Reference:

1. How to count number of grouped rows in the Crystal Report

---------------------------------------------------------------------------------------------------