How To Put Comments Using Spfile

More than once I have been asked, co-workers, or people in a forum if you can put comments on an SPFILE. In the old times it was very usual to comment when changing a parameter (with the date of the change and the old value in case there were performance problems) or when you put some new parameter. Seeing these changes was easy, since the init.ora is at the end a text file. The problem we can have with SPFILE since this is a bianrio file, ofcourse to you can make a strings to SPFILE and see some things. The correct thing is to dump SPFILE to a text file.

So, to put a comment in SPFILE the same alter sentence allows us with the comment clause, look at the example below;

alter system set “optimizer_adaptive_features”=true comment =’21.06.2017 Changed by Antonio NAVARRO’ scope=both sid=’*’;

HTH – Antonio NAVARRO

 

How Set To Broken Jobs From Other Users

Of course, you should not use dbms_job any more, but how many times are we working on an installation or a database that already has them? It would be best to migrate these jobs to dbms_scheduler. In this case I will post a small script to put all jobs to broken, without having to log in as the owner of the same.

 
SET SERVEROUTPUT ON

DECLARE
   UserID        NUMBER;
   Sentence      VARCHAR2 (500);
   Result        PLS_INTEGER;
   HandleCursor  PLS_INTEGER;
   debug         BOOLEAN := FALSE;
BEGIN
  DBMS_OUTPUT.ENABLE(1000000);

  FOR i IN (SELECT JOB, SCHEMA_USER FROM DBA_JOBS WHERE BROKEN != 'Y') LOOP
      if debug then  DBMS_OUTPUT.PUT_LINE ('DEBUG (JOB)         : ' || I.JOB);  end if;
      if debug then  DBMS_OUTPUT.PUT_LINE ('DEBUG (SCHEMA_USER) : ' || I.SCHEMA_USER);  end if;

      SELECT USER_ID INTO UserId FROM DBA_USERS WHERE USERNAME = RTRIM (LTRIM (i.SCHEMA_USER));
      Sentence := 'BEGIN DBMS_JOB.BROKEN (' || i.JOB ||', TRUE); END; ';

      HandleCursor := sys.dbms_sys_sql.open_cursor ();
      sys.dbms_sys_sql.parse_as_user (HandleCursor, Sentence, dbms_sql.native, UserId);
      Result := sys.dbms_sys_sql.execute (HandleCursor);
      sys.dbms_sys_sql.close_cursor(HandleCursor);

      DBMS_OUTPUT.PUT_LINE ('Executing :   ' || Sentence);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE (CHR (10) || 'Please, remember execute commit to close the actual transaction...');
END;
/

The trick here is to make the parseo as the owner user.

HTH – Antonio NAVARRO

ORA-31617 Error When Executing Expdp

Today I get the next error when executiong expdp in a RAC;


ORA-31693: Table data object "FOLE"."HISTI_MOV_QCATCH" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEPOPULATE callout
ORA-31617: unable to open dump file "/mntfc/FLSHPRD070317Dop3_05.dmp" for write
ORA-19505: failed to identify file "/mntwfc/FLSHPRD070317Dop3_05.dmp"
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory

After of search on ML/MOS I have discovered a bug that was the problem. In this version 11.2.0.1, when execute expdp with parallel option, parallel is executed from all actives instances in the cluster. I was trying execute from only one node and I have NFS (Network File System) mount in this node.

Solutions

1- Set cluster_database=false (From doc.) I prefer if is possible stop the other instances.

2- Mount the NFS in all nodes of the cluster, (This was my solution).

HTH – Antonio NAVARRO

How To Install Statspack On 9i Windows-box

Today I have been installing a statspack package for a older database, version 9i, maybe it is not relevant for many people but I want to post it in this entry because of I use the blog like a work diary and documentation place.

First of all we need to create a new tablespace where create the new objects for perfstat user, of course, you can use an existing tablepaspace but it is not a good idea;

 


CREATE TABLESPACE "ESTADISTICAS" DATAFILE
'X:\ORACLE\DATOS\ESTADISTICAS.DBF' SIZE 2147483648 -- 2 Gigas
LOGGING ONLINE PERMANENT BLOCKSIZE 4096
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO

After we need the spcreate.sql scritp, locate on %ORACLE_HOME%\rdbms\admin

foto1

Connect to database as sysdba and execute de sql;

foto2

This sql script call the following scripts;

  • SPCUSR.SQL: Creates the user and grants privileges
  • SPCTAB.SQL: Creates the tables
  • SPCPKG.SQL: Creates the package

Ask me for the password for user perfstat (in clear text);

foto3.PNG

Next ask me for tablespace where locate tables with the data are recolected from the system. You must write down the tablespace name create before. You need a temporary tablespace too;

foto5.PNG

if all is okay you get a reply similar to the next window, however it is necessary to review the spcpkg.lis file for any errors;

foto7.PNG

Now you must to schedule the job to pick the statistics, with the spauto it pick each hour;

foto7

And the output;

foto8

HTH – Antonio NAVARRO

 

How To See Active Mviews

This is other way, more interesnting in my opinion, to see active mviews, I use it from many years, I don’t remember where I get it, maybe ML/MOS, of course, you can see it as usual by query the dba_jobs_running

 

REM
REM Active Mviews Refresh
REM 

SELECT 
   S.OBJ#,
   O.OBJ#,
   S.CONTAINEROBJ#,
   LASTREFRESHDATE,
   PFLAGS,
   XPFLAGS,
   O.NAME,
   O.OWNER#, 
   BITAND(S.MFLAGS, 8) 
FROM 
   OBJ$ O, 
   SUM$ S
WHERE 
   O.OBJ# = S.OBJ# AND 
   O.TYPE# = 42 AND 
   BITAND(S.MFLAGS, 8) = 8;                                                         
