Oracle: how to post to https address

The normal way to achieve that is to push the certificates into the Oracle wallet via orapki.
However, if using 11.2.0.1 or XE, this is not feasible because SHA-2 certificates are not supported, and you will get

ORA-29273: HTTP request failed
ORA-06512: at “SYS.UTL_HTTP”, line 1722
ORA-28857: Unknown SSL error
ORA-06512: at line 1

A workaround is to use Apache Reverse Proxy:
Make sure that you have mod_ssl installed, and add into httpd.conf

SSLProxyEngine on
ProxyPass /oneagain https://oneagain.net
ProxyPassReverse /oneagain https://oneagain.net

Then replace:

utl_http.begin_request(‘https://oneagain.net’,’POST’);

with:

utl_http.begin_request(‘http://apache-host/oneagain/’,’POST’);

And forget about the wallet.

php/oci-extension: missing leading zero

Reading numbers from Oracle in PHP results in missing leading 0s if -1 < value < 1.
The reason for this behavior is because Oracle OCI omits 0s in results and PHP converts all results from OCI to strings without performing any casting depending on column datatype.

A simple workaroud consists in converting to float:

$a = '.63';
echo (float) $a;

source: http://stackoverflow.com/questions/4284571/php-oci-oracle-and-default-number-format

Oracle alternative:

REGEXP_REPLACE(TO_CHAR(x), '^\.', '0.')

or if we handle negative numbers

REGEXP_REPLACE(TO_CHAR(x), '(-?)^\.', '\10.')

Oracle: split / join

See http://stackoverflow.com/questions/381231/hidden-features-in-oracle

SQL> declare
  2    v_array apex_application_global.vc_arr2;
  3    v_string varchar2(2000);
  4  begin
  5  
  6    -- Convert delimited string to array
  7    v_array := apex_util.string_to_table('alpha,beta,gamma,delta', ',');
  8    for i in 1..v_array.count
  9    loop
 10      dbms_output.put_line(v_array(i));
 11    end loop;
 12  
 13    -- Convert array to delimited string
 14    v_string := apex_util.table_to_string(v_array,'|');
 15    dbms_output.put_line(v_string);
 16  end;
 17  /
alpha
beta
gamma
delta
alpha|beta|gamma|delta

Oracle: dense_rank explanation

RANK, DENSE_RANK, FIRST and LAST Analytic Functions

Oracle: return a row even if select returns no rows

SELECT nvl(data_name, 'nothing') FROM (  
    SELECT data_name
    FROM data_table
    WHERE data_table.type = v_t_id

    UNION ALL

    SELECT NULL AS data_name
    FROM dual
  ) WHERE data_name is not null or ROWNUM = 1;

Oracle Average Active Sessions per hour

From A Tour of the AWR Tables

select
   to_char(round(sub1.sample_time, 'HH24'), 'YYYY-MM-DD HH24:MI') as sample_hour,
   round(avg(sub1.on_cpu),1) as cpu_avg,
   round(avg(sub1.waiting),1) as wait_avg,
   round(avg(sub1.active_sessions),1) as act_avg,
   round( (variance(sub1.active_sessions)/avg(sub1.active_sessions)),1) as act_var_mean
from
   ( -- sub1: one row per second, the resolution of SAMPLE_TIME
     select
        sample_id,
        sample_time,
        sum(decode(session_state, 'ON CPU', 1, 0))  as on_cpu,
        sum(decode(session_state, 'WAITING', 1, 0)) as waiting,
        count(*) as active_sessions
     from
        dba_hist_active_sess_history
     where
        sample_time > sysdate - (&hours/24)
     group by
        sample_id,
        sample_time
   ) sub1
group by
   round(sub1.sample_time, 'HH24')
order by
   round(sub1.sample_time, 'HH24')
;

Oracle: select from object type

select treat(user_data as prov_db.prov_msg_type).request_id request_id from prov_db.qt_prov_msg;

Oracle: ORA-38301: can not perform DDL/DML over objects in Recycle Bin

As of 10.2g, Oracle introduced a recycle bin for dropped objects, in order to recover them with flashback.
So, if for example, you drop a table like in

DROP TABLE RADIUS_DB.RADACCT3;

Then you won't be able to drop related contraints that were renamed following the table drop, like BIN$7Cd8os82uGngQAB/AQANPQ==$0

To see objects in recycle bin:

select * from dba_recyclebin order by droptime desc;

To remove the BIN* constraints, you need to remove the dropped table from the recycle bin:

purge table radius_db.radacct3;

Oracle: schemas/tables measurements

Size of schemas:

SELECT owner, sum(bytes)/1024/1024 "Size in MiB"
FROM dba_segments
WHERE owner not in ('GPRS_TEST_DB', 'GPRS_TRIAL_DB', 'MSG_DB', 'PARAM_DB', 'PERF_DB', 'TASK_DB', 'VAS_INDICATOR_DB', 'VAS_LOG_DB', 'VAS_MULTIMSI_DB', 'VAS_PROPERTY_DB', 'XDB')
and owner like '%_DB'
group by owner
order by owner;

Size of tables

SELECT owner, segment_type, segment_name, sum(bytes)/1024/1024 "Size in MiB"
FROM dba_segments
WHERE segment_type like 'TABLE%'
and segment_name not like 'BIN%'
group by owner, segment_type, segment_name;

Num_rows of table

select owner, table_name, num_rows
from dba_tables
WHERE owner not in ('GPRS_TEST_DB', 'GPRS_TRIAL_DB', 'MSG_DB', 'PARAM_DB', 'PERF_DB', 'TASK_DB', 'VAS_INDICATOR_DB', 'VAS_LOG_DB', 'VAS_MULTIMSI_DB', 'VAS_PROPERTY_DB', 'XDB')
and owner like '%_DB'
and table_name ='HT_CO_OFFER'
order by owner;

Count indexes per table

SELECT owner, table_name, count(index_name)
FROM all_indexes
WHERE owner not in ('GPRS_TEST_DB', 'GPRS_TRIAL_DB', 'MSG_DB', 'PARAM_DB', 'PERF_DB', 'TASK_DB', 'VAS_INDICATOR_DB', 'VAS_LOG_DB', 'VAS_MULTIMSI_DB', 'VAS_PROPERTY_DB', 'XDB')
and owner like '%_DB'
group by owner, table_name
order by owner;

Oracle: Dump data into CSV file

Tom Kyte gives details on how to do that here:
http://tkyte.blogspot.fr/2009/10/httpasktomoraclecomtkyteflat.html

NB: See the flat script as a base and use chr(59) if you want to have ';' instead of tabs in the output.

Oracle: Handle error codes with sqlplus

Tom Kyte explains it here:
http://tkyte.blogspot.fr/2010/04/new-thing-about-sqlplus.html

1. Start with

   set errorlogging on identifier toto
   delete from sperrorlog where identifier = 'toto'
   /
   

2. Then use some sqlplus commands
3. And terminate with

   col nb_errors new_value nb_errors
   select count(*) as nb_errors from sperrorlog where identifier = 'toto'
   /
   exit nb_errors

This allows for testings the return code of sqlplus in a bash / perl script.

NB: See http://ss64.com/ora/syntax-sqlplus.html for the sqlplus syntax

Oracle: ORA-00600: internal error code, arguments: [kqd-objerror$]

If this error occurs when trying to drop a trigger, e.g.

DROP TRIGGER RADIUS_DB.RADACCT_RADACCTID;

1. Find the object_id of the trigger

   select object_id from dba_objects where object_name='RADACCT_RADACCTID';

2. Insert the object_id into objerror$

   insert into sys.objerror$ values(126915);
   commit;

3. shutdown abort
4. startup
5. Retry to drop the trigger, this should be OK now.

   DROP TRIGGER RADIUS_DB.RADACCT_RADACCTID;

Oracle: using statspack

Use the steps as described in:
http://www.oracledistilled.com/oracle-database/performance/installing-and-configuring-statspack/
which are:
1) Create the tablespace to hold the Statspack tables.

CREATE TABLESPACE statspack_db_data DATAFILE 
  '/home/oracle/oracle/oradata/CRMMBQT/statspack_db_data_01.dbf' SIZE 500M AUTOEXTEND ON NEXT 10M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL uniform size 1M
SEGMENT SPACE MANAGEMENT AUTO;

2) Run the ORACLE_HOME/rdbms/admin/spcreate.sql script to create the PERFSTAT schema.

