Tip#5 DDL Auditing

As a DBA , I sometimes get complains that this object is missing or changed on our development or UAT system. So in order to find out WHO is the culprit I need to setup the DDL auditing. This is how I generally do this,

STEP 1 :
Please note that you need to give the user (which you are going to use) the following grant,

GRANT ADMINISTER DATABASE TRIGGER TO username;

STEP 2 :
Create Table to log the changes, something like following

CREATE TABLE DDL_AUDIT
(
AUD_ID NUMBER,
USERNAME VARCHAR2(50 BYTE),
HOSTNAME VARCHAR2(50 BYTE),
DDL_DATE DATE,
OBJ_TYPE VARCHAR2(50 BYTE),
OBJ_OWNER VARCHAR2(30 BYTE),
OBJ_NAME VARCHAR2(30 BYTE),
DDL_EVENT VARCHAR2(50 BYTE)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING
/

STEP 3 :
Create sequence for the PK of the table,

CREATE SEQUENCE DDL_AUDIT_SEQ
START WITH 1
MAXVALUE 99999999999
MINVALUE 0
NOCYCLE
NOCACHE
NOORDER
/

STEP 4 :
Create a DDL auditing database trigger,

CREATE OR REPLACE TRIGGER ddl_audit_trigger
AFTER DDL ON DATABASE
BEGIN
INSERT INTO ddl_audit
(aud_id, username,
hostname, ddl_date, obj_type, obj_owner,
obj_name, ddl_event
)
VALUES (ddl_audit_seq.NEXTVAL, SYS_CONTEXT ('USERENV', 'OS_USER'),
REPLACE (SYS_CONTEXT ('USERENV', 'HOST'), 'your_domain\', ''), SYSDATE,
ora_dict_obj_type, ora_dict_obj_owner, ora_dict_obj_name, ora_sysevent
);
EXCEPTION
WHEN OTHERS
THEN
-- log in the error table or ignore it
NULL; -- ignore it
END;
/

Tip#4 Dump, Lock & Nth Max/Min

DUMP Function :
Sometimes if you need to handle special characters or like in my case various European characters then it is quite useful to know the Hexadecimal or decimal value of the character. You can use the DUMP sql function for the same as shown below,

dump( expression, [return_format], [start_position], [length] )

=> expression is the expression to analyze.

=> return_format is optional. It determines the format of the return value. This parameter can be any of the following values:

Value Explanation
----- -------------
8....... octal notation
10..... decimal notation
16 ..... hexadecimal notation
17 ..... single characters
1008... octal notation with the character set name
1010...decimal notation with the character set name
1016... hexadecimal notation with the character set name
1017... single characters with the character set name

=> start_position and length are optional parameters. They determines which portion of the internal representation to display. If these parameters are omitted, the dump function will display the entire internal representation in decimal notation.

e.g.
select dump('öäüß',1016) hex_value from dual;

Hex_Value
-------------
Typ=96 Len=8 CharacterSet=UTF8: c3,b6,c3,a4,c3,bc,c3,9f


Lock any table explicitly :

LOCK table MV_REPORT_E2E_LOGIC IN EXCLUSIVE MODE NOWAIT;

commit or rollback to release the lock.

Find Nth Maximum value of a column in a table :

SELECT *
FROM MY_TAB t1
WHERE &N = (SELECT count(DISTINCT(t2.col1))
FROM MY_TAB t2 WHERE t1.col1<=t2.col1)

e.g. N=1 will return first max or N=2 will return second max.

Find Nth Minimum value of a column in a table :

SELECT *
FROM MY_TAB t1
WHERE &N = (SELECT count(DISTINCT(t2.col1))
FROM MY_TAB t2 WHERE t1.col1 >=t2.col1)

e.g. N=1 will return first min or N=2 will return second min.

Tip#3 Autotrace and MV Capabilities

MV CAPABILITIES :

Using MV Capabilities table explains what is possible with a materialized view or potential materialized view e.g. sometimes I used it to find out why MV cannot be fast refreshed or on commit refreshed.

-- create MV_CAPABILITIES_TABLE
@$ORACLE_HOME/rdbms/admin/utlxmv.sql

-- invoke EXPLAIN_MVIEW with the materialized view to explain
EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW ('MV_NAME');

-- use the SEQ column in an ORDER BY clause so the rows will display in a logical order.
-- If a capability is not possible, N will appear in the P column and an explanation in the MSGTXT column.
-- If a capability is not possible for more than one reason, a row is displayed for each reason.

SELECT capability_name, possible, SUBSTR (related_text, 1, 8) AS rel_text, SUBSTR (msgtxt, 1, 60) AS msgtxt
FROM mv_capabilities_table
ORDER BY seq;

Some more detailed example can be found here

Autotrace :

If autotrace is enabled, any session can run the autotrace by SET AUTOTRACE ON at SQL*PLUS. To enabled it,

Step 1 : Run plustrce.sql as SYS

@$ORACLE_HOME/sqlplus/admin/plustrce.sql

Step 2 : Grant PLUSTRACE to user

Step 3 : PLAN_TABLE must exist in the USER’s Schema ,if user want to do autotrace with explain plan.

For creation of plan_table ,

@$ORACLE_HOME/rdbms/admin/utlxplan.sql

The AUTOTRACE options are ON, OFF, ON EXPLAIN, ON STATISTICS and ON TRACEONLY. Following is the summary ,

SET AUTOTRACE OFF - No AUTOTRACE report is generated. This is the default.
SET AUTOTRACE ON - The AUTOTRACE report includes the data, the optimizer execution path
and the SQL statement execution statistics.
SET AUTOTRACE ON EXPLAIN - The AUTOTRACE report shows the data and the optimizer execution path.
SET AUTOTRACE ON STATISTICS - The AUTOTRACE report shows the data and the SQL statement execution statistics.
SET AUTOTRACE TRACEONLY - Like SET AUTOTRACE ON, but suppresses the printing of the user's query data.

Tip#2 Setup statspack automated purging

Setup for Statspack

I generally setup a cron job to collect the stats and also make sure that I dont keep ages old stats so I cleanup/purge it after certain days e.g. in following setup I purge all stats which are older than 90 days. This is how I would set up,

STEP 1 : Create PERFSTAT tablespace

Something like this ...

CREATE TABLESPACE PERFSTAT DATAFILE
'/oradata_path/perfstat01.dbf' SIZE 2048M AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;

STEP 2 : Create perfstat user and related objects

cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
connect / as sysdba
@spcreate

STEP 3 : Customized the sppurge.sql

copy $ORACLE_HOME/rdbms/admin/sppurge.sql in your scripts directory (I also rename it to sppurge_customised.sql ) and Change it to include

column min_snap_id new_val LoSnapId
column max_snap_id new_val HiSnapId
select min(s.snap_id) min_snap_id, max(s.snap_id) max_snap_id
from stats$snapshot s
, stats$database_instance di
where s.dbid = :dbid
and di.dbid = :dbid
and s.instance_number = :inst_num
and di.instance_number = :inst_num
and di.startup_time = s.startup_time
and s.snap_time < sysdate - 90; -- purge anything older than 90 days


BEFORE the following code in the script

--
-- Post warning

prompt
prompt
prompt Warning
prompt ~~~~~~~
prompt sppurge.sql deletes all snapshots ranging between the lower and
prompt upper bound Snapshot Id's specified, for the database instance
prompt you are connected to.


STEP 4 : Automate the collection and the purging

Set up a cron job to execute the following script every hour. Please note that the "sppurge_customised.sql" is executed only once in a day i.e. at midnight.

#!/bin/bash
if pgrep -f ora_
then
. /home/oracle/.bash_profile
SCRIPTPATH=/app/oracle/admin/scripts
LOGFILE=$SCRIPTPATH/LOGS
ORACLE_SID=mysid
export ORACLE_SID

cd $SCRIPTPATH
echo Starting Statsupdate at `date` >> $LOGFILE/statspack.log

# only purge older stats once a day at midnight
cuurent_hour=`date +%H`
if [ $cuurent_hour == "00" ]; then
sqlplus /nolog >> $LOGFILE/statspack.log << EOF
connect perfstat/perfstat
@sppurge_customised.sql
EOF
fi

sqlplus /nolog >> $LOGFILE/statspack.log << EOF
connect perfstat/perfstat
exec statspack.snap;
EOF

echo Finished at `date` >> $LOGFILE/statspack.log
fi

Tip#1 run of the mill

How to print a REF CURSOR returned by an Stored Procedure?

var out_cur refcursor;
exec your_sp(param1,param2, :out_cur);
print out_cur;

How to compile invalid sys-objects?

cd $ORACLE_HOME/rdbms/admin
sqlplus sys/xxx as sysdba
start utlrp.sql

How to display time taken to refresh the materialized view ?

SELECT owner, mview_name, last_refresh_date refresh_start_time,
last_refresh_date + (fullrefreshtim / (60 * 60 * 24)) refresh_end_time,
refresh_method, fullrefreshtim refresh_time_seconds
FROM dba_mview_analysis
ORDER BY 3, 4

How to find tables which are having a same/similar Column name?

SELECT owner, table_name object_name, column_name, data_type, data_length
FROM all_tab_cols
WHERE column_name LIKE '%PART_NUMBER%'

How to find object dependencies?

SELECT NAME, TYPE, referenced_name, referenced_type
FROM SYS.all_dependencies
WHERE owner = 'owner_name'
AND referenced_name = 'any object name'

How to find all invalid objects for an user?

SELECT object_name, object_type, created, status, last_ddl_time
FROM dba_objects
WHERE status = 'INVALID'
AND owner = 'user_name'

How to find rows with non- numeric character in a column ?

Sometimes I need to check if the ID column of the table is only numbers and no characters are allowed (unfortunately can not change the data type to NUMBER ) So following query gives me all IDs with non-numeric data in it,

SELECT adm_id
FROM address_master
WHERE NOT (LENGTH (adm_id) - LENGTH (TRANSLATE (adm_id, CHR (1) || TRANSLATE (adm_id, CHR (1) || '1234567890', CHR (1)), CHR (1))) = 0 )

How to insert special character in a table?

You need to know the ascii code for the char and then you can insert it using CHR() e.g. you can insert ‘&’ whose ASCII code is 38 as below,

insert into test1 (c1) values ('xx' || Chr(38) || 'xx')

How to remove duplicate rows from a table?

If the unique/primary keys can be identified from the table, it is easier to remove the records from the table using the following query:

DELETE FROM tablename
WHERE rowid not in (SELECT MIN(rowid)
FROM tablename
GROUP BY column1, column2, column3...);

Here column1, column2, column3 constitute the identifying key for each record.

If the keys cannot be identified for the table, you may create a temporary table using the query

CREATE TABLE temptablename
AS SELECT DISTINCT *
FROM tablename;

Then drop the original table and rename the temp table to original tablename