Space Requirement for Oracle Data

The simple test below tells that 1 Character of data needs approx 1.25 Bytes of disk space in Oracle.

SQL>DEFINE n1 = 100000
SQL>DROP TABLE tsize;
Table dropped.
SQL>CREATE TABLE tsize ( v1 VARCHAR2(100), n1 NUMBER(15), d1 DATE, v2 VARCHAR2(10));
Table created.
SQL>
SQL>DECLARE
  2     lv_count NUMBER;
  3  BEGIN
  4     lv_count := &n1;
  5     FOR i IN 1..lv_count
  6     LOOP
  7        INSERT INTO tsize(v1,n1,d1,v2)
  8           VALUES ( 'This Is a String of 100 Characters Length.This Is a String of 100 Characters
 Length.This Is a String'
  9                    ,123456789012345
 10                    ,SYSDATE
 11                    ,'This  Test'
 12                  );
 13     END LOOP;
 14     COMMIT;
 15  END;
 16  /
old   4:    lv_count := &n1;
new   4:    lv_count := 100000;
PL/SQL procedure successfully completed.
SQL>
SQL>ANALYZE TABLE TSIZE COMPUTE STATISTICS;
Table analyzed.
SQL>
SQL>SELECT AVG_ROW_LEN FROM USER_TABLES WHERE TABLE_NAME = 'TSIZE';
AVG_ROW_LEN
-----------
        133
SQL>
SQL>SELECT BYTES/1024/1024 size_in_mb FROM dba_segments WHERE segment_name = 'TSIZE';
SIZE_IN_MB
----------
        16
SQL>
SQL>SELECT bytes/(avg_row_len*&n1)  size_per_char_bytes
  2    FROM user_tables         ut
  3        ,dba_segments        ds
  4   WHERE ds.segment_name     = ut.table_name
  5     AND ut.table_name       = 'TSIZE' ;    
old   1: SELECT bytes/(avg_row_len*&n1)  size_per_char_bytes
new   1: SELECT bytes/(avg_row_len*100000)  size_per_char_bytes
SIZE_PER_CHAR_BYTES
-------------------
         1.26144481
        
/*******************************************************************************************/
SQL>DEFINE n1 = 100000
SQL>DROP TABLE tsize;
Table dropped.
SQL>CREATE TABLE tsize ( v1 VARCHAR2(100), v2 VARCHAR2(100), v3 VARCHAR2(100), v4 VARCHAR2(100));
Table created.
SQL>
SQL>BEGIN
  2     FOR i IN 1..100000
  3     LOOP
  4        INSERT INTO tsize(v1) VALUES('This Is a String of 100 Characters Length.This Is a String
of 100 Characters Length.This Is a String');
  5     END LOOP;
  6     COMMIT;
  7  END;
  8  /
PL/SQL procedure successfully completed.
SQL>SELECT BYTES/1024/1024 size_in_mb FROM dba_segments WHERE segment_name = 'TSIZE';
SIZE_IN_MB
----------
        12
SQL>
SQL>UPDATE tsize
  2     SET v2 = v1;
100000 rows updated.
SQL>
SQL>UPDATE tsize
  2     SET v3 = v1;
100000 rows updated.
SQL>
SQL>SELECT BYTES/1024/1024 size_in_mb FROM dba_segments WHERE segment_name = 'TSIZE';  
SIZE_IN_MB
----------
        36
SQL>
SQL>UPDATE tsize
  2    SET v4 = v1;
100000 rows updated.
SQL>
SQL>SELECT BYTES/1024/1024 size_in_mb FROM dba_segments WHERE segment_name = 'TSIZE';  
SIZE_IN_MB
----------
        47
SQL>
SQL>ANALYZE TABLE TSIZE COMPUTE STATISTICS;
Table analyzed.
SQL>
SQL>SELECT bytes/(avg_row_len*&n1)  size_per_char_bytes
  2     FROM user_tables         ut
  3         ,dba_segments        ds
  4    WHERE ds.segment_name     = ut.table_name
  5      AND ut.table_name       = 'TSIZE' ;      
old   1: SELECT bytes/(avg_row_len*&n1)  size_per_char_bytes
new   1: SELECT bytes/(avg_row_len*100000)  size_per_char_bytes
SIZE_PER_CHAR_BYTES
-------------------
         1.19910151
/*************************************************************************************/        

Introduction to Oracle Advanced Queuing (AQ)

1.   Introduction

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

2.   Troubleshooting Advanced Queuing and Oracle Streams Propagation Issues (Doc ID 233099.1)

http://blogs.oracle.com/db/entry/oracle_support_master_note_for_troubleshooting_advanced_queuing_and_oracle_streams_propagation_issue

3.   Data Dictionary Views for Advance Queuing

 -- List of Queue Tables

SELECT * FROM dba_tables;

-- List of all the Queues

SELECT * FROM dba_queues;

-- List of Messaging Gateways

SELECT * FROM mgw_gateway

-- List of Queues in Queue Table

SELECT * FROM ppid_staging.aq$ppid_qtab_ack

-- List of Queues and its Subscribers/Consumers

select * from all_queue_subscribers

-- List of Foreign Queues

SELECT * FROM mgw_foreign_queues

--List of MQ Links Details (Host/Port/Channel)

SELECT * FROM mgw_mqseries_links

--List of Schedules, Types(Outbound/Inbound) Source,Target

SELECT * FROM mgw_schedules;

--List of Queue Schedules for AQ to AQ messaging

SELECT * FROM dba_queue_schedules

-- Registry

SELECT * FROM dba_registry

BEGIN

    dbms_mgwadm.startup;

END;

/

BEGIN

    dbms_mgwadm.shutdown;

END;

/

Hash, Nested Loop and Sort Merge Join

A good blog for Tuning Tips and other stuff.

http://oracle-online-help.blogspot.com/2007/03/nested-loops-hash-join-and-sort-merge.html