Data, data en nog eens data

Author: tom

  • Cold backup in Oracle

    —

    by

    tom
    in

    Actually, taking a cold backup in Oracle is quite simple. If you choose to undertake a cold backup by copying tables, it consists of 5 steps. Step 1 is identifying the location of the data files. We have 3 commands for that: select name from v$datafile;select member from v$logfile;select name from v$controlfile; We must remember…

  • Changing granularity

    —

    by

    tom
    in

    Let us suppose, we have a table that has a granularity in two dimensions. Think of a customer and a product dimension. So each record has a combination of customer identification and product identification that is unique. Let us also assume that we would decrease that level of granularity into one dimension. Think of customer…

  • The ORACLE_HOME

    —

    by

    tom
    in

    Oracle needs to have a set of context variables that allow Oracle to know where the programmes are located. Upon installation, you have to check if these variabled are set. If not, you have to set them yourself. In linux, such variables can ne set in the .bash_profile file that is stored as a file…

  • SQL in steps

    —

    by

    tom
    in

    Let us assume we have a request to show results from a query that is too massive to run on-line. Let us also assume that we may distinguish in this query a series on countries / persons that must be run. In pseudo code, we have the situation that we may have to run: select…

  • Writing content from Oracle

    —

    by

    tom
    in

    This note provides a PL/SQL programme that writes the the content of a table to a file. It also uses a steering table (emp_ids) that contains a series of ids that can be used in a susequent query from which the content can be generated. Let me first provide the code: create or replace PROCEDURE…

  • Working with XML in Oracle

    —

    by

    tom
    in

    Oracle has nice features to export data as an XML file. Let us discuss a few of them. The first one is to use SQL Developer. We could already export a table in SQL developer as an XML file. The second one is to use the DBMS_XMLGEN package. This allows to use queries in an…

  • Data miner in Oracle

    —

    by

    tom
    in

    I have played a bit with Oracle data miner. This feature is available in the enterprise edition of Oracle version 11 or above. It can be accessed from SQL developer. Before using the feature, one must first set up a user. This can be done with: CREATE USER dmuser IDENTIFIED BY dmpassword DEFAULT TABLESPACE users…

  • Adding information

    —

    by

    tom
    in

    Today, I struggled a bit with a simple situation. I had a table with a primary key and an additional field that was partially filled. I also had a secondary table with a key and data were to be loaded in the first table. In a scheme, the situation looked like: In this example we…

  • Calculating CPU usage in Oracle

    —

    by

    tom
    in

    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…

  • Backing up in Oracle via data pump

    —

    by

    tom
    in

    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…