| Need Guidelines on handling tablespace [message #402240] |
Fri, 08 May 2009 03:07  |
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 #402258 is a reply to message #402244] |
Fri, 08 May 2009 04:17  |
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 |
 |
Mahesh Rajendran
Messages: 10708
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 |
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
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
.
