SQL _trace causes Database Error

SQL _trace causes Database Error
Oracle9i uses the SQL Trace facility to collect performance data on individual SQL statements. the information generated by SQL Trace is stored in SQL trace files. SQL Performance Analyzer consumes the following information from these files: SQL text and username under which parse occurred Bind values for each execution CPU and elapsed times Physical reads and logical reads Number of rows processed Execution plan for each SQL statement (only captured if the cursor for the SQL statement is closed) Although it is possible to enable SQL Trace for an instance, it is recommended that you enable SQL Trace for a subset of sessions instead. when the SQL Trace facility is enabled for an instance, performance statistics for all SQL statements executed in the instance are stored into SQL trace files. Using SQL Trace in this way can have a severe performance impact and may result in increased system overhead, excessive CPU usage, and inadequate disk space. it is required that trace level be set to 4 to capture bind values, along with the execution plans. for production systems running Oracle Database 10g Release 1, use the DBMS_MONITOR.SESSION_TRACE_ENABLE procedure to enable SQL Trace transpar Ently in another session. you shoshould also enable binds explicitly by setting the binds procedure parameter to TRUE (its default value is FALSE ). after enabling SQL Trace, identify the SQL trace files containing statistics for a representative set of SQL statements that you want to use with SQL Performance Analyzer. you can then copy the SQL trace files to the SQL Performance Analyzer system. once The SQL workload is captured in the SQL trace files, disable SQL Trace on the production system. eg: [SQL] SYS @ orcl # startup ORA-32004: the obsolete and/or deprecated parameter (s) specified ORACLE routine has been started. Total System Global Area 805875712 bytes Fixed Size 2148720 bytes Variable Size 603981456 bytes Database Buffers 192937984 bytes Redo Buffers 6807552 bytes Database load is complete. The database has been opened. SYS @ orcl # show parameter SQL _trace name type ------------------------------------------------------ VALUE ---------------------------- SQL _trace boolean FALSE SYS @ orcl # view the log: [SQL] diagnostic_dest = "/opt/oracle" Deprecated system parameters with specified values: SQL _trace End of deprecated system parameter listing Tue Apr 16 22:16:05 2013 PMON started with pid = 2, OS id = 17577 Tue Apr 16 22: 22:16:05 VKTM started with pid = 3, OS id = 2013 at elevated priority VKTM running at (20) MS precision Tue Apr 16 17579 DIAG started with pid = 4, OS id = 17583 Tue Apr 16 22:16:05 2013 DBRM started with pid = 5, OS id = 17585 Tue Apr 16 22:16:05 2013 PSP0 started with pid = 6, OS id = 17587 Tue Apr 16 22:16:05 2013 DSKM started with pid = 7, OS id = 17589 Tue Apr 16 22:16:05 2013 DIA0 started with pid = 8, OS id = 17591 Tue Apr 16 22:16:05 2013 MMAN started with pid = 7, OS id = 17593 Tue Apr 16 22:16:05 2013 LGWR started with pid = 10, OS id = 17597 Tue Apr 16 22:16:06 2013 CKPT started with pid = 11, OS id = 17599 Tue Apr 16 22:16:06 2013 solution: [SQL] SYS @ orcl # alter system reset SQL _trace; the system has been changed. SYS @ orcl # shutdown the immediate database. The database has been detached. The ORACLE routine has been disabled. SYS @ orcl # the startup ORACLE routine has been started. Total System Global Area 805875712 bytes Fixed Size 2148720 bytes Variable Size 603981456 bytes Database Buffers 192937984 bytes Redo Buffers 6807552 bytes Database load is complete. The database has been opened. SYS @ orcl #