Category: nice to know

The performance of Oracle is determined by 4 factors: the CPU, internal memory, disk access and network performance. The latter, network performance, is only relevant if data are written or read from a network device. In scheme, it looks like: Let us look at the CPU. The most important data can be derived from this…

Oracle has several possibilities to back up the data. One possibility is backing up via data pump. This facility can be used by the command line. Let us discuss three different commands to use the data pump facility. The commands use the expdp and impdp facility that are stored in the bin directory with Oracle…

It happens to me quite often. I install Oracle and it only listens to the localhost. In a few steps, one can get it to listen to the IP address instead of the localhost. What is the problem if Oracle only listens to the localhost? This implies that the instance cannot be accessed from outside.…

Oracle has a nice facility to store data in an encrypted form. This allows to hide data content from users while still allowing them to see the columns. If one would like to use the encryption facilities, one must be given this grant: GRANT EXECUTE  ON dbms_crypto TO username; The code to store data in an encrypted…

Oracle has recently introduced masking as one of the facilities. Masking allows data to be hidden from users / or only display part of the content. Other users can still the normal content. Let us take an example. A salary can shown as a random number to some users while others might still see the…

The question is simple: we have an Oracle query and we want to use one search criterium as a parameter. Let us take a simple example. We want to use a parameter in the SQL statement: select * from doel where teller = parameter. So we can write the SQL only once and we can…

One may implement security by creating a view. This view then only contains the columns that you would like to show. If one adds a filter, one may also limit the rows that are shown. The user that is allowed to read a view, only sees the columns and rows that are included in the…

Oracle knows the command “drop table”. One may include this in a script where a table is first deleted (using “drop table”) and subsequently rebuilt with additional SQL statement. But what happens if the table doesn’t exist? One may then end up with an error SQL Error: ORA-00942: “table or view does not exist”. How…

It is possible to process CSV files in Spark as if these files are tables. That looks very promising: reading a file, translating the file into something that can be seen as a table and subsequently applying SQL to this. We need some additional jars however. First, we need to download 2 jars: http://mvnrepository.com/artifact/com.databricks/spark-csv_2.10 and…

Oracle has now included an Oracle tuning advisor. Its purpose is to advise on a SQL statement. For me, it provides an answer on whether to add an index or not. I was taught that one should always start with tables that have no indices. Only when it is demonstrated that indices are used, one…