Printable View
I've found something interesting in one of the posts.
I quote
'
The command is 'Drop Tablespace '. (then go and delete the corresponding datafiles at the OS level, because Oracle does not do this for you.)
'
Here is my question:
If you drop tables it realeases storage. My assumption was:
although OS doesn't delete the datafile, after dropping all tables belonging to that datafile if you look at the datafile through OS datafile's size becomes 0 or something close to that, right?
thank you.
Hi Mary,
That was my post.
Oracle doesn't do anything to the OS files after you drop a tablespace. It just doesn't consider them part of the database anymore. The files are still just as big as they were before you dropped them. Experiment and see for yourself!
:O
-John
I don't think you are right mary for a simple reason that, when you create tablespace you specify the size of the tablespace interms of datafiles size say '/... /xyz.dbf' 200M. So when you drop tablespace or objects of tablespace the size of datafile will not be zero but 200M. We know that tablespace/table is logical structure of database where as datafile is part of physical structure of database.
You're absolutely right shreddy. Thank you.
So, if you create a tablespace with a certain size datafile assigned, Oracle tells OS to creates a datafile that size even if there is nothing in it yet?
I am new and a little confused. But I am trying hard to learn.
This forum has been very helpful. Thank you guys.
Yes, There will be nothing in it. If you have something/lots of things(I mean data) in it, then the purpose of creating it (to store) will not be served as you are creating it to store data. OS Just allocates specified space (say this many tracks/cylinders) to datafile and database logical structure which is nothing but tablespace in this context will be having a pointer to this file.
Try analyzing this in 2 layers: Logical layer sitting on top of physical layer. Physical layer z what you can see with your eyes... logical layer which you can't see... I mean database objects(logical) like tablespaces, tables, indexes are gone when database is shutdown. But, Physical layer like Datafiles, controlfile, redologs,parameter files exists no matter database is up/down.
[Edited by sreddy on 12-28-2000 at 08:34 PM]
Thank you Sreddy for your last post. I wanted to investigate the matter myself. This is what I found in 'Understanding the Oracle Server' book by M.Krakovsky:
'To minimaze data fragmentation, the Oracle7 Server allocates space for data files when you create a database. If the Server did not preallocate space, and simply took disk space on an as-needed basis, it could not guarantee that related data-such as data used by a single application-would be stored contiguously.
In order for the Server to allocate space for a data file, you must tell it how much to allocate, using the CREATE DATABASE command. For example, to created a PUBREL database whith an initial data file of 20mb, you would enter a command containin the following lines:
SQLDBA> CREATE DATABASE pubrel
..
..
DATAFILE 'usr/pr/datafile1.dbf' SIZE 20M;
Correctly setting the size of each data file using the CREATE DATABASE command is important because you may not be able to change it later.'.....
I am sorry to say, Seems you didn't get the right book to explore Oracle Internals/Oracle Databse Architecture...
I mean I don't know under what context Author is talking about that. For explaination purpose they try to interpret things differently. Anyway, We can't get clear concept from a single book/ documentation. If we keep reading from different sources, we get fair idea on that topic. Just a suggetion....
[Edited by sreddy on 12-29-2000 at 11:48 PM]