-
does any one can tell me:
How to reduce the data file size of a tablespace.
thanks
-
ALTER DATABASE TEMPFILE
'E:\ORACLE\ORADATA\BSG1\TEMPORARY_DATA.ORA'
RESIZE 40M;
You may want to do the following
ALTER TABLESPACE TABLESPACENAME DELETE UNUSED;
ALTER TABLESPACE TABLESPACENAME COALESCE;
-
Actually it should be
ALTER DATABASE
DATAFILE 'D:\ORADATA\BSG1\USER1.DBF' RESIZE 40M
I use locally managed tablespaces.
-
reduce tablespace data file size
Hi, gandolf989,
please verify it for me:
1. ALTER TABLESPACE users DELETE UNUSED;
2. ALTER TABLESPACE users COALESCE;
3. ALTER DATABASE DATAFILE 'D:\ORADATA\BSG1\USER1.DBF' RESIZE 10M;
is it the correct sequence?
thanks
-
Re: reduce tablespace data file size
Originally posted by jm
1. ALTER TABLESPACE users DELETE UNUSED;
wow is that a new command
-
Originally posted by gandolf989
ALTER TABLESPACE TABLESPACENAME DELETE UNUSED;
And I thought I knew all TABLESPACE related commands in Oracle :-)
-
Hi,
so what's the correct command and step?
all I know is use
ALTER DATABASE DATAFILE 'filename' RESIZE 10M;
in fact, my true question is:
if I have a tablespace was 5Mb initally, later someone create some "hugh" tables to make the tablespace growing
to 500Mb, then those tables being dropped. I assume those space will be free.
can I use resize to set back to original size?
-
you can reduce the size of a datafile, providing there is NO data in the space you're removing (in the last 495 Mb in your example).
what you should do is :
drop table huge1;
drop table huge2;
...
once it's ok :
alter tablespace [tbs] coalesce;
and then :
alter database datafile 'your datafile' resize 5M;
if this command fails, then you certainly have data in the bad area, you should look for what it is by looking in dba_extents and dba_data_files ...
-
thanks, it is loud, clear and works.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|