Advanced Queuing hello world

First for those who are looking for the difference between Queuing, Advanced Queuing (AQ) and Streams Advanced Queuing, there is none. There is no Beginner Queuing and Advanced Queuing was renamed to Streams advanced queuing when streams was popular and renamed back to Advanced Queuing was streams was deprecated. I am the provider, I give […]

Change background of Oracle Entreprise Manager

If you have more than one OEM and want a red status bar in your production OEM, here the proceedings Stop your cloud control emctl stop oms -all Unzip the $MW/oracle_common/modules/oracle.adf.view_11.1.1/adf-richclient-impl-11.jar in a new directory $MW/jdk16/jdk/bin/jar -xvf $MW/oracle_common/modules/oracle.adf.view_11.1.1/adf-richclient-impl-11.jar edit the picture dbd_topShadow.png (for instance) in adf/images/fusion- and copy it to META-INF/adf/images/fusion- with your favorite picture […]

Monitoring details on your explain plan

We know how to generate an explain plan. SET AUTOT ON, the good old ambulance in Toad (which is no longer an ambulance those days), the explain plan button in SQL Developer or simply EXPLAIN PLAN create table t1 partition by hash(object_id) partitions 16 as select * from dba_objects; exec dbms_stats.gather_table_stats(user, ‘T1’) explain plan for […]

Google Reader end of life

