Below is my personal experience, because of the limited level, it is estimated that there are some problems with understanding. Welcome to the Oracle 8i beginners, it is likely to bring the concepts in MS SQL Server to Oracle, this control, I personally think that I don't need to do too much, if I have to have some people who haven't learned other databases, I may easily learn Oracle. Now let me talk about the differences and contacts.
1. Concepts about the database I have found or create their own database Oracle database concept has been completely different from MS SQL
Oracle's service and database, equivalent to MS SQL database service Oracle's service = background process related memory database = data file collection and Oracle's service is completely able to detach the data file from the data file, in Oracle Where can I find it, Oracle's Schema, Schema's meaning: Solution, meaning that a user has all the objects of all objects here, including tables, views, instantiated views, sequences, procedures, functions, package, synonyms (below I will explain some of these unfamiliar concepts in detail), so we have to build a database similar to MS SQL in Oracle: Rough Process · Establish Data Formspace (Data Files Plexible) · Establish temporary table Space (equivalent to the transaction log file for the MS SQL database) • Create a user, his provincial space and temporary table space is the two table space established · Authorization to the user, the minimum role permission is the role of Connect, Log in with this user, create your own table, view, instantiated view, sequence, process, function, package, synonym, etc.
2. ENETRPRISE Manager for Oracle Manager Server and MS SQL should say that the two have great differences, and the DBA Studio tool provides the same functionality as MS SQL ENETRISE Manager, and the DBA Studio tool is sufficient to use it. But here is necessary to explain the Oracle Manager Server · It is the service of Oracle Manage Distributed Database, pay attention to him is a service · It does not install it · It needs to have the database you need to manage, in a database Establish a corresponding user · Its Equity requires authentication, pay attention to the authentication here, is not a concept of the user's database, nor is the SYS and SYSTEM users in the database, but the provincial users are OemTemp · Its function is available · Use it, you have to start the Manager Server Service and Intelligent Agent Service (OracleAgent), managed database server to start smart agency service (OracleAgent)
3. Comparison of Oracle's Sys, SYSTEM and MS SQL's MASTER comparison MS SQL MASTER data stocks stored some configuration information for the current database service such as database devices (in MS SQL has been weakened), character set, data file, login account , Owned database, entire service parameter formulation and other information
Oracle's SYS Storage Oracle Services or Instance Information and All User Data Dictionary Information This point is different from MS SQL, MS SQL Each database has its own object's data dictionary information SYSTEM users have data dictionary is a view message, with these views Our query database information is particularly convenient, and the system user has DBA system role permissions, but SYS has only a DBA permission to have SYSDBA permissions DBA. What is the difference between the two system characters of sysdba?
Before this, I need to say the creation of Oracle services. Creating instances • Start instance • Creating a database (System table space is required) Startup process · Instance launch · Load Database · Open Database
Sysdba, is managed by Oracle instance, its existence does not depend on the entire database, as long as the instance is started, he already exists, log in, load the database, open the database, only the database is opened, or the entire database is fully launched After that, the DBA role has the foundation of the existence! 4, Oracle China New Database Object: Instantiated View, Snapshot, Sequence, Package, Synonyms, Abstract Data Types • Instantiated Views Also known as Scheme: Instantiation Description He has its own storage space, view: explain his Data comes from other table data. Instantiated data in the view, set to update data over a period of time, update mode can be defined as full update and incremental updates • Snapshots basically instantiated views, only different data sources, snapshot data is willing to remote database, The instantiated view is derived from the local data sheet. Sequence, equivalent to the Identity column in MS SQL, he is a digital order list. Package: he is a collection of processes, functions, global variables, he encapsulates private variables, private Procedures and private functions such as: dbms_out package · Synonym: is an alias for objects in the database, synonyms can be globally or private (belonging to a user) such as: Tab, col et al, abstract data type, similar The structure or pASCAL record type in C is about type, here there is an external look: a Tab% RowType, this is a special abstract data type, the component of this type is the field B tab.tname% Type of Tab, This defines a variable of the same data type as Tab's field TNAME, think about what is the benefits?
5. Oracle Database Connections and MS SQL Remote Connections are to implement the operation of distributed databases. Both can achieve distributed transactions, see MS SQL online help
6. Oracle Roll Rolling Sections and MS SQL Database Transaction Log File Rollback Segments provide the image before the data changes you need to use. These images are stored by the entries, and if these entries are too small, a transaction is waiting The chance of another transaction increases, which will affect the performance of the database, which provides a system back to the segment of him in the System table space. In order to improve the performance System tablespace should not store information other than any data dictionary information. MS SQL database transaction log file function Lei Tong returned to the segment, but he is closely related to a specific database
7. About the management of data sheets · Super large data sheet management a data sheet, especially the kind of water account, long expansion after long-term moon, and finally affect the performance of the inquiry performance, Oracle and MS SQL provide a method, Data file and its index are stored in a specific data file or table space, but this still does not solve the problem, what should be handed, the developer can only add time to add time: CWSJ2000 (financial data 2000), CWSJ2001, CWSJ2002, This naming method can really improve query performance, but bring a lot of trouble to the development (the wave finance is doing this), and when you don't know which table is in, you have to inquire , Isn't you? Oracle provides a good means to solve this problem: tables and index partition storage. Sub-storage is performed in the range of field values. The specific practice is not illustrated.
• The order of data storage in the Oracle index tissue and MS SQL cluster core is stored in the order of the index value.
· There is no transaction rollback concept, the operation of this table does not have the concept of the business. The concept of Mysql is not providing a rollback of the transaction (I don't know if there is any change) and Oracle provides the metrics NOLOGGING, so that Table operation does not participate in the rollback of things
· Index Oracle provides a variety of MS SQL no index types · bitmap index, for example, gender: Only men and women 1 record: Men's Article 2 Record: Male Article 3 Record: Female Article 4 Record: Male Article 5 Record: Female Article 6 Record: Male ......... The index of it: 110101 ............ See this index multiple space, it applies Field value is one of the known few
· This feature is powerful based on function or expression of the expression.
8, external connection · MS SQL Server supports two forms of inter-form connection 1 From Sybase inherited: Field 1 * = field 2 (left connection) field 1 = * field 2 (right connection) does not have this form Connection syntax 2 Standard external connection syntax LEFT [OUTER] JOIN ON logical expression Right [Outer] join ON logical expression full [Outer] Join (All Outside Connection) ON logical expressions The logical expressions here can be very complicated Expression, for example: a.id = B.ID and (a.parebt_id = 1 or a.parent_id = 2) Need to remind everyone: you write the query statement reported like this error JOINED TABLES Cannot Be Specified in a Query Containg Outer Join Operators. JOINED TABLES CANNOTBO.VU_CAF_BILLS 'Contains Joined TABLES This sentence tells you that the view or subquery of your query statement is also used, but Quote view or sub-query external connection syntax and your external connection syntax does not cause, for example: SELECT A. [ZONE], A.FLAG, A.FLAGDESC, A.CAF_NO from dbo.vu_caf_bills a, tu_flag where a.caf_no * = Tu_flag.objno View DBO.VU_CAF_BILLS The external connection syntax is the standard SQL syntax, and the external connection syntax in this statement is the Sybase type external connection syntax.
· Oracle does not support standard external connection syntax, and there is no full connection, this is its defect field 1 = field 2 ( ) (left connection) field 1 ( ) = field 2 (right connection)
· Use the use of external connection statements 1 Do not want to make the primary table data line due to table connections, this is not more saying that 1 to find a record in a table, and do not exist in the B table, use not in in practice, according to regular practice, use Not in (SELECT The query clause) syntax, using Not in the largest shortcomings slower, the reason is that each data row is done: select query broiler: 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 null9, trigger · From what I learned, MS SQL Server, only table triggers, and trigger the timing is not rich enough, if inserted in the sub- Do not distinguish the insertion of the insertion or after inserting the plug in multiple data, you need to use a cursor to handle each inserted data.
• The trigger provided by Oracle not only has a table-based trigger, but also other types, such as database-level triggers: database starts, database shutdown to table-level triggers, distinguishing between single insertion or multiple inserts, also distinguishing before insertion Trigger or inserted, trigger 10, Table Data Copy · Library Data Copy · MS SQL Server 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 (copy table does not exist) · Text file transfer, transfer Batch processing · MS SQL Server BCP command line program · Oracle SQLLDR command line Program 11, multi-table update, delete one update statement is not updating multiple tables, unless the trigger is implied with updates, I mean here: Update the form of the table you want to update according to other table data: · MS SQL Server Update A Set Field 1 = B Table Field Expression, Field 2 = B Table Field Expression from B Where Logic Expression · Oracle Update A Set Field 1 = (SELECT Field Expression from B Where ...), Field 2 = (SELECT field expression from b where ...) WHERE logical expression From above, I feel Oracle doesn't have MS SQL. Main Cause: If a need multiple field updates, MS_SQL statements are more simply concise, you know the research database How do people do this? They use a cursor one to handle
12. About the temporary table used in the stored procedure or function, both provide this feature, the most important benefit is that the operation does not leave any traces, not to generate a log, so the speed is fast · MS SQL Server Create Table # Name (........) or SELECT field expression list INTO # Table Name from Table Name Before plus #, these temporary tables are only valid only during a database connection session, Oracle Create [Global] Temporary TABLE, plus [global] is a global temporary table (all database connection sessions are visible), it is not private (valid during a database connection session)
13, both provide their desktop version of the database · MS SQL Server Newhel I have said that the function of the basic desktop version of the database is provided with the server version of the function. The full-text search service function • Oracle Oracle Lite 8 I have seen the Households Oracle Lite 8 to do customs declaration system supplements: Sybase provides desktop version SQL Anywhere14, dynamically perform SQL statement · MS SQL Server 7.0 seems to have this function, MS SQL Server 2000 has this feature. Do you want to pass a table name in the parameters of the stored procedure or dynamically generate a SQL statement in the process body, you will find it difficult. Looking at the following example: Your previous problem completely solved the declare @count int declare @sql nvarchar (200) set @SQL = N'SELECT Count (*) from sysobjects' exec sp_executesql @ SQL, N '@ i int outprut' @ count output · Oracle provides two ways to implement this feature 1 package dbms_sql, execute a statement process: open the cursor (Open_CURSOR, for non-query statements, no process) Analyze statement (PARSE) Binding variable (bind_variable) Execute Statements Close the cursor (Close_Cursor, without this procedure) 2Execute IMMEDIK LS_SQL15, database backup and recovery provide strong database backup and recovery capabilities, Oracle provides more means, ORACE claims that her database is not destroyed, not a bragging, MS SQL Server 1 database, pouring the DTS tool, if the data source and the purpose are MS SQL Server, you can completely copy the structure of the library (including the table, view) , Index, trigger, rule, default, stored procedure, user-defined function, table data 2 database backup and recovery commands The data files in the backup Database Restore Database can be compressed, this is different from Sybase and this database backup And recovery methods can be incremental and complete 3 database additional attach as long as the database's raw data and log files are not damaged, you can command: sp_attach_db, sometimes there is a problem: more than one, you can log in to log in Not using the default login account SA, because the information on the login account is stored in the master database, so you can log in with the original established account, so you have to do some of the reusing work · Oracle 1 Pour out the tool EXP and The IMP tool pours the parameters of the poured parameters 2 cool backups or offline backups, the database is the stopped backup of all data files, log files, control file 3 hot backup or online backup database at ArchiveLog mode, pay attention By default, you can choose full recovery, time point recovery, SCN point recovery, user free intervention Cancel recovery can be said that his hot backup recovery function is very powerful 4RMAN recovery manager, I It is in research, not good to say
