ORA-01843: not a valid month

SQL> insert into rousrs values ('ABHAT','TMPSTG','2013-02-05 00:00:00');
insert into rousrs values ('ABHAT','TMPLSTG','2013-02-05 00:00:00')

ERROR at line 1:
ORA-01843: not a valid month

                                             
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

insert into rousrs values ('ABHAT','TMPSTG','2013-02-05 00:00:00');

1 row inserted.

Query for Table size along with Index and lobs

Query to get the above output:

SELECT Sub."Table_Name",

  sub."Number of Rows",

  sub."Number of Indexes",

  sub."Number of LOB Columns",

  sub."Index_Size" "Index_Size",

  sub."Table_Size" "Table_Size",

  sub."LOB_Size" "LOB_Size",

  sub."Index_Size"+sub."Table_Size"+sub."LOB_Size" "Total_Size_MB"

FROM

  ( WITH index_cnt AS

  (SELECT b.table_name,

    COUNT(b.index_name) "index_count"

  FROM user_indexes b

  GROUP BY b.table_name

  ),

  lob_cnt AS

  (SELECT c.table_name,

    COUNT(c.column_name) "lob_count"

  FROM user_lobs c

  GROUP BY c.table_name

  ),

  ind_sz AS

  (SELECT e.table_name,

    SUM(Bytes/1024/1024) "index_size"

  FROM user_segments d,

    user_indexes e

  WHERE d.segment_name = e.index_name

  AND d.segment_type LIKE 'INDEX%'

  GROUP BY e.table_name

  ),

  tab_sz AS

  (SELECT d.segment_name,

    SUM(Bytes/1024/1024) "table_size"

  FROM user_segments d

  WHERE d.segment_type LIKE 'TABLE%'

  GROUP BY d.segment_name

  ),

  lob_sz AS

  (SELECT e.table_name,

    SUM(Bytes/1024/1024) "lob_size"

  FROM user_segments d,

    user_lobs e

  WHERE d.segment_name = e.segment_name

  GROUP BY e.table_name

  )

SELECT a.table_name "Table_Name",

  NVL(a.num_rows,0) "Number of Rows",

  NVL(index_cnt."index_count",0) "Number of Indexes",

  NVL(lob_cnt."lob_count",0) "Number of LOB Columns",

  NVL(ind_sz."index_size",0) "Index_Size",

  NVL(tab_sz."table_size",0) "Table_Size",

  NVL(lob_sz."lob_size",0) "LOB_Size"

FROM user_tables a ,

  index_cnt,

  lob_cnt,

  ind_sz,

  tab_sz,

  lob_sz

WHERE a.table_name=index_cnt.table_name(+)

AND a.table_name  =lob_cnt.table_name(+)

AND a.table_name  =ind_sz.table_name(+)

AND a.TABLE_NAME  =tab_sz.segment_name(+)

AND a.table_name  =lob_sz.table_name(+)

  ) sub

ORDER BY 2 DESC nulls last; 

*** Courtesy to my friend Hari Krishna for writing this above query ***

Distinct multiple rows by clubbing relevant rows

  CREATE TABLE SDR_HIST
   ( "ID" NUMBER,
"REQ_DATE" DATE,
"RES_DATE" DATE
   )  ;
 
Insert into SDR_HIST (ID,REQ_DATE,RES_DATE) values (22322,to_date('15-JAN-15','DD-MON-RR'),null);
Insert into SDR_HIST (ID,REQ_DATE,RES_DATE) values (22322,null,to_date('16-JAN-15','DD-MON-RR'));
Insert into SDR_HIST (ID,REQ_DATE,RES_DATE) values (22322,to_date('16-JAN-15','DD-MON-RR'),null);
Insert into SDR_HIST (ID,REQ_DATE,RES_DATE) values (22322,null,to_date('18-JAN-15','DD-MON-RR'));
Insert into SDR_HIST (ID,REQ_DATE,RES_DATE) values (22322,to_date('18-JAN-15','DD-MON-RR'),null);
Insert into SDR_HIST (ID,REQ_DATE,RES_DATE) values (22322,null,to_date('20-JAN-15','DD-MON-RR'));
Insert into SDR_HIST (ID,REQ_DATE,RES_DATE) values (175276,null,to_date('09-DEC-14','DD-MON-RR'));
Insert into SDR_HIST (ID,REQ_DATE,RES_DATE) values (178693,null,to_date('12-FEB-15','DD-MON-RR'));
Insert into SDR_HIST (ID,REQ_DATE,RES_DATE) values (179016,to_date('26-NOV-14','DD-MON-RR'),null);
Insert into SDR_HIST (ID,REQ_DATE,RES_DATE) values (179016,null,to_date('26-NOV-14','DD-MON-RR'));
Insert into SDR_HIST (ID,REQ_DATE,RES_DATE) values (179016,to_date('26-NOV-14','DD-MON-RR'),null);
Insert into SDR_HIST (ID,REQ_DATE,RES_DATE) values (179016,null,to_date('27-NOV-14','DD-MON-RR'));

Data at glance
===============

Required Output
===============

Query to get the required output
================================
SELECT id,  MAX(req_date),  MAX(res_date)
FROM
  (SELECT id,    req_date,    res_date,
    COALESCE (
    CASE       WHEN req_date IS NOT NULL      THEN rn1    END,
    CASE      WHEN res_date IS NOT NULL      THEN rn2    END )rn
  FROM
    (SELECT id,      req_date,      res_date,
      row_number() over (partition BY ID,
      CASE         WHEN res_date IS NULL         THEN 1      END order by id,rownum) rn1,
      row_number() over (partition BY ID,
      CASE         WHEN req_date IS NULL        THEN 1      END order by id,ROWNUM) rn2
    FROM SDR_HIST
    WHERE req_date IS NULL    OR res_date    IS NULL
    )
  )
GROUP BY rn,
  id
ORDER BY 1,2,3;

Excel Macro to fake no idle

Public Sub StupidSendKeys()
    Dim mywaittime As Single
    mywaittime = 2.5
  Dim cmt As Comment
  Dim i As Integer
  Set cmt = ActiveCell.Comment
  For i = 1 To 20
  If cmt Is Nothing Then
    SendMyKeys ("A")
    PauseSec (mywaittime)
    SendKeys "{ENTER}"
  End If
  If i = 18 Then
  i = 1
  End If
  Next i
  End Sub

Public Sub SendMyKeys(ToSend As String)
    Application.SendKeys ToSend, True
End Sub

Public Sub PauseSec(TimePause As Single)
    Dim OpenFiles As Long
    Dim x As Single
    x = Timer
    OpenFiles = DoEvents()
    While Timer - x < TimePause
    Wend
End Sub
   

To read the contents of iso file in linux

first mount it as below

1) mkdir /<new temp directory>
ex: mkdir /tmp-iso

2) mount <your iso file> to newly created directory
ex: mount your.iso /tmp-iso -o loop

now go to cd /tmp-iso directory to read the iso file contents


after the work done, you can unmount it as below

1) umount your.iso

Architecture

The above Digital image courtesy of the ss64.com

SQL Plan Management in Oracle Database 11g

http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-management-11gr2-133099.pdf