For those of my readers who were using Google Reader, here are my comments 1) backup your data, read this : 2) for the web gui, there is a one-click migration to Feedly : 3) you can import the OPML (Outline Processor Markup Language) file from Google Takeout into Microsoft Outlook (right click […]

ODBC 32bits for Windows 64bits

Windows-On-Windows 64-bit (WOW64) enables you to run 32bits applications in 64bits OS. You will see there is another powershell, another registry, another ODBC tool, another Oracle client. %SYSTEMROOT%\syswow64\WindowsPowerShell\v1.0\powershell.exe First, we run powershell(x86) Get-itemproperty HKLM:\SOFTWARE\ORACLE\*| Select-Object ORACLE_HOME,ORACLE_HOME_NAME ORACLE_HOME ORACLE_HOME_NAME ———– —————- C:\oracle\product\11.2.0\client_32 client32bit_11203 Only the Oracle 32bit client is displayed cmd /c “%SYSTEMROOT%\syswow64\odbcconf.exe /a {configdsn “”Oracle […]

Oracle ODBC hello world with powershell

Demo : cmd /c “odbcconf.exe /a {configdsn “”Oracle in OraClient11g_home1″” “”DSN=helloworld|SERVER=DB01″”}” Create a helloworld data source connecting to your DB01 tns alias in your OraClient11g_home1 Oracle Home. It is easy to get the Oracle Home name with Get-itemproperty HKLM:\SOFTWARE\ORACLE\*| Select-Object ORACLE_HOME,ORACLE_HOME_NAME ORACLE_HOME ORACLE_HOME_KEY ———– ————— C:\oracle\product\11.1.0\client_1 OraClient11g_home1 C:\oracle\product\11.2.0\client_1 OraClient11g_home2 Then we create the connection (as […]

Best practice : use double quotes, even in DBMS_STATS

Whenever you create a table, it is better to use double quotes to avoid invalid identified. SQL> CREATE TABLE /XXX(x number); CREATE TABLE /XXX(x number) * ERROR at line 1: ORA-00903: invalid table name SQL> CREATE TABLE “/XXX”(x number); Table created. Even in DBMS_STATS you should use double quotes SQL> exec dbms_stats.gather_table_stats(user,’/XXX’) BEGIN dbms_stats.gather_table_stats(user,’/XXX’); END; […]

When v$session_longops is not long enough

With large table scans, sometimes the estimated total work is far beyond reality SQL> select message from v$session_longops where target=’SCOTT.EMP’; MESSAGE ———————————————————— Table Scan: SCOTT.EMP: 7377612 out of 629683 Blocks done The total work is the Oracle estimation : SQL> select blocks from dba_tables where table_name=’EMP’; BLOCKS ———- 629683 This may differ quite a lot […]

Generate Microsoft Office Documents from command line

In previous posts (e.g. Export to Excel) I wrote about using HTML format to export to Excel. Let’s do it for real, let’s dive into the .xls file format and learn how to generate dynamic excel from Unix! 1) create one time your excel file manually. With graphs, colors, sounds, up to you. Or Word, […]

Default Oracle Home in Windows

In Oracle Universal Installer and OPatch User’s Guide it is documented that The first Oracle home is named the “DEFAULT_HOME” and registers itself in the Windows NT Registry. Remember, NT means New Technology 🙂 There is apparently a Home Selector that is a part of the installation software, maybe something like D:\oracle\product\11.2.0\client_1\bin\selecthome.bat. Sometimes. Not sure […]

Rman and DBGSQL message

I have not seen DBGSQL very often. But today again, a duplicate in RMAN was failing with, amoung other errors, sqlcode 911 RMAN> duplicate target database to DB02 until time “to_date(‘2013-01-29_00:00:00′,’YYYY-MM-DD_HH24:MI:SS’)” nofilenamecheck ; DBGSQL: TARGET> select 2013-01-29_00:00:00 from sys.dual DBGSQL: sqlcode = 911 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: […]

How big was my database last month

If you backup your database at least weekly, you could check the datafile size from your backup history. Without catalog : select to_char(d,'”W”IW-IYYY’) compl, sum(b)/1024/1024/1024*8192 GB from ( select max(DATAFILE_BLOCKS) b, trunc(completion_time,’IW’) d from v$backup_datafile group by FILE# ,trunc(completion_time,’IW’) ) group by d order by d; COMPL GB ——– —— W30-2012 3.73 W31-2012 4.84 W32-2012 […]

accent insensitive regexp

Ever wanted to find an accent insentive expression like “bébé” in a column ? Maybe you tried to list all possible accents. But Posix has the class for you, the list of éèëê could be refered as [=e=] SELECT * FROM TAB WHERE REGEXP_LIKE(C,’b[[=e=]]b[[=e=]]’) not only [=e=] is easier to read and to type, but […]

How big was my table yesterday

Oracle saves a lot of information on your behalf. Whenever you get yourself an AWR reported, you access some historic tables (included in the Diagnostic Pack). Those tables could also be accessed manually. SELECT savtime,owner,object_name,rowcnt,blkcnt FROM sys.WRI$_OPTSTAT_TAB_HISTORY w, dba_objects o WHERE o.owner=’SCOTT’ AND o.object_name=’EMP’ and o.object_id = W.OBJ# ORDER BY o.owner, o.object_name, w.savtime; SAVTIME OWNER […]

Enhancement Request : SSL listener and OEM

#em12c still does not support SSL ! Encrypting network connection (https, ssh, sftp) is common sense in today’s business. In Enhancement Request 6512390, Created 19-Oct-2007, the customer requested support for SSL. Most recent update : it is postponed to 13cR2 at least ! *** 09/14/12 04:04 am DISCUSSION ***As we kick off 13c release, cleaning […]

Create database link and logon trigger

Today I could not understand why I was getting ORA-1031 with create database link. After analysis, I found out a logon trigger that changed the current schema. As Tom always says, triggers are evil… DEMO: SQL> create user u1 identified by xxx; User created. SQL> grant create session, create database link to u2 identified by […]

Drop database link in another schema

Today I wrote this script : drop_database_link.sql accept owner char prompt “Enter database link owner : ” accept db_link char prompt “Enter link name : ” begin dbms_scheduler.create_job( job_name=>’&owner..drop_database_link’, job_type=>’PLSQL_BLOCK’, job_action=>’BEGIN execute immediate ”drop database link &db_link”;END;’ ); dbms_scheduler.run_job(‘&owner..drop_database_link’,false); dbms_lock.sleep(2); dbms_scheduler.drop_job(‘&owner..drop_database_link’); end; / I am using the scheduler to run a job as another user. […]