| data file management [message #69654] |
Wed, 13 February 2002 21:59  |
Manish Misra
Messages: 2
Registered: February 2002
|
Junior Member |
|
|
HI
i have many schemas in a tablespace
now say i dropped two of such schemas
but the data from the datafile doesnt go
how can i overcome such a situation when i have less storage capacity on the disk.
and also
help me out in reducing the size of the datafile
that has both existing and non-existing(dropped) schemas.
|
|
|
|
|
|
| Re: data file management [message #69668 is a reply to message #69658] |
Thu, 14 February 2002 20:05  |
Manish Misra
Messages: 2
Registered: February 2002
|
Junior Member |
|
|
by saying "but the data from the datafile doesnt go" i meant to say that when i drop a schema the size of the datafile associated to it is not reduced.
Does this means that droping a schema, does not physically delete that data in the datafile corresponding to that particular schema.
I would also like u to know that my 1 datafile is associated to more than 1 schemas.I now want to drop 1 of the schema's so as to recover some disk space.
Pls also help me out in dropping a datafile which is nolonger used by any user(schema).
(when can i physically delete a datafile without making my oracle unstable)
|
|
|
|
| Re: data file management [message #69676 is a reply to message #69654] |
Fri, 15 February 2002 08:28 |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
The tablespace and associated datafile(s) do not dynamically shrink. You could look at it as a container. When you remove items from the container the container doesn't shrink.
With that said, there are two ways to shrink a tablespace/datafile. Use exp/imp. Export the schema's that are in the tablespace; drop the schemas; recreate the tablespace with a smaller datafile and import.
Or you can use:
alter database datafile 'full_path_including_filename' resize n
where n would be a smaller number than the original size but not below the highwater mark. The database will not let you shrink it past the highwater mark. It will generate a ORA-03297 error but won't hurt anything.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
