Operating System - HP-UX
1748275 Members
3957 Online
108761 Solutions
New Discussion юеВ

Question in regards to table vs tablespace

 
SOLVED
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
6 REPLIES 6
Jeanine Kone
Trusted Contributor
Solution

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).

Jeanine
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.

Rgds
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

Hello,
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 tablespace.so when he creates a table it gets stored there.

Thanks
Printaporn_1
Esteemed Contributor

Re: Question in regards to table vs tablespace

before create table you need to create user like:

CREATE USER test IDENTIFIED BY test
DEFAULT TABLESPACE DATA1
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT;

Then create a table .
CREATE TABLE TEST1
(
col1 VARCHAR2(20),
col2 VARCHAR2(20),
)
TABLESPACE DATA2;

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.
Yogeeraj_1
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
minextents)

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


Hence,

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
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)