How to disable 10g recycle bin (to avoid ORA-01658)

Developer feedback that she is not able to create a table, regarless of 13GB free space in the tablespace.

The error message is :

ORA-01658: unable to create INITIAL extent for segment in tablespace.

Problem solved immediately after I purged recyclebin from my 2nd feeling.


Finally , I found :
in 10g , if you have database running with recyclebin=on  for some period and you have objects create & drop in those tablespaces.
Due to some reason, e.g restore to another location with recyclebin = off, or you decided to turn it off. Howerver, the dropped objects  when recyclebin was ON , will remain in the recyclebin even if we set the recyclebin parameter to OFF.  Hence, the space shown in dba_free_space is not swapped out, cuased the error.

Below is my test.

SYS@ODSPRX2> show parameter recyclebin;

NAME                                 TYPE        VALUE                          
------------------------------------ ----------- ------------------------------ 
recyclebin                           string      on                             



SYS@ODSPRX2> create tablespace tbs1 datafile '/software/oraods/labs/recyclebin/tbs1.dbf' size 5m
  2  extent management local uniform size 512k
  3  segment space management auto;

Tablespace created.

@> conn / as sysdba
Connected.


SYS@ODSPRX2> create user liqy identified by liqyliqy default tablespace tbs1;

User created.

SYS@ODSPRX2> grant dba to liqy;

Grant succeeded.

SYS@ODSPRX2> conn liqy/liqyliqy
Connected.
LIQY@ODSPRX2> create table t1 (f1 number)  ;

Table created.

LIQY@ODSPRX2> create table t2 (f1 number)  storage(initial 512k);

Table created.

LIQY@ODSPRX2> select segment_name, bytes from dba_segments where tablespace_name='TBS1';

SEGMENT_NAME                                                                    
--------------------------------------------------------------------------------
     BYTES                                                                      
----------                                                                      
T1                                                                              
    524288                                                                      
                                                                                
T2                                                                              
    524288                                                                      
                                                                                

LIQY@ODSPRX2> select bytes from user_free_space where tablespace_name='TBS1';

     BYTES                                                                      
----------                                                                      
   3670016                                                                      

LIQY@ODSPRX2> select bytes/512/1024 from user_free_space where tablespace_name='TBS1';

BYTES/512/1024                                                                  
--------------                                                                  
             7                                                                  

LIQY@ODSPRX2> alter table t2 allocate extent (size 3670016);

Table altered.

LIQY@ODSPRX2> select bytes from user_free_space where tablespace_name='TBS1';

no rows selected

LIQY@ODSPRX2> create table t3 (f1 number);
create table t3 (f1 number)
*
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace TBS1 


LIQY@ODSPRX2> select segment_name, bytes from dba_segments where tablespace_name='TBS1';

SEGMENT_NAME                                                                    
--------------------------------------------------------------------------------
     BYTES                                                                      
----------                                                                      
T1                                                                              
    524288                                                                      
                                                                                
T2                                                                              
   4194304                                                                      
                                                                                

LIQY@ODSPRX2> drop table t1;

Table dropped.

LIQY@ODSPRX2> select bytes/512/1024 from user_free_space where tablespace_name='TBS1';

BYTES/512/1024                                                                  
--------------                                                                  
             1                                                                  

LIQY@ODSPRX2> create table t3 (f1 number);

Table created.


LIQY@ODSPRX2> drop table t3;

Table dropped.

LIQY@ODSPRX2> select bytes/512/1024 from user_free_space where tablespace_name='TBS1';

BYTES/512/1024                                                                  
--------------                                                                  
             1                                                                  

LIQY@ODSPRX2> conn / as sysdba
Connected.
SYS@ODSPRX2> alter system set recyclebin=off;

System altered.

SYS@ODSPRX2> conn liqy/liqyliqy
Connected.
LIQY@ODSPRX2> select bytes/512/1024 from user_free_space where tablespace_name='TBS1';

BYTES/512/1024                                                                  
--------------                                                                  
             1                                                                  

LIQY@ODSPRX2> create table t3 (f1 number);
create table t3 (f1 number)
*
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace TBS1 


LIQY@ODSPRX2> purge recyclebin;

Recyclebin purged.

