Oracle statement execution history query-required skills for testing and implementers

Find something to do. After this project was completed, I found that on-site testers generally did not grasp the SQL statements at the front-end (that is, they clicked the operations on the front-end interface and then went to the database to find out which tables the operation was just performed ). Later, I chatted with several other friends who were doing the test and basically did not know these views. I felt very surprised. This should be a tester and implementer (not involved in project database development, unfamiliar with tables for specific business operations) common skills. So I decided to summarize and recommend this tips.

In fact, it is very simple, just two built-in views of Oracle: v $ SQL _bind_capture and v $ SQL.

1. First click the system foreground operation, and then query the database

Select distinct SQL _text, SQL _id, address From v $ SQL Where module = 'jdbc Thin client' -- Database Connection Method And last_active_time> sysdate-0.0005 -- a very short time, otherwise too much is difficult to distinguish And parsing_schema_name like 'ventory % '; -- database username

2. We can know through this query that the SQL statement operated on the database just now (if the statement is very long, you have to look at the SQL _FULLTEXT field). If you want to see the detailed parameters, you have to continue.

Select t. NAME, t. VALUE_STRING, t .* From gv $ SQL _bind_capture t Where t. address = '0700000916bf5110 ';

Value_string is the variable value that you pass in for execution. Unfortunately, the time cannot be displayed, but we have a solution to continue the following query.

Select t. value_string, T. NAME, T. datatype_string, DUMP (t. value_anydata ), ANYDATA. accesstimestamp (t. value_anydata) From gv $ SQL _bind_capture t Where address = '000000015e3964a8 ';

I will not explain the specifics.

Note the v $ SQL _bind_capture view that, due to different database parameter configurations, the refresh time for saving the bound variable value is different, and the implicit parameter _ cursor_bind_capture_interval, this is the parameter that controls the capture frequency of Bound variables. The default value is 900 (15 min ). The value of the modified parameter is very small. You can quickly get the value of the bound variable: alter system set "_ cursor_bind_capture_interval" = 5 scope = both; however, you have to set this according to your database. If the parameters you capture remain unchanged, pay attention to the frequency of refreshing the capture.

Select * from v $ parameter t where t. name like '% bind % ';

Note that if your database is a RAC Multi-Point Cluster, the data in the table v $ SQL _bind_capture is not stored in the shared storage area, it is on the single-point database you connect to, so sometimes you may not be able to find it, then you have to log on to a single-point database to try your luck.

When you are new to other people's second-hand code, or as a tester, implementer, or data cutover, you do not know which tables are operated by each business, the two views are not very nice to query. Haha