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;
�
�
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.
From: oracledba <oracledba71_at_gmail.com> To: Oracle-L_at_freelists.org
Sent: Wednesday, January 9, 2013 12:54 PM Subject: Need a SQL
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-lReceived on Thu Jan 10 2013 - 17:47:56 CET