Oracle time/date format

Date or time in Oracle are not always displayed in the format you want.

In order to change this:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MON/YYYY HH24:MI:SS';

select sysdate from dual;

From then, your session will display the date in the desired format.

For more details on the format syntax:

http://www.dba-oracle.com/t_nls_date_format_sysdate.htm

Here's an example of select dealing with Date format, for select with from and to date

select * from OPBI.prodapp_gen_machine_stats where 

hostname = 'sew3'  and collection_timestamp between to_date('2011/10/02 08:00:00' ,'yyyy/mm/dd hh24:mi:ss') and to_date('2011/10/05 20:00:00','yyyy/mm/dd hh24:mi:ss') order by collection_timestamp

Memory Disk usage of user in tablespace database

http://vsbabu.org/oracle/sect03.html


This following SQL display the usage of each table / user within the BE_LARGE_DATA tablespace.
Useful if you want to find the motherf*** which stucked your database.

select OWNER,
sum(BYTES)
from dba_segments t
where TABLESPACE_NAME like 'BE_LARGE_DATA'
and OWNER not in ('SYS','SYSTEM')
group by t.OWNER;

or in more detailed way :

select OWNER,
SEGMENT_NAME,
SEGMENT_TYPE,
TABLESPACE_NAME,
BYTES
from dba_segments
where TABLESPACE_NAME like 'BE_LARGE_DATA'
and OWNER not in ('SYS','SYSTEM')
order by OWNER, SEGMENT_NAME

SQL Tips

Select disctinct

How to count distinct rows within table

select count(*) from ( select distinct d1, d2, d3 , d4, d5, d6, d7, d8, d9 from in_all )

Update/Insert table

How to pass data from table to table:

For ex: pass data from table_b to table_a

truncate table TABLE_A
insert into TABLE_A select * from TABLE_B

truncate is different than the delete from TABLE command. My colleagues says that truncate command s faster ...