How to compare the content of memory parameter and spfile parameters

### Compare static (pfile) and dynamic (spfile) parameter setting:

set pages 100
set lines 400
col init_value format a36
col spfile_value format a36
col name format a38
SELECT P.NAME, P.VALUE INIT_VALUE, S.VALUE SPFILE_VALUE 
FROM   V$PARAMETER P 
    JOIN V$SPPARAMETER S ON P.NAME=S.NAME 
    WHERE P.ISBASIC='TRUE' 
    ORDER BY NAME;

How to clean away control character from a text file

I found this little example on the internet and saved it as a shorthand - it has come in handy many times. Unfortunately I have forgotten the name of the original author.

When files are sent from windows to unix, you will sometimes see that the when you open the files in a unix text editor has ^M's in them.

For example

------------------------------------------------------------------
import java.util.Hashtable; ^M
import java.util.Properties; ^Mimport java.io.IOException;
import org.xml.sax.AttributeList; ^M
import org.xml.sax.HandlerBase; ^Mimport org.xml.sax.SAXException;

/**^M
  * XMLHandler: This class parses the elements contained^M
  * within a XML message and builds a Hashtable^M

------------------------------------------------------------------

Notice that some programs are not consistent in the way they insert the line breaks so you end up with some lines that have both a carrage return and a ^M and some
lines that have a ^M and no carrage return (and so blend into one). There are two steps to clean this up.

1. replace all extraneous ^M:

:%s/^M$//g

BE SURE YOU MAKE the ^M USING "CTRL-V CTRL-M" NOT BY TYPING "CARROT M"! This expression will replace all the ^M's that have carriage returns
after them with nothing. (The dollar ties the search to the end of a line)

2. replace all ^M's that need to have carriage returns:

:%s/^M/ /g

Once again: BE SURE YOU MAKE the ^M USING "CTRL-V CTRL-M" NOT BY TYPING "CARROT M"! This expression will replace all the ^M's that didn't have
carriage returns after them with a carriage return.

Voila! Clean file. Map this to something if you do it frequently.

How to write a procedure that lets developers set the time for the database using the FIXED_DATE procedure

The following procedure may be used to let designated database developers change the system time according to their testing needs.

CREATE OR REPLACE PACKAGE admin_pack AUTHID DEFINER
AS
 PROCEDURE set_fixed_date(vv_fixed_date VARCHAR2);
 PROCEDURE unset_fixed_date;
END admin_pack;
/

CREATE OR REPLACE PACKAGE body admin_pack
AS
PROCEDURE set_fixed_date (vv_fixed_date VARCHAR2) IS
 BEGIN
  IF UPPER(vv_fixed_date) = 'NONE' THEN
     EXECUTE IMMEDIATE 'alter system set fixed_date=none';
  ELSE
     EXECUTE IMMEDIATE 'alter system set fixed_date=''' || vv_fixed_date || '''';
  END IF;
END;
PROCEDURE unset_fixed_date IS
 BEGIN
  EXECUTE IMMEDIATE 'alter system set fixed_date=none';
 END;
END admin_pack;
/


CREATE PUBLIC SYNONYM ADMIN_PACK FOR SYS.ADMIN_PACK;
GRANT EXECUTE ON ADMIN_PACK TO SCOTT;

Note the AUTHID clause in the package declaration:

A unit whose AUTHID value is CURRENT_USER is called an "invoker's rights" unit, or IR unit. A unit whose AUTHID value is DEFINER is called a "definer's rights" unit, or DR unit.

The AUTHID property of a unit determines whether the unit is IR or DR, and it affects both name resolution and privilege checking at run time. It will check:

• If the context for name resolution is CURRENT_SCHEMA.
• If the privileges checked are those of the CURRENT_USER and the enabled roles.

At compilation time, the AUTHID property has no effect—both DR and IR units are treated like DR units. At run time, however, the AUTHID property determines whether a unit is IR or DR, and the unit is treated accordingly.

Author: Tomasz Gozdz
Documentation for the AUTHID DEFINER SETTING
Documentation for the FIXED_DATE procedure

What is the difference between BY ACCESS and BY SESSION in Oracle Auditing?

The difference between BY ACCESS and BY SESSION lies in how the DBA_AUDIT_TRAIL data dictionary view records the actions that they capture:

BY ACCESS: Inserts one record in the audit trail for each audited statement.

BY SESSION: Inserts only one audit record in the audit trail, for each user and schema object, during a session that includes an audited action.

Example:

First, enable auditing on the table TABLE1:

AUDIT SELECT, UPDATE,DELETE,INSERT ON HR.TABLE1 BY SESSION WHENEVER SUCCESSFUL;

Secondly, assuming there is activity against TABLE1, check the audit trail:

SELECT  timestamp, os_username, obj_name,userhost, username,action,action_name, ses_actions
FROM    dba_audit_trail
WHERE   obj_name IN ('TABLE1')
AND     username = 'HR'
ORDER BY timestamp DESC;

The query in my case returned the following:

TIMESTAMP	OS_USERNAME	OBJ_NAME	USERHOST	USERNAME	ACTION	ACTION_NAME	SES_ACTIONS
04.07.2011 10:35:20	joe	TABLE1	laptop-joe	HR	103	SESSION REC	---------S------
08.02.2011 18:11:10	joe	TABLE1	appserver1	HR	2	INSERT
08.02.2011 18:11:10	joe	TABLE1	appserver1	HR	6	UPDATE
08.02.2011 18:11:10	jack	TABLE1	appserver1	HR	7	DELETE
08.02.2011 14:50:41	tina	TABLE1	workstation-tina	HR	103	SESSION REC	---S--S---S-----

For statements which are audited BY SESSION, the column SES_ACTIONS will indicate a pattern to view which operation we are talking about.
The 16 characters string in the SES_ACTION column represents different actions, one for each action type in the order (note that positions 14, 15, and 16 are reserved for future use):

* ALTER, AUDIT, COMMENT, DELETE, GRANT, INDEX, INSERT, LOCK, RENAME, SELECT, UPDATE, REFERENCES, and EXECUTE.

The characters are:

* - - None
* S - Success
* F - Failure
* B - Both

Finally, interpret the audit trail:
The first row in the table indicates that a successfull SELECT (the S is found on position 10) was executed on TABLE1, 04.07.2011 10:35:20.
The last row indicates a successful DELETE, INSERT and UPDATE (the S is found in position 4, 7 and 11) in TABLE1 during the session that was audited 08.02.2011 14:50:41.
The three rows where ACTION_NAME is INSERT, UPDATE and DELETE are self-explanatory.

At the tail of the AUDIT clause comes the directive WHENEVER [NOT] SUCCESSFUL which means that oracle should audit only SQL statements and operations that succeed, fail or result in errors. If you omit this clause, then Oracle Database performs the audit regardless of success or failure.

Why won't my DML statement use partition pruning?

I had a case today where a developer issued a DELETE statement against a 600 mill row table.
The table is RANGE PARTITIONED, with partitions per month. The table is created with parallel degree 8.

The DML statement looked correct, and was using the partition key in the predicate:

delete 
from  mytable 
where val_date = to_date('31.10.2013','DD.MM.YYYY');

The execution plan looked as follows:

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT      |            |   611K|    16M|  5597   (2)| 00:01:08 |       |       |        |      |            |
|   1 |  DELETE               | MYTABLE    |       |       |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR      |            |       |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)| :TQ10000   |   611K|    16M|  5597   (2)| 00:01:08 |       |       |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR |            |   611K|    16M|  5597   (2)| 00:01:08 |   118 |   118 |  Q1,00 | PCWC |            |
|*  5 |      TABLE ACCESS FULL| MYTABLE    |   611K|    16M|  5597   (2)| 00:01:08 |   118 |   118 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("VAL_DATE"=TO_DATE(' 2013-10-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

It looks like oracle is ignoring the predicate and performs a full table scan.
True or not?
When explaining without parallel slaves:

explain plan for 
delete /*+ no_parallel (a) */ 
from mytable a
where val_date = to_date('31.10.2013','DD.MM.YYYY');
select * from table(dbms_xplan.display);

