[Oracle]-[sqlplus related]-serveroutput Parameters

[Oracle]-[sqlplus related]-The serveroutput parameter encountered a problem two days ago. In the 10 GB version, run: select/* + no_index (t1 idx_t1) */* from t1 where n = 3; select * from table (dbms_xplan.display_cursor (null, null, 'advanced '); prompt: PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------SQL_ID 9m7787camwh4m, child number 0 begin: id: = sys. dbms_transaction.local_transaction_id; end; NOTE: cannot fetch plan For SQL _ID: 9m7787camwh4m, CHILD_NUMBER: 0 Please verify value of SQL _ID and CHILD_NUMBER; it coshould also be that the plan is no longer in cursor cache (check v $ SQL _p 8 rows selected check the information that serveroutput needs to be set to off before dbms_xplan can be used to print the execution plan. However, it is strange to try to use set serveroutput off without any response, and you cannot see any set values of serveroutput. Analysis: 1. The preceding statements are used in command windows of PLSQL Developer. PLSQL Developer is a third-party Oracle client tool and may not be able to print the execution plan of this dbms_xplan. 2. serveroutput is a SQL plus statement or configuration parameter, not a database or SQL statement or configuration parameter. 3. The set serveroutput on statement is used to open the output method dbms_output that comes with oracle. After execution, you can use the dbms_output method to output information. Therefore, it cannot be used in SQL window. This command is not written in pl/SQL but executed in SQL/plus. SQL WINDOW can be used without adding set serveroutput on. Data Display: 1. SQL> set serveroutput on. Then, in sqlplus, 2. SQL> store set $ ORACLE_HOME/sqlplus/admin/glogin. SQL replace completes the Save settings. you can view the setting result as follows: SQL> show serveroutput; serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED indicates that the setting is successful. Glogin. the SQL statement is as follows: ---- Copyright (c) Oracle Corporation 1988,200 0. all Rights Reserved. ---- NAME -- glogin. SQL ---- DESCRIPTION -- SQL * Plus global login startup file. ---- Add any sqlplus commands here that are to be executed when a user -- starts SQL * Plus on your system ---- USAGE -- This script is automatically run when SQL * Plus starts -- For backward compatibilitySET PAGESIZE 14 SET SQLPLUSCOMPATIBILITY 8.1.7 -- Used by Trusted OracleCOLUMN rowlabel format A15 -- Used for the show errors commandCOLUMN LINE/col format A8COLUMN error format A65 WORD_WRAPPED -- Used for the show sga commandCOLUMN comment FORMAT a24 -- Defaults for SHOW PARAMETERSCOLUMN name_col_plus_show_param FORMAT a36 heading namecolumn value_col_plus_show_param FORMAT a30 heading value -- Defaults for SET AUTOTRA Ce explain reportCOLUMN id_plus_exp FORMAT 990 HEADING iCOLUMN comment FORMAT 990 HEADING pCOLUMN plan_plus_exp FORMAT a60COLUMN object_node_plus_exp FORMAT a8COLUMN comment FORMAT a29COLUMN other_plus_exp FORMAT a44 is basically formatted. Of course, you can also bypass the restrictions of PLSQL Developer: SQL> select/* test * // * + gather_plan_statistics */* from emp; empno ename job mgr hiredate sal comm deptno ----- ---------- --------- ----------- --------- ------ 7369 smith clerk 7902 1980/12/17 800.00 20 7499 allen salesman 7698 CLERK 1600.00 300.00 7521 7698 jones manager 7839 1981/4/2 2975.00 20 7654 martin salesman 7698 1981/9/28 1250.00 1400.00 30 7698 blake manager 7839 1981/5/1 2850.00 30 7782 clark manager 7839 Jun 2550.00 10 7788 scott analyst 7566 1987/4/19 3000.00 20 7839 king president 1981/11/17 10 7844 turner salesman 7698 CLERK 1500.00 30 0.00 adams clerk 7876 1987/5/23 7788 20 1100.00 james clerk 7900 1981/12/3 7698 30 950.00 ford analyst 7902 1981/12/3 7566 20 3000.00 miller clerk 7934 1982/1/23 7782 1400.00 rows selectedSQL> select SQL _id, child_number, SQL _text from v $ SQL where SQL _text like '% test %'; SQL _ID CHILD_NUMBER SQL _TEXT ------------- ---------- limit 0200bsvkhc917 0 select wrm. last_ash_sample_id from WRM $ _ DATABASE_INSTANCE wrm, (select5ms1dhxbadq64 0 update wrm $ _ snapshot set status = 0, flush_elapsed = greatest (cast2hqz4n3062jhq 0 select/* test * // * + gather_plan_statistics */* from empcptm0vuy03d5 G 0 select SQL _id, child_number, SQL _text from v $ SQL where SQL _text like '% test %' SQL> select * from table (dbms_xplan.display_cursor ('2hqz4n3062jhq ', 0, 'allstats la'); PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------SQL_ID limit, child number 0 ------------------------------------- select/* test * // * + gather_plan_statistics */* from empPlan hash value: 3956160932 Rows | Id | Operation | Name | Starts | E-Rows | A-Time | Buffe hour | 1 | table access full | EMP | 1 | 14 | 14 | 00:00:00. 01 | ---------------------------------------------------------------------------------- 12 rows selected