Home » RDBMS Server » Server Administration » Need Guidelines on handling tablespace (Oracle 10g)
Need Guidelines on handling tablespace [message #402240] Fri, 08 May 2009 03:07 Go to next message
summoner
Messages: 44
Registered: March 2009
Member
One of our database P is to centralized the data retrieved from different databases(e.g. A,B,C).

In current setting of P, we create 2 tablespaces - P_DATA for data and P_INDEX for index only
Quote:

P_DATA - stores all data from all databases A,B,C.
P_INDEX - stores all indices



Now my boss tell us to investigate whether it will be better to separate the projects into different tablespace
i.e.

Quote:

A_DATA
A_INDEX
B_DATA
B_INDEX
C_DATA
C_INDEX



Comparing this with the old approach, do you think the new approach will work better in security and maintenance and testing?
If you have any other better approach or good reference materials, please let me know. Thank you
Re: Need Guidelines on handling tablespace [message #402244 is a reply to message #402240] Fri, 08 May 2009 03:18 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>do you think the new approach will work better in security and maintenance and testing?
Separating tablespaces by functionality will help
with ongoing maintenance and ease of administration.
By separating data and index tablespace, you afford to do any maintenance / backup individually.

Are you worried about performance?
What kind of storage you are using? (local disks/ SAN?).

Re: Need Guidelines on handling tablespace [message #402258 is a reply to message #402244] Fri, 08 May 2009 04:17 Go to previous messageGo to next message
summoner
Messages: 44
Registered: March 2009
Member
Quote:
>>do you think the new approach will work better in security and maintenance and testing?
Separating tablespaces by functionality will help
with ongoing maintenance and ease of administration.



What is the "functionality" mean?
Do you mean .......
1. "Transactional Tables", "Control Tables", "Dummy Tables"
2. "Materialized views", "Tables"
3. "Tables for Product", "Tables for Person"
?

Do you suggest putting the objects in different Source DB into same tablespace?

Quote:

By separating data and index tablespace, you afford to do any maintenance / backup individually.



Quote:

Are you worried about performance?


Yes, but less concern than frontline service DB

Quote:

What kind of storage you are using? (local disks/ SAN?).


SAN I think. Does the storage type determine the tablespace planning?
Re: Need Guidelines on handling tablespace [message #402262 is a reply to message #402258] Fri, 08 May 2009 04:42 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>What is the "functionality" mean?
My mistake. Was not clear.
In this case, I meant "different usage".
>>Do you suggest putting the objects in different Source DB into same tablespace?
As long as they have different usage, different maintenance plans you can put it separately. Else, there is no point in that.
Idea is maintain it easily and
Backup individual tablespaces.
>>SAN I think. Does the storage type determine the tablespace planning?
yes.
Separate Table and index tablespaces only for maintenance ease.
Not for performance.
In real SAN, the datafiles are anyhow mirrored. So everything goes across everywhere.
For high i/o systems, we can identify possible hotspots and allocate more spindles.


Edit:

For example,
tablespace_data has data from both sourceA and sourceB.
If you loose tablespace_data, you loose both. Could your business
afford that?
By seperating sourceA and sourceB, only one tablespace will incur
downtime.

[Updated on: Fri, 08 May 2009 04:52]

Report message to a moderator

Re: Need Guidelines on handling tablespace [message #402279 is a reply to message #402240] Fri, 08 May 2009 07:09 Go to previous messageGo to next message
summoner
Messages: 44
Registered: March 2009
Member
Will there be any impact if I separate into so much tablespaces? My supervisor concern whether there will be a lot of free space wasted on that

My colleague suggest to separate the tablespace by projects because they can import/export data for a single project easily.
It cannot be done if the tablespace is separated by usage
Re: Need Guidelines on handling tablespace [message #402281 is a reply to message #402279] Fri, 08 May 2009 07:19 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>My supervisor concern whether there will be a lot of free space wasted on that
Disk is very Cheap Wink.
You will have some wastage anyhow.
Say, if you maintain a 10% freespace (always have 10% freespace, to be in safer side), it
will be the same 10% in one tablespace with lot of data
or
10 smaller tablespaces with same data.
>>separate the tablespace by projects because they can import/export data for a single project easily.
>>It cannot be done if the tablespace is separated by usage
we were just using different terminology.
In this case, consider Project=Usage.
Each project has a separate schema. Each schema has separate usage.
we are both saying the same.
Separate tablespaces by project.
Re: Need Guidelines on handling tablespace [message #402282 is a reply to message #402279] Fri, 08 May 2009 07:23 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
To avoid further confusion with differences in terminology,
Let every schema have its own tablespaces.
Within each schema, separate tablespaces based on functionality/usage etc.
For example, group high i/o tables in a particular tablespace.
Keep the datafies across the disk (san does it for you) and distribute the I/O.
Re: Need Guidelines on handling tablespace [message #402286 is a reply to message #402240] Fri, 08 May 2009 07:43 Go to previous message
summoner
Messages: 44
Registered: March 2009
Member
Thanks Mahesh Rajendran Smile

I will convince my boss to add more storage Surprised
Previous Topic: Oracle internal error
Next Topic: Configuring SEMMSL (for semaphores) ?
Goto Forum:
  


Current Time: Sun Dec 11 08:18:53 CST 2016

Total time taken to generate the page: 0.16769 seconds