Differences between Oracle and SQL Server)

The original address is unknown ....

 

Concepts of Oracle and SQL Server
-----------------------------------------------------
· Oracle services and databases are equivalent to ms SQL database services;
· Oracle services = background processes + related memory
· Database = a collection of data files
Oracle services can be completely separated from database files; schema in Oracle and database in ms SQL.
Schema "Scheme" is a collection of all objects owned by a user.
"Object" includes tables, views, instantiation views, sequences, processes, functions, packages, and synonyms.

The general process of Oracle databases similar to MSSQL is as follows:
· Create a tablespace in the database (equivalent to the data files in the MSSQL database)
· Create a temporary tablespace (transaction log files of databases that are only in MSSQL)
· Create a user. Its default space and temporary tablespace are the two newly created tablespaces.
· Grant permissions to users. At least the role permission must be a connect role.
· Log on with this user to create tables, views, instantiation views, sequences, processes, functions, packages, synonyms, and so on.

Enterprise Manager for Oracle Manager server and MS SQL
-----------------------------------------------------
The DBA studio tool provides the same functions as the Enterprise Manager of ms SQL, but it should be said that there is a big difference between the two.
Generally, the DBA studio tool is sufficient. However, it is necessary to explain the following to the Oracle Manager server:

> It is a service for Oracle to manage distributed databases. Note that it is a service.
> It is not installed by default.
> It requires you to manage the databases you need. Create a user in a database
> Its login requires authentication. Note that authentication here is not the same as that of the user who manages the required database, nor is it the Sys, system in the database;
Its Default User is oem_temp sysman.
> When using this service, you must start the Manager server service and the intelligent proxy service (oracleagent) on the management end, And the managed database server must start the intelligent proxy service (oracleagent ).

Comparison between sys and system users of Oracle and master of MSSQL
------------------------------------------------
The master database of MSSQL stores some configuration information of the current database service, such as database devices, character sets, data files, login accounts, databases owned, and configuration of the entire service parameter.
Oracle's sys stores the Oracle service or instance information and the data dictionary information of all users.
Oracle system users have data dictionaries which are view information. With these views, it is very convenient to query database information. By default, system users have DBA system role permissions,
Sys not only has DBA permissions but also sysdba permissions.

Differences between dBA and sysdba system roles:
First, describe the creation process of the Oracle service: Creating an instance, starting an instance, and creating a database (system tablespace is required)
Second, the startup process: instance startup, Database loading, and database opening

New database objects in Oracle: instantiated views, snapshots, sequences, packages, synonyms, and abstract data types
--------------------------------------------------------------------------------
· Instantiate a view, also known as a display chart, indicates that it has its own storage space. View: indicates that its data source is from other table data.
· Set the data in the instantiation view to update data at intervals. The update mode can be defined as full update and incremental update.
· Snapshots are basically the same as the instantiation view, except that the data sources are different. The snapshot data comes from the remote database, and the instantiation view comes from the local data table.
· Sequence, which is equivalent to the identify column in MSSQL. It is a numerical sequence list.
· Package: a collection of processes, functions, and global variables. It encapsulates private variables, private procedures, and private functions. Example: dbms_out package
· Synonyms: aliases of objects in the database. synonyms can be global or private (belonging to a user), such as tab and Col.
· Abstract data type: similar to the struct or Pascal record type in C.

Oracle Database Connection and MSSQL remote connection
---------------------------------------------
Both implement distributed database operations and both implement distributed transactions.

Oracle rollback and MSSQL database transaction log files
-----------------------------------------------
Rollback segments provide the images before data changes required for transaction rollback. These images are stored by entries. If there are too few entries, the probability that one transaction will wait for another transaction
Increasing will affect the database performance. By default, a system rollback segment is provided in the system tablespace.
To improve performance, the system tablespace should not store any information other than the data dictionary information.
The transaction log file function of the MSSQL database is the same as the rollback segment, but it is closely related to a specific database.

Manage data tables
-----------------

Management of super large data tables:
Both Oracle and MSSQL provide a "store data files and their indexes in a specific data file or tablespace" method.
Oracle stores partitions of tables and indexes, that is, partitions are stored within the range of field values.
· Oracle index organization table and sqlserver Cluster Index Table: both data are stored in the order of index values.
· Tables without the concept of transaction rollback do not have the concept of transactions for such table operations; Oracle provides the table creation parameter nologging so that operations on the table do not participate in transaction rollback.
· Index: Oracle provides a variety of index types that MSSQL does not have, such as Bitmap indexes.

External Connection
= ------------------------------------

