Notes about Oracle usage

I'm off duty, write two articlesArticleAll go home :)

Oracle was also used in the past. It was all done by others. I used it directly and only felt that the stored procedure. The SQL syntax is slightly more awkward than MSSQL. Other configurations are rarely used, this time, from using PD to creating a table to setting up a database for everyone to use it, I got to know more about it and wrote down some of the items that I wrote down as a memo.

In PD, you can directly setTablespaceStorage location, such as the table-store tablespace and the lob object-store the tablespace separately, for example:

The table space for primary key storage is set as follows:

After setting, use PD to generate SQLCodeThe tablespace code is automatically generated.

The single-step debugging and Direct Generation of results in the stored procedures in Oracle were previously considered very troublesome. Because the cursor object is used, the system returns a cursor instead of directly executing the exec stored procedure name in MSSQL, I did not solve the problem of how to debug the stored procedure in the past. This time, I found that the stored procedure under the definition header was directly found in PL/SQL, such as packages, right-click and test, the corresponding test Stored Procedure Code is automatically generated. You only need to write the parameters below. After the parameters are generated, click... box to view the results generated by the stored procedure.

It is still troublesome, but integrated with independent debugging, it is more powerful.

 

Oracle dblink connectionI had to worry about it for a long time. In fact, it was very easy to find it. I wrote a text document record separately:

 

After connecting to AAA remotely, you need to connect to the BBB instance and query data. The dblink method is as follows:

Create database link bbb
Connect to user identified
Password using '(description =
(Address_list =
(Address = (Protocol = TCP) (host = 192.168.1.254) (Port = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = BBB)
)
)'

If you have added a class test to the tnsnames. ora file of the AAA (not necessarily the local machine) instance you are logged on:
Bbb =
(Description =
(Address_list =
(Address = (Protocol = TCP) (host = 192.168.1.254) (Port = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = BBB)
)
)

For such code, you only need:
Create database link bbb
Connect to user identified
Password using 'bbb'
You can.
(Note: In the upper case of the link, BBB is the name. To, the user is the login name. By, the password is the password, and the double quotation marks after using contain the name obtained by tnsnames. ora)

----------------------------------------------------------------

The test is as follows:
Select *
From ew_area @ BBB;

Ew_area is the table of the remote database, and BBB is the name of the link,

Query all the dblinks created by the login machine as follows:
Select owner, db_link from dba_db_links

Delete the specified dblink connection as follows:
Drop database link bb
-------------------------------------------------------------------------
For global use, public must be added, for example:
Create public database link bbb2 ....

You also need to delete the global
Drop public database link bbb2

 

 

Trigger Problems, Handwriting sequence when the auto-increment field starts to prepare for SQL tuning. nextval, but finally found that when using some ORM tools to create objects, such as directly insert (item) objects, at this time, you need to first get the ID from the sequence, and then insert the database, the database will be connected twice and if no sequence is written when the SQL or stored procedure is written. nextval is also better, so I decided to use the trigger. Try to use it less in other places to prevent lock tables and so on.

 

There are also many other details, but they are easy to find on the Internet. Some problems are not encountered in the project for the time being, so they will not be written.