Creating database backups using exp & and crontab

The following is a neat little script that will allow you to create logical backups (and remove old ones) of your schema in the same file system of the database.


ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE
ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
export ORACLE_HOME
ORACLE_SID=XE
export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH
export PATH

BKUP_DEST=/home/rode/cbackups
find $BKUP_DEST -name 'backup*.dmp' -mtime +10 -exec rm {} \;



cd /home/rode/cbackups && /u01/app/oracle/product/11.2.0/xe/bin/exp schema/password FILE=backup_`date +'%Y%m%d-%H%M'`.dmp

You will need to change the bits highlighted in orange with paths of your system. The script starts by exporting necessary paths, then removes any old backups greater than 10 days before using the exp utility to create the backup.

To set it up, follow these instructions: 

1. Create a directory where the backups will be stored. In my case it is: /home/ai/cbackups
2. Open vi and save the scripts (after replacing bits in orange with your own setup) in your home directory as say: /home/ai/backup_script.sh
3. Next, run crontab -e to set up a new cron job.
4. Add an entry like:
   10 0 * * * /home/ai/backup_script.sh

Above the the script will run every 10 minutes past midnight. Check out this link on how to schedule crontab: http://www.adminschoice.com/crontab-quick-reference


You may opt (and better idea) to use data pump instead as it supercedes the export/import utilities and if exporting involves lots of data. Check this document on how to use data pump:
http://www.oracle.com/technetwork/issue-archive/2009/09-jul/datapump11g2009-quickstart-128718.pdf

Resolving ORA-01940 error

This post shows how to get around ORA-01940 error. You typically get this error when the schema you are trying to drop is connected to something (presumably another application).

The following script will allow you to kill the sessions and drop the schema.

set serveroutput on
DECLARE
  cursor c1 is select SID, SERIAL# from v$session where username = '<schema_name';
BEGIN
FOR rec IN c1 loop
EXECUTE IMMEDIATE 'alter system kill session '''||rec.SID||','||rec.SERIAL#||'''';
end loop;
DROP USER <SCHEMA_NAME> CASCADE;
END;

Oracle Apex: Adding Up Tabular Form Column Values

Often with tabular form we get the requirement to tally up the total of one of the columns as user add/edit/delete rows of data.

In this post I'll show an easy & efficient way to achieve this using Javascript.

Step 1

Get your Javascript function to add-up the total ready. I have created a simple function which you can customise for your application. You will need to know the ID of the tabular form element you are trying to add.

All editable APEX tabular forms that are ID'ed like f0x_000x. You can find them out by using Firebug (chrome, firefox) or IE Developer Tool by hitting F12 as below. 

In my case I need add up the total in Salary column as I add rows or edit entries and add it to the Total Salary text field above the tabular form.

So here is the script. Find out the ID of your field and update the italics part. 

<script language="JavaScript" type="text/javascript">

 function addTotal()
 {
  var items = document.getElementsByName("f07"); // Tabular form column to add up
  
  $total = 0;
  $itemValue = 0;
  for (var i = 0; i < items.length; i++)
  {  
   // if non-numeric character was entered, it will be considered as 0, 
   // isNaN returns true if anything but number was entered
   if(isNaN(items[i].value) || items[i].value == null || items[i].value == "")
    $itemValue = 0;
   else
    $itemValue = parseFloat(items[i].value); // convert to number
   
   $total =$total+ $itemValue; // add up
  } 
  
  // $x sets the text field to be updated to the column total just calculated
  $x('P5_TOTAL_SALARY').value = $total;
 }

</script>

STEP 2. Test out the javascript.

Its a good idea to make sure your code works before putting it page attribute. So open up Firebug, go to the console tab and copy & paste the body of the function (everything in between the curly braces) and hit enter. If your Total Salary textfield gets updated as below then the function is working fine. 

STEP 3. 

Go to page attribute and in HTML Header & Body Attribute enter the code above.

STEP 4.
Next in page attribute, go to Javascript tab and  and add the newly created function addTotal() to where it says Execute when Page Loads. This will ensure that columns are totaled when page is loaded.

STEP 5. 

I will wrap that tabular form with a < DIV> tag and call this addTotal() function with onkeyup event. This will update the column total on every subsequent entries. 

With these steps, you should be able to add up the total of a tabular form column.

Here is how the it should behave if you implement the above steps correctly. 

http://apex.oracle.com/pls/apex/f?p=63895:5 

Script to Get Oracle Database Size

Save the following code as SQL file and run it via SQL developer or SQLPlus as SYSDBA to get the database size (dont forget the / in the end).

SET SERVEROUTPUT ON

Declare

  ddf Number:= 0;

  dtf Number:= 0;

  log_bytes Number:= 0;

  total Number:= 0;

BEGIN

  select sum(bytes)/power(1024,3) into ddf from dba_data_files;

  select sum(bytes)/power(1024,3) into dtf from dba_temp_files;

  select sum(bytes)/power(1024,3) into log_bytes from v$log;

  total:= round(ddf+dtf+log_bytes, 3);

  dbms_output.put_line('TOTAL DB Size is: '||total||'GB ');

END;
/

Fixing /bin/bash^M: bad interpreter: No such file or directory

If you write a bash script in Windows and you get this error after FTP'ing and running it in Linux, try the following to fix it

perl -i -pe's/\r$//;' script_name.sh

Reverse Engineering Oracle Database Schema to ER Diagram

In this post, I'll show how to use Oracle SQL Developer to reverse engineer a schema into a relational diagram. You will need SQL Developer v3.x for this.


1) Go to File >> Import >> Data Dictionary

2) This will open up the Import Wizard. Click on Add to connect to the Database which contains the schema you wish to reverse engineer.

3) Once you have successfully tested connection click OK.

4) Select the newly created connection and click on Next

5) You will be prompted for password again, enter and click OK. 

6) Now we select the schema. In my case I selected a schema called Tutorial that comes bundled with Oracle APEX.

7) Next we select the tables. You may select all tables or a handful to see the relationships between them. Here I wanted to see the relationship between the tables prefixed by OEHR (again comes with APEX).

8) Next you will get a view summary. Verify and click Finish

9) You will be asked for your logins for 3rd time in this process. Enter and click OK.

10) Review the log and close to view your ER diagram.

11) To print your ER diagram to image/PDF. Go to Data Modeler >> Print Diagram >> To Image File.

Hiding a button in Oracle Apex

There may be a situations where you want to hide a button without removing it or making it's Condition = Never.

Simply add the following: style="display:none" to Button's attribute to accomplish this.