Written by Today I started having a look to the Oracle Database Exadata Express Cloud Service announced last week at Oracle OpenWorld. Note that since the amount of resources provided (in summary, 1 OCPU, 20 or 50 GB of database storage) is very limited, in general, in my opinion that service will only be useful for functional tests. In fact, if it wasn’t because that is the very first 12.2 release (12.2.0.0.3 according to V$VERSION) available, I doubt I would care about it…
A first important thing to know is that the service gives access to a PDB via SQL*Net only (in addition to the web-based interfaces like APEX). In other words, the OS access is precluded.
Given that no OS access is available, one of the first thing I wanted to test is whether and how SQL trace is supported. In fact, SQL trace remains a critical feature for anyone working with Oracle Database.
The first thing I tried to do is to activate SQL trace for my session through the DBMS_MONITOR package:
SQL> execute dbms_monitor.session_trace_enable
BEGIN dbms_monitor.session_trace_enable; END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_MONITOR' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored |
As you can see, no access to that package is provided. BTW, I’m testing with the PDB_ADMIN user, the highest privileged user (AFAIK) that someone gets when using the service.
Given that the package DBMS_MONITOR isn’t limited to activate SQL trace for the current session, I wasn’t too surprised about it. That said, the next three attemps really surprised me:
SQL> ALTER SESSION SET sql_trace = TRUE; ERROR: ORA-01031: insufficient privileges SQL> execute dbms_session.session_trace_enable BEGIN dbms_session.session_trace_enable; END; * ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "SYS.DBMS_SESSION", line 307 ORA-06512: at line 1 SQL> ALTER SESSION SET events 'sql_trace'; ERROR: ORA-01031: insufficient privileges |
So, it looks like no privileges to activate SQL trace are provided. That must be a bug! Hence, as soon as I publish this post, I’ll immediated open an SR. UPDATE Sep 25, 18:45: as pointed out by Laurent Leturgez, this isn’t a bug, it’s a documented limitation.
Anyway, since I consider the previous issue a bug, I continued my research to find out how to get access to the trace files on the server. And, in similar situations (i.e. when I don’t receive the OS access), I usually take advantage of a database directory. Let’s try to create one for the directory where the trace files are stored:
SQL> SELECT value 2 FROM v$diag_info 3 WHERE name = 'Default Trace File'; VALUE ------------------------------------------------------------------------------------------------------------------------ /u02/app/oracle/diag/rdbms/cfcdbb1/cfcdbb1_1/trace/cfcdbb1_1_ora_19884.trc SQL> CREATE DIRECTORY diag_trace AS '/u02/app/oracle/diag/rdbms/cfcdbb1/cfcdbb1_1/trace/'; CREATE DIRECTORY diag_trace AS '/u02/app/oracle/diag/rdbms/cfcdbb1/cfcdbb1_1/trace/' * ERROR at line 1: ORA-01031: insufficient privileges |
Unfortunately, but understandably, also creating a directory isn’t allowed. Therefore, I told me that another way of doing it should be available. After browsing through the data dictionary I found out that a number of new dynamic performance views contains DIAG and TRACE in their name:
- V$DIAG_APP_TRACE_FILE
- V$DIAG_OPT_TRACE_RECORDS
- V$DIAG_SESS_OPT_TRACE_RECORDS
- V$DIAG_SESS_SQL_TRACE_RECORDS
- V$DIAG_SQL_TRACE_RECORDS
- V$DIAG_TRACE_FILE
- V$DIAG_TRACE_FILE_CONTENTS
The last two seems to provide exactly what I was looking for. Let’s have a look to them:
SQL> desc v$diag_trace_file Name Null? Type --------------- ------ ---------------------------- ADR_HOME VARCHAR2(444) TRACE_FILENAME VARCHAR2(68) CHANGE_TIME TIMESTAMP(3) WITH TIME ZONE MODIFY_TIME TIMESTAMP(3) WITH TIME ZONE CON_ID NUMBER SQL> SELECT trace_filename 2 FROM v$diag_trace_file 3 WHERE trace_filename LIKE 'cfcdbb1_1_ora_%' 4 ORDER BY 1; TRACE_FILENAME ------------------------- cfcdbb1_1_ora_100689.trc cfcdbb1_1_ora_103319.trc cfcdbb1_1_ora_104994.trc cfcdbb1_1_ora_113270.trc cfcdbb1_1_ora_19243.trc cfcdbb1_1_ora_19884.trc cfcdbb1_1_ora_34663.trc cfcdbb1_1_ora_34665.trc cfcdbb1_1_ora_45183.trc cfcdbb1_1_ora_46745.trc cfcdbb1_1_ora_4706.trc cfcdbb1_1_ora_4857.trc cfcdbb1_1_ora_52669.trc cfcdbb1_1_ora_54810.trc cfcdbb1_1_ora_76727.trc cfcdbb1_1_ora_81232.trc cfcdbb1_1_ora_81236.trc SQL> desc v$diag_trace_file_contents Name Null? Type --------------- ------ ---------------------------- ADR_HOME VARCHAR2(444) TRACE_FILENAME VARCHAR2(68) RECORD_LEVEL NUMBER PARENT_LEVEL NUMBER RECORD_TYPE NUMBER TIMESTAMP TIMESTAMP(3) WITH TIME ZONE PAYLOAD VARCHAR2(4000) SECTION_ID NUMBER SECTION_NAME VARCHAR2(64) COMPONENT_NAME VARCHAR2(64) OPERATION_NAME VARCHAR2(64) FILE_NAME VARCHAR2(64) FUNCTION_NAME VARCHAR2(64) LINE_NUMBER NUMBER THREAD_ID VARCHAR2(64) SESSION_ID NUMBER SERIAL# NUMBER CON_UID NUMBER CONTAINER_NAME VARCHAR2(30) CON_ID NUMBER SQL> SELECT payload 2 FROM v$diag_trace_file_contents 3 WHERE trace_filename = 'cfcdbb1_1_ora_19884.trc'; PAYLOAD -------------------------------------------------------------------------------------- Trace file /u02/app/oracle/diag/rdbms/cfcdbb1/cfcdbb1_1/trace/cfcdbb1_1_ora_19884.trc Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production Build label: RDBMS_12.2.0.0.3_LINUX.X64_160720 ORACLE_HOME: /u01/app/oracle/product/12.2.0.0.3/dbhome_1 System name: Linux Node name: cfcldx0170.usdc2.oraclecloud.com Release: 2.6.39-400.264.1.el6uek.x86_64 Version: #1 SMP Wed Aug 26 16:42:25 PDT 2015 Machine: x86_64 Storage: Exadata Instance name: cfcdbb1_1 Redo thread mounted by this instance: 1 Oracle process number: 46 Unix process pid: 19884, image: oracle@cfcldx0170.usdc2.oraclecloud.com *** 2016-09-25T14:10:17.628206+00:00 (XZECXPHEEJ(10)) *** SESSION ID:(636.380) 2016-09-25T14:10:17.628239+00:00 *** CLIENT ID:() 2016-09-25T14:10:17.628244+00:00 *** SERVICE NAME:(xzecxpheej.usdc2.oraclecloud.com) 2016-09-25T14:10:17.628248+00:00 *** MODULE NAME:(SQL*Plus) 2016-09-25T14:10:17.628253+00:00 *** ACTION NAME:() 2016-09-25T14:10:17.628257+00:00 *** CLIENT DRIVER:(SQL*PLUS) 2016-09-25T14:10:17.628261+00:00 *** CONTAINER ID:(10) 2016-09-25T14:10:17.628265+00:00 Backoff - 3, time increment - 60000 *** 2016-09-25T14:13:49.831644+00:00 (XZECXPHEEJ(10)) Backoff - 3, time increment - 60000 *** 2016-09-25T14:23:34.711288+00:00 (XZECXPHEEJ(10)) |
As you can see, provided it’ll be possible to create a trace file, the view V$DIAG_TRACE_FILE_CONTENTS provides access to its content.
Note that in my case all other dynamic performance views mentioned above return no rows. So, further investigation is necessary… But, based on their names, I expect that they return part of the information stored in a trace file.
Wasn’t there an old, old (version 6 and earlier) trick of adding some text to the SQL statement that would cause it to start tracing? Even if it worked, I wouldn’t consider it extremely helpful but it might be an interesting exercise.
Hi Jeff
I’m not aware of a pre-7.0 trick (I started using Oracle Database with version 7.0.15). So, if you remember how to do it, I would be happy to test it.
Cheers,
Chris