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

For our setup let’s assume we have two users:

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?

The Oracle website has the answer:

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.

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.

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.

So if you want to COMPUTE the statistics (which means to actually consider every row and not just estimate the statistics), use the following syntax:

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:

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.

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!

As always, I don’t guarantee anything, so please note:

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.

There are a few reasons to use a response file:

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.

In an earlier post, I showed how to install SQL*Plus on Debian and based on that tutorial, I wrote a little shell script to query a database (I called it check_oracle_dual.sh):