Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
Showing results for 
Search instead for 
Did you mean: 

Question in regards to table vs tablespace

Go to solution
Deanna Tran
Frequent Advisor

Question in regards to table vs tablespace

I already created the database with all the tablespace rbs etc...etc.... that is part of the creation of the database. However, in order for me to do any type of transaction I have to create an Oracle Database Object known as "table". Is this right? My questions are the following : When creating the oracle database object table, how does it know which tablespace to use? The reason,I asked when I tried to use desc command, it kept saying I do not have the schema? Thank you
Jeanine Kone
Trusted Contributor

Re: Question in regards to table vs tablespace

You need to do a "create table" command to create a table. You can find the exact syntax in the SQL Language reference (on your documentation CD). You can specify the tablespace name as part of the create table command (if you leave it out it will use the deafult tablespace for the user you are logged in as). You can than "insert" into that table to load data.

If you are logged in as the same user who owns the table - you can then just do a "desc table_name" to see it. If the table is owned by a user other than the one you are currently logged in as - then that user will need "select" permissions in order to see the table (and will also need to preface it with owner.table_name or have a synonym created).

Alexander M. Ermes
Honored Contributor

Re: Question in regards to table vs tablespace

Hi there.
Deanna, the tablespaces are the physical files, where you store the data.
The tables are logical items, which have to be created in the tablespaces.
If you check the syntax for the create table, you will see, that there is a step for the tablespace in it.

Try to get some training manuals for dba. You might find some valuable stuff in there. O'Reilly has got some good books on that.

Alexander M. Ermes
.. and all these memories are going to vanish like tears in the rain! final words from Rutger Hauer in "Blade Runner"
T G Manikandan
Honored Contributor

Re: Question in regards to table vs tablespace

In your unix whenever the user creates a file,it is stored in the home directory of the user or he can create the file where he has permissions.Just like that whenever you create a table the table is created in the users default tablespace.
As like when you are creating user in unix you give him a default directory,when creating user in a oracle you allocate him to use a default when he creates a table it gets stored there.

Esteemed Contributor

Re: Question in regards to table vs tablespace

before create table you need to create user like:


Then create a table .
col1 VARCHAR2(20),
col2 VARCHAR2(20),

if not put the tablespace cause the table will goto default tablespace that say when create user.
enjoy any little thing in my life
Deanna Tran
Frequent Advisor

Re: Question in regards to table vs tablespace

Thank you all for helping me to clarify this.
and thank you for your time.
Honored Contributor

Re: Question in regards to table vs tablespace

Hello Deanna,

I would recommend you to create your tablespaces properly first.
YOU MUST use Locally Managed Tablespaces.

The performance gain is in the allocation of new extents. I love LMT's not only
cause they are faster at allocating extents but because they make fragmentation
a thing of the past -- it just doesn't happen.

This is the future of tablespaces. Especially in 9i when we do to freelists what we did to
tablespaces (automatic space management that obsoletes freelists, freelist
groups and pctused in the way LMTS obsoleted pctinc, initial, next, and

What you need to do is set up three tablespaces -- small, med and large. Use a
small extent size on small (say 64k), a med extent size on med (say 512k) and a
big extent size on big (say 5m).

Now, any object you expect to be less then say 50meg, keep it in small. It'll
always have objects with less then 1,000 extents (which is just fine).

Any object you expect to be less then say 250m (but safely up to 500m), put in
medimum. They'll have 500-1000 extents max.

Any object you expect to be 4gig or less -- put into large.

Now, you only have to consider objects bigger then 4gig and treat them special


create tablespace tbs_lmt_small
datafile '/d08/oradata/oradb/tbs_lmt_small01.dbf' size 30M
extent management local uniform size 64K;

create tablespace tbs_lmt_medium
datafile '/d08/oradata/oradb/tbs_lmt_medium01.dbf' size 500M
extent management local uniform size 1M;

create tablespace tbs_lmt_large
datafile '/d08/oradata/cmtdb/cmtpers_lmt_indx_medium01.dbf' size 1500M
extent management local uniform size 128M;

Then create your tables:
example: create table emp(empno varchar2(10)) tablespace tbs_lmt_medium;

Hope this helps
Best Regards
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)