| Fragmentation [message #57424] |
Sun, 15 June 2003 14:12  |
James Briar
Messages: 72
Registered: January 2002
Location: Morden (South London)
|
Member |
|
|
I'm trying to down size a tablespace data file from 2.5 Gig to 1.5 Gig. I've used the dba_free_space view to check the free space and there's just over 1.5 Gig free and i want to reduce the datafile by 1 Gig. To do this i issued the command :-
alter database
datafile '/u05/oradata/dev/data2.dbf'
resize 1500M;
This failed with ORA-03297 file contains used data beyond requested resize value (so i assume the tablespace needs defragmenting). To defragment the tablespace i tried making the tablespace read only and then doing a tablespace export, then doing an import again. The export failed with ORA-29341 the transportable set is not self-contained (the indexes are in another tablespace). Is there any other way i can defragment the tablespace datafile?
We're using Oracle 8i on a Sun solaris unix platform.
|
|
|
|
|
|
| Re: Fragmentation [message #57434 is a reply to message #57425] |
Mon, 16 June 2003 02:57  |
James Briar
Messages: 72
Registered: January 2002
Location: Morden (South London)
|
Member |
|
|
Thanks for your reply Mahesh,
The tablespace is not transportable and partitioning is not in use. I'll have a look at dbms_tts.
Thanks.
P.S - The reason i want to downsize the datafile is because the system dump (which uses the unix cpio command) is failing because of the datafile being too big. I just wanted to split the data file in two. I just wanted to defrag the datafile so that, hopefully i could shrink it by 1Gig then create another datafile of 1Gig.
|
|
|
|
| Re: Fragmentation [message #57438 is a reply to message #57434] |
Mon, 16 June 2003 06:03 |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
if the tablespace is not transportable there is no need to look into dbms_tts.
But i beleive, ORA-29341 is something related to transportable tablespaces only.
Let me cross check it.
there are different options in export utility.
try
----------------------------------------------------------------------
exp help=y
----------------------------------------------------------------------
to view the options.
and did you try to take an export with indexes=n
idea is to take an export without indexes
import drop the tables.
import the tables back and recreate the indexes ( make sure you have a ddl of indexes).
[Updated on: Mon, 21 September 2009 13:38] Report message to a moderator |
|
|
|
| Re: Fragmentation [message #57443 is a reply to message #57438] |
Mon, 16 June 2003 08:57 |
James Briar
Messages: 72
Registered: January 2002
Location: Morden (South London)
|
Member |
|
|
Thanks for your reply again Mahesh,
I've now fixed my problem by exporting the user's schema, dropping the user, resizing the data file down by 1 Gig, creating another datafile in the tablespace of 1 Gig, creating the user again and finally doing an import. Thanks for your notes on dbms_tts and transportable tablespaces etc, i'll keep this for future reference. P.S - I did not try using index=n in the export option (i'll remember this for next time).
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
