Please Help! Difference between Database, Schema & Instance?

Question

Im really confused with these three in ORACLE. Assume we have database named CONTRACTS & it has two schemas i.e LEGAL schema & RI schema and LEGAL schema has got two Instance i.e QADB15 & QACNTR2. what does this all mean. Can you give me a clear picture about these.
Thanx in advance!

Answer 1

for simplicity, Instance is the name of the Database. Schema is the owner of your objects(tables, indexes, views, synonyms,etc).



In you example, you have a Database Instance named CONTRACT.



In your Contract database, you have 2 Schemas named LEGAL & RI.



Now, this is where I also get confused. Because, you said "LEGAL has got 2 instances, ie QADB15 & QACNTR2". Meaning they are inside the LEGAL schema...



If they are inside the LEGAL schema, QADB15 & QACNTR2 are OBJECTS and not instances inside the LEGAL schema. Most probably, they are tables...



Hope that clear things abit

Answer 2

Database schema is the skeleton of database. It is designed when the database doesn t exist at all. Once the database is operational, it is very difficult to make any changes to it. A database schema does not contain any data or information.The actual content of the database, the data, changes often over the years. A database state at a specific time defined through the currently existing content and relationship and their attributes is called a database instance...

Answer 3

"Schema is the term given to the properties that make up a table: the table's name, its columns, and column types, as well as any default values a column is to have." Basically, schema is the names and attributes of your information and database. The database is the software that stores the info. The instance is the info in a column, I believe. So basically, CONTRACTS is what all of your info and stuff is called/which database file it's located in. LEGAL schema can either be what a table in the database is called or what one column in a table is called. QADB15 and QACNTR2 is info is in the column (if you think about it in a spreadsheet format - the LEGAL schema is the header cell in a column of info, and QADB15 and QACNTR2 are the info that's in the cells below it.). RI is also either another table or column. Sorry if that was a bit disorganized... Hope it goes well.