sql dev 4 ubuntu core dump - fix

ulimit -c unlimited
unset -v GNOME_DESKTOP_SESSION_ID
./sqlveveloper.sh

https://forums.oracle.com/thread/2559937

sys.odciNumberList

declare
     l_data sys.odciNumberList :=
            sys.odciNumberList
            ( 35, 34, 33, 34, 35,
            36, 37, 36, 35, 34, 35,
                              36, 37 );
     l_cnt  number := l_data.count;
   begin
     for i in 1 .. l_cnt
     loop
         insert into stocks
         ( symbol, tstamp, price )
         values
         ('XYZ', sysdate-l_cnt+i,
                           l_data(i) );
     end loop;
    commit;
   end;
 /

Misc

ref_cursors,

global temp table

RELY constraints, even though they are not used for data validation, can:

• Enable more sophisticated query rewrites for materialized views. See Chapter 22, "Query Rewrite" for further details.
• Enable other data warehousing tools to retrieve information regarding constraints directly from the Oracle data dictionary.

Creating a RELY constraint is inexpensive and does not impose any overhead during DML or load. Because the constraint is not being validated, no data processing is necessary to create it.

Using fake values for NULL is a BAD idea

Quote from Tom Kyte


But what does this have to do with NULL values? Nothing really—it has to do with what can happen when developers do not use a NULL value when they should have. Many times developers fear using NULLs: they do not understand them, and they do not believe they can be indexed, so they avoid them. They will use a “fake” value—such as 01-JAN-9999—to represent a missing date value. This is a bad idea for many reasons; the first I’ll show you is how it can throw off cardinality estimates.

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

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.

How to change properties/attributes of partition table?

ALTER TABLE scott.part_table MODIFY DEFAULT ATTRIBUTES TABLESPACE NEW_TABLESPACE;

ALTER INDEX scott.local_index MODIFY DEFAULT ATTRIBUTES TABLESPACE NEW_TABLESPACE_IDX;