Thanks for the prompt reply - This is a result of your query..
SQL> select tablespace_name, sum(bytes)/1024/1024 as total_free_mb, count(*)
as fragments, max(bytes)/1024/1024 as biggest_bit
2 from dba_free_space group by tablespace_name;
TABLESPACE_NAME TOTAL_FREE_MB FRAGMENTS BIGGEST_BIT
------------------------------ ------------- ---------- -----------
DECCABLOB 1993.78906 28 713.195313
DECCADATA 13.3203125 3 7.8671875
EXTRANET 6.4765625 1 6.4765625
INDX 9.9921875 1 9.9921875
MEDIAMAN 29.9765625 3 9.9921875
OEM_REPOSITORY 4.9921875 1 4.9921875
RBS 9.9921875 1 9.9921875
SYSTEM 146.78125 3 126.796875
TEMP 9.9921875 20 4.796875
USERS 7.1015625 1 7.1015625
10 rows selected.
and this is the result of a query i did relating to the troublesome
tablespace.
SQL> select a.tablespace_name, sum(a.bytes)/1000000 freespace, count (*)
extent,
2 b.bytes/1000000, (b.bytes - sum(a.bytes))/1000000, ((b.bytes
- sum(a.bytes))/b.bytes)
3 4 from dba_free_space a, dba_data_files b
where a.tablespace_name = b.tablespace_name
5 6 and a.file_id = b.file_id
and a.tablespace_name = 'DECCABLOB'
group by a.tablespace_name, b.file_name, b.bytes 7 8 ;
TABLESPACE_NAME FREESPACE EXTENT B.BYTES/1000000
(B.BYTES-SUM(A.BYTES))/1000000 ((B.BYTES-SUM(A.BYTES))/B.BYTES)
------------------------------ ---------- ---------- --------------- -------
----------------------- --------------------------------
DECCABLOB 221.872128 2 241.17248
19.300352 .080027174
DECCABLOB 109.641728 2 367.0016
257.359872 .70125
DECCABLOB 164.052992 2 524.288
360.235008 .68709375
DECCABLOB 749.002752 2 775.94624
26.943488 .034723395
DECCABLOB 746.684416 2 786.432
39.747584 .050541667
DECCABLOB 13.975552 2 31.45728
17.481728 .555729167
DECCABLOB 21.446656 2 31.45728
10.010624 .318229167
DECCABLOB 7.880704 2 52.4288
44.548096 .8496875
DECCABLOB 1.449984 2 73.40032
71.950336 .980245536
DECCABLOB 2.00704 2 115.34336
113.33632 .982599432
DECCABLOB 9.150464 2 167.77216
158.621696 .945458984
TABLESPACE_NAME FREESPACE EXTENT B.BYTES/1000000
(B.BYTES-SUM(A.BYTES))/1000000 ((B.BYTES-SUM(A.BYTES))/B.BYTES)
------------------------------ ---------- ---------- --------------- -------
----------------------- --------------------------------
DECCABLOB 7.790592 2 251.65824
243.867648 .969042969
DECCABLOB 9.699328 1 10.48576
.786432 .075
DECCABLOB 9.330688 1 10.48576
1.155072 .11015625
DECCABLOB 8.757248 1 10.48576
1.728512 .16484375
DECCABLOB 7.897088 1 10.48576
2.588672 .246875
16 rows selected.
all these datafiles have a max size set to 1gig.
I'm trying the import again, thanks for your imput so far very useful.
Tom
"Norman Dunbar" <Norman.Dunbar_at_lfs.co.uk> wrote in message
news:E2F6A70FE45242488C865C3BC1245DA703012DA8_at_lnewton.leeds.lfs.co.uk...
> Tom,
>
> Does the tablespace have 'lots of free space' in one chunk, or in lots ?
> If you are not using Locally Managed Tablespaces, then the INITIAL and
> NEXT requirements of any object must be provided from a single
> contiguous chunk of free space. Your import bombed out requesting 138 MB
> (roughly) and in a non-LMT this has to be from one single bit of free
> space.
>
> Check out the space you have by using :
>
> select tablespace_name,
> sum(bytes)/1024/1024 as total_free_mb,
> count(*) as fragments,
> max(bytes)/1024/1024 as biggest_bit
> from dba_free_space
> group by tablespace_name;
>
> If you find that the biggest bit is smaller than the size requested by
> your import, then that is your problem. You might find that ALTER
> TABLESPACE XXXXXX COALESCE will combine enough separate free bits into
> one - but then you might just get one extra extent added before it goes
> to pot again.
>
>
> On the other hand, assuming that there is space in a single chunk, and
> that that bit of space is bigger than what you need, then what is the
> PCTINCREASE setting for the tablespace. If this is not zero, then what
> is being reported in the error message is the size that the import
> requested, but Oracle may be trying to allocate a HUGE chunkl of NEXT
> extent due to (a) the PCTINCREASE settiing and (b) the current NEXT
> extent number - it quickly goes ballistic as extents are added. Trust
> me, I've been there and I know ! (Always a good example for trainee DBAs
> - why is there 250 MB of contiguous free space in that tablespace yet
> this table cannot allocate a NEXT extent of 64 KB ?)
>
>
> One word of warning, I've got absolutely no experience with LOBs - so
> the above may not apply. :o)
>
>
> Cheers,
> Norman.
>
> -------------------------------------
> Norman Dunbar
> Database/Unix administrator
> Lynx Financial Systems Ltd.
> mailto:Norman.Dunbar_at_LFS.co.uk
> Tel: 0113 289 6265
> Fax: 0113 289 3146
> URL: http://www.Lynx-FS.com
> -------------------------------------
>
>
> -----Original Message-----
> From: Tom [mailto:tomNOSPAM_at_teameazyriders.com]
> Posted At: Wednesday, December 04, 2002 1:28 PM
> Posted To: server
> Conversation: import problems Solaris 8 and 8.1.7 - urgent
> Subject: import problems Solaris 8 and 8.1.7 - urgent
>
>
> Hi,
>
> I'm trying to import a databaseas in an earlier post. I noticed one of
> the
> tables was empty when it imported overnight.
>
> I have tried to imp this table by itself and its pretty big and takes
> anout
> 2 hours. About 3 quarters of the way through it fails and rolls back,
> hence
> no rows, with the following error.
>
> IMP-00058: ORACLE error 1691 encountered
> ORA-01691: unable to extend lob segment XXXXXX.SYS_LOB0000003331C00002$$
> by
> 138254 in tablespace XXXXXXX
>
> IMP-00028: partial import of previous table rolled back: 1628 rows
> rolled
> back
>
> BUT there is tons of room in that tablespace - it spans 12 datafiles
> that
> are set yo be autoextend to a max size of a gig each.
>
> the listing of the datafiles is as follows....
>
> bash-2.03$ du -sk *
> 235656 blob01_SOL.dbf
> 358592 blob02_SOL.dbf
> 512272 blob03_SOL.dbf
> 758152 blob04_SOL.dbf
> 768392 blob05_SOL.dbf
> 30752 blob06_SOL.dbf
> 30752 blob07_SOL.dbf
> 51248 blob08_SOL.dbf
> 71736 blob09_SOL.dbf
> 112712 blob10_SOL.dbf
> 163936 blob11_SOL.dbf
> 245896 blob12_SOL.dbf
>
> any help appreciated
>
> thanks
>
> Tom
>
>
Received on Wed Dec 04 2002 - 08:27:43 CST