LIQY@ODSPRX2> create table t3 (f1 number);

Table created.

LIQY@ODSPRX2> spool off
SYS@ODSPRX2> drop tablespace tbs1 including contents and datafiles;

Tablespace dropped.

SYS@ODSPRX2> spool off

But in 11.2.0.2, the dropped objects will be swapped out even with recyclebin=off.

BTW, in 11gr2 we can't use 'alter system' to change value of recyclebin, while in 10g we can change it on the fly. The oracle document is not correct.

UTF8 and AL32UTF8

In short, I think we should use AL32UTF8 to keep characterset support latest Unicode standard. 

 Oracle 8i (or lower) RDBMS and client releases connect to an 9i (or up) AL32UTF8 system or when connecting using database links from an 9i (or up) AL32UTF8 database to an 8i (or lower) database.  

UTF8 is Unicode revision 3.0 in 8.1.7 and up. AL32UTF8 is updated with newer Unicode versions in each major release.

If you need to understand "code point" , check code point and description in UTF-8 wiki

As far as these two character sets go in Oracle,  the only difference between AL32UTF8 and UTF8 character sets is that AL32UTF8 stores characters beyond U+FFFF as four bytes (exactly as Unicode defines UTF-8). Oracle’s “UTF8” stores these characters as a sequence of two UTF-16 surrogate characters encoded using UTF-8 (or six bytes per character).  Besides this storage difference, another difference is better support for supplementary characters in AL32UTF8 character set.

in

Difference between UTF8 and AL32UTF8

Caution:

AL32UTF8 is the Oracle Database character set that is appropriate for XMLType data. It is equivalent to the IANA registered standard UTF-8 encoding, which supports all valid XML characters. 

Using database character set UTF8 for XML data could cause a fatal error or affect security negatively.

References:

UTF-8 wiki

Problems connecting to AL32UTF8 databases from older versions (8i and lower) [ID 237593.1]

Unicode Character Sets In The Oracle Database [ID 260893.1]

Difference between UTF8 and AL32UTF8  http://oracleappstechnology.blogspot.com/2007/10/difference-between-utf8-and-al32utf8.html

Difference between UTF8 and AL32UTF8 character sets in Oracle

UTF8 to AL32UTF8 

When PHP and Oracle assume the worst about each other

AL32UTF8/UTF8 (Unicode) Database Character Set Implications

 AL32UTF8 / UTF8 (Unicode) Database Character Set Implications [ID 788156.1]

Hash Group by exceed temporary tablespace

Tuned a "HASH GROUP BY" after increase the pga_aggregate_target from 190MB to 2GB , since workload increased over the time.

Feeling the data volume in below plan :

- massive SUMs

- 52 milliows row,

- 5 month's data.

PERFSTAT@ABC>@disp_cursor_plan                                                                                             
Enter value for sqlid: 885gs8h6jjxzc                                                                                         
old   1: select * from table(dbms_xplan.display_cursor('&sqlid'))
new   1: select * from table(dbms_xplan.display_cursor('885gs8h6jjxzc'))

SQL_ID  885gs8h6jjxzc, child number 0
-------------------------------------
       ,sum(CASE period WHENesets
to_date('201107','YYYYMM') THEN (case hourofday when 0 then mou else 0 end) ELSE 0
       ,sum(case hourofday when 0 then mou else 0 end) as
       ,sum(CASE period WHEN to_date('201107','YYYYMM') THEN (case
hourofday when 0 then no_call else 0 end) ELSE 0 END) AS nocall1m_0001
,sum(case hourofday when 0 then no_call else 0 end) as nocall6m_0001
,sum(CASE period WHEN to_date('201107','YYYYMM') THEN (case hourofday when 1 then
       ,sum(case hourofday when 1 then102
       ,sum(CASE period WHEN2
to_date('201107','YYYYMM') THEN (case hourofday when 1 then no_call else 0 end)
       ,sum(case hourofday when 1 then no_call else 0
       ,sum(CASE period WHEN to_date('201107','YYYYMM') THEN
(case hourofday when 2 then mou else 0 end) ELSE 0 END) AS mou1m_0203

Plan hash value: 2842556147

--------------------------------------------------------------------------------------------
| Id  | Operation          | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                       |       |       |   263K(100)|          |
|   1 |  HASH GROUP BY     |                       |    52M|  3929M|   263K  (8)| 00:52:48 |
|*  2 |   TABLE ACCESS FULL| DM_TRAN_LOCALOUTGOING |    52M|  3929M|   253K  (4)| 00:50:43 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("CALL_TYP"='VOICE' AND "HOUROFDAY">=0 AND "HOUROFDAY"<=23 AND
              "PERIOD"<=TO_DATE(' 2011-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "PERIOD">=TO_DATE(' 2011-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

Note
-----
   - dynamic sampling used for this statement


37 rows selected.

Hash group (and hash joins, as well as other operations such as sorts etc.) can use either optimal (i.e. in-memory), one-pass or multi-pass methods. The last two methods use TEMP storage and is thus much slower.
By increasing the number of possible items you might have exceeded the number of items that will fit in memory reserved for this type of operations.
Try looking at v$sql_workarea_active whilst the query is running, to see if this is the case. Or look at v$sql_workarea for historical information. It will also give you an indication of how much memory and/or temp space is needed for the operation.
If turns out to be the actual problem - try increasing the pga_aggregate_target initialization parameter, if possible. The amount of memory available for optimal hash/sort operations is usually around a 5% fraction of the pga_aggregate_target.

Useful info from http://www.orafaq.com/node/1446

Hash Join

   The hash join is used for high-volume equi-joins (joins with equals predicates). Oracle performs a single read of the smaller row source (call this T1) and builds a hash table in memory. The join key is used as the hash-key of the hash table. Then a single pass of the larger row source (call this T2) is performed, hashing the join key of each row to obtain an address in the hash table where it will find matching T1 rows.
  Provided T1 remains small enough to build the hash table in memory, T2 can be scaled up to any arbitrarily large volume without affecting throughput or exceeding temp space. If T1 cannot be hashed in memory, then a portion of the hash-table spills to disk. When the hash table is probed by T2, the rows with join keys that match those parts of the in-memory hash table are joined immediately; the rest are written to TEMP and joined in a second pass. The bigger T1 is, the smaller the proportion of the hash table that can fit in memory, and the larger the proportion of T2 that must be scanned twice. This slows the Hash Join down considerably and also makes the join non-scalable.

For large hash processes, where temp tablespace usage is unavoidable, temp tablespace groups offer significant improvement in I/O performance and more CPU utilization.

Indexed Nested Loops

 Indexed Nested Loops is used primarily in low volume joins; it is efficient over small volumes and versatile enough to be used in a variety of situations. Although it is fully scalable, Indexed Nested Loops is inefficient over large data volumes.

2.8 millions  rows in driving table looks up a larger table still should be regarded as inefficient. I had once case which is unable to complete running for 12 hours. Solved by using hash join plus partition pruning. 

Sort-Merge

A sort-merge join works by reading each row-source in the join separately; sorting both sets of results on the join column(s); then concurrently working through the two lists, joining the rows with matching keys. Sort-Merge is generally faster than Indexed Nested Loops but slower than Hash Join for equi-joins. It is used almost exclusively for non-equi joins (>, <, BETWEEN) and will occasionally be used when one of the row sources is pre-sorted (eg. a GROUP BY inline view)
If both row sources are small then they may both be sorted in memory, however large sorts will spill to disk making then non-scalable.
There is no way to make a Sort-Merge join scalable. The only other way to resolve a non-equijoin is to use Nested Loops, which is slower. As volumes increase, Sort-Merge will continue to out-perform Nested Loops, but will eventually run out of Temp space. The only solution is to extend TEMP, or convert the join to Nested Loops (and then wait).


 also   read

http://www.dbspecialists.com/files/presentations/temp_space.html

http://stackoverflow.com/questions/154722/can-anyone-explain-how-the-oracle-hash-group-works 

http://www.dba-oracle.com/tips_oracle_hash_joins.htm

relatioship of physical reads and logical I/O

"A physical read of table or index data places the block into the buffer cache. Then we performan a logical I/O to retrieve the block. Hence most physical reads are immediately followed by a logical I/O!"

Generally, all physical I/Os result in logical I/Os 

--from 《effective Oracle by Design》