you mean like this:


ops$tkyte@ORA9IR2> select ename, deptno, sal, sum(sal) over (order by ename)
  2    from (select ename, deptno, sal from t1
  3          union all
  4          select ename, deptno, sal from t2)
  5   order by ename
  6  /
 
ENAME          DEPTNO        SAL SUM(SAL)OVER(ORDERBYENAME)
---------- ---------- ---------- --------------------------
ADAMS              20       1100                       1100
CLARK              10       2450                       3550
FORD               20       3000                       6550
JONES              20       2975                       9525
KING               10       5000                      14525
MILLER             10       1300                      15825
SCOTT              20       3000                      18825
SMITH              20        800                      19625
 
8 rows selected.
 
<b>Analytics Rock
Analytics Roll
Analytics Rule</b>

 

1) there is no such concept as a user quota in temp.  the temp segment is owned by the system, by Oracle -- NOT by the user using it.  They only borrow it for a short period of time.

2) No, they need create table:

ops$tkyte@ORA10GR2> create user a identified by a;

User created.

ops$tkyte@ORA10GR2> grant create session to a;

Grant succeeded.

ops$tkyte@ORA10GR2> connect a/a
Connected.
a@ORA10GR2> create global temporary table gtt ( x int );
create global temporary table gtt ( x int )
*
ERROR at line 1:
ORA-01031: insufficient privileges



 

(I would hardly term 92K of redo "so many redo logs", but...)


the conventional path insert generates UNDO, UNDO is always protected by REDO.

Here is an excerpt from Expert Oracle: Database Architecture regarding this:

<quote>

Temporary Tables and Redo/Undo

Temporary tables are still considered a relatively new feature of Oracle, having been introduced only in Oracle8i version 8.1.5. As such, there is some confusion surrounding them, in particular in the area of logging. In Chapter 10, we will cover how and why you might use temporary tables. In this section, we will explore only the question “How do temporary tables work with respect to logging of changes?”

Temporary tables generate no redo for their blocks. Therefore, an operation on a temporary table is not “recoverable.” When you modify a block in a temporary table, no record of this change will be made in the redo log files. However, temporary tables do generate undo, and the undo is logged. Hence, temporary tables will generate some redo. At first glance, it doesn’t seem to make total sense: why would they need to generate undo? This is because you can roll back to a SAVEPOINT within a transaction. You might erase the last 50 INSERTs into a temporary table, leaving the first 50. Temporary tables can have constraints and everything else a normal table can have. They might fail a statement on the five-hundredth row of a 500-row INSERT, necessitating a rollback of that statement. Since temporary tables behave in general just like “normal” tables, temporary tables must generate undo. Since undo data must be logged, temporary tables will generate some redo log for the undo they generate.

This is not nearly as ominous as it seems. The primary SQL statements used against temporary tables are INSERTs and SELECTs. Fortunately, INSERTs generate very little undo (you need to restore the block to “nothing,” and it doesn’t take very much room to store “nothing”), and SELECTs generate no undo. Hence, if you use temporary tables for INSERTs and SELECTs exclusively, this section means nothing to you. It is only if you UPDATE or DELETE that you might be concerned about this.

I set up a small test to demonstrate the amount of redo generated while working with temporary tables, an indication therefore of the amount of undo generated for temporary tables, since only the undo is logged for them. To demonstrate this, I will take identically configured “permanent” and “temporary” tables, and then perform the same operations on each, measuring the amount of redo generated each time. The tables I used were simply as follows:

ops$tkyte@ORA10G> create table perm
  2  ( x char(2000) ,
  3    y char(2000) ,
  4    z char(2000)  )
  5  /
Table created.
 
ops$tkyte@ORA10G> create global temporary table temp
  2  ( x char(2000) ,
  3    y char(2000) ,
  4    z char(2000)  )
  5  on commit preserve rows
  6  /
Table created.

