Advertisements
SQL oracle difference concepts architecture
In this post, I am just referring similar to the concept to understand better, but it is not exactly same as what I have referred.
| Oracle | SQL | Comments |
| System | Master | |
| SYSAUX | Resource | |
| Model | ||
| MSDB | ||
| temporary | TempDB | More than one & User can have their own There is no TempDB and it doesn’t need to re-create |
| DESCRIBE | sp_help | dba_views |
| Identity | Sequence | Auto generate number |
| DBA_SCHEDULER_JOBS | Sys.sysjobs | |
| DBMS_SCHEDULER / OEM-Oracle Scheduler | SQL Agent | |
| SMON | Recovery | |
| PMON | User kill/rollback | |
| show parameter | sp_configure | |
| Pfile & Spfile | SQL server property | you can change system and session level |
| Data file | Data file | |
| ARCHIVELOG mode | Full mode | |
| NOARCHIVELOG mode | Simple mode | Default one |
| Redo log | Transaction log | |
| Archive log backup | Transaction log backup | |
| Control files | Startup Parameters | |
| Database | Instance | |
| Block | Page | |
| Extent | Extent | |
| Segment | ||
| Tablespace | Filegroup/ database | |
| Schema | Schema/users | |
| TNS listener | Tcp/IP | Protocols |
| Default Port – 1521 | Default Port – 1433 | |
| Fast_Start_MTTR_Traget | recovery interval | Checkpoint action |
| DBA_* | sys.* | |
| DMVs | ||
| Oracle_Home | SQL default location C:\ | Installation |
| SID | SID | Both are opposite |
| collation | collation | In Oracle there is no object level setting |
| Materialized view | Similar to replication and indexed view but not refreshed | In Oracle it is a physical copy and get refresh periodically |
| Level 0 backup | Kind of full backup | Backup all data. Parent of level 1 |
| Level 1 Cumulative backup | Differential backup | Backup all data after the level 0 backup |
| Level 1 Differential backup | Kind of log backup | Backup all data after last level 1 or 0 whichever is met first |
| Archive log backup | Transaction log backup | Backup redo log file data |
| Recovery catalog database | Nothing in MSSQL | To store all the database backup and recovery details |
| DBVERIFY check | CheckDB | |
| DBMS_STATS.GATHER_* | sp_updatestats | |
| ANALYZE TABLE table_name VALIDATE STRUCTURE | dm_db_index_physical_stats | |
| SQL Server Options | Oracle Options | |
| Clustering | Real Application Clusters (RAC) | |
| Log shipping | Data Guard (primary and standby databases) | |
| Replication | Streams/Advanced Replication | |
| Database mirroring |
This link will help some more:
https://dba.stackexchange.com/questions/9765/how-to-make-a-transition-from-sql-server-dba-to-oracle
