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 dont
Received on Tue Jul 11 2000 - 06:56:52 CDT