hi jmodic

I tried yr suggestions performed steps and output i am giveing u below.

it creates the temp table with single field and initial extend 20k
but actual table like fdscheme with initial 20k does not create

Is there any other way to solve tablespace fregmentation

from Anuj Pathak
Hero Financial Services Ltd.


SQL> alter tablespace users2 coalesce ;

Tablespace altered.

SQL>
SQL> select file_id, bytes as free_chunk from dba_free_space
2 where tablespace_name = 'USERS2'
3 order by bytes desc;

FILE_ID FREE_CHUNK
--------- ----------
12 104857600
17 104855552
8 90478592
12 68157440
15 68157440
8 53114880
12 52424704
12 13617152
15 2713600
15 1054720
8 102400
8 30720

12 rows selected.

SQL> create table abc (acno char(7)) tablespace users2 storage (initial 20k);

Table created.

SQL> CREATE TABLE FDSCHEME (
2 SCHEMECD CHAR(3) CONSTRAINT PK_SCH PRIMARY KEY,
3 SCHEMENAME VARCHAR2(30) NOT NULL,
4 INTRATE NUMBER(5,2),
5 COMPOUND NUMBER(1),
6 IP_MON NUMBER(2),
7 PERIOD_S NUMBER(2) NOT NULL,
8 PERIOD_E NUMBER(2) NOT NULL,
9 USERID NUMBER(3),
10 CRE_DT DATE
11 )
12 tablespace users2 storage (initial 20k)
13 /
CREATE TABLE FDSCHEME (
*
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace USERS2