This is my learning note base on the “SQL Server Essentials for Oracle DBAs Jump Start” .
DATA BLOCK/EXTEND AND SEGMENT
image: //bbsmax.ikafan.com/static/L3Byb3h5L2h0dHAvbGg1LmdncGh0LmNvbS8tRnhFS243Q0NOZDAvVWV0a094WjZpZ0kvQUFBQUFBQUFJbnMvWWJYYkE2N2VwSncvaW1hZ2VfdGh1bWIlMjUyNTVCMSUyNTI1NUQucG5nP2ltZ21heD04MDA=.jpg
- Oracle use Extend allocation map to track extend. Extend is continually blocks. Each Extend is always part of one segment. The object is created on the segment.
- MSSQL use GAM/SGAM to track the page usages. The objects can be 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 has various block size hence has various extend size. Oracle can also has 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 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: //bbsmax.ikafan.com/static/L3Byb3h5L2h0dHAvbGg1LmdncGh0LmNvbS8tRFRfaGw5SS1reHcvVWV0a1BrNjdBeEkvQUFBQUFBQUFJb0EvOGNLTkRtOTRGOHMvaW1hZ2VfdGh1bWIxLnBuZz9pbWdtYXg9ODAw.jpg
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 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 | 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: 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