Simon Krenger

Ok, so here is a problem that a developer brought up. I thought that this problem is quite interesting and also a bit confusing. Obviously, according to Oracle, this is not a bug – it’s a feature!

When issuing a CREATE MATERIALIZED VIEW statement for a different schema (as DBA), one might encounter the following error:

dba@KDB01:SQL> CREATE MATERIALIZED VIEW simon.simon_mv AS SELECT * FROM dual;
CREATE MATERIALIZED VIEW simon.simon_mv AS SELECT * FROM dual
                                                         *
ERROR at line 1:
ORA-01031: insufficient privileges

Read the rest of this entry »

When you install the InstantClient binaries, you often want to use your existing TNSNAMES.ORA file containing all your databases. So how do you specify the location of your TNSNAMES.ORA file?

Read the rest of this entry »

Whenever one has to move large amounts of data from one database to another, storage space might become an issue. An option to circumvent this problem is to use a database link with Oracle Datapump to move the data from one database to another. This way, the data is exported across the network and imported directly into the target database.

In this post, I will provide an example on how to move data via a Oracle Datapump and a database link. This post is based on the excellent entry in Oracle FAQ and basically comments all the steps mentioned in the article.

Read the rest of this entry »

In another post, a commenter asked how to resolve the “Windows Server: The terminal server has exceeded the maximum number of allowed connections” error when connecting from a Mac.

Read the rest of this entry »

So here is another post I keep mainly for my own reference, since I regularly need to gather new schema statistics. The information here is based on the Oracle documentation for DBMS_STATS, where all the information is available.

Read the rest of this entry »

So here is another post I keep for my own reference, since I keep forgetting about precision and scale, primarily which is which.

The Oracle Concepts guide states that the NUMBER datatype stores fixed and floating-point numbers. A column with the NUMBER datatype can be defined as follows:

Read the rest of this entry »

So this happens when you try to add a privilege to an ACL that does not exist:
SQL> EXEC DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE ('myacl.xml', 'SIMON', true, 'connect');
BEGIN DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE ('myacl.xml', 'SIMON', true, 'connect'); END;

ERROR at line 1:
ORA-31001: Invalid resource handle or path name "/sys/acls/myacl.xml"
ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 384
ORA-06512: at line 1
Oracle Database 11g introduced fine-grained access control to external network services using Access Control Lists (ACLs). This basically allows you to take control over which users access which network resources, regardless of package grants. Read the rest of this entry »

An anonymous commenter noticed that there was a new version of the Tanuki Service Wrapper available. So I hereby present the latest version of the wrapper. Merry christmas to all of you!

Read the rest of this entry »

After installing the Operating System (in my case usually Red Hat Enterprise Linux or Oracle Enterprise Linux) and configuring all necessary parameters, one has to install the Oracle software. It is usually a good idea to use a response file to do this.

Read the rest of this entry »

For Nagios, many, many Oracle plugins are available for checking database availability and performance. But if you just want to check if the instance is up and running (and not add more complexity), you can use the simple script provided here.

Read the rest of this entry »