How to create a consistent dump set using expdp

Use the FLASHBACK_TIME or FLASHBACK_SCN directives.

The simplest approach:

FLASHBACK_TIME="to_timestamp(to_char(sysdate,'ddmmyyyy hh24:mi:ss'),'ddmmyyyy hh24:mi:ss')"

How to audit CREATE SESSION

Audit all session connecting to the database:

audit create session [ whenever successful | not successful ];

To audit sessions created by a specific user:

audit create session by SCOTT [ whenever successful | not successful ];

If you omit the condition "whenever successful/not successful", Oracle will audit all connections, regardless of their success or failure.


To disable auditing:

noaudit create session;
noaudit create session by scott;

To verify that the auditing options has indeed been set:

SELECT * 
 FROM DBA_PRIV_AUDIT_OPTS 
 WHERE USER_NAME = 'SCOTT';

Result:

USER_NAME	PROXY_NAME	PRIVILEGE	SUCCESS	FAILURE
SCOTT		CREATE SESSION	BY ACCESS	BY ACCESS

The view DBA_PRIV_AUDIT_OPTS gives you "current system privileges being audited across the system and by user".
Note that a NULL value in the user_name column of DBA_PRIV_AUDIT_OPTS means system-wide auditing.

To find your audit info, use the query outlined here

Can you revoke all object privileges from a role in one go?

Answer: No.

The closest shortcut you get is to revoke all privileges per object from a role.

Example:

REVOKE ALL ON SCOTT.T1 FROM TEST_ROLE;

How to add a sleep statement to your pl/sql programs

Humans will often need some time to react to the output from your scripts. To briefly "pause" the output from a pl/sql script, add the following directive:

dbms_lock.sleep(no_of_seconds);

How to enable tracing for a session

To enable trace for a session:

execute dbms_monitor.session_trace_enable(session_id => 127,
                                  serial_num => 29,
                                  waits => TRUE,
                                  binds => FALSE);

Disable as follows:

execute dbms_monitor.session_trace_disable(127,29);


Alternatively, use the dbms_system package:

execute dbms_system.set_sql_trace_in_session(17,45171,true);


When finished, disable tracing:


dbms_system.stop_trace_in_session(17,45171);


To enable the writing of a trace file for your current session:

execute dbms_session.set_sql_trace(true);

Note that the user needs the ALTER SESSION privilege to perform tracing:

GRANT ALTER SESSION TO scott;

Note 293661.1 published on MOS is another great source to use the dbms_monitor package efficiently.

Syntax for using the dbms_stats.gather_table_stats and gather_schema_stats procedures

To use the default options:

BEGIN
  SYS.DBMS_STATS.GATHER_TABLE_STATS (
     OwnName        => 'SCOTT'
    ,TabName        => 'EMP'
    ,Estimate_Percent  => DBMS_STATS.AUTO_SAMPLE_SIZE
    ,method_opt        => 'FOR ALL COLUMNS SIZE AUTO'
    ,Degree            => NULL
    ,Cascade           => DBMS_STATS.AUTO_CASCADE
    ,granularity       => 'AUTO'
    ,No_Invalidate     => DBMS_STATS.AUTO_INVALIDATE);
END;
/

BEGIN
    DBMS_STATS.GATHER_SCHEMA_STATS (
    ownname          => 'SCOTT',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
    degree           => DBMS_STATS.DEFAULT_DEGREE,
    granularity      => 'ALL',
    cascade          => DBMS_STATS.AUTO_CASCADE,
    options          => 'GATHER', 
    no_invalidate => DBMS_STATS.AUTO_INVALIDATE);
END;
/

Estimate_percent means percentage of rows to estamite. NULL means compute statistics, and gives the most accurate results, but may be completely innappropriate with large tables. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to let Oracle figure out the appropriate number autmatically. In my opinion, this is sufficient for most situations.

Granularity means Granularity of statistics to collect (only relevant if the table is partitioned).
'ALL' - gathers all (subpartition, partition, and global) statistics
'AUTO' - determines the granularity based on the partitioning type. (DEFAULT)
'DEFAULT' - This option is obsolete. You should use the 'GLOBAL AND PARTITION' for this functionality.
'GLOBAL' - gathers global statistics
'GLOBAL AND PARTITION' - gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object
'PARTITION' - gathers partition-level statistics
'SUBPARTITION' - gathers subpartition-level statistics

cascade means whether or not statistics for the indexes on this table should be gathered.
Using this option is equivalent to running the GATHER_INDEX_STATS Procedure on each of the table's indexes.
Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle determine whether index statistics to be collected or not (DEFAULT).

degree is the degree of parallelism. The default is NULL, and instructs Oracle to use the DOP (Degree of Parallelism) specified by the DEGREE-clause when the table or index was created. By using the constant DBMS_STATS.DEFAULT_DEGREE you tell Oracle to calculate the DOP for you, using the formula (PARALLEL_THREADS_PER_CPU * CPU_COUNT). Oracle may still choose to use serial execution, if the size of the Object doesn't warrant a parallel execution. See this post for more information about the DEGREE setting.


method_opt means "collect histograms"
Can be:
FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size clause] column|attribute [size_clause] [,column|attribute [size_clause]...]

size_clause: SIZE {integer | REPEAT | AUTO | SKEWONLY}

integer : Number of histogram buckets. Must be in the range [1,254].
'REPEAT' - Collects histograms only on the columns that already have histograms.
'AUTO' - Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
'SKEWONLY' - Oracle determines the columns to collect histograms based on the data distribution of the columns.
The default is FOR ALL COLUMNS SIZE AUTO. Note that you can change what's default using the SET_PARAM Procedure.

option GATHER AUTO is the default. GATHER forces a complete re-gathering of all tables

What is a histogram?
Column statistics may be stored as histograms. These histograms provide accurate estimates of the distribution of column data. Histograms provide improved selectivity estimates in the presence of data skew, resulting in optimal execution plans with nonuniform data distributions.

About privileges:
To call this procedure you must either a) be owner of the table or b) have been granted the ANALYZE ANY privilege.

See also this post about how to gather statistics on a specific partition only

Source: Oracle documentation

How to create a DDL trigger in a schema

CREATE TABLE AUDIT_DDL (
 d date,
 OSUSER varchar2(255),
 CURRENT_USER varchar2(255),
 HOST varchar2(255),
 TERMINAL varchar2(255),
 owner varchar2(30),
 type varchar2(30),
 name varchar2(30),
 sysevent varchar2(30));

connect uid/pwd

CREATE OR REPLACE TRIGGER audit_ddl_trg after ddl on schema
BEGIN
IF(ora_sysevent='TRUNCATE') THEN
 null; -- If we do not care about truncate
ELSE
 INSERT INTO audit_ddl(d, osuser,current_user,host,terminal,owner,type,name,sysevent)
 values(
  sysdate,
  sys_context('USERENV','OS_USER') ,
  sys_context('USERENV','CURRENT_USER') ,
  sys_context('USERENV','HOST') , 
  sys_context('USERENV','TERMINAL') ,
  ora_dict_obj_owner,
  ora_dict_obj_type,
  ora_dict_obj_name,
  ora_sysevent
  );
 END IF;
END;
/

Source: Don Burleson http://www.dba-oracle.com/t_ddl_triggers.htm