Help - Search - Members - Calendar
Full Version: Creating Tablespace and Datafiles
Oracle DBA Forums > Oracle > Oracle Forum
DBA_Ashok
Hi,

I am having a senario that i have to create a tablespace that has two datafiles. One datafile is used for storing usres data and other is for index storage.
How can i specify this while creating table spaces.
I have searched for this but i am not able to find a suitable purpose. I found that create two tablespaces and store the data and indexes seperately.

Can any one suggest that how can i achieve this? Is this possible or not.

Thanks

Ashok
dave
no, and it doesnt make sense to either

a tablespace is a tablespace, you store stuff in a tablespace, not in a datafile.

if you want to separate things (although there is no need to) you need to create two tablespaces
DBA_Ashok
QUOTE (dave @ Jul 24 2008, 06:38 PM) *
no, and it doesnt make sense to either

a tablespace is a tablespace, you store stuff in a tablespace, not in a datafile.

if you want to separate things (although there is no need to) you need to create two tablespaces



Thanks for you are reply.

If i am creating two tablespaces then how can we do that setup.
Can you send me the steps as i am new to this or any link.

Regards
Ashok
dave
you just specify the tablespace name on the create statement
Florin Aparaschivei
QUOTE (dave @ Jul 24 2008, 04:08 PM) *
no, and it doesnt make sense to either

a tablespace is a tablespace, you store stuff in a tablespace, not in a datafile.

if you want to separate things (although there is no need to) you need to create two tablespaces


You said there is no need to separate things? I had the impressions that putting tables in one tablespace and indexes in another is a good practice. Can you please show me some reasons against this?
SteveC
There are no reasons against it. There are reasons for it, but related to administrative issues. Some of those are:

easy identification of files - a matter of convenience, and interestingly enough, Oracle Apps (off in their own database world, you would never guess that EBS and the RDBMS come from the same company at times) does that

transportable tablespace sets - you can always rebuild/re-create indexes

tablespace point in time recovery - you can always rebuild an index

If you ever had to do a TSPITR and burned half of the recovery time waiting for indexes to come along in the process (with a fair number of people breathing down your neck), that would probably convince you to keep tables and indexes separate.
DBA_Ashok
QUOTE (dave @ Jul 24 2008, 07:45 PM) *
you just specify the tablespace name on the create statement


I am trying to specify one tablespace name to Create statement and other tablespace name to index creation step.
The create statement works. But it throws error 'no privilege on tablespace TS_INDX' that is my second tablespace.

How could i assign two tablespaces to a single user.
Should i create a tablespace group.

Thanks
dave
you dont assign tablespaces to users, you give them a quota on the tablespaces they require access to
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.