Loading files with the list of files in an external table


You could use a variant of this technique

create table df
  2  (
  3   fsname   varchar2(100),
  4   blocks   number,
  5   used     number,
  6   avail    number,
  7   capacity varchar2(10),
  8   mount    varchar2(100)
  9  )
 10  organization external
 11  (
 12    type oracle_loader
 13    default directory exec_dir
 14    access parameters
 15    (
 16      records delimited
 17      by newline
 18      preprocessor
 19      exec_dir:'run_df.sh'
 20      skip 1
 21      fields terminated by
 22      whitespace ldrtrim
 23    )
 24    location
 25    (
 26      exec_dir:'run_df.sh'
 27    )
 28  )
 29  /
Table created.

taken from

http://www.oracle.com/technetwork/issue-archive/2012/12-nov/o62asktom-1867739.html

Also look at the /*+ materialize */  hint for external tables.

Incremental statistics in 11g, sunopsis
https://blogs.oracle.com/datawarehousing/entry/managing_optimizer_statistics

exec dbms_stats.set_table_prefs('SH', 'SALES', 'INCREMENTAL', 'TRUE');

SQL> exec dbms_stats.gather_table_stats( Owname=>'SH', Tabname=>'SALES', Partname=>'23_MAY_2008', Granularity=>'AUTO');

Express a number as a percentage

TO_CHAR(bcl.tax_rate, '90.99L','NLS_CURRENCY=''%''' )

Output = 10.01% or 99.99% or 0.00%

Cacheing the result of function

http://www.oracle-developer.net/display.php?id=504

LISTAGG

http://www.oracle-developer.net/display.php?id=515


Pivots rows into comma separated list

Linux Command stack search

esc-p or !command or !?command

eg.
!ls
!?ls
esc-p followed by ls

DBMS_MVIEW.REFRESH - atomic_refresh

One of the thing that is not well documented about setting atomic_refresh => false
is it cause the refresh to truncate rather than delete from the Mview. The result can be a much quicker refresh.