Oracle usage tips and PL/SQL Developer Configuration

From: http://livenzhao.spaces.live.com/blog/cns! 6e0000be9f6ddd872! 595. entry

· PL/SQL Developer skills

 
 

1. PL/SQL Developer remember the Login Password

When PL/SQL Developer is used, PL/SQL Developer needs to remember the username and password for logging on to Oracle for ease of work;

Setting Method: PL/SQL Developer 7.1.2-> tools-> Preferences-> Oracle-> Logon History. "Store history" is selected by default. Check "Store with password, remember to enter the password once you log on again.

2. Execute a single SQL statement

When you use the SQL Window of PL/SQL Developer, press F8. By default, PL/SQL Developer executes all SQL statements in the Window and must be set to the SQL statement where the mouse is located, execute the current SQL statement;

Setting Method: PL/SQL Developer 7.1.2 --> tools-> Preferences --> Window types, check "AutoSelect Statement.

3. Format SQL statements

When PL/SQL Developer's SQL Window is used, sometimes the entered SQL statements are too long or too messy. If you want to use a common format, it looks better and better for analysis;

Usage: select the SQL statement to be formatted, and click PL/SQL beautifier In the toolbar.

4. view the execution plan

When PL/SQL Developer's SQL Window is used, sometimes the efficiency of the input SQL statement execution is analyzed to analyze the table structure. You can view the execution plan provided by Oracle to improve the query efficiency;

Usage: select the SQL statement to be analyzed, click the Explain plan button on the toolbar, or press F5.

5. debug the Stored Procedure

When PL/SQL Developer is used to operate Oracle, some stored procedures are sometimes called or debugged;

Method for calling a stored procedure: first, select Procedures in the Browser on the left of PL/SQL Developer to find the stored procedure to be called. Then, select the Stored Procedure for debugging and right-click, select Test. in the displayed Test scr limit pt window, you need to enter a Value for the parameter defined as in. Finally, click the number button above: start debugger or press F9. Click RUN or Ctrl + R.

 

· Oracle learning Manual: Common Errors for beginners

 

No one would deny that ORACLE is one of the most influential database products in the world; but good things do not seem to be so useful (it seems at first ), even a bit heartless-it always gives layman a heartless error number. The following is my personal summary, which is useful and I hope to give some inspiration to beginners.

A joke about "good things do not always look so useful (at first glance)": before attending IBM DB2 512 and 513 training, I downloaded the installer on the campus network, however, for a few of us to study it for a long time, I do not know which file is the installation file, but it was not installed successfully. Finally, I agree that this training is really necessary! I learned later that we downloaded 4 Linux!

[Take 8.1.6 as an example]:

1. ORA-12541: TNS: No listener

Cause: the listener is not started or damaged. For the former, run the net start OracleOraHome81TNSListener command (the name may be different). For the latter, you can add a listener using the "Net8 Configuration Assistant" tool Wizard to "Listener Configuration". (no information is required. You may need to delete all listeners before adding them !)

2. ORA-12500: TNS: The Listener cannot start the dedicated server process

Or

ORA-12560: TNS: protocol adapter error.

Cause: ORACLE Database Service is not started. Run the net start ORACLESERVICEORADB command (ORADB is the database name. If the problem persists, proceed to the following section.

3. If the database service fails to be started, the registry key value may be corrupted. The best practice is to perform the following two steps:

1) ORADIM-DELETE-SID oradb deletes database service items

2) ORADIM-NEW-SID oradb adds database service items

Note: if an error occurs during this process, restart the computer!

4, ORA-12154: TNS: can resolve the service name

Cause: the network service name of ORACLE is not correctly configured. Use "Net8 Configuration Assistant" tool wizard "Local Network Service name Configuration" to configure TNS. If the problem persists, proceed to the following section.

5. ORA-1034: TNS: ORACLE unavailable

Cause: the ORACLE Database Service is correctly started, but the database is not opened!

Run the following command:

1) svrmgrl start the Service Manager

2) connect internal logs in as internal

3) startup open the database

6. ORA-12560: TNS: protocol adapter error (stubborn)

Cause: unknown.

Solution: Go to "Windows Task Manager", kill oracle.exeand oradim.exe processes, write your own ora_startup.bat, and execute it!

