CODEWITHSUNDEEP
databaseoracle-database
user2206329
user2206329

Reputation: 2842

How is Schema defined in Oracle?

I've recently had to do some work on an Oracle database. I come from a MS SQL background. I am still trying to get my head around some basic definitions in Oracle

Schema - to me this just meant the structure of the database. Which includes the structure of the tables, indexes and any constrains. This does NOT include any data that is stored in the tables. A database would only contain one Schema and one set of data.

But in Oracle it seems like a Schema is defined as the structure and the data. And a database can hold many Schemas.

Is that accurate?

Upvotes: 0

Views: 391

Answers (1)

Justin Cave
Justin Cave

Reputation: 231651

Regardless of the database engine, it isn't uncommon to talk about your data model as your "schema". That's not necessarily how any relational database engine defines the term but it may be perfectly clear from the context that you're talking only about the definitions of objects and not the actual data.

In both SQL Server and Oracle, a "schema" is a way of collecting together a bunch of related objects, code, and data. If you define a schema in SQL Server and create a table foo in that schema along with a usp_setFoo procedure, the data that is in foo would be part of that schema. In the same way, an Oracle schema would generally involve table and index definitions, data, code, etc.

Technically, in Oracle, a schema is defined as the set of objects owned by a particular user. Practically, an Oracle schema is generally roughly analogous to a SQL Server "database". Oracle normally has two levels of object naming (schema.object) rather than three levels in SQL Server (database.schema.object). If you're using the enterprise edition of Oracle 12.1 with pluggable databases, that changes things a bit and an Oracle pluggable database can be similar to a SQL Server database.

Upvotes: 2

Related Questions