Solved

The Oracle system database

Posted on 2013-06-10
36
325 Views
Last Modified: 2013-08-18
Dear all,

I comes from MS SQL background, 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.
0
Comment
Question by:marrowyung
  • 17
  • 11
  • 7
36 Comments
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 200 total points
ID: 39235117
In Oracle you have schemas which are just basically just users that own objects.

There are two main ones: SYSTEM and SYS.

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.
0
 
LVL 34

Expert Comment

by:johnsone
ID: 39235782
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.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39236739
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 ...
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39237519
>>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
0
 
LVL 34

Expert Comment

by:johnsone
ID: 39237679
What is referred to as the "application owning schema", would be similar to the dbo user in SQL Server.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39278384
let me check
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39384734
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.
0
 
LVL 34

Expert Comment

by:johnsone
ID: 39385388
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
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39385602
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.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39388192
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.
0
 
LVL 34

Expert Comment

by:johnsone
ID: 39388849
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.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39392392
"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 ?
0
 
LVL 34

Expert Comment

by:johnsone
ID: 39392494
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.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39395996
"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 ..
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39396030
>>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.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39396356
thanks.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39396357
it seems it can't be reopen, right ?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39396791
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.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39399949
ok ok got it.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39400921
Dear slightwv,

This quersion has been reopened, please help to answer.
0
 
LVL 34

Expert Comment

by:johnsone
ID: 39401431
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
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39401554
>>This quersion has been reopened, please help to answer.

What additional information do you need?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39403866
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.
0
 
LVL 34

Expert Comment

by:johnsone
ID: 39404414
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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39404563
>>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.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39407077
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.
0
 
LVL 34

Expert Comment

by:johnsone
ID: 39407702
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.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39407938
":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.
0
 
LVL 34

Expert Comment

by:johnsone
ID: 39408913
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.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39410305
"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 ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39410476
but small amount of data delete will also make some left block empty, right?

should be no difference ?
0
 
LVL 34

Accepted Solution

by:
johnsone earned 300 total points
ID: 39410955
Lets say, for example, that a leaf block of an index contains 50 rows.  If 49 of those rows were deleted, the block would not be reused.  You have no idea which records are in which leaf blocks, so you cannot tell if the block will get reused.  The only gain you would get by rebuilding the index is to reclaim some space, you will not get a performance gain from the rebuild as the depth of the tree would remain the same.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39413942
"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 ?
0
 
LVL 34

Expert Comment

by:johnsone
ID: 39414035
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.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39417850
"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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now