OraFAQ Forum: Server Administration » Tablespace-Datafile-Tables ???

Home » RDBMS Server » Server Administration » Tablespace-Datafile-Tables ???

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Tablespace-Datafile-Tables ??? [message #160749]

Tue, 28 February 2006 04:26

Atul P
Messages: 61
Registered: June 2003
Location: Mumbai-Jakarta

Member

HI

I am not at all into DBA activties.
HOwever While reading through some scripts while new database installation and user creation etc came through few doubts.

1. When we create a TABLESPACE we define
Datafile, Size, Sotorage etc Parameters

2. When we create a USER we specify to which Tablespace the user
would belong to.

3. NOw while creating a Table again the TABLESPACE and Sotrage
Parameters are defined.

Doubt is While USER creation TABLESPACE already defined for same.

When making Tables in that User why do we need to do it again
for Individual Tables.
IN what aspects does it help.

If I do not do it then by default would the table belong
to the tablespace defined for that User ??
If yes then is it advisable.

Thks
Atul p

Report message to a moderator

Re: Tablespace-Datafile-Tables ??? [message #160782 is a reply to message #160749]

Tue, 28 February 2006 06:16

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>> User why do we need to do it again
Not a must.
Table level settings override tablespace level settings.
in 10g, you can even set a default tablespace for all your users.

Report message to a moderator

Re: Tablespace-Datafile-Tables ??? [message #160784 is a reply to message #160749]

Tue, 28 February 2006 06:21

orausern
Messages: 826
Registered: December 2005

Senior Member

>why do we need to do it again
for Individual Tables.
-One possible situation where you may want to do it is- when you are creating partitioned table--these tables can have their data in multiple tablespaces and in that case you may want to override the default setting.

Report message to a moderator

Re: Tablespace-Datafile-Tables ??? [message #160886 is a reply to message #160784]

Tue, 28 February 2006 22:21

Atul P
Messages: 61
Registered: June 2003
Location: Mumbai-Jakarta

Member

Hi
My COnfusion is

say if
1). I create a tablespace as below

CREATE TABLESPACE TBS_TEST DATAFILE
  'c:\dbsdata\TEST01.dbf' SIZE 50M autoextend on next 50      
     maxsize 200M default storage                                               
    (initial 10K next 10K pctincrease 1 minextents 1 maxextents unlimited);

2). Then I make User and Assign tables space to it.

create user TEST identified by TEST 
default tablespace TBS_TEST temporary tablespace temp quota unlimited on TBS_TEST;

3). Now when I am trying to create a Table in the user TEST
do I have to specify the TABLESPACE again with storage
Paramateres etc.

CREATE TABLE EMP ( 
  EMPH_CD          NUMBER (5)     NOT NULL, 
  EMPH_EMP_NAME    VARCHAR2 (30)  NOT NULL, 
  )
   TABLESPACE TEST
   PCTFREE 10
   PCTUSED 40
   INITRANS 1
   MAXTRANS 255
  STORAGE ( 
   INITIAL 98304
   NEXT 49152
   PCTINCREASE 1
   MINEXTENTS 1
   MAXEXTENTS 2147483645
   FREELISTS 1 FREELIST GROUPS 1 )
   NOCACHE; 
/

4). Or this should be sufficient

CREATE TABLE EMP ( 
  EMPH_CD          NUMBER (5)     NOT NULL, 
  EMPH_EMP_NAME    VARCHAR2 (30)  NOT NULL, 
  )
   TABLESPACE TEST

NOTE: All above codes are just for eg with some random values.

Thks & Rgds
Atul P

Report message to a moderator

Re: Tablespace-Datafile-Tables ??? [message #160953 is a reply to message #160886]

Wed, 01 March 2006 02:35

hellcat
Messages: 84
Registered: January 2006
Location: Zug, Switzerland

Member

if DEFAULT TABLESPACE is defined for a user then the user does not have to specify this tablespace in the create table statement (it will be created in that one), only if the user wants to create the object in a different one. if no DEFAULT TABLESPACE is defined then oracle creates the objects in the SYSTEM tablespace.

Report message to a moderator

Re: Tablespace-Datafile-Tables ??? [message #160980 is a reply to message #160953]

Wed, 01 March 2006 04:27

orausern
Messages: 826
Registered: December 2005

Senior Member

Yes , in majority of cases , you do not need to specify the storage clause. However regarding the pctfree and pctused parameters, please note that, sometimes it is BETTER TO specify-it depends.

The concept is that, whatever you have specified at the tablespace level will be applied to the tables that you create. But sometimes you may need to override this default behaviour. For e.g. the pctfree is set to 10 percent at the tablespace level. But you are creating a table, which is not going to have many updates and therefore , you want to specify pctfree of 5 for that table. So in such cases you may use the pctfree clause when you define the table.

So in majority of cases , it is fine not to specify the storage and pctfree parameters..but in some cases where you need to have a different settting for a perticular table, you need to specify.

Report message to a moderator

Previous Topic:	ROLLBACK Segment management to UNDO Tablespace Management
Next Topic:	tablespace read only

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 26 14:24:38 CST 2025