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

How to print the predefined error messages in the error number in pl/sql

dbms_output.put_line(sqlerrm(sqlcode));

How to set the current schema in a session

alter session set current_schema=SCOTT;

The CURRENT_SCHEMA parameter changes the current schema of the session to the specified schema.
Subsequent unqualified references to schema objects during the session will resolve to objects in the specified schema.
The setting persists for the duration of the session or until you issue another ALTER SESSION SET CURRENT_SCHEMA statement.

This setting offers a convenient way to perform operations on objects in a schema other than that of the current user
without having to qualify the objects with the schema name.

This setting changes the current schema, but it does not change the session user or the current user,
nor does it give the session user any additional system or object privileges for the session.

Source: Oracle Documentation

How to exclude certain file systems when search for files belonging to tablespaces

Statement can be useful for example when reorganizing databases etc.


SELECT file_name FROM dba_data_files
WHERE file_name NOT LIKE '/data/oracle/u01%'
INTERSECT
SELECT file_name FROM dba_data_files
WHERE file_name NOT LIKE '/data/oracle/u02%';