The Oracle system database

The internal catalog and the concepts of databases and instances are very different between MS SQL and Oracle.  I suggest going through the Concepts guide to familiarize yourself with the differences.

slightwv,

"You shouldn't create custom objects in these schemas.  You should create your own user and define the necessary privileges form them and let them create their own objects. "

You mean in Oracle, everyone is a schema and we need to create schema for each user and assign necessary privileges for each schema?

johnsone,

"The internal catalog and the concepts of databases and instances are very different between MS SQL and Oracle.  I suggest going through the Concepts guide to familiarize yourself with the differences. "

please where is it ? that's why I am asking ...

>>You mean in Oracle, everyone is a schema

Pretty much.  There really isn't a difference between a user and a schema.  The way I try to describe it is like I did above:  Create a user.  Once that user creates an object like a table, it's a schema.

The 50,000 foot view:  In Oracle the Instance consists of the memory structures and server processes.  The Database consists of the files on disk.

An instance belongs to one and only one database.  A database can be 'shared' by one or more instances (Real Application Clusters - RAC).

>>we need to create schema for each user and assign necessary privileges for each schema?

Typically an application has an owning schema.  This holds all the objects for the application.  Then you have users that need access.  You create those users and grant the minimal privs necessary for the app to run.

>>please where is it ? that's why I am asking ...

The concepts guide:
http://docs.oracle.com/cd/E11882_01/server.112/e25789/toc.htm

What is referred to as the "application owning schema", would be similar to the dbo user in SQL Server.

let me check

but one thing, I am asking this

"MS SQL has 4 x system database:
1)master (login)
2) model (template for all objects)
3) msdb (store backup plan and maintenance plan)
4)  tempdB ( for all temp object creation and the storage for data rendering).

Did Oracle has this kind of thing and pleaes give example for what task this system DB designed for. "

it seem you both talking about different thing.

the Oralce don't use database to store all Oracle based login account ? it don't use a database to store all Oracle jobs that execute regularly ?

in MS SQL , all login store in master and so once we restore that master database, all login restored !!

if we restore the MSDB database, all SQL job, including maintenace job restored.

Any database Oracle use to do this kind of thing ?

Let me tell you all that the tempDB in MS SQL store all temporary object that used to render the data for the final output, so this is the virtual RAM/PAGE.

The entire concept of how the system works is different between SQL Server and Oracle.  Oracle doesn't store login accounts in a separate database.  They are stored in a table that is in a schema that is in the database (specifically SYS.USER$, however it should be accessed with the DBA_USERS view).  You really cannot compare them.  If you are looking for some specific information in an Oracle database, we should be able to tell you where it is.

You really should read through the Concepts manual to get a better idea of the differences.  http://docs.oracle.com/cd/E11882_01/server.112/e25789/toc.htm

I agree with johnsone and also suggest you spend some time in the Concepts guide.

I just wanted to emphasize what johnsone posted:  Not only can you really not compare them, you should really stop trying to understand Oracle in SQL Server terms.

I understand SQL Server is your background but Oracle will be more confusing if you try to understand it using SQL Server concepts.

Swimming analogy:  Just because you are an Olympic class pool swimmer doesn't mean you can dive in the ocean and be instantly successful and visa versa.  They are similar only in you are swimming in water.  They are different skill sets that require different techniques to master.

johnsone,

"They are stored in a table that is in a schema that is in the database (specifically SYS.USER$, however it should be accessed with the DBA_USERS view).  "

so this mean these information stored in the table SYS.USER$ ?

slightwv,

"I just wanted to emphasize what johnsone posted:  Not only can you really not compare them, you should really stop trying to understand Oracle in SQL Server terms."

Excellent, I belive that too ! I will keep reading.

The information for database users (and I believe roles) is stored in the SYS.USER$ table.  Yes.  However, you shouldn't be looking at that base table.  If you want to view the information, you really should be looking at the DBA_USERS view.  I was just using this as an example to show the difference in how the dictionary/catalog is stored.  From the standpoint of the administration, different RDBMS cannot be compared, they need to be relearned.

"different RDBMS cannot be compared, they need to be relearned. "

yeah,, but their concept is the same from my point of view, just theri approach is different !

" However, you shouldn't be looking at that base table.  If you want to view the information, you really should be looking at the DBA_USERS view. "

why can't see that table but by view? avoid accidently delete some ?

You can look at the SYS.USER$ table, but there are typically bit fields that you cannot directly translate and all the information you probably want is not all in the one table.

I have worked with a few different RDBMS systems over the years.  The catalog is not something that translates between them.  Take a look at Postgres some time.  Their concept is different as well and the catalog consists of very few tables.

I also know someone that works within Oracle and tried to figure out the base tables of the catalog.  He said don't bother.  Every time he figured out what one of the base tables really was for and then tried to fit it into the puzzle of all the other tables, it broke something he had already figured out.  And he had access to the people that developed the catalog.  You really should only be looking at the DBA_ views to get data out of the catalog.  That is the way that Oracle has designed it and I don't think they are going to change that.

