Learning Note:sql Server VS oracle–database Architecture

Http://www.sqlpanda.com/2013/07/learning-note-sql-server-vs.html

This is my Learning note base on the "SQL Server Essentials for Oracle DBAs jump Start".

DATA Block/extend and SEGMENT

image:http://lh5.ggpht.com/-fxekn7ccnd0/uetkoxz6igi/aaaaaaaains/ybxba67epjw/image_thumb%25255b1%25255d.png?imgmax=800

• Oracle use Extend allocation maps to track Extend. Extend is continually blocks. Each Extend are always part of one segment. The object is created on the segment.
• MSSQL use Gam/sgam-to-track the page usages. The objects can uniform extend or in mix extend depends on the object size. Trace 1118 turn on would force, the SQL Server always use uniform extend.
• SQL Server is always 8K per page and 64K per extend (8 pages X 8K), Oracle have various block size hence has various exten D size. Oracle can also have uniform extend size and other various extend size base on how "Extend management" setting within the Table space. (see here ).

Tablespaces and SYSTEM DATABASES

Because There is always a database per Oracle instances so most of the system database mapping to Oracle are 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 are "database Template" which we can use the define the characteristic of Databas E 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 was 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 has UNDO). Oracle log files only have REDO. Undo Log Store in the Undo table space. In SQL Server, each database have its own log files and the log file within the same db is used sequentially. In Oracle, the log files is being divided as log group, each groups can has 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.

	Oracle	SQL Server Master Database
Users	Dba_users	syslogins
Objects	Dba_objects	Sys.objects
Tables	Dba_tables	Sys.tables
Datafiles	Dba_datafiles	sys.databases

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 is 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 is defined as advance, therefore, we have to

' 0 ';

Reconfigure

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

For Oracle, show parameter would display all the options. There is many advance options and most of the time, we don ' t really need to change it unless it's asked by Oracle support.

	Oracle	MSSQL
List All Parameters	Show parameter	sp_configure
List single parameter	Show Parameter X	sp_configure ' X '
List parameter with keyword match	Show Paramter XXX	N/A

Oracle: Refer here for detail.

scope=[memory| spfile| BOTH]

MSSQL : See here for configuration option.

' 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