I set up a small stored procedure to allow me to perform arbitrary SQL and report the amount of redo generated by that SQL. I will use this routine to perform INSERTs, UPDATEs, and DELETEs against both the temporary and permanent tables:

ops$tkyte@ORA10G> create or replace procedure do_sql( p_sql in varchar2 )
  2  as
  3      l_start_redo    number;
  4      l_redo            number;
  5  begin
  6      select v$mystat.value
  7        into l_start_redo
  8        from v$mystat, v$statname
  9       where v$mystat.statistic# = v$statname.statistic#
 10         and v$statname.name = 'redo size';
 11
 12      execute immediate p_sql;
 13      commit;
 14
 15      select v$mystat.value-l_start_redo
 16        into l_redo
 17        from v$mystat, v$statname
 18       where v$mystat.statistic# = v$statname.statistic#
 19         and v$statname.name = 'redo size';
 20
 21      dbms_output.put_line
 22      ( to_char(l_redo,'9,999,999') ||' bytes of redo generated for "' ||
 23        substr( replace( p_sql, chr(10), ' '), 1, 25 ) || '"...' );
 24  end;
 25  /
Procedure created.

Then, I ran equivalent INSERTs, UPDATEs, and DELETEs against the PERM and TEMP tables:

ops$tkyte@ORA10G> set serveroutput on format wrapped
ops$tkyte@ORA10G> begin
  2      do_sql( 'insert into perm
  3               select 1,1,1
  4                 from all_objects
  5                where rownum <= 500' );
  6
  7      do_sql( 'insert into temp
  8               select 1,1,1
  9                 from all_objects
 10                where rownum <= 500' );
 11          dbms_output.new_line;
 12
 13      do_sql( 'update perm set x = 2' );
 14      do_sql( 'update temp set x = 2' );
 15          dbms_output.new_line;
 16
 17      do_sql( 'delete from perm' );
 18      do_sql( 'delete from temp' );
 19  end;
 20  /
 3,297,752 bytes of redo generated for "insert into perm         "...
    66,488 bytes of redo generated for "insert into temp         "...
 
 2,182,200 bytes of redo generated for "update perm set x = 2"...
 1,100,252 bytes of redo generated for "update temp set x = 2"...
 
 3,218,804 bytes of redo generated for "delete from perm"...
 3,212,084 bytes of redo generated for "delete from temp"...
 
PL/SQL procedure successfully completed.
As you can see,
    *    The INSERT into the “real” table generated a lot of redo. Almost no redo was generated for the temporary table. This makes sense—there is very little undo data generated for INSERTs and only undo data is logged for temporary tables.
    *    The UPDATE of the real table generated about twice the amount of redo as the temporary table. Again, this makes sense. About half of that UPDATE, the “before image,” had to be saved. The “after image” (redo) for the temporary table did not have to be saved.
    *    The DELETEs took about the same amount of redo space. This makes sense, as the undo for a DELETE is big, but the redo for the modified blocks is very small. Hence, a DELETE against a temporary table takes place very much in the same fashion as a DELETE against a permanent table.
Note    If you see the temporary table generating more redo than the permanent table with the INSERT statement, you are observing a product issue in the database that is fixed in at least Oracle 9.2.0.6 and 10.1.0.4 patch releases (the current shipping releases as of this writing).
Therefore, the following generalizations may be made regarding DML activity on temporary tables:
    *    An INSERT will generate little to no undo/redo activity.
    *    A DELETE will generate the same amount of redo as a normal table.
    *    An UPDATE of a temporary table will generate about half the redo of an UPDATE of a normal table.

There are notable exceptions to the last statement. For example, if I UPDATE a column that is entirely NULL with 2,000 bytes of data, there will be very little undo data generated. This UPDATE will behave like the INSERT. On the other hand, if I UPDATE a column with 2,000 bytes of data to be NULL, it will behave like the DELETE as far as redo generation is concerned. On average, you can expect an UPDATE against a temporary table to produce about 50 percent of the undo/redo you would experience with a real table.

In general, common sense prevails on the amount of redo created. If the operation you perform causes undo data to be created, then determine how easy or hard it will be to reverse (undo) the effect of your operation. If you INSERT 2,000 bytes, the reverse of this is easy. You simply go back to no bytes. If you DELETE 2,000 bytes, the reverse is INSERTing 2,000 bytes. In this case, the redo is substantial.

Armed with this knowledge, you will avoid deleting from temporary tables. You can use TRUNCATE, bearing in mind, of course, that TRUNCATE is DDL that will commit your transaction, and in Oracle9i and before invalidate your cursors, or just let the temporary tables empty themselves automatically after a COMMIT or when your session terminated. All of these methods generate no undo and, therefore, no redo. You will try to avoid updating a temporary table unless you really have to for some reason. You will use temporary tables mostly as something to be INSERTed into and SELECTed from. In this fashion, you’ll make optimum use of their unique ability to not generate redo.
</quote> 

I see I was ambigous - I was comparing global temporary tables to real tables there and it wasn't really clear.


ops$tkyte%ORA9IR2> create global temporary table gtt ( x varchar2(30) ) on commit delete rows;

Table created.

ops$tkyte%ORA9IR2> create table t ( x varchar2(30) );

Table created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> insert into gtt select null from all_objects;

30722 rows created.

ops$tkyte%ORA9IR2> insert into t select null from all_objects;

30722 rows created.

ops$tkyte%ORA9IR2> @mystat "redo size"
ops$tkyte%ORA9IR2> set echo off

NAME                        VALUE
---------------------- ----------
redo size                  442452

ops$tkyte%ORA9IR2> update gtt set x = rpad('x',30,'x');

30722 rows updated.

ops$tkyte%ORA9IR2> @mystat2
ops$tkyte%ORA9IR2> set echo off

NAME                        VALUE DIFF
---------------------- ---------- ------------------
redo size                 8981464        8,539,012

ops$tkyte%ORA9IR2> @mystat "redo size"
ops$tkyte%ORA9IR2> set echo off

NAME                        VALUE
---------------------- ----------
redo size                 8981464

ops$tkyte%ORA9IR2> update t set x = rpad('x',30,'x');

30722 rows updated.

ops$tkyte%ORA9IR2> @mystat2
ops$tkyte%ORA9IR2> set echo off

NAME                        VALUE DIFF
---------------------- ---------- ------------------
redo size                24478372       15,496,908

I'll try to "disambiguate that" in subsequent releases. 

ops$tkyte%ORA10GR2> create global temporary table gtt as select * from all_objects where 1=0;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select blocks from v$sort_usage where username = USER;

no rows selected

ops$tkyte%ORA10GR2> insert into gtt select * from all_objects;

50071 rows created.

ops$tkyte%ORA10GR2> select blocks from v$sort_usage where username = USER;

    BLOCKS
----------
       768

ops$tkyte%ORA10GR2> insert into gtt select * from all_objects;

50071 rows created.

ops$tkyte%ORA10GR2> select blocks from v$sort_usage where username = USER;

    BLOCKS
----------
      1408

(SELECT sum(c1.dur_hrs_day) from c_child_DAY c1 
WHERE c.event_id = c1.event_id and trunc(c1.daytime,'MM')=trunc(c.daytime,'MM') 
) AS C_event_hrs_mth, 

(select sum(c1.prod_loss_day) FROM r_child_DAY c1 
WHERE c.event_id = c1.event_id and trunc(c1.daytime,'MM')=trunc(c.daytime,'MM') 
) AS child_loss_mth, 

(select sum(c1.lpg_val_day) from r_child_DAY c1 
WHERE c.event_id = c1.event_id and trunc(c1.daytime,'MM')=trunc(c.daytime,'MM') 
) AS child_lpg_loss_mth 

(select sum(dur_hrs_day) x, sum(prod_loss_day) y, sum(lgp_val_day) z, 
        event_id, trunc(daytime,'mm') daytime
   from c_child_day
  group by event_id, trunc(daytime,'mm')
) 

select ...., (select sum(x) from t where t.y = a.y and t.z = a.z)
  from A, ....

select ...., sum_x
  from A, ...., (select sum(x), y, z from t group by y, z ) T
where a.y = t.y and a.z = t.z

select ...., sum_x
  from A, ...., (select sum(x), y, z from t where y between 1 and 50 
                 group by y, z ) T
where a.y = t.y and a.z = t.z

select *
  from store
 where store_key in (select store_key
                       from sales
                      group by store_key
                     having sum(sales.quantity) > (select avg(sum(quantity)) from sales group by store_key) );

/* 
 *   Code added by: username
 *   Created on: date
 *   modification history: ......
 *
 *   logic:
 *     We will join stores to sales and gropu by the store_name (which is 
 *     unique - just like store key).  This will let us compute sum(quantity) - 
 *     the total sum 'quantity' (what they refer to as sales) by store.  
 *     We then compare this sum() in the having clause to the average sales
 *     by store, which we compute by getting total sales divided by the number
 *     of stores.
 */ 
select ......

drop table store_sales;
drop table store_cnt;
drop table store_qty;

create global temporary table store_qty
on commit preserve rows
as select sum(quantity) all_sales from sales;

create global temporary table store_cnt
on commit preserve rows
as select count(*) nbr_stores from store;

create global temporary table store_sales
on commit preserve rows
as select store_name, sum(quantity) store_sales
from store natural join sales group by store_name;

select store_name, store_sales, all_sales / nbr_stores avg_sales
from store_qty, store_cnt, store_sales
where store_sales > (all_sales / nbr_stores);

ops$tkyte%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> drop table temp_table_xmpl;

Table dropped.

ops$tkyte%ORA11GR2> drop table perm_table_xmpl;

Table dropped.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> CREATE GLOBAL TEMPORARY TABLE TEMP_TABLE_XMPL(COLUMN_X varchar2(30)) ON COMMIT DELETE ROWS;

Table created.

ops$tkyte%ORA11GR2> CREATE INDEX TEMP_INDEX ON TEMP_TABLE_XMPL(COLUMN_X);

Index created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> CREATE  TABLE PERM_TABLE_XMPL(COLUMN_X varchar2(30));

Table created.

ops$tkyte%ORA11GR2> CREATE INDEX PERM_INDEX ON PERM_TABLE_XMPL(COLUMN_X);

Index created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2> set autotrace traceonly statistics;
ops$tkyte%ORA11GR2> insert into temp_table_xmpl values ( 'a' );

1 row created.


Statistics
----------------------------------------------------------
          5  recursive calls
         20  db block gets
          6  consistent gets
          0  physical reads
        616  redo size
        901  bytes sent via SQL*Net to client
       1011  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte%ORA11GR2> insert into temp_table_xmpl values ( 'a' );

1 row created.


Statistics
----------------------------------------------------------
          0  recursive calls
          3  db block gets
          1  consistent gets
          0  physical reads
        292  redo size
        907  bytes sent via SQL*Net to client
       1011  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2> set autotrace traceonly statistics;
ops$tkyte%ORA11GR2> insert into perm_table_xmpl values ( 'a' );

1 row created.


Statistics
----------------------------------------------------------
         67  recursive calls
        100  db block gets
         59  consistent gets
          0  physical reads
      10120  redo size
        901  bytes sent via SQL*Net to client
       1011  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte%ORA11GR2> insert into perm_table_xmpl values ( 'a' );

1 row created.


Statistics
----------------------------------------------------------
          0  recursive calls
          5  db block gets
          1  consistent gets
          0  physical reads
        464  redo size
        909  bytes sent via SQL*Net to client
       1011  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte%ORA11GR2> exec runStats_pkg.rs_start;

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> begin
  2          for i in 1 .. 1000
  3          loop
  4                  execute immediate 'create global temporary table gtt ( x int, y int )';
  5                  execute immediate 'drop table gtt';
  6          end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> exec runStats_pkg.rs_middle;

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> exec null;

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> exec runStats_pkg.rs_stop;
Run1 ran in 1492 cpu hsecs
Run2 ran in 0 cpu hsecs

Name                                  Run1        Run2        Diff
...
LATCH.PL/SQL warning settings       10,004           4     -10,000
LATCH.call allocation               10,010           0     -10,010
STAT...HSC Heap Segment Block       10,214          15     -10,199
LATCH.shared pool simulator         11,689           1     -11,688
STAT...cluster key scans            13,000           0     -13,000
STAT...calls to kcmgas              13,749           0     -13,749
LATCH.redo writing                  16,939           0     -16,939
STAT...cluster key scan block       17,000           0     -17,000
LATCH.messages                      17,271           0     -17,271
LATCH.redo allocation               18,925           1     -18,924
LATCH.undo global data              20,104           1     -20,103
STAT...commit cleanouts succes      22,398           0     -22,398
STAT...db block gets from cach      23,400           0     -23,400
STAT...commit cleanouts             24,402           0     -24,402
STAT...index fetch by key           25,001           0     -25,001
STAT...table scan rows gotten       25,055           0     -25,055
STAT...shared hash latch upgra      27,000           0     -27,000
LATCH.Change Notification Hash      36,621          15     -36,606
STAT...session cursor cache hi      45,790           2     -45,788
STAT...enqueue requests             46,422           3     -46,419
STAT...enqueue releases             46,421           2     -46,419
STAT...redo entries                 54,015          15     -54,000
STAT...index scans kdiixs1          66,200           0     -66,200
LATCH.DML lock allocation           70,403           1     -70,402
STAT...physical read total byt      73,728           0     -73,728
STAT...cell physical IO interc      73,728           0     -73,728
STAT...physical read bytes          73,728           0     -73,728
STAT...db block gets from cach      76,368          48     -76,320
STAT...db block gets                76,368          48     -76,320
STAT...parse count (total)          79,418           3     -79,415
STAT...no work - consistent re      81,404           0     -81,404
STAT...consistent gets from ca      83,419           9     -83,410
STAT...consistent gets - exami      90,338           4     -90,334
LATCH.enqueue hash chains           93,164           5     -93,159
STAT...db block changes             94,225          72     -94,153
STAT...calls to get snapshot s     117,406           2    -117,404
STAT...buffer is not pinned co     120,201           0    -120,201
STAT...execute count               122,208           5    -122,203
STAT...opened cursors cumulati     124,208           5    -124,203
STAT...consistent gets from ca     208,754          13    -208,741
STAT...consistent gets             208,754          13    -208,741
LATCH.shared pool                  223,452          52    -223,400
STAT...session uga memory max      393,072     123,452    -269,620
STAT...session logical reads       285,122          61    -285,061
LATCH.row cache objects            313,530          43    -313,487
STAT...IMU Redo allocation siz     387,612           0    -387,612
STAT...session uga memory          393,072           0    -393,072
STAT...recursive calls             398,266           3    -398,263
STAT...IMU undo allocation siz     403,700           0    -403,700
LATCH.cache buffers chains         649,409         236    -649,173
STAT...session pga memory max      655,360           0    -655,360
STAT...session pga memory          655,360    -196,608    -851,968
STAT...temp space allocated (b   1,048,576           0  -1,048,576
STAT...undo change vector size   4,327,112       3,424  -4,323,688
STAT...redo size                13,038,916       4,608 -13,034,308

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
1,549,667         547  -1,549,120##########%

PL/SQL procedure successfully completed.