RE: Oracle Sqlplus Report

  • From: "Le, Binh T." <Binh.Le@xxxxxxx>
  • To: "dmarc-noreply@xxxxxxxxxxxxx" <dmarc-noreply@xxxxxxxxxxxxx>, "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>
  • Date: Sun, 30 Apr 2017 01:20:53 +0000

Try this ….


with table_t as
(SELECT ob.owner owner, ob.object_name object_name, 
rowcnt,to_char(analyzetime,'yyyymmdd') analyzetime
   FROM sys.wri$_optstat_tab_history, dba_objects ob
  WHERE owner='INFODBA'
    and object_name in (select table_name from dba_tables where 
owner='DEMOUSER' and num_rows > 100000)
    and object_type in ('TABLE')
    and object_id=obj#
order by savtime asc)
select table_t.owner,
       table_t.object_name,
       max(case when table_t.analyzetime = '20170301' then table_t.rowncnt else 
null end) as "20170301",
       max(case when table_t.analyzetime = '20170302' then table_t.rowncnt else 
null end) as "20170302",
       max(case when table_t.analyzetime = '20170303' then table_t.rowncnt else 
null end) as "20170303",
       max(case when table_t.analyzetime = '20170304' then table_t.rowncnt else 
null end) as "20170304",
       max(case when table_t.analyzetime = '20170305' then table_t.rowncnt else 
null end) as "20170305"
  from table_t
group by table_t.owner,table_t.object_name;

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Sanjay Mishra
Sent: Saturday, April 29, 2017 1:42 PM
To: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
Subject: Oracle Sqlplus Report

I am running query to get the data in rows to be converted to column as shown 
below and so need help for SQL experts


SELECT ob.owner, ob.object_name, rowcnt,  analyzetime
FROM sys.WRI$_OPTSTAT_TAB_HISTORY, dba_objects ob
WHERE owner='INFODBA'
and object_name in (select table_name from dba_tables where owner='DEMOUSER' 
and num_rows > 100000)
and object_type in ('TABLE')
and object_id=obj#
order by savtime asc;

Original Format
================
OWNER      OBJECT_NAME              ROWCNT     ANALYZETIME
---------- -------------------     ---------------    
---------------------------
SAMPLE    DEMO1                     3233336      20170301
SAMPLE    DEMO1                     3233990      20170302
SAMPLE    DEMO1                     3234543      20170303
SAMPLE    DEMO1                     3235324      20170304
SAMPLE    DEMO1                     3235487       20170305

SAMPLE    DEMO2                      706595       20170301
SAMPLE    DEMO2                      706595       20170302
SAMPLE    DEMO2                      706595       20170303
SAMPLE    DEMO2                      706595       20170304
SAMPLE    DEMO2                      706595       20170305

SAMPLE    DEMO3                     9173498       20170301
SAMPLE    DEMO3                     9173498       20170302
SAMPLE    DEMO3                     9173498       20170303
SAMPLE    DEMO3                     9173498       20170304
SAMPLE    DEMO3                     9173498       20170305


Required Format
OBJECT_NAME    20170301     20170302    20170303    20170304    20170305
--------------    -------------- --------------     --------------     
--------------     --------------
DEMO1       3233336        3233990        3234543        3235324        3235324
DEMO2        706595        706595        706595        706595        706595
DEMO3        9173498        9173498        9173498        9173498        9173498


Tx
Sanjay
Notice of Confidentiality: **This E-mail and any of its attachments may contain
Lincoln National Corporation proprietary information, which is privileged, 
confidential,
or subject to copyright belonging to the Lincoln National Corporation family of
companies. This E-mail is intended solely for the use of the individual or 
entity to
which it is addressed. If you are not the intended recipient of this E-mail, 
you are
hereby notified that any dissemination, distribution, copying, or action taken 
in
relation to the contents of and attachments to this E-mail is strictly 
prohibited
and may be unlawful. If you have received this E-mail in error, please notify 
the
sender immediately and permanently delete the original and any copy of this 
E-mail
and any printout. Thank You.**

Other related posts: