Better to be safe than sorry…

I’ve always been worried about taking a script that is fine to run in my non-production environments (in particular a DROP script) and accidentally running it in a Production environment, shortly followed by the typing up of a fresh resume to look for a new job once the mistake is discovered Smile

The standard means of a “least privilege required” model when it comes to Production access is a good step along this road to protecting the database. But ultimately, at one time or another, an administrator with appropriate privileges, good intentions, but (say) bad cut-paste skills might inadvertently run a drop command against a database they did not want to.  (A well publicised example of this happened quite recently https://www.theregister.co.uk/2017/02/01/gitlab_data_loss/)

So it would be nice to have a method of adding a ‘double check’ facility so that we can guard against a DROP being run on your production system inadvertently. That way, even if you did  run that drop script against Production, you would be “saved from yourself”. Here’s one such possible means you might want to consider. We’ll use a context variable that has to be set in order for DROP commands to be processed.


--
-- Our context
--
SQL> create context DDL_CHECK using ddl_checker;

Context created.

--
-- A routinte to set it
--
SQL> create or replace
  2  procedure ddl_checker(p_val varchar2) is
  3  begin
  4    dbms_session.set_context('DDL_CHECK','ENABLED',p_val);
  5  end;
  6  /

Procedure created.

--
-- If we are on the Production database, DROP DDL's only proceed is the context variable is set
--
SQL> create or replace
  2  trigger ddl_checker_before
  3  before drop on scott.schema
  4  begin
  5    if sys.database_name like '%PROD%' and sys_context('DDL_CHECK','ENABLED') is null
  6    then
  7      raise_application_error(-20000,'DDL confirmation context is not set');
  8    end if;
  9  end;
 10  /

Trigger created.

--
-- And after every DROP, the context is reset for the next use so it cannot be left on inadvertently
--
SQL> create or replace
  2  trigger ddl_checker_after
  3  after drop on scott.schema
  4  begin
  5    ddl_checker(null);
  6  end;
  7  /

Trigger created.

SQL>
SQL>
SQL> grant execute on ddl_checker to scott;

Grant succeeded.

SQL>

So now we can see what happens when SCOTT attempts some DDL in Production by mistake.


SQL> conn scott/tiger
Connected.

SQL> drop table t purge;
drop table t purge
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: DDL confirmation context is not set
ORA-06512: at line 4

--
-- We must explicitly express our intention to perform a DDL
--
SQL> exec admin.ddl_checker('true')

PL/SQL procedure successfully completed.

--
-- and only then are we allowed to run a DROP command
--
SQL> drop table t purge;

Table dropped.

--
-- and in doing so, we don't have any lingering access
--
SQL> drop table t1 purge;
drop table t1 purge
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: DDL confirmation context is not set
ORA-06512: at line 4


SQL>

I stress – this is not about securing administrator access or replacing your existing processes for auditing or locking down the privileges on your production systems. This is just about that extra check to help your over-worked and/or over-tired administrators from committing a small mistake that becomes a catastrophic error.

DDL triggers – interesting results

This question came in on AskTom, yielding a very interesting result when it comes to DDL triggers. To set the scene, I’ll first create a table called T which is just a copy of SCOTT.EMP


SQL> create table scott.t as select * from scott.emp;

Table created.

SQL> desc scott.t
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- -----------------
 EMPNO                                                             NOT NULL NUMBER(4)
 ENAME                                                                      VARCHAR2(10)
 JOB                                                                        VARCHAR2(9)
 MGR                                                                        NUMBER(4)
 HIREDATE                                                                   DATE
 SAL                                                                        NUMBER(7,2)
 COMM                                                                       NUMBER(7,2)
 DEPTNO                                                                     NUMBER(2)

Now let’s say our requirement is to capture anything that relates to columns for tables in the SCOTT schema. So if we create a table, then we want to see all of those columns, and also, if we alter an existing table, then we want to see what the table looks like as a result after the columns have been added or dropped. So let’s build a simple DDL trigger to do that. First, I’ll create a procedure which will list all of the columns for any table name that we pass it.  We’ll work exclusively in SCOTT for this example.


SQL> create or replace
  2  procedure create_audit_trigger(p_tab varchar2) is
  3  begin
  4    for i in ( select column_name from dba_tab_columns
  5               where table_name = p_tab
  6               and owner = 'SCOTT'
  7             )
  8    loop
  9      dbms_output.put_line(i.column_name);
 10    end loop;
 11  end;
 12  /

Procedure created.

And we’ll give it a quick test so make sure it works


SQL> set serverout on
SQL> exec create_audit_trigger('EMP')
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO

PL/SQL procedure successfully completed.

We’ll now put that inside our DDL trigger any time we perform an ALTER or CREATE DDL operation on a table.


SQL> create or replace
  2  TRIGGER audit_trigger_update
  3  AFTER ALTER OR CREATE ON scott.SCHEMA
  4  WHEN (ORA_DICT_OBJ_TYPE = 'TABLE')
  5  BEGIN
  6    create_audit_trigger(ORA_DICT_OBJ_NAME);
  7  END;
  8  /

Trigger created.

SQL> conn scott/tiger
Connected.

Now we’ll create a table and see if it works.


SQL> set serverout on
SQL> create table t1 ( x int, y int );
X
Y

Table created.

And we’ll drop a column to test that


SQL> alter table t1 drop column y;
X

So far so good. Now let us add a column to the table.


SQL> alter table t add x int;
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO  -- ???? no X

Table altered.

This is an unexpected result. At the time within the trigger we query the data dictionary to obtain a list of columns, it does not yet appear in the definition. Now that the statement has completed, if we perform a describe on the table, we can see that the new column X is indeed there.


SQL> desc t
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- --------------
 EMPNO                                                                   NOT NULL NUMBER(4)
 ENAME                                                                            VARCHAR2(10)
 JOB                                                                              VARCHAR2(9)
 MGR                                                                              NUMBER(4)
 HIREDATE                                                                         DATE
 SAL                                                                              NUMBER(7,2)
 COMM                                                                             NUMBER(7,2)
 DEPTNO                                                                           NUMBER(2)
 X                                                                                NUMBER(38)

The only workaround I can currently think of is to do work asynchronously, via DBMS_JOB. Rather than DBMS_OUTPUT, I’ll log the column names in a table so we can view them after the fact:


SQL> create table col_debug ( ts timestamp, col_name varchar2(50));

Table created.

SQL> create or replace
  2  procedure create_audit_trigger(p_tab varchar2) is
  3    l_now timestamp := systimestamp;
  4  begin
  5    for i in ( select column_name from dba_tab_columns
  6               where table_name = p_tab
  7               and owner = 'SCOTT'
  8             )
  9    loop
 10      insert into col_debug values (l_now,i.column_name);
 11    end loop;
 12    commit;
 13  end;
 14  /

Procedure created.

SQL> create or replace
  2  TRIGGER audit_trigger_update
  3  AFTER ALTER OR CREATE ON scott.SCHEMA
  4  WHEN (ORA_DICT_OBJ_TYPE = 'TABLE')
  5  declare
  6    j int;
  7  BEGIN
  8    dbms_job.submit(j,'create_audit_trigger('''||ORA_DICT_OBJ_NAME||''');');
  9  END;
 10  /

Trigger created.

Now I repeat the addition of a column, and the check out debugging table


SQL> alter table t add x1 int;

Table altered.

SQL> select * from col_debug;

TS                                                                          COL_NAME
--------------------------------------------------------------------------- ---------------
24-FEB-17 03.02.36.553000 PM                                                EMPNO
24-FEB-17 03.02.36.553000 PM                                                ENAME
24-FEB-17 03.02.36.553000 PM                                                JOB
24-FEB-17 03.02.36.553000 PM                                                MGR
24-FEB-17 03.02.36.553000 PM                                                HIREDATE
24-FEB-17 03.02.36.553000 PM                                                SAL
24-FEB-17 03.02.36.553000 PM                                                COMM
24-FEB-17 03.02.36.553000 PM                                                DEPTNO
24-FEB-17 03.02.36.553000 PM                                                X
24-FEB-17 03.02.36.553000 PM                                                X1

10 rows selected.

So the trigger submits a job rather than does the work itself.

Why does an ADD column operation behave differently to DROP and CREATE? That remains a mystery Smile


					

Duplicate constraints are impossible right ?

Here’s a very simple example of a table called PARENT being a (surprise surprise) parent in a referential integrity relationship to a (drum roll for my choice of name) CHILD table


SQL> create table parent ( p int, constraint PAR_PK primary key (p) );

Table created.

SQL> create table child ( c int,
  2        p int
  3      );

Table created.

SQL> alter table child add constraint fk1 foreign key ( p ) references parent ( p );

Table altered.

That is all as we would expect, and similarly, if I inadvertently try to add the same foreign key constraint, I’ll get an error


SQL> alter table child add constraint fk2 foreign key ( p ) references parent ( p );
alter table child add constraint fk2 foreign key ( p ) references parent ( p )
                                     *
ERROR at line 1:
ORA-02275: such a referential constraint already exists in the table

So you might be wondering, how on earth did I get myself into the following calamity on my database:


SQL> select c.constraint_name, cc.column_name, c.r_constraint_name
  2  from   user_constraints c,
  3         user_cons_columns cc
  4  where  c.table_name = 'CHILD'
  5  and    c.constraint_type = 'R'
  6  and    c.constraint_name = cc.constraint_name;

CONSTRAINT_NAME                COLUMN_NAME                    R_CONSTRAINT_NAME
------------------------------ ------------------------------ --------------------
FK1                            P                              PAR_PK
FK2                            P                              PAR_PK

Yes – thats TWO foreign key constraints implementing the identical check.  How did I bypass the duplicate check we saw above ?

It’s just a little idiosyncrasy in all versions of Oracle since the inception of foreign key constraints. If you specify the constraints as part of the table creation DDL, the check for duplicates is skipped.


SQL> create table child ( c int,
  2    p int,
  3    constraint fk1 foreign key ( p ) references parent ( p ) ,
  4    constraint fk2 foreign key ( p ) references parent ( p )
  5  );

Table created.

It is worth looking for and rectifying this condition on your databases, because it can lead to confusing errors, for example, when you do a datapump of such a schema – the import will (correctly) fail on the second constraint, but you’ll be scratching your head when you go look at the imported result and see that the constraint is there !

This is fixed in 12c onwards.


SQL> create table child ( c int,
  2    p int,
  3    constraint fk1 foreign key ( p ) references parent ( p ) ,
  4    constraint fk2 foreign key ( p ) references parent ( p )
  5  );
  constraint fk2 foreign key ( p ) references parent ( p )
                 *
ERROR at line 4:
ORA-02274: duplicate referential constraint specifications

Partition count for interval partitioned tables

When dealing with a RANGE partitioned table, the defined partitions dictate all of the data that can be placed into the table. For example, if I have a SALES table as per below


SQL> create table SALES
  2    ( cal_year  date,
  3      txn_id    int,
         ...
         ...
 24    )
 25  partition by range ( cal_year )
 26  (
 27    partition p_low values less than ( date '2000-01-01' ),
 28    partition p2000 values less than ( date '2001-01-01' ),
         ...
         ...
 34    partition p2016 values less than ( date '2017-01-01' )
 35  );

Table created.

then the existing partitions define a natural upper bound on the value of CAL_YEAR that I can insert into the table. For example, if I attempt to add a row for the year 2018, I get the familiar ORA-14400 that has called out many a DBA at the stroke of midnight on New Years Eve Smile


SQL> insert into SALES
  2  values ( date '2018-01-01', .... );

insert into SALES
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

As many will know, the resolution to this is either a maintenance task to ensure that there are sufficient partitions defined, or to use the INTERVAL partitioning method, which came available in 11g.


SQL> create table SALES
  2    ( cal_year  date,
  3      txn_id    int,
         ...
         ...
 23    )
 24  partition by range ( cal_year )
 25  INTERVAL( NUMTOYMINTERVAL(1,'YEAR'))
 26  (
 27    partition p_low values less than ( date '2000-01-01' ),
 28  );

Table created.

And I can observe partitions being created as required as data is added to the table


SQL> select PARTITION_NAME, HIGH_VALUE
  2  from   user_tab_partitions
  3  where  table_name = 'SALES';

PARTITION_NAME            HIGH_VALUE
------------------------- --------------------------------
P00                       TIMESTAMP' 2000-01-01 00:00:00'

SQL> insert into SALES
  2  values ( to_date('12-DEC-2011'),....);

SQL> select PARTITION_NAME, HIGH_VALUE
  2  from   user_tab_partitions
  3  where  table_name = 'SALES';

PARTITION_NAME            HIGH_VALUE
------------------------- --------------------------------
P00                       TIMESTAMP' 2000-01-01 00:00:00'
SYS_P362                  TIMESTAMP' 2012-01-01 00:00:00'

But this isn’t a post about how interval partitioning is defined, because it’s a topic that is now well understood and well detailed in the documentation and on many blogs.

I wanted to touch on a something more subtle that you might encounter when using interval partitioned tables. Let me do a query on the SALES table, which has been recreated (as INTERVAL partitioned) but is empty. Here is the execution plan when I query the table.


SQL> select * from SALES; --empty


-------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Pstart| Pstop |
-------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |     1 |       |       |
|   1 |  PARTITION RANGE ALL|       |     1 |     1 |1048575|
|   2 |   TABLE ACCESS FULL | SALES |     1 |     1 |1048575|
-------------------------------------------------------------

Wow! One million partitions ! That might seem odd, because we know that our table has been defined only with a single partition, and even that might not be instantiated yet depending on our choice of “deferred_segment_creation” parameter on the database. But the explanation is relatively simple. The moment we define a table as interval partitioned, we in effect know “in advance” the definition of every single interval that will ever follow. The starting point for the intervals is known due to the initial partition definition in the DDL, and the size/length of the interval maps out every possible future partition.

image

The maximum number of partitions is 1048575, which is then reflected in the execution plan.

You’ll see similar information when you create an index on such a table. If the index is local, and hence follows the same partitioning scheme as the underlying table, then it too has potentially 1048575 partitions all not yet in use, but known in advance. So if you look at the PARTITION_COUNT column for such an index, you’ll also see that the database will state that it has a (very) high partition count


SQL> create index sales_ix on sales ( some_col ) local;

Index created.

SQL> select TABLE_NAME,INDEX_NAME,PARTITION_COUNT from user_part_indexes;

TABLE_NAME                     INDEX_NAME                     PARTITION_COUNT
------------------------------ ------------------------------ ---------------
SALES                          SALES_IX                               1048575

1 row selected.

So if you see anything suggesting one million partitions, double check to see if you really have that many.

image

The SQL Loader log file … as data

I had an interesting AskTom question recently where the poster was using SQL Loader to load in tables, but wanted to be able to analyze the resultant log file after execution.  And of course, what better way to analyze..well…anything…than with a database and some SQL.

So we need to be able to access the log file as a table, and an external table is perfect for that, so let’s start there.

Here’s a sample SQL Loader log file (with a little perturbation to preserve anonymity).  It’s quite complex because multiple tables were loaded as part of a single SQL Loader run.


Table SCOTT.T1: 222455 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  1229074 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.
Table SCOTT.T2:
  202547 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  1248982 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.
Table SCOTT.T3:
  952092 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  499437 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.
Table SCOTT.T4:
  74373 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  1377156 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.
Table SCOTT.T5:
  62 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  1451467 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.
Space allocated for bind array:               11920000 bytes(10000 rows)
Read   buffer bytes:60485760
Total logical records skipped:          0
Total logical records read:       1451529
Total logical records rejected:         0
Total logical records discarded:        0
Run began on Mon Jan 02 16:47:04 2017
Run ended on Mon Jan 02 16:47:42 2017
Elapsed time was:     00:00:38.17
Elapsed time was:     00:00:38.17
CPU time was:         00:00:32.61

Now obviously it’s a trivial exercise to parse that file as an external table with one row per file line.


SQL> CREATE TABLE sqlldr_log (
  2    msg varchar2(200)
  3  )
  4  ORGANIZATION EXTERNAL (
  5    TYPE ORACLE_LOADER
  6    DEFAULT DIRECTORY temp
  7    ACCESS PARAMETERS (
  8      RECORDS DELIMITED BY NEWLINE
  9      FIELDS
 10      (
 11        msg      position(1:512)
 12      )
 13    )
 14    LOCATION ('sqlldr.log')
 15  )
 16  REJECT LIMIT UNLIMITED;

Table created.

SQL> select msg from sqlldr_log;

MSG
-----------------------------------------------------------------------------------------
Table SCOTT.T1: 222455 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  1229074 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.
Table SCOTT.T2:
  202547 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  1248982 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.
Table SCOTT.T3:
  952092 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  499437 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.
Table SCOTT.T4:
  74373 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  1377156 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.
Table SCOTT.T5:
  62 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  1451467 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.
Space allocated for bind array:               11920000 bytes(10000 rows)
Read   buffer bytes:60485760
Total logical records skipped:          0
Total logical records read:       1451529
Total logical records rejected:         0
Total logical records discarded:        0
Run began on Mon Jan 02 16:47:04 2017
Run ended on Mon Jan 02 16:47:42 2017
Elapsed time was:     00:00:38.17
Elapsed time was:     00:00:38.17
CPU time was:         00:00:32.61

35 rows selected.

SQL>

but “So what ?” I hear you say. Now I got a text file coming as a text output in a query. I don’t seem to be much better off.

But now that I have the full power of SQL, I can start to do some simple parsing of the rows to (for example), extract the row load information.

Firstly, I’ll extract just those rows that have the text “Table” or “Rows” in them, and use a CASE statement to parse each of the “type” of rows (success, failed, etc) into individual columns


SQL> select
  2     rownum r
  3    ,substr(msg,1,50)
  4    ,case when msg like 'Table%' then substr(msg,7,instr(msg,':')-7) end tname
  5    ,case when msg like '  %Rows%successfully%' then substr(msg,3,instr(msg,'Rows')-4) end loaded
  6    ,case when msg like '  %Rows%data errors%' then substr(msg,3,instr(msg,'Rows')-4) end errs_data
  7    ,case when msg like '  %Rows%all WHEN cla%' then substr(msg,3,instr(msg,'Rows')-4) end errs_when
  8    ,case when msg like '  %Rows%were null%' then substr(msg,3,instr(msg,'Rows')-4) end errs_null
  9  from sqlldr_log
 10  where msg like 'Table%' or msg like '  %Rows%';

         R SUBSTR(MSG,1,50)                                   TNAME                          LOADED       ERRS_DATA    ERRS_WHEN    ERRS_NULL
---------- -------------------------------------------------- ------------------------------ ------------ ------------ ------------ ------------
         1 Table SCOTT.T1: 222455 Rows successfully loaded.   SCOTT.T1
         2   0 Rows not loaded due to data errors.                                                        0
         3   1229074 Rows not loaded because all WHEN clauses                                                          1229074
         4   0 Rows not loaded because all fields were null.                                                                        0
         5 Table SCOTT.T2:                                    SCOTT.T2
         6   202547 Rows successfully loaded.                                                202547
         7   0 Rows not loaded due to data errors.                                                        0
         8   1248982 Rows not loaded because all WHEN clauses                                                          1248982
         9   0 Rows not loaded because all fields were null.                                                                        0
        10 Table SCOTT.T3:                                    SCOTT.T3
        11   952092 Rows successfully loaded.                                                952092
        12   0 Rows not loaded due to data errors.                                                        0
        13   499437 Rows not loaded because all WHEN clauses                                                           499437
        14   0 Rows not loaded because all fields were null.                                                                        0
        15 Table SCOTT.T4:                                    SCOTT.T4
        16   74373 Rows successfully loaded.                                                 74373
        17   0 Rows not loaded due to data errors.                                                        0
        18   1377156 Rows not loaded because all WHEN clauses                                                          1377156
        19   0 Rows not loaded because all fields were null.                                                                        0
        20 Table SCOTT.T5:                                    SCOTT.T5
        21   62 Rows successfully loaded.                                                    62
        22   0 Rows not loaded due to data errors.                                                        0
        23   1451467 Rows not loaded because all WHEN clauses                                                          1451467
        24   0 Rows not loaded because all fields were null.                                                                        0

24 rows selected.

Now with a simple analytic function, I can “fill in the blanks” with my table name so it appears in every row


SQL> with base_data as (
  2  select
  3     rownum r
  4    ,substr(msg,1,50)
  5    ,case when msg like 'Table%' then substr(msg,7,instr(msg,':')-7) end tname
  6    ,case when msg like '  %Rows%successfully%' then substr(msg,3,instr(msg,'Rows')-4) end loaded
  7    ,case when msg like '  %Rows%data errors%' then substr(msg,3,instr(msg,'Rows')-4) end errs_data
  8    ,case when msg like '  %Rows%all WHEN cla%' then substr(msg,3,instr(msg,'Rows')-4) end errs_when
  9    ,case when msg like '  %Rows%were null%' then substr(msg,3,instr(msg,'Rows')-4) end errs_null
 10  from sqlldr_log
 11  where msg like 'Table%' or msg like '  %Rows%'
 12  )
 13  select
 14    last_value(tname ignore nulls) over ( order by r ) as tname,
 15    loaded,
 16    errs_data,
 17    errs_when,
 18    errs_null
 19  from base_data;

TNAME                          LOADED       ERRS_DATA    ERRS_WHEN    ERRS_NULL
------------------------------ ------------ ------------ ------------ ------------
SCOTT.T1
SCOTT.T1                                    0
SCOTT.T1                                                 1229074
SCOTT.T1                                                              0
SCOTT.T2
SCOTT.T2                       202547
SCOTT.T2                                    0
SCOTT.T2                                                 1248982
SCOTT.T2                                                              0
SCOTT.T3
SCOTT.T3                       952092
SCOTT.T3                                    0
SCOTT.T3                                                 499437
SCOTT.T3                                                              0
SCOTT.T4
SCOTT.T4                       74373
SCOTT.T4                                    0
SCOTT.T4                                                 1377156
SCOTT.T4                                                              0
SCOTT.T5
SCOTT.T5                       62
SCOTT.T5                                    0
SCOTT.T5                                                 1451467
SCOTT.T5                                                              0

24 rows selected.

and once I’ve got that, then all I need to do put that result within a GROUP BY, and voila ! I now have a summary my SQL Loader execution in a nice usable format


SQL> with base_data as (
  2  select
  3     rownum r
  4    ,substr(msg,1,50)
  5    ,case when msg like 'Table%' then substr(msg,7,instr(msg,':')-7) end tname
  6    ,case when msg like '  %Rows%successfully%' then substr(msg,3,instr(msg,'Rows')-4) end loaded
  7    ,case when msg like '  %Rows%data errors%' then substr(msg,3,instr(msg,'Rows')-4) end errs_data
  8    ,case when msg like '  %Rows%all WHEN cla%' then substr(msg,3,instr(msg,'Rows')-4) end errs_when
  9    ,case when msg like '  %Rows%were null%' then substr(msg,3,instr(msg,'Rows')-4) end errs_null
 10  from sqlldr_log
 11  where msg like 'Table%' or msg like '  %Rows%'
 12  ), pad_table_name as
 13  (
 14  select
 15    last_value(tname ignore nulls) over ( order by r ) as tname,
 16    loaded,
 17    errs_data,
 18    errs_when,
 19    errs_null
 20  from base_data
 21  )
 22  select
 23    tname,
 24    max(loaded) loaded,
 25    max(errs_data) errs_data,
 26    max(errs_when) errs_when,
 27    max(errs_null) errs_null
 28  from   pad_table_name
 29  where  loaded is not null
 30    or errs_data  is not null
 31    or errs_when is not null
 32    or errs_null is not null
 33  group by tname
 34  order by 1;

TNAME                          LOADED       ERRS_DATA    ERRS_WHEN    ERRS_NULL
------------------------------ ------------ ------------ ------------ ------------
SCOTT.T1                                    0            1229074      0
SCOTT.T2                       202547       0            1248982      0
SCOTT.T3                       952092       0            499437       0
SCOTT.T4                       74373        0            1377156      0
SCOTT.T5                       62           0            1451467      0

5 rows selected.

 

Footnote: If the LAST_VALUE analytic function left you bamboozled, check out my video series on them here

Logwriter I/O

If you are on any version of the database past 10.2.0.4, then savvy DBA’s may have noticed the following message popping up occasionally in their trace files


Warning: log write time 540ms, size 444KB

In itself, that is quite a nice little addition – an informational message letting you know that perhaps your log writer performance is worth closer investigation.  MOS Note 601316.1 talks a little more about this message.

So let’s say you have seen this warning, and you are interested in picking up more information.  Well… you could start scanning trace files from time to time, and parsing out the content etc, or do some analysis perhaps using Active Session History, but given that these warnings are (by default) triggered at above 500ms, there’s a chance you might miss them via ASH.

In 12c, this has become a lot simpler – because it looks like the same data is now exposed via a view, v$lgwrio_outlier


sql> select function_name,io_size,file_name,io_latency
  2  from   V$LGWRIO_OUTLIER;

FUNCTION_NAME IO_SIZE FILE_NAME                        IO_LATENCY
------------- ------- ------------------------------- -----------
LGWR               16 /u03/oradata/MYDB/control01.ctl         745
LGWR               16 /u03/oradata/MYDB/control01.ctl         506
LGWR               12 /u02/oradata/MYDB/redo09.dbf            705
LGWR                1 /u02/oradata/MYDB/redo08.dbf            502
LGWR                5 /u02/oradata/MYDB/redo07.dbf            538
LGWR              211 /u02/oradata/MYDB/redo10.dbf           1783
LGWR             1024 /u02/oradata/MYDB/redo10.dbf            597
LGWR                0 /u02/oradata/MYDB/redo10.dbf            597
LGWR               10 /u02/oradata/MYDB/redo10.dbf            597
LGWR                2 /u02/oradata/MYDB/redo10.dbf            775
LGWR                0 /u02/oradata/MYDB/redo07.dbf            683
LGWR                0 /u02/oradata/MYDB/redo09.dbf            704
LGWR                1 /u02/oradata/MYDB/redo10.dbf            504
LGWR                0 /u02/oradata/MYDB/redo10.dbf            690
LGWR               16 /u03/oradata/MYDB/control01.ctl         504
LGWR               58 /u02/oradata/MYDB/redo08.dbf            705 
LGWR                1 /u02/oradata/MYDB/redo08.dbf            704
...

Obviously the trace files are also tagged with the timestamp of occurrence, whereas the view does not expose this infromation.

But let’s just say two things here – (a) where there is a will there is a way, and (b) like most V$ views, it is based on an X$ object, which might contain more information Smile

For other types of I/O in the database, you can also take a look at V$IO_OUTLIER

Extending Flashback Data Archive in 12c

Flashback Data Archive (previously called Total Recall) has been around for a long time now. For those unfamiliar with it, (and by the way, if you are on Enterprise Edition, you should be familiar with it, because its a free feature), here is a very quick primer.

Create a tablespace to hold your history, and create a flashback archive using that space allocation.



SQL> create tablespace SPACE_FOR_ARCHIVE
  2  datafile 'C:\ORACLE\DB11\ARCH_SPACE.DBF' 
  3  size 100M;

Tablespace created.



SQL> CREATE FLASHBACK ARCHIVE longterm
  2  TABLESPACE space_for_archive
  3  RETENTION 1 YEAR;

Flashback archive created.

Then add your tables to the archive. Some tables will be created automatically to support being able to record all of the changes to that table for as long as the archive duration specifies


SQL> ALTER TABLE EMP FLASHBACK ARCHIVE LONGTERM;

Table altered.

SQL> select table_name
  2  from   user_tables
  3  /

TABLE_NAME
---------------------------
SYS_FBA_HIST_71036
SYS_FBA_TCRV_71036
SYS_FBA_DDL_COLMAP_71036
EMP

And then voila! We can track changes to our table over time, well beyond the typical undo_retention setting for the database.


SQL> select empno, ename, job, sal, comm,
  2      nvl(VERSIONS_STARTTIME,LAST_MOD) TS
  3     ,nvl(VERSIONS_OPERATION,'I') op
  4  from EMP
  5  versions between timestamp
  6    timestamp '2014-02-11 20:12:00' and
  7    systimestamp
  8  order by empno;


     EMPNO ENAME      JOB              SAL       COMM TS           O
---------- ---------- --------- ---------- ---------- ------------ -
      7369 SMITH      CLERK            806            08.10.51 PM  I
      7369 SMITH      SALES           8060       1000 08.12.10 PM  U
      7499 ALLEN      SALESMAN        1606  300000000 08.10.51 PM  I
      7521 WARD       SALESMAN        1256  500000000 08.10.51 PM  I
      7566 JONES      MANAGER         2981            08.10.51 PM  I
      ...
      7900 JAMES      CLERK            956            08.10.51 PM  I
      7902 FORD       ANALYST         3006            08.10.51 PM  I
      7934 MILLER     CLERK           1306            08.10.51 PM  I
      7934 MILLER     CLERK           1306            08.12.10 PM  D

But sometimes we want to know more about the history. We want to know who did the change, what machine were they on, etc etc. Auditors tend to be a little snippy about those kind of things Smile

  • You: “I can confirm that the SALARY field was updated at 9:01am on January 25th”
  • Auditor: “And who did that change?”
  • You: “Er….um…..someone”

But it might not be practical to augment all of your tables with WHO, WHEN, WHERE etc columns to capture that information. That might involve a lot of application changes and testing.

If you’re on 12c, you might be in luck. Flashback Data Archive can capture additional information, assuming your application is taking advantage of the myriad of instrumentation facilities offered by the database.

We simply turn on context tracking, which can capture information on a per transaction basis


SQL> begin
  2    dbms_flashback_archive.set_context_level(
  3        level=> 'ALL');
  4  end;

PL/SQL procedure successfully completed.

Now when we perform changes to the table, we can look at the transaction ID (XID) for that change.


SQL> update EMP
  2  set    sal = sal*10
  3  where  empno = 7499;

1 row updated.

SQL> commit;


SQL> select XID from SYS_FBA_HIST_510592;

XID
----------------
09000B00C7080000

And using that XID, we can pick up the context information about that transaction


SQL> set serverout on
SQL> begin
  2    dbms_output.put_line(
  3      dbms_flashback_archive.get_sys_context (
  4         '09000B00C7080000', 'USERENV', 'SESSION_USER'));
  5
  6    dbms_output.put_line(
  7      dbms_flashback_archive.get_sys_context (
  8         '09000B00C7080000', 'USERENV', 'HOST'));
  9
 10    dbms_output.put_line(
 11      dbms_flashback_archive.get_sys_context (
 12         '09000B00C7080000', 'USERENV', 'MODULE'));
 13  end;
 14  /
SCOTT
WORKGROUP\XPS
SQL*Plus

How much more ? Well, quite a lot actually !


SQL> desc SYS.SYS_FBA_CONTEXT_AUD

 Name                      Null?    Type
 ------------------------- -------- ------------------
 XID                                RAW(8)
 ACTION                             VARCHAR2(256)
 AUTHENTICATED_IDENTITY             VARCHAR2(256)
 CLIENT_IDENTIFIER                  VARCHAR2(256)
 CLIENT_INFO                        VARCHAR2(256)
 CURRENT_EDITION_NAME               VARCHAR2(256)
 CURRENT_SCHEMA                     VARCHAR2(256)
 CURRENT_USER                       VARCHAR2(256)
 DATABASE_ROLE                      VARCHAR2(256)
 DB_NAME                            VARCHAR2(256)
 GLOBAL_UID                         VARCHAR2(256)
 HOST                               VARCHAR2(256)
 IDENTIFICATION_TYPE                VARCHAR2(256)
 INSTANCE_NAME                      VARCHAR2(256)
 IP_ADDRESS                         VARCHAR2(256)
 MODULE                             VARCHAR2(256)
 OS_USER                            VARCHAR2(256)
 SERVER_HOST                        VARCHAR2(256)
 SERVICE_NAME                       VARCHAR2(256)
 SESSION_EDITION_NAME               VARCHAR2(256)
 SESSION_USER                       VARCHAR2(256)
 SESSION_USERID                     VARCHAR2(256)
 SESSIONID                          VARCHAR2(256)
 TERMINAL                           VARCHAR2(256)
 SPARE                              VARCHAR2(256)

And since we can use our flashback query syntax to pick up all of the transaction ID’s


SQL> select empno, ename, job, sal, comm,
  2      VERSIONS_XID
  3  from EMP
  4  versions between timestamp
  5    timestamp '2014-02-11 20:12:00' and
  6    systimestamp
  7  order by empno;

     EMPNO ENAME      JOB              SAL       COMM VERSIONS_XID
---------- ---------- --------- ---------- ---------- ----------------
      7369 SMITH      CLERK            806
      7369 SMITH      SALES           8060       1000 09001C00E04A0000
      7499 ALLEN      CLERK          16060  300000000 0A000A0024080000
      7499 ALLEN      SALESMAN       16060  300000000 09000B00C7080000
      7499 ALLEN      SALESMAN        1606  300000000
      7521 WARD       SALESMAN        1256  500000000

we can also take advantage of that new dictionary view SYS.SYS_FBA_CONTEXT_AUD to get all the context information for each transaction.


SQL> select ...
  2  from
  3  (  select
  4         empno, ename, job,
  5         sal, comm, VERSIONS_XID
  6     from EMP
  7     versions between timestamp
  8        timestamp '2014-02-11 20:12:00' and
  9        systimestamp
 10  ) e, SYS.SYS_FBA_CONTEXT_AUD  a
 11  where e.VERSIONS_XID = a.XID;

Flashback Data Archive is pretty nifty in 12c.

https://docs.oracle.com/database/121/ADFNS/adfns_flashback.htm#ADFNS01011