Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: decrease tablespace size
On 20-Jan-98 15:49:02 Ulrich Waldt wrote:
>Hello everybody,
Hi!
>I need a little help with my tablspaces.
>What are the steps to decrease the amount of space for a tablespace?
>There are still data in the tablespace.
>First I thought about an Export, but there is no way to export a
>tablespace.
>We are running Oracle 7.1.6.2 on a unixsystem.
>Thanks for any help.
One possible solution could be to do something like
spool some_file
select table_name from all_tables
where tablespace_name='YOUR_TABLESPACE';
spool off
The resulting file could be copied into the TABLES clause of an export
controlfile.
As I'm writing this at home, I don't have a possibility to check, but
perhaps there are other things in your tablespace than tables. You should
check all_segments and all_objects for things in your tablespace which
also need to be exported.
Indexes will be recreated by import.
I think with some string magic, you could create the whole export control file with SQL*Plus. THe script that does this can be used for many tablespace related exports.
Another option is to migrate to Oracle 7.3 which is able to resize datafiles. ;-)
>Ulrich Waldt
Hope that helps,
Lothar
-- Lothar Armbrüster | lothar.armbruester_at_rheingau.netsurf.de Schulstr. 12 | lothar.armbruester_at_t-online.de D-65375 Oestrich-Winkel |Received on Tue Jan 20 1998 - 00:00:00 CST