Instances
In SQL Server, an instance is created when the database software is installed. It represents an independent application service that includes operating system files, memory structures, background processes, and registry information. On Windows, an instance is usually seen as a service that can be either stopped or running. When it is running, it consumes server memory and starts a certain number of background processes.
Under normal circumstances, installing SQL Server 2008 gives you only one default instance, named MSSQLSERVER. If you want multiple instances, you have to install SQL Server again for each one you need, and change the instance name during installation, such as MSSQLSERVER1.
Oracle does not work this way. It can create multiple instances as needed without having to repeat the whole installation process each time.
Databases
From a physical point of view, a SQL Server database is a collection of operating system files stored on disk. These files are divided into two types: data files and transaction log files.
A database must contain at least one data file and one transaction log file. Most of the actual database content in SQL Server is stored in the data files, while the transaction log file records the changes made to that data and is used during system recovery.
A data file or transaction log file can belong to only one specific database. Two databases cannot share the same data file or log file. If a database is very large, it can use multiple data files, and those files can be logically grouped into what SQL Server calls a file group.
Tablespaces
In SQL Server, logical grouping is handled by the database itself. In Oracle, this job is done by the tablespace.
An Oracle tablespace is a logical structure used to group tables, views, indexes, and other database objects. For example, in an Oracle product database, you might assign one tablespace to an HR application and another to a payment application. A database can be logically divided into multiple tablespaces, and each tablespace is physically made up of one or more data files.
So in Oracle, the equivalent of a SQL Server database is the tablespace.
Users
Oracle users are independent. In practice, different users correspond to databases in SQL Server. Each user is similar to an independent database in SQL Server, and in Oracle you can allocate a separate space for each user with a freely adjustable size. In SQL Server, users are not tied to databases in the same way.
Oracle can be understood as one instance, one database, and multiple tablespaces. SQL Server, by contrast, creates multiple databases under one instance. In Oracle, the structure equivalent to a SQL Server database is the tablespace.
Creating an Oracle database or tablespace
Creating a database or tablespace in Oracle generally involves three steps. Oracle also requires a corresponding user when creating a database or tablespace, and the database/tablespace and user are usually in a one-to-one relationship.
1. Create the files for the database or tablespace
CREATE TABLESPACE monitor LOGGING DATAFILE 'E:\app\owner\oradata\orcl\monitor.dbf'
SIZE 100M AUTOEXTEND ON NEXT 32M MAXSIZE 500M EXTENT MANAGEMENT LOCAL;
CREATE TEMPORARY tablespace monitor_temp tempfile 'E:\app\owner\oradata\orcl\monitor_temp.dbf'
size 100m autoextend on next 32m maxsize 500m extent management local;
2. Create a user and map it to the files created above
CREATE USER monitor IDENTIFIED BY monitor
DEFAULT TABLESPACE monitor
TEMPORARY TABLESPACE monitor_temp;
3. Grant permissions to the user
grant connect,resource,dba to monitor;
grant create session to monitor;