0

I have tried looking at a few resources but still remain a bit confused. Hopefully someone can explain to me exactly what is going on.

I installed oracle 19c. I then set up several connections. My main question is: what is a connection? Is a connection a database? What is being connected to. Other resources state that 19c is itself a database, but what if I want a second one?

I got some help from this link but still have more questions. Creating a new database and new connection in Oracle SQL Developer

enter image description here

The_Redhawk
  • 214
  • 1
  • 2
  • 11
  • Please, check this: [Oracle Concepts](https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/introduction-to-oracle-database.html#GUID-CF765A7D-9429-4901-BF33-36E0B0220293). Oracle Database is software that can manage databases, which are just files. To allow you to operate with that files you need to run an **instance**, which is a memory structures and processes to operate with DB files. So connection is a communication channel between your software and database instance. One instance can have multiple connections. – astentx Jul 14 '21 at 20:34

1 Answers1

1

In Oracle, when you connect, you connect as user. This user has its own workspace called "schema". Hence, user = schema. What you see in Sql Developer is your connection as specific user and all the objects under this user's schema. So, connection there is just a saved set of connection parameters, such as server, schema, user, password, type of connection, etc

Database is a bunch of files where your data is stored. Then at runtime, instance of that database is created in memory - this is what you connect to. You can't connect to the database, although, commonly people do say "connected to the oracle database". If you connect as system user, you can have access to many schemas. You, as user or schema, can be given permissions to access other schemas as well

"When creating the connections, the only thing that I ever entered differently was the name/username/and password section."

Out of name/username/password - on the picture I see only name (connection name), which is arbitrary, and is not part of database. username is part of database. And if your username is different in each connection, you're connected to different schemas.

T.S.
  • 18,195
  • 11
  • 58
  • 78
  • Ok, but a schema is not a database correct? All these users are connected to 1 database? – The_Redhawk Jul 14 '21 at 20:31
  • No. Database is a bunch of files where your data is stored. Then at runtime, instance of that database is created in memory - this is what you connect to. You can't connect to the database, although , commonly people do say *"connected to the oracle database"*. If you connect as `system` user, you can have access to many schemas. You, as user or schema, can be given permissions to access other schemas as well – T.S. Jul 14 '21 at 20:35
  • @The_Redhawk *"but a schema is not a database correct?"* - correct, schema is part of database.. *"All these users are connected to 1 database"* - can be one database or many different databases – T.S. Jul 14 '21 at 20:44
  • Assuming All I did was install oracle 19c and create the users/schemas in the picture above, I should only have 1 database then? – The_Redhawk Jul 14 '21 at 20:46
  • @The_Redhawk Have you installed Oracle Database or the Oracle Sql Developer? If you installed the entire database - yes, you have one database. **you're assuming** that your picture gives any sort of description of what you **really** have. But this is not true. All I see is 4 connections - "hr", "rthomas", "system" + 1. These are only some arbitrary names of the saved connections. Inside them, if I want, they can be pointed to the same instance and same schema, or to 4 different instances, or to same instance but different schemas. – T.S. Jul 14 '21 at 21:06
  • I have oracle database installed. When creating the connections, the only thing that I ever entered differently was the name/username/and password section. Everything else, like service name I kept consistent. Each connection has different tables in it. I am guessing then that I have 4 different schemas on the same instance. Thanks for the help btw, it is appreciated. – The_Redhawk Jul 14 '21 at 21:17
  • @The_Redhawk if you connected 4 times to same SID then all your connections are connected to the same instance. Out of `name/username/password` - on the picture I see only `name`, which is arbitrary, and is not part of database. `username` is part of database. And if your `username` is different in each connection, you're connected to different schemas. Because remember - user=schema. `CREATE USER john IDENTIFIED BY x` - creates schema – T.S. Jul 14 '21 at 21:22
  • 1
    I think I'm starting to get it. So essentially right now I have 1 database and 1 instance of that database with 4 schemas, each identified by a different name. – The_Redhawk Jul 14 '21 at 21:25