Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

tablespaces and data dictionary views

tablespaces and data dictionary views

tablespaces and data dictionary views

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.


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

RE: tablespaces and data dictionary views

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.

RE: tablespaces and data dictionary views

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

RE: tablespaces and data dictionary views

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.

RE: tablespaces and data dictionary views

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.  


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close