Shows blocks vs empty blocks for T tables. This does not cover lob
segments, indexes, lob indexes.
select a.segment_name,
a.blocks,
b.empty_blocks,
case when b.empty_blocks = 0 then 100
else b.empty_blocks / a.blocks
end as PCT_USED,
(select name from arschema where SUBSTR(a.segment_name, 2, LENGTH(
a.segment_name)) = TO_CHAR(schemaid))
from user_segments a,
user_tables b
where a.segment_name = b.table_name and
b.table_name like 'T%'
order by pct_used desc
/
Not 100% sure, but you probably want to estimate/generate statistics for all
the T tables before making decisions based on the results of this query.
Also, this may or may not be accurate.
Axton Grams
On Nov 26, 2007 2:38 PM, Ravi <[EMAIL PROTECTED]> wrote:
> Axton: just one another thing. Is there a way to find which remedy table
> is causing the high high-water-mark. The reason I ask is I donot want to
> blindy truncate all remedy tables b'cos some of them might have useful
> data. For example T22 has user information in it. Thatz the reason i was
> trying to retain some data and run the other query.
>
> Thanks
> Ravi
>
> Axton wrote:
> > ** This example, if I remember correctly, was to retain some data and
> > reset the high water marks. If you have deleted all the data, it's
> > simpler, just truncate the tables.
> >
> > You can get the table names based on the schemaid:
> >
> > select schemaid from arschema where name = &schemanam
> > /
> >
> > You can then get the table names from the oracle data dictionary:
> >
> > select table_name from user_tables where table_name like '_<schemaid>'
> > or table_name like '_<schemaid>C%'
> > /
> >
> > Once you've identified the tables, truncate them. At a minimum, you
> > will have a B, T, and H table.
> >
> > truncate table T99
> > /
> > truncate table H99
> > /
> > truncate table B99
> > /
> >
> > Axton Grams
> >
> > On Nov 26, 2007 1:53 PM, Ravi <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>>
> > wrote:
> >
> > Axton: I saw your example in this posting. This example has for a H
> > table. Is the following correct for T and B tables? I am using
> Oracle.
> >
> > create table H75_tmp as select * from H75 where entryid in (select
> c1
> > from T75);
> > truncate table H75;
> > insert into H75 select * from H75_tmp;
> > drop table H75_tmp;
> >
> > create table T75_tmp as select * from T75;
> > truncate table T75;
> > insert into T75 select * from T75_tmp;
> > drop table T75_tmp;
> >
> > create table B75_tmp as select * from B75;
> > truncate table B75;
> > insert into B75 select * from B75_tmp;
> > drop table B75_tmp;
> >
> >
> > Thanks
> > Ravi
> > Axton wrote:
> > > ** You have to run a truncate if you want to reset the high water
> > > marks and release the storage. It's ok to do things in sql
> against
> > > the db, you just need to have an understanding of what you are
> doing
> > > and how it impacts the db, and in turn the applications.
> > Ignorance is
> > > bliss, unfortunately it doesn't get you far.
> > >
> > > A truncate is like 'delete all', plus some other operations. So
> > long
> > > as you clear the T, H, B, B#C tables when truncating one, you
> > will not
> > > have a problem.
> > >
> > > Axton Grams
> > >
> > > On Nov 26, 2007 11:41 AM, Ravi <[EMAIL PROTECTED]
> > <mailto:[EMAIL PROTECTED]>
> > > <mailto: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>>> wrote:
> > >
> > > Hi: The diskspace keeps showing high usage even after I have
> > > deleted all
> > > records from all of the forms in my remedy instance. DBA is
> > asking
> > > me to
> > > run a truncate. But I remember being told in remedy training
> not
> > > do do
> > > anything from sql prompt. I saw one email
> > > (
> >
> http://listserv.rbugs.com/cgi-bin/wa.exe?A2=ind0709&L=arslist&P=R220861&I=-3&X=23F4FB0752F26A51E6&Y=ravinm%40cox.net
> > <
> http://listserv.rbugs.com/cgi-bin/wa.exe?A2=ind0709&L=arslist&P=R220861&I=-3&X=23F4FB0752F26A51E6&Y=ravinm%40cox.net
> >
> > > <
> >
> http://listserv.rbugs.com/cgi-bin/wa.exe?A2=ind0709&L=arslist&P=R220861&I=-3&X=23F4FB0752F26A51E6&Y=ravinm%40cox.net
> > <
> http://listserv.rbugs.com/cgi-bin/wa.exe?A2=ind0709&L=arslist&P=R220861&I=-3&X=23F4FB0752F26A51E6&Y=ravinm%40cox.net
> >>)
> > > which does run steps from the sql prompt. Can someone
> > confirm if i can
> > > actually run these (copy to a temp table, truncate the
> > original table
> > > and copy the stuff back to the orginal table) from sql
> > prompt without
> > > impacting Remedy operations?
> > >
> > > TIA
> > > Ravi
> > >
> > >
> >
> _______________________________________________________________________________
> > > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
> > <http://www.arslist.org>
> > > <http://www.arslist.org>
> > > Platinum Sponsor: www.rmsportal.com
> > <http://www.rmsportal.com> <http://www.rmsportal.com>
> > > ARSlist: "Where the Answers Are"
> > >
> > >
> > > __20060125_______________________This posting was submitted with
> > HTML
> > > in it___
> >
> >
> _______________________________________________________________________________
> > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
> > <http://www.arslist.org>
> > Platinum Sponsor: www.rmsportal.com <http://www.rmsportal.com>
> > ARSlist: "Where the Answers Are"
> >
> >
> > __20060125_______________________This posting was submitted with HTML
> > in it___
>
>
> _______________________________________________________________________________
> UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
> Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
>
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
