el123
asked on
One big tablespace for all the segments
Hi gurus,
I've come across 2 interesting scenarios, need your help/guidance:
1. there are other users objects exist in system tablespace, other then sys & system, and same for sysaux, I think it should be used by OEM/dbconsole (DBSNMP & SYSMAN), please correct me if this is not right understanding, and which user's objects should exist in these 2 tablespaces (system & sysaux).
2. And 2nd: there is one BIG tablespace for all the segments, related to one user, where as whatever I've been using, it should be segregated on the basis of segments, but need some proof ... what do you think, is this good, correct, ok or ?
Please throw your experts thoughts, suggestions and advices, and if possible to provide some reference doc/links, that'll be of great great favor.
Because it is really impossible to convenience, the existing DBA, here.
Thanks and very best regards.
Existing segments within the db:
SEGMENT_TYPE COUNT(*) MB
------------------ ---------- ----------
CLUSTER 10 51.5
INDEX 2874 488.19
INDEX PARTITION 141 9
LOB PARTITION 1 .06
LOBINDEX 676 48.19
LOBSEGMENT 676 10468.63
NESTED TABLE 25 1.56
ROLLBACK 1 .38
TABLE 1979 861.69
TABLE PARTITION 123 8.25
TYPE2 UNDO 10 20.25
I've come across 2 interesting scenarios, need your help/guidance:
1. there are other users objects exist in system tablespace, other then sys & system, and same for sysaux, I think it should be used by OEM/dbconsole (DBSNMP & SYSMAN), please correct me if this is not right understanding, and which user's objects should exist in these 2 tablespaces (system & sysaux).
2. And 2nd: there is one BIG tablespace for all the segments, related to one user, where as whatever I've been using, it should be segregated on the basis of segments, but need some proof ... what do you think, is this good, correct, ok or ?
Please throw your experts thoughts, suggestions and advices, and if possible to provide some reference doc/links, that'll be of great great favor.
Because it is really impossible to convenience, the existing DBA, here.
Thanks and very best regards.
Existing segments within the db:
SEGMENT_TYPE COUNT(*) MB
------------------ ---------- ----------
CLUSTER 10 51.5
INDEX 2874 488.19
INDEX PARTITION 141 9
LOB PARTITION 1 .06
LOBINDEX 676 48.19
LOBSEGMENT 676 10468.63
NESTED TABLE 25 1.56
ROLLBACK 1 .38
TABLE 1979 861.69
TABLE PARTITION 123 8.25
TYPE2 UNDO 10 20.25
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
As soon as I clicked submit, I think I might have the winning argument for you:
Oracle Optimal Flexible Architecture (OFA)
2. Separate groups of segments (data objects) with different behavior into different tablespaces.
http://www.dba-oracle.com/t_ofa_optimal_flexible_architecture.htm
Granted you will want to go to Oracle and get an 'official' document but that link should get you started.
Oracle Optimal Flexible Architecture (OFA)
2. Separate groups of segments (data objects) with different behavior into different tablespaces.
http://www.dba-oracle.com/t_ofa_optimal_flexible_architecture.htm
Granted you will want to go to Oracle and get an 'official' document but that link should get you started.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
2. Your db is small enough to hold all the data in one datafile (34Gb for a db_block_size=8k, default), so it is suitable for you to put all user objects in single datafile in one tablespace.