/

HTH – Antonio NAVARRO

How To Close A Database Link

Today someone ask me about how to close a dabase link in Oracle, I post a very simple example to close open cursors;

 

 
DB12*ANTO> select version from V$instance;

VERSION
_________________
12.1.0.2.0

DB12*ANTO> REMARK
DB12*ANTO> REMARK TESTCASE I (Using alter session)
DB12*ANTO> REMARK
DB12*ANTO> r
  1* select * from v$dblink

ninguna fila seleccionada

DB12*ANTO> select count (*) from dba_objects@transfer_link;

  COUNT(*)
__________
     20017

DB12*ANTO> select * from v$dblink;

DB_LINK                        OWNER_ID LOG HET PROTOC OPEN_CURSORS IN_ UPD COMMIT_POINT_STRENGTH     CON_ID
____________________________ __________ ___ ___ ______ ____________ ___ ___ _____________________ __________
TRANSFER_LINK.XXXX.COM               62 YES YES UNKN              0 YES NO                      1          0

DB12*ANTO> 
DB12*ANTO> 
DB12*ANTO> ALTER SESSION CLOSE DATABASE LINK transfer_link;
ERROR:
ORA-02080: el enlace de base de datos está en uso

DB12*ANTO> REMARK
DB12*ANTO> REMARK Of course, the transaction is open you need to close (commit/rollback)
DB12*ANTO> REMARK
DB12*ANTO> ROLLBACK;

Rollback terminado.

DB12*ANTO> ALTER SESSION CLOSE DATABASE LINK transfer_link;

Sesión modificada.

DB12*ANTO> REMARK
DB12*ANTO> REMARK TESTCASE II (Using DBMS_SESSION PACKAGE)
DB12*ANTO> REMARK
DB12*ANTO> exec DBMS_SESSION.CLOSE_DATABASE_LINK ('transfer_link');
BEGIN DBMS_SESSION.CLOSE_DATABASE_LINK ('transfer_link'); END;

*
ERROR en línea 1:
ORA-02081: el enlace de base de datos no está abierto
ORA-06512: en "SYS.DBMS_SESSION", línea 191
ORA-06512: en línea 1

DB12*ANTO> REMARK The database link is close, you need to user the db link to open it
DB12*ANTO>  select count (*) from dba_objects@transfer_link;

  COUNT(*)
__________
     20017

DB12*ANTO> SELECT * FROM V$DBLINK;

DB_LINK                        OWNER_ID LOG HET PROTOC OPEN_CURSORS IN_ UPD COMMIT_POINT_STRENGTH     CON_ID
____________________________ __________ ___ ___ ______ ____________ ___ ___ _____________________ __________
TRANSFER_LINK.XXXX.COM               62 YES YES UNKN              0 YES NO                      1          0

DB12*ANTO> COMMIT;

Confirmación terminada.

DB12*ANTO>  exec DBMS_SESSION.CLOSE_DATABASE_LINK ('transfer_link');

Procedimiento PL/SQL terminado correctamente.

DB12*ANTO> 

HTH – Antonio NAVARRO

 

Detecting Tracing

Sometimes we need to know if are there acive traces in the system, of course, there are several ways. Here I like to show, maybe, the most easy way. You need query the v$session, with the columns show below you can quickly identified sessions with activet traces;

 
 -- version 10g ---
 SQL_TRACE                      
 SQL_TRACE_WAITS                
 SQL_TRACE_BINDS                

 NOTE: SQL_TRACE_PLAN_STATS does't exists in this version 

 -- version 11g and 12c ---
 SQL_TRACE                              
 SQL_TRACE_WAITS                        
 SQL_TRACE_BINDS                        
 SQL_TRACE_PLAN_STATS                   

From the Oracle Documentation, the concept for each fields is like show below;

  • SQL_TRACE; Indicates whether SQL tracing is enabled (ENABLED) or disabled (DISABLED)
  • SQL_TRACE_WAITS; Indicates whether wait tracing is enabled (TRUE) or not (FALSE)
  • SQL_TRACE_BINDS; Indicates whether bind tracing is enabled (TRUE) or not (FALSE)
  • SQL_TRACE_PLAN_STATS; Frequency at which row source statistics are dumped in the trace files for each cursor: (never, first_execution or all_executions)

An example;

 
SFFF*ANTO> column program format a25
SFFF*ANTO> select sid, username, program, sql_trace, sql_trace_waits, sql_trace_binds, sql_trace_plan_stats
  2  from v$session
  3  where
  4  sql_trace != 'DISABLED' OR
  5  sql_trace_waits != 'FALSE' OR
  6  sql_trace_binds != 'FALSE' OR
  7  SQL_TRACE_PLAN_STATS != 'FIRST EXEC'
  8  /
SFFF*ANTO> 
SFFF*ANTO> EXEC DBMS_MONITOR.session_trace_enable;

Procedimiento PL/SQL terminado correctamente.

SFFF*ANTO> select sid, username, program, sql_trace, sql_trace_waits, sql_trace_binds, sql_trace_plan_stats
  2  from v$session
  3  where
  4  sql_trace != 'DISABLED' OR
  5  sql_trace_waits != 'FALSE' OR
  6  sql_trace_binds != 'FALSE' OR
  7  SQL_TRACE_PLAN_STATS != 'FIRST EXEC'
  8  /

    SID USERNAME        PROGRAM                   SQL_TRAC SQL_T SQL_T SQL_TRACE_
_______ _______________ _________________________ ________ _____ _____ __________
     63 ANTONION        sqlplusw.exe              ENABLED  TRUE  FALSE FIRST EXEC

HTH – Antonio NAVARRO