Home » RDBMS Server » Server Utilities » data file management
data file management [message #69654] Wed, 13 February 2002 21:59 Go to next message
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 #69658 is a reply to message #69654] Thu, 14 February 2002 03:51 Go to previous messageGo to next message
Mike
Messages: 417
Registered: September 1998
Senior Member
Hi,

what do you exactly mean by:
"but the data from the datafile doesnt go"

have a look at it may help:
http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76956/tspaces.htm#786
http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76956/dfiles.htm#534

HTH
Mike
Re: data file management [message #69668 is a reply to message #69658] Thu, 14 February 2002 20:05 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: sqlldr unable to write bad file
Next Topic: Re: OCP Exam Papers
Goto Forum:
  


Current Time: Fri Mar 29 09:32:41 CDT 2024