Open DB in RO

Read only oracle database tips

CREATE OR REPLACE TRIGGER
   manage_service
after startup on database
DECLARE
   role VARCHAR(30);
BEGIN
   SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
   IF role = 'PRIMARY' THEN
      DBMS_SERVICE.START_SERVICE('sales_rw');
   ELSE
      DBMS_SERVICE.START_SERVICE('sales_ro');
END IF;
END;

CONNECT BY Explained

 

 Source 

Result of each iteration is used as PRIOR in next iteration. 

Calculate Field Offset in a Record

Useful for generate load file:

select table_name, COLUMN_ID, COLUMN_NAME,DATA_TYPE, DATA_LENGTH, sum(DATA_LENGTH) 

over(PARTITION BY  table_name order by COLUMN_ID RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as "Running Length"

from all_tab_columns where table_name ='PS_REQ_HDR'