cd /home/oracle/oracle/product/11.2.0/dbhome_1/rdbms/admin
> sqlplus / as sysdba
SQL> @spcreate

A list of available table spaces will be presented. Type in the name of the table space created earlier and hit the enter key.
When selecting a temporary table space for the PERFSTAT user, keep the default tablespace as proposed.

3) Execute a snapshot w/ level 7:

exec statspack.snap(i_snap_level => 7);

4) Run a report:
ORACLE_HOME\rdbms\admin\spreport.sql is used to generate an instance level report.
You will then pick a beginning snapshot and an ending snapshot, as well as a file name to save results.

cd /home/oracle/oracle/product/11.2.0/dbhome_1/rdbms/admin
> sqlplus / as sysdba
SQL> @spreport

Oracle: drop interval partitions

source: http://prutser.wordpress.com/2010/01/11/dropping-interval-partitions/

-- Count number of partitions: 905
select count(*) from dba_tab_partitions where table_name = 'AT_PROCESS_LOG';

-- List of partitions to drop: keep 30 latest partitions
select * from (
select partition_name, interval, high_value, partition_position
from dba_tab_partitions where table_name = 'AT_PROCESS_LOG'
order by partition_position desc
) where 1 < partition_position and partition_position < 905 - (30-1);

Drop syntax examples:

-- Drop partition w/ high_value = 16-03-2012
alter table monitor_db.at_process_log drop partition for (to_date('15-03-2012','DD-MM-YYYY')); 

-- Drop partition from name
alter table monitor_db.at_process_log drop partition SYS_P61;

Oracle: Random UUID

v_uuid RAW(16) := sys.dbms_crypto.randombytes(16);

Oracle: Caching techniques to call functions in queries

Efficient Function Calls From SQL

Oracle: Stop a datapump job run via crontab

Show datapump jobs

select * from dba_datapump_jobs;

Stop a datapump job

DECLARE
   handle NUMBER;
BEGIN
    -- Format: DBMS_DATAPUMP.attach('[job_name]','[owner_name]');
    handle := DBMS_DATAPUMP.attach('SYS_EXPORT_FULL_02','SYSTEM');
    DBMS_DATAPUMP.STOP_JOB(handle, 1, 0);
END;

Oracle: select from list of values

select distinct column_value from table(sys.odcinumberlist(1,1,2,3,3,4,4,5));

or

select column_value from table(sys.dbms_debug_vc2coll('One', 'Two', 'Three', 'Four'));

ORA-01555: snapshot too old: rollback segment number 61 with name "_SYSSMU61_1706556987$" too small

SQL> select max(maxquerylen) from v$undostat;

26687

Add 20% to this value and update UNDO_RETENTION parameter.

SQL> ALTER SYSTEM SET UNDO_RETENTION = 32024;

Oracle PL/SLQ: On Emulating FINALLY

On Emulating FINALLY by Steven Feuerstein