· MSSQL supports two forms of table connection
(1) form inherited from sysbase:
1) Field 1 * = Field 2 (left join)
2) Field 1 = * Field 2 (Right join)
3) No full outer join syntax in this form
(2) standard outer join Syntax:
1) left [outer] Join on logical expression
2) Right [outer] Join on logical expression
3) full [outer] jion (full outer join) on logical expression
· Oracle does not support standard outer join syntax and does not have full outer join (this is a defect ).
1) Field 1 = Field 2 (+) (left join)
2) Field 1 (+) = Field 2 (Right join)
· Use of external connection statements
(1) You do not want to lose data rows in the master table because of table connection.
(2) to find a record that exists in Table A but does not exist in Table B, use the not in (Select... query statement) syntax as usual.
The biggest disadvantage of using not in is that it is slow because select queries are performed on each line. The following statement is better:
Select tu_company. * From tu_company left
Join tu_comp_agent on
Tu_company.id = tu_comp_agent.compcode where tu_comp_agent.id is null;

Trigger
==================================
· MSSQL only has a table trigger, and the trigger time is not rich enough. For example, the insert trigger does not distinguish between single insert and multiple insert, or between trigger before insert or trigger after insert. Encounter
To insert multiple data entries, you must use a cursor to process each inserted data.

· The triggers provided by Oracle include not only table-based triggers, but also other types, such as database triggers: Database startup and database shutdown. Table-level triggers
Distinguish between single insert and multiple insert, and between trigger before or trigger after insert

Table Data Replication
-------------
· Data replication in the database
· MSSQL
Insert into copy table name SELECT statement (copy table already exists)
Select field list into copy table name from table (copy table does not exist)
· Oracle
Insert into copy table name SELECT statement (copy table already exists)
Create Table copy table name as select statement (the copy table does not exist)
· Batch Transfer of text files into and out
· MSSQL
BCP command line program
· Oracle
Sqlldr command line program

Update and delete multiple tables
-------------------
An update statement cannot update multiple tables unless the trigger is used to implicitly update the table, which means to update the table to be updated based on the data of other tables.
· MSSQL
Update a set field 1 = B Table child segment expression, Field 2 = B Table child segment expression ,.... From B where logical expression

· Oracle
Update a set field 1 = (Select Sub-segment expression from B where ...), field 2 = (Select Sub-segment expression from B where ...),... from B where logical expression
For the above questions, if a requires multiple sub-segments to be updated, MSSQL is more concise!

Temporary tables used in stored procedures or functions
---------------------------------
Both have this function. The primary benefit of a temporary table is that the operation does not leave any trace and logs are not generated, so the speed is fast.
· MSSQL
Create Table # Table Name (...), or select field expression list into # table name from table name plus # before, these temporary tables are intended to be valid during a database connection session.

· Oracle
Create [Global] temporary table. In addition, [Global] is a global temporary table (all database connection sessions are visible). Otherwise, it is private (valid during a database connection session ).

Dynamically Execute SQL statements
------------------
· Pass a table name in the parameters in the stored procedure or dynamically generate an SQL statement during the process.
MSSQL:
Declare @ count int
Declare @ SQL nvarchar (200)
Set @ SQL = n' select count (*) from sysobjects'
Exec sp_executesql @ SQL, n' @ I int output', @ count output

ORACLE:
(1) package dbms_ SQL:
Open the cursor (open_cursor, this process is not available for non-query statements)
Analysis Statement (PARSE)
Bind_variable)
Execute)
Close the cursor (close_cursor, this process is not available for sorting query statements)
(2) execute immediate ls_ SQL

Database backup and recovery
-----------------------------------------------
MSSQL:
(1) Database Import and Export DTS tool, if both the data source and target are MSSQL, you can completely copy the database structure (tables, views, indexes, triggers, rules, defaults, stored procedures, user-defined functions, table data, and so on)
(2) database backup and restoration commands:
Backup Database
Restore database
The exported data files can also be compressed, and the database backup and restoration methods can be incremental and complete.
(3) Attach the database
If the raw data and log files of the database are not damaged, run the command sp_addach_db. In this case, sometimes a problem occurs: for example, you have seen the login account,
The default Logon account SA is not used. Because the login account information is stored in the master database, the original account cannot be used and the following operations are required.
ORACLE:
(1) Import and Export tools: exp and IMP
Many Import and Export Parameters
(2) Cold backup or offline backup. The database stops during Backup.
Back up all data files, log files, and control files
(3) hot backup or online backup
The database is in archivelog mode. Note that full recovery, time point recovery, SCN point recovery, and free user intervention are supported by default when archivelog is restored.
Cancel recovery can be said to be its hot backup function is very powerful.
(3) RMAN recovery management period