Then the execution plan is revealed as follows:

------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT        |            |   611K|    16M| 40443   (2)| 00:08:06 |       |       |
|   1 |  DELETE                 | MYTABLE    |       |       |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE|            |   611K|    16M| 40443   (2)| 00:08:06 |   118 |   118 |
|*  3 |    TABLE ACCESS FULL    | MYTABLE    |   611K|    16M| 40443   (2)| 00:08:06 |   118 |   118 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("VAL_DATE"=TO_DATE(' 2013-10-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Note that when you use the NO_PARALLEL hint, you need to reference the table by an alias, in my case "a". The statement below

delete /*+ no_parallel */ 
from mytable...

will not, to my surprise, override the parallel settings, while

delete /*+ no_parallel (a)*/ 
from mytable a...

will.

In conclusion, simulating a non-parallel execution revealed that the query was indeed using partition pruning (as can be seen from line 2 above that reads "PARTITION RANGE SINGLE"). In the original explained plan, this is not pointed out by oracle, and can easily lead you think that oracle is not taking advantage of partitioning.

How to hide the input from the user when using the accept directive in sqlplus

accept sch prompt "Schema : "
accept pwd prompt "Password (not displayed) : " hide

Errors ORA-31693 and ORA-29913 thrown during import

Yesterday when importing some test data from production to test, I encountered the following error during import:

. . imported "< schema name >"."< table name > ":"< partition name >" 77.08 KB 0 rows
ORA-31693: Table data object "< schema name >"."< table name > ":"< partition name >" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
. . imported "< schema name >"."< table name > ":"< partition name >" 25.69 KB 0 rows

The preceeding export executed successfully, without any errors or warnings. I used multiple threads, and the result was 7 dump files.

I first tried reimporting the table, which in many similar cases actually work. This time, it didn't; the same error was thrown.

The solution was to remove the parallel directive from the import parameter file and reimport only the missing table:

USERID='/ AS SYSDBA'
DIRECTORY=DPUMP
DUMPFILE=EXDP_SCHEMA_EXPORT_201311181340_%U.DMP
LOGFILE=IMPDP_missing_tables.log
PARALLEL=1
TRANSFORM=OID:N
TABLE_EXISTS_ACTION=REPLACE
REMAP_SCHEMA=< schema_name >:
TABLES=< schema_name >.< table_name >

. . imported "< schema name >"."< table name > ":"< partition name >" 77.08 KB 0 rows
. . imported "< schema name >"."< table name > ":"< partition name >" 1.205 GB 6758593 rows
. . imported "< schema name >"."< table name > ":"< partition name >" 25.69 KB 0 rows