Re: Need a SQL

From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Thu, 10 Jan 2013 08:47:56 -0800 (PST)
Message-ID: <1357836476.38033.YahooMailNeo_at_web121601.mail.ne1.yahoo.com>

A more complete table description would be helpful.  That being said possibly this is what you want:
 

SQL> select max(jobdate) jobdate,srvrname,dbname,tsname,filename,allocatedspace,freespace
  2  from tablespace_usage
  3  where to_number(to_char(jobdate, 'D') ) = 7
  4  group by srvrname,dbname,tsname,filename,allocatedspace,freespace;
 

JOBDATE   SRVRNAME                  DBNAME     TSNAME          FILENAME                                       ALLOCATEDSPACE       FREESPACE
--------- ------------------------- ---------- --------------- --------------------------------------------- --------------- ---------------
09-FEB-13 SMORG                     smedley    SMORF           C:\ORADB\ORADATA\SMEDLEY\SMORF01.DBF                104857600       103809024
09-FEB-13 SMORG                     smedley    USERS           C:\ORADB\ORADATA\SMEDLEY\USERS01.DBF              34332999680     34331820032
09-FEB-13 SMORG                     smedley    SYSTEM          C:\ORADB\ORADATA\SMEDLEY\SYSTEM01.DBF              1887436800         9175040
09-FEB-13 SMORG                     smedley    UNDOTBS1        C:\ORADB\ORADATA\SMEDLEY\UNDOTBS01.DBF             9882828800      9785049088
09-FEB-13 SMORG                     smedley    SYSAUX          C:\ORADB\ORADATA\SMEDLEY\SYSAUX01.DBF              4424990720      2773614592
09-FEB-13 SMORG                     smedley    INDX            C:\ORADB\ORADATA\SMEDLEY\INDX.DBF                   524288000       523239424

 

6 rows selected.
 

SQL>
My thoughts are that this table is populated daily so all you really need are the values from the most recent Saturday run.

If this isn't what you had in mind please clarify your question so a better answer can be provided.

David Fitzjarrell

---

From: oracledba <oracledba71_at_gmail.com> To: Oracle-L_at_freelists.org
Sent: Wednesday, January 9, 2013 12:54 PM Subject: Need a SQL

All,
I am a newbie to Oracle.
Can someone write a SQL to show tablespace's growth weekly like week1,week2,week3,week4 of each month?
The table has the following columns and populated everyday.

jobdate,srvrname,dbname,tsname,filename,allocatedspace,freespace

Thanks

--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jan 10 2013 - 17:47:56 CET