Summary of bad oracle programming and operation habits

 

After years of experience with oracle, I found myself and some people around me have some bad development and operation methods in oracle usage, which can be used for reference.

I. oracle programming:

1. The oracle code writing format is not standard. You can refer to the built-in oracle code format for writing.

2. The naming rules for oracle Objects should be uniform. Do not use numbers, Chinese characters, or pinyin.

3. The table name and field name must be annotated.

For example, comment on table name is 'table comment content'; -- add a comment to the table

Comment on column table name. column name is 'comment on column content'; -- add comment to column

4. Table constraints must be created separately. Do not add primary, unique and foreign key constraints directly after the column name.

5. The input and output parameters in the stored procedure must be named in a standardized manner and should not be written in crossover mode.

6. Definitions of parameters and intermediate variables should be defined using % type as much as possible. Comments should be added to all variables.

7. The stored procedure must have an exception handling section (except for intermediate procedures, which are called to handle exceptions ).

8. When you use a cursor to return results, the exception handling part must also have a cursor to return results.

9. to list specific field names, select * is strictly prohibited.

10. The insert statement must list specific field names. You are not allowed to use insert into tablename values (...) Method,

Or insert into tablename (...) Values (...) Or insert into tablename select.

11. Logic implemented using static SQL. Try not to use dynamic SQL. If dynamic SQL is used, bind variables as much as possible.

12. If the stored procedure or function returns a cursor type parameter, the cursor must be opened before any exit and closed after use.

13. Do not press enter to wrap the insert into statement. Otherwise, an error will be reported during loading.

14. Use 'end Object Name; 'to indicate an error at the END of the stored procedure and function. Write 'end' directly to END.

15. Different types of object scripts should be written in different files. Mixed writing is strictly prohibited.

16. databases with multiple domains must use 'domain name. Object name' in data scripts to prevent confusion.

17. The stored procedures and functions must be annotated accordingly.

Ii. oracle operations:

1. Each user uses their respective accounts to log on. Do not use any system account to log on, and strictly control the dba account.

2. It is strictly prohibited to use select * from t_tablename for update or select t. *, t. rowid from t_tablename t in PL/SQL to edit data by clicking the lock icon.

3. It is strictly prohibited to use page tools to import a large amount of data during peak business hours. Special tools must be used to import and export large amounts of data.

4. It is strictly prohibited to directly test stored procedures and functions in PL/SQL during business peak hours to easily lock tables.

5. It is strictly prohibited to add indexes to large tables during peak business hours. Before creating an index, it is best to check whether the index tablespace is sufficient.

6. Do not modify or compile database objects at will. Check whether invalid objects exist immediately after modification or compilation.

7. Before creating a tablespace, log on to the minicomputer to check whether the space in the/dev/directory is sufficient. Then, create a tablespace Based on the naming rules.

8. After the temporary and intermediate tables are created on a daily basis, clean them in time. truncate the table first, and then drop the table.

9. It is strictly prohibited to log on to the server for a long time without any operation. You must log out immediately after use.

10. It is strictly prohibited to perform unrelated operations on online databases.

11. You are prohibited from deleting system logs, alarm logs, logs in the windows event viewer, and logs of various servers at will.

12. When you need to restart the service, you must first obtain the consent of the relevant departments and personnel. It is strictly prohibited to restart the service without authorization.

13. Any upgrade during peak business hours is strictly prohibited. Unless otherwise specified, all relevant personnel must be notified to test the upgrade in time.

14. All Delete and update operations must be backed up before they are performed. Direct backup operations are strictly prohibited.

15. Be cautious before using the truncate command.

16. It is strictly prohibited to use 'Kill-9 Process' to end minicomputer processes.

These are some common bad habits. I hope to use them to correct them and develop good habits.