"You can look at the SYS.USER$ table, but there are typically bit fields that you cannot directly translate and all the information you probably want is not all in the one table."

Excellent, thanks.

"I have worked with a few different RDBMS systems over the years.  The catalog is not something that translates between them.  Take a look at Postgres some time.  Their concept is different as well and the catalog consists of very few tables."

good. no index is needed? tried noSQL ? is it ok? MS SQL or Oracle can get kill because of this?

"You really should only be looking at the DBA_ views to get data out of the catalog.  That is the way that Oracle has designed it and I don't think they are going to change that."

I think in this way, the Oracle account can be secured but what if someone accidentally change something in the sys.user$ table? the oracle fuck up?

if the person know the DBA_ views, then he/she can read that too and not secure ..

>>but what if someone accidentally change something in the sys.user$ table?

Anytime you manually modify anything in the data dictionary you can easily corrupt the database.  I would think that is the same in any RDBMS.

>>if the person know the DBA_ views, then he/she can read that too and not secure ..

This is why it is a DBA level view.  They aren't for 'normal' users.  You need to have DBA level access to be able to see them.

There are three levels of views in Oracle DBA, ALL, USER.

So for users: DBA_USERS, ALL_USERS and USER_USERS
Tables:  DBA_TABLES, ALL_TABLES and USER_TABLES
etc...


As suggested before, the concepts guide is probably the best place to become familiar with Oracle.

Here is the section that talks about these views:
http://docs.oracle.com/cd/E11882_01/server.112/e25789/datadict.htm#BHAHEADF


You have also accepted solutions to this question.  If you would like to continue with it, it should probably be re-opened.

thanks.

it seems it can't be reopen, right ?

If you would like it re-opened, I can do that for you.  Just let me know.

You can also click the request attention link and a Moderator will take care of it.

ok ok got it.

Dear slightwv,

This quersion has been reopened, please help to answer.

Like any other database access is controlled through privileges.  No users, other than DBAs, should have access to the DBA_ views.  Typically only users with the DBA role get privileges to access those views.

In addition, if you are worried about users modifying the catalog tables, make sure that the O7_DICTIONARY_ACCESSIBILITY parameter is set to FALSE.  The doc for that parameter is here -> http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams157.htm#CHDGCHHA

>>This quersion has been reopened, please help to answer.

What additional information do you need?

johnsone,

I think not only the view has permission to access it but also the table, right? so I think access the DBA view is not only because of this but also it is bit value and human cna't understand, right?

slightwv,

where Oracle store all the SQL job/plan information? we talk about the user account more but not the maintenance plan and job.

No user should have access on any of the tables owned by SYS.  Access should only be granted through the views.  Having privileges on the view does not give you privilege on the table.

If I understand correctly, the job information you are looking for would be stored in DBA_SCHEDULER_JOBS.  With my limited SQL Server knowledge I believe that is what you are looking for.  However, you definitely won't find the same types of jobs in there.

>>where Oracle store all the SQL job/plan information?

The online docs have everything you need and it has a pretty decent search engine.

We really can't bring you up to speed with everything you need to know on a site like this.

The best thing to do is install a development database and play.  Just poke around with what you read in the concepts guide and other documentation and you can see what is where and why.

johnsone,

"If I understand correctly, the job information you are looking for would be stored in DBA_SCHEDULER_JOBS.  "

so this one is a view too and what is the respective table to store the real one? The job I am talking about is like "rebuilt index", something like that.

slightwv,

"The best thing to do is install a development database and play.  Just poke around with what you read in the concepts guide and other documentation and you can see what is where and why. "

good, but a quick hints only on this is the finally one, cound be nice. As this is the question.

You don't need to know the underlying tables that store the job information.  Typically you aren't rebuilding indexes anyway.  The are being rebalanced for you, so there really isn't a need to rebuild them.  The biggest exception to that is if you have deleted a significant number of rows from the base table.

":The biggest exception to that is if you have deleted a significant number of rows from the base table. "

And the reason is ? the data range is reserve for next the same data range anyway.

The reason is leaf block usage in the index.  Oracle will only reuse a leaf block once it is completely empty.  Then a rebuild will get back the space and make it available for re-use.

"Oracle will only reuse a leaf block once it is completely empty.  Then a rebuild will get back the space and make it available for re-use."

excellent.

but if a large amount of data delete, a lot of left block is empty, right ?

but small amount of data delete will also make some left block empty, right?

should be no difference ?

"The only gain you would get by rebuilding the index is to reclaim some space"

this is good and really different from what I expected.

so no shrink of DB too in Oralce if you speak in this way ?

I have no idea what "shrink of DB" is.  I am assuming that in SQL Server space at the OS level would be released in the case of rebuilding an index?  Is that correct?  If so, then that would not happen in Oracle.  OS level space is pre-allocated, I will refer you to the Concepts guide that has already been pointed out.

"I have no idea what "shrink of DB" is.  I am assuming that in SQL Server space at the OS level would be released in the case of rebuilding an index?  Is that correct?"

rebuild index is not going to release space in MS SQL.

usually shrink of DB is good on very earlier version of MS SQL but it increase the index defragementation.