tablespaces and data dictionary views 1

[Guest_imported]

I'm studying Oracle SQL part-time and have a couple of questions I can;t find an answer to..

what are the 4 table spaces present on every Oracle server and what does each contain/do.

and

in each schema, which data dictionary view contains one row for each object in the database.

I've have exhausted many other avenues and woould appreciate any help. thanks in advance. David

 

[karluk]

This answer is off the top of my head, so I won't vouch for its complete accuracy.

1. I would say that the four tablespaces would be system, rollback, temporary, and data. System contains the system catalog. Rollback contains rollback segments for backing out of uncommited transactions. Temporary is used for temporary data storage, such as sorts. Data contains the application data.

The wording of your question is confusing. These four tablespaces aren't absolutely essential to create a database, so it's a bit much to say that they are on every single Oracle server. The only one I'm certain is necessary is the system tablespace. For example I'm fairly certain that it's possible to create rollback segments in the system tablespace. It's just a bad idea to do so.

2. The catalog view, USER_OBJECTS, contains one row for each object in a schema. If you want one row for every object in the database, regardless of schema, I would use DBA_OBJECTS. The phrasing of the question makes it a little unclear which is right.

 

[saltydog]

If you go to TUSC.com you can download a "poster" program that they wrote to take a look at all the different views & objects & such......Rich

 

[DBAwhosaysNIE]

I will (vouche for karluk's answers).

The ONLY tablespace present on EVERY Oracle server is SYSTEM.

Whoever wrote that question was confusing tablespaces with segment types. Rollback, temporary and "data" (table, index, etc.) SEGMENTS almost always go in separate and/or dedicated tablespaces but there is no requirement (other than good judgement to do so. Rollback segments can be created in SYSTEM and user's temp/data segments default to it unless specified otherwise.

The answer to the second question (as phrased) is none. Data dictionary views exist only in the SYS schema but are referenced with public synonyms when conencted as user other than SYS. Even if you ignore that and equate the synonym to the view the question still maked little sense as the only view with one row per object in the DATABASE is DBA_OBJECTS but it's (almost certainly) not going to be accessible from EVERY schema.

If you're paying for OCP prep (or something like that) you may want to re-check the instructor's credentials.

 

[Guest_imported]

thank-you all very much for you help. I agree the question is worded badly but your responses have given me some excellent information to work with.

David