PS:

1. My ora_startup.bat:

Net start OracleOraHome81TNSListener

Net start ORACLESERVICEORADB

Svrmgrl is generally not used, but sometimes it is indispensable. For detailed steps, see step 1.

2. My ora_shutdown.bat:

Net stop OracleOraHome81TNSListener

Net stop ORACLESERVICEORADB

3. For more information about ORACLE service names, see service names starting with ORACLE in "Management Tools"> "services.

 

· Oracle 10g green client plus PL/SQL Developer-Build a convenient Oracle client environment

 

Oracle-based data processing programs are being implemented throughout the summer vacation. However, there has been no convenient way to access the database in the lab manually.

In the coolest case, I write a program and connect to the database using JDBC to obtain the desired table ......

Later I found that many machines in the lab were installed with Oracle clients, but ms was installed with genuine installation cds when the database was built in the lab, I have a physiological fear for such a bloated client, so I still use the original method for my own development.

One day, I saw 10 Gb green simple client on the official Oracle website ......

Http://www.oracle.com/technology/tech/oci/instantclient/index.html

To download software from the official Oracle website, you need an account. You only need to apply for the software. You do not have any space on the Internet to store the software. Even if there is no reliable and persistent software on the official website, therefore, only one link is provided.

Http://www.allroundautomations.nl/plsqldev.html

PL/SQL Developer is a software used to develop database-related engineering, and ms is Free. I don't know much about it, but many people who develop Oracle-related databases are using it, I generally only use it as the Client interface for interacting with the database server ......, Most of the time is to open a window to knock on the SQL statement ......)

Below is the installation process, because it is free of installation, You need to configure some environment variables and files by yourself, it is more troublesome ......

First, extract the downloaded Oracle client package to a path and decompress it to E: \ OracleClient on the local machine. Therefore, the actual path of the Oracle client is E: \ OracleClient \ instantclient_10_2.

Enter the instantclient_10_2 folder, create a new network folder, create a new admin folder under the network folder, and create a new name named tnsnames in the admin folder. ora file, which is used to configure the login information for connecting to the remote database (the client software obtains the information for connecting to the database from the file in the relative path). The content is as follows:

Databasename =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = ip address) (PORT = 1521 ))

)

(CONNECT_DATA =

(SERVICE_NAME = database name)

)

)

For example, the file content of my link to the lab database is:

TCM =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = *. *) (PORT = 1521 ))

)

(CONNECT_DATA =

(SERVICE_NAME = TCM)

)

)

I will not write the IP address. The database I use is named TCM, so ......

 

The installation of PL/SQL Developer is silly. After you enter PL/SQL (You are required to log on to the database at the beginning, but PL/SQL still cannot find your Oracle client, so the login will not succeed, but you can go to the software), select Tools> Preferences, and fill in the path of the Oracle client and the path of the OCI file (OCI. dll files are directly in the instantclient_10_2 folder), so the content I entered in these two options is "E: \ OracleClient \ instantclient_10_2" and "E: \ OracleClient \ instantclient_10_2 \ oci. dll ". Then you can log on successfully. If not, restart PL/SQL.

 

The green version of the client may have Chinese support problems, mainly because the character set specified on the server and the default Character Set of the client are different, as long as you find the character set settings on the server, set the character set of the client to be consistent with that of the server. There are several ways to modify the character set of the client. You can modify the registry or use environment variables. However, the client I introduced here is a green version, but only decompress it to a certain path. Therefore, the Registry method is not very suitable here, so I wrote a STARTUP script, before starting PL/SQL, create a temporary environment variable nls_lang, assign a value to the variable, and then start the software. (I have performed experiments and the method of setting up system environment variables does not work here. I cannot explain the specific reasons> <)

 

The content of my script plsql. bat is as follows:

 

Set nls_lang = SIMPLIFIED CHINESE_CHINA.ZHS16GBK

Cd "c: \ Program Files \ PLSQL Developer"

PLSQLDev.exe

The second line is the path to which PL/SQL Developer is installed.

 

I actually set the server-side character set to SIMPLIFIED CHINESE_CHINA.ZHS16GBK, So I assigned this value to nls_lang. Run the plsql. bat script to access the server database.