To find the Approx. Data within a Table
1) analyze table <name> compute statistics
2) select avg_row_len from sys.dba_tables where table_name =<name> to get
Data in 1 row inBYTES
3) select count(*) from <name>
4) Multiply count(*) from avg_row_len to get the Data of that Table
5) analyze table <name> delete statistics to Revert back
6) Compare with EXTENT Sizes
LIST PLEASE CORRECT
> -----Original Message-----
> From: Devendra Koppol [SMTP:dkoppol_at_tatatel.co.in]
> Sent: Tuesday, July 11, 2000 6:22 PM
> To: VIVEK_SHARMA; 'Devendra Koppol'
> Cc: oracledba_at_quickdoc.co.uk; 'ORACLE-L_at_fatcity.com'
> Subject: RE: TEMP TABLESPACE PROBLEM- Urgent
>
> great ! I got some idea.
>
> But I am looking for the query which can reuse the space ALLOCATED for
> the
> EXTENT which has become empty by deleting the rows.
> Secondly,
> How to find the free space within the ALLOCATED EXTENT so as to LIMIT that
> EXTENT to its appropriate size. ?
>
> Regards
> Dev
> > -----Original Message-----
> > From: VIVEK_SHARMA [SMTP:vivek_sharma_at_inf.com]
> > Sent: Tuesday, July 11, 2000 5:27 PM
> > To: 'Devendra Koppol'
> > Cc: oracledba_at_quickdoc.co.uk; 'ORACLE-L_at_fatcity.com'
> > Subject: RE: TEMP TABLESPACE PROBLEM- Urgent
> >
> >
> > Ans 1. Chunks of Contiguous UNAllocated (FREE) EXTENT SpaceS Anywhere in
> > the
> > Tablespace
> >
> > Ans 2. Because though the Data is Deleted from the ALLOCATED Extent ,
> the
> > Allocated Extent
> > Size Does NOT Change though there may be Empty Space Within it
> >
> > AIM - To Reduce the Size of the Existing Allocated EXTENTs
> EXAMPLE
> > below :-
> >
> > NOTE Check How the following applies to you & Revert back
> >
> > SQL> create table tmp2 (t2 number)
> > 2 tablespace tba_temp
> > 3 storage (initial 1M);
> >
> > Table created.
> >
> > SQL> select segment_name,extents,bytes,initial_extent
> > 2 from sys.dba_segments
> > 3 where segment_name like 'TMP2%';
> >
> > SEGMENT_NAME
> >
> --------------------------------------------------------------------------
> > --
> > ----
> > EXTENTS BYTES INITIAL_EXTENT
> > ---------- ---------- --------------
> > TMP2
> > 1 1064960 1048576
> >
> > 1 row selected.
> >
> > SQL> select * from sys.dba_free_space
> > 2 where tablespace_name like 'TBA_TEMP%'
> > 3 order by bytes;
> >
> > TABLESPACE_NAME FILE_ID BLOCK_ID BYTES
> BLOCKS
> > ------------------------------ ---------- ---------- ----------
> ----------
> > TBA_TEMP 39 1395 41009152
> 5006
> >
> >
> >
> > SQL> insert into tmp2 values(1);
> >
> > 1 row created.
> > SQL> commit;
> >
> > Commit complete.
> >
> > SQL> alter table tmp2 deallocate unused keep 10K;
> >
> > Table altered.
> > SQL> select segment_name,extents,bytes,initial_extent
> > 2 from sys.dba_segments
> > 3 where segment_name like 'TMP2%';
> >
> > SEGMENT_NAME
> >
> --------------------------------------------------------------------------
> > --
> > ----
> > EXTENTS BYTES INITIAL_EXTENT
> > ---------- ---------- --------------
> > TMP2
> > 1 32768 32768
> >
> > 1 row selected.
> >
> > SQL> select * from sys.dba_free_space
> > 2 where tablespace_name like 'TBA_TEMP%'
> > 3 order by bytes;
> >
> > TABLESPACE_NAME FILE_ID BLOCK_ID BYTES
> BLOCKS
> > ------------------------------ ---------- ---------- ----------
> ----------
> > TBA_TEMP 39 1269 42041344
> 5132
> >
> >
> > SQL> select * from tmp2;
> >
> > T2
> > ----------
> > 1
> > 1 row selected.
> >
> >
> > LIST PLEASE CORRECT
> >
> > > -----Original Message-----
> > > From: Devendra Koppol [SMTP:dkoppol_at_tatatel.co.in]
> > > Sent: Tuesday, July 11, 2000 2:27 PM
> > > To: VIVEK_SHARMA
> > > Cc: oracledba_at_quickdoc.co.uk
> > > Subject: RE: TEMP TABLESPACE PROBLEM- Urgent
> > >
> > > Hi sharma !
> > >
> > > I have some doubt in the query related to dba_free_space.
> > > 1. In this bytes cloumn gives only the last chunk of free space in the
> > > tablespace or anywhere in the tablespace ?
> > >
> > > 2..By deleting approx 1 crore of rows in the table,could not get any
> > free
> > > space ? how can we re-claim the space.(As its a very large table I
Received on Wed Jul 12 2000 - 02:02:45 CDT
