title: Oracle: Setting Session Parameters in Packages and Procedures
body: <p>I'm a SQL Server DBA currently getting up to speed on Oracle.  I'm trying to create something very similar to sp_WhoIsActive for SQL Server but for Oracle without reinventing the wheel.  Essentially all I'm doing is selecting some values from v$session and inserting them into a table (poor man's ASH/AWR).  </p>
  
  <p>It would seem that in Oracle 12.1, there's a bug when querying dictionary views where it can take forever due to bad parsing logic (Bug 22225899 : SLOW PARSE FOR COMPLEX QUERY).  The work-around is to set a session parameter:</p>
  
  <p><code>alter session set "_optimizer_squ_bottomup"=false;</code></p>
  
  <p>In T-SQL, I could very easily execute a stored procedure in-session and set this variable at runtime.  However in Oracle, it wouldn't seem thats the case.</p>
  
  <p>Sample Code:</p>
  
  <pre><code>CREATE OR REPLACE PROCEDURE SP_DB_ACTIVITY
  (
      v_temp NUMBER :=1
  ) IS
  BEGIN
  alter session set "_optimizer_squ_bottomup"=false;
  INSERT INTO SY_DB_ACTIVITY 
      SELECT  
         &lt;fields&gt;
      FROM
        v$session;
  commit;
  </code></pre>
  
  <p>When I run this, I get the error:</p>
  
  <p><em>"PLS-00103:  Encountered symbol 'ALTER' when expecting one of the following..."</em></p>
  
  <p>Right now, the only way I know how to do this is via a utility like SQL Plus that initiates an interactive user session.  Can anyone give me some direction as to how Oracle handles this situation?  I'd like to bundle this up into a SP or a Package and then call it from Oracle Scheduler.</p>