Maximun length of identifier in Oracle

The maximum length of an identifier in oracle is 30 characters. You cannot create an identifier beyond this limit

Display date with timestamp in sqlplus

If you have column of type date in your table,
and  if you want to query it in sqlplus like below, it will only display the date without timestamp.

select salesdate from sales;
Output:
SALESDATE
---------

31-DEC-13





31-AUG-13


9 rows selected.


In order to display date with timestamp also, you have to use to_char() and format the output as below

select to_char(salesdate,'dd/mm/yyyy hh24:mi:ss') from sales;
TO_CHAR(SALESDATE,
-------------------

31/12/2013 06:30:00





31/08/2013 06:30:00


9 rows selected.

Convert between long(java) and Date(Oracle):

Do you need to convert between Date in oracle and long in java, you can use the following technique to achive that.

To convert from long value to Date:

oracle_date date := (in__date / (24 * 60 * 60 * 1000)) + to_date('01/01/1970','mm/dd/yyyy');

To convert from Date to long value:

long_date date := (in__date - to_date('01/01/1970','mm/dd/yyyy') ) * (24 * 60 * 60 * 1000);

How to invoke/run a pl/sql function from sqldeveloper

I wanted to run a pl/sql function from sqldeveloper and want to unit test it.
That function returns a sys_refcursor, which I would like to print it and see the result.

After searching for long time, I found the following two ways which worked for me.

My function spec is as below

function get_data(
  in_abs_start_date    in  int,
  in_abs_end_date      in  int
) return sys_refcursor;

And I have used the following two methods to invoke this pl/sql function from sql developer.

1)

variable v_ref_cursor refcursor;

exec :v_ref_cursor := package_name.get_data(in_abs_start_date =>1375209000000,in_abs_end_date =>1375295400000);

print :v_ref_cursor


2)

SELECT package_name.get_data(in_abs_start_date =>13752090000,in_abs_end_date =>1375295400000) FROM dual

free - memory management, buffers and cache, swap in linux

"free" is a command very often used to check the memory status of linux server. But many people misinterpret the output of it.
For example I got an alarming ticket saying our application has used almost all of the memory in production. They gave us the below "top" commasn output and claimed our application is eating all the memory though the load is less.

Mem:66000840k total, 65825700k used,175140k free,201104k buffers
Swap: 20972848k total,718156k used, 20254692k free, 30351488k cached

Note: top and free, both can be used to check memory and its output is almost same.

They claimed that out of 64GB of total memory, our application has used most of it leaving only 171 MB free to use.
This is what most of the people think if they are not sure on how to interpret the "free" result.

Actiually, Linux will noramlly allocate the memory to application as and when it is required. Also, if the total available memory is not used completely for some time, then that memory will used by Linux for caching. This includes the page cache, inode caches, etc. This is good and it will improve the overall system performance. And if any time, if the application requires more memory, then this cache memory will be released to the application.

So, we have to calculate the actual free memory = free + buffers + cache.
In the above case, it equates to ~ 30 GB of free memory.

This will be more clear if we look at the "free" command output. Following is an output taken from my fedora desktop.

[test@Vijay]# free -m
             total       used       free     shared    buffers     cached
Mem:          3835       3536        298          0         58        789
-/+ buffers/cache:       2688       1146
Swap:         5887        129       5758

Here in the first line, along with total, used , free columns you can also see how much memory is buffered and cached.
But hold on, whats that 2nd line implies. it again says buffer/cache. Many people would have got these question...see below.

line 2, used value (2688) = line 1 used (3536) - line 1 cached(789) - line 1 buffers (58)
line 2, free value (1146) = line 1 free (298) + line 1 cached (789) + line 1 buffers (58)

i.e., Line 2 gives us the actual memory used and actual free meory available in the system.

Line 3, gives us the swap usage.
why my swap space is used when enough free memoey available?
If you get the above question then the answer is below.

As we have seen above, our unused memory will used by Linux for caching which in turn will improve the overall system performance.
Consider at one point of time, all the free memory available (including the cache) is allocated to applications. And there is no memory for caching by Linux.

Linux is so intelligent here... It will re allocate some of the unused application's memory from RAM to swap memory. And that freed memory will be used for caching. Note clearly that only the unused memory from RAM will be moved to swap memory. This is the reason we see swap is being used eventhoug we have free memory.

Can I control the swap memory ?
Oh..yes...linux has provision for it also. You can use the "swappiness" setting available. You can check the swappiness with the following command

[test@Vijay]# cat /proc/sys/vm/swappiness
60

In my fedora, the default value is set to 60.

You can also switch off your swap memory by the following command.
swapoff -a

But you got to be very careful when you use the above two commands to control the swap behaviour as it affects the system performance to a great extent.