Oracle Common Settings

1, default automatically check my Objects by default, plsql developer login, Brower will select all Objects, if you are logged in the user is a DBA, to expand the tables directory, the normal situation will need to wait a few seconds, and choose my The response rate after objects is measured in milliseconds. The Tools menu, Object Brower Filters, opens the Order window for Brower folders and sets the "My Objects" to default. Tools menu--Object Brower Folders, a few directories that you often point to (such as: Tables views Seq Functions Procedures) Move up a bit, and add color to distinguish, This way your average look-up time will be greatly shortened, try it. /* Set Method: Tools menu--brower Folders, will open the Brower Folders Order window, the "My Objects" to the top. In the same way, you can move a few of the directories you often point (such as: Tables views Seq Functions Procedures) moved up a little, and add color to distinguish, so your average time to find the table will be greatly shortened, try. */
2, remember password This is a controversial feature, because remembering passwords can be a problem for data security. But if it is the development of the library, the password can even be the same as the user name, each time to enter a password is meaningless, you can consider let Plsql developer remember the password. Location: Tools menu--preferences--oracle--logon history--store with password
3, double-click to display the table data Plsql developer The default response when you double-click a table or view in the mouse is disappointing, because I am most concerned about the table structure and data, but two things do not happen after double-click, perhaps the default response is the master needs, But for me to look at the data and table structure is the most important, the other I do not care. But the good thing is this can be set, you can give the mouse double-click and drag and drop the required events, such as: Double-click Edit Data, drag and drop display table structure, yeah! Location: Preferences--user Interface, on the right, double-click and drag-and-drop operations for different object types.
4,sql statement characters all uppercase self think this is a good habit, the core of the information system is the database, the first thing to check when the system problem is SQL statement, how to quickly find that SQL statement in the vast log is a more painful thing. SQL statement capitalization does not solve this problem completely, but finding a line of all uppercase characters in the middle of a heap of code is relatively easy, and your eyes will thank you. Set the position in editor. At the same time I think the equal width character can reduce the program error rate, so all my tools exclusively with Courier New, if an IDE does not support this font, I would basically choose to give up. Ah, I'm a guy full of tiglate.
5, Special Copy in SQL window to write SQL statements usually need to put in Java or other languages, you need to turn into a string and add the corresponding hyphen, this thing does not need to repeat, in the written SQL right-click, using special copy namely ok!
6, custom shortcut key Plsql developer has a lot of keys reserved for users to customize, it is very hight thing. Unlike the overbearing word, basically all the keys have been pre-defined features, the change is very headache. Usually, after opening Plsql developer, the most often do is to open the SQL window and Command window, the two operations have defined shortcut keys, alt+s and ALT + C, so that the mouse point three things only need to press the key.
7, other quick empty table, TRUNCATE table, right-click can be found. It's not clear. Two features: Analyze, VALID 7, SQL window automatically select statement settings based on cursor position: Preferences, Window Types---SQL window, AutoSelect Statement selected. Notice that you add a semicolon after each statement.
Add:
8, start Plsql Developer,window list Menu Auto-call requires two-step setup, first to save the desktop settings, and then tick the window list option, the following: A, in the menu item's tools under the preference option in the user Select option in the interface, and tick the front check box on the right for AutoSave desktop. B. The window list option is checked under Tools in the menu item.
9, formatted SQL (format) when you get a long SQL statement, you want to quickly see the logic, you can put in this tool, format, the logic of the statement is also a glance
10, the database automatically detect the connection situation because the database every time does not operate, will automatically disconnect, and then need to manually connect themselves, because network reasons, always stuck there. The tool provides the ability to detect connections, which can be automatically connected. The specific settings are checked in front of the Tools-preferences-check Connection,check connection.
11, commonly used shortcut keys in fact, these are mentioned in the 6th item, the following is my custom settings: Ctrl+tab switch Windows window (or alt+left/right) ALT + n Create new SQL window ALT + C close the current window
ALT + F format SQL
Add again:
1. Right-click menu
In the PL/SQL Developer (hereinafter referred to as PLD) in each of the text editing window, such as Window,command window and Porgram window, right-click on an object name, will pop up a menu containing the Action Object command, We call this a right-click menu.
Object types can be tables, views, synonyms, stored procedures, functions, and so on. The menu that pops up differs depending on the type of object. Tables and views have features such as view, edit, Rename, Drop, Query data, and edit data. View and edit are the structure information for viewing and modifying tables, such as fields, primary keys, indexes, constraints, and so on. Query data is equivalent to opening a new window and executing the SELECT * from table. Edit data is equivalent to opening a new window and executing the SELECT * from table for update. Stored procedures and functions have the test function and can be checked to enter the debug state. Sometimes due to PLD recognition error, right-click on the object does not come out the correct menu, can be in the object's DDL or DML statement before, plus a semicolon, so that PLD can correctly determine the type of object
2. Select for Update
Sometimes we need to import some data into the database, if you use the UE to spell INSERT statement, it will be more troublesome, and not strong operability. PLD SQL window can query, add, modify and delete the contents of the table. Query from needless to say, and new, delete and modify, just after the SELECT statement to add for update, the table row-level lock, and then click the window lock icon, you can enter the editing state. Here's how to extract text from Excel insert into a database there are three columns in our Excel file: Create a temporary table in the database: Create TABLE T1 (Cino varchar2, Contno varchar2 (+), Loanno VARCHAR2 (100)) then enter select t1 for Update in SQL window and click on the lock mouse to enter the editing state: Click on the first line of the input window with the mouse, then PLD will deadlock a few seconds, and then you can see the cursor in the first line of the input box flashing, With the mouse to Cino, Contno, Loanno selected: Into Excel, the need to insert the contents of the database selected, and then switch to PLD, press CTRL + V: Click Yes, and then click the Commit button, the data submitted to the table T1, execute SELECT * FROM T1 can see the content:
3. PL/SQL beautifier (PL/SQL beautification)
PLD 6 or later has the ability to format DML code. Select the part of the code in the SQL window or program window (if you don't select the entire window's code action), and in the menu, choose Edit-PL/SQL Beautifier to get the formatted code. For illegal DML statements or DDL statements, PLD will prompt PL/SQL Beautifier could not parse text in the status bar below. In the default state, PLD will arrange each field of the DML statement in a separate line, so it is not easy to see. Choose Editàpl/sql beautifier Options in the menu, go to the Preferences window, select Edit, go to the profile editing interface: Select DML in the tab bar, in the middle of the window, In the Insert and update groups box, select Fit and click Save to save the configuration file to the PLD installation directory, and click Close. In the rules file, enter the location of the configuration file, click OK to complete the profile switch. Then you can format the code so that each field is as close as possible on one line.
4. TNS Names
Menu Helpàsupport infoàtns Names, you can view the Tnsnames.ora of Oracle.
5. Copy to Excel
Execute the SELECT statement in SQL window and, after the result, right-click on the data area below and select Copy to Excel, and you can copy the data area's record to Excel. But there are two points to note: One, field can not start with =, or Excel will be mistaken for function, second, the number should not exceed 17 bits, otherwise the number of digits will be set to 0, but can be by the number in front of ' to make Excel think that field is text, At the same time for the Numbe type of field in the database, it is best to use to_char output, otherwise it may show abnormal
6, PL/SQL developer remember login password
When using PL/SQL developer, you want PL/SQL developer to remember the username and password to log in to Oracle for your convenience;
Setup method: PL/SQL Developer 7.1.2->tools->preferences->oracle->logon, "Store History" is checked by default, tick "store With password "can, re-login in the input password once remember. (I can't seem to.)
7. Execute a single SQL statement
When using PL/SQL developer, press the F8 key, PL/SQL developer is the default to execute all of this window, and need to be set to the SQL statement that the mouse, that is, the execution of the current SQL statement;
Setup method: PL/SQL Developer 7.1.2-->tools->preferences-->window types, check "AutoSelect Statement".
7. Formatting SQL statements
When using PL/SQL developer, sometimes the SQL statement entered is too long or too messy, I hope to use a more general format words, so look good, or analysis;
To use: Select the SQL statement that you want to format, and then click the PL/SQL Beautifier button on the toolbar.
8. View Execution Plan
When using the SQL window of PL/developer, sometimes the efficiency of input SQL statement execution, analysis of the following table structure, how to improve the efficiency of the query, you can view the execution plan provided by Oracle;
To use: Select the SQL statement you want to analyze, and then click the Explain Plan button on the toolbar (that is, execution plan), or press F5 directly.
9. Debugging Stored Procedures
When using PL/SQL developer to operate Oracle, some stored procedures are sometimes called, or stored procedures are debugged;
Method of calling the stored procedure: first, select procedures in the browser to the left of PL/SQL developer to find the stored procedure that needs to be called, then select the stored procedure for debugging, right-click, select Test, and in the pop-up Test scrīpt window, For a parameter that is defined as an in type, you need to enter a value for the parameter's value; Last click the number of bars button above: Start debugger or press F9; Last click: RUN or Ctrl+r.
(in order to tune a stored procedure, please refer to the operating manual, this is probably the application of the instructions).
10, Oralce streamline the use of the client
For PL/SQL to connect to an Oracle database, Oracle clients are required in addition to PL/SQL Developer, and a more convenient way is to use the Oracle thin client, which can be downloaded in many places, with small files and low cost sources. After the installation is complete, modify the \oracle\ora90\network\admin\tnsnames.ora file in the installation directory: The format is as follows:
database_name = (DESCRIPTION = (Address_list = (ADDRESS = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT = 1521)) (Connect_data = # (service_name = dealer) (SID = sid_name) # (SERVER = dedicated))
11. Automatic Capitalization of keywords

People who know a little about programming know that coding style is important. In terms of reading code, maintaining a consistent coding style is easier to read, and maintaining a consistent coding style in terms of execution efficiency is more likely to be put into a shared SQL zone, which provides the efficiency of execution. The setting is also simple: tools->preferences->editor, keyword case is selected uppercase. I usually have the keyword capitalized, other things such as table name, field name, etc. are lowercase. Everyone should develop a coding habit of their own and keep it going.

Add:

1, PL/SQL code hint: 1. Manual prompt setting: Key configuration->tools/code assistance changed to alt+/2. Auto prompt settings: tools-->preferences-->user interface-->code assistant-->authomatically activated PL/SQL display window List lists: Tools->window list, window->save Layout PL/SQL in keyword settings: Tools-->preferences-->user interface--> Editor-->keyword case 2, execution of a single SQL statement when using SQL window with PL/developer, press F8, PL/SQL developer default is all SQL statements that execute the window. Need to be set to the SQL statement where the mouse is located, that is, execute the current SQL statement; Setup method: PL/SQL Developer 7.1.2-->tools->preferences-->window types? SQL Windows, tick "AutoSelect Statement". 3, formatted SQL statements when using PL/SQL developer, sometimes the input SQL statement is too long or too messy, I hope to use a more general format words, so look good, or analysis; How to use: Select the SQL statement that you want to format, then click the PL/SQL Beautifier button on the toolbar. 4. To view the execution plan when using SQL window with PL/SQL developer, sometimes the efficiency of the input statement execution, the analysis of the following table structure, how to improve the efficiency of the query, you can view the execution plan provided by Oracle; To use: Select the SQL statement you want to analyze, and then click the Explain Plan button on the toolbar (that is, execution plan), or press F5 directly. 5. Debug stored procedures sometimes call some stored procedures or debug stored procedures when using PL/SQL developer to operate Oracle: First, select procedures in the browser to the left of PL/SQL developer Locate the stored procedure that you want to call, and then select theTest the stored procedure, right-click, select Test, in the pop-up Test scrīpt window, for the parameter defined as the type in, you need to enter a value for the parameter, and finally click the number of bars button: Start debugger or press F9; Last click: RUN or Ctrl+r 6. The lower left corner shows the window list, click menu Tools, Window list, drag the small pop-up window to the lower-left corner, and then click menu Window->save Layout 7. Prevent Login Timeouts TOOLS-&G T Preferences-->oracle->connection Select "Check Connection" 8. Do not back up the SQL file Tools->preferences->files->backup, Page in Backup files, select disabled

9. View database current character Set parameter settings SELECT * from V$nls_parameters; or select * from Nls_database_parameters or select Userenv (' language ') from dual;
10. View database available character Set parameter settings SELECT * from V$nls_valid_values;

11. Client Nls_lang Setup Method Windows: # Common Chinese Character set set nls_lang=simplified Chinese_china. ZHS16GBK # Common Unicode character set set Nls_lang=american_america. Al32utf8 can be permanently set by modifying the registry key value Hkey_local_machine\software\oracle\homexx\nls_lang
Unix: # Common Unicode character set export Nls_lang=american_america. Al32utf8 # Common Chinese Character set export nls_lang= "simplified Chinese_china". ZHS16GBK can edit the Bash_profile file for permanent setup vi. bash_profile nls_lang= "Simplified Chinese_china". ZHS16GBK Export Nls_lang # makes Bash_profile settings take effect for source. bash_profile

Oracle Common Settings