Oracle 11g Study Notes (5)

Some ASP. NET + Oracle 11g learning and writing essays. This seriesArticleIt can help. net that is unfamiliar with Oracle and is not even used at all.ProgramStaff, can get started and develop applications in the shortest time, to avoid having to spend as much time as I did for the support team to flip books, Query files, create code for implementation and test, ask questions, and reply. This post includes Oracle's "Null String = NULL" feature, Oracle's transaction isolation level, and Oracle's backup & restoration.

(10) Tips for Oracle

* In SQL plus, run the "DESC" command to view the schema of a data table, for example, DESC Table1;

* Whether using SQL plus or Oracle SQL developer, after executing the insert, update, and delete statements, you must run the "commit;" command again to write the statements into the database, this is different from other databases.. NET application to write data to Oracle, you do not need to add this action. If a person writes data to Oracle through SQL plus or Oracle SQL developer, the "commit;" or "rollback;" command is not executed, the record is locked, no one else can modify this record (the user's application or browser will remain in the waiting state), but it can still be read 」.

Bytes ------------------------------------------------------------------------------------------

 

(11) Oracle does not have such a "Null String ".

There is no such thing as an "null string ('') "in Oracle, And the Null String is considered null, however, this is not the case with SQL Server, other brand-name databases, and ansi SQL 92 definitions.

When you update the varchar2 field of a table in Oracle into an empty string, the following code is used:
Update Table1 set col1 = ''[where id = 80];
In Oracle, it is equivalent:
Update Table1 set col1 = NULL [where id = 80];

At this time, when you use the following SQL statement to select, no data can be found (but it can be found using other factory-level databases ):
Select * From Table1 where col1 = '';

If it is changed to the following, use is null to query the data only when the query condition is used (but the data cannot be found in databases of other brands ):
Select * From Table1 where col1 is null;

Even if you use the oracledatareader. isdbnull method of ADO. net, write the. NET application to judge the result.

In this case, when you use the length function to determine the length of the col1 field, null is returned instead of 0, as shown below:
Select length (col1) from Table1 where id = 80;

These oracle "null strings" features are different from those defined by other brand-name databases and ansi SQL 92.

Reference file:
Http://www.adp-gmbh.ch/ora/misc/null.html
Http://blog.darkthread.net/blogs/darkthreadtw/archive/2008/04/01/empty-string-in-oracle.aspx

Bytes ------------------------------------------------------------------------------------------

 

(12) Oracle's "transaction" isolation level

SQL Server has seven isolation levels, which can be used with ADO. while Oracle has only two isolation levels: readcommitted and serializable, But Oracle's readcommitted and SQL Server's readcommitted have the same name, however, the functions and definitions are different. Oracle readcommitted is similar to SQL Server 2005's Snapshot isolation )」.

Readcommitted is the default isolation level of SQL server. It features that when a record is in "Edit; Update", no one else can "read" it 」; readcommitted is also the default isolation level of Oracle, but its features are completely different from those of SQL Server. The version of Oracle is that when a record is "modified, others can still "read", but cannot "modify" (the record will be locked until the individual commit or rollback being modified ).

The readcommitted feature of Oracle is that when a record is "modified", if someone else just "reads" the record, oracle will obtain this record from the previous version of commit and read it to others to avoid "share lock 」, that is to say, to prevent other people from waiting for their browsers or applications, rather than doing other work.

The data provider used in ADO. Net/Visual Studio to connect to Oracle. This is true for oledb, oracleclient, and ODP. net, and is completely different from the isolation level of sqlclient.

Reference file:
Http://www.programmer-club.com/pc2020v5/forum/ShowSameTitleN.asp? Url = N & board_pc2020 = Oracle & Index = 3 & id = 3985 & mode = & type_pc2020 = sametitleLevel-2
Http://www.oracle.com.cn/viewthread.php? Tid = 54983 & extra = Page % 3d109% 26amp % 3 bfilter % 3d0% 26amp % 3 borderby % 3 ddateline % 26amp % 3 bascdesc % 3 ddesc
Http://cherishchen.javaeye.com/blog/183229
Http://msdn.microsoft.com/zh-tw/library/system.data.oracleclient.oracletransaction.isolationlevel.aspx
Http://www.oracle.com/technology/global/cn/pub/articles/price_dbtrans_dotnet.html
Http://blog.darkthread.net/blogs/darkthreadtw/archive/2007/6/22.aspx

Bytes ------------------------------------------------------------------------------------------

 

(13) Oracle backup and Restoration Methods

Oracle supports the following four methods:
* Cool backup (offline backup)
* Hot Backup (online backup)
* RMAN
* Exp/imp

At Oracle 10 Gb, A New Data Pump tool was introduced, and the performance was better than the old exp/IMP, because it didn't need to be performed through session, but it was troublesome to use, you must first create a directory for backup. The command is as follows:
Expdp userid = system/password tables = xxx, YYY directory = abc_dir => abc_dir: This is the Oracle directory. Create
Impdp userid = system/password dumpfile = expdat. dmp directory = abc_dir

The other oldest EMP/IMP can only be regarded as a storage-to-storage tool, which is suitable for small databases only because of its slow execution speed. I tested the results by using the Export command to back up the entire Oracle 11g orcl database, which contains only 18 new data tables (each table has more than 10 records, up to 100 or 200 records ), it took more than half an hour; the import restoration command was slower. After running for more than an hour, it was not finished until the next day at work at the company. The command is as follows, but it is not in SQL plus. In Windows, it is in "command prompt character (cmd.exe) run the following command 」):
Exp system/password @ orcl file = c: \ orcl_081207.dmp full = y
IMP system/password @ orcl full = y ignore = y file = c: \ orcl_081207.dmp

In this way, the whole orcl database will be exported, including index, sequence ,... And so on. For the parameter, ignore = y indicates that the data table with the same name must be overwritten during import. If this parameter is not added, the table with the same name will not be imported.

To make exp/IMP run faster, do not set the full = y parameter. Switching to user mode (specifying owner = XXXX) for backup will be faster. In addition, only one or more data tables can be exported or imported, as shown in the following figure (a semicolon is not added at the end 」):
Exp system/password @ orcl file = c: \ orcl_081207.dmp tables = (Table1, table2)
IMP system/password @ orcl file = c: \ orcl_081207.dmp tables = (Table1)

If you want to see the Help Command, the following help parameters are available:
EXP-help
Imp-help

In addition, TNS (tnsnames. ora) must be correctly set before EMP/imp is executed. For Windows Server 2003 and Oracle 11g, the file is located in the following directory:
C: \ app \ Administrator \ product \ 11.1.0 \ db_1 \ Network \ admin

Tnsnames. ora:

Orcl_s =
(Description =
(Address_list =
(Address = (Protocol = TCP) (host = 192.168.0.1) (Port = 1521 ))
)
(CONNECT_DATA =
(SID = orcl)
)
)

You can also use the built-in Oracle graphicsInterfaceSet the tool "net manager. Note that the above Sid must be set (set to the database name), but when oracle is just installed, Sid may not be automatically added. After the settings are complete, run the "tnsping service name" command to test whether the settings are correct. for ora, run the tnsping command like this in the "command prompt character" in the Windows operating system (you do not need to add a semicolon to the end 」):
Tnsping orcl_s

Reference file:
Http://big5.webasp.net/article/21/20961_print.htm
Http://www.blueshop.com.tw/board/show.asp? Subcde = brd20081202144229dsv & fumcde = fum200410061527578k1 & rplcnt = 11

Bytes ------------------------------------------------------------------------------------------

 

(14) Oracle stored procedures, written in conjunction with ASP. NET's "Paging" Application

Previous versions used SQL Server and Sybase databases with ASP. when writing a net 2.0 program, a stored procedure is written to handle the pagination function of the gridview control, refer to several articles on this site-Asp. NET data paging:

Http://www.cnblogs.com/WizardWu/archive/2008/09/28/1301616.html
Http://www.cnblogs.com/WizardWu/archive/2008/09/07/1286270.html
Http://www.cnblogs.com/WizardWu/archive/2008/08/06/1261589.html
Http://www.cnblogs.com/WizardWu/archive/2008/08/02/1258832.html

The stored procedures of SQL Server and Sybase databases can directly return data in the resultset format (that is, table data with multiple rows and multiple columns, like ADO. net datatable) to call (CALL) its application, as shown below, but Oracle cannot directly return like this:

Create procedure DBO. pager_sqlserver2005
As
Set @ sqldatatable = 'select * From table1'
Exec (@ sqldatatable)
Return

Oracle has another method. It can only process and return the "result set" through the ref cursor type, but it is said that this performance is not good. I don't know why Oracle wants to develop another set like this (including the previously mentioned "Null String" and "null"). It must be different from the databases of other brands, all programmers have to learn a set of standards. For Oracle stored procedure and ref cursor, refer to the following files:

Http://msdn.microsoft.com/zh-cn/library/ms971506.aspx
Http://msdn.microsoft.com/zh-cn/library/4s2zbbsz (vs.80). aspx
Http://msdn.microsoft.com/zh-tw/library/4s2zbbsz (vs.80). aspx (same as above, but this is a traditional Chinese Version)
Http://support.microsoft.com/kb/308072/zh-tw
Http://big5.chinaz.com: 88/www.chinaz.com/program/.net/0h512hh007.html
Http://www.phpq.net/oracle/oracle-stored-procedure-tutorial.html
Http://www.bccn.net/Article/sjk/oracle/200709/6126.html
Http://blog.csdn.net/dacula/archive/2005/03/01/306566.aspx

For ASP. net's gridview "Paging" function. I gave up the Oracle stored procedure and wrote it myself. net function, to receive parameters from the objectdatasource control (to retrieve the number of records to the number of records), the original work that can be processed in the stored procedure, moved. net app_code folder.

Bytes ------------------------------------------------------------------------------------------