Learning Note: SQL Server VS Oracle–Database architecture

TABLESPACES AND SYSTEM DATABASES

Because there is always one database per oracle instances so most of the system database mapping to Oracle is tablespace.

• Model DB: Because there is no need to create the separate user db in the Oracle. The close thing to the SQL Server model db is “database Template” which we can use to define the characteristic of database and we can use it to create the database on another instance.
• Tempdb: Oracle can have multiple temporary table space. SQL server’s tempdb is shared among entire servers. If we turn on the RCSI for the SQL Server database, the version store is also store in the Tempdb versus Oracle store the version in the UNDO tablespace.  DBA_TEMP_FILES list the temporary table space files. For SQL Server, sp_helpdb tempdb.
• Log file: SQL Server log files are split internally as multiple VLFs and contain both UNDO and REDO logs. ( Except the tempdb log files , it only has UNDO). Oracle log files only has REDO. UNDO log store in the UNDO table space. In SQL Server, each database has its own log files and the log file within the same db are used sequentially. In Oracle, the log files are being divided as log group, each groups can have multiple log files.

image: http://lh5.ggpht.com/-DT_hl9I-kxw/UetkPk67AxI/AAAAAAAAIoA/8cKNDm94F8s/image_thumb1.png?imgmax=800

SYSTEM LEVEL INFORMATION

Oracle store the system level information in the SYSTEM tablespace under the schema SYS.

SQL Server store in the master database.

In SQL Server, we can use sp_help to find out the basic information of the object. In Oracle, we use DESC . See here .

v$datafiles and V$logfile are the system level view which allow as to see the information within oracle even the database is not open.

LIST ALL PROCESS

Oracle: v$sessoins

MSSQL: sys.dm_exec_requests or sp_who2

SERVER CONFIGURATION

For SQL Server, a lot of configuration values are defined as advance, therefore , we have to

EXEC sp_configure ‘show advanced option‘, ‘0‘;

reconfigure

In SQL Server 2008R2, the basic configuration option is 16. All options including advance are 70.

For Oracle, show parameter would display all the options. there are many advance options but most of time, we don’t really need to change it unless it is asked by Oracle support.

Oracle:  please refer here for detail.

alter system set PARAMETER = XX scope=[MEMORY|SPFILE|BOTH]

MSSQL :See here for configuration option.

sp_configure ‘XXX‘.value;

reconfigure;

Read more at http://www.sqlpanda.com/2013/07/learning-note-sql-server-vs.html#Ocyxh20sYbvww0jA.99

Learning Note: SQL Server VS Oracle–Database architecture

标签：depends   ppi   time   show   soft   blocks   turn   another   model